Excel BI’s Excel Challenge #310 — solved in R

Defining the Puzzle:

It was early Monday morning when hunt for Fridays come up. Of course new riddle about Fridays 13th.

Task was really short yet challenging: Find the next 3 Friday the 13th dates for dates given in column A.

Loading Data from Excel:

Let me get needed libraries, exercise dates and solution from our excercise file.

library(tidyverse)
library(lubridate)
library(readxl)
library(data.table)

input = read_excel(“Next 3 Friday the 13th.xlsx”, range = “A1:A10”)
test = read_excel(“next 3 friday the 13th.xlsx”, range = “B2:D10”, 
 col_names = c(“friday_13th_1”, “friday_13th_2”, “friday_13th_3”))

Approach 1: Tidyverse with purrr

get_3_f13_tv<- function(date) {
 start_date <- as.Date(paste0(year(date), “-”, month(date), “-13”))
 end_date <- start_date + years(3)
 date_seq_13th <- seq.Date(from=start_date, to=end_date, by=”month”)
 
 friday_13ths <- date_seq_13th[wday(date_seq_13th) == 6 & date_seq_13th > date]
 
 head(friday_13ths, 3)
}

next_3_tv = input %>%
 rowwise() %>%
 mutate(friday_13th_1 = get_3_f13_tv(Date)[1] %>% as.character(),
 friday_13th_2 = get_3_f13_tv(Date)[2] %>% as.character() ,
 friday_13th_3 = get_3_f13_tv(Date)[3] %>% as.character()) %>%
 ungroup() %>%
 select(-Date)

Approach 2: Base R

get_3_f13_base <- function(date) {
 start_date <- as.Date(paste0(format(as.Date(date), “%Y-%m”), “-13”))
 end_date <- start_date + (365.25 * 3) 
 date_seq_13th <- seq(start_date, end_date, by=”month”)
 
 weekday <- weekdays(date_seq_13th )
 friday_13ths <- date_seq_13th[weekday == “Friday” & date_seq_13th > date]
 
 head(friday_13ths, 3)
}

next_3_base <- tibble(
 friday_13th_1 = character(nrow(input)),
 friday_13th_2 = character(nrow(input)),
 friday_13th_3 = character(nrow(input))
)

for (i in 1:nrow(input)) {
 f13_dates <- get_3_f13_base(input$Date[i])
 next_3_base$friday_13th_1[i] <- as.character(f13_dates[1])
 next_3_base$friday_13th_2[i] <- as.character(f13_dates[2])
 next_3_base$friday_13th_3[i] <- as.character(f13_dates[3])
}

Approach 3: Data.table

input_dt = setDT(input)

get_3_f13_dt <- function(date) {
 start_date <- as.Date(paste0(year(date), “-”, month(date), “-13”))
 end_date <- start_date + years(3)
 date_seq_13th <- seq.Date(from=start_date, to=end_date, by=”month”)
 
 friday_13ths <- date_seq_13th[wday(date_seq_13th) == 6 & date_seq_13th > date]
 
 friday_13ths <- head(friday_13ths, 3)
 
 c(friday_13ths, rep(NA, 3 — length(friday_13ths)))
}

input_dt[, c(“friday_13th_1”, “friday_13th_2”, “friday_13th_3”) := 
 as.list(get_3_f13_dt(Date)), by = 1:nrow(input_dt)][
 , `:=`(friday_13th_1 = as.character(friday_13th_1),
 friday_13th_2 = as.character(friday_13th_2),
 friday_13th_3 = as.character(friday_13th_3))][
 , Date := NULL]

next_3_dt = as_tibble(as.data.frame(input_dt))

Validating Our Solutions:

identical(test, next_3_tv)
#> [1] TRUE

identical(test, next_3_base)
#> [1] TRUE

identical(test, next_3_dt)
#> [1] TRUE

If you like my publications or have your own ways to solve those puzzles in R, Python or whatever tool you choose, let me know.

Next 3 Friday the 13th was originally published in Numbers around us on Medium, where people are continuing the conversation by highlighting and responding to this story.