lubridate adding a new column to add hour of the week-CodePudding

I have this data frame;

  city                 date           
London  2022-01-01 00:00:00     
London  2022-01-01 01:00:00      
London  2022-01-01 02:00:00
London  2022-01-01 03:00:00
London  2022-01-01 04:00:00
London  2022-01-01 05:00:00
London  2022-01-01 06:00:00
...
London  2022-01-07 00:00:00
...
Glasgow 2022-01-01 00:00:00
Glasgow 2022-01-01 00:00:00
Glasgow 2022-01-01 00:00:00
...

I would like to group by city and add new column to called hour of the week.

where it shows;

  city                 date         hour of the week               
London  2022-01-01 00:00:00               0
London  2022-01-01 01:00:00               1   
London  2022-01-01 02:00:00               2
London  2022-01-01 03:00:00               3
London  2022-01-01 04:00:00               4
London  2022-01-01 05:00:00               5
London  2022-01-01 06:00:00               6
...
London  2022-01-07 23:00:00             167
...
Glasgow 2022-01-01 00:00:00               0
Glasgow 2022-01-01 01:00:00               1
Glasgow 2022-01-01 02:00:00               2
...

I have tried; ([day-1] * 24 hour of abs) which worked but I was wondering if there is a way to do it with lubridate. I have checked there is as.duration, but I couldn't manage to make it work.

CodePudding user response：

We may do

library(dplyr)
library(lubridate)
df1 %>%
    mutate(date = ymd_hms(date), 
      hour_of_the_week = (day(date)-1) * 24   hour(date))