How to count values between timestamps in a pandas dataframe

user_11797148 0
Viewed 341

I have a Pandas DataFrame looking like this:

>>> df
       Start_Time           End_Time
0      2014-10-16 15:05:17  2014-10-16 17:13:14
1      2014-10-16 14:56:37  2014-10-16 15:07:17
2      2014-10-16 14:25:16  2014-10-16 18:06:17
...

Now, I have another DataFrame containing several timestamps:

>>> times
       Time           
0      2014-10-16 15:17:17
1      2014-10-16 14:53:37
2      2014-10-16 14:26:16
...

What I want to receive in the end is a count of the rows, where Start_Time < Times < End_Time:

>>> times
       Time                 Count          
0      2014-10-16 15:17:17  1
1      2014-10-16 15:05:37  2
2      2014-10-16 14:26:16  1
...

Of course I could do it by iterating through times and make sub_dfs using loc:

  ls_len = []    
  for index, row in times.iterrows():
     sub_df = df.loc[(df['Start_Time']<row['Time']) & (df['End_Time']>row['Time'])]
     ls_len.append(len(sub_df))
  times['Count'] = ls_len

But this is very time consuming and feels non-optimal. Is there a way to do this operation without iterating? Thank you guys very much in advance!

pandas count timestamp python
1 Answers
  #This is more optimal than looping        
  def count_val(x):
     sub_df = df.loc[(df['Start_Time']<x['Time']) & (df['End_Time']>x['Time'])]
     count = len(sub_df)
     return count

  times['count'] = times.apply(count_val, axis=1)
user_144426870
Related