Take time points, and make labels against datetime object to correlate for things around points

Question

I'm trying to use the usual times I take medication (so + 4 hours on top of that) and fill in a data frame with a label, of being 2,1 or 0, for when I am on this medication, or for the hour after the medication as 2 for just being off of the medication.

As an example of the dataframe I am trying to add this column too,

<bound method NDFrame.to_clipboard of                           id  sentiment  magnitude  angry  disgusted  fearful  \
created                                                                         
2020-05-21 12:00:00     23.0  -0.033333        0.5    NaN        NaN      NaN   
2020-05-21 12:15:00      NaN        NaN        NaN    NaN        NaN      NaN   
2020-05-21 12:30:00      NaN        NaN        NaN    NaN        NaN      NaN   
2020-05-21 12:45:00      NaN        NaN        NaN    NaN        NaN      NaN   
2020-05-21 13:00:00      NaN        NaN        NaN    NaN        NaN      NaN   
...                      ...        ...        ...    ...        ...      ...   
2021-04-20 00:45:00      NaN        NaN        NaN    NaN        NaN      NaN   
2021-04-20 01:00:00      NaN        NaN        NaN    NaN        NaN      NaN   
2021-04-20 01:15:00      NaN        NaN        NaN    NaN        NaN      NaN   
2021-04-20 01:30:00      NaN        NaN        NaN    NaN        NaN      NaN   
2021-04-20 01:45:00  46022.0  -1.000000        1.0    NaN        NaN      NaN   

                     happy  neutral  sad  surprised  
created                                              
2020-05-21 12:00:00    NaN      NaN  NaN        NaN  
2020-05-21 12:15:00    NaN      NaN  NaN        NaN  
2020-05-21 12:30:00    NaN      NaN  NaN        NaN  
2020-05-21 12:45:00    NaN      NaN  NaN        NaN  
2020-05-21 13:00:00    NaN      NaN  NaN        NaN  
...                    ...      ...  ...        ...  
2021-04-20 00:45:00    NaN      NaN  NaN        NaN  
2021-04-20 01:00:00    NaN      NaN  NaN        NaN  
2021-04-20 01:15:00    NaN      NaN  NaN        NaN  
2021-04-20 01:30:00    NaN      NaN  NaN        NaN  
2021-04-20 01:45:00    NaN      NaN  NaN        NaN  

[32024 rows x 10 columns]>

And the data for the timestamps for when i usually take my medication,

['09:00 AM', '12:00 PM', '03:00 PM']

How would I use those time stamps to get this sort of column information?

Update

So, trying to build upon the question, How would I make sure it only adds medication against places where there is data available, and making sure that the after medication timing of one hour is applied correctly!

Thanks

Answer 1

Use np.select() to choose the appropriate label for a given condition.

First dropna() if all values after created are null (subset=df.columns[1:]). You can change the subset depending on your needs (e.g., subset=['id'] if rows should be dropped just for having a null id).

Then generate datetime arrays for taken-, active-, and after-medication periods based on the duration of the medication. Check whether the created times match any of the times in active (label 1) or after (label 2), otherwise default to 0.

# drop rows that are empty except for column 0 (i.e., except for df.created)
df.dropna(subset=df.columns[1:], inplace=True)

# convert times to datetime
df.created = pd.to_datetime(df.created)
taken = pd.to_datetime(['09:00:00', '12:00:00', '15:00:00'])

# generate time arrays
duration = 2 # hours
active = np.array([(taken + pd.Timedelta(f'{h}H')).time for h in range(duration)]).ravel()
after = (taken + pd.Timedelta(f'{duration}H')).time

# define boolean masks by label
conditions = {
    1: df.created.dt.floor('H').dt.time.isin(active),
    2: df.created.dt.floor('H').dt.time.isin(after),
}

# create medication column with np.select()
df['medication'] = np.select(conditions.values(), conditions.keys(), default=0)

Here is the output with some slightly modified data that better demonstrate the active / after / nan scenarios:

               created       id  sentiment  magnitude  medication
0  2020-05-21 12:00:00     23.0  -0.033333        0.5           1
3  2020-05-21 12:45:00     39.0  -0.500000        0.5           1
4  2020-05-21 13:00:00     90.0  -0.500000        0.5           1
5  2020-05-21 13:15:00    100.0  -0.033333        0.1           1
9  2020-05-21 14:15:00   1000.0   0.033333        0.5           2
10 2020-05-21 14:30:00      3.0   0.001000        1.0           2
17 2021-04-20 01:00:00  46022.0  -1.000000        1.0           0
20 2021-04-20 01:45:00  46022.0  -1.000000        1.0           0