How do I transform df using pandas

user_1968829 0
Viewed 40

I have the following dataframe snippet:

predictor   b   z   pvalue  model   ss
age_raw 1.026695    4.678675    2.89E-06    composite_outcome_nonenglish    1
elixsum 1.228125    8.514571    1.67E-17    composite_outcome_nonenglish    1
age_raw 1.087716    0.228507    0.819252    composite_outcome_english   0
elixsum 1.760882    1.68492     0.092004    composite_outcome_english   0

that I need to transpose into a multi-index column dataframe using model as the highest level and (b, z, pvalue, ss) as the secondary level the predictor as rows:

                    model                              model
          composite_outcome_nonenglish            composite_outcome_english 
           b        z           pvalue   ss           b         z           pvalue  ss
age_raw 1.026695    4.678675    2.89E-06 0            1.087716  0.228507    0.819252 1
elixsum 1.228125    8.514571    1.67E-17 0            1.760882  1.68492     0.092004 1

I've tried all sorts of groupings, and stacking and unstacking, etc., and for the life of me, I cannot get this right.

pandas transpose dataframe
2 Answers

You want pivot and swaplevel:

(df.pivot(index='predictor', columns='model')
   .swaplevel(0,1, axis=1)
   .sort_index(axis=1)
)

Output:

model       composite_outcome_english               composite_outcome_nonenglish
            b           pvalue      ss  z           b           pvalue      ss          z
predictor                               
age_raw     1.087716    0.819252    0   0.228507    1.026695    2.890000e-06    1   4.678675
elixsum     1.760882    0.092004    0   1.684920    1.228125    1.670000e-17    1   8.514571
user_42384080

set index with unstack + stack

out = df.set_index(['predictor','model']).stack().unstack(level=[1,2])
Out[366]: 
model     composite_outcome_nonenglish            ... composite_outcome_english     
                                     b         z  ...                    pvalue   ss
predictor                                         ...                               
age_raw                       1.026695  4.678675  ...                  0.819252  0.0
elixsum                       1.228125  8.514571  ...                  0.092004  0.0
user_79645270
Related