Python 報表自動化/袁佳林

這篇文章是『讀者分享系列』第二篇，這一篇來自袁佳林同學，這是他在讀完我的書以后做的第一個Python報表自動化項目，現在他把整體的思路以及實現代碼分享出來，希望對你有幫助。

你還可以看『讀者分享系列』的第一篇：Python中的這幾種報錯你遇到過嗎？

1.案例場景

作為企業的數據統計崗，每天都需要做很多報表，日報、周報、季報、月報。如果我們能利用Python的數據分析功能把這些常規的流程標準化的報表自動化，那么我們將能有更多的時間集中于數據背后的業務發展及邏輯的分析上，這樣才能被稱為是企業的數據分析師，而不是簡單的數據搬運工。下面我們通過一個簡單的案例來看看Python報表自動化的建模過程。某銀行貸款業務部門數據分析員每天需要根據系統生成的個人貸款客戶信息表統計管轄區域內各經營單位的不同貸款產品今年的投放情況。源數據表格式及字段如下圖所示；

需要統計的數據報表如下圖所示：

2.Excel制作過程

結合以上兩張圖，我們知道利用Excel的數據透視表功能就制作該報表：選中數據表中任意一個單元格，點擊插入數據透視表，然后按以下步驟執行：

• 將合同生效日字段放在頁區域（篩選今年）

• 將用途字段放在列區域。

• 將單位字段放在透視表的行區域。

當處理到單位字段時我們會發現，表中每一筆貸款都有三家網點進行業績分成。我們需要將分成比例也考慮進去。所以透視表中的行區域及值區域不能簡單的放入單位1和貸款金額。此時大部分人都會想到先在數據源表格中添加三列按分成比例分成以后的貸款金額。

三個數值的計算方法分別為：

分成貸款金額1=貸款金額*分成比例1

分成貸款金額2=貸款金額*分成比例2

分成貸款金額3=貸款金額*分成比例3

然后將單位1及分成貸款金額1拖放到透視表的行區域及值區域。求出每個網點在分成金額1上的貸款投放，用同樣的方法將各網點在分成貸款金額2及3的和。于是就會得到結構如下的三個數據透視表：

最后一步就是運用VlOOKUP將同一家網點的同種貸款金額整合相加到日報相對應的單元格里，實現最后的報表輸出。

以上流程每天都需要進行重復：插入列、編寫公式、做數據透視表、VLOOKUP，相信就算是熟悉Excel的人也需要華20到25分鐘，而在操作過程中很容易因為疏忽而造成錯誤。如此循環往復，效率低下并且出錯率高。而從操作上來講，整個流程都是標準化的，因此我們可以考慮使用Python進行自動化設計。

3.Python優化報表制作過程

通過以上分析，我們知道問題的難點在于處理分成比例。存在多個分成比例產生了很多重復性的工作。由于每筆貸款的三個分成比例都是對同一個貸款金額進行分成，我們可以將貸款金額分別與分成單位1、2、3及分成比例1、2、3組成三張分表，然后將分表縱向追加。這樣計算分成貸款金額時就只需要將新表的貸款金額及合并成一列的分成比例進行相乘。得出每個分成比例對于的分成貸款金額，最后將分成貸款金額按照單位及用途進行數據透視。

3.1加載數據表

數據加載過程比較簡單，使用read_excel()進行設置即可，這里不在贅述。僅提出以下建議，供大家參考，

• 利用read_excel()的usecols參數對表列進行指定，排除不必要的干擾列。

• 養成數據加載以后，使用head()進行預覽的習慣。

• 養成使用shape及info()了解表格的基本情況的習慣。

import?pandas?as?pd from?datetime?import?datetime???#?因為后面需要處理到日期篩選，所以需要將datetime類從datetime模塊中加載進來 data=pd.read_excel(r"E:\個人貸款客戶信息表.xlsx",usecols=[1,4,6,7,8,9,10,11,12])??#?假設個人貸款客戶信息表放在本地E盤 data.shape --- (50585,?9)

以上為導入個人貸款信息表格代碼，由于個人貸款客戶信息表為工作簿第一張工作表，因此缺省sheet_name參數。

通過指定導入例的方法將與計算無關的“協議編號”，“貸款余額”，“固定利率”，“合同到期日”去除。

#查看data表的基本信息 data.info() --- <class?'pandas.core.frame.DataFrame'> RangeIndex:?50585?entries,?0?to?50584 Data?columns?(total?9?columns): 貸款金額?????50585?non-null?int64 合同生效日????50585?non-null?datetime64[ns] 用途???????50585?non-null?object 單位1??????50585?non-null?object 分成比例1????50585?non-null?int64 單位2??????16418?non-null?object 分成比例2????16418?non-null?float64 單位3??????958?non-null?object 分成比例3????958?non-null?float64 dtypes:?datetime64[ns](1),?float64(2),?int64(2),?object(4) memory?usage:?3.5+?MB

接下來預覽一下data表的數據，默認顯示前5行

data.head()?

3.2日期篩選

個人貸款信息表包含該銀行所有的歷史數據，而我們每日的報表只需要統計當年的投放情況。所以計算投放金額前，我們需要將合同生效日期不符合要求的貸款記錄排除掉。這里我們通過判斷日期是否為2019年（大于2018-12-31）返回TRUE/FALSE進行選擇判斷。這種利用判斷條件來選擇數據的方式叫布爾索引。

這里解釋一下import datetime和from datetime import datetime的區別。datetime 是模塊，而datetime模塊里面還包含一個datetime類。通過from datetime import datetime能從datetime模塊直接導入datetime類。如果導入import datetime ，則在定義時間時，需要使用datetime.datetime()格式。

data=data[data["合同生效日"]>datetime(2018,12,31)] data.shape?#經過對日期的過濾，輸出了1673行，9列 --- (1673,?9)

對日期列進行觀察，可以看到合同生效日都是2019年的日期了。

data.head()?

3.3數據表拆分

下一步，我們需要處理分成比例問題了。此案例的重點也是在這里。按照 1.3節 Python優化報表制作過程中的分析，我們需要先將貸款金額分別與分成單位1、2、3及分成比例1、2、3組成三張分表。數據表的拆分代碼很簡單。直接用普通索引將需要的列傳導給分表就可以了。

data1=data[["用途","貸款金額","單位1","分成比例1"]] data2=data[["用途","貸款金額","單位2","分成比例2"]] data3=data[["用途","貸款金額","單位3","分成比例3"]]

對data1表進行預覽

data1.head()

對data2表進行預覽

data2.head()

對data3表進行預覽

data3.head()

3.4數據追加合并

接下來我們需求是將三個分離的表進行縱向的拼接。在我們的例子中，需要將三個表的單位及分成比例字段追加在同一列。但是目前三個新表中的單位及分成比例字段名字是不一致的，不能直接追加。所以我們需要先將分表的名字統一。

3.4.1重命名列索引

在Python中重命名，使用rename()函數。并使用鍵值對的方式對columns參數進行賦值。將各分表的單位字段統一命名為單位，分成比例字段統一命名為分成比例。

data1=data1.rename(columns={"單位1":"單位","分成比例1":"分成比例"}) data2=data2.rename(columns={"單位2":"單位","分成比例2":"分成比例"}) data3=data3.rename(columns={"單位3":"單位","分成比例3":"分成比例"})

預覽data3表

data3.head()

3.4.2縱向拼接分表

通過以上重命名操作，三個分表列名已經一致，這時我們可以將三個表格縱向追加起來。縱向追加使用concat()函數，并使用參數ignore_index重置行索引。

data4=pd.concat([data1,data2,data3],ignore_index=True)

預覽合并后的表

data4.head()

3.5數據分組/透視

3.5.1空值處理

此時利用info()返回的數據可以判斷data4是否存在空值。從以下運行結果來看，data4數據表格共5019行，貸款金額及貸款用途都含有5019行非空值，說明者兩列都沒有空值出現。而單位及分成比例只有2041行數據為非空。其他行為空值。根據業務邏輯可知，如果單位列數據為空，則一定不存在分成比例，即：分成比例也為空。那么該條記錄就是無效的。因此可以直接將其刪除。使用dropna()函數進行空值處理。

data4.info() --- <class?'pandas.core.frame.DataFrame'> RangeIndex:?5019?entries,?0?to?5018 Data?columns?(total?4?columns): 用途??????5019?non-null?object 貸款金額????5019?non-null?int64 單位??????2041?non-null?object 分成比例????2041?non-null?float64 dtypes:?float64(1),?int64(1),?object(2) memory?usage:?156.9+?KB

對空值進行刪除

data4=data4.dropna()?#?此處對不設置?how="all",因為此場景中只要出現空值，就將記錄刪除。從以下輸出結果可知存在空值的記錄已經被刪除。

查看刪除后表的信息

data4.info() --- <class?'pandas.core.frame.DataFrame'> Int64Index:?2041?entries,?0?to?3365 Data?columns?(total?4?columns): 用途??????2041?non-null?object 貸款金額????2041?non-null?int64 單位??????2041?non-null?object 分成比例????2041?non-null?float64 dtypes:?float64(1),?int64(1),?object(2) memory?usage:?79.7+?KB

3.5.2插入新列

接下來一步是計算分成貸款金額，即：我們需要插入一列，使其等于貸款金額列剩余分成比例。注意到分成比例并非百分比格式，我們需要將其轉化為百分比（除以100）。插入新列可以使用insert()函數，也可以直接以索引的方式進行。為了演示，我們分別選擇不同的方法插入百分比列及分成貸款金額列。

• 使用insert()插入百分比列

data4.insert(2,"分成百分比",data4["分成比例"]/100)

對插入數據后的表進行預覽

data4.head()

• 使用普通索引方式插入分成貸款金額列

data4["分成貸款金額"]=data4["貸款金額"]*data4["分成百分比"]/10000?#?除以10000，將結果單位換算為萬元

對插入數據后的表進行預覽

data4.head()

3.5.3數據透視

至此，數據清洗過程基本上已經完成了，接下來只需要對數據進行分組透視啦。這里還是遵循排除干擾的原則，先使用普通索引的方式提取需要用到的列，排除不必要的干擾。然后使用pivot_table()設置相關參數進行透視。

data5=data4[["單位","用途","分成貸款金額"]]? pd.pivot_table(data5,values="分成貸款金額",columns="用途",index="單位",aggfunc='sum').fillna(0).reset_index()?#將無投放數據的地方填充為0
至此，我們的任務就完成了。至于結果輸出部分，我們可以選擇直接復制黏貼到結果表上。當然也可以使用to_excel()將輸出結果保存為excel文件。甚至我們還可以導入xlrd模塊，直接對我們的日報表進行修改輸出。這里就不多做演示了，請讀者們自己動起手來。模型建立好以后，我們只需要將最新的個人貸款客戶信息表放置在E盤，覆蓋舊的數據文件。然后按下圖所示點擊 Run All 執行以上代碼就可以一鍵完成我們每天需要的日報了。

其實以上模型處理除了可以計算年累計投放數據以外，我們還可以通過修改日期篩選的范圍。一鍵統計每日、每周或者每季度的報表。

需要本篇數據集的可以去本書github里面隨書數據集里面下載。github地址：https://github.com/junhongzhang/Excel-Python-DA

總結

以上是生活随笔為你收集整理的Python报表自动化的全部內容，希望文章能夠幫你解決所遇到的問題。

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