Pandas 讀取excel繳費檔，清洗數據並整合欄位名稱!!

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

In [1]:

import pandas as pd
import numpy as np

Location1 = '範例temp.xls'

df1 = pd.read_excel(Location1, header = [0,1])
# 資料讀出來後，欄名其實是前二列，但以multiindex的方式呈現

df1.head()

Out[1]:

	Unnamed: 0_level_0	Unnamed: 1_level_0	Unnamed: 2_level_0	Unnamed: 3_level_0	Unnamed: 4_level_0	Unnamed: 5_level_0	Unnamed: 6_level_0	Unnamed: 7_level_0	Unnamed: 8_level_0	Unnamed: 9_level_0	...	住宿保證金			學生團體保險費	僑生保險費	外籍生保險費		全民健康保險
	Unnamed: 0_level_1	學制	學制	系所	系所	系所	學號	學號	學號	姓名	...	Unnamed: 33_level_1	Unnamed: 34_level_1	Unnamed: 35_level_1	Unnamed: 36_level_1	Unnamed: 37_level_1	Unnamed: 38_level_1	Unnamed: 39_level_1	Unnamed: 40_level_1	Unnamed: 41_level_1	繳費日期
0	NaN	大學部	NaN	動動漫系	NaN	NaN	A1057116	NaN	NaN	朱俊宇	...	0.0	NaN	NaN	555.0	0.0	0.0	NaN	0.0	NaN	1100203.0
1	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
2	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
3	NaN	大學部	NaN	動動漫系	NaN	NaN	A1057116	NaN	NaN	王莉婷	...	0.0	NaN	NaN	0.0	0.0	0.0	NaN	0.0	NaN	1100323.0
4	NaN	大學部	NaN	動動漫系	NaN	NaN	A1057133	NaN	NaN	張雅娥	...	0.0	NaN	NaN	555.0	0.0	0.0	NaN	0.0	NaN	1100220.0

5 rows × 43 columns

In [2]:

# 再重新讀取一次，將欄位名稱當成資料
df1 = pd.read_excel(Location1, header = None)
df1.head()

Out[2]:

	0	1	2	3	4	5	6	7	8	9	...	33	34	35	36	37	38	39	40	41	42
0	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	...	住宿保證金	NaN	NaN	學生團體保險費	僑生保險費	外籍生保險費	NaN	全民健康保險	NaN	NaN
1	NaN	學制	NaN	系所	NaN	NaN	學號	NaN	NaN	姓名	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	繳費日期
2	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
3	NaN	大學部	NaN	動動漫系	NaN	NaN	A1057116	NaN	NaN	朱俊宇	...	0	NaN	NaN	555	0	0	NaN	0	NaN	1100203
4	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN

5 rows × 43 columns

In [3]:

# 取出前二列，準備處理欄位名稱
df1_title = df1.iloc[:2,:]
df1_title

Out[3]:

	0	1	2	3	4	5	6	7	8	9	...	33	34	35	36	37	38	39	40	41	42
0	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	...	住宿保證金	NaN	NaN	學生團體保險費	僑生保險費	外籍生保險費	NaN	全民健康保險	NaN	NaN
1	NaN	學制	NaN	系所	NaN	NaN	學號	NaN	NaN	姓名	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	繳費日期

2 rows × 43 columns

In [4]:

# 將欄位名稱全部是NaN的資料刪除
df1_title = df1_title.dropna(axis = 1, how = 'all')
df1_title

Out[4]:

	1	3	6	9	11	13	15	18	20	22	...	29	30	31	32	33	36	37	38	40	42
0	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	...	NaN	NaN	NaN	上學期住宿電費	住宿保證金	學生團體保險費	僑生保險費	外籍生保險費	全民健康保險	NaN
1	學制	系所	學號	姓名	虛擬帳號	學費	雜費	學雜費合計	學雜費基數	學分費	...	住宿費	宿網費	基本電費	NaN	NaN	NaN	NaN	NaN	NaN	繳費日期

2 rows × 23 columns

In [5]:

# 將第1列欄位名稱是NaN的以第0列的欄位名稱取代
df1_title = df1_title.fillna(axis = 0, method = 'ffill')
df1_title

Out[5]:

	1	3	6	9	11	13	15	18	20	22	...	29	30	31	32	33	36	37	38	40	42
0	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	...	NaN	NaN	NaN	上學期住宿電費	住宿保證金	學生團體保險費	僑生保險費	外籍生保險費	全民健康保險	NaN
1	學制	系所	學號	姓名	虛擬帳號	學費	雜費	學雜費合計	學雜費基數	學分費	...	住宿費	宿網費	基本電費	上學期住宿電費	住宿保證金	學生團體保險費	僑生保險費	外籍生保險費	全民健康保險	繳費日期

2 rows × 23 columns

In [6]:

df1_title = df1_title.iloc[1].reset_index(drop = True)
df1_title

Out[6]:

0             學制
1             系所
2             學號
3             姓名
4           虛擬帳號
5             學費
6             雜費
7          學雜費合計
8          學雜費基數
9            學分費
10    電腦與網路通訊使用費
11       語言設備使用費
12       外語設備實習費
13           住宿費
14           宿網費
15          基本電費
16       上學期住宿電費
17         住宿保證金
18       學生團體保險費
19         僑生保險費
20        外籍生保險費
21        全民健康保險
22          繳費日期
Name: 1, dtype: object

In [7]:

df1 = df1.iloc[2:, :]
df1 = df1.dropna(axis = 1, how = 'all')
df1.head()

Out[7]:

	1	3	6	9	11	13	15	18	20	22	...	29	30	31	32	33	36	37	38	40	42
2	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
3	大學部	動動漫系	A1057116	朱俊宇	98765436225867	0	0	0	0	0	...	0	0	0	0	0	555	0	0	0	1100203
4	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
5	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
6	大學部	動動漫系	A1057116	王莉婷	98765436260018	0	4260	0	0	6789	...	0	0	0	0	0	0	0	0	0	1100323

5 rows × 23 columns

In [8]:

df1 = df1.dropna(axis = 0, how = 'all')
df1.head()

Out[8]:

	1	3	6	9	11	13	15	18	20	22	...	29	30	31	32	33	36	37	38	40	42
3	大學部	動動漫系	A1057116	朱俊宇	98765436225867	0	0	0	0	0	...	0	0	0	0	0	555	0	0	0	1100203
6	大學部	動動漫系	A1057116	王莉婷	98765436260018	0	4260	0	0	6789	...	0	0	0	0	0	0	0	0	0	1100323
7	大學部	動動漫系	A1057133	張雅娥	98765436225883	0	0	0	0	0	...	0	0	0	0	0	555	0	0	0	1100220
8	大學部	動動漫系	A1057133	蔡依桂	98765436260024	12345	6789	0	0	0	...	0	0	0	0	0	0	0	0	0	1100325
9	大學部	動動漫系	A1057140	陳佳玲	98765436225895	0	0	0	0	0	...	12345	400	0	0	5000	555	0	0	0	1100219

5 rows × 23 columns

In [9]:

df1.columns = list(df1_title)
df1 = df1.reset_index(drop = True)
df1.head()

Out[9]:

	學制	系所	學號	姓名	虛擬帳號	學費	雜費	學雜費合計	學雜費基數	學分費	...	住宿費	宿網費	基本電費	上學期住宿電費	住宿保證金	學生團體保險費	僑生保險費	外籍生保險費	全民健康保險	繳費日期
0	大學部	動動漫系	A1057116	朱俊宇	98765436225867	0	0	0	0	0	...	0	0	0	0	0	555	0	0	0	1100203
1	大學部	動動漫系	A1057116	王莉婷	98765436260018	0	4260	0	0	6789	...	0	0	0	0	0	0	0	0	0	1100323
2	大學部	動動漫系	A1057133	張雅娥	98765436225883	0	0	0	0	0	...	0	0	0	0	0	555	0	0	0	1100220
3	大學部	動動漫系	A1057133	蔡依桂	98765436260024	12345	6789	0	0	0	...	0	0	0	0	0	0	0	0	0	1100325
4	大學部	動動漫系	A1057140	陳佳玲	98765436225895	0	0	0	0	0	...	12345	400	0	0	5000	555	0	0	0	1100219

5 rows × 23 columns

補充資料：

如讀入檔案，欄位名稱有Unnamed，刪除方式如下：

df1 = df1.loc[:, ~df1.columns.str.contains('^Unnamed')] df1.head()

In [ ]: