grouping the Datetime column on timestamp

Roshankumar 0
Viewed 16

Existing Dataframe :

Unique_Id              Date 
A                 11-01-2022 10:20:30.500
A                 11-01-2022 13:10:10:258
A                 11-01-2022 17:30:22.223
A                 11-01-2022 23:20:38.222
B                 02-02-2022 08:25:30.000
B                 04-02-2022 11:35:40.928

Expected Dataframe :

Unique_Id                 Date                     Time_Group         
A                 11-01-2022 10:20:30.500          9AM - 12AM      
A                 11-01-2022 13:10:10:258          12PM - 3PM        
A                 11-01-2022 17:30:22.223          5PM  - 8PM        
A                 11-01-2022 23:20:38.222          9PM - 12PM        
B                 02-02-2022 08:25:30.000          6AM - 9AM         
B                 04-02-2022 11:35:40.928          9AM - 12AM

Trying to Bin the time into the Time-Group

approached it using pd.cut(df.Date.dt.hour) , but stuck with binning part

pandas
1 Answers

Here is one approach:

bins = [0,9,12,15,18,24]
# function to convert hour as int to xAM/xPM
h_to_str = lambda x: pd.to_datetime(str(x), format='%H').strftime('%-I%p')
h_as_str = [h_to_str(x%24) for x in bins]
labels = [f'{a} - {b}' for a,b in zip(h_as_str, h_as_str[1:])]

df['Time_Group'] = pd.cut(pd.to_datetime(df['Date']).dt.hour,
                          bins=bins, labels=labels)

output:

  Unique_Id                     Date  Time_Group
0         A  11-01-2022 10:20:30.500  9AM - 12PM
1         A  11-01-2022 13:10:10.258  12PM - 3PM
2         A  11-01-2022 17:30:22.223   3PM - 6PM
3         A  11-01-2022 23:20:38.222  6PM - 12AM
4         B  02-02-2022 08:25:30.000  12AM - 9AM
5         B  04-02-2022 11:35:40.928  9AM - 12PM
mozway0
Related