Data wrangling with Python Pandas and pivoting

user_14461231 0
Viewed 51

I have a simple DataFrame containing population data for different years, regions, and countries:

df = pd.DataFrame({'country': ['one', 'one', 'one', 'two', 'two',
                           'two'],
                   'region': ['A', 'B', 'C', 'D', 'E', 'C'],
                   'population 2015': [10, 20, 30, 40, 50, 90],
                   'population 2016': [100, 200, 300, 400, 500, 900]})

I pivoted the DataFrame as follows, to transform it into a specific structure:

df1 = df.pivot(index='region', columns='country', values=['population 2015', 'population 2016']).fillna(0)
df1

Now, I am struggling with calculating each regions total population for each year. I want to this in an efficient and generalizable way, e.g. without using loops. But maybe by using the .apply() and .sum() methods, or the use of of a lambda function.

The output should look similar to:

df = pd.DataFrame({'region': ['A', 'B', 'C', 'D', 'E'],
                   'population 2015': [10, 20, 120, 40, 50],
                   'population 2016': [100, 200, 1200, 400, 500]})

Thank you very much for your help in advance!

pandas apply dataframe pivot python
1 Answers

You can use with level with sum:

df1 = df1.sum(level=0, axis=1).reset_index()
print(df1)

Output:

  region  population 2015  population 2016
0      A             10.0            100.0
1      B             20.0            200.0
2      C            120.0           1200.0
3      D             40.0            400.0
4      E             50.0            500.0

Matching the output format:

df1 = df1.sum(level=0, axis=1).reset_index()
df1.iloc[:, 1:] = df1.iloc[:, 1:].astype(int)
print(df1)

  region  population 2015  population 2016
0      A               10              100
1      B               20              200
2      C              120             1200
3      D               40              400
4      E               50              500
user_56045620
Related