Renaming column in pandas with a multi level index

user_7475838 0
Viewed 266

I would like to rename 'multi level columns' of a pandas dataframe to 'single level columns'. My code so far does not give any errors but does not rename either. Any suggestions for code improvements?

import pandas as pd

url = 'https://en.wikipedia.org/wiki/Gross_national_income'

df = pd.read_html(url)[3][[('Country', 'Country'), ('GDP[10]', 'GDP[10]')]]\
.rename(columns={('Country', 'Country'):'Country', ('GDP[10]', 'GDP[10]'): 'GDP'})

df

I prefer to use the rename method. df.columns = ['Country', 'GDP'] works but is not what I am looking for.

pandas indexing python
2 Answers

For rename solution create dictionary by flatten values of MultiIndex with join with new columns names in zip:

url = 'https://en.wikipedia.org/wiki/Gross_national_income'
df = pd.read_html(url)[3]

df.columns = df.columns.map('_'.join)

old = ['No._No.', 'Country_Country', 'GNI (Atlas method)[8]_value (a)',
       'GNI (Atlas method)[8]_a - GDP', 'GNI[9]_value (b)', 'GNI[9]_b - GDP',
       'GDP[10]_GDP[10]']
new = ['No.','Country','GNI a','GDP a','GNI b', 'GNI b', 'GDP']

df = df.rename(columns=dict(zip(old, new)))

If want create dictionary for rename:

d = {'No._No.': 'No.', 'Country_Country': 'Country', 'GNI (Atlas method)[8]_value (a)': 'GNI a', 'GNI (Atlas method)[8]_a - GDP': 'GDP a', 'GNI[9]_value (b)': 'GNI b', 'GNI[9]_b - GDP': 'GNI b', 'GDP[10]_GDP[10]': 'GDP'}
df = df.rename(columns=d)

print (df)
   No.         Country     GNI a   GDP a     GNI b   GNI b       GDP
0    1   United States  20636317   91974  20837347  293004  20544343
1    2           China  13181372 -426779  13556853  -51298  13608151
2    3           Japan   5226599  255276   5155423  184100   4971323
3    4         Germany   3905321  -42299   4058030  110410   3947620
4    5  United Kingdom   2777405  -77891   2816805  -38491   2855296
5    6          France   2752034  -25501   2840071   62536   2777535
6    7           India   2727893    9161   2691040  -27692   2718732
7    8           Italy   2038376  -45488   2106525   22661   2083864
8    9          Brazil   1902286   16804   1832170  -53312   1885482
9   10          Canada   1665565  -47776   1694054  -19287   1713341
user_29010020

For alternatives of "rename", you can use get_level_values(). See below:

df.columns = df.columns.get_level_values(0)

>>> print(df)
          Country   GDP[10]
0   United States  20544343
1           China  13608151
2           Japan   4971323
3         Germany   3947620
4  United Kingdom   2855296
5          France   2777535
6           India   2718732
7           Italy   2083864
8          Brazil   1885482
9          Canada   1713341
user_82285580
Related