CODEWITHSUNDEEP
rdata.tableiteration
JerryN
JerryN

Reputation: 2506

r rowdies iteration on a data table, again

There are at least a couple of Q/As that are similar to this but I can't seem to get the hang of it. Here's a reproducible example. DT holds the data. I want food(n) = food(n-1) * xRatio.food(n)

DT <- fread("year    c_Crust xRatio.c_Crust
X2005 0.01504110             NA
X2010         NA      0.9883415
X2015         NA      1.0685221
X2020         NA      1.0664189
X2025         NA      1.0348418
X2030         NA      1.0370386
X2035         NA      1.0333771
X2040         NA      1.0165511
X2045         NA      1.0010563
X2050         NA      1.0056368")

The code that gets closest to the formula is

DT[,res := food[1] * cumprod(xRatio.food[-1])]

but the res value is shifted up, and the first value is recycled to the last row with a warning. I want the first value of xRatio.food to be NA

Upvotes: 0

Views: 60

Answers (1)

Frank
Frank

Reputation: 66819

I'd rename/reshape...

myDT = melt(DT, id = "year", meas=list(2,3), 
  variable.name = "food", 
  value.name = c("value", "xRatio"))[, food := "c_Crust"][]

# or for this example with only one food...
myDT = DT[, .(year, food = "c_Crust", xRatio = xRatio.c_Crust, value = c_Crust)]

... then do the computation per food group with the data in long form:

myDT[, v := replace(first(value)*cumprod(replace(xRatio, 1, 1)), 1, NA), by=food]

# or more readably, to me anyways
library(magrittr)
myDT[, v := first(value)*cumprod(xRatio %>% replace(1, 1)) %>% replace(1, NA), by=food]

Alternately, there's myDT[, v := c(NA, first(value)*cumprod(xRatio[-1])), by=food], extending the OP's code, though I prefer just operating on full-length vectors with replace rather than trying to build vectors with c, since the latter can run into weird edge cases (like if there is only one row, will it do the right thing?).

Upvotes: 1

Related Questions