制作类似透视表
方法一:DataFrame.pivot_table(index='xx',columns='xx',values='xx',aggfunc=xx)
import pandas as pd
import numpy as np
pd.options.display.max_columns=999
orders=pd.read_excel('023/Orders.xlsx')
orders['Year']=pd.DatetimeIndex(orders['Date']).year
pt1=orders.pivot_table(index='Category',columns='Year',values='Total',aggfunc=np.sum)
#aggregation function用来聚合的函数
print(pt1)
结果:
Year 2011 2012 2013 2014
Category
Accessories 2.082077e+04 1.024398e+05 6.750247e+05 4.737876e+05
Bikes 1.194565e+07 2.898552e+07 3.626683e+07 1.745318e+07
Clothing 3.603148e+04 5.555877e+05 1.067690e+06 4.612336e+05
Components 6.391730e+05 3.880758e+06 5.612935e+06 1.669727e+06
方法二:
orders['Year']=pd.DatetimeIndex(orders['Date']).year
groups=orders.groupby(['Category','Year'])
s=groups['Total'].sum()
c=groups['ID'].count()
pt2=pd.DataFrame({'sum':s,'count':c})
print(pt2)
结果: