CODEWITHSUNDEEP
python-2.7pandasdataframegroup-by
dlwlrma
dlwlrma

Reputation: 828

How to assign python groupby results to columns?

I have a data "last_3_days" like this:

enter image description here

Then I groupby ['user_id','sku_id','type'] and count the number of each 'type' of each ['user_id','sku_id'] pair.

How can I assign the groupby results to each ['user_id','sku_id'] pair? For each ['user_id','sku_id'] pair, they should have these additional columns:['type1_count','type2_count',...,'type6_count'].
Each column means the count of that 'type'. There are 6 types in the 'type' column.

Update: @gereleth's answer is what I want. But the result is like this:

enter image description here

How to change the above to this?

enter image description here

Upvotes: 0

Views: 817

Answers (1)

gereleth
gereleth

Reputation: 2482

I think you want to use unstack on the groupby results.

df = (last_౩_days.groupby('user_id', 'sku_id','type')
        .size().unstack(fill_value=0)
        .add_prefix('type').add_suffix('count'))

Unstack will turn the last level of index into columns. fill_value is the value to use for missing combinations. The names of new columns will be the unique values of type, so the last line renames them into the format you want.

Upvotes: 1

Related Questions