Create new column using multiple groupby's in Pandas

Question

I have a dataset where I would like to:

group by location and box and take a count of the box

Data

ID  location    type    box     status          
aa  NY          no      box55   hey         
aa  NY          no      box55   hi          
aa  NY          yes     box66   hello           
aa  NY          yes     box66   goodbye         
aa  CA          no      box11   hey         
aa  CA          no      box11   hi          
aa  CA          yes     box11   hello           
aa  CA          yes     box11   goodbye         
aa  CA          no      box86   hey         
aa  CA          no      box86   hi          
aa  CA          yes     box86   hello           
aa  CA          yes     box99   goodbye         
aa  CA          no      box99   hey         
aa  CA          no      box99   hi

Desired

location    box count   box     
NY          2           box55   
NY          2           box66   
CA          3           box11   
CA          3           box86   
CA          3           box99

Doing

df['box count'] = df.groupby(['location','box'])['box'].size()

Any suggestion is appreciated.

and what is wrong to your solution?

PTQuoc
– PTQuoc

2022-11-22 23:41:20 +00:00
Commented Nov 22, 2022 at 23:41 — PTQuoc
– PTQuoc, Commented Nov 22, 2022 at 23:41

Andrej Kesely · Accepted Answer · 2022-11-23 00:01:39Z

1

Try:

df = df.groupby(["location", "box"], as_index=False).agg(
    **{"box count": ("box", "size")}
)
print(df)

Prints:

  location    box  box count
0       CA  box11          4
1       CA  box86          3
2       CA  box99          3
3       NY  box55          2
4       NY  box66          2

EDIT:

m = df.groupby(["location"])["box"].nunique()
df = df.groupby(["location", "box"], as_index=False).agg(
    **{
        "box count": (
            "location",
            lambda x: m[x.iat[0]],
        )
    }
)
print(df)

Prints:

  location    box  box count
0       CA  box11          3
1       CA  box86          3
2       CA  box99          3
3       NY  box55          2
4       NY  box66          2

edited Nov 23, 2022 at 0:01

answered Nov 22, 2022 at 23:27

Andrej Kesely

196k15 gold badges60 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lynn Over a year ago

Hi @Andrej the 4 should be a 3 right? We are saying there are 3 distinct boxes in CA location (box11, box86 and box99)

Collectives™ on Stack Overflow

Create new column using multiple groupby's in Pandas

Data

Desired

Doing

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Data

Desired

Doing

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related