CODEWITHSUNDEEP
rloopsrecursionmatrixdataframe
M. Sadu
M. Sadu

Reputation: 33

Recursive function that operates on its own preceding output

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

Upvotes: 1

Views: 76

Answers (1)

G. Grothendieck
G. Grothendieck

Reputation: 270020

fun inputs a set of row numbers, subsets MyData to those rows and determines the index of the base value, ix.base. First check that there is exactly one baseline price and if not return NA; otherwise, calculate the multipliers prior to the base, hd, and after the base, tl. For each of these we can use cumprod to avoid the type of iterative calculation shown in the spreadsheet formulas in the question. Finally multiply the calculated multipliers by the base price. Use ave to apply this to each city. No packages are used:

fun <- function(ix) with(MyData[ix, ], {
  ix.base <- which(!is.na(Price_BaselineYear))
  if (length(ix.base) != 1) return(NA)
  hd <- rev(cumprod(rev(1/head(MultiplicationFactor, ix.base)[-1])))
  tl <- cumprod(tail(MultiplicationFactor, - ix.base))
  Price_BaselineYear[ix.base] * c(hd, 1, tl)
})
transform(MyData, Price_AllYears = ave(seq_along(Year), City, FUN = fun))

giving:

   Year     City MultiplicationFactor Price_BaselineYear Price_AllYears
1  1990 New York                   NA                 NA         69.444
2  1991 New York                 0.90                 NA         62.500
3  1992 New York                 2.00                 NA        125.000
4  1993 New York                 0.80                100        100.000
5  1994 New York                 0.60                 NA         60.000
6  1995 New York                 0.80                 NA         48.000
7  1996 New York                 2.00                 NA         96.000
8  1990   Boston                   NA                 NA        200.000
9  1991   Boston                 1.60                 NA        320.000
10 1992   Boston                 1.25                 NA        400.000
11 1993   Boston                 0.50                200        200.000
12 1994   Boston                 1.75                 NA        350.000
13 1995   Boston                 2.50                 NA        875.000
14 1996   Boston                 0.50                 NA        437.500

Upvotes: 1

Related Questions