R语言使用data.table实现年度值补全对应缺失季度记录

• 现有data.table类型数据集test_dt，value列存储2017、2018年A、B两个分组各季度活动、年度活动的销售增长率数据。
• 部分场景下分组在对应季度未举办季度活动，仅举办年度活动。需要在test_dt中新增行，将对应年度活动的增长值复制为该季度季度活动的增长值。
• 约束：仅允许使用data.table相关语法实现。

原始数据集 #

test_dt = structure(list(group = c("A", "A", "A", "A", "A", "A", "A", "A", 
"B", "B", "B", "B", "B", "B", "B", "B", "B"), yq = structure(c(17167, 
17257, 17348, 17440, 17532, 17622, 17713, 17805, 17167, 17257, 
17257, 17348, 17440, 17532, 17622, 17713, 17805), fiscal_start = 1, class = c("yearquarter", 
"vctrs_vctr")), event_type = c("quarterly", "annual", "quarterly", 
"quarterly", "quarterly", "quarterly", "quarterly", "quarterly", 
"quarterly", "annual", "quarterly", "quarterly", "quarterly", 
"quarterly", "annual", "quarterly", "quarterly"), value = c(4.39958592132506, 
-10.9243697478992, 3.8793103448276, -6.7936507936508, -3.44322344322345, 
4.41176470588236, -4.99153976311336, -1.98321891685735, -18.2953077916487, 
0.918273645546375, -4.99770747363595, 3.1849912739965, -10.7555555555556, 
-12.2950819672131, -1.15257958287595, -17.8796046720575, -6.99300699300699
)), row.names = c(NA, -17L), class = c("data.table", "data.frame"
))

原始数据输出预览：

> test_dt
    group      yq event_type       value
 1:     A 2017 Q1  quarterly   4.3995859
 2:     A 2017 Q2     annual -10.9243697
 3:     A 2017 Q3  quarterly   3.8793103
 4:     A 2017 Q4  quarterly  -6.7936508
 5:     A 2018 Q1  quarterly  -3.4432234
 6:     A 2018 Q2  quarterly   4.4117647
 7:     A 2018 Q3  quarterly  -4.9915398
 8:     A 2018 Q4  quarterly  -1.9832189
 9:     B 2017 Q1  quarterly -18.2953078
10:     B 2017 Q2     annual   0.9182736
11:     B 2017 Q2  quarterly  -4.9977075
12:     B 2017 Q3  quarterly   3.1849913
13:     B 2017 Q4  quarterly -10.7555556
14:     B 2018 Q1  quarterly -12.2950820
15:     B 2018 Q2     annual  -1.1525796
16:     B 2018 Q3  quarterly -17.8796047
17:     B 2018 Q4  quarterly  -6.9930070

预期输出 #

> test_dt
    group      yq event_type       value
 1:     A 2017 Q1  quarterly   4.3995859
 2:     A 2017 Q2     annual -10.9243697
 3:     A 2017 Q2  quarterly -10.9243697
 4:     A 2017 Q3  quarterly   3.8793103
 5:     A 2017 Q4  quarterly  -6.7936508
 6:     A 2018 Q1  quarterly  -3.4432234
 7:     A 2018 Q2  quarterly   4.4117647
 8:     A 2018 Q3  quarterly  -4.9915398
 9:     A 2018 Q4  quarterly  -1.9832189
10:     B 2017 Q1  quarterly -18.2953078
11:     B 2017 Q2     annual   0.9182736
12:     B 2017 Q2  quarterly  -4.9977075
13:     B 2017 Q3  quarterly   3.1849913
14:     B 2017 Q4  quarterly -10.7555556
15:     B 2018 Q1  quarterly -12.2950820
16:     B 2018 Q2     annual  -1.1525796
17:     B 2018 Q2  quarterly  -1.1525796
18:     B 2018 Q3  quarterly -17.8796047
19:     B 2018 Q4  quarterly  -6.9930070

# 加载data.table
library(data.table)

# 步骤1：按group+yq分组，筛选出"仅存在年度活动、无季度活动"的年度记录，修改事件类型为quarterly
add_rows <- test_dt[
  , .SD[event_type == "annual" & !"quarterly" %in% event_type], 
  by = .(group, yq)
][, event_type := "quarterly"]

# 步骤2：将新增行追加到原表，按分组、季度、事件类型排序得到最终结果
test_dt <- rbindlist(list(test_dt, add_rows), use.names = TRUE)[
  order(group, yq, event_type)
]

逻辑说明：字符串排序规则下annual首字母a排在quarterly首字母q之前，排序后自然保持年度活动在前、季度活动在后的顺序，和预期输出格式一致。

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