Explode values of column into columns and group by another column while showing values of another co-CodePudding

I have a dataframe df

id	value	category
AB	2	small
BC	3	big
AB	4	small
AB	5	big
BC	6	small
BC	2	small
BC	4	big
AB	8	big

I want the resulting dataframe to calculate average values in such a way that the resulting dataframe looks like

ID	small	big
AB	2	6
BC	4	3.5

CodePudding user response：

Can you try the following:

df.groupby(['id', 'category']).mean().unstack(fill_value=0)

CodePudding user response：

Try .pivot_table:

x = df.pivot_table(
    index="id", columns="category", values="value", aggfunc="mean"
).reset_index()

x.columns.name = None
print(x)

Prints:

   id  big  small
0  AB  6.5    3.0
1  BC  3.5    4.0

CodePudding user response：

You might use Pivot. So you can define which column's values to be converted into separate columns columns=['category']. And which column should be used to fill respective result values='value', based on the aggregation function you want: aggfunc=np.mean

table = pd.pivot_table(df, values='value', index=['id'],
                        columns=['category'], aggfunc=np.mean)