同事正在为怎样处理一个18万行的全年财务Excel文件发愁,文件足足有30M,打开文件也要两三分钟,于是他就向我求助。大概意思就是要筛选出Data工作簿“源数据”Sheet中所有收款人对应的付款人及付款笔数、金额小计,于是我简化做了一个表格,只有4个涉及到的列,随机数据200行用于测试。其实我也没好好学过pandas库的用法,只能边写代码边查库函数方法的帮助。
读者想测试的话把以下表格建一个Data.xlsx文件,放入工作表sheet改名为“源数据”:
程序执行后,输出的output.xlsx文件格式:
源代码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学习图一张: