Modify column value based on another column value

Question

This is creating troubles to me,I am using dplyr and I want to change the value of each Week(W1 to W3) based on the value of CP: if < CP then 0

      CP             W1              W2              W3             W4
   <dbl>           <dbl>           <dbl>          <dbl>           <dbl>
 1    50               0              60              0               0
 4    10               0               0              0               0
 5    50              20              20             21              50
 6    10               5               0              0              21
 8    10               0              31              0               0

The desired output should is the following:

     CP             W1              W2              W3             W4 
    <dbl>           <dbl>           <dbl>          <dbl>           <dbl>
1    50               0              60              0               0
4    10               0               0              0               0
5    50               0               0              0              50
6    10               0               0              0              21
8    10               0              31              0               0

Do you have any idea on how to handle this problem with dplyr or other? Thank you!

Answer 1

You could go for mutate_at:

library(dplyr)

df %>%
  mutate_at(vars(starts_with("W")), funs(ifelse(. < CP, 0, .)))

Output:

  CP W1 W2 W3 W4
1 50  0 60  0  0
2 10  0  0  0  0
3 50  0  0  0 50
4 10  0  0  0 21
5 10  0 31  0  0

Note that starts_with will match any column name that starts with W.

If this is a problem (e.g. if you have other columns with that pattern which you don't want to consider), you could also use matches with a regular expression that would only consider those W that are followed by a number:

df %>%
  mutate_at(vars(matches("^W[0-9]+")), funs(ifelse(. < CP, 0, .)))