Convert YearQtr series to End of the month Date

Question

I am trying to convert a quarterly series of data in to monthly series in R. I can repeat the same quarterly data for each of the three months in the quarter. Not sure why as.yearmon gives out "Jul" irrespective of Q1,Q2,Q3 etc. Also any help on splitting 1986Q1 in to 1/31/1986, 2/28/1986,3/31/1986 would help. Thanks!

#Input

1986Q1 25

1986Q2 30

#Output

1/31/1986 25

2/28/1986 25

3/31/1986 25

4/30/1986 30

......

#code

start=as.yearmon("1986Q1","%YQ")
end=as.yearmon("1986Q2","%YQ")
month=seq(start, end, 1/12)

print(as.yearmon("1986Q1","%YQ"))
"Jul 1986"

Try `start <- as.yearmon(as.yearqtr("1986Q1","%YQ%q")); end <- as.yearmon(as.yearqtr("1986Q2","%YQ%q"), frac = 1); as.Date(seq(start, end, by = 1/12), frac = 1)` — G. Grothendieck, Jul 11 '22 at 12:57

score 3 · Accepted Answer · answered Jul 11 '22 at 09:46

3

Using zoo

library(zoo)
qrtrs = c("1986Q1","1986Q2","1986Q3","1986Q4")
mnths = sapply(1:3, \(i) as.Date(as.yearmon(as.yearqtr(qrtrs)) + i/12) - 1)
sort(as.Date(mnths))

output

[1] "1986-01-31" "1986-02-28" "1986-03-31" "1986-04-30" "1986-05-31" "1986-06-30" "1986-07-31"
 [8] "1986-08-31" "1986-09-30" "1986-10-31" "1986-11-30" "1986-12-31"

This also works for leap years

answered Jul 11 '22 at 09:46

guasi

1,461
3
12

Thanks. Getting an error though Error: unexpected input in "mnths = sapply(1:3, \" – Jain Jul 11 '22 at 10:43
Just ran what's posted here as is without an error. To verify it worked I even started a fresh R session. – guasi Jul 11 '22 at 10:52
Can it be a version issue? Seems my version cannot take the \(i), is there an alternate way to specify this function inside sapply? – Jain Jul 11 '22 at 11:03
1

You can write out `function(i)` instead of the shorthand `\(i)` – guasi Jul 11 '22 at 11:05
My versions: R version 4.2.1 (2022-06-23) and packageVersion("zoo") ‘1.8.10’ – guasi Jul 11 '22 at 11:09
1

Also `ym <- as.yearmon(as.yearqtr(qrtrs)); as.Date(as.yearmon(rbind(ym, ym+1/12, ym+2/12)), frac = 1)` – G. Grothendieck Jul 11 '22 at 11:46

score 2 · Answer 2 · answered Jul 11 '22 at 09:09

Another approach could be using functions from {lubridate} package.

Using lubridate::parse_date_time2 with format specifier %Y%q and then with lubridate::ymd we can parse 1986Q1 as 1986-01-01.

library(dplyr)
library(lubridate)

parse_date_time2("1986Q1", "%Y%q") %>% 
  ymd() %m+% months(1:3)  %m-% days(1)

#> [1] "1986-01-31" "1986-02-28" "1986-03-31"

Now to apply this for the whole data

library(dplyr)
library(lubridate)
library(tibble)
library(tidyr)


df <- tibble(
  qdate = c("1986Q1", "1986Q2"),
  x = c(25, 50)
)

df %>% 
  rowwise() %>% 
  mutate(
    mdate = list(
      parse_date_time2(qdate, "%Y%q") %>% 
        ymd() %m+% months(1:3)  %m-% days(1)
    )
  ) %>% 
  tidyr::unnest(mdate)

#> # A tibble: 6 × 3
#>   qdate      x mdate     
#>   <chr>  <dbl> <date>    
#> 1 1986Q1    25 1986-01-31
#> 2 1986Q1    25 1986-02-28
#> 3 1986Q1    25 1986-03-31
#> 4 1986Q2    50 1986-04-30
#> 5 1986Q2    50 1986-05-31
#> 6 1986Q2    50 1986-06-30

^{Created on 2022-07-11 by the reprex package (v2.0.1)}

jay.sf · Answer 3 · 2022-07-11T08:48:11.773

You could strsplit at "Q" and use a translation list q, which provides months' first of the respective quarter. From this we add one month using seq.Date and subtract one (day).

q <- list(`1`=sprintf('%02d-01', 1:3), `2`=sprintf('%02d-01', 4:6), 
       `3`=sprintf('%02d-01', 7:9), `4`=sprintf('%02d-01', 10:12))

res <- strsplit(z, 'Q') |>
  lapply(\(x) {
    tmp <- paste(x[1], q[[x[2]]], sep='-')
    sapply(tmp, \(u) as.character(seq.Date(as.Date(u), by='month', length.out=2L)[2] - 1),
           USE.NAMES=FALSE)
    }) |> unlist() |> as.Date()

Gives

res
# [1] "1986-01-31" "1986-02-28" "1986-03-31" "1986-04-30" "1986-05-31"
# [6] "1986-06-30" "1986-07-31" "1986-08-31" "1986-09-30" "1986-10-31"
# [11] "1986-11-30" "1986-12-31" "2020-01-31" "2020-02-29" "2020-03-31"
# [16] "2020-04-30" "2020-05-31" "2020-06-30" "2020-07-31" "2020-08-31"
# [21] "2020-09-30" "2020-10-31" "2020-11-30" "2020-12-31"

As we can see, the approach also cares for leap years.

Data:

z <- c("1986Q1", "1986Q2", "1986Q3", "1986Q4",
       "2020Q1", "2020Q2", "2020Q3", "2020Q4")

Convert YearQtr series to End of the month Date

3 Answers3

Gives