如何按ID重塑DataFrame，将问题变量转为新列存储答案？

原始数据集 #

目标数据结构 #

尝试的代码及问题 #

• 最初尝试代码（报错“无此列”）：

  df2 <- pivot_wider(df1, timevar = "Question", times = c("Favourite colour?","Favourite food?","Favourite film?"), idvar = c("ID", "Name", "Location"), direction = "wide")
  

  问题：pivot_wider不支持timevar/times/direction参数，这些是旧版reshape()函数的参数，调用方式错误。

• 写法A（收到重复值警告）：

  df %>% pivot_wider(id_cols = c("ID", "Name","Location"), names_from = "Question", values_from = "Answer")
  

  警告信息：

  Warning message:
  Values from Original.string.value are not uniquely identified; output will contain list-cols.
  • Use values_fn = list to suppress this warning.
  • Use values_fn = {summary_fun} to summarise duplicates.
  • Use the following dplyr code to identify duplicates.
  {data} |> dplyr::summarise(n = dplyr::n(), .by = c(Number, Name, Vendor, Confirmed.Risk.Tier,Question)) |> dplyr::filter(n > 1L)
  问题：实际数据中存在同一ID+Name+Location+Question组合对应多个Answer的情况，导致无法直接生成常规列，只能生成列表列。

• 写法B（无报错但数据未“显式”变化）：

  pivot_wider(df,id_cols = c("ID", "Name", "Location"),names_from = "Question",values_from = "Answer", values_fn = list)
  

  问题：values_fn = list会将每个单元格转为列表类型，表面看数据结构类似长格式，但实际列已转为宽格式的列表列，需进一步处理才能看到常规宽表结构。

• @Phil建议的代码（仍收到重复值警告）：

  pivot_wider(df, names_from = Question, values_from = Answer)
  

  警告信息：

  Warning message:
  Values from Answer are not uniquely identified;output will contain list-cols.
  • Use values_fn = list to suppress this warning.
  • Use values_fn = {summary_fun} to summarise duplicates.
  • Use the following dplyr code to identify duplicates.
  {data} |> dplyr::summarise(n = dplyr::n(), .by = c(Number, Name,Location, Question)) |> dplyr::filter(n > 1L)
  问题：未指定id_cols，且重复值问题未解决。

解决方案 #

1. 先排查重复行 #

首先确认是否存在同一参与者同一问题的重复回答，执行以下代码：

library(dplyr)
df %>%
  summarise(n = n(), .by = c(ID, Name, Location, Question)) %>%
  filter(n > 1)

如果输出非空，说明存在重复行，需先处理重复。

2. 处理重复并转宽 #

情况1：无重复行（如示例数据）

直接使用正确的pivot_wider语法：

library(tidyr)
library(dplyr)

df_wide <- df %>%
  pivot_wider(
    id_cols = c(ID, Name, Location),
    names_from = Question,
    values_from = Answer
  )

运行后即可得到目标宽表结构。

情况2：存在重复行

根据需求选择重复值处理方式：

• 保留每个问题的第一个回答：

  df_wide <- df %>%
    pivot_wider(
      id_cols = c(ID, Name, Location),
      names_from = Question,
      values_from = Answer,
      values_fn = first
    )
  

• 拼接同一问题的所有回答：

  df_wide <- df %>%
    pivot_wider(
      id_cols = c(ID, Name, Location),
      names_from = Question,
      values_from = Answer,
      values_fn = ~paste(., collapse = ", ")
    )
  

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