Home / Questions / 295908
Accepted
kev
Asked: 2020-09-25 09:16:24 +0800 CST 2020-09-25 09:16:24 +0800 CST

How to transform a daily time series to quarterly?

  • 772

I have an excel with daily data for a variable (evaporation) for 5 places from 01/01/2000 to 08/31/2019 and I need to transform the information to quarterly data, this is the script I have:

library(readxl)
evaporacion <- read_excel("directorio", 
                         col_types = c("date", "numeric", "numeric", 
                         "numeric", "numeric", "numeric", 
                         "numeric", "numeric"))
attach(evaporacion)
library(tseries)
data = ts(evaporacion,
          start = c(2000,1,1),
          end = c(2019,31,8),
          frequency = 365)

mydata_quartely = aggregate.ts(data,nfrequency = 4 ,FUN = mean)

The problem arises when I execute the last line, an error appears saying that it cannot transform data from frequency 365 to 4.

Error in aggregate.ts(data, nfrequency = 4, FUN = mean) : cannot change frequency from 365 to 4

r

1 Answers

  1. Best Answer

    You can't, because 365 is simply not a multiple of 4. Each month, or four months, has a different number of days, depending on the month and even the year (leap). So you should always pivot with the exact date to be able to do any type of aggregation. With a tsyou could make an aggregation if, for example, it was already monthly, to semesters, four-month periods, quarters and two-month periods because 12 is a multiple of all these periods.

    For what you are trying to do, if you already have one data.framewith one fechalike the following:

    set.seed(2019)
data = data.frame(fecha = as.Date.character('2001-01-01') + seq(1, 365*19),
                  valor = runif(365*19)
)
head(data)

       fecha      valor
1 2001-01-02 0.76990155
2 2001-01-03 0.71283973
3 2001-01-04 0.30336020
4 2001-01-05 0.61823636
5 2001-01-06 0.05048374
6 2001-01-07 0.04321880

    We can group by semester and apply the function mean() thanks to as.yearqtr()the packagezoo

    library(zoo)
data_ym <- aggregate(valor ~ as.yearqtr(fecha), data, mean)
head(data_ym)

  as.yearqtr(fecha)     valor
1           2001 Q1 0.4574261
2           2001 Q2 0.5040360
3           2001 Q3 0.4740474
4           2001 Q4 0.4657588
5           2002 Q1 0.4531709
6           2002 Q2 0.5319238

    We already have the data organized by quarters in a data.framecommon, if you want it in a time series, you could do:

    ts(data_ym$valor,
  start = c(2001,1), 
  frequency = 4) -> data_qtr
data_qtr

          Qtr1      Qtr2      Qtr3      Qtr4
2001 0.4574261 0.5040360 0.4740474 0.4657588
2002 0.4531709 0.5319238 0.4647185 0.4793997
2003 0.4992733 0.4806432 0.4991905 0.4941822
2004 0.5240616 0.4513678 0.4847060 0.5245267
2005 0.4921100 0.4942957 0.4783180 0.4951553
2006 0.5084751 0.5294727 0.5060529 0.5213479
2007 0.5462217 0.4669883 0.5292718 0.5154430
2008 0.4981720 0.4818773 0.5111669 0.4664715
2009 0.5860370 0.5278600 0.5397684 0.4739722
2010 0.5407834 0.4338254 0.4667589 0.4804344
2011 0.5250560 0.5327915 0.4529550 0.4845999
2012 0.4736525 0.4880895 0.4823336 0.4906161
2013 0.5238813 0.5257305 0.4842852 0.5433635
2014 0.5033355 0.4742920 0.4947432 0.4833168
2015 0.5499442 0.4833643 0.5602218 0.4580269
2016 0.4910237 0.4637757 0.4503626 0.5100586
2017 0.5212699 0.4880358 0.5297874 0.4874585
2018 0.4925394 0.4871316 0.4607038 0.5632771
2019 0.4765936 0.4946300 0.4536314 0.5211122
    • 2