Comparing all elements in a cell of a row with all elements in a cell in the other row without considering the order

user_11560966 0
Viewed 90

I have a worksheet with 2 columns A and B as given in the image which have entries separated by a comma(,). I want to compare these 2 rows cell by cell in R such that it returns 1 (TRUE) only when

  1. When all the entries match in both the rows
  2. The order is not considered while comparing(Row 5 in the image)

A sample expected output is given in the Result column of the image. I have used the match command in R but it only returns 1 (True) for Row 2 and not Row 5, which has the same entries but in a different order.

Can someone suggest any package or function in R that will help me do that (preferably a solution where there are no loops)as I want to use this on a data set which has thousands of rows.

enter image description here

rstudio match r excel comparison
2 Answers

This should help:

# example data
dt = data.frame(A = c("1,ab", "1,x,df"),
                B = c("2,ab", "1,df,x"),
                stringsAsFactors = F)

# vectorised function to check matches
ff = function(x,y) as.numeric(identical(sort(unlist(strsplit(x,","))), 
                                        sort(unlist(strsplit(y,",")))))
ff = Vectorize(ff)

# apply function
dt$Result = ff(dt$A, dt$B)

dt

#        A      B Result
# 1   1,ab   2,ab      0
# 2 1,x,df 1,df,x      1

If you might have some spaces after the commas you can use function trimws like this sort(trimws(unlist(strsplit(x,",")))) in the function above.

user_51934850

Here is a option with tidyverse

library(tidyverse)
rownames_to_column(dt, 'rn') %>%
  separate_rows(A, B) %>% 
  group_by(rn) %>% 
  summarise(Result = as.integer(all(sort(A) == sort(B)))) %>% 
  select(Result) %>% 
  bind_cols(dt, .)

data

dt <- structure(list(A = c("1,ab", "1,x,df"), B = c("2,ab", "1,df,x"
)), class = "data.frame", row.names = c(NA, -2L))
user_37322710
Related