Pii January 2017

How to add sub-margins for each starting index in a pandas pivot table in Python?

Consider the code:

import pandas as pd 

df = pd.DataFrame({'Book': ['B1', 'B1', 'B2', 'B3', 'B3', 'B3'], 
                   'Trader': ['T1', 'T2', 'T2', 'T1', 'T3', 'T2'], 
                   'Position':[10, 33, -34, 87, 43, 99]})
df = df[['Book', 'Trader', 'Position']]

pt = pd.pivot_table(df, index=['Book', 'Trader'], values=['Position'], aggfunc=np.sum, margins=True)

The output pivot table is as follows: Table 1

However, I would like to generate this table instead: Table 2

Notice how there is an additional row for each 'Book' which sums the positions for every trader in that book. How can I generate 'Table 2' instead of 'Table 1'.

I've tried to use the advice in this question: Pandas Pivot tables row subtotals

However, the stack() function does not work for me here.

Answers


jezrael January 2017

You can use:

df1 = df.groupby(['Book','Trader']).Position.sum().unstack()
df1['All'] = df1.sum(1)
print (df1.stack())
Book  Trader
B1    T1         10.0
      T2         33.0
      All        43.0
B2    T2        -34.0
      All       -34.0
B3    T1         87.0
      T2         99.0
      T3         43.0
      All       22

Post Status

Asked in January 2017
Viewed 3,346 times
Voted 5
Answered 1 times

Search




Leave an answer