CODEWITHSUNDEEP
rmeanlagsummarizedplyr
jkl
jkl

Reputation: 87

summarize weekly average using daily data in R

How to add one column price.wk.average to the data such that price.wk.average is equal to the average price of last week, and also add one column price.mo.average to the data such that it equals to the average price of last month? The price.wk.average will be the same for the entire week. 

 Dates    Price   Demand  Price.wk.average   Price.mo.average
 2010-1-1   x        x
 2010-1-2   x        x
 ......
  2015-1-1  x         x

Upvotes: 1

Views: 7340

Answers (2)

CPak
CPak

Reputation: 13581

(Since I don't have enough points to comment)

I wanted to point out that Eric's answer will not distinguish average weekly price by year. Therefore, if you are interested in unique weeks (Week 1 of 2012 != Week 1 of 2015 ), you will need to do extra work to group by unique weeks.

df <- data.frame( Dates = c("2010-1-1", "2010-1-2", "2015-01-3"),
                  Price = c(50, 20, 40) )

      Dates Price
1  2010-1-1    50
2  2010-1-2    20
3 2015-01-3    40

Just to keep your data frame tidy, I suggest converting dates to POSIX format then sorting the data frame:

library(lubridate)
df <- df %>% 
      mutate(Dates = lubridate::parse_date_time(Dates,"ymd")) %>% 
      arrange( Dates )

To group by unique weeks:

df <- df %>% 
      group_by( yw = paste( year(Dates), week(Dates)))

Then mutate and ungroup.

To group by unique months:

df <- df %>% 
      group_by( ym = paste( year(Dates), month(Dates)))

and mutate and ungroup.

Upvotes: 5

Eric
Eric

Reputation: 1389

jkl,

try to post reproducible examples. It will make it easier to help you. you can use dplyr:

library(dplyr)
df <- data.frame(date = seq(as.Date("2017-1-1"),by="day",length.out = 100), price = round(runif(100)*100+50,0))
df <- df %>% 
  group_by(week = week(date)) %>%
  mutate(Price.wk.average = mean(price)) %>%
  ungroup() %>%
  group_by(month = month(date)) %>%
  mutate(Price.mo.average = mean(price))

Upvotes: 5

Related Questions