how to pivot table from excel to pandas

Question

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

Yes you can, please read [`pivot`](https://stackoverflow.com/questions/47152691/how-can-i-pivot-a-dataframe) in detail and ask a real question. Just providing a screen capture and requesting other to code for you is not a question ;) — mozway, Apr 19 '23 at 05:33

jezrael · Accepted Answer · 2023-04-19T05:47:42.557

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

how to pivot table from excel to pandas

1 Answers1