如何按行名与多列lag值匹配，将DF2的Loss数据聚合到DF1？

空数据框DF1 #

DF1 <- matrix(data = NA, nrow = 4, ncol = 4)
rownames(DF1) <- c("Q1_2020", "Q2_2020", "Q3_2020", "Q4_2020")
colnames(DF1) <- seq(1,4,1)
DF1

输出：

1  2  3  4
Q1_2020 NA NA NA NA
Q2_2020 NA NA NA NA
Q3_2020 NA NA NA NA
Q4_2020 NA NA NA NA

DF1的列名代表lag值，该值也存在于DF2中。

样本数据框DF2 #

Loss <- c(100,200,150,100,50,0,0,50,500,0)
AccidentQtr <- c("Q1_2020", "Q1_2020", "Q1_2020", "Q2_2020", "Q2_2020", "Q2_2020", "Q3_2020", "Q3_2020", "Q4_2020", "Q4_2020")
Q1_2020 <- c(1,1,1,2,2,2,3,3,4,4)
Q2_2020 <- c(2,2,2,3,3,3,4,4,NA,NA)
Q3_2020 <- c(3,3,3,4,4,4,NA,NA,NA,NA)
Q4_2020 <- c(4,4,4,NA,NA,NA,NA,NA,NA,NA)
DF2 <- data.frame(Loss, AccidentQtr, Q1_2020, Q2_2020, Q3_2020, Q4_2020)
DF2

输出：

Loss AccidentQtr Q1_2020 Q2_2020 Q3_2020 Q4_2020
1   100     Q1_2020       1       2       3       4
2   200     Q1_2020       1       2       3       4
3   150     Q1_2020       1       2       3       4
4   100     Q2_2020       2       3       4      NA
5    50     Q2_2020       2       3       4      NA
6     0     Q2_2020       2       3       4      NA
7     0     Q3_2020       3       4      NA      NA
8    50     Q3_2020       3       4      NA      NA
9   500     Q4_2020       4      NA      NA      NA
10    0     Q4_2020       4      NA      NA      NA

DF2的第3至6列对应DF1的lag值，需以此为关联条件，将DF2的Loss数据聚合到DF1中，匹配规则为：DF1行名 = DF2的AccidentQtr列值。

实现步骤 #

使用tidyr和dplyr包完成数据转换与聚合，代码如下：

1. 转换为长格式并过滤缺失值 #

library(tidyr)
library(dplyr)

df_long <- DF2 %>%
  pivot_longer(cols = Q1_2020:Q4_2020, names_to = "TargetQtr", values_to = "lag") %>%
  filter(!is.na(lag))

2. 按分组聚合Loss #

df_agg <- df_long %>%
  group_by(AccidentQtr, lag) %>%
  summarise(total_loss = sum(Loss), .groups = "drop")

3. 重构为宽格式并填充到DF1 #

df_wide <- df_agg %>%
  pivot_wider(names_from = lag, values_from = total_loss) %>%
  column_to_rownames("AccidentQtr")

# 将聚合结果填充到DF1
DF1[rownames(df_wide), colnames(df_wide)] <- df_wide

最终结果 #

执行后DF1的输出：

1    2    3    4
Q1_2020 450  450  450  450
Q2_2020 NA  150  150  150
Q3_2020 NA   NA   50   50
Q4_2020 NA   NA   NA  500

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