基于邮政编码分组并透视统计各分类字段计数的R语言实现问题

我明白你的需求啦——你想要按邮政编码（Postleitzahl）分组，不仅算出每个邮编的总案例数和平均年龄，还要把每个分类字段的不同取值都转成单独的列，统计每个邮编下对应取值的出现次数对吧？

先说说你原来代码的问题 #

你之前用了n_distinct()函数，这个函数是用来统计每个分组下该字段有多少不同的取值，而不是每个取值的具体出现次数，所以自然没法得到你想要的“每个分类值作为新列、显示计数”的效果。

解决方案（分步实现） #

我们可以用tidyverse工具包来完成这个需求，分两步走：先计算基础统计量，再处理每个分类字段的计数并透视成宽表，最后合并所有结果。

1. 加载所需工具包并准备数据

library(tidyverse)

# 你的原始数据
my_data <- data.frame(
  Datum = c("2022-01-01", "2022-02-02", "2022-03-03","2021-03-09", "2021-02-02", "2020-01-06"),
  Alter = c(45, 33, 22, 34,21, 33),
  Postleitzahl = c("8000", "8001", "8002","8000", "8001", "8002"),
  Triagekategorie = c("red", "yellow", "green","red", "yellow", "green"),
  Nationalitaet = c("Swiss", "German", "Italian","Swiss", "German", "French"),
  Nationalitaet_Typ = c("A", "B", "C", "A", "D", "C"),
  Grund = c("accident", "illness", "injury","injury", "illness", "injury"),
  Typ = c("type1", "type2", "type3","type3", "type6", "type3"),
  Monat = c("01", "02", "03","09", "02", "06"),
  Tag = c("01", "02", "03", "03", "02", "01"),
  Jahr = c("2022", "2022", "2022","2021", "2021", "2020"),
  Ort = c("Zurich", "Geneva", "Basel","Basel", "Geneva", "Bern"),
  Kanton = c("ZH", "GE", "BS","BS", "GE", "BE"),
  geometry = c("POINT(8.54 47.37)", "POINT(6.14 46.21)", "POINT(7.59 47.56)", "POINT(7.59 47.56)", "POINT(6.14 46.21)", "POINT(3.59 44.56)")
)

2. 计算基础统计量（总案例数、平均年龄）

base_stats <- my_data %>%
  group_by(Postleitzahl) %>%
  summarise(
    n_cases = n(),  # 总案例数
    Average_Age = mean(Alter, na.rm = TRUE),  # 平均年龄
    .groups = "drop"  # 取消分组状态
  )

3. 处理单个分类字段的计数并透视

以Triagekategorie为例，我们先按邮编和分类值分组计数，再把分类值转成列：

triage_counts <- my_data %>%
  group_by(Postleitzahl, Triagekategorie) %>%
  summarise(count = n(), .groups = "drop") %>%
  pivot_wider(
    names_from = Triagekategorie,  # 把分类值作为列名
    values_from = count,  # 把计数作为列值
    values_fill = 0  # 没有该分类值的邮编填0
  )

用同样的方法处理其他分类字段（Nationalitaet、Nationalitaet_Typ、Grund、Typ）：

# 国籍计数
nationalitaet_counts <- my_data %>%
  group_by(Postleitzahl, Nationalitaet) %>%
  summarise(count = n(), .groups = "drop") %>%
  pivot_wider(names_from = Nationalitaet, values_from = count, values_fill = 0)

# 国籍类型计数
nationalitaet_typ_counts <- my_data %>%
  group_by(Postleitzahl, Nationalitaet_Typ) %>%
  summarise(count = n(), .groups = "drop") %>%
  pivot_wider(names_from = Nationalitaet_Typ, values_from = count, values_fill = 0)

# 原因计数
grund_counts <- my_data %>%
  group_by(Postleitzahl, Grund) %>%
  summarise(count = n(), .groups = "drop") %>%
  pivot_wider(names_from = Grund, values_from = count, values_fill = 0)

# 类型计数
typ_counts <- my_data %>%
  group_by(Postleitzahl, Typ) %>%
  summarise(count = n(), .groups = "drop") %>%
  pivot_wider(names_from = Typ, values_from = count, values_fill = 0)

4. 合并所有统计结果

把基础统计和各个分类字段的统计结果按邮编合并：

final_data <- base_stats %>%
  left_join(triage_counts, by = "Postleitzahl") %>%
  left_join(nationalitaet_counts, by = "Postleitzahl") %>%
  left_join(nationalitaet_typ_counts, by = "Postleitzahl") %>%
  left_join(grund_counts, by = "Postleitzahl") %>%
  left_join(typ_counts, by = "Postleitzahl")

# 查看最终结果
print(final_data)

更高效的批量处理方式 #

如果你的分类字段很多，重复写代码太麻烦，可以用map()函数批量处理：

# 定义需要处理的分类字段列表
categorical_cols <- c("Triagekategorie", "Nationalitaet", "Nationalitaet_Typ", "Grund", "Typ")

# 批量生成所有分类字段的统计宽表
categorical_stats <- map(categorical_cols, function(col) {
  my_data %>%
    group_by(Postleitzahl, .data[[col]]) %>%
    summarise(count = n(), .groups = "drop") %>%
    pivot_wider(names_from = .data[[col]], values_from = count, values_fill = 0)
}) %>%
  reduce(left_join, by = "Postleitzahl")  # 合并所有分类统计宽表

# 合并基础统计和分类统计
final_data <- base_stats %>%
  left_join(categorical_stats, by = "Postleitzahl")

最终结果示例 #

运行后得到的final_data就和你期望的结构一致啦，比如：

备注：内容来源于stack exchange，提问作者MisterCoder