I have daily prices series over a wide range of products; I want to convert to a new dataframe with weekly or monthly data.
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.
Click a cell in the date column of the pivot table that Excel created in the spreadsheet. Right-click and select "Group," then "Days." Enter "7" in the "Number of days" box to group by week. Click "OK" and verify that you have correctly converted daily data to weekly data.
Method 1: using Python for-loops. Function new_case_count() takes in DataFrame object, iterates over it and converts indexes, which are dates in string format, to Pandas Datetime format. Based on the date's day of the week, each week's new cases count is calculated and stored in a list.
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
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.
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With