R语言：高效拆分列并统计category与value对应duration总和的方法

针对你描述的场景，无需拆分多个独立数据框，以下两种方法可高效完成统计需求：

方法一：使用tidyverse实现简洁处理 #

适合优先保证代码可读性的场景，通过链式操作一步完成拆分、转换与统计：

library(tidyverse)

# 模拟示例数据
df <- tibble(
  id = 1:3,
  overlaps = c(
    "1_hands:N:1.768,2_feet:Y:0.98,1_hands:N:0.5",
    "1_hands:Y:2.3,3_head:N:1.1",
    "2_feet:N:0.76,1_hands:N:0.32"
  )
)

# 核心处理逻辑
result <- df %>%
  # 将逗号分隔的多观测拆分为单独行
  separate_rows(overlaps, sep = ",") %>%
  # 将冒号分隔的三部分拆分为独立列
  separate(overlaps, into = c("category", "value", "duration"), sep = ":") %>%
  # 转换时长为数值型
  mutate(duration = as.numeric(duration)) %>%
  # 按category和value分组求和
  group_by(category, value) %>%
  summarise(total_duration = sum(duration), .groups = "drop")

print(result)

输出结果：

# A tibble: 5 × 3
  category value total_duration
  <chr>    <chr>          <dbl>
1 1_hands  N               2.59
2 1_hands  Y               2.3 
3 2_feet   N               0.76
4 2_feet   Y               0.98
5 3_head   N               1.1 

方法二：使用data.table提升处理效率 #

适合大数据集或批量处理多数据集的场景，data.table的操作速度远优于基础字符串拆分方法：

library(data.table)

# 转换为data.table格式
dt <- as.data.table(df)

# 核心处理逻辑
result_dt <- dt[, strsplit(overlaps, ","), by = id][
  , tstrsplit(V1, ":"), by = id][
    , .(total_duration = sum(as.numeric(V3))), by = .(category = V1, value = V2)]

print(result_dt)

批量处理多个数据集 #

如果要处理多个结构相同的数据集，可封装为函数批量执行：

# 基于tidyverse的批量处理函数
process_overlaps <- function(data) {
  data %>%
    separate_rows(overlaps, sep = ",") %>%
    separate(overlaps, into = c("category", "value", "duration"), sep = ":") %>%
    mutate(duration = as.numeric(duration)) %>%
    group_by(category, value) %>%
    summarise(total_duration = sum(duration), .groups = "drop")
}

# 假设有多个数据集df1、df2、df3，批量处理
dataset_list <- list(df1, df2, df3)
results_list <- lapply(dataset_list, process_overlaps)

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