pandas - select row of maximum value_counts

Question

I have this simple dataframe df:

User,C,G
111,ar,1
112,es,1
112,es,1
112,es,2
113,ca,2
113,ca,3
113,ca,3
114,en,4

I grouped that by:

result = df.groupby(['User','G'])['C'].value_counts()

obtaining:

User  G    
111   1  ar    1
112   1  es    2
      2  es    1
113   2  ca    1
      3  ca    2
114   4  en    1

My goal is then to keep only the rows with the maximum value_count per group, so that the resulting dataframe appears:

User  G    
111   1  ar    1
112   1  es    2
113   3  ca    2
114   4  en    1

I found also this question related to a similar issues, but I can't figured out how to apply that method in my case.

Jianxun Li · Accepted Answer · 2015-08-26 11:03:01Z

2

You can first create a mask to indicate whether a particular row is maximum in its group.

mask = result.groupby(level='User').apply(lambda g: g == g[g.idxmax()])
mask

User  G    
111   1  ar     True
112   1  es     True
      2  es    False
113   2  ca    False
      3  ca     True
114   4  en     True
dtype: bool

And then, select using this boolean mask

result[mask]

User  G    
111   1  ar    1
112   1  es    2
113   3  ca    2
114   4  en    1
dtype: int64

answered Aug 26, 2015 at 11:03

Jianxun Li

24.9k10 gold badges64 silver badges78 bronze badges

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

1 Comment

Fabio Lamanna Over a year ago

What about multiple equal occurrences? Is there a way to select only single maximum values? I mean, sometimes can happen that the maximum values is not unique...

Soudipta Dutta · Accepted Answer · 2025-05-23 14:02:48Z

Years later :

import pandas as pd

data_rows = [
    {'User': 111, 'C': 'ar', 'G': 1},
    {'User': 112, 'C': 'es', 'G': 1},
    {'User': 112, 'C': 'es', 'G': 1}, 
    {'User': 113, 'C': 'es', 'G': 2},
    {'User': 113, 'C': 'ca', 'G': 2},
    {'User': 113, 'C': 'ca', 'G': 3},
    {'User': 113, 'C': 'ca', 'G': 3}, 
    {'User': 114, 'C': 'en', 'G': 4},
]
df = pd.DataFrame(data_rows)

'''
 User     C  G
0   111  ar  1
1   112  es  1
2   112  es  1
3   113  es  2
4   113  ca  2
5   113  ca  3
6   113  ca  3
7   114  en  4
'''

res = df.groupby(['User','G'])['C'].value_counts() 
'''
User  G  C 
111   1  ar    1
112   1  es    2
113   2  ca    1
         es    1
      3  ca    2
114   4  en    1
Name: count, dtype: int64
'''

res = res.loc[res.groupby('User').idxmax()]
print(res)
'''
User  G  C 
111   1  ar    1
112   1  es    2
113   3  ca    2
114   4  en    1
Name: count, dtype: int64
'''

Collectives™ on Stack Overflow

pandas - select row of maximum value_counts

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related