python - Pandas GroupBy 单列并显示多列作为值计数

Question

示例 df

retailer_dict = {
    'id': [1, 2, 3, 1, 1, 3],
    'gender': ['Men', 'Women', 'Men', 'Women', 'Men', 'Women'],
    'category': ['western', 'formal', 'casual', 'western', 'formal', 'casual']
}
df = pd.DataFrame(retailer_dict); df

# Output
    id  gender  category
0   1   Men     western
1   2   Women   formal
2   3   Men     casual
3   1   Women   western
4   1   Men     formal
5   3   Women   casual

我想按 id 分组并将每个元素的计数显示为一个值。

到目前为止我尝试了什么:

df.groupby('id')['gender'].value_counts()

# Output
id  gender
1   Men       2
    Women     1
2   Women     1
3   Men       1
    Women     1
Name: gender, dtype: int64

还有:

df.groupby('id')['gender'].apply(list)

但我不知道如何对多列执行相同的操作。

例子:

# gives AttributeError
df.groupby('id')[['gender', 'category']].value_counts()

# Provides unuseful output
df.groupby('id')[['gender', 'category']].apply(list)
# Output
id
1    [gender, category]
2    [gender, category]
3    [gender, category]
dtype: object

预期输出:

id  gender                category
1   {Men: 2, Women:1}     {western: 2, formal:1} 
2   {Women:1}             {formal:1}
3   {Men: 1, Women:1}     {casual: 2}

任何问题或进一步的建议都会有所帮助。

Answer 1

使用GroupBy.agg使用 value_counts 并转换为 dict:

print (df.groupby('id')['gender', 'category'].agg(lambda x: x.value_counts().to_dict()))

或者:

from collections import Counter

print (df.groupby('id')['gender', 'category'].agg(lambda x: Counter(x)))

---

                    gender                     category
id                                                     
1   {'Men': 2, 'Women': 1}  {'western': 2, 'formal': 1}
2             {'Women': 1}                {'formal': 1}
3   {'Women': 1, 'Men': 1}                {'casual': 2}

如果需要再次用列表填充新列，请使用 agg:

print (df.groupby('id')['gender', 'category'].agg(list))
               gender                    category
id                                               
1   [Men, Women, Men]  [western, western, formal]
2             [Women]                    [formal]
3        [Men, Women]            [casual, casual]

对多列使用 value_counts 是有问题的，因为用两列的值创建了 MultiIndex 的第二层:

print (pd.concat([df.groupby('id')['gender'].value_counts(),
                  df.groupby('id')['category'].value_counts()]))

id  gender 
1   Men        2
    Women      1
2   Women      1
3   Men        1
    Women      1
1   western    2
    formal     1
2   formal     1
3   casual     2
dtype: int64