如何为DataFrame添加匹配矩阵的seasonal_factor列？解决left_join报错

错误原因 #

你的lookup_df中，月份（Jan、Apr等）是以列名形式存在的，而D1中的Month是存储月份取值的列。直接用left_join按Month匹配时，lookup_df里没有名为Month的列，因此报错。

修正方案 #

需要先将lookup_df从宽格式转换为长格式，让月份成为一列取值，再与D1进行关联。具体步骤如下：

完整代码 #

# 加载tidyverse工具包（包含dplyr和tidyr）
library(tidyverse)

# 原始数据
D1 <- structure(list(year = c("2021", "2021", "2021", "2021", "2021"), Month = c("Apr", "Apr", "Apr", "Apr", "Apr"), Weekday = c("Monday","Monday", "Monday", "Monday", "Thursday"), hour = c("07", "08", "16", "17", "07"), avg_speed = c(40.2, 38.3, 40, 40.1, 39.6), avg_volume = c(2612, 2389, 2108, 1948, 2612)), row.names = c(NA, 5L), class = c("tbl_df", "tbl", "data.frame"))

lookup_df <- structure(list(Weekday = c("Sunday", "Monday", "Tuesday", "Wednesday", 
"Thursday", "Friday"), Jan = c(1.816, 1.123, 1.15, 1.089, 1.065, 
1.03), Feb = c(1.617, 1.122, 1.171, 1.05, 1.045, 1.031), Mar = c(1.428, 
1.018, 0.988, 0.96, 0.97, 0.919), Apr = c(1.354, 1, 0.966, 0.956, 
0.956, 0.907), May = c(1.233, 0.949, 0.917, 0.897, 0.88, 0.863
), Jun = c(1.158, 0.919, 0.896, 0.878, 0.852, 0.848), Jul = c(1.177, 
0.957, 0.896, 0.866, 0.855, 0.852), Aug = c(1.145, 0.909, 0.881, 
0.868, 0.853, 0.831), Sep = c(1.168, 0.91, 0.907, 0.889, 0.86, 
0.82), Oct = c(1.234, 0.942, 0.899, 0.883, 0.871, 0.845), Nov = c(1.364, 
0.944, 0.922, 0.901, 0.905, 0.855), Dec = c(1.358, 1.011, 0.971, 
0.945, 0.938, 0.931), year = c(2021, 2021, 2021, 2021, 2021, 
2021)), row.names = c(NA, 6L), class = "data.frame")

# 1. 将lookup_df转为长格式，提取月份和对应的季节因子
lookup_long <- lookup_df %>%
  pivot_longer(
    cols = Jan:Dec,  # 选中所有月份列
    names_to = "Month",  # 列名转为Month列的取值
    values_to = "seasonal_factor"  # 列值转为季节因子列
  ) %>%
  mutate(year = as.character(year))  # 统一year类型为字符，匹配D1的year格式

# 2. 左连接两个数据框
D2 <- left_join(D1, lookup_long, by = c("year", "Weekday", "Month"))

# 查看结果
print(D2)

代码说明 #

1. 格式转换：用pivot_longer将lookup_df中以列名存在的月份（Jan~Dec）转换为Month列的取值，同时将对应数值存入seasonal_factor列。
2. 类型统一：把lookup_long的year转为字符型，与D1的year类型保持一致，避免因类型不匹配导致的连接失败。
3. 左连接：此时两个数据框拥有相同的连接键（year、Weekday、Month），可以正确关联并添加seasonal_factor列。

原始错误信息（翻译后） #

left_join()执行错误：用于连接的列在数据框y中不存在，问题出在Month列。运行rlang::last_trace()查看错误发生位置。

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