1.0: 關于excel數據透視表的數據填充

1.1: 前言

參加某某比賽時，遇到比較復雜的數據填充，當時由于時間限制，加之對excel數據透視表格式的不理解，在比賽中使用了非常笨的方式去填充，在之后寫比賽總結時，發現了可以更快速地進行填充，因此寫下此文章進行記錄。

2.0: 過程

2.1: 數據、任務介紹

該數據是使用excel聚合得到的數據透視表，我們可以看到其中有空缺值。我們需要根據他這一整年，四個季度中沒有空缺的值求和取平均，去填充空缺值。但是excel對于這種復雜的操作沒有可行的辦法，因此使用python，進行數據處理。

由于python無法直接導入excel的數據透視表，因此我們將該數據透視表中刪去無關列，得到最終的數據，再使用pd.read_clipboard()函數將其導入，刪去無關列后的數據如下:

2.2: 具體過程

2.2.1: 導入數據

frame = pd.read_clipboard(header= None) frame.head(5) # 輸出前五條數據0 1 2 3 4 5 6 ... 26 27 28 29 30 31 32 0 Algeria 1292.01 -21.80 NaN NaN 723.41 36.17 ... 13.51 876.49 -26.29 245.20 -4.90 651.66 -32.58 1 Angola 93.20 7.46 688.25 68.83 670.01 47.53 ... 41.41 398.18 -19.91 366.34 0.00 878.19 61.47 2 Benin 1282.29 88.48 2412.11 48.40 739.48 -7.39 ... 30.48 2432.26 -81.39 657.94 -38.64 830.33 -8.30 3 Botswana 660.12 0.00 953.86 95.39 874.66 78.72 ... -23.70 NaN NaN 1154.90 -24.69 NaN NaN 4 Burkina Faso 1101.36 30.66 NaN NaN 745.22 -29.81 ... -63.30 NaN NaN 642.20 -18.00 NaN NaN

這里需要注意的是read_clipboard使用了header= None，表示第一行不是表的列索引，他為我們默認的添加上了從0開始的列索引。

2.2.2: 創建多層索引

# 根據數據透視表，創建多層索引 col = pd.MultiIndex.from_arrays([['2017', '2017', '2017', '2017', '2017', '2017', '2017', '2017','2018', '2018', '2018', '2018', '2018', '2018','2018','2018','2019', '2019', '2019', '2019', '2019', '2019', '2019', '2019','2020', '2020', '2020', '2020', '2020', '2020', '2020', '2020'],['第一季', '第一季', '第二季', '第二季', '第三季', '第三季', '第四季', '第四季','第一季', '第一季', '第二季', '第二季', '第三季', '第三季', '第四季', '第四季','第一季', '第一季', '第二季', '第二季', '第三季', '第三季', '第四季', '第四季','第一季', '第一季', '第二季', '第二季', '第三季', '第三季', '第四季', '第四季'],['銷售', '利潤', '銷售', '利潤', '銷售', '利潤', '銷售', '利潤','銷售', '利潤', '銷售', '利潤', '銷售', '利潤', '銷售', '利潤','銷售', '利潤', '銷售', '利潤', '銷售', '利潤', '銷售', '利潤','銷售', '利潤', '銷售', '利潤', '銷售', '利潤', '銷售', '利潤',]]) # 將國家名稱那一列設置為索引，國家名稱對應的列索引為0，上面有介紹 frame.set_index(0, inplace= True) # inplace= True表示會影響原來的frame，默認是為False。# 設置列 frame.columns = col frame.columns# 這里只截取了部分的結果， 可以看到我們成功地為其設置了多層索引。 MultiIndex([('2017', '第一季', '銷售'),('2017', '第一季', '利潤'),('2017', '第二季', '銷售'),('2017', '第二季', '利潤'),('2017', '第三季', '銷售')

2.2.3: 填充數據

創建一個字典，為填充每一年的數據做準備。(這里本來可以使用字符串拼接的方式，去生成每一次填充的索引，但是考慮到閱讀效果，還是直接將所有的寫出來，方便快速理解。）

dic = {'2017':[('2017', '第一季', '銷售'),('2017', '第二季', '銷售'),('2017', '第三季', '銷售'),('2017', '第四季', '銷售')],'2018':[('2018', '第一季', '銷售'),('2018', '第二季', '銷售'),('2018', '第三季', '銷售'),('2018', '第四季', '銷售')],'2019':[('2019', '第一季', '銷售'),('2019', '第二季', '銷售'),('2019', '第三季', '銷售'),('2019', '第四季', '銷售')],'2020':[('2020', '第一季', '銷售'),('2020', '第二季', '銷售'),('2020', '第三季', '銷售'),('2020', '第四季', '銷售')]} for ls in dic.values():data = frame.loc[:, ls] # 提取出每一年的數據for index in data.index:data.loc[index, :] = data.loc[index, :].fillna(round(data.loc[index, :].mean(), 2))frame.loc[:, ls] = data

dic.values()，輸出的是字典的value，如下：

dic.values()dict_values([[('2017', '第一季', '銷售'), ('2017', '第二季', '銷售'), ('2017', '第三季', '銷售'), ('2017', '第四季', '銷售')], [('2018', '第一 季', '銷售'), ('2018', '第二季', '銷售'), ('2018', '第三季', '銷售'), ('2018', '第四季', '銷售')], [('2019', '第一季', '銷售'), ('2019', '第二季', '銷 售'), ('2019', '第三季', '銷售'), ('2019', '第四季', '銷售')], [('2020', '第一季', '銷售'), ('2020', '第二季', '銷售'), ('2020', '第三季', '銷售'), ('2 020', '第四季', '銷售')]])

利潤數據的填充，只需要將字典dic中的’銷售‘改為’利潤‘就行了，代碼是一樣的。執行完后，我們輸出結果，并使用to_excel()函數將其保存到本地，效果如下：

frame.to_excel('本地地址')

3.0: 用簡單的數據模擬填充的核心過程

3.1: 創建數據

import pandas as pd import numpy as np df = pd.DataFrame(np.arange(16).reshape(4, 4), columns= ['a', 'b', 'c', 'd]) df.loc[1, ['a', 'b']] = np.nan df.loc[2, ['a', 'c']] = np.nan dfa b c d 0 0.0 1.0 2.0 3 1 NaN NaN 6.0 7 2 NaN 9.0 NaN 11 3 12.0 13.0 14.0 15

3.2: 填充數據

3.2.1原理： 篩選出相應的列，然后for循環對每一行進行平均填充的操作，再將結果覆蓋回原來的數據。

3.2.2: 具體過程

for index in df.index:df.loc[index, :] = df.loc[index, :].fillna(round(df.loc[index, :].mean(), 1))dfa b c d 0 0.0 1.0 2.0 3.0 1 6.5 6.5 6.0 7.0 2 10.0 9.0 10.0 11.0 3 12.0 13.0 14.0 15.0

3.2.3: 代碼解釋

• df.index: 輸出一個由df索引組成的數組。
• 第一個df.loc[index, :]代表著覆蓋源數據的值。
• 第三個df.loc[index, :].mean()是求第index行的平均值，用來填充。
• fillna()是用來填充空值。
• round()函數的使用是為了確保某些無限小數，導致整個數據小數點有很多位。
• 整條代碼的意思是：用第index行數據的平均值，去填充該行空缺的值，然 后再覆蓋原來的值。

3.2.4 關于loc函數

我們知道經過loc提取出來的數據，對它進行操作是不會影響到原來的數據的。比如我們用使用loc提取出df的所有列，然后對其進行乘2。

df.loc[:, :] * 2 dfa b c d 0 0 1 2 3 1 4 5 6 7 2 8 9 10 11 3 12 13 14 15

可以看到他是不會改變的。但是如果對他進行下面的操作，原數據就會改變

df.loc[:, :] = df.loc[:, :] * 2 dfa b c d 0 0 2 4 6 1 8 10 12 14 2 16 18 20 22 3 24 26 28 30

總結

以上是生活随笔為你收集整理的关于excel数据透视表的数据填充的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。