如何使用R统计数据框不同列间非NA与NA值的重叠数量

统计数据集各分组（对应数据集列）的非NA值总数量，以及所有任意列组合下的行同时非NA的重叠数量，仅判断同一行对应列是否全部不为空，无需匹配单元格内容，结果可直接用于韦恩图绘制。

数据预处理 #

首先对输入数据做结构化处理，生成非NA判断的逻辑矩阵：

# 读入示例数据，替换为你自己的数据集即可
df <- structure(list(V1 = c("Group1", "XP_032738419.1", "XP_032715310.1",
"XP_032703108.1", "XP_032700385.1", NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA), V2 = c("Group2", "XP_011286297.1, XP_011286306.1, XP_019670819.1, XP_019670818.1, XP_023097752.1, XP_011286308.1, XP_011286311.1, XP_023097760.1, XP_011286303.1, XP_023097755.1, XP_023097756.1, XP_023097757.1, XP_023097758.1, XP_023097754.1, XP_023097753.1, XP_011286310.1, XP_023097759.1, XP_019670826.1, XP_011286304.1, XP_019670828.1",
NA, "XP_019685915.1", "XP_023112367.1", NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA), V3 = c("Group3", "XP_038528678.1",
"XP_038300380.1", "XP_038538922.1", "XP_038295408.", NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), V4 = c("Group4",
"XP_012903997.1", "XP_004748105.1, XP_012909429.1", "XP_012905661.1",
"XP_012901919.1", NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA), V5 = c("Group5", "NP_001310871.1", "NP_001341201.1",
"NP_001374917.1", "NP_001123304.1", NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA), V6 = c("Group6", "XP_044098939.1",
"XP_044080143.1", "XP_044112499.1", "XP_044084408.1", NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA)), row.names = c("1",
"2", "3", "4", "5", "NA", "NA.1", "NA.2", "NA.3", "NA.4", "NA.5",
"NA.6", "NA.7", "NA.8", "NA.9", "NA.10", "NA.11", "NA.12", "NA.13",
"NA.14"), class = "data.frame")

# 仅当你的数据集第一行是分组名时执行以下两行，已自带正确列名可跳过
colnames(df) <- as.character(df[1,])
df <- df[-1,]

# 生成非NA逻辑矩阵：非NA值对应TRUE，NA对应FALSE
logic_mat <- !is.na(df)

统计所有组合的重叠数 #

n_col <- ncol(logic_mat)
result <- list()
# 遍历所有长度的列组合
for (k in 1:n_col) {
  combos <- combn(colnames(logic_mat), k, simplify = FALSE)
  for (com in combos) {
    # 统计该组合下所有列同时为非NA的行数
    overlap_count <- sum(apply(logic_mat[, com], 1, all))
    result[[paste(com, collapse = "+")]] <- overlap_count
  }
}
# 转换为数据框方便查看和后续调用
result_df <- data.frame(
  group_comb = names(result),
  overlap_num = unlist(result),
  row.names = NULL
)

数据预处理 #

使用pandas处理数据集，生成逻辑矩阵：

import pandas as pd
from itertools import combinations

# 读入示例数据，可替换为pd.read_csv("你的文件路径")
data = {
    'V1': ["Group1", "XP_032738419.1", "XP_032715310.1", "XP_032703108.1", "XP_032700385.1"] + [None]*15,
    'V2': ["Group2", "XP_011286297.1, XP_011286306.1, XP_019670819.1, XP_019670818.1, XP_023097752.1, XP_011286308.1, XP_011286311.1, XP_023097760.1, XP_011286303.1, XP_023097755.1, XP_023097756.1, XP_023097757.1, XP_023097758.1, XP_023097754.1, XP_023097753.1, XP_011286310.1, XP_023097759.1, XP_019670826.1, XP_011286304.1, XP_019670828.1", None, "XP_019685915.1", "XP_023112367.1"] + [None]*15,
    'V3': ["Group3", "XP_038528678.1", "XP_038300380.1", "XP_038538922.1", "XP_038295408."] + [None]*15,
    'V4': ["Group4", "XP_012903997.1", "XP_004748105.1, XP_012909429.1", "XP_012905661.1", "XP_012901919.1"] + [None]*15,
    'V5': ["Group5", "NP_001310871.1", "NP_001341201.1", "NP_001374917.1", "NP_001123304.1"] + [None]*15,
    'V6': ["Group6", "XP_044098939.1", "XP_044080143.1", "XP_044112499.9", "XP_044084408.1"] + [None]*15
}
df = pd.DataFrame(data)

# 仅当你的数据集第一行是分组名时执行以下两行，已自带正确列名可跳过
df.columns = df.iloc[0]
df = df[1:].reset_index(drop=True)

# 生成非NA逻辑矩阵
logic_df = df.notna()

统计所有组合的重叠数 #

n_col = logic_df.shape[1]
result = []
# 遍历所有长度的列组合
for k in range(1, n_col+1):
    for comb in combinations(logic_df.columns, k):
        # 统计该组合下所有列同时为非NA的行数
        overlap_count = logic_df[list(comb)].all(axis=1).sum()
        result.append({
            "group_comb": "+".join(comb),
            "overlap_num": overlap_count
        })
# 转换为DataFrame方便后续使用
result_df = pd.DataFrame(result)

• 若分组数量超过6个，韦恩图可视化效果会很差，建议先筛选需要展示的分组后再统计结果
• 统计得到的result_df中，组合名长度为1的行对应单个分组的非NA总数量，多组名组合对应重叠数量，可直接提取数值用于韦恩图绘制

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