Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Time series, change monthly data to quarterly

Now I have some monthly data like :

1/1/90 620
2/1/90,591
3/1/90,574
4/1/90,542
5/1/90,534
6/1/90,545
#...etc

If I use ts() function, it's easy to make the data into time series structure like:

         Jan Feb Mar ... Nov Dec
   1990  620 591 574 ... 493 464
   1991  100 200 300 ...........

Is there any possibilities to change it into quarterly repeating like this:

            1st 2nd 3rd 4th
   1990-Q1  620 591 574 464
   1990-Q2  100 200 300 400
   1990-Q3  ...
   1990-Q4  ...
   1991-Q1  ...

I tried to change

   ts(mydata,start=c(1990,1),frequency=12)

to

   ts(mydata,start=c(as.yearqrt("1990-1",1)),frequency=4) 

but it seems not working.

Could anyone help me? Thank you very much.

like image 891
user2152814 Avatar asked May 14 '14 21:05

user2152814


2 Answers

 monthly <- ts(mydata, start = c(1990, 1), frequency = 12)
 quarterly <- aggregate(monthly, nfrequency = 4)
like image 191
Rob Hyndman Avatar answered Sep 21 '22 15:09

Rob Hyndman


I don't agree with Hyndman on this one. Which is rare as Hyndman can usually do no wrong. However, I can show you his solution doesn't give the OP what he wants.

test<-c(1:100)
test_ts <- ts(test, start=c(2000,1), frequency=12)
test_ts

     Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
2000   1   2   3   4   5   6   7   8   9  10  11  12
2001  13  14  15  16  17  18  19  20  21  22  23  24
2002  25  26  27  28  29  30  31  32  33  34  35  36
2003  37  38  39  40  41  42  43  44  45  46  47  48
2004  49  50  51  52  53  54  55  56  57  58  59  60
2005  61  62  63  64  65  66  67  68  69  70  71  72
2006  73  74  75  76  77  78  79  80  81  82  83  84
2007  85  86  87  88  89  90  91  92  93  94  95  96
2008  97  98  99 100     

test_agg <-  aggregate(test_ts, nfrequency=4)
test_agg

2000    6   15   24   33
2001   42   51   60   69
2002   78   87   96  105
2003  114  123  132  141
2004  150  159  168  177
2005  186  195  204  213
2006  222  231  240  249
2007  258  267  276  285
2008  294

Well, wait, that first quarter isn't the average of the 3 months, its the sum. (1+2+3 =6 but you want it to show the mean=2). So you will need to modify that a tad.

test_agg <-  aggregate(test_ts, nfrequency=4)/3
# divisor is (old freq)/(new freq) = 12/4 = 3
     Qtr1 Qtr2 Qtr3 Qtr4
2000    2    5    8   11
2001   14   17   20   23
2002   26   29   32   35
2003   38   41   44   47
2004   50   53   56   59
2005   62   65   68   71
2006   74   77   80   83
2007   86   89   92   95
2008   98           

Which now shows you the mean of the monthly data written as quarterly. The divisor is the trick here. If you had weekly (freq=52) and wanted quarterly (freq=4) you'd divide by 52/4=13.

like image 29
NonReformed Bayesianist Avatar answered Sep 20 '22 15:09

NonReformed Bayesianist