Keeping index section (different columns) when subtotal row in pivot table (pandas) is used

Question

I am trying to add subtotal rows in a pivot table (using pandas pd.pivot_table). This is the code table = pd.pivot_table(df, values= ['Quantity', 'Money', 'Cost'], index=['house','date', 'currency', 'family name'], columns=[], fill_value=0, aggfunc=np.sum). This is the corresponding output (exported to excel):

Then, I tried to get a subtotal row using house as a reference. I followed the steps stated in this link Pivot table subtotals in Pandas so I created a group using tablesum = table.groupby(level='house').sum(). everything seemed to be fine until I tried to concatenate table and tablesum dataframes. This is what I got (for family A):

Basically, I obtained the four categories stated in table´s index (house, date, currency, family name) in just one column (separate by commas). So, even when I get the subtotal by house, I lost the pivot_table separation. So, my question is: how can I keep it (mantaining pivot_table´s index in different columns)?

Any help would be highly apprecited it.

Regards,

pd: I also checked this link Sub Total in pandas pivot Table but this gave me another type of error related to strings and numbers.

jezrael jezrael · Accepted Answer · 2017-12-21T12:57:39

You can create custom MultiIndex with 4 levels and then assign.

Notice: Second level date has to be converted to strings, because concat with strings too, else get:

TypeError: Cannot compare type 'Timestamp' with type 'str'

df = pd.DataFrame({'house':list('aaaaabbbbb'),
                   'date':['2015-01-01'] * 3 + ['2015-01-02'] * 2 + 
                          ['2015-01-01'] * 3 +['2015-01-02'] * 2,
                   'currency':['USD'] * 3 + ['NK'] * 2 + ['USD'] * 3 +['NK'] * 2,
                   'Quantity':[1,3,5,7,1,0,7,2,3,9],
                   'Money':[5,3,6,9,2,4,7,2,3,9],
                   'Cost':[5,3,6,9,2,4,7,2,3,9],
                   'family name':list('aabbccaabb')})

print (df)
   Cost  Money  Quantity currency        date family name house
0     5      5         1      USD  2015-01-01           a     a
1     3      3         3      USD  2015-01-01           a     a
2     6      6         5      USD  2015-01-01           b     a
3     9      9         7       NK  2015-01-02           b     a
4     2      2         1       NK  2015-01-02           c     a
5     4      4         0      USD  2015-01-01           c     b
6     7      7         7      USD  2015-01-01           a     b
7     2      2         2      USD  2015-01-01           a     b
8     3      3         3       NK  2015-01-02           b     b
9     9      9         9       NK  2015-01-02           b     b

#convert only for subtotal - join with empty strings
df['date'] = df['date'].astype(str)

table = pd.pivot_table(df, values= ['Quantity', 'Money', 'Cost'], 
                       index=['house','date', 'currency', 'family name'], 
                       fill_value=0, 
                       aggfunc=np.sum)
print (table)
                                       Cost  Money  Quantity
house date       currency family name                       
a     2015-01-01 USD      a               8      8         4
                          b               6      6         5
      2015-01-02 NK       b               9      9         7
                          c               2      2         1
b     2015-01-01 USD      a               9      9         9
                          c               4      4         0
      2015-01-02 NK       b              12     12        12

tablesum = table.groupby(level='house').sum()

tablesum.index = pd.MultiIndex.from_arrays([tablesum.index.get_level_values(0)+ '_sum', 
                                            len(tablesum.index) * [''],
                                            len(tablesum.index) * [''],
                                            len(tablesum.index) * ['']])

print (tablesum)
          Cost  Money  Quantity
a_sum       25     25        17
b_sum       25     25        21

print (tablesum.index)
MultiIndex(levels=[['a_sum', 'b_sum'], [''], [''], ['']],
           labels=[[0, 1], [0, 0], [0, 0], [0, 0]])


df = pd.concat([table, tablesum]).sort_index(level=0)
print (df)
                                       Cost  Money  Quantity
house date       currency family name                       
a     2015-01-01 USD      a               8      8         4
                          b               6      6         5
      2015-01-02 NK       b               9      9         7
                          c               2      2         1
a_sum                                    25     25        17
b     2015-01-01 USD      a               9      9         9
                          c               4      4         0
      2015-01-02 NK       b              12     12        12
b_sum                                    25     25        21

Keeping index section (different columns) when subtotal row in pivot table (pandas) is used

2 Answers