R dplyr: product of two columns

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

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.