Python Pandas: Convert Rows as Column headers [duplicate]

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.

Andy Hayden · Accepted Answer · 2013-06-25 13:18:36Z

150

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

answered Jun 25, 2013 at 13:18

Andy Hayden

378k110 gold badges640 silver badges546 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

madoki Over a year ago

This creates a multilevel index, which is not 100% what was wanted. Get rid of it by medals.reset_index( drop=False, inplace=True ) then follow by medals.reindex_axis(['Year', 'Country', 'Gold', 'Silver', 'Bronze'], axis=1)

Bn.F76 Over a year ago

Python v3.6+ '.reindex_axis' is deprecated and will be removed in a future version. Use '.reindex' instead.

LayneSadler · Accepted Answer · 2022-09-15 22:11:11Z

15

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')

edited Sep 15, 2022 at 22:11

LayneSadler

6,0926 gold badges54 silver badges89 bronze badges

answered Nov 20, 2018 at 5:53

Manu Sharma

1,7494 gold badges32 silver badges53 bronze badges

Collectives™ on Stack Overflow

Python Pandas: Convert Rows as Column headers [duplicate]

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related