How do I convert one level of pandas MultiIndex column to a standalone column?

Question

I have a dataframe with a MultiIndex column with two levels like this:

import pandas as pd
df = pd.DataFrame(
    np.arange(16).reshape(4,4),
    columns=pd.MultiIndex.from_tuples(
        (("ID1", "Field1"), ("ID1", "Field2"), ("ID2", "Field1"), ("ID2", "Field2"),))
    )
df.insert(0, "Date", pd.bdate_range("2021-11-01", "2021-11-04"))
df

Date            ID1             ID2
                Field1  Field2  Field1  Field2
0   2021-11-01  0       1       2       3
1   2021-11-02  4       5       6       7
2   2021-11-03  8       9       10      11
3   2021-11-04  12      13      14      15

I am trying to convert level 0 of the MultiIndex column to another column called "ID" so that the dataframe looks like this:

df1 = pd.DataFrame(
    np.arange(16).reshape(8,2),
    columns=["Field1", "Field2"]
)
df1.insert(0, "ID", ["ID1", "ID2"]*4)
df1.insert(0, "Date", pd.bdate_range("2021-11-01", "2021-11-04").repeat(2))
df1

    Date        ID      Field1  Field2
0   2021-11-01  ID1     0       1
1   2021-11-01  ID2     2       3
2   2021-11-02  ID1     4       5
3   2021-11-02  ID2     6       7
4   2021-11-03  ID1     8       9
5   2021-11-03  ID2     10      11
6   2021-11-04  ID1     12      13
7   2021-11-04  ID2     14      15

I tried using the melt function

df.melt(col_level=0, id_vars=["Date"])

but that combines Field1 and Field2 into a single column instead of leaving them as separate columns as desired.

Any suggestions would be appreciated. Thank you!

Try working with the solutions from this: (https://stackoverflow.com/questions/14507794/pandas-how-to-flatten-a-hierarchical-index-in-columns). I feel it should give you what you're looking for. — iamakhilverma, Jan 30 '22 at 03:00

score 0 · Accepted Answer · answered Jan 30 '22 at 03:30

You can try the following:

out = (df
       .set_index('Date')
       .stack(level=0)
       .reset_index()
       .rename({'level_1': 'ID'}, axis=1))
print(out)

It gives:

        Date   ID  Field1  Field2
0 2021-11-01  ID1       0       1
1 2021-11-01  ID2       2       3
2 2021-11-02  ID1       4       5
3 2021-11-02  ID2       6       7
4 2021-11-03  ID1       8       9
5 2021-11-03  ID2      10      11
6 2021-11-04  ID1      12      13
7 2021-11-04  ID2      14      15

That is perfect! Does exactly what is needed – ragnard34 Jan 30 '22 at 03:40 — ragnard34, Jan 30 '22 at 03:40

score 0 · Answer 2 · answered Jan 30 '22 at 03:38

here is anther way :

df = pd.pivot(df.melt(id_vars=['Date'], var_name=['ID','Fields']),index=['Date','ID'],columns='Fields', values='value').reset_index()

print(df)

output:

Fields       Date   ID  Field1  Field2
0      2021-11-01  ID1       0       1
1      2021-11-01  ID2       2       3
2      2021-11-02  ID1       4       5
3      2021-11-02  ID2       6       7
4      2021-11-03  ID1       8       9
5      2021-11-03  ID2      10      11
6      2021-11-04  ID1      12      13
7      2021-11-04  ID2      14      15

This does it too. Good to understand the parameters of melt() better — ragnard34, Jan 30 '22 at 03:48

How do I convert one level of pandas MultiIndex column to a standalone column?

2 Answers2