如何利用Python库Pandas统一各Excel银行流水格式

        以下我介绍一下几个重要的函数以及案例，以便大家理解Pandas对EXCEL进行操作。

        建立从已知的两列建立新列

        假设我们有一个序时账，其格式如下：

序时账

科目代码	科目名称	金额
1010	现金	5000
2100	应付账款	-3000
5100	主营业务收入	-1500
6100	销售费用	500

        现在我们希望建立一个新列，将科目代码和科目名称合并到新列之中，方便我们检查科目汇总表，这时代码如下：

import pandas as pd
df = pd.read_excel('序时账.xlsx')
df['代码+科目'] = df['科目代码'].astype(str) + '_' + df['科目名称']
df

运行后，我们得出的表格如下：

序时账

科目代码	科目名称	金额	代码+科目
1010	现金	5000	1010_现金
2100	应付账款	-3000	2100_应付账款
5100	主营业务收入	-1500	5100_主营业务收入
6100	销售费用	500	6100_销售费用

        这时新列就建好了，如果想对新的表格再进行汇总分析之类，我们可以在后面加上sum()等的代码，按上面的原理再结合函数，我们可以进行更加复杂的表格处理。

        现在网上银行可以导出银行流水，大多数是EXCEL文件格式，核对这些流水是审计中必须要检查的事项，但每个银行的格式是不一样的，我们需要将这些不同的银行流水汇总到一张工作表中，并转化为统一的格式，如果按传统的方法，我们需要一个又一个地打开每个文件，然后通过复制将数据粘贴到一张表格上面。但如果我们掌握了Pandas，我们可以通过代码一次性地将这些不同的表格化为我们审计的格式，过程只需要几秒。然后我们就可以慢慢检查公司的银行流水。

        首先，为了方便读取文件，最好将这些银行流水的文件放到同一个文件夹中，然后在这个文件夹建立一个新的python文件写代码。

打开之后，就开始写代码了。具体代码如下：

import Pandas as pd
import numpy as np
np.set_printoptions(suppress=True) #为了直观的显示数字，不采用科学计数法
import warnings
warnings.filterwarnings('ignore') #不显示代码警告（警告不是报错）
#设置数据源文件路径
file = '广东中行 8791.xls' #此处为相对路径，可根据xlsx文件的实际路径更改此变
file2 = '广东农行4276.xls' 
file3 = '广东工行 8573.xlsx'
file4 = '广东广发 0156.xls' 
file5 = '广东建行1054.xls'
file14='广东农商行 7295.xls'
file6 = '湖北中行2986.xls' #此处为相对路径，可根据budget_data.xlsx文件的实际路径更改此变
file13 = '湖北工行9666.csv' 
file12='广东农行4659.xls'

df01 = pd.read_Excel(file,sheet_name=0,header = 8) #导入登记表，header=8就是从第八行开始读取
df01.rename(columns={'交易日期[ Transaction Date ]':'日期'},inplace=True) #将表头的名称改成统一的名称
df01.rename(columns={"付款人名称[ Payer's Name ]":'付款人'},inplace=True)
df01.rename(columns={"收款人名称[ Payee's Name ]":'收款人'},inplace=True)
df01.rename(columns={"交易附言[ Remark ]":'摘要'},inplace=True)
df01.rename(columns={"交易金额[ Trade Amount ]":'金额'},inplace=True)
df01=df01[["日期","付款人","收款人","摘要","金额"]]#选取需要的列，形成一个新表
df01['对方户名']=df01['付款人'].map(str)+"付"+df01['收款人'].map(str)##将两列合并变成一列
df01['日期']=pd.to_datetime(df01['日期'],format="%Y%m%d",errors='coerce')#日期格式转化，这个作为索引
df01 = df01.fillna(0)#将NAN值变成0
df11=df01.loc[df01['金额'] >= 0]##将金额大于零的数值提取出来。
df11.rename(columns={'金额':'收入金额'},inplace=True)#改名
df12=df01.loc[df01['金额'] < 0]##将金额小于零的数值提取出来
df12.rename(columns={'金额':'支出金额'},inplace=True)
df16 = pd.merge(df11, df12, on=['日期', '对方户名','摘要'], how='outer')#合并成一行
df16['银行']='广东中行 8791'##加上银行名称
df16['支出金额']=df16['支出金额']*-1##负数改成正数
df16 = df16.fillna(0)#将NAN值变成0
df16=df16[["日期","对方户名","摘要","支出金额","收入金额","银行"]]#重新得出一个新表
df02 = pd.read_Excel(file2,sheet_name=0,header = 1) #导入登记表，如果加上index_col=2，就是以二级分类作为索引
df02['日期']=pd.to_datetime(df02['会计日期'],format="%Y%m%d",errors='coerce')#日期格式转化
##df02['金额']=df02['收入金额']+df02['支出金额']*-1
df02.rename(columns={"交易用途":'摘要'},inplace=True)#改列名
df02=df02[["日期","对方户名","摘要","支出金额","收入金额"]]
df02 = df02.fillna(0)#将NAN值变成0
df02=df02.loc[df02['摘要'] != 0]##将摘要不等于零的提取出来
df02['银行']='广东农行4276'##增加一行银行列

df03 = pd.read_Excel(file3,header = 1)  #导入登记表，如果加上index_col=2，就是以二级分类作为索引 gb2312 gb18030
df03['入账日期']=df03['入账日期\t'].str.replace('\t','')
df03['日期']=pd.to_datetime(df03['入账日期'],format="%Y-%m-%d",errors='coerce')#日期格式转化
df03['对方户名']=df03['对方单位\t'].str.replace('\t','')
df03['收入金额']=df03['转入金额\t'].str.replace('\t','')
df03['支出金额']=df03['转出金额\t'].str.replace('\t','')
df03['摘要']=df03['摘要\t'].str.replace('\t','')
#df03 = df03.fillna(0)#将NAN值变成0
#df03[['收入金额', '支出金额']] = df03[['收入金额', '支出金额']].astype(float)
##df03['金额']=df03['收入金额']+df03['支出金额']*-1
df03['银行']='广东工行 8573'
df03=df03[["日期","对方户名","摘要","支出金额","收入金额","银行"]]

df04 = pd.read_Excel(file4,sheet_name=0,header = 7) #导入登记表，如果加上index_col=2，就是以二级分类作为索引
df04['日期']=pd.to_datetime(df04['交易时间'],format="%Y-%m-%d",errors='coerce')#日期格式转化
df04 = df04.fillna(0)#将NAN值变成0
df04['摘要'] = df04['摘要'].map(str)+ df04['附言'].map(str)##两列合并成一列
df04.rename(columns={"支出":'支出金额'},inplace=True)#改列名
df04.rename(columns={"收入":'收入金额'},inplace=True)#改列名
df04['银行']="广东广发 0156"
df04=df04[["日期","对方户名","摘要","支出金额","收入金额","银行"]]

df05 = pd.read_Excel(file5,sheet_name=0,header = 0)
df05['日期']=pd.to_datetime(df05['交易时间'],format="%Y-%m-%d",errors='coerce')#日期格式转化
df05.rename(columns={"借方发生额（支取）":'支出金额'},inplace=True)#改列名
df05.rename(columns={"贷方发生额（收入）":'收入金额'},inplace=True)#改列名
df05.rename(columns={"摘要":'摘要1'},inplace=True)#改列名
df05.rename(columns={"备注":'摘要'},inplace=True)
df05['银行']="广东建行1054"
df05=df05[["日期","对方户名","摘要","支出金额","收入金额","银行"]]

df06 = pd.read_Excel(file6,sheet_name=0,header = 8) #导入登记表，如果加上index_col=2，就是以二级分类作为索引
df06.rename(columns={'交易日期[ Transaction Date ]':'日期'},inplace=True)
df06.rename(columns={"付款人名称[ Payer's Name ]":'付款人'},inplace=True)
df06.rename(columns={"收款人名称[ Payee's Name ]":'收款人'},inplace=True)
df06.rename(columns={"交易附言[ Remark ]":'摘要'},inplace=True)
df06.rename(columns={"交易金额[ Trade Amount ]":'金额'},inplace=True)
df06=df06[["日期","付款人","收款人","摘要","金额"]]
df06['对方户名']=df06['付款人'].map(str)+"付"+df06['收款人'].map(str)#合并成一列，然后中间加上一个付字
df06['日期']=pd.to_datetime(df06['日期'],format="%Y%m%d",errors='coerce')#日期格式转化
df06 = df06.fillna(0)#将NAN值变成0
df116=df06.loc[df06['金额'] >= 0]
df116.rename(columns={'金额':'收入金额'},inplace=True)
df126=df06.loc[df06['金额'] < 0]
df126.rename(columns={'金额':'支出金额'},inplace=True)
df166 = pd.merge(df116, df126, on=['日期', '对方户名','摘要'], how='outer')
df166['银行']='湖北中行2986'
df166['支出金额']=df166['支出金额']*-1
df166 = df166.fillna(0)#将NAN值变成0
df166=df166[["日期","对方户名","摘要","支出金额","收入金额","银行"]]

df103 = pd.read_csv(file13,header = 1,encoding="gb18030")  #导入登记表，如果加上index_col=2，就是以二级分类作为索引 gb2312 gb18030
df103['入账日期']=df103['入账日期\t'].str.replace('\t','')
df103['日期']=pd.to_datetime(df103['入账日期'],format="%Y-%m-%d",errors='coerce')#日期格式转化
df103['对方户名']=df103['对方单位\t'].str.replace('\t','')
df103['收入金额']=df103['转入金额\t'].str.replace('\t','')
df103['支出金额']=df103['转出金额\t'].str.replace('\t','')
df103['摘要']=df103['摘要\t'].str.replace('\t','')
#df03 = df03.fillna(0)#将NAN值变成0
#df03[['收入金额', '支出金额']] = df03[['收入金额', '支出金额']].astype(float)
##df03['金额']=df03['收入金额']+df03['支出金额']*-1
df103['银行']='湖北工行9666'
df103=df103[["日期","对方户名","摘要","支出金额","收入金额","银行"]]

df102 = pd.read_Excel(file12,sheet_name=0,header = 1) #导入登记表，如果加上index_col=2，就是以二级分类作为索引
df102['日期']=pd.to_datetime(df102['会计日期'],format="%Y%m%d",errors='coerce')#日期格式转化
##df02['金额']=df02['收入金额']+df02['支出金额']*-1
df102.rename(columns={"交易用途":'摘要'},inplace=True)
df102=df102[["日期","对方户名","摘要","支出金额","收入金额"]]
df102 = df102.fillna(0)#将NAN值变成0
df102=df102.loc[df102['摘要'] != 0]
df102['银行']='广东农行4659'

df104 = pd.read_Excel(file14,sheet_name=0,header = 2) #导入登记表，如果加上index_col=2，就是以二级分类作为索引
df104['日期']=pd.to_datetime(df104['交易日期'],format="%Y%m%d",errors='coerce')#日期格式转化
df104.rename(columns={"支出":'支出金额'},inplace=True)
df104.rename(columns={"收入":'收入金额'},inplace=True)
df104.rename(columns={"对方名称":'对方户名'},inplace=True)#改列名 
df104 = df104.fillna(0)#将NAN值变成0
df104=df104[["日期","对方户名","摘要","支出金额","收入金额"]]
df104['银行']='广东农商行7295'

df20 = pd.concat([df16,df02,df03,df04,df05,df166,df103,df102,df104])
df20 = df20.sort_values(by='日期')
df20['绝对支出金额']=df20['支出金额']
df20['绝对收入金额']=df20['收入金额']
df20['绝对支出金额'].replace("-",'0')##将-改为0
df20['绝对收入金额'].replace("-",'0')

file7='重要各银行流水合并.xlsx'
with pd.ExcelWriter(file7, mode='a',engine='openpyxl') as writer:
    df20.to_Excel(writer,sheet_name='I202101',index=False)  #新开一工作表I01而保存文件

        以上是全部代码，为了方便初学者理解，没有用循环for，也没有用判断if等的结构，这样虽然代码非常长，但更直观，当然，高手们可以将代码压到更快更短。运行后会得出以下的结果：

        

        基本上工作就完成了，只需要将每个月的银行流水放到同一个文件夹，只要文件名正确，生成每个月的合并表格就是几秒钟的事。

        对很多做过这个工作的实习生来说，以上这个程序将减轻他们的工作量，毕竟如果交到电脑来操作，出错的机会更少。

        特别说明：为了循序渐进，代码没有使用FOR，IF等的循环结构，虽然代码较长，但更适合初学者理解。同时在这个例子当中，我用了很多Python和Pandas的函数，而熟悉这些函数是操作更复杂的数据清洗的基础。当然，我们又不是程序员，不用太过死记硬背，如果我想将某列的数据变化成我想要的格式，我会在网上百度一下该代码又或者函数是什么，然后再尝试操作一下，由于Python的语法比较友好，很多时候都能成功。

        由于我不是专业的程序员，我是站在财务的角度去解决问题，尽量贴近业务逻辑，写得不好不要见怪。