CODEWITHSUNDEEP
rxts
Alexandre Sanches
Alexandre Sanches

Reputation: 231

How to transform annual data into quarterly?

I have a xts object with annual data. I would like to convert this in quarterly data, the values will keep the same over the year, so, I don't have to interpolate the data.

I tried apply.quarterly and to.quarterly, but the data is still yearly.

Here's my code:

library(xts)

apply.quarterly(x, FUN = mean)

My output:

            Cons_gov      FBKF
1996-12-01 0.1975085 0.1864072
1997-12-01 0.1953536 0.1912290
1998-12-01 0.2007051 0.1854231
1999-12-01 0.1978274 0.1701628
2000-12-01 0.1876777 0.1830450
2001-12-01 0.1934325 0.1841800
2002-12-01 0.1980967 0.1792619
2003-12-01 0.1907744 0.1660478
2004-12-01 0.1846763 0.1732019
2005-12-01 0.1888999 0.1705619
2006-12-01 0.1903895 0.1721032
2007-12-01 0.1894298 0.1799576
2008-12-01 0.1883942 0.1938534
2009-12-01 0.1965066 0.1910200
2010-12-01 0.1901688 0.2053467
2011-12-01 0.1866926 0.2060899
2012-12-01 0.1853010 0.2071671
2013-12-01 0.1889248 0.2091192
2014-12-01 0.1915354 0.1987301
2015-12-01 0.1977682 0.1783580

My dput():

structure(c(0.197508549153391, 0.195353585641678, 0.200705142210663, 
0.197827364070052, 0.187677686384626, 0.193432520923332, 0.198096706851479, 
0.190774358523613, 0.184676286553441, 0.188899853679931, 0.190389507978999, 
0.189429845570079, 0.188394248767526, 0.1965065515285, 0.190168835777631, 
0.186692569341525, 0.185300994171881, 0.18892479001219, 0.191535387119431, 
0.197768166586277, 0.203792941796483, 0.201619077612893, 0.198950735000909, 
0.200777424344436, 0.204929121903237, 0.183310918318924, 0.186407226330573, 
0.191228971241134, 0.18542307036158, 0.170162846564942, 0.183045018192917, 
0.184180045540359, 0.179261923121358, 0.166047809279776, 0.173201889924658, 
0.170561931719758, 0.172103177073606, 0.179957599687971, 0.193853437018358, 
0.191019967063092, 0.205346736503007, 0.206089870582595, 0.207167084721339, 
0.20911921875888, 0.198730115991599, 0.178358040677889, 0.155243408026601, 
0.145589735475542, 0.150969119553704, 0.153124811412088, 0.16430683828827, 
0.187924188341473), index = structure(c(849398400, 880934400, 
912470400, 944006400, 975628800, 1007164800, 1038700800, 1070236800, 
1101859200, 1133395200, 1164931200, 1196467200, 1228089600, 1259625600, 
1291161600, 1322697600, 1354320000, 1385856000, 1417392000, 1448928000, 
1480550400, 1512086400, 1543622400, 1575158400, 1606780800, 1622505600
), tzone = "UTC", tclass = "Date"), class = c("xts", "zoo"), .Dim = c(26L, 
2L), .Dimnames = list(NULL, c("Cons_gov", "FBKF")))

Upvotes: 1

Views: 1184

Answers (1)

Artem
Artem

Reputation: 3414

You can transform xts object into a data frame. Then you can repeat every row of a data.frame given amount of time. I used dplyr for data transformations and lubridate for date information manipulation, finally I'm transforming it into quarterly format.

See below xts object to transform:

yearly <- structure(c(0.197508549153391, 0.195353585641678, 0.200705142210663, 
            0.197827364070052, 0.187677686384626, 0.193432520923332, 0.198096706851479, 
            0.190774358523613, 0.184676286553441, 0.188899853679931, 0.190389507978999, 
            0.189429845570079, 0.188394248767526, 0.1965065515285, 0.190168835777631, 
            0.186692569341525, 0.185300994171881, 0.18892479001219, 0.191535387119431, 
            0.197768166586277, 0.203792941796483, 0.201619077612893, 0.198950735000909, 
            0.200777424344436, 0.204929121903237, 0.183310918318924, 0.186407226330573, 
            0.191228971241134, 0.18542307036158, 0.170162846564942, 0.183045018192917, 
            0.184180045540359, 0.179261923121358, 0.166047809279776, 0.173201889924658, 
            0.170561931719758, 0.172103177073606, 0.179957599687971, 0.193853437018358, 
            0.191019967063092, 0.205346736503007, 0.206089870582595, 0.207167084721339, 
            0.20911921875888, 0.198730115991599, 0.178358040677889, 0.155243408026601, 
            0.145589735475542, 0.150969119553704, 0.153124811412088, 0.16430683828827, 
            0.187924188341473), index = structure(c(849398400, 880934400, 
                                                    912470400, 944006400, 975628800, 1007164800, 1038700800, 1070236800, 
                                                    1101859200, 1133395200, 1164931200, 1196467200, 1228089600, 1259625600, 
                                                    1291161600, 1322697600, 1354320000, 1385856000, 1417392000, 1448928000, 
                                                    1480550400, 1512086400, 1543622400, 1575158400, 1606780800, 1622505600
            ), tzone = "UTC", tclass = "Date"), class = c("xts", "zoo"), .Dim = c(26L, 
                                                                                  2L), .Dimnames = list(NULL, c("Cons_gov", "FBKF")))

Algorithm itself:

library(xts)
library(tidyverse)
library(lubridate)

quarterly <- as_tibble(yearly, rownames = "dates") %>%
  slice(rep(1:n(), each = 4)) %>%
  mutate(years = year(dates)) %>%
  mutate(months = rep((1:4) * 3, times = n() / 4)) %>%
  mutate(dates = ymd(paste0(years, "-", months, "-01"))) %>%
  select(-years, -months) %>%
  column_to_rownames(var = "dates") %>%
  as.xts 
xts1 <- to.quarterly(quarterly[, 1])
xts2 <- to.quarterly(quarterly[, 2])
final <- cbind(xts1[, 1], xts2[, 1])
names(final) <- c("Cons_gov", "FBKF")
head(final)

Output:

         Cons_gov      FBKF
1996 Q1 0.1975085 0.1864072
1996 Q2 0.1975085 0.1864072
1996 Q3 0.1975085 0.1864072
1996 Q4 0.1975085 0.1864072
1997 Q1 0.1953536 0.1912290
1997 Q2 0.1953536 0.1912290

Upvotes: 2

Related Questions