Filtering out specific hours each day in pandas

Question

Given a dataset where each row represent a hour sample, that is each day has 24 entries with the following index set

...
2020-10-22T20:00:00
2020-10-22T21:00:00
2020-10-22T22:00:00
...
2020-10-22T20:00:00
2020-10-22T20:00:00
2020-10-22T20:00:00
...

Now I want to filter out so that for each day only the hours between 9am-3pm is left.

The only way I know would be to iterate over the dataset and filter each row given a condition, however knowing pandas there is always some trick for this kind of filtering that does not involve explicit iterating.

Answer 1

You can use the aptly named pd.DataFrame.between_time method. This will only work if your dataframe has a DatetimeIndex.

Data Creation

date_index = pd.date_range("2020-10-22T20:00:00", "2020-11-22T20:00:00", freq="H")
values = np.random.rand(len(dates), 1)

df = pd.DataFrame(values, index=date_index, columns=["value"])
print(df.head())
                        value
2020-10-22 20:00:00  0.637542
2020-10-22 21:00:00  0.590626
2020-10-22 22:00:00  0.474802
2020-10-22 23:00:00  0.058775
2020-10-23 00:00:00  0.904070

Method

subset = df.between_time("9:00am", "3:00pm")

print(subset.head(10))
                        value
2020-10-23 09:00:00  0.210816
2020-10-23 10:00:00  0.086677
2020-10-23 11:00:00  0.141275
2020-10-23 12:00:00  0.065100
2020-10-23 13:00:00  0.892314
2020-10-23 14:00:00  0.214991
2020-10-23 15:00:00  0.106937
2020-10-24 09:00:00  0.900106
2020-10-24 10:00:00  0.545249
2020-10-24 11:00:00  0.793243

Answer 2

import pandas as pd

# sample data (strings)
data = [f'2020-10-{d:02d}T{h:02d}:00:00' for h in range(24) for d in range(1, 21)]

# series of DT values
ds = pd.to_datetime(pd.Series(data), format='%Y-%m-%dT%H:%M:%S')

# filter by hours
ds_filter = ds[(ds.dt.hour >= 9) & (ds.dt.hour <= 15)]