在我的设置中，我有三个文件：

1. index.csv
2. original.csv
3. processed.csv

索引文件看起来是这样的：

Source column, Source code, Source value
Handedness, Left,1
Handedness, Right,2
Handedness, Ambidextrous,3
Drinking habits, Daily,1
Drinking habits, Weekly,2
Drinking habits, Monthly,3
Drinking habits, Yearly,4
Drinking habits, Never,0
Smoking, Daily,1
Smoking, Weekly,2
Smoking, Monthly,3
Smoking, Yearly,4
Smoking, Never,0
Exercise, Daily,1
Exercise, Weekly,2
Exercise, Monthly,3
Exercise, Yearly,5
Exercise, Never,6

original.csv数据集看起来是这样的：

Vol_ID, Handedness, Drinking habits, Smoking, Exercise,
8750, Right, Daily, Daily, Weekly,
9526, Left, Weekly, Daily, Never,
4428, Right, Never, Never, NA,
6134, Left, Monthly, Never, Weekly,
7891, Right, Weekly, Weekly, Daily,
5280, Right, Never, Never, Weekly,
9042, Left, Daily, Daily, Never,
3176, Right, Weekly, Never, Monthly,
6603, Left, Weekly, Weekly, Weekly,
4819, Right, Never, Daily, NA,

processed.csv数据集看起来是这样的：

Per_ID, Handedness, Drinking habits, Smoking, Exercise,
8750, 2, 1, 1, 2,
9526, 1, 2, 1, 6,
4428, 2, 0, 0, NA,
6134, 1, 3, 0, 2,
7891, 2, 2, 3, 1,
5280, 2, 0, 0, 2,
9042, 1, 1, 1, 6,
3176, 2, 2, 0, 3,
6603, 1, 2, 2, 2,
4819, 2, 0, 1, NA,

我想取original.csv，与processed.csv进行比较，使用index.csv作为参考来生成“QC列”并输出应看起来像这样：

Per_ID, Handedness, Handedness_QC, Drinking habits, Drinking habits_QC, Smoking, Smoking_QC, Exercise, Exercise_QC,  
8750, Right, Match, Daily, Match, Daily, Match, Weekly, Match,  
9526, Left, Mismatch!, Weekly, Match, Daily, Mismatch!, Never, Match,  
4428, Right, Mismatch!, Never, Match, Never, Match,  NA, No data,  
<and so forth>

基本上，如果 original 的文本与 processed 匹配，那么在相邻的QC列中写入“匹配”。如果不匹配，则写入“不匹配！”。如果没有数据（NA），则显示“无数据”。

我可以对单个列完成这一步。但在从索引中获取必要的变量并创建合适的循环来运行检查时，我确实很吃力。

我现在在做的事情

在当前的设置中，我手动创建了一个名为 handedness.csv 的文件，其内容为：

handedness, value,  
Left, 1,  
Right, 2,  
Handedness, Ambidextrous, 3,

然后执行如下的脚本：

library(tidyverse)  

# Load data  
original <- read_csv("original.csv")  
processed <- read_csv("processed.csv")  
code_map <- read_csv("handedness.csv")  

# QC for handedness  
qc_data <- processed %>%  
  left_join(original %>%   
              select(Vol_ID, handedness),   
            by =c("Vol_ID"="Per_ID"))%>% #The datasets annoyingly have different ID's. I'm not allowed to change it.  
  left_join(code_map, by = c("handedness.x" = "handedness"))%>% # Join mapping (to get the expected score)  
  mutate(  
    handedness_QC = if_else(handedness.y == value, "Correct", "Mismatch!")  
  )%>%  # Compare processed value vs expected value  
  rename(  
    handedness = handedness.x) %>%   # Clean up column names  
  relocate(handedness_QC, .after = handedness) %>%   # Place QC column next to original column  
  select(-handedness.y, -value)   #Remove unwanted columns

我需要为每一列重复这个过程并做相应修改。

解决方案

我会在一个名为index的对象中的某个 subset 上使用 match 将值转换为代码。

for (nm in names(processed)[-1]) {
  s <- subset(index, Source.column == nm)
  processed[[nm]] <- 
    s$Source.code[
      match(
        processed[[nm]],
        s$Source.value[s$Source.column == nm],
      )
    ]
}

然后只替换不一致的：

processed[processed != original] <- 'Mismatch!'

得到

> processed
  Vol_ID Handedness Drinking habits
1   8750      Right           Daily
2   9526  Mismatch!          Weekly

在读取数据时，您可能需要使用 check.names-FALSE。

original <- read.csv('original.csv', check.names=FALSE)[1:2, 1:3]
processed <- read.csv('processed.csv', check.names=FALSE)[, 1:3]
index <- read.csv('index.csv')

注： 我在这里对你的数据进行了子集化（[1:2, 1:3]、[, 1:3]），在完整数据时这可能不是必需的。

编辑

要得到相邻的QC列，只需在副本 merge 上执行“mismatch”，然后对列进行排序。

processed2 <- processed
processed2[processed2 != original] <- 'Mismatch!'
names(processed2)[-1] <- paste0(names(processed2)[-1], '_QC')

res <- merge(processed, processed2)
n <- ncol(processed)
res <- res[c(
  1,
  sapply(seq_len(ncol(processed) - 1) + 1, \(x) c(x, x + n - 1))
)]

得到

> res
  Vol_ID Handedness Handedness_QC Drinking habits Drinking habits_QC
1   8750      Right         Right           Daily              Daily
2   9526      Right     Mismatch!          Weekly             Weekly