R: Update with value of previous row (subject to condition)

Question

I would like to update the value in a table with values of the previous row, within groups, (and probably stop the updates on a given condition)

Here is an example:

set.seed(12345)

field <- data.table(time=1:3, player = letters[1:2], prospects = round(rnorm(6),2))
setkey(field, player, time)
field[time == 1, energy := round(rnorm(2),2)] #initial level - this is what I want to propagate down the table
#let 'prospects < 0.27' be the condition that stops the process, and sets 'energy = 0'
#player defines the groups within which the updates are made

Here is the table I have.

> field
time player prospects energy
1:    1      a      0.81  -0.32
2:    2      a      0.25     NA
3:    3      a      2.05     NA
4:    1      b      1.63  -1.66
5:    2      b      2.20     NA
6:    3      b      0.49     NA

Here is the table I want.

> field
time player prospects energy
1:    1      a      0.81  -0.32
2:    2      a      0.25  0
3:    3      a      2.05  0
4:    1      b      1.63  -1.66
5:    2      b      2.20  -1.66
6:    3      b      0.49  -1.66

Thanks in advance

IF you're updating values with ones from previous row, shouldn't `energy` for 5th and 6th rows be `-1.66`? — Arun, Sep 20 '13 at 11:10
I don't see how this necessarily has to depend on the previous value algorithmically. Just figure out what the value should be for each group, set it, and then go back later and set all the energy's to 0 if prospects is below zero. — Ari B. Friedman, Sep 20 '13 at 11:20
I just fixed that. The example addresses both the update and stop conditions now. — user2627717, Sep 20 '13 at 11:23

score 2 · Accepted Answer · edited Sep 20 '13 at 11:46

Probably there are better ways, but this is what came to my mind. This makes use of roll=TRUE argument. The idea is to first set energy=0.0 where prospects < 0.27:

field[prospects < 0.27, energy := 0.0]

Then, if we remove the NA values from field, we can use roll=TRUE by doing a join with all combinations as follows:

field[!is.na(energy)][CJ(c("a", "b"), 1:3), roll=TRUE][, prospects := field$prospects][]
#    player time prospects energy
# 1:      a    1      0.81   0.63
# 2:      a    2      0.25   0.00
# 3:      a    3      2.05   0.00
# 4:      b    1      1.63  -0.28
# 5:      b    2      2.20  -0.28
# 6:      b    3      0.49  -0.28

We've to reset prospects because the roll changes it too. You could do it better, but you get the idea.

A variation, so that the roll is performed only on energy column:

field[!is.na(energy)][CJ(c("a", "b"), 1:3), list(energy), 
           roll=TRUE][, prospects := field$prospects][]

Or it may be simpler to use na.locf from package zoo :

field[time == 1, energy := round(rnorm(2),2)]
field[prospects < 0.27, energy := 0.0]
require(zoo)
field[, energy := na.locf(energy, na.rm=FALSE)]

which works if the first row of each group is guaranteed to be non-NA, which it is here by construction. But if not, you can run na.locf by group, too :

field[, energy := na.locf(energy, na.rm=FALSE), by=player]

I think you need to check that energy is NA before setting it to 0, and can avoid `zoo` if want to: `field[prospects < 0.27 & is.na(energy), energy := 0][, energy := energy[1], by = cumsum(!is.na(energy))][]` — eddi, Sep 20 '13 at 14:34

score 0 · Answer 2 · answered Sep 20 '13 at 11:40

0

something like this ?

ddply(field, 'player', function(x) {
  baseline <- x[x$time == 1, 'energy']
  x$energy <- baseline
  ind <- which(x$prospects < 0.27)
  if (length(ind)) {
    x[min(ind):nrow(x), 'energy'] <- 0
  }
  x
})

answered Sep 20 '13 at 11:40

Karl Forner

4,175
25
32

R: Update with value of previous row (subject to condition)

2 Answers2

Linked