Not sure the order of melt/stacking/unstacking to morph my Data Frame

October 26, 2022

I have a multiindex column dataframe. I want to preserve the existing index, but move a level from the multindex columns to become a sublevel of the index instead.

I can’t figure out the correct incantation of melt/stack/unstack/pivot to move from what i have to what i want. Unstacking() turned things into a series and lost the original date index.

names = ['mike', 'matt', 'dave']
details = ['bla', 'foo', ]

columns = pd.MultiIndex.from_tuples((n,d) for n in names for d in details)
index = pd.date_range(start="2022-10-30",  end="2022-11-3" ,freq="d", )
have = pd.DataFrame(np.random.randint(0,100, size = (5,6)), index=index, columns=columns)
have
   

want_columns = details
want_index = pd.MultiIndex.from_product([index, names])
want = pd.DataFrame(np.random.randint(0,100, size = (15,2)), index=want_index, columns=want_columns)
want

>Solution :

Use DataFrame.stack with level=0:

print (have.stack(level=0))
                 bla  foo
2022-10-30 dave   88   18
           matt   49   55
           mike   92   45
2022-10-31 dave   33   27
           matt   53   41
           mike   24   16
2022-11-01 dave   48   19
           matt   94   75
           mike   11   19
2022-11-02 dave   16   90
           matt   14   93
           mike   38   72
2022-11-03 dave   80   15
           matt   97    2
           mike   11   94