Hello folks,
I am looking to create a 'day_of_week' column on a dataset that already contains another column in UTC. I am struggling to get the correct function for this. Any tip?
Thanks,
1 Like
maybe from lubridate
lubridate::wday(Sys.Date(),label = TRUE)
1 Like
Can week days appear as 1=Sunday...7=Saturday with this function?
Here are some examples of using wday. I think you want the third one.
library(lubridate)
#make a date
DATE <- ymd_hms("2021-06-04 11:32:26")
wday(DATE, label = TRUE)
[1] Fri
Levels: Sun < Mon < Tue < Wed < Thu < Fri < Sat
wday(DATE, label = TRUE, abbr = FALSE)
[1] Friday
Levels: Sunday < Monday < Tuesday < Wednesday < Thursday < Friday < Saturday
wday(DATE)
[1] 6
wday(DATE, week_start = 1) #Start week on Monday
[1] 5
I need to start the week on Sunday, not Monday.
That is the third example:
Thanks buddy, but how to include the column name 'day_of_week' ?
library(lubridate)
#>
#> Attaching package: 'lubridate'
#> The following objects are masked from 'package:base':
#>
#> date, intersect, setdiff, union
#make data
DF <- data.frame(DATE = ymd_hms(c("2021-06-01 12:34:25", "2021-06-02 19:45:01")))
DF
#> DATE
#> 1 2021-06-01 12:34:25
#> 2 2021-06-02 19:45:01
DF$day_of_week <- wday(DF$DATE)
DF
#> DATE day_of_week
#> 1 2021-06-01 12:34:25 3
#> 2 2021-06-02 19:45:01 4
Created on 2021-06-04 by the reprex package (v0.3.0)
1 Like
The last function seems to work but it only creates 2 rows when I have multiple rows. Do you think I should replace the date and time to refer to another column in the dataset like this? The other columns in the dataset are named 'start_time' and 'end_time' and match "2021-06-01 12:34:25" as above
dataset <- data.frame(Date = ymd_hms(c("start_time", "end_time")
I think you are mistaken, here is a demonstration that the solution provided would work on multiple rows
(DF <- data.frame(DATE =
seq.Date(Sys.Date()-10,Sys.Date(),by = 1)))
DF$day_of_week <- wday(DF$DATE)
DF
Its not clear how other columns in your data would affect your ask, do you want each date column to have a corresponding day_of_week column ? how would you like them named if so ?
1 Like
Ok cool! Is there a way to have names on the column instead of numbers? Friday instead of 6, and so on...
(DF <- data.frame(DATE =
seq.Date(Sys.Date()-10,Sys.Date(),by = 1)))
DF$day_of_week <- wday(DF$DATE)
DF$day_of_week_name <- c("Sunday",
"Monday",
"Tuesday",
"Wednesday",
"Thursday",
"Friday",
"Saturday"
)[DF$day_of_week]
DF
DATE day_of_week day_of_week_name
1 2021-05-25 3 Tuesday
2 2021-05-26 4 Wednesday
3 2021-05-27 5 Thursday
4 2021-05-28 6 Friday
5 2021-05-29 7 Saturday
6 2021-05-30 1 Sunday
7 2021-05-31 2 Monday
8 2021-06-01 3 Tuesday
9 2021-06-02 4 Wednesday
10 2021-06-03 5 Thursday
11 2021-06-04 6 Friday
1 Like
You got it, man! That sounds much better...
One last thing, after checking the dataset with head(dataset), the column was created separately but it was not added to the big dataset itself. Where should I place mutate function to add the new column to the dataset??
In r we use the arrow assignment operator to save the outcome of an operation to an object name. So you can choose a name and put the <- between the name you chose and the mutate
Are you saying I should do like this?
DF1 %>%
mutate(DF <- data.frame(DATE = seq.Date(Sys.Date()-10,Sys.Date(), by = 1)))
No.
Either
DF2 <- DF1 %>%
mutate.... Blah blah
Or
DF1 %>%
mutates... blah blah -> DF2
(You could also assign back to DF1 if you prefer)
Thanks Calum, I was insisting on mutate() because there's another 'UTC' column in the table that the new colum 'day_of_week' must be able to read the date (2020-12-10) and display the corresponding day of week (Sunday thru Saturday).
All I am saying is that the script you gave me produces 2 columns: Date and day of week. I don't need the 'DATE' column because another column similar on the table already exist. I just want the new 'day_of_week' column to reference the day (Sunday thru Saturday) based on that date column. Does it make sense?
I think you have all the information that you would need, but , do you have more questions in this topic ?