Convert some columns into row and one column's rows into columns in python

user_11160421 0
Viewed 551

I have one table in which I want to convert all the years column into row and one column's row should be converted into columns. Following is the sample table

City Sales 2016 2017 2018
A X 100 120 160
A Y 90 120 130
A Z 130 160 190
B X 200 220 260
B Y 290 220 230
B Z 230 260 290
C X 300 320 360
C Y 390 320 330
C Z 330 360 390

Final table looks like:

City Year X Y Z
A 2016 100 90 130
A 2017 120 120 160
A 2018 160 130 190
B 2016 200 290 230
B 2017 220 220 260
B 2018 260 230 290
C 2016 300 390 330
C 2017 320 320 360
C 2018 360 330 390
pandas backend dataframe numpy python
4 Answers

Try:

>>> df.melt(id_vars = ["City", "Sales"], 
            value_vars=["2016","2017","2018"], 
            var_name="Year")
      .pivot(index=["City","Year"], columns="Sales", values="value")

Sales        X    Y    Z
City Year               
A    2016  100   90  130
     2017  120  120  160
     2018  160  130  190
B    2016  200  290  230
     2017  220  220  260
     2018  260  230  290
C    2016  300  390  330
     2017  320  320  360
     2018  360  330  390
user_98576310

Use melt:

>>> df.melt(['City', 'Sales'], var_name='Year', value_name=None) \ 
      .set_index(['City', 'Year', 'Sales']) \
      .squeeze().unstack().rename_axis(columns=None)

             X    Y    Z
City Year
A    2016  100   90  130
     2017  120  120  160
     2018  160  130  190
B    2016  200  290  230
     2017  220  220  260
     2018  260  230  290
C    2016  300  390  330
     2017  320  320  360
     2018  360  330  390
user_152399510

Pivot and stack

df.pivot('City', 'Sales').stack(0).rename_axis(['City', 'Year'])

Sales        X    Y    Z
City Year               
A    2016  100   90  130
     2017  120  120  160
     2018  160  130  190
B    2016  200  290  230
     2017  220  220  260
     2018  260  230  290
C    2016  300  390  330
     2017  320  320  360
     2018  360  330  390
user_128331660

This is exactly what I was looking for! However, it worked for some files, but not for others with same structure. Not sure why, probably too many to repeat and pivot. I found that if I sort the data frame by the key fields before pivot, it works for any file. So, in the example above, you would need to sort on at least the key 'City'; in my own work, I sort both fields like 'City' and 'Year' just in case. I thought of this because it's required for SAS proc transpose.

user_185503710
Related