Using rbind and lapply to conditionally copy rows between two dataframes with identical columns in R

1
votes

I have read through other stackoverflow questions on this topic, but I'm still lost. I think I need to use lapply, but I'm failing to see exactly how to do it.

Let's say I have the following two dataframes:

df1 <- data.frame(Color = c("Red", "Green", "Blue", "Green", "Purple", "Red"),
Year = c(1999, 2008, 2010, 2018, 2017, 2018),
License = c("123ABC", "544HGB", "923LWD", "443JFD", "889WER", "932OIF"))

df2 <- data.frame(Color = c("White", "Green", "Black", "Silver", "Purple", "Blue"),
Year = c(2013, 2008, 2004, 2012, 2017, 2019),
License = c("342UDD", "544HGB", "398KJX", "654KIR", "889WER", "874SSD"))

I want R to loop through df2, and every time it encounters a value in the license column that is not present in the license column of df1, it should append the entire row containing that license value from df2 to the top of df1 (i.e. make it the top row). Can anyone advise on the right way to do this?

2
rdataframeapplylapplyrbind
Are those supposed to have three columns? As you've pasted it, they have 1 row and 18 columns. (Perhaps add c( and )?) - r2evans
Yes, my apologies, thanks for spotting that. I forgot c(), just went back and added that. - ben_p_4370

2 Answers

1
votes

Base R

rbind(df2[ ! df2$License %in% df1$License, ], df1)
#     Color Year License
# 1   White 2013  342UDD
# 3   Black 2004  398KJX
# 4  Silver 2012  654KIR
# 6    Blue 2019  874SSD
# 11    Red 1999  123ABC
# 2   Green 2008  544HGB
# 31   Blue 2010  923LWD
# 41  Green 2018  443JFD
# 5  Purple 2017  889WER
# 61    Red 2018  932OIF

Data

df1 <- structure(list(Color = c("Red", "Green", "Blue", "Green", "Purple", "Red"), Year = c(1999, 2008, 2010, 2018, 2017, 2018), License = c("123ABC", "544HGB", "923LWD", "443JFD", "889WER", "932OIF")), class = "data.frame", row.names = c(NA, -6L))
df2 <- structure(list(Color = c("White", "Green", "Black", "Silver", "Purple", "Blue"), Year = c(2013, 2008, 2004, 2012, 2017, 2019), License = c("342UDD", "544HGB", "398KJX", "654KIR", "889WER", "874SSD")), class = "data.frame", row.names = c(NA, -6L))
1
votes

You can use anti_join to get rows in df2 that are not in df1 and bind them with df1.

library(dplyr)
result <- bind_rows(df1, anti_join(df2, df1, by = 'License'))
result

#    Color Year License
#1     Red 1999  123ABC
#2   Green 2008  544HGB
#3    Blue 2010  923LWD
#4   Green 2018  443JFD
#5  Purple 2017  889WER
#6     Red 2018  932OIF
#7   White 2013  342UDD
#8   Black 2004  398KJX
#9  Silver 2012  654KIR
#10   Blue 2019  874SSD