如何使用R语言重塑DataFrame：将宽格式转换为长格式

现有如下结构的DataFrame：

需要将其转换为以下长格式结构：

核心需求：拆分原表中包含Location和Variable的列名，将Location提取为独立列，各Variable作为单独字段，同时按日期和Location展开行数据。

方法1：使用tidyverse包（推荐） #

tidyverse的pivot_longer和pivot_wider组合可快速完成格式转换：

步骤1：构造示例数据 #

library(tidyverse)

# 模拟原数据结构
df <- tibble(
  date = c("Jan 20", "Feb 20", "Dec 20"),
  `Location A Variable 1` = c(2, 2, 2),
  `Location A Variable 2` = c(2, 2, 2),
  `Location B Variable 1` = c(2, 2, 2),
  `Location B Variable 2` = c(2, 2, 2)
)

步骤2：执行格式转换 #

df_long <- df %>%
  # 将非date列转为长格式，暂存列名和对应值
  pivot_longer(-date, names_to = "col_name", values_to = "value") %>%
  # 拆分列名为Location和Variable两部分
  separate(col_name, into = c("Location", "Variable"), sep = " Variable ", extra = "merge") %>%
  # 移除Location字段中的"Location "前缀
  mutate(Location = str_remove(Location, "Location ")) %>%
  # 将Variable转为列，填充对应值
  pivot_wider(names_from = "Variable", values_from = "value")

方法2：使用data.table包 #

处理大数据集时，data.table的效率更有优势：

步骤1：构造示例数据 #

library(data.table)

# 模拟原数据结构
dt <- data.table(
  date = c("Jan 20", "Feb 20", "Dec 20"),
  `Location A Variable 1` = c(2, 2, 2),
  `Location A Variable 2` = c(2, 2, 2),
  `Location B Variable 1` = c(2, 2, 2),
  `Location B Variable 2` = c(2, 2, 2)
)

步骤2：执行格式转换 #

# 先将宽表转为长表
dt_melted <- melt(dt, id.vars = "date", variable.name = "col_name", value.name = "value")
# 拆分列名为Location和Variable
dt_melted[, c("Location", "Variable") := tstrsplit(col_name, " Variable ", fixed = TRUE)]
# 移除Location字段前缀
dt_melted[, Location := gsub("Location ", "", Location)]
# 将长表转回目标格式
dt_long <- dcast(dt_melted, date + Location ~ Variable, value.var = "value")

两种方法最终都会输出符合需求的长格式数据。

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