In R, replace rowvalues based on colname matching rowname in another column

Question

I want R to replace rows with NA values if the column name matches the row name of another column. For instance, if individual 123 was last registered in array 6 (Very.last=Last.6), it will replace the time in Last.6 with NAs.

E.g. Before

ID	Last.5	Last.6	Very.last
123	2021-05-03	2021-05-04	Last.8
124	2021-04-01	2021-04-15	Last.6

After replacing its last registrations with NA:

ID	Last.5	Last.6	Very.last
123	2021-05-03	2021-05-04	Last.8
123	2021-04-01	NA	Last.6

Any suggestions?

Have tried using dplyr::filter, but not sure how to filter based on the row name matching a column name for a certain individual.

Answer 1

Use cur_column() within dplyr::across() to test each column name against Very.last.

library(dplyr)

dat %>%
  mutate(across(
    Last.1:Last.4,
    ~ if_else(cur_column() == Very.last, NA_real_, .x)
  ))

# A tibble: 4 × 6
     ID Last.1 Last.2 Last.3 Last.4 Very.last
  <int>  <dbl>  <dbl>  <dbl>  <dbl> <chr>    
1   121  7.10    9.62  8.73   NA    Last.4   
2   122  2.46   NA     0.411   5.66 Last.2   
3   123 NA       5.74  6.61    5.94 Last.1   
4   124  0.914   7.64 NA       3.65 Last.3   

Example data:

set.seed(13)

dat <- tibble::tibble(ID = 121:124)

for (i in 1:4) {
  dat[[paste0("Last.", i)]] <- runif(4, 0, 10)
}

dat[["Very.last"]] <- paste0("Last.", sample(4))