CODEWITHSUNDEEP
pythonpandas
Rtut
Rtut

Reputation: 1007

convert a dataframe to multiple index dataframe

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

Upvotes: 2

Views: 99

Answers (1)

akuiper
akuiper

Reputation: 214927

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))

enter image description here

Upvotes: 4

Related Questions