I have a data frame with Date in the "%d-%m-%Y" format and have the week number. The dates are weekdays and I want the the Saturday for that week in another column.
I had initially check whether the date is a weekday or weekend using function in the Chron package but that was a Boolean validation. I had formatted the date variable to Date format and extracted the week number for each date.
df = data.frame(date=c("2014-08-20", "2014-08-25", "2014-10-08"))
df$date=as.Date(df$date,format="%Y-%m-%d")
df$week=week(ymd(df$date))
The expected result should be:
date week EOW
2014-08-20 34 2014-08-23
2014-08-25 34 2014-08-30
2014-10-08 41 2014-10-11
Base R option. First create a list of all days, then match it with weekdays and subtract it from 6 (as we want Saturday) to get how many days we need to add in the original date column.
all_days <- c("Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday")
#As @nicola mentioned this is locale dependent
#If your locale is not English you need weekdays in your current locale
#which you can manually write as shown above or do any one of the following
#all_days <- weekdays(seq(as.Date("2019-01-14"),by="day",length.out=7))
#OR
#all_days <- format(seq(as.Date("2019-01-14"),by="day",length.out=7), "%A")
df$EOW <- df$date + 6 - match(weekdays(df$date), all_days)
df
# date week EOW
#1 2014-08-20 34 2014-08-23
#2 2014-08-25 34 2014-08-30
#3 2014-10-08 41 2014-10-11
Or lubridate has a function ceiling_date which when used with unit = "week" would return you the next "Sunday" so we subtract 1 day from it to get "Saturday" instead.
library(lubridate)
df$EOW <- ceiling_date(df$date, unit = "week") - 1