[datatable-help] Recursive function that operates on its own preceding output

[abhishekdev]

I have the price for a particular baseline year (in this case for 1993), and
the multiplication factor for all the years. Using these known
multiplication factor, I want to compute (project) price for all years
succeeding and preceding the baseline year.

Here is the input data:

    Year	City	MultiplicationFactor	Price_BaselineYear
    1990	New York	      NA    	    NA
    1991	New York	      0.9    	    NA
    1992	New York	      2.0    	    NA
    1993	New York	      0.8    	    100
    1994	New York	      0.6    	    NA
    1995	New York	      0.8    	    NA
    1996	New York	      2.0    	    NA
    1990	Boston	           NA    	    NA
    1991	Boston	           1.6    	    NA
    1992	Boston	           1.25    	    NA
    1993	Boston	           0.5    	    200
    1994	Boston	           1.75    	    NA
    1995	Boston	           2.5    	    NA
    1996	Boston	           0.5    	    NA

The code to construct the input 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"), MultiplicationFactor = 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),`Price(BaselineYear)` = c(NA, NA, NA, 100L, NA, NA, NA, NA,NA, NA,
200L, NA, NA, NA)), .Names = c("Year", "City",
"MultiplicationFactor","Price_BaselineYear"), class = c("tbl_df", "tbl",
"data.frame"), row.names = c(NA, -14L))


The output I desire (the last column, Price_AllYears):

    Year	City	MultiplicationFactor	Price_BaselineYear	Price_AllYears
    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 what I have so far thanks to @alistaire:

    myData %>%
      group_by(City) %>%
      arrange(Year) %>%
      mutate(Price_AllYears = ifelse(Year <
Year[which(!is.na(Price_BaselineYear))], 
                            lead(Price_AllYears) /
lead(MultiplicationFactor),
                            ifelse(Year >
Year[which(!is.na(Price_BaselineYear))],
                                   lag(Price_AllYears) *
MultiplicationFactor,
                                   Price_BaselineYear)))%>%
      ungroup() %>% 
      arrange(City)


This is the error I get:

>Error: object 'Price_AllYears' not found


Here is the method I would use if I had to use Excel:

    	A		B		C						D					E
    1	Year	City	MultiplicationFactor	Price_BaselineYear	Price_AllYears
    2	1990	New York	NA					NA					E3/C3
    3	1991	New York	0.9					NA					E4/C4
    4	1992	New York	2.0					NA					E5/C5
    5	1993	New York	0.8					100					D5
    6	1994	New York	0.6					NA					E5*C6
    7	1995	New York	0.8					NA					E6*C7
    8	1996	New York	2.0					NA					E7*C8
    9	1990	Boston		NA					NA					E10/C10
    10	1991	Boston		1.6					NA					E11/C11
    11	1992	Boston		1.25				NA					E12/C12
    12	1993	Boston		0.5					200					D12
    13	1994	Boston		1.75				NA					E12*C13
    14	1995	Boston		2.5					NA					E13*C14
    15	1996	Boston		0.5					NA					E14*C15



--
View this message in context: http://r.789695.n4.nabble.com/Recursive-function-that-operates-on-its-own-preceding-output-tp4721234.html
Sent from the datatable-help mailing list archive at Nabble.com.