How to figure third Friday of a month in R
The RcppBDT package wraps a number of date (and related time) calculations from Boost Date_Time.
Among them is a function doing what you want here:
R> getNthDayOfWeek(third, Fri, Apr, 2014)
[1] "2014-04-18"
R>
Instead of the (package-local) constants Jan, Feb, ... you can also use integers to fill you past thiry-sixth months.
Here is a simple (ugly) hack for the sequence:
R> dates <- data.frame(mon=c(5:12, 1:12, 1:12, 1:4),
+ year=c(rep(2011,8), rep(2012,12),
+ rep(2013,12), rep(2014,4)))
R> sapply(1:36, function(i)
+ format(getNthDayOfWeek(third, Fri, dates[i,1], dates[i,2])))
[1] "2011-05-20" "2011-06-17" "2011-07-15" "2011-08-19"
[5] "2011-09-16" "2011-10-21" "2011-11-18" "2011-12-16"
[9] "2012-01-20" "2012-02-17" "2012-03-16" "2012-04-20"
[13] "2012-05-18" "2012-06-15" "2012-07-20" "2012-08-17"
[17] "2012-09-21" "2012-10-19" "2012-11-16" "2012-12-21"
[21] "2013-01-18" "2013-02-15" "2013-03-15" "2013-04-19"
[25] "2013-05-17" "2013-06-21" "2013-07-19" "2013-08-16"
[29] "2013-09-20" "2013-10-18" "2013-11-15" "2013-12-20"
[33] "2014-01-17" "2014-02-21" "2014-03-21" "2014-04-18"
R>
Note that I used format()
which converts to character; else the date turns into numeric. That is just a side-effect of sapply()
though, and you could e.g. assign the Date
type back into the data frame dates
we use here.
How to get every third Friday of every third month in R
The following base R code outputs the 3rd Fridays of the required months. The output month names are in my current locale but the code itself does not depend on it.
friday3 <- function(start.year, end.year){
d <- seq(ISOdate(start.year - 1, 12, 1), ISOdate(end.year, 12, 1), by = "3 month")[-1]
d <- as.Date(d)
res <- lapply(d, function(x){
s <- seq(x, by = "day", length.out = 28)
i <- format(s, "%u") == "5"
s[i][3]
})
res <- Reduce(c, res)
data.frame(Month = format(d, "%Y-%B"), Day = res)
}
My locale is not English, so I have to set an English language locale to test it, but the code itself does not depend on locale.
#ol <- Sys.getlocale("LC_TIME")
#Sys.setlocale("LC_TIME", "en_US.UTF-8")
friday3(2018, 2020)
# Month Day
#1 2018-March 2018-03-16
#2 2018-June 2018-06-15
#3 2018-September 2018-09-21
#4 2018-December 2018-12-21
#5 2019-March 2019-03-15
#6 2019-June 2019-06-21
#7 2019-September 2019-09-20
#8 2019-December 2019-12-20
#9 2020-March 2020-03-20
#10 2020-June 2020-06-19
#11 2020-September 2020-09-18
#12 2020-December 2020-12-18
And back to mine.
#Sys.setlocale("LC_TIME", ol)
Finding the third Friday of a month and data table
Since you did not specify a by
clause in your transformation, :=
is (presumably) trying to apply getNthDayOfWeek
as a vectorized function.
This should work:
Data[
,third.friday := getNthDayOfWeek(third, Fri, mon, year)
,by = "mon,year"]
Data
# mon year third.friday
#1: 5 2011 2011-05-20
#2: 6 2011 2011-06-17
#3: 7 2011 2011-07-15
#4: 8 2011 2011-08-19
#5: 9 2011 2011-09-16
#6: 10 2011 2011-10-21
#7: 11 2011 2011-11-18
#8: 12 2011 2011-12-16
#9: 1 2012 2012-01-20
Or, more generally, in case you have duplicate mon,year
tuples in your object:
Data[,Idx := 1:.N][
,third.friday := getNthDayOfWeek(third, Fri, mon, year)
,by = "mon,year,Idx"
][,Idx := NULL][]
# mon year third.friday
#1: 5 2011 2011-05-20
#2: 6 2011 2011-06-17
#3: 7 2011 2011-07-15
#4: 8 2011 2011-08-19
#5: 9 2011 2011-09-16
#6: 10 2011 2011-10-21
#7: 11 2011 2011-11-18
#8: 12 2011 2011-12-16
#9: 1 2012 2012-01-20
Identify 3rd Friday in a sequence of Dates
To cover the corner case when the data starts in the middle of the month you can try this:
occ <- 3L # 3rd occurrence of selected weekday
data[, ThirdFridayInMonth := weekday == "Friday" &
as.integer(day - lubridate::floor_date(day, "month")) %/% 7L + 1L == occ]
This works also for other weekdays in the month, e.g., to flag every first Monday.
Edit: Explanation as requested.
The basic idea is that the 1st Friday in each month must be one of the first 7 days of each month, the 2nd Friday must be one of the days 8 to 14 and so forth. So, lubridate::floor_date(day, "month")
computes the first day of the month day
is located in. Now, you build the difference which is a difftime
object which needs to be converted into an integer
. If day
happens to be the first of a month this is 0
. Now you use integer division %/%
which returns 0 for the first 7 days of the month, 1 for the second 7 days, etc. which is then adjusted by adding 1.
Edit 2: Improved code
While writing the explanation, I recognized that the code can be further improved.
We can use the day of month directly which saves us from computing the difference in dates and subsequent conversion to integer:
data[, ThirdFridayInMonth := weekday == "Friday" &
(mday(day) - 1) %/% 7L + 1L == occ]
Here, I use mday
(part of data.table
package) instead of as.integer(format(day, "%d"))
.
R: 3rd Wedndesday of a specific month using XTS
Why not just
library(xts)
x = xts(1:3650, Sys.Date()+1:3650)
x[.indexwday(x) == 3 &
.indexmday(x) >= 15 &
.indexmday(x) <= 21
]
If first Wednesday is on 1st then third is on 15th.
If first Wednesday is on 7th then third is on 21st.
So anywhere between 15th and 21st.
Finding the last day of 3rd week for each month in R
This depends on how you define weeks in each month as stated by @IRTFM. However, here is one attempt at this using base R taking help from the function here if d
is the vector of dates.
monthweeks <- function(x) {
ceiling(as.numeric(format(x, "%d")) / 7)
}
d[monthweeks(d) == 3 & weekdays(d) == 'Friday']
#[1] "2021-01-15" "2021-02-19" "2021-03-19" "2021-04-16" "2021-05-21"
R language, count the Friday in a month
I think you're relatively close, but there are a couple of things that are off. First, you're using &&
instead of &
. Only the latter is vectorized (you use the former with if/else
, and the latter with ifelse
). Additionally, the second condition doesn't work because Flag_new_month
isn't always 1 on the Friday. I believe this does what you want:
ll$Count_Friday <-
with(ll, ifelse(Flag_Friday, ave(Flag_Friday, Month, FUN=cumsum), 0))
If you notice I don't use Flag_new_month
, instead I use ave
to break up the Flag_Friday
column by month, and then within each month, do a cumulative sum of the Fridays. The ifelse
is just so that I only populate this cumulative sum value for Fridays (otherwise every day following a Friday up to the next Friday would have the same number as Count_Friday
).
And here is the result. Interestingly 2014 had 5 Fridays in the first month.
head(ll, 40)
# Dates Month Weekday Flag_new_month Flag_Friday Count_Friday
# 1 2014-01-01 1 Wednesday 1 0 0
# 2 2014-01-02 1 Thursday 0 0 0
# 3 2014-01-03 1 Friday 0 1 1
# 4 2014-01-04 1 Saturday 0 0 0
# 5 2014-01-05 1 Sunday 0 0 0
# 6 2014-01-06 1 Monday 0 0 0
# 7 2014-01-07 1 Tuesday 0 0 0
# 8 2014-01-08 1 Wednesday 0 0 0
# 9 2014-01-09 1 Thursday 0 0 0
# 10 2014-01-10 1 Friday 0 1 2
# 11 2014-01-11 1 Saturday 0 0 0
# 12 2014-01-12 1 Sunday 0 0 0
# 13 2014-01-13 1 Monday 0 0 0
# 14 2014-01-14 1 Tuesday 0 0 0
# 15 2014-01-15 1 Wednesday 0 0 0
# 16 2014-01-16 1 Thursday 0 0 0
# 17 2014-01-17 1 Friday 0 1 3
# 18 2014-01-18 1 Saturday 0 0 0
# 19 2014-01-19 1 Sunday 0 0 0
# 20 2014-01-20 1 Monday 0 0 0
# 21 2014-01-21 1 Tuesday 0 0 0
# 22 2014-01-22 1 Wednesday 0 0 0
# 23 2014-01-23 1 Thursday 0 0 0
# 24 2014-01-24 1 Friday 0 1 4
# 25 2014-01-25 1 Saturday 0 0 0
# 26 2014-01-26 1 Sunday 0 0 0
# 27 2014-01-27 1 Monday 0 0 0
# 28 2014-01-28 1 Tuesday 0 0 0
# 29 2014-01-29 1 Wednesday 0 0 0
# 30 2014-01-30 1 Thursday 0 0 0
# 31 2014-01-31 1 Friday 0 1 5
# 32 2014-02-01 2 Saturday 1 0 0
# 33 2014-02-02 2 Sunday 0 0 0
# 34 2014-02-03 2 Monday 0 0 0
# 35 2014-02-04 2 Tuesday 0 0 0
# 36 2014-02-05 2 Wednesday 0 0 0
# 37 2014-02-06 2 Thursday 0 0 0
# 38 2014-02-07 2 Friday 0 1 1
# 39 2014-02-08 2 Saturday 0 0 0
# 40 2014-02-09 2 Sunday 0 0 0
Here is how I made the data:
ll <- data.frame(Dates=seq(as.Date("2014-01-01"), len=365, by="1 day"))
ll <- transform(
ll,
Month=month(Dates),
Weekday=weekdays(Dates),
Flag_new_month=diff(c(0, month(Dates))),
Flag_Friday=as.integer(weekdays(Dates) == "Friday")
)
How to flag last friday or last day or month
With the additional clarification and following the comment by @eminik the code below
library(data.table)
setDT(data)
data[, LastDayInMonth := day == max(day), by = .(year(day), month(day))]
data[, LastFridayInMonth := weekday == "Friday" & day == max(day),
by = .(year(day), month(day), weekdays(day))]
produces:
# show results (only relevant rows)
data[LastDayInMonth | LastFridayInMonth == TRUE]
day weekday LastDayInMonth LastFridayInMonth
1: 2016-01-29 Friday TRUE TRUE
2: 2016-02-26 Friday FALSE TRUE
3: 2016-02-29 Monday TRUE FALSE
4: 2016-03-25 Friday FALSE TRUE
5: 2016-03-31 Thursday TRUE FALSE
6: 2016-04-29 Friday TRUE TRUE
7: 2016-05-06 Friday FALSE TRUE
8: 2016-05-10 Tuesday TRUE FALSE
Edit: Code modified to account for change of years as requested by OP.
Note: weekdays
returns a character vector of names in the locale in use. Therefore, the code only works if you are in an English locale. Otherwise, you may have to use Sys.setlocale(category = "LC_ALL", locale = "English")
before.
Create end of the month date from a date variable
To get the end of months you could just create a Date
vector containing the 1st of all the subsequent months and subtract 1 day.
date.end.month <- seq(as.Date("2012-02-01"),length=4,by="months")-1
date.end.month
[1] "2012-01-31" "2012-02-29" "2012-03-31" "2012-04-30"
Count the number of Fridays or Mondays in Month in R
1) Here d
is the input, a Date
class object, e.g. d <- Sys.Date()
. The result gives the number of Fridays in the year/month that contains d
. Replace 5 with 1 to get the number of Mondays:
first <- as.Date(cut(d, "month"))
last <- as.Date(cut(first + 31, "month")) - 1
sum(format(seq(first, last, "day"), "%w") == 5)
2) Alternately replace the last line with the following line. Here, the first term is the number of Fridays from the Epoch to the next Friday on or after the first of the next month and the second term is the number of Fridays from the Epoch to the next Friday on or after the first of d
's month. Again, we replace all 5's with 1's to get the count of Mondays.
ceiling(as.numeric(last + 1 - 5 + 4) / 7) - ceiling(as.numeric(first - 5 + 4) / 7)
The second solution is slightly longer (although it has the same number of lines) but it has the advantage of being vectorized, i.e. d
could be a vector of dates.
UPDATE: Added second solution.
Related Topics
Ggplot Bar Plot Side by Side Using Two Variables
Renaming and Hiding an Exported Rcpp Function in an R Package
Is There a Limit for the Possible Number of Nested Ifelse Statements
Knitr: Opts_Chunk$Set() Not Working in Rscript Command
How to Convert Unix Timestamp (Milliseconds) and Timezone in R
Write Different Data Frame in One .CSV File with R
How to Make a Barplot with R from a Table
Meaning of Tilde and Dot Notation in Dplyr
Data.Table := Assignments When Variable Has Same Name as a Column
Control Font Thickness Without Changing Font Size
How to Change Name of Factor Levels
The Representation of an Empty Argument in a "Call"
Find the Source File Containing R Function Definition
Combining Geom_Point and Geom_Line with Position_Jitterdodge for Two Grouping Factors