I am having issues with Pandas between() function. I have datetime.timestamp values and am checking if times occur within a range of datetime.time values.
It works if my range Start time is less than End time within a given day.
import pandas as pd
dProd=[{ 'Time' : '2021-10-20 20:32:00'},
{'Time' : '2021-10-21 11:50:00'},
{'Time' : '2022-01-11 20:10:00'}]
dfProd = pd.DataFrame(dProd)
dfProd['Time'] = pd.to_datetime(dfProd['Time'])
dfProd['Pure_Time'] = dfProd['Time'].dt.time
dRange=[{'Start': '11:45', 'End' :'20:20'}]
dfRange = pd.DataFrame(dRange)
dfRange['Start'] = pd.to_datetime(dfRange['Start'],format= '%H:%M' ).dt.time
dfRange['End'] = pd.to_datetime(dfRange['End'],format= '%H:%M' ).dt.time
dfProd['Pure_Time'].between(dfRange['Start'].iloc[0],dfRange['End'].iloc[0])
Output is as I would expect, between works fine.
0 False
1 True
2 True
But if my time range goes "over the end of the day" nothing seems to ever return True
dRange=[{'Start': '20:00', 'End' :'2:00'}]
dfRange = pd.DataFrame(dRange)
dfRange['Start'] = pd.to_datetime(dfRange['Start'],format= '%H:%M' ).dt.time
dfRange['End'] = pd.to_datetime(dfRange['End'],format= '%H:%M' ).dt.time
dfProd['Pure_Time'].between(dfRange['Start'].iloc[0],dfRange['End'].iloc[0])
Output is always False for all values.
0 False
1 False
2 False
I would expect 0 and 2 to be True as they are between 22:00 and 2:00 going "around" the 24 hour mark. Is there a way to achieve this? I understand no integer would ever be between(4,-1) but I would expect datetime.time to work differently.
>Solution :
Use DataFrame.between_time. You will need to set your datetime64[ns] values to the Index, but then this allows for you to slice "over" the end of a day without issue. With this method you don’t even need to create the ‘Pure_Time’ column.
dfProd.set_index('Time').between_time('20:00', '2:00')
# Pure_Time
#Time
#2021-10-20 20:32:00 20:32:00
#2022-01-11 20:10:00 20:10:00