如何修改R代码保留非Team_模式列并实现多列左连接？

处理包含Company、City这类非Team_命名列，以及20+个带数字后缀的Team相关列的df_1时，原代码会丢失这些非Team模式的列。需要调整代码，在完成Team_URL与df_2的URL列左连接的同时，保留所有非Team列，输出格式符合要求。

示例数据 #

df_1 <- data.frame (Company = c("Comp1", "Comp2", "Comp3"),
                    City = c("Atlanta", "New York", "Boston"),
                    Team_1  = c("wrg", "werg", "sdf"),
                    Team_Desc_1  = c("wer", "wtrb", "wergt"),
                    Team_URL_1 = c("ewrg", "werg", "asd"),
                    Team_Ver_1  = c("25", "2523", "342"),
                    Team_Num_1 = c("aed", "jfsa", "vsf"),
                    Team_Value_1 = c("aed", "jfsa", "vsf"),
                    Team_2  = c("werh", "wtt", "qwe"),
                    Team_Desc_2  = c("sdfg", "wer", "sdfgv"),
                    Team_URL_2 = c("qwe", "gvre", "vrw"),
                    Team_Ver_2  = c("4123", "5133", "4126"),
                    Team_Num_2 = c("aefbvd", "jfswreta", "vsefwf"),
                    Team_Value_2 = c("aewed", "jfsbwa", "vsbf")
)

df_2 <- data.frame (Name  = c("etwbv", "werg", "sdfg", "qwreg", "gvr", "wref"),
                    URL = c("ewrg", "werg", "asd", "qwe", "gvre", "vrw"),
                    Txt = c("abc", "bfh", "fse", "rege", "wer", "vwr"),
                    Head = c("abc1", "bfh", "fse", "rege1", "wer", "vwr")
)

原代码（丢失非Team列） #

df_1 %>%
  pivot_longer(everything(), names_to = c(".value", "id"), names_pattern = '(.*)_(\d)$') %>% 
  left_join(df_2, by = c("Team_URL" = "URL")) %>% 
  mutate(row_id = row_number(), .by = id) %>% 
  pivot_wider(names_from = id, values_from = Team:Head, names_vary = "slowest") %>% 
  select(-row_id)

期望输出 #

Company City Team_1 Team_Des…¹ Team_…² Team_…³ Team_…⁴ Team_…⁵ Name_1 Txt_1 Head_1 Team_2 Team_…⁶ Team_…⁷ 
Comp1   Atlanta     wrg    wer        ewrg    25      aed     aed     etwbv  abc   abc1   werh   sdfg    qwe    
Comp2   New York    werg   wtrb       werg    2523    jfsa    jfsa    werg   bfh   bfh    wtt    wer     gvre   
Comp3   Boston      sdf    wergt      asd     342     vsf     vsf     sdfg   fse   fse    qwe    sdfgv   vrw    

library(tidyverse)

df_1 %>%
  # 只处理Team开头的列，保留Company、City不动
  pivot_longer(
    cols = starts_with("Team_"),
    names_to = c(".value", "id"),
    names_pattern = '(Team.*)_([0-9]+)$'
  ) %>%
  # 左连接df_2
  left_join(df_2, by = c("Team_URL" = "URL")) %>%
  # 给每一行原始数据加标识，避免转宽格式时错位
  mutate(original_row = row_number(), .by = c(Company, City)) %>%
  # 转回宽格式，保留非Team列
  pivot_wider(
    id_cols = c(Company, City, original_row),
    names_from = id,
    values_from = c(Team, Team_Desc, Team_URL, Team_Ver, Team_Num, Team_Value, Name, Txt, Head),
    names_vary = "slowest"
  ) %>%
  # 移除辅助列，恢复原始顺序
  select(-original_row) %>%
  arrange(Company, City)

改动要点 #

• 精准选择转换列：用starts_with("Team_")限定仅转换Team相关列，Company和City全程保留，不会被误转换。
• 添加原始行标识：通过original_row确保每一组原始数据在转宽格式时对应正确，避免数据错位。
• 明确宽格式参数：id_cols指定保留的非Team列，values_from列出所有需要扩展的字段，保证输出列完整匹配期望格式。

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