如何在R中按组宽转长后合并生成面板数据集？

数据背景 #

导入的数据集以每两行为一组组织：

• 每组第1行：列...2到...13存储日期（Excel序列号格式），第1列...1为NA
• 每组第2行：列...2到...13存储对应日期的公司收益率，第1列...1为公司标识（如F00001092B - Return）

原始数据结构如下：

structure(list(...1 = c(NA, "F00001092B - Return", NA, "F00001092A - Return", 
NA, NA, NA, "F000015112 - Return", NA, "0P0001KFG1 - Return", 
NA, "0P0001KFG2 - Return", NA, "0P0001KG31 - Return"), ...2 = c("43220", 
"7.1993099999999997", "43220", "9.9567099999999993", "-N/A", 
NA, "44074", "5.0628000000000002", "44104", "-7.2036899999999999", 
"44104", "-7.71122", "44074", "3.45444"), ...3 = c(43251, 2.50914, 
43251, 7.08661, NA, NA, 44104, -9.33194, 44135, -1.46045, 44135, 
-1.33158, 44104, -6.43119), ...4 = c(43281, 10.58262, 43281, 
9.74265, NA, NA, 44135, -2.18682, 44165, 19.35054, 44165, 19.1103, 
44135, -2.34933), ...5 = c(43312, -2.08777, 43312, -3.51759, 
NA, NA, 44165, 21.84286, 44196, -2.95694, 44196, -3.05413, 44165, 
21.73591), ...6 = c(43343, 15.81356, 43343, 19.44444, NA, NA, 
44196, -0.38177, 44227, 7.80132, 44227, 7.27166, 44196, -5.5202
), ...7 = c(43373, -3.663, 43373, -4.36047, NA, NA, 44227, 7.00191, 
44255, 5.01423, 44255, 8.44608, 44227, 6.63778), ...8 = c(43404, 
-1.46088, 43404, -3.19149, NA, NA, 44255, 4.64376, 44286, 11.27339, 
44286, 8.3409, 44255, 3.08164), ...9 = c(43434, -7.53453, 43434, 
-9.89011, NA, NA, 44286, 7.97628, 44316, 3.1574, 44316, 3.81616, 
44286, 9.15585), ...10 = c(43465, -3.08588, 43465, -4.00697, 
NA, NA, 44316, 6.94333, 44347, 4.03152, 44347, 2.85649, 44316, 
6.65098), ...11 = c(43496, -1.09915, 43496, -0.54446, NA, NA, 
44347, 5.04557, 44377, 6.4516, 44377, 6.63552, 44347, 2.31668
), ...12 = c(43524, 10.30018, 43524, 12.22628, NA, NA, 44377, 
2.83606, 44408, -3.87638, 44408, -3.88994, 44377, 5.59935), ...13 = c(43555, 
4.50815, 43555, 7.47968, NA, NA, 44408, -3.8261, 44439, -1.63488, 
44439, -0.88845, 44408, -4.49692)), class = "data.frame", row.names = c("1", 
"2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", 
"14"))

需要将其转换为以下结构的长格式面板数据集：

Date  Firm  Return
10/2   X    2.04
10/3   X    2.07
10/2   Y    3.4
10/3   Y    4.2

尝试过的代码（未成功） #

#Create Row Identifiers to Group the Data
rets$two_days <- c(0, rep(1:(nrow(rets)-1)%/%2))

#Group by every two days and pivot wider
rets%>%group_by(two_days)%>%
  pivot_longer(cols = !1,names_to = "month", values_to = "ret")

解决方案 #

核心思路：先将每组的日期行和收益率行配对，分别转长格式后合并，再清理格式。

步骤1：分组并拆分日期与收益率行 #

先给每行分配组ID，确保每2行归为一组：

library(tidyverse)

# 假设数据已存入rets对象
# rets <- structure(...)  # 你的原始数据结构

# 生成组ID：每2行一组
rets <- rets %>%
  mutate(group_id = rep(seq_len(nrow(.) %/% 2 + 1), each = 2, length.out = nrow(.)))

# 拆分出日期行（组内第1行，...1为NA）和收益率行（组内第2行，...1为公司名）
date_rows <- rets %>% filter(is.na(...1))
return_rows <- rets %>% filter(!is.na(...1))

步骤2：分别转长格式并合并 #

将日期行和收益率行转为长格式，再按组和列名匹配合并：

# 日期行转长格式：保留group_id，将...2-...13转为Date列
date_long <- date_rows %>%
  pivot_longer(cols = starts_with("..."), names_to = "col_name", values_to = "Date", 
               cols = !c(group_id, ...1)) %>%
  select(group_id, col_name, Date) %>%
  # 过滤无效日期
  filter(!is.na(Date), Date != "-N/A") %>%
  # 将Excel序列号转为标准日期格式
  mutate(Date = as.Date(as.numeric(Date), origin = "1899-12-30"))

# 收益率行转长格式：保留group_id和公司名，将...2-...13转为Return列
return_long <- return_rows %>%
  pivot_longer(cols = starts_with("..."), names_to = "col_name", values_to = "Return",
               cols = !c(group_id, ...1)) %>%
  rename(Firm = ...1) %>%
  select(group_id, col_name, Firm, Return) %>%
  # 过滤无效收益率
  filter(!is.na(Return), Return != "-N/A") %>%
  # 转换为数值型
  mutate(Return = as.numeric(Return))

# 合并日期和收益率数据，去除中间辅助列
panel_data <- date_long %>%
  inner_join(return_long, by = c("group_id", "col_name")) %>%
  select(Date, Firm, Return) %>%
  # 可选：清理Firm名称，去掉末尾的" - Return"
  mutate(Firm = str_remove(Firm, " - Return"))

步骤3：查看结果 #

运行后panel_data即为目标面板数据集，示例输出：

head(panel_data)
#         Date       Firm   Return
# 1 2018-05-01 F00001092B  7.19931
# 2 2018-06-01 F00001092B  2.50914
# 3 2018-07-01 F00001092B 10.58262
# 4 2018-08-01 F00001092B -2.08777
# 5 2018-09-01 F00001092B 15.81356
# 6 2018-10-01 F00001092B -3.66300

关键说明 #

• 原代码失败原因：分组逻辑错误（two_days生成方式不正确），且未区分日期行和收益率行的不同含义，直接转长会导致日期与收益率混淆。
• 日期转换：Excel日期序列号以1899-12-30为原点，需用as.Date(as.numeric(Date), origin = "1899-12-30")转换为标准日期。
• 数据清理：过滤NA和-N/A无效值，将收益率转为数值型，公司名称可按需简化。

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