在R中提取冒号后的值（含重复ratio%字段）

问题场景 #

给定包含重复ratio%字段的数据集（Note列存储结构化文本）：

Name Group  Note
 ENG1 AR1  　regio regnum, domain kingdom: 271  ratio%:   23.9  regio phylum, domain phylum: 328  ratio%:   46.7  
 ENG2 AR2  　regio regnum, domain kingdom: 543  ratio%:   63.9  regio phylum, domain phylum: 104  ratio%:   11.9  
 ENG3 AR2  　regio regnum, domain kingdom: 669  ratio%:   71.1  regio phylum, domain phylum: 703  ratio%:   82.4  
 ENG5 AR1  　regio regnum, domain kingdom: 306  ratio%:   44.4  regio phylum, domain phylum: 641  ratio%:   76.5  
 ENG6 AR4  　regio regnum, domain kingdom: 802  ratio%:   89.7  regio phylum, domain phylum:  12  ratio%:   3.4  
 ENG7 AR4  　regio regnum, domain kingdom: 175  ratio%:   10.6  regio phylum, domain phylum: 528  ratio%:   57.2  

尝试str_extract(x, "(?<=regio regnum, domain kingdom: )(.*)(?= ratio%)")提取效果不佳，需将Note列拆分为指定格式的数值列。

解决方案 #

方法1：用tidyr::extract直接拆分（推荐） #

通过正则分组一次性将Note列拆分为目标列，同时自动转换数值类型：

library(tidyr)
library(dplyr)

# 假设数据集名为df
df <- df %>%
  extract(
    Note,
    into = c(
      "regio regnum, domain kingdom",
      "ratio%",
      "regio phylum, domain phylum",
      "ratio%"
    ),
    regex = "regio regnum, domain kingdom:\\s*(\\d+\\.?\\d*)\\s*ratio%:\\s*(\\d+\\.?\\d*)\\s*regio phylum, domain phylum:\\s*(\\d+\\.?\\d*)\\s*ratio%:\\s*(\\d+\\.?\\d*)",
    convert = TRUE
  )

方法2：用stringr::str_extract_all批量提取 #

先提取所有冒号后的数值，再拆分到对应列：

library(stringr)
library(dplyr)
library(tidyr)

df <- df %>%
  mutate(
    # 匹配冒号后所有数字（含小数），忽略前后空格
    values = str_extract_all(Note, "(?<=:)\\s*\\d+\\.?\\d*")
  ) %>%
  unnest_wider(values, names_sep = "_") %>%
  rename(
    `regio regnum, domain kingdom` = values_1,
    `ratio%_1` = values_2,
    `regio phylum, domain phylum` = values_3,
    `ratio%_2` = values_4
  ) %>%
  select(-Note) %>%
  mutate(across(c(values_1:values_4), as.numeric))

方法3：单字段精准提取 #

如果需要单独提取某类数值，可使用精准正则避免多余匹配：

• 提取kingdom对应数值：str_extract(df$Note, "(?<=regio regnum, domain kingdom: )\\s*\\d+\\.?\\d*")
• 提取kingdom对应ratio%：str_extract(df$Note, "(?<=regio regnum, domain kingdom: \\d+\\.?\\d*\\s*ratio%: )\\s*\\d+\\.?\\d*")

以上方法均可得到目标格式结果：

Name Group regio regnum, domain kingdom ratio% regio phylum, domain phylum ratio%
1 ENG1 AR1                            271   23.9                         328   46.7
2 ENG2 AR2                            543   63.9                         104   11.9
3 ENG3 AR2                            669   71.1                         703   82.4
4 ENG5 AR1                            306   44.4                         641   76.5
5 ENG6 AR4                            802   89.7                          12    3.4
6 ENG7 AR4                            175   10.6                         528   57.2

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