How can I transform a level of index into new level in Multi level column (Pandas)

Question

I have the following dataframe dfg (which is a result of previous aggregations).

                   F-1   F-2
dataset Model               
G       Baseline 0.971 0.967
        Version2 0.971 0.967
H       Baseline 0.780 0.762
        Version2 0.800 0.777
S       Baseline 0.401 0.320
        Version2 0.453 0.365
T       Baseline 0.881 0.825
        Version2 0.989 0.985

What I want is to obtain the following organization of my dataframe:

        Baseline    Version2
dataset F-1   F-2   F-1   F-2
G       0.971 0.967 0.971 0.967
H       0.780 0.762 0.800 0.777
S       0.401 0.320 0.453 0.365
T       0.881 0.825 0.989 0.985

I tried several things, but what I thought were the best solutions always gave me errors. My most "logical" solution was:

reset the index (to extract 'Model' into columns);
create the multi-level column from tuples;
changing the columns into multi-level columns.

like this:

dfg.reset_index(inplace=True, level=['Model']
new_cols = [('Baseline', 'F-1'), ('Baseline', 'F-2'), ('Version2', 'F-1'), ('Version2', 'F-2')]
multi_cols = pd.MultiIndex.from_tuples(new_cols, names=('Model', 'Measure'))

but I'm getting the following errors:

ValueError: Length mismatch: Expected axis has 3 elements, new values have 4 elements

I know this is rather raw, but I can't find any source that can explain how to build multi-level columns from existing dataframes.

jezrael · Accepted Answer · 2021-12-08 12:37:38Z

4

Use DataFrame.stack with Series.unstack, last clean columns names by DataFrame.rename_axis:

#last previous, last levels
df = df.stack().unstack([-2,-1]).rename_axis((None, None), axis=1)
#or second and third levels
#df = df.stack().unstack([1,2]).rename_axis((None, None), axis=1)
print (df)
        Baseline        Version2       
             F-1    F-2      F-1    F-2
dataset                                
G          0.971  0.967    0.971  0.967
H          0.780  0.762    0.800  0.777
S          0.401  0.320    0.453  0.365
T          0.881  0.825    0.989  0.985

edited Dec 8, 2021 at 12:37

answered Dec 8, 2021 at 12:31

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MrT77 Over a year ago

Thanks! And how could I "erase" 'Model'?

jezrael Over a year ago

@MrT77 - Answer was edited.

sunnytown · Accepted Answer · 2021-12-08 12:35:08Z

1

df.stack().unstack(0).transpose()

answered Dec 8, 2021 at 12:35

sunnytown

2,0661 gold badge9 silver badges19 bronze badges

Collectives™ on Stack Overflow

How can I transform a level of index into new level in Multi level column (Pandas)

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related