CODEWITHSUNDEEP
pythonpandaslistdictionary
piyush daga
piyush daga

Reputation: 501

Pandas GroupBy a single column and display multiple columns as value counts

Example 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

I would like to group by id and display the counts of each element as a value.

What I have tried so far:

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

Also:

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

But I can't figure out how to do the same for multiple columns.

Example:

# 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

Expected Output:

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

Any questions or further suggestions will be helpful.

Upvotes: 1

Views: 3976

Answers (2)

Joe
Joe

Reputation: 12417

Answer before the edit of the question with the expected output

If I understood you correctly, you could do in this way:

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

Output:

id  gender  category
1   Men     formal      1
            western     2
    Women   western     1
2   Women   formal      2
3   Men     casual      1
    Women   casual      1

Upvotes: 0

jezrael
jezrael

Reputation: 862761

Use GroupBy.agg with value_counts and converting to dict:

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

Or:

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}

If need new columns filled with lists again use 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]

Using value_counts with multiple columns is problem, because is created second level of MultiIndex with values of both columns:

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

Upvotes: 3

Related Questions