CODEWITHSUNDEEP
pandaspivot-table
OO7
OO7

Reputation: 450

How to get Total Sum of Value of columns in pivot table of Panda dataframe?

I was able to create the Pivot table with the following code as per below.

d = {'ID': [1,2,1,2,3], 'Month':["Apr-20","May-20","June-20","Apr-20","June-20"],'Value1':[12,23,15,45,34], 'Value2':[124,214,1234,1324,234], 'Value3':[124,214,1234,1324,234], 'Value4':[124,214,1234,1324,234]}
df = pd.DataFrame(d)

df_pvt =    pd.pivot_table(df, values=['Value1','Value2', 'Value3', 'Value4'],
                        index=['ID'],
                        columns=['Month'],
                        aggfunc=np.sum,
                        fill_value=0)

I got stuck to calculate "Total Sum of Value" and Grand Total Row as per red mark in the below image. Need your help. I am expecting below output.

enter image description here

Upvotes: 1

Views: 166

Answers (1)

jezrael
jezrael

Reputation: 862801

Here is necessary add another DataFrame after sum with MultiIndex created by MultiIndex.from_product:

df = df_pvt.sum(axis=1, level=0)
df.columns = pd.MultiIndex.from_product([['Total'], df.columns])

df_pvt = df_pvt.join(df)
print (df_pvt)
      Value1                Value2                Value3                 \
Month Apr-20 June-20 May-20 Apr-20 June-20 May-20 Apr-20 June-20 May-20   
ID                                                                        
1         12      15      0    124    1234      0    124    1234      0   
2         45       0     23   1324       0    214   1324       0    214   
3          0      34      0      0     234      0      0     234      0   

      Value4                 Total                       
Month Apr-20 June-20 May-20 Value1 Value2 Value3 Value4  
ID                                                       
1        124    1234      0     27   1358   1358   1358  
2       1324       0    214     68   1538   1538   1538  
3          0     234      0     34    234    234    234

If want select one of new added columns use tuple:

print (df_pvt[('Total','Value1')])
ID
1    27
2    68
3    34
Name: (Total, Value1), dtype: int64

Another idea is change order of new levels:

df = df_pvt.sum(axis=1, level=0)
df.columns = pd.MultiIndex.from_product([df.columns, ['Total'], ])

df_pvt = df_pvt.join(df)
print (df_pvt)
      Value1                Value2                Value3                 \
Month Apr-20 June-20 May-20 Apr-20 June-20 May-20 Apr-20 June-20 May-20   
ID                                                                        
1         12      15      0    124    1234      0    124    1234      0   
2         45       0     23   1324       0    214   1324       0    214   
3          0      34      0      0     234      0      0     234      0   

      Value4                Value1 Value2 Value3 Value4  
Month Apr-20 June-20 May-20  Total  Total  Total  Total  
ID                                                       
1        124    1234      0     27   1358   1358   1358  
2       1324       0    214     68   1538   1538   1538  
3          0     234      0     34    234    234    234  

#swapped order
print (df_pvt[('Value1','Total')])

Also is possible create empty strings levels, but I think it is confused (because for seelcting is necessary use empty string), so prefer first solution:

df = df_pvt.sum(axis=1, level=0)
df.columns = pd.MultiIndex.from_product(['Total Sum of ' + df.columns, ['']])

df_pvt = df_pvt.join(df)
print (df_pvt)
      Value1                Value2                Value3                 \
Month Apr-20 June-20 May-20 Apr-20 June-20 May-20 Apr-20 June-20 May-20   
ID                                                                        
1         12      15      0    124    1234      0    124    1234      0   
2         45       0     23   1324       0    214   1324       0    214   
3          0      34      0      0     234      0      0     234      0   

      Value4                Total Sum of Value1 Total Sum of Value2  \
Month Apr-20 June-20 May-20                                           
ID                                                                    
1        124    1234      0                  27                1358   
2       1324       0    214                  68                1538   
3          0     234      0                  34                 234   

      Total Sum of Value3 Total Sum of Value4  
Month                                          
ID                                             
1                    1358                1358  
2                    1538                1538  
3                     234                 234

For seelcting is necessary also use empty string:

print (df_pvt[('Total Sum of Value1','')])
ID
1    27
2    68
3    34
Name: (Total Sum of Value1, ), dtype: int64

Upvotes: 2

Related Questions