How To Drop Rows Based On Timestamp Where Hours Are Not In List

I have a large dataframe (several million rows) where one of my columns is a timestamp (labeled 'Timestamp') in the format 'hh:mm:ss' e.g. '07:00:04'. I want to drop the rows where

Solution 1:

First you can give your DataFrame a proper DatetimeIndex as follows:

dtidx = pd.DatetimeIndex(df['Date'].astype(str) + ' ' + df['Timestamp'].astype(str))
df.index = dtidx

and then use between_time to get the hours between hours 07 and 21 inclusive:

df.between_time('07:00', '22:00')
# returns
                         Date Timestamp   Close
2018-01-02 07:05:00  20180102  07:05:00   12926
2018-01-02 21:05:02  20180102  21:05:02 12925.5
2018-01-03 07:05:07  20180103  07:05:07 12925.8

Solution 2:

Since you mentioned about slicing and someone already mentioned about how to go with it, I would like to introduce you to extracting the hour using dt.hour

First convert your date with type string to date with type datetime:

df['date'] = pd.to_datetime(df['date'])

You can now easily extract the hour part using dt.hour:

df['hour'] = df['date'].dt.hour

You can also extract year, month, second, and so on in a similar way.

Now you can do normal filtering as you would do with other dataframes:

df[(df.hour >= 7) & (df.hour <= 21)]

Solution 3:

I prefer the other answers which work with proper timestamp data types, but since you mentioned trying and failing with a string slicing method, it might be helpful for you to see a solution using string slicing that does work:

df['Hour'] = df['Timestamp'].str.slice(0, 2).astype(int)
df[(df['Hour'] >= 7) & (df['Hour'] <= 21)]

The first line creates a new integer column from the slice of the string which represents the hour, and the second line filters on said new column.

       Date Timestamp      Close  Hour
0  20180102  07:05:00  12925.979     7
1  20180102  21:05:02  12925.479    21
5  20180103  07:05:07  12925.780     7

Solution 4:

My guess would be to use pd.between_time.

df['Timestamp'] = pd.to_datetime(df['Timestamp'])
df.set_index('Timestamp').between_time('07:00:00', '21:59:59')

Timestamp           Date        Close
2019-07-22 07:05:00 20180102    12925.979
2019-07-22 21:05:02 20180102    12925.479
2019-07-22 07:05:07 20180103    12925.78