CODEWITHSUNDEEP
r
elliezee
elliezee

Reputation: 133

Refer to the next row of a different column in r

I want to create a column of when "end" time based on the following information

case<-c(3,3,3,57,57,57,57,9,9,9)
a<-c(12,15,20,12,14,15,17,14,16,19)
c<-c(25,25,25,21,21,21,21,24,24,24)

data<-data.frame(case,a,c)

   case  a  c
1     3 12 25
2     3 15 25
3     3 20 25
4    57 12 21
5    57 14 21
6    57 15 21
7    57 17 21
8     9 14 24
9     9 16 24
10    9 19 24

Column c provides the end time of the last entry. I need to create a column b by deducting that the time ended -1 of the next a column.

So the final product should look like this

   case  a  b  c
1     3 12 14 25
2     3 15 19 25
3     3 20 25 25
4    57 12 13 21
5    57 14 14 21
6    57 15 16 21
7    57 17 21 21
8     9 14 15 24
9     9 16 17 24
10    9 19 24 24

In essence, [column b row 1] is [column a row 2] - 1, by case.

My first step is to group_by(case) then I would create a dummy column of d, which is a-1, and try to match() but I am not sure how to refer to different rows.

b<-c(14,19,25,13,14,16,21,15,17,24)

data<-data.frame(case,a,b,c)

Upvotes: 0

Views: 141

Answers (2)

user2974951
user2974951

Reputation: 10385

Base R

do.call(rbind,
  by(data,list(data$case),function(x){
    cbind(
      x,
      "b"=c(tail(x$a,-1)-1,x$c[1])
    )
  })
)

     case  a  c  b
3.1     3 12 25 14
3.2     3 15 25 19
3.3     3 20 25 25
9.8     9 14 24 15
9.9     9 16 24 18
9.10    9 19 24 24
57.4   57 12 21 13
57.5   57 14 21 14
57.6   57 15 21 16
57.7   57 17 21 21

Upvotes: 1

det
det

Reputation: 5232

First step is OK, then just create new column which is lead of column (a-1) with default value (last value) equal to last value of column c.

library(dplyr)
data %>% 
  group_by(case) %>% 
  mutate(b = lead(a-1, default = last(c))) %>% 
  ungroup()

    case     a     c     b
   <dbl> <dbl> <dbl> <dbl>
 1     3    12    25    14
 2     3    15    25    19
 3     3    20    25    25
 4    57    12    21    13
 5    57    14    21    14
 6    57    15    21    16
 7    57    17    21    21
 8     9    14    24    15
 9     9    16    24    18
10     9    19    24    24

Upvotes: 1

Related Questions