如何解析列数可变的行并按下划线前缀拆分对齐数据列

1. 加载依赖包与原始数据 #

首先加载tidyverse工具包（处理数据更便捷），并导入你的数据集：

library(tidyverse)

df <- structure(list(V1 = c(2949735L, 3041435L, 2972485L, 3038555L, 
2970906L, 3041901L), V2 = c("superkingdom_Bacteria,phylum_Pseudomonadota,class_Alphaproteobacteria,order_Hyphomicrobiales,family_Lichenihabitantaceae,genus_Lichenifustis", 
"superkingdom_Bacteria,phylum_Pseudomonadota,class_Alphaproteobacteria,order_Hyphomicrobiales,family_Beijerinckiaceae,genus_Methylocapsa", 
"superkingdom_Bacteria,phylum_Pseudomonadota,class_Alphaproteobacteria,order_Sphingomonadales,family_Sphingomonadaceae,genus_Sphingomonas", 
"superkingdom_Bacteria,phylum_Pseudomonadota,class_Betaproteobacteria,order_Burkholderiales,family_Burkholderiaceae,genus_Caballeronia", 
"superkingdom_Bacteria,phylum_Pseudomonadota,class_Alphaproteobacteria,order_Rhodospirillales,family_Azospirillaceae,genus_Azospirillum", 
"superkingdom_Eukaryota,kingdom_Viridiplantae,phylum_Chlorophyta,class_Ulvophyceae,clade_TCBD clade,order_Bryopsidales,suborder_Bryopsidineae,family_Bryopsidaceae,genus_Bryopsis"
)), row.names = c(NA, 6L), class = "data.frame")

2. 三步完成数据拆分对齐 #

步骤1：按逗号拆分V2列为多行

把每个样本的分类层级拆成单独行，方便后续处理：

df_split <- df %>%
  separate_rows(V2, sep = ",")

步骤2：拆分层级名称与分类值

将每个分类字符串按下划线分割，提取出层级（如superkingdom）和对应分类值（如Bacteria）：

df_keyval <- df_split %>%
  separate(V2, into = c("level", "taxon"), sep = "_", extra = "merge")

extra = "merge"用于兼容带下划线的分类值（比如如果有clade_TCBD_clade，会把下划线后的内容全部保留为分类值）。

步骤3：转换为宽格式对齐

把层级名称作为列名，对应分类值填充到样本的对应位置：

df_final <- df_keyval %>%
  pivot_wider(names_from = level, values_from = taxon)

3. 查看最终结果 #

运行print(df_final)会得到如下对齐后的数据集：

# A tibble: 6 × 10
      V1 superkingdom phylum        class               order               family               genus           kingdom     clade       suborder    
    <int> <chr>        <chr>         <chr>               <chr>               <chr>                <chr>           <chr>       <chr>       <chr>       
1 2949735 Bacteria     Pseudomonadota Alphaproteobacteria Hyphomicrobiales    Lichenihabitantaceae Lichenifustis   NA          NA          NA          
2 3041435 Bacteria     Pseudomonadota Alphaproteobacteria Hyphomicrobiales    Beijerinckiaceae     Methylocapsa    NA          NA          NA          
3 2972485 Bacteria     Pseudomonadota Alphaproteobacteria Sphingomonadales    Sphingomonadaceae    Sphingomonas    NA          NA          NA          
4 3038555 Bacteria     Pseudomonadota Betaproteobacteria  Burkholderiales     Burkholderiaceae     Caballeronia    NA          NA          NA          
5 2970906 Bacteria     Pseudomonadota Alphaproteobacteria Rhodospirillales    Azospirillaceae      Azospirillum    NA          NA          NA          
6 3041901 Eukaryota    Chlorophyta   Ulvophyceae         Bryopsidales        Bryopsidaceae        Bryopsis        Viridiplantae TCBD clade Bryopsidineae

4. 基础R替代方案（无需tidyverse） #

如果不想用第三方包，可以用基础R实现：

# 拆分V2列
split_v2 <- strsplit(df$V2, ",")
# 转换为键值对数据框
keyval_list <- lapply(seq_along(split_v2), function(i) {
  kv <- strsplit(split_v2[[i]], "_", fixed = TRUE)
  data.frame(
    V1 = df$V1[i],
    level = sapply(kv, `[`, 1),
    taxon = sapply(kv, function(x) paste(x[-1], collapse = "_"))
  )
})
df_keyval_base <- do.call(rbind, keyval_list)
# 转换为宽格式
df_final_base <- reshape(df_keyval_base, idvar = "V1", timevar = "level", direction = "wide")
names(df_final_base) <- gsub("taxon\\.", "", names(df_final_base))

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