Convert some columns into row and one column's rows into columns in python
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.
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