CODEWITHSUNDEEP
rdplyr
john
john

Reputation: 1036

Lag based on condition

I have historical monthly data and need to perform rolling calculation. Price of each period will be compared to 3 years back date i.e. Current Price / Base Price. Here Base is 3 years past date. It will be rolling for each month. For every month it should be compared 3 years paste date. I am using lag function to find out past date. It returns NA before Jan-2013 which is correct.

I want to add additional criteria - if minimum date of combination of (Location, Asset, SubType) is post year 2010, it should be compared with minimum date of the combination. For example minimum date is Jan-2014 so all the prices after Jan-2014 should be compared with Jan-2014 (static base year).

You can read data from the code below -

library(readxl)
library(httr)
GET("https://sites.google.com/site/pocketecoworld/Trend_Sale%20-%20Copy.xlsx", write_disk(tf <- tempfile(fileext = ".xlsx")))
dff <- read_excel(tf)

My code -

dff <- dff %>% group_by(Location, Asset, SubType) %>% 
  mutate(BasePrice=lag(Price, 36), 
         Index = round(100*(Price/BasePrice), 1)) %>% 
  filter(Period >= '2013-01-31')

Upvotes: 0

Views: 81

Answers (1)

Ronak Shah
Ronak Shah

Reputation: 388817

Do you mean something like this ?

library(dplyr)

dff %>%
  group_by(Location, Asset, SubType) %>% 
  mutate(BasePrice= if(lubridate::year(min(Period)) > 2010) 
                       Price[which.min(Period)] else lag(Price, 36), 
         Index = round(100*(Price/BasePrice), 1))

If minimum date in Period is after 2010 we select the Price of minimum Period value or use 3 year earlier Price as BasePrice.

Upvotes: 1

Related Questions