Convert YearQtr series to End of the month Date-CodePudding

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"

CodePudding user response：

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

CodePudding user response：

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)}

CodePudding user response：

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('d-01', 1:3), `2`=sprintf('d-01', 4:6), 
       `3`=sprintf('d-01', 7:9), `4`=sprintf('d-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")