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
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