CODEWITHSUNDEEP
r
NewUsr
NewUsr

Reputation: 69

Conditional replacement of values in a column

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

Upvotes: 0

Views: 39

Answers (3)

Juan C
Juan C

Reputation: 6132

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

Upvotes: 1

r2evans
r2evans

Reputation: 160397

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))

Upvotes: 3

MrFlick
MrFlick

Reputation: 206167

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.

Upvotes: 0

Related Questions