Pandas, multi index column pivot/groupby-CodePudding

I literally have a data frame that look like this, and week over a week it's filling up more data, so I'm trying to make a pivot table with multi-index column but no luck

print (df)
    week   level letter  pieces value
0      1   first      A      10    $5
1      1   first      B      15   $10
2      1   first      C      15    $8
3      1  second      D       5   $10
4      1  second      E      10    $5
5      1  second      A      15   $10
6      2   first      A       5   $10
7      2   first      B      10    $5
8      2   first      C      10    $5
9      2  second      D      15   $10
10     2  second      E      15    $8
11     2  second      A      15   $10

I'm looking to transform this data into this, I've tried pd.pivot_table but doesn't work either pandas or any other library :

this is the code that I'm using:

pt = df.pivot_table(
                     index=['level', 'letter'],
                      columns='week',
                      values=['pieces', 'value' ], aggfunc='sum')

CodePudding user response：

For me working DataFrame.pivot with DataFrame.swaplevel and DataFrame.sort_index:

df = (df.pivot(index=['level', 'letter'], columns='week', values=['pieces','value'])
        .swaplevel(1,0, axis=1)
        .sort_index(axis=1))
print (df)
week               1            2      
              pieces value pieces value
level  letter                          
first  A          10    $5      5   $10
       B          15   $10     10    $5
       C          15    $8     10    $5
second A          15   $10     15   $10
       D           5   $10     15   $10
       E          10    $5     15    $8

Your solution is similar, only is converted value column to numbers, for possible use aggfunc='sum':

df['value'] = df['value'].str.lstrip('$').astype(int)

df = (df.pivot_table(index=['level', 'letter'], 
                     columns='week', 
                     values=['pieces','value'], 
                     aggfunc='sum')
        .swaplevel(1,0, axis=1)
        .sort_index(axis=1))
print (df)
week               1            2      
              pieces value pieces value
level  letter                          
first  A          10     5      5    10
       B          15    10     10     5
       C          15     8     10     5
second A          15    10     15    10
       D           5    10     15    10
       E          10     5     15     8