R语言tidyr::pivot_wider无法合并行问题求助

初始数据集 #

a <- data.frame(out=c('asd', NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,
                      "adhd",NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA),
                exposure=c('x susceptibility', NA,NA,NA,
                           'hospitalised x', NA,NA,NA,
                           'severe x', NA,NA,NA,
                           'x susceptibility', NA,NA,NA,
                           'hospitalised x', NA,NA,NA,
                           'severe x', NA,NA,NA),
                method=rep(c('a','b','c','d'),6),
                or=c(rnorm(3,0,0.004),NA,rnorm(11,0,0.004),NA,rnorm(8,0,0.004)),
                loci_or=c(rnorm(3,0,0.004),NA,rnorm(11,0,0.004),NA,rnorm(8,0,0.004)),
                upci_or=c(rnorm(3,0,0.004),NA,rnorm(11,0,0.004),NA,rnorm(8,0,0.004)),
                p_val=c(rnorm(3,0,0.004),NA,rnorm(11,0,0.004),NA,rnorm(8,0,0.004)),
                egger_int=c(NA,0.00004,NA,NA,NA,0.00009,NA,NA,NA,0.00003,NA,NA,
                            NA,0.00001,NA,NA,NA,0.00002,NA,NA,NA,0.00007,NA,NA),
                egger_int_p=c(NA,0.00004,NA,NA,NA,0.00009,NA,NA,NA,0.00003,NA,NA,
                              NA,0.00001,NA,NA,NA,0.00002,NA,NA,NA,0.00007,NA,NA))

目标格式 #

希望将数据转换为每个out-exposure对一行的宽格式，示例如下：

b <- data.frame(out=c('asd', NA,NA,
                  "adhd",NA,NA),
            exposure=c('x susceptibility','hospitalised x','severe x',
                       'x susceptibility','hospitalised x','severe x'),
            a_or=rnorm(6,0,0.004),
            a_loci_or=rnorm(6,0,0.004),
            a_upci_or=rnorm(6,0,0.004),
            a_p_val=rnorm(6,0,0.004),
            b_or=rnorm(6,0,0.004),
            b_loci_or=rnorm(6,0,0.004),
            b_upci_or=rnorm(6,0,0.004),
            b_p_val=rnorm(6,0,0.004),
            c_or=rnorm(6,0,0.004),
            c_loci_or=rnorm(6,0,0.004),
            c_upci_or=rnorm(6,0,0.004),
            c_p_val=rnorm(6,0,0.004),
            egger_int=c(0.00004,0.00009,0.00003,0.00001,0.00002,0.00007),
            egger_int_p=c(0.00004,0.00009,0.00003,0.00001,0.00002,0.00007))

尝试的代码及问题 #

尝试了以下代码，但转换后每个out-exposure对出现两行，egger_int、egger_int_p与其他{method}_{.value}列值分属不同行，原本需要6行却得到12行：

tidy_dev <- a %>%
            # fills missing values in these columns using next/previous entry.
            # Values are not repeated,
            tidyr::fill(outcome,exposure) %>%
            # changing from long format to wide format
            tidyr::pivot_wider(names_from = method,
                               values_from = or:p_value,
                               # naming scheme: value1_name1, value2_name1 etc
                               names_vary = 'slowest',
                               # how you want to format column names
                               names_glue = '{method}_{.value}') %>%
            # moving Egger intercept and its p-value to the last column
            dplyr::relocate(c(egger_int,
                              egger_int_p),
                            .after = last_col())

问题根源：

1. 填充列名错误：原数据中列名为out而非outcome，导致分组标识填充不完整
2. egger_int和egger_int_p的NA值未统一：同一out-exposure组内存在NA与非NA值，导致宽表转换后拆分出多行

修正后的代码：

library(dplyr)
library(tidyr)

tidy_dev <- a %>%
  # 正确填充out和exposure的NA值，向下补全分组标识
  fill(out, exposure, .direction = "down") %>%
  # 按out-exposure分组，将组内egger相关列的NA值用非NA值补全
  group_by(out, exposure) %>%
  fill(egger_int, egger_int_p, .direction = "downup") %>%
  ungroup() %>%
  # 转换为宽格式，生成method_指标名的列
  pivot_wider(
    names_from = method,
    values_from = c(or, loci_or, upci_or, p_val),
    names_glue = "{method}_{.value}"
  ) %>%
  # 去重，保留每个out-exposure组的唯一行
  distinct(out, exposure, .keep_all = TRUE) %>%
  # 将egger相关列移至末尾
  relocate(c(egger_int, egger_int_p), .after = last_col())

代码说明：

• 先修正填充列名，确保每行都有明确的out-exposure分组标识
• 分组填充egger_int和egger_int_p，保证同一组内所有行的这两个值一致
• 宽表转换后，同一组的行内容完全重复，通过distinct去重得到6行的目标结构
• 最后调整列位置，符合目标格式的布局

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