My csv contain like (df) :
item_group item_code total_qty total_amount cost_center
0 Drink IC06-1P 1 3.902 Cafe II
1 Drink IC09-1 1 2.927 Cafe II
2 BreakFast FS04-2 1 6.463 Cafe II
3 Drink IC08-1 1 2.927 Cafe II
4 Drink DT05-1 1 2.561 Cafe II
.. ... ... ... ... ...
79 Standard Food FS01-2 12 83.412 Cafe II
80 Standard Food FS01-1 13 101.465 Cafe II
81 Drink IC05-1 14 54.628 Cafe I
82 Standard Food FS01-2 35 243.285 Cafe I
83 Standard Food FS01-1 44 343.420 Cafe I
Here I can pivot in Excel
Anyone, please guide me to do the same in pandas code
>Solution :
I think you need DataFrame.pivot_table with DataFrame.swaplevel for change order of level of MulitIndex in columns with sorting MultiIndex, last add total columns with aggregate sum per second level of MultiIndex:
df1 = df.pivot_table(index=['item_group','item_code'],
columns=['cost_center'],
values=['total_qty','total_amount'],
aggfunc='sum').swaplevel(axis=1).sort_index(level=0, axis=1)
out = df1.join(pd.concat({'total': df1.groupby(axis=1, level=1).sum()}, axis=1))
print (out)
cost_center Cafe I Cafe II \
total_amount total_qty total_amount total_qty
item_group item_code
BreakFast FS04-2 NaN NaN 6.463 1.0
Drink DT05-1 NaN NaN 2.561 1.0
IC05-1 54.628 14.0 NaN NaN
IC06-1P NaN NaN 3.902 1.0
IC08-1 NaN NaN 2.927 1.0
IC09-1 NaN NaN 2.927 1.0
Standard Food FS01-1 343.420 44.0 101.465 13.0
FS01-2 243.285 35.0 83.412 12.0
cost_center total
total_amount total_qty
item_group item_code
BreakFast FS04-2 6.463 1.0
Drink DT05-1 2.561 1.0
IC05-1 54.628 14.0
IC06-1P 3.902 1.0
IC08-1 2.927 1.0
IC09-1 2.927 1.0
Standard Food FS01-1 444.885 57.0
FS01-2 326.697 47.0
Last if necessary convert total_qty to integers (with NaNs):
idx = pd.IndexSlice
out.loc[:, idx[:, 'total_qty']] = out.loc[:, idx[:, 'total_qty']].astype('Int64')
print (out)
cost_center Cafe I Cafe II \
total_amount total_qty total_amount total_qty
item_group item_code
BreakFast FS04-2 NaN <NA> 6.463 1
Drink DT05-1 NaN <NA> 2.561 1
IC05-1 54.628 14 NaN <NA>
IC06-1P NaN <NA> 3.902 1
IC08-1 NaN <NA> 2.927 1
IC09-1 NaN <NA> 2.927 1
Standard Food FS01-1 343.420 44 101.465 13
FS01-2 243.285 35 83.412 12
cost_center total
total_amount total_qty
item_group item_code
BreakFast FS04-2 6.463 1
Drink DT05-1 2.561 1
IC05-1 54.628 14
IC06-1P 3.902 1
IC08-1 2.927 1
IC09-1 2.927 1
Standard Food FS01-1 444.885 57
FS01-2 326.697 47