Pandas 讀取excel考試檔，並清洗數據¶

以下資料姓名為亂數產生，考試資料也非真人實際資料

In [1]:

import pandas as pd

#顯示所有列
pd.set_option('display.max_columns', None)
#顯示所有行
pd.set_option('display.max_rows', None)
#設置value的顯示長度為100，默認為50
pd.set_option('max_colwidth',100)

df = pd.read_excel('00mytest.xlsx')
df.head()

Out[1]:

	排序	學測\n應試號碼	考生姓名	身分代碼	國文	英文	數學	學科能力測驗成績	審查資料面試	指定項目甄試成績	個人申請總成績	名次	附註
0	NaN	NaN	NaN	NaN	社會	自然	總級分	NaN	NaN	NaN	NaN	NaN	NaN
1	1	10033861	張俊嘉	1	12	11	12	23.62	81.00 83.67	57.76	81.39	34	不錄取，未達最低錄取分數
2	NaN	NaN	NaN	NaN	14	11	60	NaN	NaN	NaN	NaN	NaN	NaN
3	2	10110042	黃穎萱	1	12	8	13	23	83.33 92.33	61.93	84.93	12	正取12
4	NaN	NaN	NaN	NaN	14	11	58	NaN	NaN	NaN	NaN	NaN	NaN

以上資料顯示，這個excel檔的欄位名稱其實延伸到第2列

In [2]:

# 所以我們要將header設為2列但「社會」、「自然」和「總級分」的資料跑到下一列去了
df = pd.read_excel('00mytest.xlsx', header = [0,1])
df

Out[2]:

	排序	學測\n應試號碼	考生姓名	身分代碼	國文	英文	數學	學科能力測驗成績	審查資料面試	指定項目甄試成績	個人申請總成績	名次	附註
	Unnamed: 0_level_1	Unnamed: 1_level_1	Unnamed: 2_level_1	Unnamed: 3_level_1	社會	自然	總級分	Unnamed: 7_level_1	Unnamed: 8_level_1	Unnamed: 9_level_1	Unnamed: 10_level_1	Unnamed: 11_level_1	Unnamed: 12_level_1
0	1	10033861	張俊嘉	1	12	11	12	23.62	81.00 83.67	57.76	81.39	34	不錄取，未達最低錄取分數
1	NaN	NaN	NaN	NaN	14	11	60	NaN	NaN	NaN	NaN	NaN	NaN
2	2	10110042	黃穎萱	1	12	8	13	23	83.33 92.33	61.93	84.93	12	正取12
3	NaN	NaN	NaN	NaN	14	11	58	NaN	NaN	NaN	NaN	NaN	NaN
4	3	10111172	李鳳善	1	13	14	8	22.87	80.67 81.67	56.86	79.74	41	不錄取，未達最低錄取分數
5	NaN	NaN	NaN	NaN	11	12	58	NaN	NaN	NaN	NaN	NaN	NaN
6	4	10127348	張慶松	1	11	12	15	24.37	84.67 89.00	61	85.38	9	正取9
7	NaN	NaN	NaN	NaN	11	11	60	NaN	NaN	NaN	NaN	NaN	NaN
8	5	10129065	李心怡	1	12	13	10	24.12	86.00 89.00	61.4	85.53	8	正取8
9	NaN	NaN	NaN	NaN	13	13	61	NaN	NaN	NaN	NaN	NaN	NaN
10	6	10148374	朱佩珊	1	13	12	11	23.37	81.00 85.33	58.43	81.81	30	不錄取，未達最低錄取分數
11	NaN	NaN	NaN	NaN	12	11	59	NaN	NaN	NaN	NaN	NaN	NaN
12	7	10159940	杜怡婷	1	13	11	14	24.5	81.00 85.67	58.56	83.07	23	不錄取，未達最低錄取分數
13	NaN	NaN	NaN	NaN	10	12	60	NaN	NaN	NaN	NaN	NaN	NaN
14	8	10163034	彭緯聿	1	12	13	11	24.37	78.67 84.67	57.46	81.84	29	不錄取，未達最低錄取分數
15	NaN	NaN	NaN	NaN	12	13	61	NaN	NaN	NaN	NaN	NaN	NaN
16	9	10170573	蕭惠君	1	13	12	12	23.87	77.67 84.00	56.9	80.78	37	不錄取，未達最低錄取分數
17	NaN	NaN	NaN	NaN	12	11	60	NaN	NaN	NaN	NaN	NaN	NaN
18	10	10172046	楊哲銘	1	12	13	11	24.12	80.33 85.00	58.09	82.22	27	不錄取，未達最低錄取分數
19	NaN	NaN	NaN	NaN	13	12	61	NaN	NaN	NaN	NaN	NaN	NaN
20	11	10173537	李馨慧	1	13	13	11	23.75	84.67 86.33	59.93	83.68	18	不錄取，未達最低錄取分數
21	NaN	NaN	NaN	NaN	12	11	60	NaN	NaN	NaN	NaN	NaN	NaN
22	12	10178838	何侑劭	1	12	13	12	24.12	85.67 0.00	25.7	49.83	##	不錄取，未通過指定項目檢定
23	NaN	NaN	NaN	NaN	11	12	60	NaN	NaN	NaN	NaN	NaN	NaN
24	13	10181637	洪青凱	1	12	14	11	23.75	81.33 84.33	58.13	81.88	28	不錄取，未達最低錄取分數
25	NaN	NaN	NaN	NaN	12	11	60	NaN	NaN	NaN	NaN	NaN	NaN
26	排序	學測\n應試號碼	考生姓名	身分代碼	國文	英文	數學	學科能力測驗成績	審查資料面試	指定項目甄試成績	個人申請總成績	名次	附註
27	NaN	NaN	NaN	NaN	社會	自然	總級分	NaN	NaN	NaN	NaN	NaN	NaN
28	14	10195746	毛彥志	1	12	14	11	22.75	80.67 85.00	58.2	80.95	35	不錄取，未達最低錄取分數
29	NaN	NaN	NaN	NaN	12	9	58	NaN	NaN	NaN	NaN	NaN	NaN
30	15	10196244	黃啟斌	1	14	13	11	24.12	80.67 81.33	56.73	80.86	36	不錄取，未達最低錄取分數
31	NaN	NaN	NaN	NaN	12	11	61	NaN	NaN	NaN	NaN	NaN	NaN
32	16	10208250	吳向惟	1	12	13	11	23.12	86.67 88.00	61.2	84.33	15	正取15
33	NaN	NaN	NaN	NaN	11	11	58	NaN	NaN	NaN	NaN	NaN	NaN
34	17	10213841	鄭宇瑄	1	12	12	13	24	80.33 81.67	56.76	80.77	38	不錄取，未達最低錄取分數
35	NaN	NaN	NaN	NaN	12	11	60	NaN	NaN	NaN	NaN	NaN	NaN
36	18	10214143	李涵迪	1	11	12	10	22.62	81.33 77.67	55.46	78.09	42	不錄取，未達最低錄取分數
37	NaN	NaN	NaN	NaN	14	11	58	NaN	NaN	NaN	NaN	NaN	NaN
38	19	10218344	陳如君	1	11	13	8	22.5	87.67 91.33	62.83	85.33	10	正取10
39	NaN	NaN	NaN	NaN	14	12	58	NaN	NaN	NaN	NaN	NaN	NaN
40	20	10221035	陳慧發	1	11	12	14	24.87	83.00 85.67	59.16	84.04	16	不錄取，未達最低錄取分數
41	NaN	NaN	NaN	NaN	13	12	62	NaN	NaN	NaN	NaN	NaN	NaN
42	21	10221861	王聖全	1	13	10	12	23.87	81.33 88.33	59.73	83.61	19	不錄取，未達最低錄取分數
43	NaN	NaN	NaN	NaN	13	12	60	NaN	NaN	NaN	NaN	NaN	NaN
44	22	10223472	王芳羽	1	12	11	11	23.62	89.00 89.00	62.3	85.93	4	正取4
45	NaN	NaN	NaN	NaN	14	12	60	NaN	NaN	NaN	NaN	NaN	NaN
46	23	10224674	林凱鈞	1	14	11	10	23.87	85.67 84.00	59.3	83.18	22	不錄取，未達最低錄取分數
47	NaN	NaN	NaN	NaN	14	12	61	NaN	NaN	NaN	NaN	NaN	NaN
48	24	10234247	洪玟君	1	13	12	8	22.62	84.00 81.00	57.6	80.23	40	不錄取，未達最低錄取分數
49	NaN	NaN	NaN	NaN	13	12	58	NaN	NaN	NaN	NaN	NaN	NaN
50	25	10235547	張鈞元	1	12	8	14	23.75	82.33 83.33	58.03	81.78	31	不錄取，未達最低錄取分數
51	NaN	NaN	NaN	NaN	13	12	59	NaN	NaN	NaN	NaN	NaN	NaN
52	26	10240760	葉誠妤	1	12	9	12	23.37	82.00 88.67	60.06	83.44	20	不錄取，未達最低錄取分數
53	NaN	NaN	NaN	NaN	12	13	58	NaN	NaN	NaN	NaN	NaN	NaN
54	排序	學測\n應試號碼	考生姓名	身分代碼	國文	英文	數學	學科能力測驗成績	審查資料面試	指定項目甄試成績	個人申請總成績	名次	附註
55	NaN	NaN	NaN	NaN	社會	自然	總級分	NaN	NaN	NaN	NaN	NaN	NaN
56	27	10244938	張立白	1	11	14	11	23.37	81.67 84.33	58.23	81.61	33	不錄取，未達最低錄取分數
57	NaN	NaN	NaN	NaN	12	11	59	NaN	NaN	NaN	NaN	NaN	NaN
58	28	10245260	林宏成	1	13	12	8	22.37	87.67 86.67	60.96	83.34	21	不錄取，未達最低錄取分數
59	NaN	NaN	NaN	NaN	14	11	58	NaN	NaN	NaN	NaN	NaN	NaN
60	29	10247745	許靖雯	1	13	11	12	23.75	86.00 88.67	61.26	85.02	11	正取11
61	NaN	NaN	NaN	NaN	11	12	59	NaN	NaN	NaN	NaN	NaN	NaN
62	30	10249766	張嘉侑	1	11	12	10	23.12	85.00 89.33	61.23	84.36	14	正取14
63	NaN	NaN	NaN	NaN	12	13	58	NaN	NaN	NaN	NaN	NaN	NaN

先看一下欄位名稱

In [3]:

df.columns

Out[3]:

MultiIndex([(             '排 序',  'Unnamed: 0_level_1'),
            ( '學       測\n應試號碼',  'Unnamed: 1_level_1'),
            (            '考生姓名',  'Unnamed: 2_level_1'),
            (           '身分 代碼',  'Unnamed: 3_level_1'),
            (              '國文',                  '社會'),
            (              '英文',                  '自然'),
            (              '數學',                 '總級分'),
            (       '學科能力 測驗成績',  'Unnamed: 7_level_1'),
            ('審查資料          面試',  'Unnamed: 8_level_1'),
            (       '指定項目 甄試成績',  'Unnamed: 9_level_1'),
            (      '個人申請 總 成 績', 'Unnamed: 10_level_1'),
            (              '名次', 'Unnamed: 11_level_1'),
            (              '附註', 'Unnamed: 12_level_1')],
           )

In [4]:

# 把第2列的欄位清除掉
df.columns = df.columns.droplevel(level = 1)
df

Out[4]:

	排序	學測\n應試號碼	考生姓名	身分代碼	國文	英文	數學	學科能力測驗成績	審查資料面試	指定項目甄試成績	個人申請總成績	名次	附註
0	1	10033861	張俊嘉	1	12	11	12	23.62	81.00 83.67	57.76	81.39	34	不錄取，未達最低錄取分數
1	NaN	NaN	NaN	NaN	14	11	60	NaN	NaN	NaN	NaN	NaN	NaN
2	2	10110042	黃穎萱	1	12	8	13	23	83.33 92.33	61.93	84.93	12	正取12
3	NaN	NaN	NaN	NaN	14	11	58	NaN	NaN	NaN	NaN	NaN	NaN
4	3	10111172	李鳳善	1	13	14	8	22.87	80.67 81.67	56.86	79.74	41	不錄取，未達最低錄取分數
5	NaN	NaN	NaN	NaN	11	12	58	NaN	NaN	NaN	NaN	NaN	NaN
6	4	10127348	張慶松	1	11	12	15	24.37	84.67 89.00	61	85.38	9	正取9
7	NaN	NaN	NaN	NaN	11	11	60	NaN	NaN	NaN	NaN	NaN	NaN
8	5	10129065	李心怡	1	12	13	10	24.12	86.00 89.00	61.4	85.53	8	正取8
9	NaN	NaN	NaN	NaN	13	13	61	NaN	NaN	NaN	NaN	NaN	NaN
10	6	10148374	朱佩珊	1	13	12	11	23.37	81.00 85.33	58.43	81.81	30	不錄取，未達最低錄取分數
11	NaN	NaN	NaN	NaN	12	11	59	NaN	NaN	NaN	NaN	NaN	NaN
12	7	10159940	杜怡婷	1	13	11	14	24.5	81.00 85.67	58.56	83.07	23	不錄取，未達最低錄取分數
13	NaN	NaN	NaN	NaN	10	12	60	NaN	NaN	NaN	NaN	NaN	NaN
14	8	10163034	彭緯聿	1	12	13	11	24.37	78.67 84.67	57.46	81.84	29	不錄取，未達最低錄取分數
15	NaN	NaN	NaN	NaN	12	13	61	NaN	NaN	NaN	NaN	NaN	NaN
16	9	10170573	蕭惠君	1	13	12	12	23.87	77.67 84.00	56.9	80.78	37	不錄取，未達最低錄取分數
17	NaN	NaN	NaN	NaN	12	11	60	NaN	NaN	NaN	NaN	NaN	NaN
18	10	10172046	楊哲銘	1	12	13	11	24.12	80.33 85.00	58.09	82.22	27	不錄取，未達最低錄取分數
19	NaN	NaN	NaN	NaN	13	12	61	NaN	NaN	NaN	NaN	NaN	NaN
20	11	10173537	李馨慧	1	13	13	11	23.75	84.67 86.33	59.93	83.68	18	不錄取，未達最低錄取分數
21	NaN	NaN	NaN	NaN	12	11	60	NaN	NaN	NaN	NaN	NaN	NaN
22	12	10178838	何侑劭	1	12	13	12	24.12	85.67 0.00	25.7	49.83	##	不錄取，未通過指定項目檢定
23	NaN	NaN	NaN	NaN	11	12	60	NaN	NaN	NaN	NaN	NaN	NaN
24	13	10181637	洪青凱	1	12	14	11	23.75	81.33 84.33	58.13	81.88	28	不錄取，未達最低錄取分數
25	NaN	NaN	NaN	NaN	12	11	60	NaN	NaN	NaN	NaN	NaN	NaN
26	排序	學測\n應試號碼	考生姓名	身分代碼	國文	英文	數學	學科能力測驗成績	審查資料面試	指定項目甄試成績	個人申請總成績	名次	附註
27	NaN	NaN	NaN	NaN	社會	自然	總級分	NaN	NaN	NaN	NaN	NaN	NaN
28	14	10195746	毛彥志	1	12	14	11	22.75	80.67 85.00	58.2	80.95	35	不錄取，未達最低錄取分數
29	NaN	NaN	NaN	NaN	12	9	58	NaN	NaN	NaN	NaN	NaN	NaN
30	15	10196244	黃啟斌	1	14	13	11	24.12	80.67 81.33	56.73	80.86	36	不錄取，未達最低錄取分數
31	NaN	NaN	NaN	NaN	12	11	61	NaN	NaN	NaN	NaN	NaN	NaN
32	16	10208250	吳向惟	1	12	13	11	23.12	86.67 88.00	61.2	84.33	15	正取15
33	NaN	NaN	NaN	NaN	11	11	58	NaN	NaN	NaN	NaN	NaN	NaN
34	17	10213841	鄭宇瑄	1	12	12	13	24	80.33 81.67	56.76	80.77	38	不錄取，未達最低錄取分數
35	NaN	NaN	NaN	NaN	12	11	60	NaN	NaN	NaN	NaN	NaN	NaN
36	18	10214143	李涵迪	1	11	12	10	22.62	81.33 77.67	55.46	78.09	42	不錄取，未達最低錄取分數
37	NaN	NaN	NaN	NaN	14	11	58	NaN	NaN	NaN	NaN	NaN	NaN
38	19	10218344	陳如君	1	11	13	8	22.5	87.67 91.33	62.83	85.33	10	正取10
39	NaN	NaN	NaN	NaN	14	12	58	NaN	NaN	NaN	NaN	NaN	NaN
40	20	10221035	陳慧發	1	11	12	14	24.87	83.00 85.67	59.16	84.04	16	不錄取，未達最低錄取分數
41	NaN	NaN	NaN	NaN	13	12	62	NaN	NaN	NaN	NaN	NaN	NaN
42	21	10221861	王聖全	1	13	10	12	23.87	81.33 88.33	59.73	83.61	19	不錄取，未達最低錄取分數
43	NaN	NaN	NaN	NaN	13	12	60	NaN	NaN	NaN	NaN	NaN	NaN
44	22	10223472	王芳羽	1	12	11	11	23.62	89.00 89.00	62.3	85.93	4	正取4
45	NaN	NaN	NaN	NaN	14	12	60	NaN	NaN	NaN	NaN	NaN	NaN
46	23	10224674	林凱鈞	1	14	11	10	23.87	85.67 84.00	59.3	83.18	22	不錄取，未達最低錄取分數
47	NaN	NaN	NaN	NaN	14	12	61	NaN	NaN	NaN	NaN	NaN	NaN
48	24	10234247	洪玟君	1	13	12	8	22.62	84.00 81.00	57.6	80.23	40	不錄取，未達最低錄取分數
49	NaN	NaN	NaN	NaN	13	12	58	NaN	NaN	NaN	NaN	NaN	NaN
50	25	10235547	張鈞元	1	12	8	14	23.75	82.33 83.33	58.03	81.78	31	不錄取，未達最低錄取分數
51	NaN	NaN	NaN	NaN	13	12	59	NaN	NaN	NaN	NaN	NaN	NaN
52	26	10240760	葉誠妤	1	12	9	12	23.37	82.00 88.67	60.06	83.44	20	不錄取，未達最低錄取分數
53	NaN	NaN	NaN	NaN	12	13	58	NaN	NaN	NaN	NaN	NaN	NaN
54	排序	學測\n應試號碼	考生姓名	身分代碼	國文	英文	數學	學科能力測驗成績	審查資料面試	指定項目甄試成績	個人申請總成績	名次	附註
55	NaN	NaN	NaN	NaN	社會	自然	總級分	NaN	NaN	NaN	NaN	NaN	NaN
56	27	10244938	張立白	1	11	14	11	23.37	81.67 84.33	58.23	81.61	33	不錄取，未達最低錄取分數
57	NaN	NaN	NaN	NaN	12	11	59	NaN	NaN	NaN	NaN	NaN	NaN
58	28	10245260	林宏成	1	13	12	8	22.37	87.67 86.67	60.96	83.34	21	不錄取，未達最低錄取分數
59	NaN	NaN	NaN	NaN	14	11	58	NaN	NaN	NaN	NaN	NaN	NaN
60	29	10247745	許靖雯	1	13	11	12	23.75	86.00 88.67	61.26	85.02	11	正取11
61	NaN	NaN	NaN	NaN	11	12	59	NaN	NaN	NaN	NaN	NaN	NaN
62	30	10249766	張嘉侑	1	11	12	10	23.12	85.00 89.33	61.23	84.36	14	正取14
63	NaN	NaN	NaN	NaN	12	13	58	NaN	NaN	NaN	NaN	NaN	NaN

In [5]:

# 先把欄位名稱中的「空格」及「跳行」用「_」取代，再將「_」刪除
df.columns = df.columns.str.replace(' |\n', '_').str.replace('_', '')
df.columns

Out[5]:

Index(['排序', '學測應試號碼', '考生姓名', '身分代碼', '國文', '英文', '數學', '學科能力測驗成績', '審查資料面試',
       '指定項目甄試成績', '個人申請總成績', '名次', '附註'],
      dtype='object')

In [6]:

df.head()

Out[6]:

	排序	學測應試號碼	考生姓名	身分代碼	國文	英文	數學	學科能力測驗成績	審查資料面試	指定項目甄試成績	個人申請總成績	名次	附註
0	1	10033861	張俊嘉	1	12	11	12	23.62	81.00 83.67	57.76	81.39	34	不錄取，未達最低錄取分數
1	NaN	NaN	NaN	NaN	14	11	60	NaN	NaN	NaN	NaN	NaN	NaN
2	2	10110042	黃穎萱	1	12	8	13	23	83.33 92.33	61.93	84.93	12	正取12
3	NaN	NaN	NaN	NaN	14	11	58	NaN	NaN	NaN	NaN	NaN	NaN
4	3	10111172	李鳳善	1	13	14	8	22.87	80.67 81.67	56.86	79.74	41	不錄取，未達最低錄取分數

In [7]:

# 接下來將「1、3、5……等列」的「排序」、「學測應試號碼」以前1列的值取代「NaN」
df['排序'] = df['排序'].fillna(method = 'ffill')
df['學測應試號碼'] = df['學測應試號碼'].fillna(method = 'ffill')
df.head()

Out[7]:

	排序	學測應試號碼	考生姓名	身分代碼	國文	英文	數學	學科能力測驗成績	審查資料面試	指定項目甄試成績	個人申請總成績	名次	附註
0	1	10033861	張俊嘉	1	12	11	12	23.62	81.00 83.67	57.76	81.39	34	不錄取，未達最低錄取分數
1	1	10033861	NaN	NaN	14	11	60	NaN	NaN	NaN	NaN	NaN	NaN
2	2	10110042	黃穎萱	1	12	8	13	23	83.33 92.33	61.93	84.93	12	正取12
3	2	10110042	NaN	NaN	14	11	58	NaN	NaN	NaN	NaN	NaN	NaN
4	3	10111172	李鳳善	1	13	14	8	22.87	80.67 81.67	56.86	79.74	41	不錄取，未達最低錄取分數

In [8]:

# 將「排序」不是數字的刪除掉
df = df[df['排序'].astype(str).str.isdigit()]
print(len(df))
df

Out[8]:

	排序	學測應試號碼	考生姓名	身分代碼	國文	英文	數學	學科能力測驗成績	審查資料面試	指定項目甄試成績	個人申請總成績	名次	附註
0	1	10033861	張俊嘉	1	12	11	12	23.62	81.00 83.67	57.76	81.39	34	不錄取，未達最低錄取分數
1	1	10033861	NaN	NaN	14	11	60	NaN	NaN	NaN	NaN	NaN	NaN
2	2	10110042	黃穎萱	1	12	8	13	23	83.33 92.33	61.93	84.93	12	正取12
3	2	10110042	NaN	NaN	14	11	58	NaN	NaN	NaN	NaN	NaN	NaN
4	3	10111172	李鳳善	1	13	14	8	22.87	80.67 81.67	56.86	79.74	41	不錄取，未達最低錄取分數
5	3	10111172	NaN	NaN	11	12	58	NaN	NaN	NaN	NaN	NaN	NaN
6	4	10127348	張慶松	1	11	12	15	24.37	84.67 89.00	61	85.38	9	正取9
7	4	10127348	NaN	NaN	11	11	60	NaN	NaN	NaN	NaN	NaN	NaN
8	5	10129065	李心怡	1	12	13	10	24.12	86.00 89.00	61.4	85.53	8	正取8
9	5	10129065	NaN	NaN	13	13	61	NaN	NaN	NaN	NaN	NaN	NaN
10	6	10148374	朱佩珊	1	13	12	11	23.37	81.00 85.33	58.43	81.81	30	不錄取，未達最低錄取分數
11	6	10148374	NaN	NaN	12	11	59	NaN	NaN	NaN	NaN	NaN	NaN
12	7	10159940	杜怡婷	1	13	11	14	24.5	81.00 85.67	58.56	83.07	23	不錄取，未達最低錄取分數
13	7	10159940	NaN	NaN	10	12	60	NaN	NaN	NaN	NaN	NaN	NaN
14	8	10163034	彭緯聿	1	12	13	11	24.37	78.67 84.67	57.46	81.84	29	不錄取，未達最低錄取分數
15	8	10163034	NaN	NaN	12	13	61	NaN	NaN	NaN	NaN	NaN	NaN
16	9	10170573	蕭惠君	1	13	12	12	23.87	77.67 84.00	56.9	80.78	37	不錄取，未達最低錄取分數
17	9	10170573	NaN	NaN	12	11	60	NaN	NaN	NaN	NaN	NaN	NaN
18	10	10172046	楊哲銘	1	12	13	11	24.12	80.33 85.00	58.09	82.22	27	不錄取，未達最低錄取分數
19	10	10172046	NaN	NaN	13	12	61	NaN	NaN	NaN	NaN	NaN	NaN
20	11	10173537	李馨慧	1	13	13	11	23.75	84.67 86.33	59.93	83.68	18	不錄取，未達最低錄取分數
21	11	10173537	NaN	NaN	12	11	60	NaN	NaN	NaN	NaN	NaN	NaN
22	12	10178838	何侑劭	1	12	13	12	24.12	85.67 0.00	25.7	49.83	##	不錄取，未通過指定項目檢定
23	12	10178838	NaN	NaN	11	12	60	NaN	NaN	NaN	NaN	NaN	NaN
24	13	10181637	洪青凱	1	12	14	11	23.75	81.33 84.33	58.13	81.88	28	不錄取，未達最低錄取分數
25	13	10181637	NaN	NaN	12	11	60	NaN	NaN	NaN	NaN	NaN	NaN
28	14	10195746	毛彥志	1	12	14	11	22.75	80.67 85.00	58.2	80.95	35	不錄取，未達最低錄取分數
29	14	10195746	NaN	NaN	12	9	58	NaN	NaN	NaN	NaN	NaN	NaN
30	15	10196244	黃啟斌	1	14	13	11	24.12	80.67 81.33	56.73	80.86	36	不錄取，未達最低錄取分數
31	15	10196244	NaN	NaN	12	11	61	NaN	NaN	NaN	NaN	NaN	NaN
32	16	10208250	吳向惟	1	12	13	11	23.12	86.67 88.00	61.2	84.33	15	正取15
33	16	10208250	NaN	NaN	11	11	58	NaN	NaN	NaN	NaN	NaN	NaN
34	17	10213841	鄭宇瑄	1	12	12	13	24	80.33 81.67	56.76	80.77	38	不錄取，未達最低錄取分數
35	17	10213841	NaN	NaN	12	11	60	NaN	NaN	NaN	NaN	NaN	NaN
36	18	10214143	李涵迪	1	11	12	10	22.62	81.33 77.67	55.46	78.09	42	不錄取，未達最低錄取分數
37	18	10214143	NaN	NaN	14	11	58	NaN	NaN	NaN	NaN	NaN	NaN
38	19	10218344	陳如君	1	11	13	8	22.5	87.67 91.33	62.83	85.33	10	正取10
39	19	10218344	NaN	NaN	14	12	58	NaN	NaN	NaN	NaN	NaN	NaN
40	20	10221035	陳慧發	1	11	12	14	24.87	83.00 85.67	59.16	84.04	16	不錄取，未達最低錄取分數
41	20	10221035	NaN	NaN	13	12	62	NaN	NaN	NaN	NaN	NaN	NaN
42	21	10221861	王聖全	1	13	10	12	23.87	81.33 88.33	59.73	83.61	19	不錄取，未達最低錄取分數
43	21	10221861	NaN	NaN	13	12	60	NaN	NaN	NaN	NaN	NaN	NaN
44	22	10223472	王芳羽	1	12	11	11	23.62	89.00 89.00	62.3	85.93	4	正取4
45	22	10223472	NaN	NaN	14	12	60	NaN	NaN	NaN	NaN	NaN	NaN
46	23	10224674	林凱鈞	1	14	11	10	23.87	85.67 84.00	59.3	83.18	22	不錄取，未達最低錄取分數
47	23	10224674	NaN	NaN	14	12	61	NaN	NaN	NaN	NaN	NaN	NaN
48	24	10234247	洪玟君	1	13	12	8	22.62	84.00 81.00	57.6	80.23	40	不錄取，未達最低錄取分數
49	24	10234247	NaN	NaN	13	12	58	NaN	NaN	NaN	NaN	NaN	NaN
50	25	10235547	張鈞元	1	12	8	14	23.75	82.33 83.33	58.03	81.78	31	不錄取，未達最低錄取分數
51	25	10235547	NaN	NaN	13	12	59	NaN	NaN	NaN	NaN	NaN	NaN
52	26	10240760	葉誠妤	1	12	9	12	23.37	82.00 88.67	60.06	83.44	20	不錄取，未達最低錄取分數
53	26	10240760	NaN	NaN	12	13	58	NaN	NaN	NaN	NaN	NaN	NaN
56	27	10244938	張立白	1	11	14	11	23.37	81.67 84.33	58.23	81.61	33	不錄取，未達最低錄取分數
57	27	10244938	NaN	NaN	12	11	59	NaN	NaN	NaN	NaN	NaN	NaN
58	28	10245260	林宏成	1	13	12	8	22.37	87.67 86.67	60.96	83.34	21	不錄取，未達最低錄取分數
59	28	10245260	NaN	NaN	14	11	58	NaN	NaN	NaN	NaN	NaN	NaN
60	29	10247745	許靖雯	1	13	11	12	23.75	86.00 88.67	61.26	85.02	11	正取11
61	29	10247745	NaN	NaN	11	12	59	NaN	NaN	NaN	NaN	NaN	NaN
62	30	10249766	張嘉侑	1	11	12	10	23.12	85.00 89.33	61.23	84.36	14	正取14
63	30	10249766	NaN	NaN	12	13	58	NaN	NaN	NaN	NaN	NaN	NaN

接下來要將0、2、4 ……等列篩選出來，另外將1、3、5 ……列篩選出來。

In [9]:

df1 = df.iloc[::2]
df1.head()

Out[9]:

	排序	學測應試號碼	考生姓名	身分代碼	國文	英文	數學	學科能力測驗成績	審查資料面試	指定項目甄試成績	個人申請總成績	名次	附註
0	1	10033861	張俊嘉	1	12	11	12	23.62	81.00 83.67	57.76	81.39	34	不錄取，未達最低錄取分數
2	2	10110042	黃穎萱	1	12	8	13	23	83.33 92.33	61.93	84.93	12	正取12
4	3	10111172	李鳳善	1	13	14	8	22.87	80.67 81.67	56.86	79.74	41	不錄取，未達最低錄取分數
6	4	10127348	張慶松	1	11	12	15	24.37	84.67 89.00	61	85.38	9	正取9
8	5	10129065	李心怡	1	12	13	10	24.12	86.00 89.00	61.4	85.53	8	正取8

In [10]:

df2 = df.iloc[1::2]
df2.head()

Out[10]:

	排序	學測應試號碼	考生姓名	身分代碼	國文	英文	數學	學科能力測驗成績	審查資料面試	指定項目甄試成績	個人申請總成績	名次	附註
1	1	10033861	NaN	NaN	14	11	60	NaN	NaN	NaN	NaN	NaN	NaN
3	2	10110042	NaN	NaN	14	11	58	NaN	NaN	NaN	NaN	NaN	NaN
5	3	10111172	NaN	NaN	11	12	58	NaN	NaN	NaN	NaN	NaN	NaN
7	4	10127348	NaN	NaN	11	11	60	NaN	NaN	NaN	NaN	NaN	NaN
9	5	10129065	NaN	NaN	13	13	61	NaN	NaN	NaN	NaN	NaN	NaN

In [11]:

# 將df2只留
df2 = df2[['排序', '學測應試號碼', '國文', '英文', '數學']]

# 然後將欄名改成['排序', '學測應試號碼', '社會', '自然', '總級分']
df2.columns = ['排序', '學測應試號碼', '社會', '自然', '總級分']

df2.head()

Out[11]:

	排序	學測應試號碼	社會	自然	總級分
1	1	10033861	14	11	60
3	2	10110042	14	11	58
5	3	10111172	11	12	58
7	4	10127348	11	11	60
9	5	10129065	13	13	61

將df1和df2串接

In [12]:

df_final = pd.merge(df1, df2, on = ['排序', '學測應試號碼'], how = 'left')
df_final

Out[12]:

	排序	學測應試號碼	考生姓名	身分代碼	國文	英文	數學	學科能力測驗成績	審查資料面試	指定項目甄試成績	個人申請總成績	名次	附註	社會	自然	總級分
0	1	10033861	張俊嘉	1	12	11	12	23.62	81.00 83.67	57.76	81.39	34	不錄取，未達最低錄取分數	14	11	60
1	2	10110042	黃穎萱	1	12	8	13	23	83.33 92.33	61.93	84.93	12	正取12	14	11	58
2	3	10111172	李鳳善	1	13	14	8	22.87	80.67 81.67	56.86	79.74	41	不錄取，未達最低錄取分數	11	12	58
3	4	10127348	張慶松	1	11	12	15	24.37	84.67 89.00	61	85.38	9	正取9	11	11	60
4	5	10129065	李心怡	1	12	13	10	24.12	86.00 89.00	61.4	85.53	8	正取8	13	13	61
5	6	10148374	朱佩珊	1	13	12	11	23.37	81.00 85.33	58.43	81.81	30	不錄取，未達最低錄取分數	12	11	59
6	7	10159940	杜怡婷	1	13	11	14	24.5	81.00 85.67	58.56	83.07	23	不錄取，未達最低錄取分數	10	12	60
7	8	10163034	彭緯聿	1	12	13	11	24.37	78.67 84.67	57.46	81.84	29	不錄取，未達最低錄取分數	12	13	61
8	9	10170573	蕭惠君	1	13	12	12	23.87	77.67 84.00	56.9	80.78	37	不錄取，未達最低錄取分數	12	11	60
9	10	10172046	楊哲銘	1	12	13	11	24.12	80.33 85.00	58.09	82.22	27	不錄取，未達最低錄取分數	13	12	61
10	11	10173537	李馨慧	1	13	13	11	23.75	84.67 86.33	59.93	83.68	18	不錄取，未達最低錄取分數	12	11	60
11	12	10178838	何侑劭	1	12	13	12	24.12	85.67 0.00	25.7	49.83	##	不錄取，未通過指定項目檢定	11	12	60
12	13	10181637	洪青凱	1	12	14	11	23.75	81.33 84.33	58.13	81.88	28	不錄取，未達最低錄取分數	12	11	60
13	14	10195746	毛彥志	1	12	14	11	22.75	80.67 85.00	58.2	80.95	35	不錄取，未達最低錄取分數	12	9	58
14	15	10196244	黃啟斌	1	14	13	11	24.12	80.67 81.33	56.73	80.86	36	不錄取，未達最低錄取分數	12	11	61
15	16	10208250	吳向惟	1	12	13	11	23.12	86.67 88.00	61.2	84.33	15	正取15	11	11	58
16	17	10213841	鄭宇瑄	1	12	12	13	24	80.33 81.67	56.76	80.77	38	不錄取，未達最低錄取分數	12	11	60
17	18	10214143	李涵迪	1	11	12	10	22.62	81.33 77.67	55.46	78.09	42	不錄取，未達最低錄取分數	14	11	58
18	19	10218344	陳如君	1	11	13	8	22.5	87.67 91.33	62.83	85.33	10	正取10	14	12	58
19	20	10221035	陳慧發	1	11	12	14	24.87	83.00 85.67	59.16	84.04	16	不錄取，未達最低錄取分數	13	12	62
20	21	10221861	王聖全	1	13	10	12	23.87	81.33 88.33	59.73	83.61	19	不錄取，未達最低錄取分數	13	12	60
21	22	10223472	王芳羽	1	12	11	11	23.62	89.00 89.00	62.3	85.93	4	正取4	14	12	60
22	23	10224674	林凱鈞	1	14	11	10	23.87	85.67 84.00	59.3	83.18	22	不錄取，未達最低錄取分數	14	12	61
23	24	10234247	洪玟君	1	13	12	8	22.62	84.00 81.00	57.6	80.23	40	不錄取，未達最低錄取分數	13	12	58
24	25	10235547	張鈞元	1	12	8	14	23.75	82.33 83.33	58.03	81.78	31	不錄取，未達最低錄取分數	13	12	59
25	26	10240760	葉誠妤	1	12	9	12	23.37	82.00 88.67	60.06	83.44	20	不錄取，未達最低錄取分數	12	13	58
26	27	10244938	張立白	1	11	14	11	23.37	81.67 84.33	58.23	81.61	33	不錄取，未達最低錄取分數	12	11	59
27	28	10245260	林宏成	1	13	12	8	22.37	87.67 86.67	60.96	83.34	21	不錄取，未達最低錄取分數	14	11	58
28	29	10247745	許靖雯	1	13	11	12	23.75	86.00 88.67	61.26	85.02	11	正取11	11	12	59
29	30	10249766	張嘉侑	1	11	12	10	23.12	85.00 89.33	61.23	84.36	14	正取14	12	13	58

調整欄位順序，將['社會', '自然', '總級分']放到「數學」後面

In [13]:

cols = df_final.columns.tolist()
cols

Out[13]:

['排序',
 '學測應試號碼',
 '考生姓名',
 '身分代碼',
 '國文',
 '英文',
 '數學',
 '學科能力測驗成績',
 '審查資料面試',
 '指定項目甄試成績',
 '個人申請總成績',
 '名次',
 '附註',
 '社會',
 '自然',
 '總級分']

In [14]:

cols = cols[:7] + cols[-3:] + cols[7:13]
cols

Out[14]:

['排序',
 '學測應試號碼',
 '考生姓名',
 '身分代碼',
 '國文',
 '英文',
 '數學',
 '社會',
 '自然',
 '總級分',
 '學科能力測驗成績',
 '審查資料面試',
 '指定項目甄試成績',
 '個人申請總成績',
 '名次',
 '附註']

In [15]:

df_final = df_final[cols]
df_final.head()

Out[15]:

	排序	學測應試號碼	考生姓名	身分代碼	國文	英文	數學	社會	自然	總級分	學科能力測驗成績	審查資料面試	指定項目甄試成績	個人申請總成績	名次	附註
0	1	10033861	張俊嘉	1	12	11	12	14	11	60	23.62	81.00 83.67	57.76	81.39	34	不錄取，未達最低錄取分數
1	2	10110042	黃穎萱	1	12	8	13	14	11	58	23	83.33 92.33	61.93	84.93	12	正取12
2	3	10111172	李鳳善	1	13	14	8	11	12	58	22.87	80.67 81.67	56.86	79.74	41	不錄取，未達最低錄取分數
3	4	10127348	張慶松	1	11	12	15	11	11	60	24.37	84.67 89.00	61	85.38	9	正取9
4	5	10129065	李心怡	1	12	13	10	13	13	61	24.12	86.00 89.00	61.4	85.53	8	正取8

接下來將「審查資料面試」拆成「審查資料」和「面試」2個成績，再把「審查資料面試」刪除

In [16]:

df_final.insert(11, '審查資料', df_final['審查資料面試'].copy().str[:5])
df_final.insert(12, '面試', df_final['審查資料面試'].copy().str[-5:])

df_final = df_final.drop(columns = '審查資料面試')
df_final.head()

Out[16]:

	排序	學測應試號碼	考生姓名	身分代碼	國文	英文	數學	社會	自然	總級分	學科能力測驗成績	審查資料	面試	指定項目甄試成績	個人申請總成績	名次	附註
0	1	10033861	張俊嘉	1	12	11	12	14	11	60	23.62	81.00	83.67	57.76	81.39	34	不錄取，未達最低錄取分數
1	2	10110042	黃穎萱	1	12	8	13	14	11	58	23	83.33	92.33	61.93	84.93	12	正取12
2	3	10111172	李鳳善	1	13	14	8	11	12	58	22.87	80.67	81.67	56.86	79.74	41	不錄取，未達最低錄取分數
3	4	10127348	張慶松	1	11	12	15	11	11	60	24.37	84.67	89.00	61	85.38	9	正取9
4	5	10129065	李心怡	1	12	13	10	13	13	61	24.12	86.00	89.00	61.4	85.53	8	正取8

In [ ]:

Kuo's 3C 筆記

2020年11月17日星期二

Pandas 讀取excel考試檔，並清洗數據!!

Pandas 讀取excel考試檔，並清洗數據¶

以下資料姓名為亂數產生，考試資料也非真人實際資料

以上資料顯示，這個excel檔的欄位名稱其實延伸到第2列

先看一下欄位名稱

接下來要將0、2、4 ……等列篩選出來，另外將1、3、5 ……列篩選出來。

將df1和df2串接

調整欄位順序，將['社會', '自然', '總級分']放到「數學」後面

接下來將「審查資料面試」拆成「審查資料」和「面試」2個成績，再把「審查資料面試」刪除

【公告】網站遷移，未來內容將發表於新網站!!

追蹤者

標籤

2020年11月17日 星期二

Pandas 讀取excel考試檔，並清洗數據!!

Pandas 讀取excel考試檔，並清洗數據¶

以下資料姓名為亂數產生，考試資料也非真人實際資料

以上資料顯示，這個excel檔的欄位名稱其實延伸到第2列

先看一下欄位名稱

接下來要將0、2、4 ……等列篩選出來，另外將1、3、5 ……列篩選出來。

將df1和df2串接

調整欄位順序，將['社會', '自然', '總級分']放到「數學」後面

接下來將「審查資料面試」拆成「審查資料」和「面試」2個成績，再把「審查資料面試」刪除

【公告】網站遷移，未來內容將發表於新網站!!

2020年11月17日星期二