convert a dataframe to multiple index dataframe

Question

May be fairly easy to convert to multiple indexes but I could not get my head around it. I have the following dataframe that I would like to convert to multiple indexes.

My Input dataframe:

mydf= pd.DataFrame({'id':['dataid1','dataid2','dataid1','dataid1','dataid2'],'Ref':['Ref1','Ref2','Ref3','Ref4','Ref5'],'Val1':[0.7,0.5,0.8,9.0,1],'Val2':[7,8,9,1.1,2]})
mydf= mydf.set_index('id')

          Ref  Val1  Val2
id                       
dataid1  Ref1   0.7   7.0
dataid2  Ref2   0.5   8.0
dataid1  Ref3   0.8   9.0
dataid1  Ref4   9.0   1.1
dataid2  Ref5   1.0   2.0

Desired dataframe:

    dataid1        dataid2
Ref  Val1  Val2  Ref  Val1  Val2
Ref1  0.7  7.0   Ref2  0.5  8.0
Ref3  0.8  9.0   Ref5  1.0  2.0
Ref4  9.0  1.1

akuiper · Accepted Answer · 2017-02-07 21:13:56Z

4

You can make a rowid column to indicate the correspondence between rows from different ids, then do the unstack/pivot:

(mydf.assign(rowid = mydf.groupby('id').cumcount())
     .set_index(['id', 'rowid']).unstack(level=0)
     .swaplevel(axis=1).sort_index(axis=1))

edited Feb 7, 2017 at 21:13

answered Feb 7, 2017 at 20:31

akuiper

216k33 gold badges362 silver badges379 bronze badges

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

Collectives™ on Stack Overflow

convert a dataframe to multiple index dataframe

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related