capture change in sign with same value and other conditions

user_1212176 0
Viewed 26

I have a dataframe that looks like this:

df   <-  data.frame(id1 = c(232, 232, 232,233, 233, 233), value = c(20.1, -11.1, 11.1,  3.1, -4.1, 4.1), 
                  dt = c('11-20', '11-02', '11-02', '11-03', '11-04','11-04'), id2 = c(21, 22, 23, 21, 25 ,26))

I want to

  1. group by id1
  2. create a new logical column 'flag'
  3. flag should have TRUE
    • when id1 have same dt
    • same value but sign reversed and
    • different id2

the expected output will look like this

 id1 value    dt id2  flag
1 232  20.1 11-20  21  FALSE
2 232 -11.1 11-02  22  TRUE
3 232  11.1 11-02  23  TRUE
4 233   3.1 11-03  21  FALSE
5 233  -4.1 11-04  25  TRUE
6 233   4.1 11-04  26  TRUE
sign r
1 Answers

We may need an absolute column to do this. Grouped by 'id1' and the absolute value of 'value' column, check whether there are two values (n() == 2) and whether the number of distinct sign are 2. If there are more than 2 elements, and if we are checking whether more than one reversal happened, then change the condition to n() > 1 & n_distinct(sign(value)) > 1 (not clear about the logic from the OP's post)

library(dplyr)
df %>% 
  group_by(id1, grp = abs(value)) %>%
  mutate(flag = n() == 2& n_distinct(sign(value)) == 2) %>%
  ungroup %>% 
  select(-grp)

-output

# A tibble: 6 × 5
    id1 value dt      id2 flag 
  <dbl> <dbl> <chr> <dbl> <lgl>
1   232  20.1 11-20    21 FALSE
2   232 -11.1 11-02    22 TRUE 
3   232  11.1 11-02    23 TRUE 
4   233   3.1 11-03    21 FALSE
5   233  -4.1 11-04    25 TRUE 
6   233   4.1 11-04    26 TRUE
user_37322710
Related