R Compare duplicate values ​for each row in two data sets + in any order

user_16396496 0
Viewed 86

I want to compare whether the 'values set' ​​in each row are the same. In this case, duplicated and all_equal function are not suitable.

Reproducible Sample Data

a=c(1,1)
b=c(2,2)
c=c(3,3)
d=c(4,5)


df1<-rbind(a,b,c)
df1<-df1 %>% as.data.frame()
df2<-rbind(a,d,b)
df2<-df2 %>% as.data.frame()

> df1
  V1 V2
a  1  1
b  2  2
c  3  3
> df2
  V1 V2
a  1  1
d  4  5
b  2  2

Expected output

df1$idx1 <- 1:nrow(df1)
df2$idx2 <- 1:nrow(df2)

df1
df2

df3<-full_join(df1,df2,by=c('V1','V2'))
df3

df3$need <- ifelse(is.na(df3$idx2), 'only_df1',
                    ifelse(is.na(df3$idx1), 'only_df2',
                           'duplicated'))

> df3
  V1 V2 idx1 idx2       need
1  1  1    1    1 duplicated
2  2  2    2    3 duplicated
3  3  3    3   NA   only_df1
4  4  5   NA    2   only_df2

I try... but This is complicated. I think there must be a better way. help!

r
3 Answers

Since you are already using dplyr, you may use case_when which is easier to understand and write especially when you have lot of conditions.

library(dplyr)

full_join(df1,df2,by=c('V1','V2')) %>%
  mutate(need = case_when(is.na(idx2) ~ 'only_df1', 
                          is.na(idx1) ~ 'only_df2', 
                          TRUE ~ 'duplicated'))

#  V1 V2 idx1 idx2       need
#1  1  1    1    1 duplicated
#2  2  2    2    3 duplicated
#3  3  3    3   NA   only_df1
#4  4  5   NA    2   only_df2
user_39629140

As already mentioned in the comments, your way looks ok. In case you want to see how it could be done in base:

a <- c(1,1)
b <- c(2,2)
c <- c(3,3)  #Better don't use existing function names
d <- c(4,5)

df1 <- as.data.frame(rbind(a,b,c))
df2 <- as.data.frame(rbind(a,d,b))

df1$idx1 <- seq_len(nrow(df1))  #seq_len will also work in case nrow=0
df2$idx2 <- seq_len(nrow(df2))

df3 <- merge(df1, df2, all=TRUE)

df3$need <- ifelse(is.na(df3$idx2), "only_df1",
            ifelse(is.na(df3$idx1), "only_df2",
                   "duplicated"))

df3
#  V1 V2 idx1 idx2       need
#1  1  1    1    1 duplicated
#2  2  2    2    3 duplicated
#3  3  3    3   NA   only_df1
#4  4  5   NA    2   only_df2
user_104885040

We can use

library(arsenal)
summary(comparedf(df1, df2))
user_37322710
Related