在R中如何按TA_code、TA_name分组统计各限速对应的crash数量

你需要的按TA区域维度聚合、每个限速值单独列统计事故数的需求，可以用以下两种方案实现：

方案1：自动适配所有限速值（推荐） #

借助tidyr::pivot_wider自动生成所有限速对应的列，无需手动枚举取值，适配性更强：

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

crash <- st_read("df")

crash_speed <- crash %>%
  # 先统计每个区域+限速组合的事故数
  count(TA_code, TA_name, speed_limit, name = "crash_cnt") %>%
  # 把限速值转为单独列，无事故的限速默认填充0
  pivot_wider(
    names_from = speed_limit,
    values_from = crash_cnt,
    values_fill = 0,
    names_prefix = "speed_"
  ) %>%
  # 新增列：返回当前区域事故最多的限速值
  rowwise() %>%
  mutate(
    max_crash_speed = str_remove(names(.)[which.max(c_across(starts_with("speed_")))], "speed_") %>% as.integer()
  ) %>%
  ungroup()

方案2：用mutate手动生成列 #

如果需要自定义每个限速列的统计逻辑，可以用该方案：

library(dplyr)
library(stringr)

crash <- st_read("df")

crash_speed <- crash %>%
  group_by(TA_code, TA_name) %>%
  summarise(
    speed_10 = sum(speed_limit == 10, na.rm = T),
    speed_20 = sum(speed_limit == 20, na.rm = T),
    speed_30 = sum(speed_limit == 30, na.rm = T),
    speed_40 = sum(speed_limit == 40, na.rm = T),
    speed_50 = sum(speed_limit == 50, na.rm = T),
    speed_60 = sum(speed_limit == 60, na.rm = T),
    speed_70 = sum(speed_limit == 70, na.rm = T),
    speed_80 = sum(speed_limit == 80, na.rm = T),
    speed_90 = sum(speed_limit == 90, na.rm = T),
    speed_100 = sum(speed_limit == 100, na.rm = T),
    speed_110 = sum(speed_limit == 110, na.rm = T),
    max_crash_speed = str_remove(names(cur_data())[which.max(c_across(starts_with("speed_")))], "speed_") %>% as.integer()
  ) %>%
  ungroup()

补充说明 #

• 两种方案最终输出结构一致，每一行对应一个TA区域，speed_xx列存储对应限速下的事故总数
• 统计逻辑加入na.rm = T，避免speed_limit存在缺失值时结果异常
• 额外新增的max_crash_speed列可直接返回该区域事故发生最多的限速值

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