8

I have a zoo object called pp with daily data and 77 columns that looks like this:

            X02R X03N X04K X04N X04R X06I X06N X08J X08P X09O X11O X12L X14N X15G X16K  (...)
1961-01-01  8.3  5.2  3.2  0.0  8.7  5.2 15.0  7.2 11.5 13.0  0.0  4.9  0.0  2.9  6.0   
1961-01-02  1.1  3.2 10.0  0.0  0.0  3.5  0.0  8.7  0.4  1.2  0.0  0.4  0.0  3.2  0.2    
1961-01-03 12.0  4.2 50.5  0.0  9.0 38.5 15.0 31.7  1.7  8.7  9.0 69.2  4.2 22.2  9.2 
(...)

I want to use apply.monthly to each of the columns, so in the end I will still have 77 columns but with monthly data instead of daily data. I tried apply.monthly(pp, FUN=sum) but the result is a zoo object with just one column (I think is adding all the columns).

I also tried a loop:

for (i in 1:77) { mensal<-apply.monthly(pp[,i], FUN=sum) } but it also results in just one column (the 77th column). I might be able to make the loop work with some trial and error but it takes ages to compute ( I have 17897 rows and 77 columns) and I guess there is a simpler way of doing this without using loops... So if you know how, please help. Thanks!

sbg
  • 1,772
  • 8
  • 27
  • 45

3 Answers3

13

In order for apply.monthly to return an object with more than one column, you have to use a function that operates by column (or apply a function that doesn't).

library(quantmod)
getSymbols("SPY")
zSPY <- as.zoo(SPY)
# sum doesn't operate by column; it sums everything to one value
sum(zSPY)
spy.sum <- apply.monthly(zSPY, sum)
# colSums operates by column
spy.colSums <- apply.monthly(zSPY, colSums)
# use apply to operate by column
spy.apply.sum <- apply.monthly(zSPY, apply, 2, sum)
Joshua Ulrich
  • 173,410
  • 32
  • 338
  • 418
  • 1
    I also get 1 column. I got errors until I updated. It does require either a restart or detach()-ing/(re-)require()-ing all of the packages that were resident after the update. Try with FUN=sum on the structure offered. (will post dput() output in an answer box.) – IRTFM Sep 02 '11 at 17:51
  • Do you have any idea why your SPY example did not get collapsed? It wouldn't make much sense to sum() dates or indices, but last() would have a natural interpretation for an ordered index perhaps? – IRTFM Sep 02 '11 at 18:25
  • @Dwin: I used `last` in my initial answer and it returns a 1xn object. `sum` just returns a one element vector. – Joshua Ulrich Sep 02 '11 at 18:30
  • Thanks. I had NA values, I did apply.monthly(data, colSums, na.rm = TRUE), and it seems to work. – PatrickT Mar 29 '13 at 19:42
3

Try this:

> library(zoo)
>
> # test data
> z <- zooreg(cbind(a = 1:365, b = 1:365), Sys.Date())
> head(z)
           a b
2011-09-02 1 1
2011-09-03 2 2
2011-09-04 3 3
2011-09-05 4 4
2011-09-06 5 5
2011-09-07 6 6
>
> aggregate(z, as.yearmon)
             a     b
Sep 2011   435   435
Oct 2011  1395  1395
Nov 2011  2265  2265
Dec 2011  3286  3286
Jan 2012  4247  4247
Feb 2012  4843  4843
Mar 2012  6107  6107
Apr 2012  6825  6825
May 2012  7998  7998
Jun 2012  8655  8655
Jul 2012  9889  9889
Aug 2012 10850 10850
G. Grothendieck
  • 254,981
  • 17
  • 203
  • 341
0

This is just for a reproducible example. Created with:

dat <- read.zoo(textConnection("date  X02R X03N X04K X04N X04R X06I X06N X08J X08P X09O X11O X12L X14N X15G X16K
 1961-01-01  8.3  5.2  3.2  0.0  8.7  5.2 15.0  7.2 11.5 13.0  0.0  4.9  0.0  2.9  6.0
 1961-01-02  1.1  3.2 10.0  0.0  0.0  3.5  0.0  8.7  0.4  1.2  0.0  0.4  0.0  3.2  0.2
 1961-01-03 12.0  4.2 50.5  0.0  9.0 38.5 15.0 31.7  1.7  8.7  9.0 69.2  4.2 22.2  9.2"), header=TRUE)

dput(dat) structure(c(8.3, 1.1, 12, 5.2, 3.2, 4.2, 3.2, 10, 50.5, 0, 0, 0, 8.7, 0, 9, 5.2, 3.5, 38.5, 15, 0, 15, 7.2, 8.7, 31.7, 11.5, 0.4, 1.7, 13, 1.2, 8.7, 0, 0, 9, 4.9, 0.4, 69.2, 0, 0, 4.2, 2.9, 3.2, 22.2, 6, 0.2, 9.2), .Dim = c(3L, 15L), .Dimnames = list( NULL, c("X02R", "X03N", "X04K", "X04N", "X04R", "X06I", "X06N", "X08J", "X08P", "X09O", "X11O", "X12L", "X14N", "X15G", "X16K" )), index = structure(c(-3287, -3286, -3285), class = "Date"), class = "zoo")

But now that I have a way to re-create the error, maybe I can offer a tested solution:

 ldat <-lapply(dat, FUN=apply.monthly, sum)
 as.data.frame(ldat)
           X02R X03N X04K X04N X04R X06I X06N X08J X08P X09O X11O X12L X14N X15G X16K
1961-01-03 21.4 12.6 63.7    0 17.7 47.2   30 47.6 13.6 22.9    9 74.5  4.2 28.3 15.4

It looks as though the sum argument (eventually) gets positionally matched to the apply.monthly "FUN" argument, but you do get an error if you name them both "FUN" in the lapply argument list.

IRTFM
  • 258,963
  • 21
  • 364
  • 487