jesseWUT jesseWUT - 2 months ago 17
Python Question

Create unique MultiIndex from Non-unique Index Python Pandas

I have a pandas DataFrame with a non-unique index:

index = [1,1,1,1,2,2,2,3]

df = pd.DataFrame(data = {'col1': [1,3,7,6,2,4,3,4]}, index=index)

df
Out[12]:
col1
1 1
1 3
1 7
1 6
2 2
2 4
2 3
3 4


I'd like to turn this into unique MultiIndex and preserve order, like this:

col1
Ind2
1 0 1
1 3
2 7
3 6
2 0 2
1 4
2 3
3 0 4


I would imagine pandas would have a function for something like this but haven't found anything

Answer

You can do a groupby.cumcount on the index, and then append it as a new level to the index using set_index:

df = df.set_index(df.groupby(level=0).cumcount(), append=True)

The resulting output:

     col1
1 0     1
  1     3
  2     7
  3     6
2 0     2
  1     4
  2     3
3 0     4
Comments