R find last weekday of month

Question

How do I find the last weekday (e.g., Wednesday) of the month using R? In the code below, I calculate the month, day of the month, week of the month, and weekday. There are 5 Wednesdays in January 2014, but only 4 Wednesdays in February 2014, so I cannot use max(week of the month) as a filter. Any help is appreciated although I prefer to use the base R functions.

DF <- data.frame(DATE = seq(as.Date("2014-01-01"), as.Date("2014-12-31"), "day"))

DF$MONTH         <- as.numeric(format(DF$DATE, "%m"))
DF$DAY_OF_MONTH  <- as.numeric(format(DF$DATE, "%d"))
DF$WEEK_OF_MONTH <- ceiling(as.numeric(format(DF$DATE, "%d")) / 7)
DF$WEEKDAY       <- format(DF$DATE, "%A")

DF

Extensor

The goal is to create a new column that, for each day/row, gives the last weekday of that month? – Monosepalous

Answer 1

I think this is what you're after:

DF$last_weekday_o_month <- ave( 
  weekdays(DF$DATE), 
  months(DF$DATE), 
  FUN = function(x) tail(x[ !(x %in% c("Saturday","Sunday")) ], 1) 
)

To find the particular date that is the last weekday....

DF$last_weekdaydate_o_month <- ave( 
  DF$DATE, 
  months(DF$DATE), 
  FUN = function(x) tail(x[ !(weekdays(x) %in% c("Saturday","Sunday")) ], 1) 
)

the result looks like...

          DATE last_weekday_o_month last_weekdaydate_o_month
1   2014-01-01               Friday               2014-01-31
2   2014-01-02               Friday               2014-01-31
3   2014-01-03               Friday               2014-01-31
4   2014-01-04               Friday               2014-01-31
5   2014-01-05               Friday               2014-01-31
6   2014-01-06               Friday               2014-01-31
...
360 2014-12-26            Wednesday               2014-12-31
361 2014-12-27            Wednesday               2014-12-31
362 2014-12-28            Wednesday               2014-12-31
363 2014-12-29            Wednesday               2014-12-31
364 2014-12-30            Wednesday               2014-12-31
365 2014-12-31            Wednesday               2014-12-31

If you did this first, of course you could compute last_weekday_o_month as weekdays(last_weekdaydate_o_month).

---

With a couple packages, this can be done more elegantly/readably, as suggested by @RichardScriven:

library(data.table)
setDT(DF)[, 
  last_weekdaydate_o_month := last(DATE[!chron::is.weekend(DATE)])
, by = month(DATE)]

which gives

           DATE last_weekdaydate_o_month
  1: 2014-01-01               2014-01-31
  2: 2014-01-02               2014-01-31
  3: 2014-01-03               2014-01-31
  4: 2014-01-04               2014-01-31
  5: 2014-01-05               2014-01-31
 ---                                    
361: 2014-12-27               2014-12-31
362: 2014-12-28               2014-12-31
363: 2014-12-29               2014-12-31
364: 2014-12-30               2014-12-31
365: 2014-12-31               2014-12-31

Monosepalous

I am trying to find the last specific weekday, for example, the last Wednesday of the month. Maybe I could modify your code to say FUN = function(x) tail(x[ ! (x %in% c("Monday", "Tuesday", "Thursday", "Friday", "Saturday", "Sunday"). – Extensor

@Extensor I've updated the answer; yeah, it's something like that. – Monosepalous

setDT(DF)[, last(DATE[!chron::is.weekend(DATE)]), by = month(DATE)] might be useful – Westphalia

Answer 2

Here is a method using dplyr. Essentially you group by the month, filter out the 'weekend' days and return the weekday of the last (i.e. max) day.

library(dplyr)

DF <- data.frame(DATE = seq(as.Date("2014-01-01"), as.Date("2014-12-31"), "day"))

DF %>%
  mutate(month = months(DATE), weekday = weekdays(DATE)) %>%
  group_by(month) %>%
  filter(!weekday %in% c("Saturday", "Sunday")) %>%
  summarise(last_weekday = weekdays(max(DATE))) 

Source: local data frame [12 x 2]

       month        last_weekday
1      April           Wednesday
2     August              Friday
3   December           Wednesday
4   February              Friday
5    January              Friday
6       July            Thursday
7       June              Monday
8      March              Monday
9        May              Friday
10  November              Friday
11   October              Friday
12 September             Tuesday

Shrink

@Frank, ah, I just had it loaded already so I added it in thinking I used it. It isn't necessary here. – Shrink

Answer 3

With base R:

LastWd <- function(y,m){ #y: year, m: month
last <- paste(y, m, diff(seq(as.Date(
paste(y, "/",m,"/01", sep="")), by = "month", length.out = 2)), sep="/")
l3 <- format(seq((as.Date(last)-2), as.Date(last),by="day"), "%A, %Y-%m-%d")
rev(l3[l3<"S" | l3>"T"])[1]
}

The result:

Map(f, 2012, 1:12)
[[1]]
[1] "Tuesday, 2012-01-31"

[[2]]
[1] "Wednesday, 2012-02-29"

[[3]]
[1] "Friday, 2012-03-30"

[[4]]
[1] "Monday, 2012-04-30"

[[5]]
[1] "Thursday, 2012-05-31"

[[6]]
[1] "Friday, 2012-06-29"

[[7]]
[1] "Tuesday, 2012-07-31"

[[8]]
[1] "Friday, 2012-08-31"

[[9]]
[1] "Friday, 2012-09-28"

[[10]]
[1] "Wednesday, 2012-10-31"

[[11]]
[1] "Friday, 2012-11-30"

[[12]]
[1] "Monday, 2012-12-31"

Blackleg

Answer 4

library(lubridate)
x <- seq(as.Date("2007-12-31"), by="1 day", length.out=(Sys.Date() - as.Date("2007-12-31")))
library(plyr)
df <- data.frame(date=x, year=year(x), month=month(x))
df[,"weekday"] <- weekdays(df[,"date"])
df<- df[! df[,"weekday"] %in% c("Saturday", "Sunday"),]
df <- ddply(df, .(year, month), summarize, last=max(date))

Angers