CODEWITHSUNDEEP
rlag
Clara Rodriguez
Clara Rodriguez

Reputation: 55

How can i LAG the previous value that meets a condition in other column (R)?

I would like to return the previous value of each row, but not the n = 1, the previous must meet a condition in other column. In this case it would be if Presence = 1.

Table with expected result

enter image description here

Thanks!

Upvotes: 3

Views: 970

Answers (1)

Martin Gal
Martin Gal

Reputation: 16978

You could use dplyr and tidyr:

library(dplyr)
library(tidyr)

data %>% 
  group_by(person, indicator = cumsum(presence)) %>% 
  mutate(expected_lag = ifelse(presence == 0, NA, presence * result)) %>% 
  fill(expected_lag, .direction = "down") %>% 
  group_by(person) %>% 
  mutate(expected_lag = lag(expected_lag)) %>% 
  select(-indicator) %>% 
  ungroup()

which returns

# A tibble: 9 x 4
  person presence result expected_lag
  <chr>     <dbl>  <dbl>        <dbl>
1 Ane           1      5           NA
2 Ane           0      6            5
3 Ane           0      4            5
4 Ane           1      8            5
5 Ane           1      7            8
6 John          0      9           NA
7 John          1      2           NA
8 John          0      4            2
9 John          1      3            2

Data

For simplification I removed the date column.

structure(list(person = c("Ane", "Ane", "Ane", "Ane", "Ane", 
"John", "John", "John", "John"), presence = c(1, 0, 0, 1, 1, 
0, 1, 0, 1), result = c(5, 6, 4, 8, 7, 9, 2, 4, 3)), class = c("spec_tbl_df", 
"tbl_df", "tbl", "data.frame"), row.names = c(NA, -9L), spec = structure(list(
    cols = list(person = structure(list(), class = c("collector_character", 
    "collector")), presence = structure(list(), class = c("collector_double", 
    "collector")), result = structure(list(), class = c("collector_double", 
    "collector"))), default = structure(list(), class = c("collector_guess", 
    "collector")), skip = 1L), class = "col_spec"))

Upvotes: 3

Related Questions