merging data frames while assigning factors to missing data

1
votes

I have two data frames that have overlap between samples (df1 and df2). I want to create a new data frame (df3) from these while assigning "absent" (a factor) to the samples in df1 that are not present in df2. I then create a 4th data frame to do an outer join on df2 and df3.

I have figured out a very long way to do this, but would appreciate suggestions on how to make this code more succinct. 

Sample <- c("TG1","TG2","TG3","TG4","TG5","TG6","TG7","TG8","TG9","TG10")
blaOKP <- c(rep("-",10))
tet.B <- c(rep("-", 10))
df1<- data.frame(Sample,blaOKP,tet.B)

Sample <- c("TG1","TG4","TG8")
gyrA <- c(rep("T83I",3))
gyrB <- c(rep("D87N",3))
df2 <- data.frame(Sample,gyrA,gyrB)

df3 <- df1[-which(df1$Sample %in% df2$Sample),]
df3$gyrA <- "absent"
df3$gyrB <- "absent"
df3 <- df3[,c(1,3:5)]


require(plyr)
df4 <- join(x=df2, y=df3, by="Sample", type="full")
2
rmergedplyr

2 Answers

2
votes

Here is a way to get the result similar to "df4" (ordered by "Sample" column). This would be faster for big datasets. Convert the "df1" to data.table by setDT and set the key variable as "Sample" (setkey). Then join the "df2" with "df1" after removing the 2nd column ("blaOKP") as it was not in the "df4". Assign the "tet.B" column to "NA", then assign columns "2:4" with "absent" and "-" for those rows that are "NA" in "gyrA"

library(data.table)
setkey(setDT(df2), Sample)[df1[-2]][, 
       tet.B := NA_character_][is.na(gyrA),
         2:4 := list('absent', 'absent', '-')][]
2
votes

Joining conventions in dplyr is illustrated nicely here.

anti_join(df1,df2, by="Sample") %>% 
  mutate(gyrA="absent", gyrB="absent") %>% 
  full_join(df2, by="Sample")