r - Recursive function that operates on its own preceding output -
i have price particular baseline year (in case 1993), , multiplication factor years. using these known multiplication factor, want compute (project) price years succeeding , preceding baseline year.
here 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 construct 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 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 have far @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 error get:
error: object 'price_allyears' not found
here method use if had use excel:
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
fun inputs set of row numbers, subsets mydata rows , determines index of base value, ix.base. first check there 1 baseline price , if not return na; otherwise, calculate multipliers prior base, hd, , after base, tl. each of these can use cumprod avoid type of iterative calculation shown in spreadsheet formulas in question. multiply calculated multipliers base price. use ave apply each city. no packages 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
Comments
Post a Comment