Removing all occurrences from a dataframe that occur in a second dataframe

user_11286620 0
Viewed 39

I have two dataframes. I am looking to drop all the rows that match a second dataframe. I know there are similar questions out there but the solutions didnt work for me

dates <- rep(seq(as.Date("2004/01/01"), as.Date("2020/12/31"), "days"), each=20)
Animal_id <- rep(1:20, times=length(unique(dates)))
df1 <- data.frame(dates=dates, id=Animal_id)

dates2<-rep(seq(as.Date("2004/01/01"), as.Date("2020/12/31"), "days"), each=2)
Animal_id2<-rep(1:2, times=length(unique(dates2)))
df2<- data.frame(dates=dates2, id=Animal_id2)
df2 <- df2[-4, ]
df2 <- df2[-6, ]

##I would like to ensure that there any animal in df2 is removed from df1

df1$remove<-paste(df1$dates,df1$id,sep="-")
df2$remove<-paste(df2$dates,df1$id,sep="-")

dim(df1)
dim(df2)


anti_join(df1, df1, by = "remove")

I have also found the following and tried but it does not work

df1[!(df1$remove %in% df2$remove),]

I do not get any error messages, it just simply does not remove the columns (the dimensions of the data do not change). My actual dataset is quite large and I am hoping to avoid having to type out every date+ID combo I would like to filter out.

Is there a way I can get R to go through and remove matches between two dataframes when I need to do this over multiple columns (i.e. I can't just use ID because there will be differences in dates between the two)

r
1 Answers

If I understand you correct this should be the correct code (as inidcated by @Waldi) in the comments:

anti_join....return all rows from x where there are not matching values in y, keeping just columns from x.

library(dplyr)

anti_join(df1, df2, by="id")

output:

 A tibble: 111,780 x 2
   dates         id
   <date>     <int>
 1 2004-01-01     3
 2 2004-01-01     4
 3 2004-01-01     5
 4 2004-01-01     6
 5 2004-01-01     7
 6 2004-01-01     8
 7 2004-01-01     9
 8 2004-01-01    10
 9 2004-01-01    11
10 2004-01-01    12
# ... with 111,770 more rows
user_133216470
Related