CODEWITHSUNDEEP
rtime-seriesdata-manipulation
Tamimi
Tamimi

Reputation: 37

How to break quarterly data into monthly using certain formula in R?

Dears

I am trying to breakdown quarterly data into monthly data using R. I am not concerned about dates as I can generate a vector of months corresponding to values without problems. The problem is with the vector of values and imputing the missing data. See the example below:

Quarter Value
2010-Q1 10
2010-Q2 15
2010-Q3 18
2010-Q4 12

The new data set should look like the following

Month   Value
2010-3  10
2010-4  11.67
2010-5  13.34
2010-6  15
2010-7  16
2010-8  17
2010-9  18
2010-10 16
2010-11 14
2010-12 12

Now, the months within each quarter are filled using the following formula

The first month of the quarter[i] = The previous quarter value [i-1] + ((The difference between the quarter [i] and [i-1])/3)
The second month of the quarter[i] = The previous quarter value [i-1] + 2*((The difference between the quarter [i] and [i-1])/3)

For example:

2020-Q1 = 10

2020-Q2 = 15

Difference/3 = 5/3

2020-April = 10 + diff

2020-May = 10 + 2*diff

2020-June = 15 (end of the quarter stays the same) or can be calculated as 10 + 3*diff

I am wondering how to generate a new variable that can break down the values as mentioned above.

Thanks

Upvotes: 1

Views: 3756

Answers (1)

G. Grothendieck
G. Grothendieck

Reputation: 269644

1) Convert the input to a zoo series z with yearqtr index (which directly represents year and quarter without month or day) and then pad out with NAs and apply na.approx to fill them in linearly giving Value. Assuming that the series is regularly spaced we can just convert the first index value to yearmon (which directly represents a year and month without day) using a frequency of 12 months per year. Finally, either leave it as Value or else use the last line to convert it back to data frame DF2. Another possibility would be to use as.ts(Value) to convert it to a ts series.

Note that yearmon class displays as shown below but represents year and month internally as year plus a fraction equal to 0 for Jan, 1/12 for Feb, ..., 11/12 for Dec so as.integer(time(Value)) will give the year and cycle(time(Value)) will give the month number (Jan = 1, ..., Dec = 12).

library(zoo)

z <- read.zoo(DF, FUN = function(x) as.yearqtr(x, "%Y-Q%q"))
Value <- zooreg(na.approx(c(t(cbind(z, NA, NA)))), 
  start = as.yearmon(start(z)), freq = 12)
DF2 <- fortify.zoo(Value) # optional

giving:

> DF2
      Index    Value
1  Jan 2010 10.00000
2  Feb 2010 11.66667
3  Mar 2010 13.33333
4  Apr 2010 15.00000
5  May 2010 16.00000
6  Jun 2010 17.00000
7  Jul 2010 18.00000
8  Aug 2010 16.00000
9  Sep 2010 14.00000
10 Oct 2010 12.00000

Graphically it looks like this:

plot(Value, type = "o")

(continued after plot) screenshot

2) A second method starting with z from (1) is to first create the output yearmon time sequence tt, convert the time index of z to yearmon giving z.ym and then merge them generating NA's and finally apply na.approx to fill them in.

tt <- seq(as.yearmon(start(z)), as.yearmon(end(z)), 1/12)
z.ym <- aggregate(z, as.yearmon, c)
Value <- na.approx(merge(z.ym, zoo(, tt)))

Note

The input in reproducible form:

Lines <- "Quarter Value
2010-Q1 10
2010-Q2 15
2010-Q3 18
2010-Q4 12"
DF <- read.table(text = Lines, header = TRUE)

Upvotes: 2

Related Questions