0
$\begingroup$

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.

$\endgroup$
0
$\begingroup$

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...

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.