How to manipulate a dataframe in Python?

Question

I have the following sample dataframe:

I need to transform it to look like :

Pls note that:

In the new column, the last 2 columns in original data frame have been replaced by the district variables of second last column of original df
The original df was duplicating columns upto 'Year' column and the new df should have fewer rows because the new variables are now in a 'transposed manner'

What I had thought would work out never did. Because the rows are still duplicated:

print(PopRatesC.shape)
PopRates = PopRatesC.copy()
PopRates.rename(columns={'Value':'PopRate'}, inplace=True)
PopRates=pd.get_dummies(data=PopRates, columns=['Series'])
PopRates['Rural_PopRate']= PopRates['Series_Rural_Rate']*PopRates['PopRate']
PopRates['Urban_PopRate']= PopRates['Series_Urban_Rate']*PopRates['PopRate']
print(PopRates.shape)
PopRates[416:422]

Kindly explain the right approach I should use.

On stack overflow images should not be used for textual content, see [Why should I not upload images of code/data/errors?](https://meta.stackoverflow.com/a/285557) for why. — HedgeHog, May 14 '23 at 10:58

score 2 · Accepted Answer · answered May 14 '23 at 10:56

It looks like you wanna get a pivot based on group of columns, so simply use pandas.pivot().

Example

import pandas as pd

df = pd.DataFrame({
    'code': [108,108,108,108,132,132],
    'country': ['burundi','burundi','burundi','burundi','cabo verde','cabo verde'],
    'year': [2010,2010,2015,2015,2005,2005],
    'series': ['urban_rate','rural_rate','urban_rate','rural_rate','urban_rate','rural_rate'],
    'value': [5.9,3.0,5.6,2.7,3.3,-0.2]
})

pd.pivot(df, index=['code','country','year'], columns='series', values='value').reset_index()

Output

code	country	year	rural_rate	urban_rate
108	burundi	2010	3.0	5.9
108	burundi	2015	2.7	5.6
132	cabo verde	2005	-0.2	3.3

How to manipulate a dataframe in Python?

1 Answers1

Example

Output