多列genre频次统计并按year分组的实现问题

给定如下电影数据集，包含年份列year和三个类型列genre1、genre2、genre3：

genre_structure<-structure(
  list(
    year = c(
      "2008",
      "2003",
      "2010",
      "2001",
      "2002",
      "1999",
      "1980",
      "2020",
      "1977",
      "1991",
      "1954",
      "2022",
      "1962",
      "2000",
      "1994",
      "2019",
      "2019",
      "1981",
      "2012",
      "2003"
    ),
    genre1 = c(
      "Action",
      "Action",
      "Action",
      "Action",
      "Action",
      "Action",
      "Action",
      "Action",
      "Action",
      "Action",
      "Action",
      "Action",
      "Action",
      "Action",
      "Action",
      "Action",
      "Action",
      "Action",
      "Action",
      "Action"
    ),
    genre2 = c(
      "Crime",
      "Adventure",
      "Adventure",
      "Adventure",
      "Adventure",
      "SciFi",
      "Adventure",
      "Drama",
      "Adventure",
      "SciFi",
      "Drama",
      "Drama",
      "Drama",
      "Adventure",
      "Crime",
      "Adventure",
      "Adventure",
      "Adventure",
      "Drama",
      "Drama"
    ),
    genre3 = c(
      "Drama",
      "Drama",
      "SciFi",
      "Drama",
      "Drama",
      "",
      "Fantasy",
      "",
      "Fantasy",
      "",
      "",
      "Mystery",
      "Mystery",
      "Drama",
      "Drama",
      "Crime",
      "Drama",
      "",
      "",
      "Mystery"
    )
  ),
  row.names = c(NA,-20L),
  class =  "data.frame"
  )

需要统计每年每个类型的出现次数，预期结果格式如下：

genre | year| count
Action |2008| 1
Comedy | 2008 | 3
Drama | 2008 | 4
...

原尝试代码无法得到预期结果，它会按多类型组合+年份分组，导致年份随类型组合重复出现：

genre_years_test<-genre_structure %>% 
  group_by(genre1, genre2, genre3, year) %>% 
  summarise(total=n(), .groups = "drop")

核心思路是先将宽格式的多类型列转换为长格式，再按genre和year分组统计：

完整实现代码 #

library(dplyr)
library(tidyr)

genre_year_count <- genre_structure %>%
  # 将三个genre列合并为一列，保留year信息
  pivot_longer(cols = starts_with("genre"), 
               names_to = "genre_col", 
               values_to = "genre") %>%
  # 过滤空的类型值
  filter(genre != "") %>%
  # 按类型和年份分组统计次数
  group_by(genre, year) %>%
  summarise(count = n(), .groups = "drop")

代码解释 #

1. pivot_longer：把genre1/genre2/genre3三列转为一列genre，每一行对应一个年份+单个类型的记录，解决原代码中多类型组合分组的问题。
2. filter(genre != "")：剔除数据中空的类型条目，避免无效统计。
3. group_by(genre, year) + summarise：按类型和年份分组，统计每个组合的出现次数，得到预期的三列结果。

执行后得到的部分结果示例：

# A tibble: 28 × 3
   genre     year  count
   <chr>     <chr> <int>
 1 Action    1954      1
 2 Action    1962      1
 3 Action    1977      1
 4 Action    1980      1
 5 Action    1981      1
# ℹ 23 more rows

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