[FIXED] Assign a column value to a pandas dataframe based on a datetime range

Issue

I have a dictionary that contains a label as the key and a start and end datetime in a list as the value.

d = {'A': [2020-01-01 00:00:00+00:00, 2024-01-24 00:00:00+00:00]}

I have a dataframe containing a time column. I want to assign a label to the dataframe if the datetime in the time column is within the range of the start and end datetimes from the dictionary. It would look like this when completed.

ID  time                       Label
1   2024-01-23 00:00:00+00:00  A

If I was dealing with just dates, I could use the following code:

for k, (s, e) in d.items():
    df.loc[df["time"].isin(pd.date_range(s, e)), 'Label'] = k

However, the pd.date_range function does not work for datetimes. How would I modify this code to be a time range instead of a date range?

Solution

You can use pd.Series.between method instead of pd.date_range and isin

here is an example

import pandas as pd

#dict
d = {'A': ['2020-01-01 00:00:00+00:00', '2024-01-24 00:00:00+00:00']}

# Let's convert strings to datetime
for k, (start, end) in d.items():
  d[k] = [pd.to_datetime(start), pd.to_datetime(end)]

#df
df = pd.DataFrame({
'ID': [1, 2, 3],
'time': pd.to_datetime(['2024-01-23 00:00:00+00:00', '2025-01-23 
 00:00:00+00:00', '2019-01-23 00:00:00+00:00'])
 })

# Assign labels
for k, (s, e) in d.items():
   df.loc[df["time"].between(s, e), 'Label'] = k

print(df)

Answered By - Ahmed Sayed