Pandas枢轴中的Subtotal和Grandtotal

Question

Data Currently Populated

Data Desired

使用的代码片段:-

df["Average"] = df["Average"].astype("category")

writer = pd.ExcelWriter('New.xlsx', engine='xlsxwriter')

table1 = pd.pivot_table(df,values=['Average'],index=['Manager','Cost'],aggfunc=[len],fill_value=0)

table2 = pd.pivot_table(df,values=['Average'], index=['Manager','Cost'],aggfunc=[np.sum],fill_value=0)

table = pd.concat((table1, table2), axis=1)

table.to_excel(writer, sheet_name='Sheet2')

table3 = pd.pivot_table(df,values=['Average'], index=['Project','Cost'],aggfunc=[len],fill_value=0)

table4 = pd.pivot_table(df,values=['Average'], index=['Project','Cost'],aggfunc=[np.sum],fill_value=0)

project_table = pd.concat((table3, table4), axis=1)

project_table.to_excel(writer, sheet_name='Sheet3')

请帮我实现预期的结果？

Answer 1

你需要通过sum创建新的DataFrame，通过MultiIndex.from_arrays为MultiIndex添加新的关卡，concat一起添加，sort_index和最后通过sum添加总计。

为了正确排序，Total已更改为A_total，B_total。

df = pd.DataFrame({'Manager':['A']*10+['B']*5,
                   'Cost':['a']*5+['c']*5+['b']*4+['a'],
                   'Average':range(15)})

print (df)
    Average Cost Manager
0         0    a       A
1         1    a       A
2         2    a       A
3         3    a       A
4         4    a       A
5         5    c       A
6         6    c       A
7         7    c       A
8         8    c       A
9         9    c       A
10       10    b       B
11       11    b       B
12       12    b       B
13       13    b       B
14       14    a       B

df1 = df.groupby(['Manager','Cost'])['Average'].agg(['size','sum'])
df2 = df1.sum(level=0)
df2.index = pd.MultiIndex.from_arrays([df2.index.get_level_values(0) + '_total', 
                                       len(df2.index) * ['']])

print (df2)
          size  sum
A_total     10   45
B_total      5   60

df = pd.concat([df1, df2]).sort_index()
df.loc[('Grandtotal', ''),:] = df1.sum()
print (df)
                 size    sum
Manager    Cost             
A          a      5.0   10.0
           c      5.0   35.0
A_total          10.0   45.0
B          a      1.0   14.0
           b      4.0   46.0
B_total           5.0   60.0
Grandtotal       15.0  105.0