Pandas how to melt only first level column in multi index columns-CodePudding

I have a pandas dataframe with multi index columns (single index column and multi index column are mixed):

dic = {
    ("nation", "") : ["USA", "Korea", "China"],
    ("data", "") : ["a", "b", "c"],
    ("P1", "ev") : [2, 3, 1],
    ("P1", "reason") : ["a.2", "b.3", "c.1"],
    ("P2", "ev") : [2, 5, 2],
    ("P2", "reason") : ["b.2", "d.5", "a.1, c.1"],
}


out:

       nation      data        P1                  P2
                               ev       reason     ev      reason
0      USA          a          2         a.2       2        b.2
1      Korea        b          3         b.3       5        d.5
2      China        c          1         c.1       2      a.1, c.1

I want to change to this:

       nation     data       person      ev      reason
0      USA          a          P1        2        a.2
0      USA          a          P2        2        b.2
1      Korea        b          P1        3        b.3
1      Korea        b          P2        5        d.5
2      China        c          P1        1        c.1
2      China        c          P2        2      a.1, c.1

How could I get this?

CodePudding user response：

Let's try

out = (df.set_index(['nation', 'data'])
       .stack(level=0)
       .reset_index()
       .rename(columns={'level_2': 'person'}))

print(out)

  nation data person  ev    reason
0    USA    a     P1   2       a.2
1    USA    a     P2   2       b.2
2  Korea    b     P1   3       b.3
3  Korea    b     P2   5       d.5
4  China    c     P1   1       c.1
5  China    c     P2   2  a.1, c.1