I have a dateframe with the following column:
0 2019-06-30 17:31:43
1 2019-06-30 07:07:41
2 2019-06-30 17:11:46
3 2019-06-30 12:13:50
4 2019-06-30 12:13:55
5 2019-06-30 03:01:53
6 2019-06-30 07:22:02
7 2019-06-30 18:12:47
8 2019-06-30 21:38:19
9 2019-06-30 03:01:58
10 2019-06-30 10:06:16
11 2019-06-30 03:46:43
12 2019-06-30 00:44:24
13 2019-06-30 00:44:29
14 2019-06-30 00:44:32
15 2019-06-30 00:44:33
16 2019-06-30 19:32:09
17 2019-06-30 09:09:42
18 2019-06-30 11:52:10
19 2019-06-30 18:30:00
20 2019-06-30 01:26:56
21 2019-06-30 19:29:02
22 2019-06-30 11:54:39
23 2019-06-30 16:54:47
24 2019-06-30 06:00:49
25 2019-06-30 18:51:17
26 2019-06-30 05:54:55
27 2019-06-30 19:22:43
28 2019-06-29 23:06:12
29 2019-06-30 04:26:07
What I'm after is a new column that has the time of day [Morning, Afternoon, Evening].
The following line returns a Boolean
test['visitStartTime_aest'].dt.strftime('%H:%M:%S').between('00:00:00','12:00:00')
This line returns a filtered dataframe and changes the value to morning [which is what I want]
test[test['visitStartTime_aest'].dt.strftime('%H:%M:%S').between('00:00:00','12:00:00')] = 'Morning'
This works, but now that column has mixed dtypes making it hard to transform the remaining timestamps as some are now str and others are datetime64[ns]
I have tried the following with no luck:
def time_of_day(df, date_col):
df[date_col] = df[df[date_col].dt.strftime('%H:%M:%S').between('00:00:00','12:00:00')] = 'Morning'
df[date_col] = df[df[date_col].dt.strftime('%H:%M:%S').between('12:00:01','18:00:00')] = 'Afternoon'
df[date_col] = df[df[date_col].dt.strftime('%H:%M:%S').between('18:00:01','23:59:59')] = 'Evening'
return df
This executes the first row perfectly but the following rows suffer the same fate as above [mixed dtypes]
I have also tried this, but with no luck:
def time_of_day(df):
if df['visitStartTime_aest'].dt.strftime('%H:%M:%S').between('00:00:00','12:00:00'):
return 'Morning'
elif df['visitStartTime_aest'].dt.strftime('%H:%M:%S').between('12:00:01','18:00:00'):
return 'Afternoon'
else:
return 'Evening'
test.apply(time_of_day, axis=1)
Any idea as to what I'm missing? Or any guidance on how to execute?
Thanks!