Search code examples
pandastabular

Pandas - convert rows to columns 'age_group' and assign values from another column 'registered_patients' to those corresponding columns

I want to convert rows to columns 'age_group' and assign values from another column 'registered_patients' to those corresponding columns. All data is open source.

tabulation_1

Solution

  • Below worked for me.

    df_ni_f.fillna('', inplace=True)

df_f = df_ni_f.groupby(['practice', 'lsoa_code', 'lsoa_name', 'la_code', 'la_name', 'sex_', 'mdm_decile', 'age_group'], as_index=False).agg({'registered_patients': ' '.join})
df_ff = df_f.pivot(index = ['practice', 'lsoa_code', 'lsoa_name', 'la_code', 'la_name', 'mdm_decile', 'sex_'], columns = ['age_group'], values = ['registered_patients'])

df_ff = pd.DataFrame(df_ff).reset_index()

df_ff.columns = df_ff.columns.get_level_values(0)

df_ff.head()