Replace value on specific column if condition is met

Question

I have a wide df with columns representing the months of many given years and the changes of colour in each month:

df <- data.frame(id = as.integer(c(123,124,125,126)),
                 change = as.integer(c(0,1,0,1)),
                 change_date = as.character(c(NA, "May.2010", NA, "Sep.2010")),
                 `Jan.2010` = as.character(c("green", "black", "pink", "grey")),
                 `Feb.2010` = as.character(c("green", "black", "pink", "grey")),
                 `Mar.2010` = as.character(c("green", "red", "pink", "grey")),
                 `Apr.2010` = as.character(c("green", "red", "pink", "grey")),
                 `May.2010` = as.character(c("green", "red", "pink", "grey")),
                 `Jun.2010` = as.character(c("green", NA, "pink", "grey")),
                 `Jul.2010` = as.character(c("green", NA, "pink", "grey")),
                 `Ago.2010` = as.character(c("red", NA, "pink", "grey")),
                 `Sep.2010` = as.character(c("red", NA, "pink", "grey")),
                 `Oct.2010` = as.character(c("red", NA, "pink", NA)),
                 `Nov.2010` = as.character(c("red", NA, "pink", NA)),
                 `Dez.2010` = as.character(c("red", NA, "grey", NA))
)
df  

  id change change_date Jan.2010 Feb.2010 Mar.2010 Apr.2010 May.2010 Jun.2010 Jul.2010 Ago.2010 Sep.2010 Oct.2010 Nov.2010 Dez.2010
1 123      0        <NA>    green    green    green    green    green    green    green      red      red      red      red      red
2 124      1    May.2010    black    black      red      red      red     <NA>     <NA>     <NA>     <NA>     <NA>     <NA>     <NA>
3 125      0        <NA>     pink     pink     pink     pink     pink     pink     pink     pink     pink     pink     pink     grey
4 126      1    Sep.2010     grey     grey     grey     grey     grey     grey     grey     grey     grey     <NA>     <NA>     <NA>

If a change occurs (change == 1), I want the correspondent column month.year in which the change occurs to receive the value "CHANGE":

  id change change_date Jan.2010 Feb.2010 Mar.2010 Apr.2010 May.2010 Jun.2010 Jul.2010 Ago.2010 Sep.2010 Oct.2010 Nov.2010 Dez.2010
1 123      0        <NA>    green    green    green    green    green    green    green      red      red      red      red      red
2 124      1    May.2010    black    black      red      red   CHANGE     <NA>     <NA>     <NA>     <NA>     <NA>     <NA>     <NA>
3 125      0        <NA>     pink     pink     pink     pink     pink     pink     pink     pink     pink     pink     pink     grey
4 126      1    Sep.2010     grey     grey     grey     grey     grey     grey     grey     grey   CHANGE     <NA>     <NA>     <NA>

The solutions I've tried failed in different ways. I appreciate your help on this.

score 3 · Accepted Answer · answered Jul 06 '23 at 10:49

In base R, you can do it like this:

# Work out which rows need changed, and which corresponding column
with(df,
  data.frame(
    index = which(change == 1),
    column = change_date[change == 1]
  )
) -> to_change

# Use that object to make all the changes in a loop
for(i in seq_along(to_change$index)){
  df[to_change$index[i], to_change$column[i]] = "CHANGE"
}

score 3 · Answer 2 · answered Jul 06 '23 at 11:01

Here is vectorised base R way -

# Row numbers
row <- which(df$change == 1)
# Column numbers
col <- match(df$change_date[row], names(df))
# Create a matrix of row/column index to change the values
df[cbind(row, col)] <- "CHANGE"
df

#   id change change_date Jan.2010 Feb.2010 Mar.2010 Apr.2010 May.2010
#1 123      0        <NA>    green    green    green    green    green
#2 124      1    May.2010    black    black      red      red   CHANGE
#3 125      0        <NA>     pink     pink     pink     pink     pink
#4 126      1    Sep.2010     grey     grey     grey     grey     grey

#  Jun.2010 Jul.2010 Ago.2010 Sep.2010 Oct.2010 Nov.2010 Dez.2010
#1    green    green      red      red      red      red      red
#2     <NA>     <NA>     <NA>     <NA>     <NA>     <NA>     <NA>
#3     pink     pink     pink     pink     pink     pink     grey
#4     grey     grey     grey   CHANGE     <NA>     <NA>     <NA>

Replace value on specific column if condition is met

2 Answers2