Transform pandas dataframe - add rows-CodePudding

I have a following pandas dataframe:

city,temp,real_feel
Los Angeles,0.97,-4.05
London,2.34,-3.18
Amsterdam,6.05,1.63
Rome,4.59,1.29
Lisabon,15.48,15.15
Berlin,2.38,-1.65

I was playing around with some pivot options, but can't make it to work. How can I transform it to this shape:

city,type,value
Los Angeles,temp,0.97
Los Angeles,real_feel,-4.05
London,temp,,2.34
London,real_feel,-3.18
Amsterdam,temp,6.05
Amsterdam,real_feel,1.63
Rome,temp,4.59
Rome,real_feel,1.29
Lisabon,temp,15.48
Lisabon,real_feel,15.15
Berlin,temp,2.38
Berlin,real_feel,-1.65

CodePudding user response：

Without melt

new_df = df.rename_axis(columns='type').set_index('city')\
           .stack().reset_index(name='value')



           city       type  value
0   Los Angeles       temp   0.97
1   Los Angeles  real_feel  -4.05
2        London       temp   2.34
3        London  real_feel  -3.18
4     Amsterdam       temp   6.05
5     Amsterdam  real_feel   1.63
6          Rome       temp   4.59
7          Rome  real_feel   1.29
8       Lisabon       temp  15.48
9       Lisabon  real_feel  15.15
10       Berlin       temp   2.38
11       Berlin  real_feel  -1.65

If you use melt, you can pass ignore_index=False in oder to sort your new dataframe after melt with sort_index (If you don't pass ignore_index=False DataFrame.melt 'll create a new index and you 'll lose your original index, so you won't be able to use). We pass ignore_index=True in sort_index to sort our rows by the original index but creating a new range index (0,1,2,..)

new_df = df.melt('city', var_name='type', ignore_index=False)\
           .sort_index(ignore_index=True)
print(new_df)


           city       type  value
0   Los Angeles       temp   0.97
1   Los Angeles  real_feel  -4.05
2        London       temp   2.34
3        London  real_feel  -3.18
4     Amsterdam       temp   6.05
5     Amsterdam  real_feel   1.63
6          Rome       temp   4.59
7          Rome  real_feel   1.29
8       Lisabon       temp  15.48
9       Lisabon  real_feel  15.15
10       Berlin       temp   2.38
11       Berlin  real_feel  -1.65