CODEWITHSUNDEEP
r
Supersquirrel
Supersquirrel

Reputation: 71

R: Delete certain values from all columns

I have a dataframe that looks like this: 

  compare   col1    col2   col3   (...)
1   cat01 bird02  bird03  cat01   (...)
2  bird02 bird03   cat02  dog01   (...)
3   cat02 bird04 horse01  cat06   (...)
4   cat03  cat01  bird04  cat08   (...)
5   dog01 bird02   dog01 bird03   (...)

I want to change all values in col1, col2, col3 etc. that do not appear in column "compare" to NA. 

  compare   col1  col2  col3
1   cat01 bird02  <NA> cat01
2  bird02   <NA> cat02 dog01
3   cat02   <NA>  <NA>  <NA>
4   cat03  cat01  <NA>  <NA>
5   dog01 bird02 dog01  <NA>

For similar cases, I used 

data$col1[!data$col1 %in% data$compare] <- NA

before, but I have to change all columns by hand. Since this specific dataframe has a large number of columns, is there any way to make this faster and easier by comparing all other columns and their values at once?

Thank you!

Upvotes: 1

Views: 796

Answers (6)

Sven Hohenstein
Sven Hohenstein

Reputation: 81683

You can use the is.na<- function:

is.na(data[-1]) <- matrix(!as.matrix(data[-1]) %in% data$compare, nrow(data))

#   compare   col1  col2  col3
# 1   cat01 bird02  <NA> cat01
# 2  bird02   <NA> cat02 dog01
# 3   cat02   <NA>  <NA>  <NA>
# 4   cat03  cat01  <NA>  <NA>
# 5   dog01 bird02 dog01  <NA>

Upvotes: 1

akrun
akrun

Reputation: 887028

You can try

df1[-1][!mapply(`%in%`, df1[-1], df1[1])] <- NA
df1
#  compare   col1  col2  col3
#1   cat01 bird02  <NA> cat01
#2  bird02   <NA> cat02 dog01
#3   cat02   <NA>  <NA>  <NA>
#4   cat03  cat01  <NA>  <NA>
#5   dog01 bird02 dog01  <NA>

Or

df1[-1][`dim<-`(!grepl(paste(df1[,1], collapse="|"),
            as.matrix(df1[-1])), dim(df1[-1]))] <- NA

Upvotes: 2

IRTFM
IRTFM

Reputation: 263331

If I were near an R console I would have tried 

 df[ ! df %in% df[[1]] ] <- NA

That should have succeeded with a matrix but I can't test it right now. You don't need to exclude col 1 b/c they are all in col1.

EDIT: Well, that didn't work, .... but if it had been a character matrix....

 dat <- as.matrix(df)

 dat[ ! dat %in% dat[,1] ] <- NA
 dat
#-----------
  compare  col1     col2    col3   
1 "cat01"  "bird02" NA      "cat01"
2 "bird02" NA       "cat02" "dog01"
3 "cat02"  NA       NA      NA     
4 "cat03"  "cat01"  NA      NA     
5 "dog01"  "bird02" "dog01" NA

Upvotes: 1

Steven Beaupr&#233;
Steven Beaupr&#233;

Reputation: 21621

Using dplyr

library(dplyr)
df %>% mutate_each(funs(ifelse(. %in% df[,1], ., NA)), -compare)

You get:

#  compare   col1  col2  col3
#1   cat01 bird02  <NA> cat01
#2  bird02   <NA> cat02 dog01
#3   cat02   <NA>  <NA>  <NA>
#4   cat03  cat01  <NA>  <NA>
#5   dog01 bird02 dog01  <NA>

Upvotes: 2

jalapic
jalapic

Reputation: 14192

My way would be:

df[,2:ncol(df)][apply(df[,2:ncol(df)], 2, function(x) x %in% df[,1])==F] <- NA

#  compare   col1  col2  col3
#1   cat01 bird02  <NA> cat01
#2  bird02   <NA> cat02 dog01
#3   cat02   <NA>  <NA>  <NA>
#4   cat03  cat01  <NA>  <NA>
#5   dog01 bird02 dog01  <NA>

Upvotes: 0

David Arenburg
David Arenburg

Reputation: 92282

Here's a possible data.table solution to update your data set by reference (you can improve performance using set and a for loop, but you probably wouldn't need it if your data set isn't too big)

library(data.table)
indx <- unique(df$compare)
setDT(df)[, names(df)[-1] := lapply(.SD, function(x) replace(x, !x %in% indx, NA)), .SDcols = -"compare"][]
#    compare   col1  col2  col3
# 1:   cat01 bird02    NA cat01
# 2:  bird02     NA cat02 dog01
# 3:   cat02     NA    NA    NA
# 4:   cat03  cat01    NA    NA
# 5:   dog01 bird02 dog01    NA

Upvotes: 2

Related Questions