how to calculate number of datapoints within a given time interval?

Question

I have a dataframe with one column which is a timestamp. I have converted that column to a datetime object using pandas to_datetime() method. However what I want is to count, for each time stamp, the number of timestamps that fall between this timestamp and prior 15 minutes.

For example: If a timestamp is (2018,7,6,13,55) [year,month, day, hour, minute format], then I want to count the number of timestamps (or datapoints) that fall between this timestamp and the timestamp (2018,7,6,13,40).

It is easy to see what needs to be done, but it involves repeatedly going back and forth over the dataset. I have close to 750,000 datapoints and what I am thinking is not efficient.

How can I do it efficiently?

Any suggestion/hint would be highly appreciated.

Thanks.

Answer 1

The answer given by @Aditya is very good and educational despite the fact that the how is deprecated. So the right answer would be as follows:

df.resample("15T").agg({'count':'sum'})

Answer 2

You can do this doing resample.

• First we should index on the timestamp column, if you have not already done so:

  df.set_index('time', inplace=True)

• The we will add a temporary column(you need something to aggregate on):

  df['count'] = 1

• And finally resample as needed:

  df.resample('15T', how='sum')

Or

Since You can subtract them easily thanks to Pandas; let's say you have sorted your time-stamps, now you can easily iterate and use isin combined with pd.timedelta() ...

Something like this should probably work since

current_time<=current_time+timedelta(15 mins), you can count then using isin and .size() etc...