MultiIndex Pivot Table with Subtotals in Pandas

Question

Helllo,

I have the following data:

date    item_type    item_name    gold_exit
2018-01-25    type1    item1    1
2018-01-25    type2    item2    2
2018-01-25    type3    item3    3
2018-01-25    type1    item4    4
2018-01-25    type2    item5    5
2018-01-26    type3    item6    6
2018-01-26    type1    item7    7
2018-01-26    type2    item8    8
2018-01-26    type3    item9    9
2018-01-26    type1    item10    10
2018-01-27    type2    item11    11
2018-01-27    type3    item12    12
2018-01-27    type1    item13    13
2018-01-27    type2    item14    14
2018-01-27    type3    item15    15
2018-01-28    type1    item16    16
2018-01-28    type2    item17    17
2018-01-28    type3    item18    18
2018-01-28    type1    item19    19
2018-01-28    type2    item20    20
2018-01-29    type3    item21    21
2018-01-29    type1    item22    22
2018-01-29    type2    item23    23
2018-01-29    type3    item24    24
2018-01-29    type1    item25    25
2018-01-30    type2    item26    26
2018-01-30    type3    item27    27
2018-01-30    type1    item28    28
2018-01-30    type2    item29    29
2018-01-30    type3    item30    30

I have managed to pivot it myself using this:

import pandas as pd
import numpy as np

df = pd.read_csv(r"C:\Users\user\Desktop\sql_values.csv")

table = pd.pivot_table(df,index=["date","item_type","item_name"],
                   values=["gold_exit"],
                   aggfunc=[np.sum])
print(table)

And this is what i'm getting:

date    item_type   item_name   Sum - gold_exit
2018-01-25  type1   item1   1
                    item4   4
            type2   item2   2
                    item5   5
            type3   item3   3
2018-01-26  type1   item10  10
                    item7   7
            type2   item8   8
            type3   item6   6
                    item9   9
2018-01-27  type1   item13  13
            type2   item11  11
                    item14  14
            type3   item12  12
                    item15  15
2018-01-28  type1   item16  16
                    item19  19
            type2   item17  17
                    item20  20
            type3   item18  18
 2018-01-29 type1   item22  22
                    item25  25
            type2   item23  23
            type3   item21  21
                    item24  24
2018-01-30  type1   item28  28
            type2   item26  26
                    item29  29
            type3   item27  27
                    item30  30

What exactly should i do in order to get to here, by adding subtotals?

date    item_type   item_name   Sum - gold_exit
2018-01-25  type1       
                        item1   1
                        item4   4
            type1 Result        5
            type2       
                        item2   2
                        item5   5
            type2 Result        7
            type3       
                       item3    3
            type3 Result        3...

I have looked at first at this question - Pivot table subtotals in Pandas - but this does not answer my needs.

Could someone please help? Thanks

score 3 · Accepted Answer · answered Apr 17 '18 at 15:18

This will need the pd.concat and sum notice i pass the para to level , cause you want to have the subtotal for index date and item_type

s=pd.concat([table,table.sum(level=[0,1]).assign(iten_name='result').set_index('iten_name',append=True)]).sort_index(level=[0,1,2])
s
Out[709]: 
                                     sum
                               gold_exit
date       item_type item_name          
2018-01-25 type1     item1             1
                     item4             4
                     result            5
           type2     item2             2
                     item5             5
                     result            7
           type3     item3             3
                     result            3
2018-01-26 type1     item10           10
                     item7             7
                     result           17
           type2     item8             8
                     result            8
           type3     item6             6
                     item9             9
                     result           15
2018-01-27 type1     item13           13
                     result           13
           type2     item11           11
                     item14           14
                     result           25
           type3     item12           12
                     item15           15
                     result           27
2018-01-28 type1     item16           16
                     item19           19
                     result           35
           type2     item17           17
                     item20           20
                     result           37
           type3     item18           18
                     result           18
2018-01-29 type1     item22           22
                     item25           25
                     result           47
           type2     item23           23
                     result           23
           type3     item21           21
                     item24           24
                     result           45
2018-01-30 type1     item28           28
                     result           28
           type2     item26           26
                     item29           29
                     result           55
           type3     item27           27
                     item30           30
                     result           57

Hello Wen, thanks for your answer. In my original set of data, which due to legal matters i have not published here the subtotal is shown and calculated but it is shown above a group for an item_type and i would like to be shown at the bottom of the group. Do you know a resolve to this as well? — George C. Serban, Apr 17 '18 at 15:59
@GeorgeC.Serban you can using `sort_index` , also it my answer work for you, would like consider accept it ? check mark at the left — BENY, Apr 17 '18 at 16:06

MultiIndex Pivot Table with Subtotals in Pandas

1 Answers1