CODEWITHSUNDEEP
python-3.xpandas
Yog
Yog

Reputation: 825

count in between values in a column on condition say I need to count for only specific value and group them by another column using python pandas

I have the following input

Input:

Bus Fare Startcity
56  98   sathy
95  85   sathy
98  95   chennai
85  92   chennai
56  75   chennai
56  83   chennai

I have to count it by fare >=90 and fare<=98 and groupby "Startcity"

Output 1:

Fare Startcity 
1    Sathy 
2    Chennai

Also to calculate averageif fare >=90 and fare<=98 and groupby "Startcity"

Output 2:

Fare   Startcity
98     Sathy 
93.5   Chennai

Upvotes: 1

Views: 30

Answers (1)

jezrael
jezrael

Reputation: 862571

If want count number of rows per condition per groups create boolean mask by ge (<=) and count True values by sum:

df1 = df['Fare'].ge(90).groupby(df['Startcity']).sum().astype(int).reset_index()
print (df1)
  Startcity  Fare
0   chennai     2
1     sathy     1

If want check between with filtering use:

df = df[df['Fare'].between(90, 98)].groupby('Startcity')['Fare'].mean().reset_index()
print (df)
  Startcity  Fare
0   chennai  93.5
1     sathy  98.0

Or if need also 0 for non matched groups:

df3=df.groupby('Startcity')['Fare'].apply(lambda x: x[x.between(90, 98)].mean()).reset_index()
print (df3)
  Startcity  Fare
0   chennai  93.5
1     sathy  98.0

Upvotes: 2

Related Questions