Pandas: convert column values ​​to independent columns

Question

I have a Pandas DataFrame that looks like this ( df_olymic ). I would like the Type column values ​​to be converted to independent columns ( df_olympic_table )

Source data block

 In [3]: df_olympic Out[3]: Country Type Num 0 USA Gold 46 1 USA Silver 37 2 USA Bronze 38 3 GB Gold 27 4 GB Silver 23 5 GB Bronze 17 6 China Gold 26 7 China Silver 18 8 China Bronze 26 9 Russia Gold 19 10 Russia Silver 18 11 Russia Bronze 19 

Converted Data Frame

 In [5]: df_olympic_table Out[5]: Country N_Gold N_Silver N_Bronze 0 USA 46 37 38 1 GB 27 23 17 2 China 26 18 26 3 Russia 19 18 19 

What will be the most convenient way to achieve this?

Answer 1

You can use DataFrame.pivot :

 df = df.pivot(index='Country', columns='Type', values='Num') print (df) Type Bronze Gold Silver Country China 26 26 18 GB 17 27 23 Russia 19 19 18 USA 38 46 37 

Another solution with DataFrame.set_index and Series.unstack :

 df = df.set_index(['Country','Type'])['Num'].unstack() print (df) Type Bronze Gold Silver Country China 26 26 18 GB 17 27 23 Russia 19 19 18 USA 38 46 37 

but if get:

ValueError: index contains duplicate records, cannot change form

you need a pivot_table with some aggreagte function, by default it is np.mean , but you can use sum , first ...

 #add new row with duplicates value in 'Country' and 'Type' print (df) Country Type Num 0 USA Gold 46 1 USA Silver 37 2 USA Bronze 38 3 GB Gold 27 4 GB Silver 23 5 GB Bronze 17 6 China Gold 26 7 China Silver 18 8 China Bronze 26 9 Russia Gold 19 10 Russia Silver 18 11 Russia Bronze 20 < - changed value to 20 11 Russia Bronze 100 < - add new row with duplicates df = df.pivot_table(index='Country', columns='Type', values='Num', aggfunc=np.mean) print (df) Type Bronze Gold Silver Country China 26 26 18 GB 17 27 23 Russia 60 19 18 < - Russia get ((100 + 20)/ 2 = 60 USA 38 46 37 

Or groupby using aggreagting mean and change the form to unstack :

 df = df.groupby(['Country','Type'])['Num'].mean().unstack() print (df) Type Bronze Gold Silver Country China 26 26 18 GB 17 27 23 Russia 60 19 18 < - Russia get ((100 + 20)/ 2 = 60 USA 38 46 37