CODEWITHSUNDEEP
rmergexts
Anvar
Anvar

Reputation: 21

merging quarterly time series in R

How do you merge a and b xts series that are presented like this:

a:

1948-01-01  1

1948-04-01  1

1948-07-01  1

1948-10-01  1

b: 
1948-03-01 2

1948-06-01 2

1948-09-01 2

1948-12-01 2

Result should look like this 

          a   b

1948Q1    1   2

1948Q2    1   2

1948Q3    1   2

1948Q4    1   2

In what format the date is doesn't matter, as long as the a and b are aligned by quarters. This is much easier to do with monthly using indexFormat() and %b%Y and etc., but there isn't an index available for quarterly.

aggregate(a, as.yearqtr) doesn't work too well because for some reason it takes Q2 as the first quarter of the year. Then if you want to take yearly average, it takes Q2,Q3,Q4,Q1 for each year, instead of Q1-Q4. So, I am looking for another method. Let me know if such exists.

Upvotes: 2

Views: 373

Answers (1)

nghauran
nghauran

Reputation: 6768

Try this 2-step solution:

# Step 1: set rownames as quarters
library(zoo) 
rownames(a) <- format.yearqtr(as.Date(a$V1))
rownames(b) <- format.yearqtr(as.Date(b$V1))
# Step2: merge by rownames (quarters)
merge(a, b, by = "row.names", all = TRUE, suffixes = c(".a",".b"))[, -c(2, 4)]
# output (you can now store the result in a dataframe and change colnames as you want)
  Row.names V2.a V2.b
1   1948 Q1    1    2
2   1948 Q2    1    2
3   1948 Q3    1    2
4   1948 Q4    1    2

Data

a <- structure(list(V1 = c("1948-01-01", "1948-04-01", "1948-07-01", 
"1948-10-01"), V2 = c(1L, 1L, 1L, 1L)), .Names = c("V1", "V2"
), class = "data.frame", row.names = c("1948 Q1", "1948 Q2", 
"1948 Q3", "1948 Q4"))
b <- structure(list(V1 = c("1948-03-01", "1948-06-01", "1948-09-01", 
"1948-12-01"), V2 = c(2L, 2L, 2L, 2L)), .Names = c("V1", "V2"
), class = "data.frame", row.names = c("1948 Q1", "1948 Q2", 
"1948 Q3", "1948 Q4"))

Upvotes: 1

Related Questions