1

I have a datetime column and need to find those records where the time is between 9:30 to 17:00 of any day. I am not concerned about the date, but need to filter data on the basis of time.

2018-12-28 10:53:24.950
2018-12-28 10:53:55.010
2019-01-02 16:48:31.593
2019-01-02 16:48:31.593
2019-01-02 16:48:31.593

I am using the following command to extract the hours.

df1['hour_of_timestamp'] = df1['User_date'].dt.hour
David Buck
  • 3,752
  • 35
  • 31
  • 35
Sunil Raperia
  • 129
  • 8
  • Does this answer your question? [How to select rows within a pandas dataframe based on time only when index is date and time](https://stackoverflow.com/questions/13221218/how-to-select-rows-within-a-pandas-dataframe-based-on-time-only-when-index-is-da) – David Buck Mar 19 '20 at 07:40
  • Kind of, but not getting the results as it errors out – Sunil Raperia Mar 19 '20 at 08:08
  • If you gave a [reproducible example](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) of your DataFrame it would be easier to test. – David Buck Mar 19 '20 at 09:36

3 Answers3

1

Pandas does have a built in method, between_time() to select rows within a certain time period but it only works in the datetime object is the index (or, recently, column).

As you already have a datetime column, you could extract the rows like this (adapting the example from here):

import pandas as pd

data = [["20090102 04:51:00", 89.9900, 89.9900, 89.9900, 89.9900, 100], ["20190102 05:36:00", 90.0100, 90.0100, 90.0100, 90.0100, 200], ["20090102 05:44:00", 90.1400, 90.1400, 90.1400, 90.1400, 100], ["20090102 05:50:00", 90.0500, 90.0500, 90.0500, 90.0500, 500], ["20090102 05:56:00", 90.1000, 90.1000, 90.1000, 90.1000, 300], ["20090102 05:57:00", 90.1000, 90.1000, 90.1000, 90.1000, 200]]

# Building sample dataframe with Datetime column
df = pd.DataFrame(data)
df.columns = ["Datetime", "1", "2", "3", "4", "5"]
df['Datetime'] = pd.to_datetime(df['Datetime'], format="%Y%m%d %H:%M:%S")

# Extract rows with datetime matching index range
print(df.set_index("Datetime").between_time('5:30:00', '5:45:00'))

This outputs just the records between the time range.

                         1      2      3      4    5
Datetime                                            
2019-01-02 05:36:00  90.01  90.01  90.01  90.01  200
2009-01-02 05:44:00  90.14  90.14  90.14  90.14  100
David Buck
  • 3,752
  • 35
  • 31
  • 35
0

You can parse your dates with d = datetime.datetime.fromisoformat(date_str). And then you can inspect d.hour and d.minute to make your decision.

Chris
  • 2,461
  • 1
  • 23
  • 29
0

If your dates are always in the format YYYY-MM-DD then why not just slice your string like so to get the times:

time_str = date_str[10:].strip()

Then you can just sort your list of times with sorted(time_list) and look for times between 9:30 and 17:00.

Bob Smith
  • 220
  • 4
  • 21