如何用R语言rollapply计算日度周环比百分比变化
问题描述
我有如下结构的数据:
date col1 col2 col3 <chr> <dbl> <dbl> <dbl> 1 2020_09_01 53542. 22133. 25295. 2 2020_09_02 54157. 22505. 25327. 3 2020_09_03 54137. 23115. 24993. 4 2020_09_04 50795. 23127. 24166. 5 2020_09_05 32829. 19600. 21860.
我尝试使用rollapply函数计算7天间隔的百分比变化(即周环比百分比变化),但rollapply始终无法达到预期效果,我实际需要实现的是让rollapply每日计算当日相对于前一周(7天前)的百分比变化,现有代码如下:
lagPeriod = 7 matrixCalcFunction <- function(x){ (myData[[x]] - myData[[x - lagPeriod]]) / myData[[x - lagPeriod]] } myData %>% pivot_longer(cols = contains("col")) %>% tidyquant::tq_mutate( select = value, mutate_fun = rollapply, width = lagPeriod , align = "right", FUN = matrixCalcFunction )
预期输出结果
date col1 col2 col3 <chr> <dbl> <dbl> <dbl> 1 2020_09_01 NA NA NA 2 2020_09_02 NA NA NA 3 2020_09_03 NA NA NA 4 2020_09_04 NA NA NA 5 2020_09_05 NA NA NA 6 2020_09_06 NA NA NA 7 2020_09_07 -0.065 -0.055 -0.39 8 2020_09_08 -0.058 -0.029 -0.041 9 2020_09_09 0.068 0.071 0.039 10 2020_09_10 0.023 -0.0002 0.045
可复现数据集代码
myData <- structure(list(date = c("2020_09_01", "2020_09_02", "2020_09_03", "2020_09_04", "2020_09_05", "2020_09_06", "2020_09_07", "2020_09_08", "2020_09_09", "2020_09_10", "2020_09_11", "2020_09_12", "2020_09_13", "2020_09_14", "2020_09_15", "2020_09_16", "2020_09_17", "2020_09_18", "2020_09_19", "2020_09_20", "2020_09_21", "2020_09_22", "2020_09_23", "2020_09_24", "2020_09_25", "2020_09_26", "2020_09_27", "2020_09_28", "2020_09_29", "2020_09_30"), col1 = c(53542.497, 54156.934, 54136.844, 50794.971, 32828.797, 28475.082, 50083.573, 51017.288, 57819.908, 51945.242, 27823.172, 34349.466, 28527.527, 54845.664, 56531.057, 56556.415, 55396.121, 54303.732, 37513.441, 30041.867, 52397.815, 55449.939, 56203.125, 53654.182, 53289.437, 38511.761, 28046.879, 52132.573, 56055.611, 55520.683), col2 = c(22133.29, 22504.958, 23115.242, 23126.773, 19599.718, 16752.282, 20920.38, 21844.255, 24763.05, 23121.879, 17430.447, 20110.582, 18795.882, 24027.224, 24890.61, 24408.889, 24363.402, 24582.204, 20146.731, 18376.923, 23063.298, 24221.946, 25228.194, 24658.424, 23333.315, 20066.397, 17504.372, 23561.362, 23456.284, 24101.302), col3 = c(25294.573, 25326.797, 24992.764, 24166.084, 21859.885, 17549.005, 24306.496, 24269.409, 25968.326, 25253.976, 17974.404, 22636.375, 20105.166, 27000.274, 26291.22, 27277.371, 26851.75, 26133.317, 24055.107, 19515.875, 25573.014, 31957.279, 28961.316, 26896.495, 26440.726, 22941.927, 19990.825, 26595.878, 27725.468, 25965.802)), row.names = c(NA, -30L), class = c("tbl_df", "tbl", "data.frame"))
补充说明
我尝试运行了如下代码,但对diff(.x))/lag(.x, 7)的逻辑存在疑问,且得到的结果和预期输出不一致:
myData %>% column_to_rownames("date") %>% mutate(across(everything(), ~ round(c(NA, diff(.x))/lag(.x, 7), 5), names = "{col}_delta"))
针对单个观测值(比如col1的第1行和第7行),我可以通过如下方式计算:diff(c(pull(myData[7, 2]), pull(myData[1, 2]))) = 3458.924,再除以7天前的数值即可得到结果:3458.924 / pull(myData[1, 2]) = 0.0646,因此不清楚是否需要给diff函数添加类似diff(c(.x, lag(.x, 7)))的逻辑来得到正确结果?
解决方案
现有代码核心错误
rollapply传入FUN的参数是滑动窗口内的数值向量,不是行索引,你写的myData[[x]]直接把窗口向量当索引取值,逻辑完全错位- 计算7天间隔变化不需要滑动7天的窗口,只需要取当前值和前7天值两个数做差比,之前指定的
width = lagPeriod窗口长度不符合计算需求 - 你尝试的
diff(.x)/lag(.x,7)逻辑存在维度错位:diff(.x)默认计算相邻2天的差值,和分母的7天前时间点不匹配,自然结果错误
最简实现方式
周环比的本质逻辑是 (当日值 - 7天前的值) / 7天前的值,直接用dplyr的lag函数即可实现,不需要强行使用rollapply:
library(dplyr) result <- myData %>% mutate(across( .cols = starts_with("col"), .fns = ~ (.x - lag(.x, 7)) / lag(.x, 7), .names = "{.col}_wow" # 可自行修改命名规则,也可直接覆盖原列 )) # 验证前10行输出是否符合预期 head(result, 10)
必须使用rollapply的修正写法
如果有特殊滑动计算需求必须用rollapply,修正后的实现如下:
library(zoo) library(dplyr) library(tidyquant) lagPeriod <- 7 # 窗口函数:rollapply传入长度为lagPeriod+1的向量,取首尾两个值计算周环比 calc_wow <- function(x) { (x[length(x)] - x[1]) / x[1] } myData %>% pivot_longer(cols = starts_with("col")) %>% group_by(name) %>% # 按指标分组分别计算,避免跨列取值 tq_mutate( select = value, mutate_fun = rollapply, width = lagPeriod + 1, # 窗口需包含当前值+7天前的值,总长度为8 align = "right", FUN = calc_wow, na.rm = FALSE ) %>% pivot_wider(names_from = name, values_from = value) # 还原为宽表格式
diff函数的正确用法
如果要使用diff计算7天间隔变化,直接指定滞后参数即可,等价于.x - lag(.x, 7):
# 正确的diff计算方式 ~ diff(.x, lag = 7) / lag(.x, 7)
内容的提问来源于stack exchange,提问作者user8959427
相关产品推荐
相关产品推荐

