Pandas groupby multiple columns to compare values-CodePudding

My df looks like this: (There are dozens of other columns in the df but these are the three I am focused on)

Param    Value      Limit  
A        1.50       1
B        2.50       1
C        2.00       2
D        2.00       2.5
E        1.50       2

I am trying to use pandas to calculate how many [Value] that are less than [Limit] per [Param], Hoping to get a list like this:

Param    Count       
A        1
B        1       
C        1       
D        0       
E        0

I've tried with a few methods, the first being

value_count = df.loc[df['Value'] < df['Limit']].count() but this just gives the full count per column in the df.

I've also tried groupby function which I think could be the correct idea, by creating a subset of the df with the chosen columns

df_below_limit = df[df['Value'] < df['Limit']]
df_below_limit.groupby('Param')['Value'].count()

This is nearly what I want but it excludes values below which I also need. Not sure how to go about getting the list as I need it.

CodePudding user response：

Assuming you want the count per Param, you can use:

out = df['Value'].ge(df['Limit']).groupby(df['Param']).sum()

output:

Param
A    1
B    2
C    1
D    0
E    0
dtype: int64

used input (with a duplicated row "B" for the example):

  Param  Value  Limit
0     A    1.5    1.0
1     B    2.5    1.0
2     B    2.5    1.0
3     C    2.0    2.0
4     D    2.0    2.5
5     E    1.5    2.0

as DataFrame

df['Value'].ge(df['Limit']).groupby(df['Param']).sum().reset_index(name='Count')

# or

df['Value'].ge(df['Limit']).groupby(df['Param']).agg(Count='sum').reset_index()

output:

  Param  Count
0     A      1
1     B      2
2     C      1
3     D      0
4     E      0