检查每个唯一值是否具有相同的唯一标识符。

Question

我有一个看起来像这样的Excel表格：

Col1    Col2
IJ-123  A2B1
IJ-123  A2B1
IJ-456  C2C2
IJ-456  c2c2
IJ-456  D1e2
IJ-789  LJ87
IJ-456  
IJ-789  LJ98

x = data.frame(
  Col1 = c("IJ-123", "IJ-123", "IJ-456", "IJ-456", 
           "IJ-456", "IJ-789", "IJ-456", "IJ-789"), 
  Col2 = c("A2B1", "A2B1", "C2C2", "c2c2",
           "D1e2", "LJ87", NA, "LJ98")
)

我希望添加一列并检查（对于每个唯一的Col2值）在Col1中分配的值是TRUE还是FALSE。

输出：

Col1    Col2  Result
IJ-123  A2B1  TRUE
IJ-123  A2B1  TRUE
IJ-456  C2C2  TRUE
IJ-456  c2c2  TRUE
IJ-456  D1e2  FALSE
IJ-789  LJ87  TRUE (Because Col2 count=1 for this value)
IJ-456        C2C2
IJ-789  LJ98  TRUE (Because Col2 count=1 for this value)

逻辑：

1. 如果col2中有超过1个相同的值，则检查仅针对这些col2值的相应col1值是否相同
2. 如果只有一个col2值，则检查col1是否唯一，但仅与多次出现的col2值的col1值进行比较。
3. 在这些字段中，Col2中有空白数据，如果我们有重复的Col1值，则在结果中显示映射到这些Col1的Col2值（参见第7行）。

对于此问题，我有一个 excel 公式 =IF(COUNTIF($B$2:$B$8,B2)=1,SUMPRODUCT(--(($A$2:$A$8=A2)*(COUNTIF($B$2:$B$8,$B$2:$B$8))>1))=0,COUNTIFS($B$2:$B$8,B2,$A$2:$A$8,"<>"&A2)=0) 但是它运行非常缓慢，因为等待了约4个小时后，仅完成了 ~20万数据的28%处理。

我已将文件以CSV格式上传到R，并希望在R上执行相同的操作以实现更快的处理。

- Roy1245

---

我不太明白你给 Result 列赋值的逻辑，请更好地解释一下。不过放弃 Excel 转向 R 是个好主意。 - Tim Biegeleisen

@TimBiegeleisen 简单来说，我想检查每个大于1的相同“Col2”值是否具有相同的“Col1”，并且对于每个等于1或小于1的“Col2”值，在整个Excel表中应该有唯一的“Col1”值。如果在“Col2”值为空的地方发现分配的“Col1”值是重复的，那么如果我们有特定“Col1”值的“Col2”值，则在“Result”（即第7行）中显示该值。 - Roy1245

对我来说，你想要将TRUE/FALSE与Col2中隐含的缺失值混合在一起非常奇怪...为什么不将该值回填到Col2中，而不是污染你的新列呢？ - MichaelChirico

添加一个单独的（第四）列作为“标志”，指示您已更改原始数据。 - MichaelChirico

让我们在聊天中继续这个讨论。点击此处进入聊天室。 - tjebo

Answer 1

像往常一样，我建议使用data.table。

library(data.table)
setDT(x) # convert your data.frame to data.table to unlock syntax
# convert to lowercase
x[ , Col2 := tolower(Col2)]
# count how many observations are associated with each Col2 value
x[ , col2_count := .N, by = Col2]

# first deal with rows where Col2 is non-missing
x[!is.na(Col2), Result := {
  # when there's more than one value in Col2,
  #   TRUE if and only if there's exactly one unique value in Col1
  if (.N > 1) uniqueN(Col1) == 1L
  # otherwise, TRUE if and only if Col1 is _not_ found among the
  #   Col1 values associated with the Col2 rows for which there are
  #   multiple observations of that Col2 (i.e, col2_count > 1)
  else !Col1 %in% x[col2_count > 1, unique(Col1)]
}, by = Col2]

# now, deal with the missing rows case, adding a flag to
#   record that we've done so
x[is.na(Col2), c('Col2', 'col2_flag') :=
    # use the rows of the subset data.table to look up
    #   the non-missing rows from X with the same Col1,
    #   and take the _first_ observed value of Col2
    x[!is.na(Col1)][copy(.SD), .(Col2, TRUE), on = 'Col1', mult = 'first']
  ]

- MichaelChirico

Answer 2

尝试使用dplyr：

require(dplyr)
x$Col2 <- toupper(x$Col2) #make all letters same case.

x_assigned <- x %>% group_by(Col2,Col1) %>% 

summarise(n = n()) %>% #counts the number of occurrences
  group_by(Col1) %>% arrange(desc(n)) %>%  # arranges so that the highest count per Col1 is first
  mutate(assigned = if (first(n) == 1) { #this conditional statement will assign the 'correct' Col2 value to your Col1 value
      Col2
    } else if (first(n) > 1) { 
      first(Col2)
    },
  test = assigned == Col2)

x_assigned

# A tibble: 6 x 5
# Groups:   Col1 [3]
  Col2  Col1      n assigned test 
  <chr> <chr> <int> <chr>    <lgl>
1 A2B1  I-123     2 A2B1     T    
2 C2C2  I-456     2 C2C2     T    
3 D1E2  I-456     1 C2C2     F    
4 LJ87  I-789     1 LJ87     T    
5 LJ98  I-789     1 LJ98     T    
6 <NA>  I-456     1 C2C2     NA  

为了获得所需的结果，您可以对 x 和 x_assigned 进行简单的左连接。

left_join(x, x_assigned, by = c('Col1', 'Col2'))

你可以通过这种方式看到哪些值是缺失的，也可以轻松地分配“正确”的 Col2 值。如果我误解了你的问题，我很抱歉，我还不确定你如何为 Col1 值分配“正确”的 Col2 值。

- tjebo

Answer 3

我会先聚合数据并添加两列附加信息：

library(dplyr)

# Create dummy dataframe
Col1 <- c("IJ-123", "IJ-123", "IJ-456", "IJ-456", "IJ-456", "IJ-789", "IJ-456", "IJ-789") 
Col2 <- c("A2B1", "A2B1", "C2C2", "c2c2", "D1e2", "LJ87", "C2C2", "LJ98")
df <- data.frame(Col1, Col2)

# Aggregate data - Col2 Vs Col1 and Col1 Vs Col2
Col2vsCol1 <- aggregate(Col1 ~ Col2, data = df, paste, collapse = ",")
colnames(Col2vsCol1)[2] <- "Col2vsCol1"
Col1vsCol2 <- aggregate(Col2 ~ Col1, data = df, paste, collapse = ",")
colnames(Col1vsCol2)[2] <- "Col1vsCol2"
# Outer join these as two extra columns to original df:
df <- merge(x = df, y = Col2vsCol1, by = "Col2", all = TRUE)
df <- merge(x = df, y = Col1vsCol2, by = "Col1", all = TRUE)

然后，您可以使用这些列来执行逻辑检查：

+----------------------------------------------------------+
|   Col1   Col2    Col2vsCol1          Col1vsCol2          |
+----------------------------------------------------------+
| 1 IJ-123 A2B1    IJ-123,IJ-123       A2B1,A2B1           |
| 2 IJ-123 A2B1    IJ-123,IJ-123       A2B1,A2B1           |
| 3 IJ-456 c2c2    IJ-456              C2C2,c2c2,D1e2,C2C2 |
| 4 IJ-456 C2C2    IJ-456,IJ-456       C2C2,c2c2,D1e2,C2C2 |
| 5 IJ-456 C2C2    IJ-456,IJ-456       C2C2,c2c2,D1e2,C2C2 |
| 6 IJ-456 D1e2    IJ-456              C2C2,c2c2,D1e2,C2C2 |
| 7 IJ-789 LJ87    IJ-789              LJ87,LJ98           |
| 8 IJ-789 LJ98    IJ-789              LJ87,LJ9898         |
+----------------------------------------------------------+

- Graeme

---

我已经考虑过使用聚合方法了，但是由于我的Col1值与Col2值之间有成千上万的关联，我发现这种方法对于分析来说并不是很有用。 - Roy1245

顺便说一句，谢谢。 - Roy1245

@Graeme 为什么要使用 require(dplyr) - 这段代码看起来像是基本的 R 语言 :) - tjebo