在Pandas中使用多个groupby创建新列

Question

我有一个数据集，我想在其中：

1. 按位置和框分组，并对

进行计数。

数据

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  

所需

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

正在做什么

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

如有任何建议，将不胜感激。

Answer 1

尝试：

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

指纹：

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

编辑：

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)

指纹：

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