How can I combine columns in pandas dataframe with duplicate names?

user_12276279 0
Viewed 86

I have got pandas dataframe which looks as following: enter image description here

There are multiple columns for Australia based on the provinces, and the columns are titled Australia, Australia.1, Australia.2 and so on. It is also the case for other countries such as the USA, the UK or Canada. I want to have only one column for each of these countries. For example, I want to have one column named Australia with the sum total of values in each provinces, and I want to avoid duplicate column names. How can I do it using pandas dataframe in Python?

pandas dataframe duplicates python
1 Answers

You can transpose the dataframe and reset the index. Then, remove periods and number and groupby the columns which in my example is index:

df = df.T.reset_index()
df['index'] = df['index'].str.replace('\.\d+', '')
df.iloc[:,2:] = df.iloc[:,2:].astype(float)
df = df.groupby('index').sum()
df
user_63667700
Related