CODEWITHSUNDEEP
pythonpandasdataframe
caston1414
caston1414

Reputation: 35

Finding frequency of rows in a dataframe that meet conditions

I have a dataframe in the form of:

Date    Time  station   num_bikes  
1     3.02    Girwood   0                        
2     4.10    Fraser    0                           
3     10.10   Carslile  0
4     10.10   Girwood   5
5     7.46    Fraser    0
6     3.02    Girwood   0

I want to know the time and the number of occurances that the number of bikes is zero. In the form of:

Time  Station Occurrences 
3.02   Girwood   2   
9.05   Girwood   1  
4.10   Fraser    1                                  
7.46   Fraser    1  
10.10  Carslile  1

Upvotes: 0

Views: 186

Answers (2)

Andy L.
Andy L.

Reputation: 25269

Try something different using pd.crosstab with direct column name Occurences

df1 = df.loc[df.num_bikes.eq(0)]

pd.crosstab([df1.Time, df1.station], 'Occurrences').reset_index()

Out[996]:
    Time   station  Occurrences
0   3.02   Girwood            2
1   4.10    Fraser            1
2   7.46    Fraser            1
3  10.10  Carslile            1

Upvotes: 1

ALollz
ALollz

Reputation: 59579

Filter to only those rows that satisfy your condition and then groupby + size. Using a NamedAgg cleans up the syntax a bit. You can take the size of any column, so I arbitrarily chose 'Time'.

(df[df.num_bikes.eq(0)]
   .groupby(['Time', 'station'])
   .agg(occurences=('Time', 'size'))
   .reset_index())

    Time   station  occurences
0   3.02   Girwood           2
1   4.10    Fraser           1
2   7.46    Fraser           1
3  10.10  Carslile           1

Upvotes: 1

Related Questions