Conditional replacement of values in a column

Question

I have the following:

    ID           Value1       Value2        Code
   0001            3.3          432          A              
   0001             0           654          A              
   0001             0           63           A                
   0002             0           78           B               
   0002             1           98           B    
   0003             0           22           C    
   0003             0           65           C    
   0003             0           91           C

I need the following:

    ID           Value1       Value2        Code
   0001            3.3          432          A              
   0001             0            0           A              
   0001             0            0           A                
   0002             0            0           B               
   0002             1           98           B    
   0003             0           22           C    
   0003             0           65           C    
   0003             0           91           C

i.e., for the same "Code" if there is at least one row with Value1 !=0 then all the other rows referred to the same Code will be set to 0 (meaning that 654 and 63 for 0001 relative to Value2 will be set to 0). If this is not the case (like for 0003 nothing will be done).

Can anyone help me please?

Thank you in advance

score 3 · Accepted Answer · answered Nov 23 '22 at 15:33

dplyr

library(dplyr)
quux %>%
  group_by(Code) %>%
  mutate(Value2 = if_else(abs(Value1) > 0 | !any(abs(Value1) > 0), 
                          Value2, 0L)) %>%
  ungroup()
# # A tibble: 8 x 4
#      ID Value1 Value2 Code 
#   <int>  <dbl>  <int> <chr>
# 1     1    3.3    432 A    
# 2     1    0        0 A    
# 3     1    0        0 A    
# 4     2    0        0 B    
# 5     2    1       98 B    
# 6     3    0       22 C    
# 7     3    0       65 C    
# 8     3    0       91 C

base R

quux |>
  transform(Value2 = ifelse(ave(abs(Value1), Code, FUN = function(v) abs(v) > 0 | !any(abs(v) > 0)), 
                            Value2, 0L))
#   ID Value1 Value2 Code
# 1  1    3.3    432    A
# 2  1    0.0      0    A
# 3  1    0.0      0    A
# 4  2    0.0      0    B
# 5  2    1.0     98    B
# 6  3    0.0     22    C
# 7  3    0.0     65    C
# 8  3    0.0     91    C

data.table

library(data.table)
as.data.table(quux)[, Value2 := fifelse(abs(Value1) > 0 | !any(abs(Value1) > 0), Value2, 0L), by = Code][]
#       ID Value1 Value2   Code
#    <int>  <num>  <int> <char>
# 1:     1    3.3    432      A
# 2:     1    0.0      0      A
# 3:     1    0.0      0      A
# 4:     2    0.0      0      B
# 5:     2    1.0     98      B
# 6:     3    0.0     22      C
# 7:     3    0.0     65      C
# 8:     3    0.0     91      C

Data

quux <- structure(list(ID = c(1L, 1L, 1L, 2L, 2L, 3L, 3L, 3L), Value1 = c(3.3, 0, 0, 0, 1, 0, 0, 0), Value2 = c(432L, 654L, 63L, 78L, 98L, 22L, 65L, 91L), Code = c("A", "A", "A", "B", "B", "C", "C", "C")), class = "data.frame", row.names = c(NA, -8L))

Could simplify `!any(abs(Value1) > 0)` to `all(Value1 == 0)`. Right? — s_baldur, Nov 23 '22 at 15:49
Perhaps, but I didn't want to guess at precision of OP's floating point, where https://stackoverflow.com/q/9508518/3358272 might come into play. The `abs(.)` is (again) not assuming guaranteed absence of negative numbers. @sindri_baldur — r2evans, Nov 23 '22 at 16:20

score 1 · Answer 2 · answered Nov 23 '22 at 15:33

This should do it:

df %>% group_by(Code) %>% 
mutate(Value2 = if_else(row_number() == 1 & any(Value1 != 0), Value2, 0)) 

# A tibble: 8 × 4
# Groups:   Code [3]
# ID Value1 Value2 Code 
# <int>  <dbl>  <dbl> <fct>
# 1     1    3.3    432 A    
# 2     1    0        0 A    
# 3     1    0        0 A    
# 4     2    0       78 B    
# 5     2    1        0 B    
# 6     3    0        0 C    
# 7     3    0        0 C    
# 8     3    0        0 C

score 0 · Answer 3 · answered Nov 23 '22 at 15:32

0

We can use an if_else here. For example

library(dplyr)
dd %>% 
  group_by(ID) %>% 
  mutate(Value2=if_else(any(Value1!=0) & Value1==0, 0L, Value2))

Basically we use any() to check for non-zero values and then replace with 0s if one is found.

answered Nov 23 '22 at 15:32

MrFlick

195,160
17
277
295

Conditional replacement of values in a column

3 Answers3

dplyr

base R

data.table