How to replace all values in multiple columns that are not among the values in another column

user_15798970 0
Viewed 149

I have a dataset with one variable with participant IDs and several variables with peer-nominations (in form of IDs).

I need to replace all numbers in the peer-nomination variables, that are not among the participant IDs, with NA.

Example: I have

ID       PN1       PN2
1         2         5
2         3         4
4         6         2      
5         2         7

I need

ID       PN1       PN2
1         2         5
2         NA        4
4         NA        2      
5         2         NA

Would be great if someone can help! Thank you so much in advance.

na r replace
5 Answers
library(tidyverse)

df %>%
  mutate(across(-ID, ~if_else(. %in% ID, ., NA_real_)))

which gives:

#   ID PN1 PN2
# 1  1   2   5
# 2  2  NA   4
# 3  4  NA   2
# 4  5   2  NA

Data used:

df <- data.frame(ID = c(1, 2, 4, 5),
                 PN1 = c(2, 3, 6, 2),
                 PN2 = c(5, 4, 2, 7))
user_27257730

Here is a base R way.
The lapply loop on all columns except for the id column, uses function is.na<- to assign NA values to vector elements not in df1[[1]]. Then returns the changed vector.

df1[-1] <- lapply(df1[-1], function(x){
  is.na(x) <- !x %in% df1[[1]]
  x
})

df1
#  ID PN1 PN2
#1  1   2   5
#2  2  NA   4
#3  4  NA   2
#4  5   2  NA

Data in dput format

df1 <-
structure(list(ID = c(1L, 2L, 4L, 5L), 
PN1 = c(2L, NA, NA, 2L), PN2 = c(5L, 4L, 2L, NA)), 
row.names = c(NA, -4L), class = "data.frame")
user_82454060

An alternative with Base R,

df[,-1][matrix(!(unlist(df[,-1]) %in% df[,1]),nrow(df))] <- NA
df

gives,

  ID PN1 PN2
1  1   2   5
2  2  NA   4
3  4  NA   2
4  5   2  NA
user_72243540

We could use mutate with case_when:

library(dplyr)
df %>% 
  mutate(across(starts_with("PN"), ~case_when(!(. %in% ID) ~ NA_real_,
                                              TRUE ~ as.numeric(.))))

Output:

# A tibble: 4 x 3
     ID   PN1   PN2
  <int> <dbl> <dbl>
1     1     2     5
2     2    NA     4
3     4    NA     2
4     5     2    NA
user_133216470

With data.table you can (l)apply the function fifelse() to every column you have selected with .SD & .SDcols.

require(data.table)

cols = grep('PN', names(df)) # column indices (or names)
df[ , lapply(.SD, function(x) fifelse(!x %in% ID, NA_real_, x)),
    .SDcols = cols ]

Data from @deschen:

df = data.frame(ID = c(1, 2, 4, 5),
                PN1 = c(2, 3, 6, 2),
                PN2 = c(5, 4, 2, 7))
setDT(df)
user_47428890
Related