Reference column from next row with different value

Question

I have a large dataset with this form:

df <- data.frame(event = c("request","request","response", "request"), 
      value = 1:4)
event      value
request     1
request     2
response    3
request     4

I'd like write to next_different_event_value, the value in the row with next different event. Desired output:

event      value    next_different_event_value
request     1       3
request     2       3
response    3       4
request     4       NA

Ideally, I can do this with tidyverse approach and without joins.

Thanks.

For more context, my actual value column is timestamp data. — user2579689
– user2579689, Commented Dec 27, 2018 at 17:47

tmfmnk · Accepted Answer · 2018-12-27 23:18:27Z

3

A dplyr solution:

df %>%
 mutate(next_different_event_value = ifelse(event != lead(event), lead(value), NA)) %>%
 fill(next_different_event_value, .direction = "up")

     event value next_different_event_value
1  request     1                          3
2  request     2                          3
3 response     3                          4
4  request     4                         NA

It compares whether the "event" is the same as the next "event" row. If not, it assigns the value from the next "event" row, otherwise it assigns NA. Then, it fills the missing values with the last non-NA value from down to up.

edited Dec 27, 2018 at 23:18

answered Dec 27, 2018 at 18:16

tmfmnk

40.4k4 gold badges54 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2579689 Over a year ago

this solved the underlying problem for me: github.com/robinsones/funneljoin

Collectives™ on Stack Overflow

Reference column from next row with different value

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related