基于Variant_Type匹配双文件指定列并追加AC、AF字段的实现方法

以下提供两种可行实现，小文件推荐用R实现更灵活，GB级以上大文件推荐用awk实现效率更高。

方案1：R语言实现 #

核心逻辑是将文件1按变异类型拆分，分别匹配后合并，代码如下：

# 读取两个文件，默认制表符分隔，可自行修改sep参数
file1 <- read.table("file1.txt", header = T, sep = "\t", stringsAsFactors = F, check.names = F)
file2 <- read.table("file2.txt", header = T, sep = "\t", stringsAsFactors = F, check.names = F)

# 拆分文件1为DEL组和INS/SNP组
file1_del <- file1[file1$Variant_Type == "DEL", ]
file1_other <- file1[file1$Variant_Type %in% c("INS", "SNP"), ]

# 分别按对应键合并，all.x=T保留所有file1的行，匹配不到则AF/AC为NA
merge_del <- merge(file1_del, file2[, c("Chromosome", "vcf_pos", "Reference_Allele", "AF", "AC")],
                   by = c("Chromosome", "vcf_pos", "Reference_Allele"),
                   all.x = T)
merge_other <- merge(file1_other, file2[, c("Chromosome", "vcf_pos", "Tumor_Seq_Allele2", "AF", "AC")],
                     by = c("Chromosome", "vcf_pos", "Tumor_Seq_Allele2"),
                     all.x = T)

# 合并两个结果，恢复文件1原有列顺序
result <- rbind(merge_del, merge_other)
result <- result[, c(colnames(file1), "AF", "AC")]

# 输出结果
write.table(result, "merged_result.txt", sep = "\t", quote = F, row.names = F, na = "")

方案2：awk实现（大文件优先） #

核心逻辑是先把文件2的两种匹配规则存入内存哈希，再遍历文件1匹配输出，适合处理超大文件，无需加载全部文件1到内存：

BEGIN {
    FS = "\t" # 若文件是空格分隔可改为FS="[[:space:]]+"
    OFS = "\t"
}
# 先处理第二个文件，构建两种匹配哈希
NR == FNR {
    # DEL类型匹配键：chr_vcf_pos_ref
    del_key = $1"_"$2"_"$5
    del_map[del_key] = $3"\t"$4
    # INS/SNP类型匹配键：chr_vcf_pos_alt
    ins_snp_key = $1"_"$2"_"$6
    ins_snp_map[ins_snp_key] = $3"\t"$4
    next
}
# 处理表头
FNR == 1 {
    print $0, "AF", "AC"
    next
}
# 处理文件1的每一行，按变异类型匹配
{
    if ($7 == "DEL") {
        key = $2"_"$8"_"$5
        val = del_map[key]
    } else {
        key = $2"_"$8"_"$6
        val = ins_snp_map[key]
    }
    print $0, val
}

使用方法：在终端执行命令 awk -f merge.awk file2.txt file1.txt > merged_result.txt

内容的提问来源于stack exchange，提问作者Anna