I have a table with Multiindex getting by groupby():
new_data=data.groupby(['id','category']).sum('amount')
new_data
Output looks like that (just a random table with the same form as mine):
# amount
#id category
1 2 12
3 1
2 1 45
3 56
Unique Id has some amount in each category.
But I need to get a table where is amount presented for each 'id' for each category in the same columns.
Something like that:
#id amount_category_1 amount_category_2 amount_category_3
1 0 12 1
2 45 0 56
Assuming that you reset the index of your dataframe and have it in the form:
id category amount
0 1 1 45
1 1 3 34
2 2 2 36
3 2 3 24
you can pivot your dataframe and rename your columns:
df = df.pivot_table(index='id',columns='category',values='amount').rename_axis(None, axis=1).reset_index()
df = df.rename(columns={c: 'category_'+ str(c) for c in df.columns if c not in ['id']})
to get somethng like this:
id category_1 category_2 category_3
0 1 45.0 NaN 34.0
1 2 NaN 36.0 24.0
Change NaN using df.fillna(0) to get
id category_1 category_2 category_3
0 1 45.0 0.0 34.0
1 2 0.0 36.0 24.0