R语言中对两组列执行pivot_longer的正确实现方法咨询

给定以下使用tidyverse创建的数据集：

library(tidyverse)

df <- data.frame(names = c("John", "Peter", "Mary", "Allan", "Anne"),
                 year = 2001:2005,
                 first_class = letters[1:5],
                 second_class = c("NA", "c", "d", "NA", "e"),
                 third_class = c(letters[1:3], "NA", "NA"),
                 first_day = c("one", "two", "NA", "two", "one"),
                 second_day = c("two", "NA", "one", "one", "two"),
                 third_day = c("NA", "one", "two", "NA", "two"))

需要对first_class:third_class和first_day:third_day这两组列执行pivot_longer操作，尝试了以下代码：

df |>
  pivot_longer(cols = first_class:third_class,
               values_to = "my_class",
               names_to = NULL) |>
  pivot_longer(cols = first_day:third_day,
               values_to = "my_day",
               names_to = NULL)

但输出出现信息重复（两次pivot生成笛卡尔积导致45行结果）：

# A tibble: 45 × 4
   names  year my_class my_day
   <chr> <int> <chr>    <chr> 
 1 John   2001 a        one   
 2 John   2001 a        two   
 3 John   2001 a        NA    
 4 John   2001 NA       one   
 5 John   2001 NA       two   
 6 John   2001 NA       NA    
 7 John   2001 a        one   
 8 John   2001 a        two   
 9 John   2001 a        NA    
10 Peter  2002 b        two  

预期结果是按first/second/third的对应关系匹配class和day：

names year my_class my_day
1  John 2001        a    one
2  John 2001       NA    two
3  John 2001        a     NA
4 Peter 2002        b    two
5 Peter 2002        c     NA
6 Peter 2002        b    one

两次独立的pivot_longer会生成笛卡尔积（每个class对应所有day），正确做法是一次pivot_longer同时处理两组列，通过提取列名中的共同前缀关联class和day：

df |>
  pivot_longer(
    cols = starts_with(c("first_", "second_", "third_")),
    names_to = c("group", ".value"),
    names_sep = "_"
  ) |>
  rename(my_class = class, my_day = day)

代码说明： #

• cols = starts_with(c("first_", "second_", "third_"))：选中所有以first_/second_/third_开头的目标列
• names_to = c("group", ".value")：按_分割列名，第一部分（first/second/third）存入group列，第二部分（class/day）作为新的列名（.value表示这部分是值的类型）
• rename：将自动生成的class和day列重命名为需求的my_class和my_day

执行后得到的结果与预期一致，若不需要group列，可在管道末尾添加select(-group)：

df |>
  pivot_longer(
    cols = starts_with(c("first_", "second_", "third_")),
    names_to = c("group", ".value"),
    names_sep = "_"
  ) |>
  rename(my_class = class, my_day = day) |>
  select(-group)

内容的提问来源于stack exchange，提问作者always.learning