R语言多列left_join设置多后缀报错，如何解决？

错误原因 #

dplyr的left_join()中，suffix参数仅用于单次连接时区分左右表重复的列名，只能接受长度为2的字符向量（分别对应左表、右表的重复列后缀），你传入长度为9的向量不符合参数要求，因此报错。

方案1：逐次连接时手动指定后缀 #

每次执行left_join时，明确设置当前连接的后缀（确保每次suffix都是长度2的向量），同时通过后缀区分不同列的合并结果：

df_in2 <- df_in %>%
  left_join(df_in_tidy, by = c("in_1" = "stock_name"), suffix = c("", "_1")) %>%
  left_join(dfedu_categories_tidy, by = c("in_2" = "stock_name"), suffix = c("", "_2")) %>%
  left_join(dfedu_categories_tidy, by = c("in_3" = "stock_name"), suffix = c("", "_3")) %>%
  left_join(dfedu_categories_tidy, by = c("in_4" = "stock_name"), suffix = c("", "_4")) %>%
  left_join(dfedu_categories_tidy, by = c("in_5" = "stock_name"), suffix = c("", "_5")) %>%
  left_join(dfedu_categories_tidy, by = c("in_6" = "stock_name"), suffix = c("", "_6")) %>%
  left_join(dfedu_categories_tidy, by = c("in_7" = "stock_name"), suffix = c("", "_7")) %>%
  left_join(dfedu_categories_tidy, by = c("in_8" = "stock_name"), suffix = c("", "_8")) %>%
  left_join(dfedu_categories_tidy, by = c("in_9" = "stock_name"), suffix = c("", "_9"))

注意：若前序连接已产生重复列名，需先重命名再执行下一次连接，避免列名混淆。

方案2：转长表合并后转回宽表（更高效） #

针对多列重复连接的场景，用tidyr的pivot_longer和pivot_wider处理更简洁，无需重复编写left_join：

library(dplyr)
library(tidyr)

# 1. 将df_in的in_1到in_9转换为长表格式
df_in_long <- df_in %>%
  pivot_longer(
    cols = starts_with("in_"), 
    names_to = "in_col", 
    values_to = "stock_name"
  )

# 2. 与tidy表合并
df_merged_long <- df_in_long %>%
  left_join(dfedu_categories_tidy, by = "stock_name")

# 3. 转回宽表，自动为不同in列的合并结果添加后缀
df_in2 <- df_merged_long %>%
  pivot_wider(
    id_cols = -c(in_col, stock_name),  # 保留原df_in中除in列外的所有列
    names_from = in_col,
    values_from = names(dfedu_categories_tidy)[!names(dfedu_categories_tidy) %in% "stock_name"],
    names_glue = "{.value}_{str_remove(in_col, 'in_')}"
  )

这种方式代码更简洁，不会因多次连接出现列名混乱，是多列合并场景的最优选择。

补充说明 #

dplyr不支持一次性为多次join设置对应后缀，因为每次join都是独立操作，仅能针对单次连接的重复列设置后缀。

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