How To Create This Table From Sorted Dataframe?

I have sorted the dataframe and now I would like to create this table: Sample of data: df4 = {'category': {0: 'HC', 1: 'HC', 2: 'HC', 3: 'AMG HC', 4: 'MUP', 5: 'MUP', 6: 'AMG MUP'

Solution 1:

You can do it this way:

df5 = (df4.reset_index(drop=True)
          .set_index(['category', 'segment'], append=True)
          .unstack()
          .swaplevel(axis=1)
          .sort_index(level=0, axis=1, ascending=False)
          .reindex(['ytd2018', 'ytd2019', 'Evolution'], level=1, axis=1)
      )

df6 = df5.groupby(level='category', sort=False).first()

Result:

print(df6)



segment   Online                   Offline                   Independent                  
         ytd2018 ytd2019 Evolution ytd2018 ytd2019 Evolution     ytd2018 ytd2019 Evolution
category                                                                                  
HC         1000110010%30002800       -7%80092015%
AMG HC       500450      -10%NoneNoneNoneNoneNoneNone
MUP        20001800      -10%NoneNoneNoneNoneNoneNone
AMG MUP      50060020%102010200%31044042%
S          1500200033%23002200       -4%NoneNoneNone
AMG S        40065063%108011002%NoneNoneNone

Solution 2:

You can use pivot_table with first as aggregation function:

(df4.pivot_table(index='category',
                 columns='segment',
                 values=['ytd2018', 'ytd2019', 'Evolution'],
                 aggfunc='first')
    .swaplevel(axis=1)
    .sort_index(level=0, axis=1, ascending=False)
    .reindex(['ytd2018', 'ytd2019', 'Evolution'], level=1, axis=1)
)

output:

segment   Online                   Offline                   Independent                  
         ytd2018 ytd2019 Evolution ytd2018 ytd2019 Evolution     ytd2018 ytd2019 Evolution
category                                                                                  
AMG HC       500450-10%     NaN     NaN       NaN         NaN     NaN       NaN
AMG MUP      500     600       20%102010200%         310     440       42%
AMG S        40065063%   1 080   1 100        2%NaNNaNNaN
HC         1000110010%   3 000   2 800       -7%80092015%
MUP        2 000   1 800      -10%NaNNaNNaNNaNNaNNaN
S          1500200033%   2 300   2 200       -4%NaNNaNNaN

Solution 3:

Group the result at last by level=1, then call first to get the required result:

>>>  (df4.set_index(['category', 'segment'], append=True)
         .unstack()
         .swaplevel(axis=1)
         .sort_index(level=0, axis=1, ascending=False)
         .reindex(['ytd2018', 'ytd2019', 'Evolution'], level=1, axis=1)
         .groupby(level=1, sort=False)
         .first()
         )

OUTPUT:

segment   Online                   Offline                   Independent  \
         ytd2018 ytd2019 Evolution ytd2018 ytd2019 Evolution     ytd2018   
category                                                                   
HC         1000110010%   30002800       -7%         800   
AMG HC       500450      -10%    NoneNoneNoneNone   
MUP        20001800      -10%    NoneNoneNoneNone   
AMG MUP      50060020%   1 020   1 020        0%         310   
S          1500200033%   23002200       -4%        None   
AMG S        40065063%   1 080   11002%        None   
segment                     
         ytd2019 Evolution  
category                    
HC           92015%  
AMG HC      NoneNone  
MUP         NoneNone  
AMG MUP      44042%  
S           NoneNone  
AMG S       NoneNone