Python Pandas: Convert Rows as Column headers

Question

I have the following dataframe:

Year    Country          medal    no of medals
1896    Afghanistan      Gold        5
1896    Afghanistan      Silver      4
1896    Afghanistan      Bronze      3
1896    Algeria          Gold        1
1896    Algeria          Silver      2
1896    Algeria          Bronze      3

I want it this way.

Year    Country      Gold   Silver   Bronze
1896    Afghanistan    5      4         3
1896    Algeria        1      2         3

Stack/Unstack dont seem to work.

Answer 1

You're looking for pivot_table:

In [11]: medals = df.pivot_table('no of medals', ['Year', 'Country'], 'medal')

In [12]: medals
Out[12]:
medal             Bronze  Gold  Silver
Year Country
1896 Afghanistan       3     5       4
     Algeria           3     1       2

and if you want to reorder the columns:

In [12]: medals.reindex_axis(['Gold', 'Silver', 'Bronze'], axis=1)
Out[12]:
medal             Gold  Silver  Bronze
Year Country
1896 Afghanistan     5       4       3
     Algeria         1       2       3

Answer 2

Stack/ Unstack won't work until you have the desired column in your row/ column indexes. e.g. In simple words, Stack/ Unstack will bring the lowest level of column index to the lowest level of row index and vice versa.

So in your case, you can achieve the same results with stack/unstack via:

df.set_index(['Year','Country','medal'], drop=True).unstack('medal')