How to filter a pandas DatetimeIndex by day of week and hour in the day

Question

I have a pandas DatetimeIndex and I would like to filter the index by the criterion that the day of the week and hour of the day matches a list. For example, I have of list of tuples indicating valid (day of week, hour, minute) for each TimeStamp:

[(4, 6), (5, 7)]

The final index should only contain date times that are Friday(day_of_week = 4) hour 6 or Saturday(day_of_week = 5) hour 7.

Lets say the input data frame is like:

2016-04-02 06:30:00  1
2016-04-02 06:45:00  2
2016-04-02 07:00:00  3
2016-04-02 07:15:00  4
2016-04-03 07:30:00  5
2016-04-03 07:45:00  6
2016-04-03 08:00:00  7

After the filter, it should be like:

2016-04-02 06:30:00  1
2016-04-02 06:45:00  2
2016-04-03 07:30:00  5

Because I only keep indices whose day of week and hour of the day in the list [(4, 6), (5, 7)]

question is little vague. how does your dataframe look like and what should be the op? — skrubber, Jan 31 '18 at 15:51

score 8 · Answer 1 · answered Jan 31 '18 at 16:41

8

You could store the dayofweek and hour methods from your index in variables, and then use them with iloc to filter:

dayofweek = df.index.dayofweek
hour = df.index.hour

df.iloc[((dayofweek == 4) & (hour == 6)) | ((dayofweek == 5) & (hour == 7))]

answered Jan 31 '18 at 16:41

cmaher

5,100
1
22
34

score 5 · Accepted Answer · answered Jan 31 '18 at 16:23

You should add a column day_of_week and a column hour, then you can filer on this columns.

For example :

df["day_of_week"] = df["date"].dayofweek()
df["hour"] = df["date"].hour()

pd.concat([
    df.loc[df["day_of_week"].isin(x[0]) & df["hour"].isin(x[1])]
    for x in [(4, 6), (5, 7)]
])

Note that I iterate over all your conditions, then I concatenate all the resulting dataframe.

How to filter a pandas DatetimeIndex by day of week and hour in the day

2 Answers2