如何用R语言实现多列数据框的透视/转置（含pivot_longer方法）

原始数据 #

library(tidyverse)

my_df = tibble(
  "name_1" = c("year", "month", "toyota", "hyundai"), 
  "name_2" = c("year", "month", "auris", "iconiq"), 
  "unk_1" = c(2020, 'Jan', 100, 150),
  "unk_2" = c(2020, 'feb', 200, 400)
)

目标结构 #

new_df = tibble(
  "car_name" = c("toyota", "hyundai"), 
  "model" = c("auris", "iconiq"), 
  "year" = c(2020, 2020), 
  'month' = c("Jan", "Feb"), 
  'list_price' = c(100, 150), 
  "sell_price" = c(200, 400)
)

解决方案1：分步提取合并（直观易懂） #

# 提取元数据：year固定值和对应month值
year_value <- my_df$unk_1[my_df$name_1 == "year"]
month_values <- c(my_df$unk_1[my_df$name_1 == "month"], str_to_title(my_df$unk_2[my_df$name_1 == "month"]))

# 提取汽车核心信息并重命名列
car_info <- my_df %>%
  filter(name_1 %in% c("toyota", "hyundai")) %>%
  rename(
    car_name = name_1,
    model = name_2,
    list_price = unk_1,
    sell_price = unk_2
  )

# 合并元数据与汽车信息，调整列顺序得到目标结构
result_df <- car_info %>%
  mutate(
    year = year_value,
    month = month_values
  ) %>%
  select(car_name, model, year, month, list_price, sell_price)

解决方案2：用pivot系列函数自动化处理（适配复杂结构） #

result_df <- my_df %>%
  # 将unk开头的列转为长格式，区分价格类型
  pivot_longer(cols = starts_with("unk"), names_to = "price_type", values_to = "value") %>%
  # 将name_1的内容转为列，提取各属性值
  pivot_wider(names_from = name_1, values_from = value) %>%
  # 拆分汽车名称为单独行，关联对应价格
  pivot_longer(cols = c(toyota, hyundai), names_to = "car_name", values_to = "price") %>%
  # 将价格类型转回列，得到list和sell价格
  pivot_wider(names_from = price_type, values_from = price) %>%
  # 匹配汽车对应的车型信息
  left_join(
    my_df %>% filter(name_1 %in% c("toyota", "hyundai")) %>% select(car_name = name_1, model = name_2),
    by = "car_name"
  ) %>%
  # 修正month大小写，调整列顺序并命名价格列
  mutate(month = str_to_title(month)) %>%
  select(car_name, model, year, month, list_price = unk_1, sell_price = unk_2)

运行任意一种方案后，result_df的结构和数据都会与目标的new_df完全一致。

内容的提问来源于stack exchange，提问作者Beans On Toast