如何在R中将长数据的统计量合并到宽数据框

需求 #

对数据框1中每个rs_id的不同基因型(gt)，计算Age、ACE2Conc.ngml、Height、BMI这四个表型的中位数(IQR)，并将结果按rs_id和表型名称合并到数据框2中。

示例数据 #

数据框1（包含样本、表型、基因型信息） #

structure(list(BiobankID = c(131, 133, 134, 136, 140, 141, 143, 144, 145, 131, 133, 134, 136, 140, 141, 143, 144, 145, 131, 133, 134, 136, 140, 141, 143, 144, 145), Age = c(29, 58, 48, 44, 40, 32, 43, 61, 64, 29, 58, 48, 44, 40, 32, 43, 61, 64, 29, 58, 48, 44, 40, 32, 43, 61, 64), ACE2Conc.ngml = c(113.49, 52.91, 89.25, 74.82, 28.5, 97.33, 97.53, 63.72, 56.86, 113.49, 52.91, 89.25, 74.82, 28.5, 97.33, 97.53, 63.72, 56.86, 113.49, 52.91, 89.25, 74.82, 28.5, 97.33, 97.53, 63.72, 56.86), Height = c(170, 160.2, 161.3, 168, 148, 175.2, 167, 174, 163.1, 170, 160.2, 161.3, 168, 148, 175.2, 167, 174, 163.1, 170, 160.2, 161.3, 168, 148, 175.2, 167, 174, 163.1), BMI = c(31.8, 30.8, 33.6, 31.8, 20.1, 26.8, 26, 27.4, 31.3, 31.8, 30.8, 33.6, 31.8, 20.1, 26.8, 26, 27.4, 31.3, 31.8, 30.8, 33.6, 31.8, 20.1, 26.8, 26, 27.4, 31.3), rs_id = structure(c(9L, 9L, 9L, 9L, 9L, 9L, 9L, 9L, 9L, 11L, 11L, 11L, 11L, 11L, 11L, 11L, 11L, 11L, 35L, 35L, 35L, 35L, 35L, 35L, 35L, 35L, 35L), .Label = c("rs2278426", "rs1122326", "rs10033119", "rs4932373", "rs2071410", "rs41303171", "rs4646116", "rs328", "rs12130333", "rs1748197", "rs1748195", "rs79566395", "rs55843109", "rs4703642", "rs199347", "rs148110342", "rs16909225", "rs6814581", "rs3825041", "rs67904839", "rs11508026", "rs368669", "rs673548", "rs2266788", "rs9457", "rs7770437", "rs1046322", "rs1997623", "rs964184", "rs6589566", "rs12440118", "rs2160669", "rs4375003", "rs114060881", "rs4675468", "rs5754701", "rs12642481"), class = "factor"), gt = c("1", "2", "2", "1", "1", "2", "1", "1", "1", "1", "2", "3", "1", "2", "2", "1", "2", "1", "3", "3", "3", "3", "3", "3", "3", "3", "3")), row.names = c(NA, -27L), class = c("tbl_df", "tbl", "data.frame"))

数据框2（包含SNP关联分析结果） #

structure(list(Phenotype = c("ACE2Conc.ngml", "ACE2Conc.ngml", "ACE2Conc.ngml", "Age", "Age", "Age", "BMI", "BMI", "BMI", "Height", "Height", "Height"), CHR = c(1L, 1L, 2L, 1L, 1L, 2L, 1L, 1L, 2L, 1L, 1L, 2L), SNP = c("rs1748195", "rs12130333", "rs4675468", "rs1748195", "rs12130333", "rs4675468", "rs1748195", "rs12130333", "rs4675468", "rs1748195", "rs12130333", "rs4675468"), BP = c(63049593L, 63191777L, 205400216L, 63049593L, 63191777L, 205400216L, 63049593L, 63191777L, 205400216L, 63049593L, 63191777L, 205400216L), A1 = c("G", "T", "T", "G", "T", "T", "G", "T", "T", "G", "T", "T"), TEST = c("ADD", "ADD", "ADD", "ADD", "ADD", "ADD", "ADD", "ADD", "ADD", "ADD", "ADD", "ADD"), NMISS = c(1209L, 1242L, 1247L, 2018L, 2060L, 2066L, 2016L, 2058L, 2064L, 2016L, 2058L, 2064L), BETA = c(-1.13, -2.189, 0.5272, 0.08263, 0.7493, -0.419, -0.3865, 0.1747, -0.5264, 0.2913, 1.334, -0.613), STAT = c(-1.175, -1.515, 0.2892, 0.2338, 1.371, -0.6622, -2.081, 0.6047, -1.575, 0.9933, 2.962, -1.169), P = c(0.2402, 0.1301, 0.7724, 0.8152, 0.1706, 0.5079, 0.0376, 0.5454, 0.1154, 0.3207, 0.003091, 0.2426)), class = "data.frame", row.names = c(NA, -12L))

实现代码 #

使用tidyverse工具链完成数据整理、统计与合并：

library(tidyverse)

# 1. 将数据框1转为长表，方便按表型分组
df1_long <- df1 %>%
  pivot_longer(cols = c(Age, ACE2Conc.ngml, Height, BMI),
               names_to = "Phenotype",
               values_to = "Value")

# 2. 计算每个rs_id、基因型、表型的中位数(IQR)，并转为宽表格式
summary_stats <- df1_long %>%
  group_by(rs_id, gt, Phenotype) %>%
  summarise(
    Median_IQR = paste0(round(median(Value), 2), " (", 
                       round(quantile(Value, 0.25), 2), "-", 
                       round(quantile(Value, 0.75), 2), ")"),
    .groups = "drop"
  ) %>%
  pivot_wider(names_from = gt, values_from = Median_IQR, names_prefix = "gt_")

# 3. 匹配合并到数据框2
result_df <- df2 %>%
  left_join(summary_stats, by = c("SNP" = "rs_id", "Phenotype"))

# 查看最终结果
print(result_df)

代码说明 #

• 第一步将宽表转为长表，让每个表型的数值单独成行，简化后续分组统计逻辑。
• 第二步按rs_id、基因型、表型分组计算统计量，格式化为中位数(IQR)的字符串后，再将不同基因型的结果转为列，匹配目标输出格式。
• 第三步通过left_join将统计结果关联到数据框2，关联键为SNP（对应数据框1的rs_id）和Phenotype。

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