将含嵌套列名的Eikon宽格式数据重构为长格式

问题背景 #

从Eikon导出的数据结构特殊：

• 列名为资产ID（如GB0002404191）
• 首行是嵌套的指标类型（DATE/HIGH/LOW）
• 后续行是对应指标的数值/日期值

需要将数据转换为每个ID的DATE、HIGH、LOW作为独立列，所有ID数据垂直堆叠的格式，示例结构如下：

解决方案（R语言） #

以下提供两种主流包的实现方式，按需选择：

方式1：使用tidyverse工具链 #

library(tidyverse)

# 假设原始数据框名为df
# 1. 提取首行作为指标名，重命名列（ID_指标名）
metrics <- as.character(df[1, ])
colnames(df) <- paste0(colnames(df), "_", metrics)

# 2. 移除首行，保留有效数据
df_clean <- df[-1, ]

# 3. 先转长格式拆分ID和指标，再转宽格式生成目标列
result <- df_clean %>%
  pivot_longer(
    cols = everything(),
    names_to = c("ID", "metric"),
    names_sep = "_",
    values_to = "value"
  ) %>%
  pivot_wider(
    names_from = metric,
    values_from = value
  ) %>%
  # 按需转换数值列类型
  mutate(
    HIGH = as.numeric(HIGH),
    LOW = as.numeric(LOW)
  )

方式2：使用data.table包（适合大数据量） #

library(data.table)

setDT(df)
# 1. 提取首行作为指标名，重命名列
metrics <- as.character(df[1, ])
setnames(df, paste0(names(df), "_", metrics))

# 2. 移除首行
df_clean <- df[-1, ]

# 3. 拆分列名、重塑数据
result <- melt(df_clean, measure.vars = names(df_clean),
               variable.name = "ID_metric", value.name = "value") %>%
  # 拆分ID和指标名
  .[, c("ID", "metric") := tstrsplit(ID_metric, "_")] %>%
  # 转宽格式生成目标列
  dcast(ID + DATE ~ metric, value.var = "value") %>%
  # 转换数值类型
  .[, `:=`(HIGH = as.numeric(HIGH), LOW = as.numeric(LOW))]

验证结果 #

处理完成后，result即为符合要求的数据结构，每个资产ID对应其所有日期的HIGH/LOW数值，DATE、HIGH、LOW作为独立列展示。

内容的提问来源于stack exchange，提问作者Jef van Cappellen