Geek asks and answers

In R, how do I set a value for a variable based on a change from a previous (or next) line?

Given a data frame:

id<-c(1,1,1,1,1,1,2,2,2,2,2,2)
t<-c(6,8,9,11,12,14,55,57,58,60,62,63)
p<-c("a","a","a","b","b","b","a","a","b","b","b","b")
df<-data.frame(id,t,p)

row id  t   p
1   1   6   a
2   1   8   a
3   1   9   a
4   1   11  b
5   1   12  b
6   1   14  b
7   2   55  a
8   2   57  a
9   2   58  b
10  2   60  b
11  2   62  b
12  2   63  b

I want to create a new variable ta, so that the value of ta will be:

  • Zero for the line in which "p" changes from a to b for the given identifier (lines 4 and 9) (I can do this)
  • In each unique id, when p is 'a', the value of ta must be counted from zero by changing t between the line in question and the line above it. For example, for line 3, the value of ta should be 0 - (11-9) = -2.
  • In each unique id, when p is 'b', the value of ta must be calculated from scratch by changing t between the line in question and the line below. For example, for line 5, the value of ta should be 0 + (12-11) = 1.

, , :

row id  t   p   ta
1   1   6   a   -5
2   1   8   a   -3
3   1   9   a   -2
4   1   11  b   0
5   1   12  b   1
6   1   14  b   3
7   2   55  a   -3
8   2   57  a   -1
9   2   58  b   0
10  2   60  b   2
11  2   62  b   4
12  2   63  b   5

cumsum() head() tail() id/in condition summing. , , . .

+3
1

. split-apply-comb, id, p=='a' p=='b', . , , .

do.call('rbind',
lapply(split(df, id), function(x) {
    # save values of `0` at transition points in `p`
    x <- cbind.data.frame(x, ta=ifelse(c(0,diff(as.numeric(as.factor(x$p))))==1, 0, NA))

    # identify indices for those points
    w <- which(x$ta==0)

    # handle `ta` values for `p=='b'`
    x$ta[(w+1):nrow(x)] <- x$ta[w] + (x$t[(w+1):nrow(x)] - x$t[w])

    # handle `ta` values for `p=='a'`
    x$ta[1:(w-1)] <- x$ta[w] - (x$t[w] - x$t[1:(w-1)])

    return(x)
})
)

:

     id  t p ta
1.1   1  6 a -5
1.2   1  8 a -3
1.3   1  9 a -2
1.4   1 11 b  0
1.5   1 12 b  1
1.6   1 14 b  3
2.7   2 55 a -3
2.8   2 57 a -1
2.9   2 58 b  0
2.10  2 60 b  2
2.11  2 62 b  4
2.12  2 63 b  5
+2

More articles:


All Articles