r - 基于另一个数据框的两行比较

Question

让我们从主要数据集开始:

> dput(tbl_test1)
structure(list(X1 = structure(c(17L, 14L, 20L, 16L, 1L, 2L, 3L, 
4L, 15L, 8L, 9L, 10L, 11L, 12L, 13L, 21L, 22L, 23L, 18L, 19L, 
5L, 6L, 7L), .Label = c("Astra_1", "Astra_2", "Astra_3", "Astra_4", 
"Audi_1", "Audi_2", "Audi_3", "BMW_1", "BMW_2", "BMW_3", "BMW_4", 
"BMW_5", "Fiat_1", "Mazda_2", "Mercedes_1", "Nexia_1", "Porsche_1", 
"Scania_1", "Scania_2", "Tico_1", "VW_1", "VW_2", "VW_3"), class = "factor"), 
    X2 = structure(c(2L, 3L, 10L, 7L, 8L, 12L, 9L, 14L, 11L, 
    4L, 5L, 6L, 15L, 13L, 4L, 5L, 9L, 14L, 11L, 1L, 3L, 10L, 
    16L), .Label = c("Astra_1", "Astra_3", "Astra_4", "Audi_1", 
    "Audi_2", "Audi_3", "BMW_1", "BMW_2", "Mazda_2", "Mercedes_1", 
    "Nexia_1", "Porsche_1", "Scania_2", "Tico_1", "VW_2", "VW_3"
    ), class = "factor"), AUC_1 = c(5860133.702, 1296009.939, 
    333123.4932, 250348.9407, 1376193.334, 4080502.863, 3777603.233, 
    3503973.487, 99101538.62, 231873.8462, 87258.75465, 147430.9913, 
    1028986.892, 1451482.832, 8136.72382, 25311.41683, 131352.7137, 
    565410.8186, 30196.23792, 70184.82268, 2526321.019, 381643.2138, 
    819687.9824), AUC_2 = c(4849720.322, 928980.4715, 320547.6185, 
    223287.2029, 1340641.323, 4720329.699, 4369150.434, 3371021.243, 
    108591253.3, 266489.7601, 85384.84604, 165726.7626, 1052130.559, 
    1470876.65, 9499.927679, 49309.74984, 138482.765, 444600.7911, 
    25132.73714, 55453.67019, 2038911.81, 422559.3293, 1445477.433
    ), ratio = c(1.20834467, 1.395088463, 1.03923247, 1.121196994, 
    1.02651866, 0.864452935, 0.864608186, 1.039439753, 0.91261069, 
    0.87010415, 1.021946618, 0.889602795, 0.978003046, 0.98681479, 
    0.856503765, 0.513314647, 0.948513078, 1.271726974, 1.201470327, 
    1.265647926, 1.2390536, 0.90317072, 0.567070757), Country = structure(c(1L, 
    1L, 2L, 4L, 6L, 6L, 6L, 6L, 5L, 8L, 8L, 8L, 8L, 8L, 7L, 7L, 
    7L, 7L, 9L, 9L, 3L, 3L, 3L), .Label = c("France", "Germany", 
    "Ireland", "Italy", "Norway", "Poland", "Spain", "Sweden", 
    "Ukraine"), class = "factor")), .Names = c("X1", "X2", "AUC_1", 
"AUC_2", "ratio", "Country"), class = "data.frame", row.names = c(NA, 
-23L))

我想通过查看另一张表格来比较前两列中的汽车。为了更详细地解释，让我放下一张表:

> dput(tbl_test2)
structure(list(X = structure(c(17L, 14L, 20L, 16L, 1L, 2L, 3L, 
8L, 9L, 10L, 11L, 12L, 4L, 15L, 13L, 21L, 22L, 23L, 18L, 19L, 
5L, 6L, 7L), .Label = c("Astra_1", "Astra_2", "Astra_3", "Astra_4", 
"Audi_1", "Audi_2", "Audi_3", "BMW_1", "BMW_2", "BMW_3", "BMW_4", 
"BMW_5", "Fiat_1", "Mazda_2", "Mercedes_1", "Nexia_1", "Porsche_1", 
"Scania_1", "Scania_2", "Tico_1", "VW_1", "VW_2", "VW_3"), class = "factor"), 
    X10 = c(0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
    0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), X34 = c(0L, 0L, 
    0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 1L, 0L, 0L, 0L, 0L, 0L, 0L, 
    0L, 0L, 0L, 1L, 0L, 1L), X59 = c(0L, 0L, 0L, 0L, 0L, 0L, 
    0L, 0L, 0L, 0L, 1L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
    1L, 1L), X84 = c(0L, 0L, 1L, 0L, 0L, 0L, 0L, 0L, 1L, 0L, 
    1L, 0L, 0L, 1L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 1L, 1L), X110 = c(0L, 
    0L, 1L, 0L, 0L, 0L, 0L, 1L, 0L, 1L, 0L, 0L, 0L, 1L, 0L, 0L, 
    0L, 0L, 0L, 0L, 0L, 1L, 0L), X134 = c(0L, 0L, 0L, 0L, 0L, 
    0L, 0L, 0L, 0L, 1L, 0L, 0L, 0L, 1L, 0L, 0L, 0L, 0L, 0L, 0L, 
    0L, 0L, 0L), X165 = c(1L, 0L, 0L, 1L, 0L, 0L, 0L, 0L, 0L, 
    0L, 0L, 0L, 0L, 1L, 1L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), 
    X199 = c(1L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
    0L, 0L, 1L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), X234 = c(1L, 
    0L, 0L, 0L, 1L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 1L, 0L, 
    0L, 1L, 0L, 0L, 0L, 0L, 0L), X257 = c(1L, 0L, 0L, 0L, 1L, 
    0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 1L, 1L, 0L, 1L, 0L, 0L, 
    0L, 0L, 0L), X362 = c(0L, 0L, 0L, 0L, 1L, 0L, 0L, 0L, 0L, 
    0L, 0L, 0L, 0L, 0L, 0L, 1L, 0L, 1L, 0L, 0L, 0L, 0L, 0L), 
    X433 = c(0L, 1L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
    0L, 0L, 0L, 1L, 0L, 1L, 0L, 0L, 0L, 0L, 0L), X506 = c(0L, 
    0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 1L, 
    1L, 1L, 1L, 0L, 0L, 0L, 0L), X581 = c(0L, 0L, 0L, 0L, 0L, 
    0L, 0L, 0L, 0L, 0L, 0L, 1L, 0L, 0L, 0L, 0L, 1L, 0L, 1L, 0L, 
    0L, 0L, 0L), X652 = c(0L, 0L, 0L, 0L, 0L, 0L, 1L, 0L, 0L, 
    0L, 0L, 1L, 0L, 0L, 0L, 0L, 1L, 0L, 1L, 0L, 0L, 0L, 0L), 
    X733 = c(0L, 0L, 0L, 0L, 0L, 1L, 1L, 0L, 0L, 0L, 0L, 1L, 
    0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), X818 = c(0L, 
    0L, 0L, 0L, 0L, 1L, 1L, 0L, 0L, 0L, 0L, 1L, 1L, 0L, 0L, 0L, 
    0L, 0L, 0L, 1L, 0L, 0L, 0L), X896 = c(0L, 0L, 0L, 0L, 0L, 
    1L, 1L, 0L, 0L, 0L, 0L, 0L, 1L, 0L, 0L, 0L, 0L, 0L, 0L, 1L, 
    0L, 0L, 0L), X972 = c(0L, 0L, 0L, 0L, 0L, 1L, 1L, 0L, 0L, 
    0L, 0L, 0L, 1L, 0L, 0L, 0L, 0L, 0L, 0L, 1L, 0L, 0L, 0L), 
    X1039 = c(0L, 0L, 0L, 0L, 0L, 0L, 1L, 0L, 0L, 0L, 0L, 0L, 
    1L, 0L, 0L, 0L, 0L, 0L, 0L, 1L, 0L, 0L, 0L)), .Names = c("X", 
"X10", "X34", "X59", "X84", "X110", "X134", "X165", "X199", "X234", 
"X257", "X362", "X433", "X506", "X581", "X652", "X733", "X818", 
"X896", "X972", "X1039"), class = "data.frame", row.names = c(NA, 
-23L))

上表将先前数据集中的汽车存储在第一列中，在接下来的列中，我们有两个数字:0 或 1。我想在第一个数据中添加新列，指示第一列中是否有汽车，例如 Porsche_1 (全名很重要。我的意思是 _)和汽车第二列 Astra_3 的数据集 2 的同一列中包含数字 1。这是不正确的，因此我们在此附加列中放入 0 或 NA。如果两者在同一列中都有数字 1，例如 Porsche_1 和 Astra_1 - 数字 1 应放在附加列中。

期望的结果:最后一列的数字是随机生成的!

           X1         X2        AUC_1        AUC_2     ratio Country Comp
1   Porsche_1    Astra_3  5860133.702 4.849720e+06 1.2083447  France  0
2     Mazda_2    Astra_4  1296009.939 9.289805e+05 1.3950885  France  1
3      Tico_1 Mercedes_1   333123.493 3.205476e+05 1.0392325 Germany  1
4     Nexia_1      BMW_1   250348.941 2.232872e+05 1.1211970   Italy  0
5     Astra_1      BMW_2  1376193.334 1.340641e+06 1.0265187  Poland  0
6     Astra_2  Porsche_1  4080502.863 4.720330e+06 0.8644529  Poland  1
7     Astra_3    Mazda_2  3777603.233 4.369150e+06 0.8646082  Poland  0
8     Astra_4     Tico_1  3503973.487 3.371021e+06 1.0394398  Poland  1
9  Mercedes_1    Nexia_1 99101538.620 1.085913e+08 0.9126107  Norway  1
10      BMW_1     Audi_1   231873.846 2.664898e+05 0.8701041  Sweden  0

Answer 1

实现此目的的一种方法是使用 mapply 并检查该行中是否存在 tbl_test2 的任何 1 (第一列除外) X 中第一个汽车名称的行对于 X 中包含第二个汽车名称的行也是 1 (也就是说，检查对于 任何 列，我们是否有 TRUE & TRUE)，每对都会得到 TRUE 或 FALSE，然后您可以将其转换为 0/1 as.integer:

tbl_test1$Comp <- mapply(function(x, y) as.integer(any(unlist(tbl_test2[tbl_test2$X==x, -1]) & unlist(tbl_test2[tbl_test2$X==y, -1]))), 
                         x=as.character(tbl_test1$X1), 
                         y=as.character(tbl_test1$X2))

head(tbl_test1)
#           X1         X2        AUC_1        AUC_2     ratio Country Comp
#1   Porsche_1    Astra_3  5860133.702 4.849720e+06 1.2083447  France    0
#2     Mazda_2    Astra_4  1296009.939 9.289805e+05 1.3950885  France    0
#3      Tico_1 Mercedes_1   333123.493 3.205476e+05 1.0392325 Germany    1
#4     Nexia_1      BMW_1   250348.941 2.232872e+05 1.1211970   Italy    0
#5     Astra_1      BMW_2  1376193.334 1.340641e+06 1.0265187  Poland    0
#6     Astra_2  Porsche_1  4080502.863 4.720330e+06 0.8644529  Poland    0

编辑

如果你想知道匹配的列号，可以使用:

mapply(function(x, y) paste(which(unlist(tbl_test2[tbl_test2$X==x, -1]) & unlist(tbl_test2[tbl_test2$X==y, -1]))+1, collapse=","), x=as.character(tbl_test1$X1), y=as.character(tbl_test1$X2))