Selecting one date at a time from a resampled dataframe

Question

I have a dataframe of tick data, which i have resampled into minute data. doing a vanilla

df.resample('1Min').ohlc().fillna(method='ffill')

super easy.

I now need to iterate over that resampled dataframe each day at a time, but i cant figure out the best way to do it.

ive tried taking my 1min resampled dataframe and then resampling that for "1D" and then converting that to a list to iterate over and filter, but that gives me a list of:

Timestamp('2011-09-13 00:00:00', freq='D')

objects, and it wont let me slice a dataframe based on that.

this seems like it would be something easy, but i just cant find the answer. thanks-

#sample data_1m dataframe
data_1m.head()


                     open  high  low  close
timestamp                                  
2011-09-13 13:53:00   5.8   6.0  5.8    6.0
2011-09-13 13:54:00   5.8   6.0  5.8    6.0
2011-09-13 13:55:00   5.8   6.0  5.8    6.0
2011-09-13 13:56:00   5.8   6.0  5.8    6.0
2011-09-13 13:57:00   5.8   6.0  5.8    6.0
...

#i want to get everything for date 2011-09-13 im trying

days_in_df = data_1m.resample('1D').ohlc().fillna(method='ffill').index.to_list()


data_1m.loc[days_in_df[0]]

KeyError: Timestamp('2011-09-13 00:00:00', freq='D')

can you add a sample of your data frame? also, can you add a column of the minute of the timestamp, and the day of the timestamp and then loop on the `day` column? — MattR, Jul 10 '19 at 19:17
so, you want to pass a date to `loc` and return all of the values that happen during that day, yes? — MattR, Jul 10 '19 at 19:36
Possible duplicate of [Filtering Pandas DataFrames on dates](https://stackoverflow.com/questions/22898824/filtering-pandas-dataframes-on-dates). I would look at this post. Just pass whatever dates you want through `loc` — MattR, Jul 10 '19 at 19:43
i dont really care how it gets done i just need to iterate over the dates one at a time, this was my best guess, it can be some other way, i just need to be able to basically break this df up into individual date sub dataframes — user1772250, Jul 10 '19 at 19:44
You may want to post an expected result. I have three things that I think you could be asking, other Stack Overflow user could have more as well. — MattR, Jul 10 '19 at 19:50
i saw that other post, i want a single date at a time, not a range of dates. that post (as i indicated, doesnt work) — user1772250, Jul 10 '19 at 19:55
what happens if you use the same dates? `df.loc['2011-09-13':'2011-09-13']` Does that fit your needs? — MattR, Jul 10 '19 at 19:56

score 0 · Answer 1 · answered Jul 10 '19 at 23:11

Here's my two cents. I don't resample the data so much as adding another index level to the frame:

data_1m = data_1m.reset_index()
data_1m['date'] = data_1m['timestamp'].astype('datetime64[D]')
data_1m = data_1m.set_index(['date', 'timestamp'])

And to select an entire day:

data_1m.loc['2011-09-13']

Selecting one date at a time from a resampled dataframe

1 Answers1