Pandas数据透视表中的小计

Question

我喜欢按组进行小计，这样就不会影响到总分

代码是

import pandas as pd
import numpy as np
dict_data = [{'duration': 0.7, 'project_id': 3, 'resource': u'Arya Stark', 'activity': u'Development'},
{'duration': 0.9, 'project_id': 4, 'resource': u'Ned Stark', 'activity': u'Development'},
{'duration': 2.88, 'project_id': 7, 'resource': u'Robb Stark', 'activity': u'Development'},
{'duration': 0.22, 'project_id': 9, 'resource': u'Jon Snow', 'activity': u'Support'},
{'duration': 0.3, 'project_id': 9, 'resource': u'Jon Snow', 'activity': u'Support'},
{'duration': 2.15, 'project_id': 3, 'resource': u'Arya Stark', 'activity': u'Practise'},
{'duration': 3.35, 'project_id': 4, 'resource': u'Sansa Stark', 'activity': u'Development'},
{'duration': 2.17, 'project_id': 9, 'resource': u'Rickon Stark', 'activity': u'Development'},
{'duration': 1.03, 'project_id': 4, 'resource': u'Benjan Stark', 'activity': u'Design'},
{'duration': 1.77, 'project_id': 4, 'resource': u'Bran Stark', 'activity': u'Testing'},
{'duration': 1.17, 'project_id': 4, 'resource': u'Ned Stark', 'activity': u'Development'},
{'duration': 0.17, 'project_id': 9, 'resource': u'Jon Snow', 'activity': u'Support'},
{'duration': 1.77, 'project_id': 3, 'resource': u'catelyn stark', 'activity': u'Development'},
{'duration': 0.3, 'project_id': 9, 'resource': u'Jon Snow', 'activity': u'Support'},
{'duration': 0.45, 'project_id': 9, 'resource': u'Jon Snow', 'activity': u'Support'}]
df = pd.DataFrame(dict_data)
pvt = pd.pivot_table(df, values=['duration'],index=['project_id','resource'], columns=['activity'], aggfunc=np.sum,margins=True, fill_value=0)

因此，我期望输出如下所示：

问题是我无法将小计行追加到表

Answer 1

通过^{}重塑以创建新的最后一行subtotal，但需要过滤掉所有All列。然后是^{}、^{}和{a4}。列已排序，因此按子集将All列移到最后一个位置：

pvt = pvt.unstack(0)
mask = pvt.columns.get_level_values('project_id') != 'All'
#print (mask)
pvt.loc['subtotal'] = pvt.loc[:, mask].sum()
pvt = pvt.stack().swaplevel(0,1).sort_index()
pvt = pvt[pvt.columns[1:].tolist() + pvt.columns[:1].tolist()]
print (pvt)
                         duration                                            
activity                   Design Development Practise Support Testing    All
project_id resource                                                          
3          Arya Stark        0.00        0.70     2.15    0.00    0.00   2.85
           catelyn stark     0.00        1.77     0.00    0.00    0.00   1.77
           subtotal          0.00        2.47     2.15    0.00    0.00   4.62
4          Benjan Stark      1.03        0.00     0.00    0.00    0.00   1.03
           Bran Stark        0.00        0.00     0.00    0.00    1.77   1.77
           Ned Stark         0.00        2.07     0.00    0.00    0.00   2.07
           Sansa Stark       0.00        3.35     0.00    0.00    0.00   3.35
           subtotal          1.03        5.42     0.00    0.00    1.77   8.22
7          Robb Stark        0.00        2.88     0.00    0.00    0.00   2.88
           subtotal          0.00        2.88     0.00    0.00    0.00   2.88
9          Jon Snow          0.00        0.00     0.00    1.44    0.00   1.44
           Rickon Stark      0.00        2.17     0.00    0.00    0.00   2.17
           subtotal          0.00        2.17     0.00    1.44    0.00   3.61
All                          1.03       12.94     2.15    1.44    1.77  19.33