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

user_14281607 0
Viewed 34

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
pandas explode dataframe group-by python
3 Answers

Can you try the following:

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

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
user_100359850

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)
user_87794870
Related