Extract first value after a specific observation

Question

I couldn't quite find what I was looking for: this comes closest: Extract rows for the first occurrence of a variable in a data frame.

So I'm trying to figure out how I could extract the row directly following a specific observation. So for every year, there will be a place in the data where the observation is "over" and then I want the first numeric value following that "over." How would I do that?

So in the minimal example below, I would want to pluck the "7" (from the threshold variable) which directly follows the "over."

Thanks much!

other.values = c(13,10,10,9,8,4,5,7,7,5)
values = c(12,15,16,7,6,4,5,8,8,4)
df = data.frame(values, other.values)%>%mutate(threshold = ifelse(values - other.values > 0, "over", values))

Ronak Shah · Accepted Answer · 2021-02-11T00:25:52.500

1

You can do :

library(dplyr)

df %>%
  mutate(grp = cumsum(threshold != 'over')) %>%
  filter(lag(threshold) == 'over' & lag(grp) != grp)

#  values other.values threshold grp
#1      7            9         7   2
#2      4            5         4   6

edited Feb 11 '21 at 00:25

answered Feb 10 '21 at 08:03

Ronak Shah

377,200
20
156
213

Thanks! so the issue is that there are actual numeric values that appear before the "over"; and the first of these values is what is being returned. – James Feb 10 '21 at 21:00
Awesome! Thanks so much! – James Feb 11 '21 at 00:38

score 0 · Answer 2 · answered Feb 10 '21 at 05:42

0

You could try something like this but surely there is a better way

df$threshold[max(which(df$threshold == "over"))+1]

Basically, this return the row index of the last match to "over" and then adds 1.

answered Feb 10 '21 at 05:42

George

903
8
22

Chris Ruehlemann · Answer 3 · 2021-02-11T10:10:37.547

0

EDIT:

You can subset on those rows which do not have the value over in threshold AND (&) which do have over in the row prior (lag):

library(dplyr)
df[which(!df$threshold=="over" & lag(df$threshold)=="over"),]
  values other.values threshold
   values other.values threshold
4       7            9         7
10      4            5         4

edited Feb 11 '21 at 10:10

answered Feb 10 '21 at 08:39

Chris Ruehlemann

20,321
4
12
34

So there are two issues: the first is that there are actual numeric values that appear before the "over" (which is what is being returned with the code you provided); and then the second issue is that since this repeats every year, I need to find the first value after over for every subsequent year (I suppose I could integrate some kind of for loop here). Thanks! – James Feb 10 '21 at 19:21

Extract first value after a specific observation

3 Answers3