在R语言长格式转换数据框时合并两列的实现方法

我已经能用tidyr::pivot_longer(DATA, -InstNm, names_sep = "_", names_to = c("Indicator", ".value"))得到接近目标的结果，但想知道怎么精确实现把CPct和EPct合并成CPct_EPct列，达到如下的Desired_output格式：

原始数据：

DATA <- read.table(header=T, text=
"InstNm    OTE_CPct OTE_L OTE_P    OTE_R      RAE_EPct RAE_NPct RAE_L RAE_B RAE_P  RAE_R
 Baker          60      1     1  Limited            56     76     1     0     1   Limited")

目标输出：

Desired_output <- read.table(header=T, text=
"InstNm    Indicator Rating   NPct  CPct_EPct  L   P  B
 Baker     OTE      Limited    NA        60    1   1  NA
 Baker     RAE      Limited    76        56    1   1  0")

解决方案 #

结合tidyr和dplyr可以一步到位实现需求，核心是先拆分数据，再合并指定列，最后调整格式：

library(tidyr)
library(dplyr)

# 加载原始数据
DATA <- read.table(header=T, text=
"InstNm    OTE_CPct OTE_L OTE_P    OTE_R      RAE_EPct RAE_NPct RAE_L RAE_B RAE_P  RAE_R
 Baker          60      1     1  Limited            56     76     1     0     1   Limited")

# 生成目标结果
result <- DATA %>%
  # 按下划线拆分列名，生成Indicator和指标值列
  pivot_longer(-InstNm, names_sep = "_", names_to = c("Indicator", ".value")) %>%
  # 合并CPct和EPct列，取非空值作为CPct_EPct
  mutate(CPct_EPct = coalesce(CPct, EPct)) %>%
  # 将R列重命名为Rating
  rename(Rating = R) %>%
  # 调整列顺序，移除多余的CPct、EPct列
  select(InstNm, Indicator, Rating, NPct, CPct_EPct, L, P, B) %>%
  # 统一空值为NA
  mutate(across(c(NPct, B), ~replace_na(., NA)))

# 查看结果
print(result)

步骤说明 #

1. 拆分数据：用pivot_longer按_拆分列名，将OTE_/RAE_前缀提取为Indicator列，后缀作为指标列名，此时会生成CPct（仅OTE行有值）和EPct（仅RAE行有值）两列；
2. 合并列：用coalesce函数自动取两列中的非空值，合并为CPct_EPct列；
3. 格式调整：重命名R列为Rating，调整列顺序匹配目标输出，最后把空值统一替换为NA，确保格式完全一致。

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