CODEWITHSUNDEEP
pythonpython-3.xpandas
GlenCloncurry
GlenCloncurry

Reputation: 517

Doing the opposite of pivot in pandas Python

I'm trying to do the opposite of pivot in pandas. Wondering whether someone can provide some assistance? Trying to transform the data from the below

|Date|A|B|C|D|E |
|----|-|-|-|-|--|
|2005|1|2|3|4|50|
|2006|6|7|8|9|10|

to 

|Date|X|Val|
|----|-|---|
|2005|A|1  |
|2005|B|2  |
|2005|C|3  |
etc 
|2006|A|6  |
etc
|2006|E|10 |

Thank you very much

Upvotes: 26

Views: 18794

Answers (1)

jezrael
jezrael

Reputation: 863266

Use set_index with stack:

df = df.set_index('Date').stack().reset_index(name='Val').rename(columns={'level_1':'X'})
print (df)
   Date  X  Val
0  2005  A    1
1  2005  B    2
2  2005  C    3
3  2005  D    4
4  2005  E   50
5  2006  A    6
6  2006  B    7
7  2006  C    8
8  2006  D    9
9  2006  E   10

Or melt, but there is different ordering of values:

df = df.melt('Date', var_name='X', value_name='Val')
print (df)
   Date  X  Val
0  2005  A    1
1  2006  A    6
2  2005  B    2
3  2006  B    7
4  2005  C    3
5  2006  C    8
6  2005  D    4
7  2006  D    9
8  2005  E   50
9  2006  E   10

So for same output add sort_values:

df = (df.melt('Date', var_name='X', value_name='Val')
        .sort_values(['Date','X'])
        .reset_index(drop=True))
print (df)
   Date  X  Val
0  2005  A    1
1  2005  B    2
2  2005  C    3
3  2005  D    4
4  2005  E   50
5  2006  A    6
6  2006  B    7
7  2006  C    8
8  2006  D    9
9  2006  E   10

Upvotes: 38

Related Questions