Search code examples
pythonpandastranspose

Transpose one row to column in Pandas

Consider the following table: I have some values for each state per year and age.

Age Year State1 State2 State3
1 2010 123 456 789
2 2010 111 222 333
1 2011 444 555 666
2 2011 777 888 999

Now I'd like to transpose the table in such a way, that the Year becomes the columns:

Age State 2010 2011
1 State1 123 444
1 State2 456 555
1 State3 789 666
2 State1 111 777
2 State2 222 888
2 State3 333 999

I can't get it to work, to transpose only that specific column.

What would be a good solution to achieve this in Pandas?

Solution

  • You can stack and unstack your dataframe:

    out = (
    df.set_index(["Age", "Year"])
    .stack()
    .unstack("Year")
    .reset_index()
    .rename(columns={"level_1": "State"})
)

Year  Age   State  2010  2011
0       1  State1   123   444
1       1  State2   456   555
2       1  State3   789   666
3       2  State1   111   777
4       2  State2   222   888
5       2  State3   333   999