Table of Contents
Issue
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 |
Solution
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
Answered By – Shubham Sharma
Answer Checked By – Mary Flores (BugsFixing Volunteer)