R语言：如何按不同列名重塑DataFrame至指定格式？

原始数据 #

首先定义并查看原始DataFrame：

a <- data.frame(list(X1=c("stn", "s1", "stn", "s2"), 
  X2=c("var1", "1", "var4", "2"), 
  X3=c("var2", "2", "var3", "3"), 
  X4=c("NA", "NA", "var2", "2")))

输出结果：

X1   X2   X3   X4
1 stn var1 var2   NA
2  s1    1    2   NA
3 stn var4 var3 var2
4  s2    2    3    2

目标格式 #

期望转换后的DataFrame定义及输出：

b <- data.frame(list(stn=c("s1", "s2"), 
  var1=c(1, NA), 
  var2=c(2, 2), 
  var3=c(NA, 3), 
  var4=c(NA, 2)))

输出结果：

stn var1 var2 var3 var4
1  s1    1    2   NA   NA
2  s2   NA    2    3    2

解决方案 #

方法一：使用tidyverse工具包

通过长格式转换、分组处理实现，逻辑清晰易读：

library(tidyverse)

# 为每一组（站点+变量行）添加分组标识
a <- a %>% mutate(group = rep(1:(nrow(.)/2), each = 2))

# 整理变量名与对应值的映射关系
temp_df <- a %>%
  pivot_longer(cols = X2:X4, names_to = "col", values_to = "val") %>%
  group_by(group) %>%
  # 提取变量名并向下填充到对应的值行
  mutate(var = ifelse(X1 == "stn", val, NA),
         value = ifelse(X1 != "stn", val, NA)) %>%
  fill(var, .direction = "down") %>%
  # 过滤无效行，处理字符串NA为真实NA并转换数据类型
  filter(!is.na(value)) %>%
  select(group, var, value) %>%
  mutate(value = ifelse(value == "NA", NA, value)) %>%
  type.convert(as.is = TRUE)

# 转换为宽格式并调整列顺序
final_df <- temp_df %>%
  pivot_wider(names_from = var, values_from = value) %>%
  mutate(stn = paste0("s", group)) %>%
  select(stn, everything()) %>%
  select(-group)

print(final_df)

方法二：基础R实现

不依赖第三方包，用基础矩阵/列表操作完成：

# 分离变量名行和对应的值行
var_rows <- a[a$X1 == "stn", -1]
val_rows <- a[a$X1 != "stn", -1]

# 提取每个站点的有效变量和对应值（排除"NA"）
site_vars <- apply(var_rows, 1, function(x) x[x != "NA"])
site_vals <- apply(val_rows, 1, function(x) x[x != "NA"])

# 获取所有出现过的变量
all_vars <- unique(unlist(site_vars))

# 初始化结果DataFrame
final_df <- data.frame(stn = a$X1[a$X1 != "stn"])

# 为每个变量填充对应站点的值
for (var in all_vars) {
  final_df[[var]] <- sapply(1:length(site_vars), function(i) {
    match_idx <- which(site_vars[[i]] == var)
    if (length(match_idx) > 0) {
      val <- site_vals[[i]][match_idx]
      if (val == "NA") NA else as.numeric(val)
    } else {
      NA
    }
  })
}

# 调整列顺序与目标一致
final_df <- final_df[, c("stn", "var1", "var2", "var3", "var4")]

print(final_df)

两种方法最终都会输出符合要求的DataFrame。

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