如何用pivot_longer()转换多列R数据？现有代码无法生成目标结果

原始数据框 #

df <- data.frame(year = c("2001", "2001", "2001", "2002", "2002", "2002", 
                          "2003", "2003", "2003", "2004", "2004", "2004"), 
             city = c("Rome", "Rome", "Rome", "London", "London", "London", 
                      "New York", "New York", "New York", "Paris", "Paris", "Paris"),
             position_1 = c("first", "first", "first", "second", "second", "second", 
                            "third", "third", "third", "fourth", "fourth", "fourth"),
             position_2 = c("fifth", "fifth", "fifth", "sixth", "sixth", "sixth", 
                            "seventh", "seventh", "seventh", "eighth", "eighth", "eighth"), 
             position_3 = c("ninth", "ninth", "ninth", "eleventh", "eleventh", "eleventh", 
                            "twelfth", "twelfth", "twelfth", "thirteenth", "thirteenth", "thirteenth"))

需求 #

将上述宽格式数据转换为包含year、city、new_position（对应原position_1/2/3列名）及对应位置值的长格式表格。

问题代码 #

尝试了以下代码但未得到预期结果：

df <- df %>% 
  pivot_longer(cols = position_1:position_3,
               names_to = "new_position")

正确实现方法 #

问题出在缺少values_to参数，该参数用于指定存储原位置列值的新列名。此外原数据存在重复行，可按需去重：

1. 保留所有原始行的转换 #

library(tidyr)
library(dplyr)

df_long <- df %>%
  pivot_longer(
    cols = position_1:position_3, # 选择需要转换的列
    names_to = "new_position",    # 原列名存入该列
    values_to = "position_value"  # 原列的值存入该列
  )

2. 去除冗余重复行后的转换 #

原数据中每个year+city组合有3条完全重复的行，转换后会产生冗余，先用distinct()去重再转换：

df_long_unique <- df %>%
  distinct() %>% # 去除完全重复的行
  pivot_longer(
    cols = position_1:position_3,
    names_to = "new_position",
    values_to = "position_value"
  )

结果说明 #

转换后的长格式表格结构示例（以去重后的结果为例）：

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