python中的pandas可以方便地生成透视表,读取原始数据。
>>> df = pd.DataFrame(pd.read_excel('测试数据.xlsx',sheet_name = 'Sheet1'))
>>> df
编号 客户 商品 金额 付款
0 100 张三 苹果 10 是
1 100 张三 香蕉 20 是
2 100 张三 葡萄 20 NaN
3 101 李四 雪梨 30 是
4 101 李四 龙眼 10 是
5 101 李四 苹果 50 NaN
6 101 李四 香蕉 30 NaN
7 102 王五 葡萄 30 是
8 102 王五 雪梨 10 NaN
如想统计每个客户有几个商品已经付款了,aggfunc可以取’count’值,如果是len值,则会把空值添加上,这里LEN统计下ALL的值为什么是5而不是9暂时未知原因。
>>> pd.pivot_table(df,index=["客户"],values = ["付款"],\
aggfunc= ['count',len],margins = True)
count len
付款 付款
客户
张三 2 3
李四 2 4
王五 1 2
All 5 5
使用下面的代码可以获得正确的结果,这里要注意空值的处理,对df要先进行df.fillna(0)处理,把空值都替换为0,否则np.count_nonzero会把所有不等于0的值进行计数,得到错误的结果。
>>> pd.pivot_table(df.fillna(0),index=["客户"],values = ["付款"],\
aggfunc= ['count',len,np.count_nonzero],margins = True)
count len count_nonzero
付款 付款 付款
客户
张三 3 3 2
李四 4 4 2
王五 2 2 1
All 9 9 5
完整代码如下:
import pandas as pd
import openpyxl
import numpy as np
df = pd.DataFrame(pd.read_excel('测试数据.xlsx',sheet_name = 'Sheet1'))
writer = pd.ExcelWriter('test.xlsx')
df2 = pd.pivot_table(df.fillna(0),index=["客户"],values = ["付款"],\
aggfunc= ['count',len,np.count_nonzero],margins = True)
df2.to_excel(writer,sheet_name = '汇总')
writer.save()
writer.close()