基于tidyverse转换xts价格为月度收益率的报错排查

问题概述 #

从xts格式的prices对象提取日期创建tibble日期列时，遇到两个问题：

1. 执行index(.)时报错：object '.' not found
2. 修复第一个问题后，执行spread(asset, returns)时报错：Each row of output must be identified by a unique combination of keys

原代码与报错信息 #

原代码 #

# Converting Daily Prices to Monthly Returns in the tidyverse
asset_returns_dplyr = 
  prices %>%
  to.monthly(indexAt = "lastof", OHLC = FALSE) %>%
  # convert the index to a date
  data.frame(date = index(.)) %>%
  # now remove the index because it got converted to row names
  remove_rownames() %>%
  gather(asset, prices, -date) %>%
  group_by(asset) %>% 
  mutate(returns = (log(prices) - log(lag(prices)))) %>%
  select(-prices) %>%
  spread(asset, returns) %>%
  select(date, symbols) %>%
  na.omit()

报错信息 #

Error in `spread()`:
! Each row of output must be identified by a unique combination of keys.
ℹ Keys are shared for 625 rows
• 501, 502, 503, 504, 505, 506, 507, 508, 509, 510, 511, 512, 513, 514, 515, 516, 517, 
518, 519, 520, 521, 522,
  523, 524, 525, 526, 527, 528, 529, 530, 531, 532, 533, 534, 535, 536, 537, 538, 539, 5 
540, 541, 542, 543, 544,
545, 546, 547, 548, 549, 550, 551, 552, 553, 554, 555, 556, 557, 558, 559, 560, 561, 
562, 

prices对象结构 #

structure(c(117.827011108398, 120.846908569336, 120.57389831543, 
121.103385925293, 120.772438049316, 120.424964904785, 
120.73104095459, 
121.690849304199, 121.682556152344, 121.59984588623, 
42.5093727111816, 
43.1672821044922, 42.7486114501953, 42.9654159545898, 
42.7785224914551, 
42.5392799377441, 42.7560882568359, 43.3018455505371, 
43.3915596008301, 
43.4438934326172, 34.7362937927246, 35.6893730163574, 
35.6421546936035, 
35.9212112426758, 35.7108459472656, 35.5734710693359, 
35.6765060424805, 
35.7194404602051, 35.693675994873, 35.6850929260254, 
35.7743263244629, 
36.4761009216309, 36.2179832458496, 36.2905693054199, 
36.0163269042969, 
35.6936645507812, 35.8469200134277, 36.1615180969238, 
35.8711166381836, 
36.0243835449219, 86.7947540283203, 86.6931762695312, 
86.4743957519531, 
86.5681304931641, 86.5212860107422, 86.5993957519531, 
86.5369033813477, 
86.5290603637695, 86.6150131225586, 86.6931762695312), class = 
c("xts", 
"zoo"), src = "yahoo", updated = structure(1685658877.66735, class 
= c("POSIXct", 
"POSIXt")), index = structure(c(1356912000, 1357084800, 1357171200, 
1357257600, 1357516800, 1357603200, 1357689600, 1357776000, 
1357862400, 
1358121600), tzone = "UTC", tclass = "Date"), dim = c(10L, 5L
), dimnames = list(NULL, c("SPY", "EFA", "IJS", "EEM", "AGG")))

解决方案 #

问题1：index(.)找不到对象的修复 #

原因：管道操作中，data.frame(date = index(.))里的.没有正确引用前一步的xts对象——管道默认将前一步输出作为下一个函数的第一个参数，但此处.是在index()的参数中，而非data.frame的第一个参数，导致R无法识别.。

修复方法：将xts对象转为数据框时，先提取行名（即xts的索引）作为日期列，再转为Date类型：

prices %>%
  to.monthly(indexAt = "lastof", OHLC = FALSE) %>%
  as.data.frame() %>%
  rownames_to_column(var = "date") %>% # 将行名转为date列
  mutate(date = as.Date(date)) # 确保日期为Date类型

问题2：spread键不唯一的修复 #

原因：gather后的数据中，存在重复的date + asset组合，导致spread无法确定每一行的唯一标识。

修复方法：

1. 先去除重复的date + asset组合；
2. 推荐使用pivot_wider替代已弃用的spread，功能更灵活。

完整修正代码 #

# 将每日价格转换为月度收益率（修正版）
asset_returns_dplyr = 
  prices %>%
  to.monthly(indexAt = "lastof", OHLC = FALSE) %>%
  # 转换xts为数据框并提取日期
  as.data.frame() %>%
  rownames_to_column(var = "date") %>%
  mutate(date = as.Date(date)) %>%
  # 宽表转长表
  gather(asset, prices, -date) %>%
  # 去除重复的日期-资产组合
  distinct(date, asset, .keep_all = TRUE) %>%
  # 计算对数收益率
  group_by(asset) %>% 
  mutate(returns = log(prices) - log(lag(prices))) %>%
  select(-prices) %>%
  # 长表转宽表（替代spread）
  pivot_wider(names_from = asset, values_from = returns) %>%
  # 选择日期和资产列（替换为你的symbols向量，或用everything()）
  select(date, SPY, EFA, IJS, EEM, AGG) %>%
  na.omit()

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