I'm currently struggling with some date transformations in R. I have a large dataset of financial data with a date column. Since securities are not traded on the weekend, I need to have only weekdays in my dataset. How can I round the dates in this column to the previous weekday? So each Saturday and Sunday should be transformed to the previous Friday. in the extract below, the the first date is a Saturday and the second a Sunday. Now I would like to transform these into 2007-03-02 and leave the other rows as they are.
# A tibble: 6 x 5
Ticker Date mean_PX_ASK mean_PX_BID Agency
<chr> <date> <dbl> <dbl> <chr>
1 ABNANV 2007-03-03 102. 102. Moody's
2 ABNANV 2007-03-04 102. 102. Moody's
3 ABNANV 2007-03-12 102. 102. Moody's
4 ABNANV 2007-03-12 102. 102. Moody's
5 ABNANV 2008-09-17 88.9 88.4 Fitch
6 ABNANV 2008-09-17 88.9 88.4 Fitch
Glad for any kind of help!
A simple solution could be using case_when from dplyr to check if weekday for that day is "Saturday" or "Sunday" and subtract the days accordingly.
library(dplyr)
df %>%
mutate(Day = weekdays(Date),
Date = case_when(Day == "Saturday" ~ Date - 1,
Day == "Sunday" ~ Date - 2,
TRUE ~ Date)) %>%
select(-Day)
# Ticker Date mean_PX_ASK mean_PX_BID Agency
#1 ABNANV 2007-03-02 102.0 102.0 Moody's
#2 ABNANV 2007-03-02 102.0 102.0 Moody's
#3 ABNANV 2007-03-12 102.0 102.0 Moody's
#4 ABNANV 2007-03-12 102.0 102.0 Moody's
#5 ABNANV 2008-09-17 88.9 88.4 Fitch
#6 ABNANV 2008-09-17 88.9 88.4 Fitch
With bizdays we need to create a calendar using create.calendar and default weekdays. We can then use adjust.previous to get the previous working day.
library(bizdays)
cal <- create.calendar("Actual", weekdays=c("saturday", "sunday"))
adjust.previous(df$Date, cal)
#[1] "2007-03-02" "2007-03-02" "2007-03-12" "2007-03-12" "2008-09-17" "2008-09-17"