Change columns names to a row variable in Pandas

Question

I´m importing an excel file with column names in merge cells. When I create a pandas data frame what I have is something like this:

|------|------|------|------|------|------|------|------|------|
|      |      |      |  A   |   A  |   B  |   B  |   C  |   C  |
|------|------|------|------|------|------|------|------|------|
|   K  |  L   |  M   |  X   |   Y  |   X  |   Y  |   X  |   Y  |
|------|------|------|------|------|------|------|------|------|
|  k1  |  l1  |  m1  |  1   |   2  |   3  |   4  |   5  |   6  |
|------|------|------|------|------|------|------|------|------|

And what I expect is to put the firts columns names as a new column like this:

|------|------|------|------|------|------|
|   K  |  L   |  M   |value |   X  |   Y  |
|------|------|------|------|------|------|
|  k1  |  l1  |  m1  |  A   |   1  |   2  |
|------|------|------|------|------|------|
|  k1  |  l1  |  m1  |  B   |   3  |   4  |
|------|------|------|------|------|------|
|  k1  |  l1  |  m1  |  C   |   5  |   5  |
|------|------|------|------|------|------|

UserWarning: DataFrame columns are not unique, some columns will be omitted. — LizUlloa, May 07 '20 at 01:15

score 1 · Answer 1 · answered May 07 '20 at 00:18

If you have below dataframe , we just need to do stack

s=df.stack(level=0).reset_index()
    K   L   M level_3  X  Y
0  k1  l1  m1       A  1  2
1  k1  l1  m1       B  3  4
2  k1  l1  m1       C  5  6

Sample Data

          A     B     C   
          X  Y  X  Y  X  Y
K  L  M                   
k1 l1 m1  1  2  3  4  5  6

df.to_dict()
{('A', 'X'): {('k1', 'l1', 'm1'): 1}, ('A', 'Y'): {('k1', 'l1', 'm1'): 2}, ('B', 'X'): {('k1', 'l1', 'm1'): 3}, ('B', 'Y'): {('k1', 'l1', 'm1'): 4}, ('C', 'X'): {('k1', 'l1', 'm1'): 5}, ('C', 'Y'): {('k1', 'l1', 'm1'): 6}}

Change columns names to a row variable in Pandas

1 Answers1