how to count value in a dataframe column based on filtering

Question

Given this dataframe :

DriverId    time                         SPEED
0           2021-04-16 21:40:00+00:00   58.500000
            2021-04-16 21:41:00+00:00   32.850000
            2021-04-16 21:42:00+00:00   89.633333
            2021-04-16 21:43:00+00:00   88.166667
            2021-04-16 21:44:00+00:00   118.016667
... ... ...
88          2021-04-27 07:30:00+00:00   79.566667
            2021-04-27 07:31:00+00:00   59.383333
            2021-04-27 07:32:00+00:00   89.133333
            2021-04-27 07:33:00+00:00   59.966667
            2021-04-27 07:34:00+00:00   25.72413

i want add column to count number of speed under 40 km/h for each driver so i've tried this :

y[y.SPEED<40].count()

it shows this :

    SPEED    4721
    dtype: int64

and it is not exactly what i want ,the expexted result must be like this :

  DriverId        SPEED         count 
      0            15.20            2
                   32.850000 
                   89.633333
                  88.166667
                  118.016667
... ... ...
88              79.566667          1
                59.383333
                89.133333
                59.966667
                25.72413

my dataframe was a serie which i transform to dataframe

 y.info()
    <class 'pandas.core.frame.DataFrame'>
MultiIndex: 15082 entries, (0, Timestamp('2021-04-16 21:40:00+0000', tz='UTC')) to (88, Timestamp('2021-04-27 07:34:00+0000', tz='UTC'))
Data columns (total 1 columns):
 #   Column  Non-Null Count  Dtype  
---  ------  --------------  -----  
 0   SPEED   15082 non-null  float64
dtypes: float64(1)
memory usage: 922.5 KB

score 1 · Answer 1 · answered Aug 17 '21 at 12:18

1

At first, I would have the DriverId in each row rather than only in the first row of the group and then try the following:

y["Count of speed<40 for given driver"]=[sum((y.Driver==x) & (y["Speed"]<40)) for x in y.Driver]

answered Aug 17 '21 at 12:18

hbstha123

1,260
11
23

thank you himalaya bir shrestha but driverid is an index not a column in my case – girl_of_data Aug 17 '21 at 12:52
you can change index to columns by y.reset_index(inplace = True) – hbstha123 Aug 17 '21 at 13:13

Rinshan Kolayil · Accepted Answer · 2021-08-17T16:13:06.970

df = pd.DataFrame([['0','2021-04-16 21:40:00+00:00',58.500000],
    ['0','2021-04-16 21:41:00+00:00', 32.850000],#FIRST ONE
    ['0','2021-04-16 21:42:00+00:00', 15.633333],#SECOND ONE
    ['0','2021-04-16 21:43:00+00:00', 88.166667],
    ['0','2021-04-16 21:44:00+00:00',118.016667],
    ['88','[2021-04-27 07:30:00+00:00',79.566667],
    ['88','2021-04-27 07:31:00+00:00',59.383333],
    ['88','2021-04-27 07:32:00+00:00',89.133333],
    ['88','2021-04-27 07:33:00+00:00',59.966667],
    ['88','2021-04-27 07:34:00+00:00',25.72413] # THIRD ONE
  ],columns=['driver_id','time','speed'])
df = df.set_index("driver_id")
counts = df[df['speed'] < 40].groupby(["driver_id",],as_index=False).agg(
    count_col=pd.NamedAgg(column="speed", aggfunc="count")
)
merged_Frame = pd.merge(df, counts, on = 'driver_id', how='inner')

Output

driver_id   time                   speed        count_col
0   0   2021-04-16 21:40:00+00:00   58.500000   2
1   0   2021-04-16 21:41:00+00:00   32.850000   2
2   0   2021-04-16 21:42:00+00:00   15.633333   2
3   0   2021-04-16 21:43:00+00:00   88.166667   2
4   0   2021-04-16 21:44:00+00:00   118.016667  2
5   88  [2021-04-27 07:30:00+00:00  79.566667   1
6   88  2021-04-27 07:31:00+00:00   59.383333   1
7   88  2021-04-27 07:32:00+00:00   89.133333   1
8   88  2021-04-27 07:33:00+00:00   59.966667   1
9   88  2021-04-27 07:34:00+00:00   25.724130   1

Reference

Edit

import pandas as pd

df = pd.DataFrame([['0','2021-04-16 21:40:00+00:00',58.500000],
    ['0','2021-04-16 21:41:00+00:00', 32.850000],#FIRST ONE
    ['0','2021-04-16 21:42:00+00:00', 15.633333],#SECOND ONE
    ['0','2021-04-16 21:43:00+00:00', 88.166667],
    ['0','2021-04-16 21:44:00+00:00',118.016667],
    ['88','[2021-04-27 07:30:00+00:00',79.566667],
    ['88','2021-04-27 07:31:00+00:00',59.383333],
    ['88','2021-04-27 07:32:00+00:00',89.133333],
    ['88','2021-04-27 07:33:00+00:00',59.966667],
    ['88','2021-04-27 07:34:00+00:00',25.72413] # THIRD ONE
  ],columns=['driver_id','time','speed'])
df = df.set_index(['driver_id', 'time'])
df['count'] = df[df['speed'] < 40].groupby('driver_id')['speed'].transform('count')

Output

thanks rinshan but in reality my dataframe was a serie after being dataframe driverid and time were an index only speed is a column i will update my post for more explication — girl_of_data, Aug 17 '21 at 12:48

how to count value in a dataframe column based on filtering

2 Answers2