Python 帮同事用pandas快速筛选Excel文件

简介: Python 帮同事用pandas快速筛选Excel文件

同事正在为怎样处理一个18万行的全年财务Excel文件发愁,文件足足有30M,打开文件也要两三分钟,于是他就向我求助。大概意思就是要筛选出Data工作簿“源数据”Sheet中所有收款人对应的付款人及付款笔数、金额小计,于是我简化做了一个表格,只有4个涉及到的列,随机数据200行用于测试。其实我也没好好学过pandas库的用法,只能边写代码边查库函数方法的帮助。


读者想测试的话把以下表格建一个Data.xlsx文件,放入工作表sheet改名为“源数据”:


image.pngimage.pngimage.pngimage.pngimage.pngimage.pngimage.pngimage.pngimage.pngimage.pngimage.pngimage.pngimage.png



程序执行后,输出的output.xlsx文件格式:

image.pngimage.pngimage.png

image.pngimage.pngimage.pngimage.pngimage.pngimage.pngimage.png



源代码20行(其中有6行是测试用时的代码可以去除)如下:

import time, pandas as pd
def tprint(info): print(info); return time.time()
t1 = tprint('正在读取数据,请稍候...')
df = pd.read_excel('data.xlsx', sheet_name='源数据')
t2 = tprint('正在数据计算,请稍候...')
ls, label = [], ('付款人','收款人','摘要','金额','笔数','总笔数','全部摘要')
for 收款人 in df.收款人.unique():
    付款人 = df[df.收款人==收款人].付款人
    总笔数 = 付款人.shape[0]
    for 付款人 in 付款人.unique():
        数集 = df[(df.收款人==收款人) & (df.付款人==付款人)].fillna('空值')
        笔数, 金额 = 数集.shape[0], 数集.金额.sum().round(2)
        摘要,全部摘要 = 数集.摘要.iloc()[0], "、".join(数集.摘要.unique())
        ls.append('~'.join(map(str,(eval(_) for _ in label))).split("~"))
data = pd.DataFrame(ls, columns=label).sort_values(by='收款人')
data.金额, data.笔数, data.总笔数 = data.金额.astype('float'),data.笔数.astype('int'),data.总笔数.astype('int')
t3 = tprint('正在写入文件,请稍候...')
data.to_excel('output1.xlsx', sheet_name='Sheet1',index=False)
t4 = time.time()
print(f'读取耗时:{t2-t1:.3f}s\n计算耗时:{t3-t2:.3f}s\n写入耗时:{t4-t3:.3f}s\n共计耗时:{t4-t1:.3f}s')



测试效果:


   正在读取数据,请稍候...

   正在数据计算,请稍候...

   正在写入文件,请稍候...

   读取耗时:0.859s

   计算耗时:0.314s

   写入耗时:0.063s

   共计耗时:1.236s


处理那个30M的文件时,因为列名一样,都不用改代码。实测读取用了58秒,计算用1600秒,共计用时28分钟左右,完美完成筛选任务。


边学边写的代码,可能就根本没用到pandas的精华所在。但也谈谈代码中几个注意点:


1. python 支持中文名变量,直接用列名作变量有好处,省得注释以及想很多变量名称来命名。


2. DataFrame筛选条件的逻辑运算用 & | ,其运算级别高于“大于”“小于”“等于”等,所以括号不能省。如代码中的: df[(df.收款人==收款人) & (df.付款人==付款人)]


3. 取DataFrame的列,可以用 df['收款人'] 也能用 df.收款人,我觉得用后者更方便点。


4. 源数据“摘要”列中有空单元格,读入DataFrame中后其值为nan,这个起先没注意到代码一直报错。后查询到df有.fillna()方法可以替换掉空值。


5. 求行数可以直接用len(df)求,也可用df.shape()方法返回一个元组:(行数、列数)。


6. 行数据我用了一句代码 '~'.join(map(str,(eval(_) for _ in label))).split("~") 合并后分割成列表。意思是从元组中取数eval转成变量,再把当层循环的变量值转字符型合并最后分割,字符“~”任意取其它字符来替代,但不能在数据中出现过否则分割列数不正常。


7. 数值类型的列都要用.astype()转回相应的数值类型,否则得到的.xlsx文件中数值列都是文本型数字(Excel中表现为单元格左上角有绿色小三角),不便于统计合计数。如:

data.金额 = data.金额.astype('float')
   data.笔数 = data.笔数.astype('int')



8. df.unique()是去重方法,相当于用set()给列表去重;df.sort_values(by='',acending=True)是排序方法,升序或降序scending=True表示升序,ascending=False表示降序,默认为升序。


(本文完)


附pandas学习图一张:

c5b4ec8085ff4e11a7929de35d1852bf.png





目录
相关文章
|
18天前
|
数据处理 Python
Python实用记录(十):获取excel数据并通过列表的形式保存为txt文档、xlsx文档、csv文档
这篇文章介绍了如何使用Python读取Excel文件中的数据,处理后将其保存为txt、xlsx和csv格式的文件。
37 3
Python实用记录(十):获取excel数据并通过列表的形式保存为txt文档、xlsx文档、csv文档
|
3天前
|
数据挖掘 数据处理
Excel筛选技巧
【10月更文挑战第23天】Excel筛选技巧
15 7
|
1天前
|
存储 Java API
Java实现导出多个excel表打包到zip文件中,供客户端另存为窗口下载
Java实现导出多个excel表打包到zip文件中,供客户端另存为窗口下载
14 4
|
5天前
|
JavaScript 前端开发 数据处理
Vue导出el-table表格为Excel文件的两种方式
Vue导出el-table表格为Excel文件的两种方式
|
3天前
|
数据管理
excel筛选
【10月更文挑战第23天】excel筛选
14 3
|
8天前
|
Python
python读写操作excel日志
主要是读写操作,创建表格
19 2
|
21天前
|
easyexcel Java UED
SpringBoot中大量数据导出方案:使用EasyExcel并行导出多个excel文件并压缩zip后下载
在SpringBoot环境中,为了优化大量数据的Excel导出体验,可采用异步方式处理。具体做法是将数据拆分后利用`CompletableFuture`与`ThreadPoolTaskExecutor`并行导出,并使用EasyExcel生成多个Excel文件,最终将其压缩成ZIP文件供下载。此方案提升了导出效率,改善了用户体验。代码示例展示了如何实现这一过程,包括多线程处理、模板导出及资源清理等关键步骤。
|
25天前
|
JSON 数据格式
LangChain-20 Document Loader 文件加载 加载MD DOCX EXCEL PPT PDF HTML JSON 等多种文件格式 后续可通过FAISS向量化 增强检索
LangChain-20 Document Loader 文件加载 加载MD DOCX EXCEL PPT PDF HTML JSON 等多种文件格式 后续可通过FAISS向量化 增强检索
48 2
|
26天前
|
IDE 开发工具 数据安全/隐私保护
Python编程--实现用户注册信息写入excel文件
Python编程--实现用户注册信息写入excel文件
16 1
|
14天前
|
前端开发 JavaScript API
前端基于XLSX实现数据导出到Excel表格,以及提示“文件已经被损坏,无法打开”的解决方法
前端基于XLSX实现数据导出到Excel表格,以及提示“文件已经被损坏,无法打开”的解决方法
77 0