Python 報(bào)表自動(dòng)化/袁佳林

這篇文章是『讀者分享系列』第二篇，這一篇來自袁佳林同學(xué)，這是他在讀完我的書以后做的第一個(gè)Python報(bào)表自動(dòng)化項(xiàng)目，現(xiàn)在他把整體的思路以及實(shí)現(xiàn)代碼分享出來，希望對(duì)你有幫助。

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

1.案例場(chǎng)景

作為企業(yè)的數(shù)據(jù)統(tǒng)計(jì)崗，每天都需要做很多報(bào)表，日?qǐng)?bào)、周報(bào)、季報(bào)、月報(bào)。如果我們能利用Python的數(shù)據(jù)分析功能把這些常規(guī)的流程標(biāo)準(zhǔn)化的報(bào)表自動(dòng)化，那么我們將能有更多的時(shí)間集中于數(shù)據(jù)背后的業(yè)務(wù)發(fā)展及邏輯的分析上，這樣才能被稱為是企業(yè)的數(shù)據(jù)分析師，而不是簡(jiǎn)單的數(shù)據(jù)搬運(yùn)工。下面我們通過一個(gè)簡(jiǎn)單的案例來看看Python報(bào)表自動(dòng)化的建模過程。某銀行貸款業(yè)務(wù)部門數(shù)據(jù)分析員每天需要根據(jù)系統(tǒng)生成的個(gè)人貸款客戶信息表統(tǒng)計(jì)管轄區(qū)域內(nèi)各經(jīng)營單位的不同貸款產(chǎn)品今年的投放情況。源數(shù)據(jù)表格式及字段如下圖所示；

需要統(tǒng)計(jì)的數(shù)據(jù)報(bào)表如下圖所示：

2.Excel制作過程

結(jié)合以上兩張圖，我們知道利用Excel的數(shù)據(jù)透視表功能就制作該報(bào)表：選中數(shù)據(jù)表中任意一個(gè)單元格，點(diǎn)擊插入數(shù)據(jù)透視表，然后按以下步驟執(zhí)行：

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

• 將用途字段放在列區(qū)域。

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

當(dāng)處理到單位字段時(shí)我們會(huì)發(fā)現(xiàn)，表中每一筆貸款都有三家網(wǎng)點(diǎn)進(jìn)行業(yè)績分成。我們需要將分成比例也考慮進(jìn)去。所以透視表中的行區(qū)域及值區(qū)域不能簡(jiǎn)單的放入單位1和貸款金額。此時(shí)大部分人都會(huì)想到先在數(shù)據(jù)源表格中添加三列按分成比例分成以后的貸款金額。

三個(gè)數(shù)值的計(jì)算方法分別為：

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

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

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

然后將單位1及分成貸款金額1拖放到透視表的行區(qū)域及值區(qū)域。求出每個(gè)網(wǎng)點(diǎn)在分成金額1上的貸款投放，用同樣的方法將各網(wǎng)點(diǎn)在分成貸款金額2及3的和。于是就會(huì)得到結(jié)構(gòu)如下的三個(gè)數(shù)據(jù)透視表：

最后一步就是運(yùn)用VlOOKUP將同一家網(wǎng)點(diǎn)的同種貸款金額整合相加到日?qǐng)?bào)相對(duì)應(yīng)的單元格里，實(shí)現(xiàn)最后的報(bào)表輸出。

以上流程每天都需要進(jìn)行重復(fù)：插入列、編寫公式、做數(shù)據(jù)透視表、VLOOKUP，相信就算是熟悉Excel的人也需要華20到25分鐘，而在操作過程中很容易因?yàn)槭韬龆斐慑e(cuò)誤。如此循環(huán)往復(fù)，效率低下并且出錯(cuò)率高。而從操作上來講，整個(gè)流程都是標(biāo)準(zhǔn)化的，因此我們可以考慮使用Python進(jìn)行自動(dòng)化設(shè)計(jì)。

3.Python優(yōu)化報(bào)表制作過程

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

3.1加載數(shù)據(jù)表

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

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

• 養(yǎng)成數(shù)據(jù)加載以后，使用head()進(jìn)行預(yù)覽的習(xí)慣。

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

import pandas as pd
from datetime import datetime   # 因?yàn)楹竺嫘枰幚淼饺掌诤Y選，所以需要將datetime類從datetime模塊中加載進(jìn)來
data=pd.read_excel(r"E:\個(gè)人貸款客戶信息表.xlsx",usecols=[1,4,6,7,8,9,10,11,12])  # 假設(shè)個(gè)人貸款客戶信息表放在本地E盤
data.shape
---
(50585, 9)

以上為導(dǎo)入個(gè)人貸款信息表格代碼，由于個(gè)人貸款客戶信息表為工作簿第一張工作表，因此缺省sheet_name參數(shù)。

通過指定導(dǎo)入例的方法將與計(jì)算無關(guān)的“協(xié)議編號(hào)”，“貸款余額”，“固定利率”，“合同到期日”去除。

#查看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

接下來預(yù)覽一下data表的數(shù)據(jù)，默認(rèn)顯示前5行

data.head() 

3.2日期篩選

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

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

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

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

data.head() 

3.3數(shù)據(jù)表拆分

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

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

對(duì)data1表進(jìn)行預(yù)覽

data1.head()

對(duì)data2表進(jìn)行預(yù)覽

data2.head()

對(duì)data3表進(jìn)行預(yù)覽

data3.head()

3.4數(shù)據(jù)追加合并

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

3.4.1重命名列索引

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

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

預(yù)覽data3表

data3.head()

3.4.2縱向拼接分表

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

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

預(yù)覽合并后的表

data4.head()

3.5數(shù)據(jù)分組/透視

3.5.1空值處理

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

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

對(duì)空值進(jìn)行刪除

data4=data4.dropna() # 此處對(duì)不設(shè)置 how="all",因?yàn)榇藞?chǎng)景中只要出現(xiàn)空值，就將記錄刪除。從以下輸出結(jié)果可知存在空值的記錄已經(jīng)被刪除。

查看刪除后表的信息

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插入新列

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

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

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

對(duì)插入數(shù)據(jù)后的表進(jìn)行預(yù)覽

data4.head()

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

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

對(duì)插入數(shù)據(jù)后的表進(jìn)行預(yù)覽

data4.head()

3.5.3數(shù)據(jù)透視

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

data5=data4[["單位","用途","分成貸款金額"]] 
pd.pivot_table(data5,values="分成貸款金額",columns="用途",index="單位",aggfunc='sum').fillna(0).reset_index() #將無投放數(shù)據(jù)的地方填充為0