MultiIndex Pandas From Dataframe

Question

How would I take a df like this:

Dates          Type           1      2      3                                                                                                                            ...
2018-01-01     Type1        Golf    Van    Jeep
2018-01-02     Type1        Golf    Van    Jeep
2018-01-01     Type2        Golf1   Van1   Jeep1
2018-01-02     Type2        Golf2   Van2   Jeep2

and turn it into:

                               Type1                    Type2
Dates                    1      2      3           1      2      3                                                                                                               ...
2018-01-01            Golf    Van    Jeep      Golf1    Van1    Jeep1
2018-01-02            Golf    Van    Jeep      Golf2    Van2    Jeep2

EDIT: I would like to introduce a second index like this:

Type                          Type1                    Type2
Numbers                  1      2      3           1      2      3    
Dates                                                                                                                              ...
2018-01-01            Golf    Van    Jeep      Golf1    Van1    Jeep1
2018-01-02            Golf    Van    Jeep      Golf2    Van2    Jeep2

EDIT: Now if I wanted to relabel all the Number index values - how would I create this:

Type                          Type1                    Type2
Numbers                  p1     p2     p3         p1      p2      p3    
Dates                                                                                                                              ...
2018-01-01            Golf    Van    Jeep      Golf1    Van1    Jeep1
2018-01-02            Golf    Van    Jeep      Golf2    Van2    Jeep2

EDIT: Can just use: .add_prefix('hh')

jezrael · Accepted Answer · 2020-02-27 14:27:08Z

3

Use DataFrame.set_index with DataFrame.unstack, then change order of levels by DataFrame.swaplevel and sorting MultiIndex by DataFrame.sort_index:

df = df.set_index(['Dates','Type']).unstack().swaplevel(0,1, axis=1).sort_index(axis=1)
print (df)
Type       Type1             Type2             
               1    2     3      1     2      3
Dates                                          
2018-01-01  Golf  Van  Jeep  Golf1  Van1  Jeep1
2018-01-02  Golf  Van  Jeep  Golf2  Van2  Jeep2

EDIT: Add DataFrame.rename_axis by tuple:

df = (df.set_index(['Dates','Type'])
        .unstack()
        .swaplevel(0,1, axis=1)
        .sort_index(axis=1)
        .rename_axis(('Type','Numbers'), axis=1))
print (df)
Type       Type1             Type2             
Numbers        1    2     3      1     2      3
Dates                                          
2018-01-01  Golf  Van  Jeep  Golf1  Van1  Jeep1
2018-01-02  Golf  Van  Jeep  Golf2  Van2  Jeep2

edited Feb 27, 2020 at 14:27

answered Feb 27, 2020 at 13:50

jezrael

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

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

4 Comments

Bob Over a year ago

This is great, thank you. How would we add a level below Type to name the numbers

jezrael Over a year ago

@Bob - Can you show expected output in edited question?

Bob Over a year ago

Thank you, very much all your help :) - If i was to also re-label all the numbers to say p1, p2, p3 .... how would this be done ?

Bob Over a year ago

Ahhh I would just .add_prefix('pp')

BENY · Accepted Answer · 2020-02-27 13:56:35Z

1

IIUC melt then pivot it back

s=df.melt(['Dates','Type']).pivot_table(index=['Dates'],columns=['Type','variable'],values=['value'],aggfunc='sum')
s.columns=s.columns.droplevel(level=0)
s
Out[189]: 
Type       Type1             Type2             
variable       1    2     3      1     2      3
Dates                                          
2018-01-01  Golf  Van  Jeep  Golf1  Van1  Jeep1
2018-01-02  Golf  Van  Jeep  Golf2  Van2  Jeep2

answered Feb 27, 2020 at 13:56

BENY

324k22 gold badges176 silver badges250 bronze badges

Collectives™ on Stack Overflow

MultiIndex Pandas From Dataframe

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related