如何在r中的两个多类别列之间找到差异/setDiff（）

Question

我将数据放在两个数据表中，如下（比此处显示的列更多） -

dataTable 1 = data_sale

site id	id country	id
1000375476	in id canada	ug10000000000000000 wisd
country	加拿大	UGD12895
1000706152	瑞士	UG10000-WISD
UG80000	-	1000797366
ITALY	UG10000	WISD
ITALY	>	UG12785

DATATABL

-	NTCD	6
1000797366	加拿大	UGD12895
1000797366	1000706152	-
1000797366	意大利	瑞士
G	意大利	UG12210

我想计算Data_sale中所有站点ID的唯一产品ID的集合差，请保留所有行。

这是我到目前为止所做的 -

1. 对于两个数据表，我都创建了一个带有所有独特产品的新列。

data_sale <-
  data_sale[, `unique_products` := paste0(unique(`Product ID`), collapse = ","), 
              keyby = c("Site Id")]

data_licenses <-
  data_licenses[, .(`unique_products` = paste0(unique(`Product ID`), collapse = ",")), 
              keyby = c("Site Id")]

2. 现在与数据_LICENS合并的数据_SALE

merge(data_sale, data_licenses, by = 'Site Id', all.x = TRUE)

现在，合并的数据表现 -

网站ID	国家	产品ID	simel_products.data_sale	simel_products.data_licenses
1000375476	加拿大	UG100000000000000000000	-WISD UG1000000000000000000000000	-WISD，UGD12895
-	WISD128955128954	UG10000	wist 12895	UG10000-WISD ，UGD12895
1000706152	瑞士	UG10000-WISD	-WISD，UG80000-NTCD-G	UG80000
-	NTCD	-G	UG100000000000000000000000000-	210
1000797366	意大利	UG12210	WISD	NA
1000706152	瑞士	UG10000	UG10000-WISD，UG12210	UG12785，UG12210

问题在于我的最后一步，我想要一个新的列显示data_sale和data_licenss产品之间的区别，看起来应该像这样 -

站点ID	乡村	产品ID	simels data_sale_sale	simelor_products.data_licenss	差异
1000375476	加拿大	ug10000 wisd ug1000000000000 wisd，	ug1000000 wisd，ug1000000 wisd， UGD12895	，UGD12895	NA
1000375476	加拿大	UGD12895	WISD，UGD12895	UGD12895	NA
100070706152 1000706152	瑞士	UGD10000 -	WISD	UG10000- WISD	UG10000
-	，	UG80000-NTCD -G	UG10000-WISD，UG80000-NTCD-G	NA	UG10000-WISD，UG80000-NTCD-G
1000797366	ITALY	-WISD	UG10000-WISD，UG122210	UG10000	-
UG12785	WIG12211211101211121101210 210	UG10000	，	UG12210	UG10000- WISD

关于如何实现它的任何潜在客户都会有很大的帮助。谢谢！

以下是使用DPUT（）的数据

structure(list(`Site Id` = c("1000375476", "1000375476", "1000706152", 
"1000706152", "1000797366", "1000797366"), Country = c("Canada", 
"Canada", "Switzerland", "Switzerland", "Italy", "Italy"), `Product ID` = c("UG10000-WISD", 
"UGD12895", "UG10000-WISD", "UG80000-NTCD-G", "UG10000-WISD", 
"UG12210"), unique_products.x = c("UG10000-WISD,UGD12895", "UG10000-WISD,UGD12895", 
"UG10000-WISD,UG80000-NTCD-G", "UG10000-WISD,UG80000-NTCD-G", 
"UG10000-WISD,UG12210", "UG10000-WISD,UG12210"), unique_products.y = c("UG10000-WISD,UGD12895", 
"UG10000-WISD,UGD12895", NA, NA, "UG12785,UG12210", "UG12785,UG12210"
)), sorted = "Site Id", class = c("data.table", "data.frame"), row.names = c(NA, 
-6L), .internal.selfref = <pointer: 0x556bb5c10a40>)

Answer 1

这将使不在data_sale中的产品中获取data_license by 网站ID中的产品。与其串联唯一的产品ID，不如将唯一列作为字符向量工作更容易。

library(data.table)

data_licenses <- data.table(`Site Id` = c("1000375476", "1000375476", "1000797366", "1000797366"),
                            Country = c("Canada", "Canada", "Italy", "Italy"),
                            `Product ID` = c("UG10000-WISD", "UGD12895", "UG12785", "UG12210"))
data_sale <- data.table(`Site Id` = c("1000375476", "1000375476", "1000706152", "1000706152", "1000797366", "1000797366"),
                        Country = c("Canada", "Canada", "Switzerland", "Switzerland", "Italy", "Italy"),
                        `Product ID` = c("UG10000-WISD", "UGD12895", "UG10000-WISD", "UG80000-NTCD-G", "UG10000-WISD", "UG12210"))

data_unique <- data_sale[
  , .(unique_products.data_sale = .(unique(`Product ID`))), c("Site Id", "Country")
][
  data_licenses[, .(unique_products = .(unique(`Product ID`))), "Site Id"],
  unique_products.data_licenses := i.unique_products,
  on = "Site Id"
][
  , difference := lapply(.I, function(i) setdiff(unique_products.data_sale[[i]], unique_products.data_licenses[[i]]))
]
print(data_unique)
#>       Site Id     Country   unique_products.data_sale unique_products.data_licenses                  difference
#> 1: 1000375476      Canada       UG10000-WISD,UGD12895         UG10000-WISD,UGD12895                            
#> 2: 1000706152 Switzerland UG10000-WISD,UG80000-NTCD-G                               UG10000-WISD,UG80000-NTCD-G
#> 3: 1000797366       Italy        UG10000-WISD,UG12210               UG12785,UG12210                UG10000-WISD

Answer 2

可能有一种方法可以尝试结合一些内置功能，但是一个具有简单自定义功能的示例：

find_differences = function(x,y){
  # x: column list of strings we want to compare to
  # y: other column list
  x = strsplit(x,',') # transform strings to lists
  y = strsplit(y,',')
  
  differences = list()

  for(i in seq(1,length(x))){  # for every row (nested-list)
    
    if(identical(x[[i]],y[[i]])){
      row_diff = NA
    }
    else{
    row_diff = paste(x[[i]][ ! x[[i]] %in% y[[i]] ],collapse=',')
    }
    
    differences = c(differences,row_diff)
  }
  return(differences)
}

使用您的示例：

example = rename(example, 
                 unique_products.data_sale = unique_products.x,
                 unique_products.data_licenses = unique_products.y)

example$difference = find_differences(example$unique_products.data_sale, example$unique_products.data_license)

> example
      Site Id     Country     Product ID   unique_products.data_sale unique_products.data_licenses                  difference
1: 1000375476      Canada   UG10000-WISD       UG10000-WISD,UGD12895         UG10000-WISD,UGD12895                          NA
2: 1000375476      Canada       UGD12895       UG10000-WISD,UGD12895         UG10000-WISD,UGD12895                          NA
3: 1000706152 Switzerland   UG10000-WISD UG10000-WISD,UG80000-NTCD-G                          <NA> UG10000-WISD,UG80000-NTCD-G
4: 1000706152 Switzerland UG80000-NTCD-G UG10000-WISD,UG80000-NTCD-G                          <NA> UG10000-WISD,UG80000-NTCD-G
5: 1000797366       Italy   UG10000-WISD        UG10000-WISD,UG12210               UG12785,UG12210                UG10000-WISD
6: 1000797366       Italy        UG12210        UG10000-WISD,UG12210               UG12785,UG12210                UG10000-WISD

Answer 3

这种快速方法如何获得每个站点的差异？然后，您可以将结果合并回任何具有站点ID的帧：

data_licenses[, .(licen_p = .(.(`Product ID`))), by = `Site Id`] %>% 
  .[data_sale[, .(sale_p= .(.(`Product ID`))), by=`Site Id`],on=.(`Site Id`)] %>% 
  .[,.(difference = toString(unlist(setdiff(sale_p, licen_p)))), by=`Site Id`]

输出：

      Site Id                   difference
1: 1000375476                             
2: 1000706152 UG10000-WISD, UG80000-NTCD-G
3: 1000797366        UG10000-WISD, UG12210