In pandas, can asfreq be used to select the specific times sampled?

user_10705848 0
Viewed 175

I have some minutely time series data in a pandas DataFrame which looks like:

                      last   returns
time                                
1990-01-02 09:47:00  21.80       NaN
1990-01-02 09:48:00  21.82  0.000917
1990-01-02 09:49:00  21.82  0.000000
1990-01-02 09:50:00  21.80 -0.000917
1990-01-02 09:51:00  21.86  0.002752
1990-01-02 09:52:00  21.87  0.000457
1990-01-02 09:53:00  21.82 -0.002286
1990-01-02 09:54:00  21.83  0.000458
1990-01-02 09:55:00  21.85  0.000916
...                    ...       ...

I want to filter it to leave only the data on minutes divisible by 5 as shown below. To get this, I used the following code:

df.truncate(before='1990-01-02 09:50:00').asfreq('5T')

                      last   returns
time                                
1990-01-02 09:50:00  21.80 -0.000917
1990-01-02 09:55:00  21.85  0.000916
1990-01-02 10:00:00  21.79 -0.001375
1990-01-02 10:05:00  21.84 -0.000458
1990-01-02 10:10:00  21.91  0.000914
1990-01-02 10:15:00  21.88  0.000000
1990-01-02 10:20:00  21.87 -0.000457
1990-01-02 10:25:00  21.92 -0.000456
1990-01-02 10:30:00  21.93  0.000913
...                    ...       ...

This code obviously does not generalize well. Is there functionality within pandas to allow me to get the 5-minutely data for a generic DataFrame? Note, the following code gives the following output. It looks at the first available time and pulls an entry every 5-minutes after that time.

df.asfreq('5T')

                      last   returns
time                                
1990-01-02 09:47:00  21.80       NaN
1990-01-02 09:52:00  21.87  0.000457
1990-01-02 09:57:00  21.85  0.000458
1990-01-02 10:02:00  21.84  0.001835
1990-01-02 10:07:00  21.85 -0.000914
1990-01-02 10:12:00  21.88  0.000915
1990-01-02 10:17:00  21.88  0.000915
1990-01-02 10:22:00  21.92  0.000913
1990-01-02 10:27:00  21.90  0.000914
...                    ...       ...
pandas time-series dataframe date python
2 Answers

One way is to math it:

print (df[df.index.minute%5==0])

                      last   returns
time                                
1990-01-02 09:50:00  21.80 -0.000917
1990-01-02 09:55:00  21.85  0.000916
user_92844230

resample + asfreq

So long as the frequency evenly divides a day, or is evenly divisible by a day:

The bins of the grouping are adjusted based on the beginning of the day of the time series starting point.

df.resample('5T').asfreq()

                      last   returns
time                                
1990-01-02 09:45:00    NaN       NaN
1990-01-02 09:50:00  21.80 -0.000917
1990-01-02 09:55:00  21.85  0.000916

Because of this, you can unambiguously change the start point with the offset parameter if pandas >= '1.1.0'.

df.resample('5T', offset='2T').asfreq()

                      last   returns
time                                
1990-01-02 09:47:00  21.80       NaN
1990-01-02 09:52:00  21.87  0.000457
user_43333590
Related