I have the price for particular years, and the growth rates for all the years. Using these known growth rates, I want to compute (project) price for both subsequent and preceding years. I would prefer to use dplyr package for this task
Here is the data and the how the output (OutputPrice) would look like:
Year City GrowthRate InputPrice OutputPrice
1990 New York NA NA 69.4
1991 New York 0.9 NA 62.5
1992 New York 2.0 NA 125.0
1993 New York 0.8 100 100.0
1994 New York 0.6 NA 60.0
1995 New York 0.8 NA 48.0
1996 New York 2.0 NA 96.0
1990 Boston NA NA 200.0
1991 Boston 1.6 NA 320.0
1992 Boston 1.25 NA 400.0
1993 Boston 0.5 200 200.0
1994 Boston 1.75 NA 350.0
1995 Boston 2.5 NA 875.0
1996 Boston 0.5 NA 437.5
Here is the code to construct the data:
myData<-structure(list(Year = c(1990L, 1991L, 1992L, 1993L, 1994L, 1995L,
1996L, 1990L, 1991L, 1992L, 1993L, 1994L, 1995L, 1996L), City = structure(c(2L,
2L, 2L, 2L, 2L, 2L, 2L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), .Label = c("Boston",
"New York"), class = "factor"), GrowthRate = c(NA, 0.9, 2, 0.8,
0.6, 0.8, 2, NA, 1.6, 1.25, 0.5, 1.75, 2.5, 0.5), InputPrice = c(NA,
NA, NA, 100L, NA, NA, NA, NA, NA, NA, 200L, NA, NA, NA), OutputPrice = c(69.4,
62.5, 125, 100, 60, 48, 96, 200, 320, 400, 200, 350, 875, 437.5
)), .Names = c("Year", "City", "GrowthRate", "InputPrice", "OutputPrice"
), class = c("tbl_df", "tbl", "data.frame"), row.names = c(NA,
-14L))
Here is what I have so far:
myData %>%
group_by(City) %>%
arrange(Year) %>%
mutate(OutputPrice= ifelse(Year<1993, lead(GrowthRate,1)*lead(OutputPrice,1), ifelse(Year>1993, GrowthRate*lag(OutputPrice,1)), InputPrice)) %>%
ungroup() %>%
arrange(City)
Here is the operation I would do in a spreadsheet