在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)

所以我期望的输出如下：

问题是我无法将小计行附加到表格中。

- Vigneshwaran Thenraj

---

你想要小计仅在第一组中还是对于MultiIndex的第一级别中的每个组都有？ - jezrael

每个分组按第一级 - Vigneshwaran Thenraj

但是我可以在单独的表格中进行求和，但是我无法将其附加到带有小计的源表格中 @jezrael - Vigneshwaran Thenraj

Answer 1

使用unstack进行重塑，创建新的最后一行subtotal并使用sum进行计算，但需要过滤掉所有All列。然后进行stack、swaplevel和sort_index操作。对列进行排序，通过子集获取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

- jezrael

Answer 2

如果您有兴趣，我刚刚创建了一个小函数，以使应用这个代码“subtotal”到许多表格更加容易。它适用于通过pivot_table()和groupby()创建的表格。

def get_subtotal(table, sub_total='subtotal', get_total=False, total='TOTAL'):
    """
    Parameters
    ----------
    table : dataframe, table with multi-index resulting from pd.pivot_table() or 
    df.groupby().
    sub_total : str, optional
        Name given to the subtotal. The default is '_Sous-total'.
    get_total : boolean, optional
        Precise if you want to add the final total (in case you used groupeby()). 
        The default is False.
    total : str, optional
        Name given to the total. The default is 'TOTAL'.

    Returns
    -------
    A table with the total and subtotal added.
    """
    index_name1 = table.index.names[0]
    index_name2 = table.index.names[1]
    
    pvt = table.unstack(0)
    mask = pvt.columns.get_level_values(index_name1) != 'All'
    #print (mask)
    pvt.loc[sub_total] = pvt.loc[:, mask].sum()
    
    pvt = pvt.stack().swaplevel(0,1).sort_index()
    pvt = pvt[pvt.columns[1:].tolist() + pvt.columns[:1].tolist()]
    
    if get_total:
        mask = pvt.index.get_level_values(index_name2) != sub_total
        pvt.loc[(total, '' ),: ] = pvt.loc[mask].sum()
    print (pvt)
    return(pvt)

- Tomtomtom