Get Row Value of Maximum Count After Applying Group by in Pandas

Get row value of maximum count after applying group by in pandas

1)

required_df = veg_df.groupby(['vegetable','size'], as_index=False)['market'].count()\
         .sort_values(by=['vegetable', 'market'])\
         .drop_duplicates(subset='vegetable', keep='last')

2)

merged_df = veg_df.merge(required_df, on='vegetable')
cols = ['size_x', 'market_x', 'vegetable', 'size_y']
dict_renaming_cols = {'size_x': 'size', 
                      'market_x': 'market',
                      'size_y': 'confirm_availability'}
merged_df = merged_df.loc[:,cols].rename(columns=dict_renaming_cols)

Get max of count() function on pandas groupby objects

You can do with max on level=0

matches.groupby(["FeatureID", "gene"]).count().max(level=0)

If keep both level

df.groupby(["FeatureID", "gene"]).count().sort_values().groupby(level=0).tail(1)

Get the max value from each group with pandas.DataFrame.groupby

From your original DataFrame you can .value_counts, which returns a descending count within group, and then given this sorting drop_duplicates will keep the most frequent within group.

df1 = (df.groupby('col1')['col2'].value_counts()
         .rename('counts').reset_index()
         .drop_duplicates('col1'))

  col1 col2  counts
0    A   AY       3
2    B   BX       3
4    C   CX       5

Extract row with maximum value in a group pandas dataframe

You can use first

In [14]: df.groupby('Mt').first()
Out[14]: 
   Sp  Value  count
Mt                 
s1  a      1      3
s2  c      3      5
s3  f      6      6

Update

Set as_index=False to achieve your goal

In [28]: df.groupby('Mt', as_index=False).first()
Out[28]: 
   Mt Sp  Value  count
0  s1  a      1      3
1  s2  c      3      5
2  s3  f      6      6 

Update Again

Sorry for misunderstanding what you mean. You can sort it first if you want the one with max count in a group

In [196]: df.sort('count', ascending=False).groupby('Mt', as_index=False).first()
Out[196]: 
   Mt Sp  Value  count
0  s1  a      1      3
1  s2  e      5     10
2  s3  f      6      6

---

Related Topics