How to use pivot_table or unstack to create new data frame

user_16460703 0
Viewed 43

I am very new to Python, so please do help me. I have a data frame as below:
enter image description here

and I want to unstack/pivot it to below: enter image description here

I tried different methods, but I am not getting the desired result.

I first tried groupby with only 2 values for trial, but this doesn't work for what I want and it fills with NaN:

newdf = df.groupby(['ACCIDENT_NO', 'SEX'])['Age_Group'].value_counts().unstack()

I also tried pivot_table:

new_1 = new_df.pivot_table(index=['ACCIDENT_NO'], columns= [ 'SEX','Age_Group'], aggfunc=len, fill_value=0)

This at least fills the zero but here SEX becomes the main columns and the age groups are subdivided under it. I dont care about any subdivisions or anything ...I just want to unstack the categories as different columns as shown in the desired image above.

pivot python
1 Answers

Try this one-liner using get_dummies and groupby:

df = pd.get_dummies(df, columns=df.columns[1:], prefix='', prefix_sep='').groupby('ACCIDENT_NO', as_index=False).sum()

And now printing df would return the desired result.

user_87083640
Related