7

I have daily prices series over a wide range of products; I want to convert to a new dataframe with weekly or monthly data.

enter image description here

I first used xts in order to apply the to.weekly function...which works only for OHLC format. I am sure there may exist a function similar to to.weekly but for dataframe where the format is not OHLC.

There a different posts already related to this as the following: Does rollapply() allow an array of results from call to function? or Averaging daily data into weekly data

I eventually used:

length(bra)

[1] 2416

test<-bra[seq(1,2416,7),]

Would there be a more efficient approach? Thanks.

user438383
  • 5,716
  • 8
  • 28
  • 43
saradi
  • 141
  • 1
  • 1
  • 7
  • 6
    `?to.weekly` clearly states it works on OHLC *or univariate* objects. [Related](http://stackoverflow.com/q/16402915/271616). – Joshua Ulrich May 08 '13 at 14:20
  • 2
    Instead of giving us a picture of your data, it is better paste the output of `dput(brazil[1:15, ])` in your question so that we can source it into our own R sessions easily. – GSee May 08 '13 at 17:05
  • 1
    Do you want the weekly data to just be the last row of each week, or do you want to do some sort of aggregation? Maybe you want OHLC for each of the input columns? – GSee May 08 '13 at 17:06

3 Answers3

15

Let's try with this data:

library(zoo)
tt <- seq(Sys.Date(), by='day', length=365)
vals <- data.frame(A=runif(365), B=rnorm(365), C=1:365)
z <- zoo(vals, tt)

Now I define a function which extracts the year and the number of the week (drop %Y if you don't need to distinguish between years):

week <- function(x)format(x, '%Y.%W')

You can use this function to aggregate the zoo object with mean (for example):

aggregate(z, by=week, FUN=mean)

which produces this result:

                A           B  C
2013.18 0.3455357  0.34129269  3
2013.19 0.4506297  0.57665133  9
2013.20 0.3950585  0.46197173 16
2013.21 0.5990886 -0.02689994 23
2013.22 0.5115043  0.18726564 30
2013.23 0.5327597  0.16250339 37
Oscar Perpiñán
  • 4,491
  • 17
  • 28
  • why would anyone want a string instead of a date format as an index and how is this the top rated answer? As Joshua Ulricht said, `apply.weekly(df, tail, 1)` is a easier und tidier way to achieve this. – Quastiat Jan 27 '22 at 13:52
7

I'm fairly new to R but stumbled on this when I had a similar problem. I needed to convert xts data that isn't OHLC. to.monthly states that it can handle univariate series but it also says in the details that it only supports returning OHLC. I think it might work by just setting OHLC=FALSE. Alternatively, the source of to.period uses the following function which worked for me even to convert several series (all with same time index)

data.monthly <- data[endpoints(data, on="months", k=1), ]

Short and clean and even copies the column names.

ah bon
  • 9,293
  • 12
  • 65
  • 148
KDJ
  • 83
  • 1
  • 5
  • 1
    Just saw it is the same answer as here: [link](https://stackoverflow.com/questions/16402915/from-daily-time-series-to-weekly-time-series-in-r-xts-object?lq=1) – KDJ Nov 12 '14 at 10:24
  • Hello, this solution is getting its value last week. It does not average. To reach the average value `apply.weekly (data, colMeans)` can be used. – NCC1701 Sep 25 '20 at 13:57
1

Using tidyquant can help you achieve this without converting series into zoo or xts.

library(tidyquant)

library(zoo)

tt <- seq(Sys.Date(), by='day', length=365)  
vals <- data.frame(A=runif(365), B=rnorm(365), C=1:365)
z <- data.frame(vals, tt)

Now, use tidyquant library

z <- z  %>% tq_transmute(mutate_fun = apply.monthly, FUN = mean, na.rm = TRUE)
Anoushiravan R
  • 21,622
  • 3
  • 18
  • 41
Azam Yahya
  • 646
  • 1
  • 7
  • 10