如何从DataFrame的多个Team_URL列提取LinkedIn链接末尾ID？

我有一个名为df_1的DataFrame，包含70个格式为Team_URL_1、Team_URL_2……Team_URL_70的列，这些列存储的是https://www.linkedin.com/in/namexyz/格式的LinkedIn链接。需要从每个链接末尾的两个斜杠之间提取ID（如namexyz），并生成对应的Team_ID_n列存储该ID。

示例输入数据：

structure(list(Company = c("csd", "fwef", "wrev"), Team_1 = c("0", 
"werg", "sdf"), Team_Desc_1 = c("wer", "wtrb", "wergt"), Team_URL_1 = c("https://www.linkedin.com/in/namexyz/", 
"https://www.linkedin.com/in/namesrvf/", "https://www.linkedin.com/in/nameawrf/"
), Team_Ver_1 = c("25", "2523", "342"), Team_Num_1 = c(0, 23, 
12), Team_Value_1 = c("aed", "jfsa", "vsf"), Team_2 = c("werh", 
"wtt", "qwe"), Team_Desc_2 = c("sdfg", "wer", "sdfgv"), Team_URL_2 = c("https://www.linkedin.com/in/namexqwrg/", 
"https://www.linkedin.com/in/namewqrg/", "https://www.linkedin.com/in/nameqerwg/"
), Team_Ver_2 = c("4123", "5133", "4126"), Team_Num_2 = c(3, 
0, 123), Team_Value_2 = c("aewed", "jfsbwa", "vsbf")), class = "data.frame", row.names = c(NA, 
-3L))

期望输出（新增Team_ID_n列）：

Company Team_1 Team_Desc_1  Team_URL_1                             Team_Ver_1 Team_Num_1 Team_Value_1 Team_ID_1     ...
csd     0      wer          https://www.linkedin.com/in/namexyz/   25         0          aed          namexyz
fwef    werg   wtrb         https://www.linkedin.com/in/namesrvf/  2523       23         jfsa         namesrvf
wrev    sdf    wergt        https://www.linkedin.com/in/nameawrf/  342        12         vsf          nameawrf

方法一：tidyverse 批量处理 #

借助dplyr的批量列操作和stringr的正则提取，代码简洁高效：

library(tidyverse)

# 筛选所有Team_URL开头的列
url_cols <- str_subset(colnames(df_1), "^Team_URL_\\d+$")

# 提取ID并生成对应Team_ID列
df_1 <- df_1 %>%
  mutate(
    across(
      all_of(url_cols),
      ~str_extract(., "(?<=in/)[^/]+"),  # 匹配in/后到下一个/前的内容
      .names = "Team_ID_{str_remove(.col, 'Team_URL_')}"
    )
  )

方法二：Base R 原生实现 #

无需额外安装包，通过循环和正则完成处理：

# 获取所有Team_URL列的名称
url_cols <- grep("^Team_URL_\\d+$", colnames(df_1), value = TRUE)

# 逐个处理每个URL列
for (col in url_cols) {
  # 生成对应的Team_ID列名
  id_col <- gsub("Team_URL_", "Team_ID_", col)
  # 用正则提取ID：匹配in/后的内容，直到末尾或下一个/
  df_1[[id_col]] <- sub(".*/in/([^/]+)/?$", "\\1", df_1[[col]])
}

正则说明 #

两种方法用到的正则逻辑一致：

• (?<=in/)[^/]+：正向断言确保匹配in/之后的内容，[^/]+匹配所有非斜杠字符，直到遇到下一个斜杠停止。
• .*/in/([^/]+)/?$：匹配任意字符直到in/，捕获括号内的内容（ID），/?兼容链接末尾有无斜杠的情况。

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