Removing or Setting pairs to NA based on conditions in another data frame (R)

Question

[selplat21]

I have two dataframes loaded in R:

File 1:

Sample1       Sample2        Metric

AAA        BBB        0.1

BBB        DDD        0.4

CCC        FFF        1.2

File 2

Sample        Site
AAA        A
BBB        C
CCC        B
DDD        C
EEE        A
FFF        B

I want to set the metric to NA in any rows in file 1 where both samples do not have the same Site in file 2.

Answer 1

[mark.ziemann]

Let's say these objects exist in your R session as file1 and file2, then you can use apply to loop over each row in file1 like this:

res <- apply(X=file1,MARGIN=1,FUN=function(x) { 
  S1=x[1]
  S2=x[2]
  S1F2 <- file2[which(file2[,"Sample"]==S1),"Site"]
  S2F2 <- file2[which(file2[,"Sample"]==S2),"Site"]
  if ( S1F2 == S2F2 ) {
    METRIC <- x[3]
  } else {
    METRIC <- NA
  }
  return(as.numeric(METRIC))
} )

file1$Metric2 <- res

The result will look like this:

  Sample1 Sample2 Metric Metric2
1     AAA     BBB    0.1      NA
2     BBB     DDD    0.4     0.4
3     CCC     FFF    1.2     1.2

[selplat21]

You rock. Thank you!