CODEWITHSUNDEEP
pythonpandasdataframe
addicted
addicted

Reputation: 3051

python - max value of aggregated count from 2 groupby columns

I want to get the max value out of row count of columns name and hours below:

import pandas as pd

hours = [8,8,9,9,
         8,9,10,10,
         8,9,12,12,
         10,11,12,12]
names = ['A', 'B', 'C', 'D'] * 4

df = pd.DataFrame({'names': names,
                   'hours', hours})

My expected output:

names  hours  count
A      8      3
B      9      2
C      12     2
D      12     2

What I have tried:

# This will get me the aggregated count based on names and hours
df.groupby(['names', 'hours']).size().reset_index(name='count')

# result
names   hours   count
A       8       3
        10      1
B       8       1
        9       2
        11      1
C       9       1
        10      1
        12      2
D       9       1
        10      1
        12      2

# To get the max value for each names & hours group (But failed)
df.groupby(['names', 'hours']).size().reset_index(name='count').\
groupby(['names','hours']).max()
# I get the same result as I got above

Upvotes: 0

Views: 60

Answers (3)

Allen Qin
Allen Qin

Reputation: 19957

Another way using groupby and value_counts:

(
    df.groupby('names')
    .apply(lambda x: x.hours.value_counts().nlargest(1))    
    .reset_index()
    .set_axis(['names','hours','count'], axis=1, inplace=False)
)

Out[249]: 
  names  hours  count
0     A      8      3
1     B      9      2
2     C     12      2
3     D     12      2

Upvotes: 0

satomacoto
satomacoto

Reputation: 11963

df.groupby(['names', 'hours']) \
  .size() \
  .reset_index(name='count') \
  .sort_values('count', ascending=False) \
  .drop_duplicates(['names'])

Upvotes: 1

zipa
zipa

Reputation: 27879

How about this:

grouped = df.groupby(['names', 'hours']).size().reset_index(name='count')
final = df.loc[df.groupby(['names'])['count'].transform(max) == df['count']]
final
#names  hours  count
#A      8      3
#B      9      2
#C      12     2
#D      12     2

Upvotes: 1

Related Questions