【Python自动化Excel】pandas操作Excel的“分分合合”
话说Excel数据表,分久必合、合久必分。Excel数据表的“分”与“合”是日常办公中常见的操作。手动操作并不困难,但数据量大了之后,重复性操作往往会令人崩溃。利用Python的Pandas库,便可以自动实现Excel数据表的“分分合合”。下面结合实例来分享本人整理的实用代码片段。(如有更好的方式,欢迎批评指正)
创新互联公司专业为企业提供洪洞网站建设、洪洞做网站、洪洞网站设计、洪洞网站制作等企业网站建设、网页设计与制作、洪洞企业网站模板建站服务,10余年洪洞做网站经验,不只是建网站,更提供有价值的思路和整体网络服务。
分:纵向“分”
从数据平台(如问卷平台)中导出的数据往往是清单型的,每一行都是一条记录,数据量大的时候,表格往往是很“长”的。有时需要按照某列的不同数值,将一个总表“分”成单独的一些Excel文件。
一个工作表“分”为多个Excel文件
defto_excelByColName(sourceDf,colName,outPath,excelName):
'''
纵向“分”:一个工作表“分”为多个Excel文件
根据指定的列名中的不同值,分解Excel,并存储成多个Excel文件。
sourceDf:原始的DataFrame
colName:指定列名
outPath:输出路径
excelName:文件名,加.xlsx后缀
'''
colNameList=sourceDf[colName].drop_duplicates().tolist()
foreachColNameincolNameList:
sourceDf[sourceDf[colName]==eachColName].to_excel('/'.join([outPath,eachColName+excelName]),index=False)
例如:将20个班级1000名学生的总表,按班级分成20个Excel文件。
调用to_excelByColName
函数,效果如下:
to_excelByColName(sourceDf=sourceDf,colName="班级",outPath=".\分班数据表",excelName="生成数据表.xlsx")
一个工作表“分”为一个文件的多个sheet
defto_excelByColNameWithSheets(sourceDf,colName,outPath):
'''
纵向“分”:一个工作表“分”为一个文件的多个sheet
根据指定的列名中的不同值,分解Excel,并存储成单个Excel文件的多个Sheet。
sourceDf:原始的DataFrame
colName:指定列名
outPath:输出路径,加.xlsx后缀
'''
writer=pd.ExcelWriter(outPath)
colNameList=sourceDf[colName].drop_duplicates().tolist()
foreachColNameincolNameList:
sourceDf[sourceDf[colName]==eachColName].to_excel(writer,sheet_name=eachColName)
writer.save()
例如:将20个班级1000名学生的总表,按班级分成1个Excel文件的20个sheet表。
调用to_excelByColNameWithSheets
函数,效果如下:
to_excelByColNameWithSheets(sourceDf=sourceDf,colName="班级",outPath=".\分班数据表\生成数据表.xlsx")
分:横向“分”
在处理数据的时候,有时需要添加多个辅助列,这样也会让数据表越来越“宽”。而最终我们只需要某些关键列即可,那么这就涉及到横向数据分割,或者说提取某些列保持成一个单独的数据表。横向的分割只需要给DataFrame传入列名列表即可。
例如:只需要数据表中的姓名和班级字段,可以这样写。
df1=sourceDf[["姓名","班级"]]
df1.to_excel("只含有姓名和班级的数据表.xlsx")
合:纵向“合”
对于结构相同的数据,在数据处理时可以将其在纵向上拼接,方便一起处理。
多个Excel文件合并成一个工作表
defreadExcelFilesByNames(fpath,fileNameList=[],header=0):
'''
纵向“合”:多个Excel文件合并成一个工作表
读取路径下指定的Excel文件,并合并成一个总的DataFrame。
每个Excel文件的数据表格式上要一致。
1.fpath:必填,是Excel文件所在路径,不加文件名
2.fileNameList:需要读取的Excel文件名列表
3.header:指定读取的行数
'''
outdf=pd.DataFrame()
forfileNameinfileNameList:
tempdf=pd.read_excel('/'.join([fpath,fileName]),header=header)
outdf=pd.concat([outdf,tempdf])
returnoutdf
例如:将20个班级的Excel文件,合并成一个数据表
调用readExcelFilesByNames
函数,效果如下:
fileNameList=[
"六1班数据表.xlsx","六2班数据表.xlsx","六3班数据表.xlsx","六4班数据表.xlsx",
"六5班数据表.xlsx","六6班数据表.xlsx","六7班数据表.xlsx","六8班数据表.xlsx",
"六9班数据表.xlsx","六10班数据表.xlsx","六11班数据表.xlsx","六12班数据表.xlsx",
"六13班数据表.xlsx","六14班数据表.xlsx","六15班数据表.xlsx","六16班数据表.xlsx",
"六17班数据表.xlsx","六18班数据表.xlsx","六19班数据表.xlsx","六20班数据表.xlsx",
]
readExcelFilesByNames(fpath=".\分班数据表",fileNameList=fileNameList)
多个Sheet合并成一个工作表
defreadExcelBySheetsNames(fpath,header=0,prefixStr="",sheetNameStr="sheetName",prefixNumStr="prefixNum"):
'''
纵向“合”:多个Sheet合并成一个工作表
读取所有的Excel文件的sheet,并合并返回一个总的DataFrame。
每个sheet的数据表格式上要一致。
1.fpath:必填,是Excel文件的路径,加文件名
2.会生成两个新列:sheetName和prefixNum,方便数据处理
sheetName列是所有sheet的名称列
prefixNum列是计数列
3.header:指定读取的行数
'''
xl=pd.ExcelFile(fpath)
#获取Excel文件内的所有的sheet名称
sheetNameList=xl.sheet_names
outfd=pd.DataFrame()
num=0
forsheetNameinsheetNameList:
num+=1
data=xl.parse(sheetName,header=header)
#产生sheet名称列和计数列
data[sheetNameStr]=sheetName
data[prefixNumStr]=prefixStr+str(num)
#数据表拼接
outfd=pd.concat([outfd,data.dropna()])
xl.close()
returnoutfd
如下调用readExcelBySheetsNames
,运行效果如下:
readExcelBySheetsNames(fpath=".\分班数据表\总数据表.xlsx",sheetNameStr="sheet名",prefixNumStr="sheet序号")
合:横向“合”
对于不同Excel工作表之间的横向合并,主要是用根据某些列(如:姓名、身份证号等)进行合并。在pandas库中可以用merge
方法来实现,这是个十分好用的方式,展开讲篇幅较长,后续详细整理。
DataFrame.merge(right,how='inner',on=None,left_on=None,right_on=None,left_index=False,right_index=False,sort=False,suffixes=('_x','_y'),copy=True,indicator=False,validate=None)
结语
本文所谈的Python处理Excel文件方式主要是基于pandas
库的,主要针对的是清单型的数据表
。清单型的数据表在下面的文章中有详细介绍:
https://www.cnblogs.com/wansq/p/.html
数据表的分
主要涉及的是文件保存(写入),对程序来说属于输出
环节;
数据表的合
主要针对的是文件打开(读取),对程序而言属于输入
环节。
以上代码在针对大量重复性的表格分与合时,优势巨大;但对于偶尔、少量的分与合,也许用鼠标点击更快。
技术没有好坏之分,需要灵活使用!
分享标题:【Python自动化Excel】pandas操作Excel的“分分合合”
URL标题:http://scyanting.com/article/dsojoih.html