使用tidyverse的pivot/group_by/summarise处理嵌套数据统计需求

模拟数据 #

df <- structure(list(stop1 = c("New York", "Milwaukee", "New York",
                               "Los Angeles", NA, "Milwaukee"),
                     stop2 = c(NA, "New York", "Los Angeles", 
                               "New York", NA, "New York"),
                     stop1_apple = c("apple", "apple", NA, "apple", NA, "apple"),
                     stop1_pear = c("pear", "pear", "pear", NA, NA, "pear"),
                     stop2_apple = c(NA, "apple", "apple", NA, NA, "apple"), 
                     stop2_pear = c(NA, "pear", "pear", "pear", NA, NA)),
                class = "data.frame", row.names = c(NA, -6L))

df
        stop1       stop2 stop1_apple stop1_pear stop2_apple stop2_pear
1    New York        <NA>       apple       pear        <NA>       <NA>
2   Milwaukee    New York       apple       pear       apple       pear
3    New York Los Angeles        <NA>       pear       apple       pear
4 Los Angeles    New York       apple       <NA>        <NA>       pear
5        <NA>        <NA>        <NA>       <NA>        <NA>       <NA>
6   Milwaukee    New York       apple       pear       apple       <NA>

数据说明 #

每一行代表一位旅客。例如第三行的旅客在纽约和洛杉矶中转，在纽约的第一站吃了梨，在洛杉矶的第二站吃了苹果和梨。

需求说明 #

1. 计算每个城市的中转总人数。例如纽约共有5人中转（2人在stop1，3人在stop2）；
2. 计算每个城市的苹果和梨食用数量，例如纽约有3个苹果、4个梨被食用。

期望输出 #

Location     NStops  NApples   NPears
Los Angeles  2       2         1
Milwaukee    2       2         2
New York     5       3         4 

尝试的代码及问题 #

library(tidyverse)
df %>% pivot_longer(c(stop1, stop2)) %>% 
  rename(Location = value, Stop = name) %>% 
  add_count(Location, name = "NStops") %>% 
  pivot_longer(c(stop1_apple, stop1_pear, stop2_apple, stop2_pear)) %>% 
  group_by(Location, value, NStops) %>% 
  summarise(NFruits = n()) %>% 
  pivot_wider(names_from = value, values_from = NFruits) %>% 
  rename(NApples = apple, NPears = pear) %>% 
  select(-`NA`) %>% 
  filter(!is.na(Location))

输出结果：

Location    NStops NApples NPears
Los Angeles      2       2      3
Milwaukee        2       4      3
New York         5       7      7

各城市中转人数统计正确，但水果食用数量不符合预期。实际场景中存在更多站点、水果种类和城市，需使用tidyverse解决方案。

优化解决方案（含替代数据适配） #

替代数据 #

df <- structure(list(Q57 = c("New York", "Milwaukee", "New York",
                               "Los Angeles", NA, "Milwaukee"),
                     Q60 = c(NA, "New York", "Los Angeles", 
                               "New York", NA, "New York"),
                     `Q58/apple` = c("apple", "apple", NA, "apple", NA, "apple"),
                     `Q58/pear` = c("pear", "pear", "pear", NA, NA, "pear"),
                     `Q61/apple` = c(NA, "apple", "apple", NA, NA, "apple"), 
                     `Q61/pear` = c(NA, "pear", "pear", "pear", NA, NA)),
                class = "data.frame", row.names = c(NA, -6L))

df
          Q57         Q60 Q58/apple Q58/pear Q61/apple Q61/pear
1    New York        <NA>     apple     pear      <NA>     <NA>
2   Milwaukee    New York     apple     pear     apple     pear
3    New York Los Angeles      <NA>     pear     apple     pear
4 Los Angeles    New York     apple     <NA>      <NA>     pear
5        <NA>        <NA>      <NA>     <NA>      <NA>     <NA>
6   Milwaukee    New York     apple     pear     apple     <NA>

适配后的解决方案代码 #

library(dplyr)
library(tidyr)
df %>% 
  # 将站点列重命名为带统一后缀的格式
  rename(
    `Q57/stop` = Q57, 
    `Q60/stop` = Q60
  ) %>% 
  # 通过正则匹配拆分列名，将同类型数据聚合到对应列
  pivot_longer(
    everything(), 
    names_pattern = "Q\\d+/(.*)", 
    names_to = ".value"
  ) %>% 
  # 按城市分组统计
  summarize(
    NStops = n(),
    NApples = sum(apple == "apple", na.rm = TRUE),
    NPears = sum(pear == "pear", na.rm = TRUE),
    .by = stop
  ) %>% 
  filter(!is.na(stop)) %>% 
  rename(Location = stop) %>% 
  arrange(Location)

输出结果：

Location    NStops NApples NPears
Los Angeles      2       2      1
Milwaukee        2       2      2
New York         5       3      4

方案说明 #

核心思路是通过统一列名格式+pivot_longer的.value参数，将站点信息和对应水果食用数据自动匹配关联，避免了原代码中因重复展开导致的水果计数错误，同时具备扩展性，可适配更多站点、水果种类的场景。

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