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