1

My df has multi-index column label like below (six columns):

Col lvl 1 01/02/89      31/12/97      15/07/65
Col lvl 2      ID1  ID2      ID1  ID2      ID1  ID2
Apple          dec  box      coy  sil      mcd  les
Banana         fox  dog      bil  neo      dol  nit
Carrot         kil  not      bis  xev      min  les

And I am looking to get a dataframe like the below:

     col1      col2  ID1  ID2
0   Apple  01/02/89  dec  box
1  Banana  01/02/89  fox  dog
2  Carrot  01/02/89  kil  not
3   Apple  31/12/97  coy  sil
4  Banana  31/12/97  bil  neo
5  Carrot  31/12/97  bis  xev
6   Apple  15/07/65  mcd  les
7  Banana  15/07/65  dol  nit
8  Carrot  15/07/65  min  les

Tried my luck with df.melt() but to no luck. Can someone please help ?

BeRT2me
  • 12,699
  • 2
  • 13
  • 31
SakshamC
  • 11
  • 2
  • Please share your code, its output, and your troubleshooting thought process. – picobit Oct 21 '22 at 16:17
  • Does this answer your question? [Reverting from multiindex to single index dataframe in pandas](https://stackoverflow.com/questions/32938060/reverting-from-multiindex-to-single-index-dataframe-in-pandas) – LazyClown Oct 21 '22 at 16:33

1 Answers1

1

It looks like you want to stack the 2nd level:

out = df.stack(-2).reset_index()
out.columns = ['Fruit', 'Date', 'ID1', 'ID2']
out.columns.name = None
print(out)

Output:

    Fruit      Date  ID1  ID2
0   Apple  01/02/89  dec  box
1   Apple  15/07/65  mcd  les
2   Apple  31/12/97  coy  sil
3  Banana  01/02/89  fox  dog
4  Banana  15/07/65  dol  nit
5  Banana  31/12/97  bil  neo
6  Carrot  01/02/89  kil  not
7  Carrot  15/07/65  min  les
8  Carrot  31/12/97  bis  xev
BeRT2me
  • 12,699
  • 2
  • 13
  • 31