CODEWITHSUNDEEP
rdataframedplyr
jackbio
jackbio

Reputation: 130

How to compute running sum conditional on TRUE & FALSE

I am attempting to create a new column that is a conditional difference based on a column of TRUE and FALSE. If the lag 1 row is FALSE then we should compute a difference from either the beginning or the last TRUE row, whichever is later in the dataframe, however if the lag 1 row is TRUE then the difference should be should be reset.

I would like to use the dplyr::mutate function as much as possible. I'm attempting to use dplyr::lag with an ifelse() but I'm having a hard time with the conditions

 dat <- data.frame(logic_col = c(F, F, T, T, F, F, F, T, F),
                   time_col = c(200, 435, 567, 895, 1012, 1345, 1456, 1700, 1900),
                   expected_col_unseen = c(200, 435, 567, 328, 117, 450, 561, 805, 200))

Upvotes: 1

Views: 480

Answers (2)

M--
M--

Reputation: 28955

We can do something like this using tidyr and dplyr:

library(dplyr)
library(tidyr)

dat %>% 
  mutate(tmp = lag(logic_col * time_col),
         tmp = ifelse(tmp==0, NA,tmp)) %>% 
  tidyr::fill(tmp, .direction = c("down")) %>% 
  mutate(out = time_col - ifelse(is.na(tmp), 0,tmp)) %>% 
  select(-tmp)

#>   logic_col time_col expected_col_unseen out
#> 1     FALSE      200                 200 200
#> 2     FALSE      435                 435 435
#> 3      TRUE      567                 567 567
#> 4      TRUE      895                 328 328
#> 5     FALSE     1012                 117 117
#> 6     FALSE     1345                 450 450
#> 7     FALSE     1456                 561 561
#> 8      TRUE     1700                 805 805
#> 9     FALSE     1900                 200 200

Upvotes: 1

r2evans
r2evans

Reputation: 160447

I'll throw this out there in case manually calculating it is just confusing things:

library(dplyr)
dat %>%
  group_by(grp = cumsum(lag(!logic_col, default=FALSE))) %>%
  mutate(out = c(time_col[1], diff(time_col))) %>%
  ungroup()
# # A tibble: 9 x 5
#   logic_col time_col expected_col_unseen   grp   out
#   <lgl>        <dbl>               <dbl> <int> <dbl>
# 1 FALSE          200                 200     0   200
# 2 FALSE          435                 435     1   435
# 3 TRUE           567                 567     2   567
# 4 TRUE           895                 328     2   328
# 5 FALSE         1012                 117     2   117
# 6 FALSE         1345                 450     3  1345
# 7 FALSE         1456                 561     4  1456
# 8 TRUE          1700                 805     5  1700
# 9 FALSE         1900                 200     5   200

Upvotes: 1

Related Questions