Keeping index section (different columns) when subtotal row in pivot table (pandas) is used
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.
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