如何在R中按Division统计不同文件后缀的数量汇总？

给定如下R语言数据框：

df <- data.frame(
  "Domain" = c("Euka"),
  "Kingdom" = c("An","Plan"),
  "Division" = c("20181121","20181128","20181203"),
  "Species" = c("20181115_AG25_MAGH_50_A05_CGT.TXT","20181122_AG25_MAGH_50_C05_CGT.ARR",
                "20181115_AG25_MAGH_50_G05_CGT.TXT","20181124_AG25_MAGH_50_G45_CGT.TXT",
                "20181204_AG25_MAGH_50_G05_CGT.ARR","20181205_AG25_MAGH_50_G45_CGT.TXT",
                "20181207_AG25_MAGH_50_T05_CGT.ARR","20181215_AG25_MAGH_50_F45_CGT.TXT",
                "20181223_AG25_MAGH_50_R07_CGT.GGI","20181225_TW77_MAGH_33_L06_CGT.ARR",
                "20181226_TW77_MAGH_33_S07_CGT.ARR","20181227_TW77_MAGH_33_C06_CGT.TXT")
)

需要生成按Division维度统计不同文件后缀数量的汇总表格，目标格式如下：

方法一：使用tidyverse工具集 #

步骤说明 #

1. 加载依赖包

library(tidyverse)

2. 提取文件后缀并生成统计标识
   从Species列中提取后缀，生成Total_后缀名格式的分类列：

df_processed <- df %>%
  mutate(
    Suffix = str_extract(Species, "\\.[A-Z]+$") %>% str_remove("\\."),
    Suffix_Type = paste0("Total_", Suffix)
  )

3. 分组计数并转换为宽表
   按Division和后缀类型统计数量，再将长格式数据转为目标宽格式，缺失值填充为0：

summary_table <- df_processed %>%
  count(Division, Suffix_Type) %>%
  pivot_wider(
    names_from = Division,
    values_from = n,
    values_fill = 0
  ) %>%
  select(Suffix_Type, "20181121", "20181128", "20181203") %>%
  rename(Division = Suffix_Type)

4. 查看结果

print(summary_table, row.names = FALSE)

方法二：基础R实现 #

步骤说明 #

1. 提取文件后缀

df$Suffix <- sub(".*\\.", "", df$Species)
df$Suffix_Type <- paste0("Total_", df$Suffix)

2. 生成交叉统计表

cross_tab <- table(df$Suffix_Type, df$Division)

3. 转换为数据框并调整格式

summary_table_base <- as.data.frame.matrix(cross_tab)
summary_table_base$Division <- rownames(summary_table_base)
summary_table_base <- summary_table_base[, c("Division", "20181121", "20181128", "20181203")]
rownames(summary_table_base) <- NULL

4. 查看结果

print(summary_table_base, row.names = FALSE)

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