R语言：如何使用pivot_wider重组长格式调查数据集？

问题描述 #

现有一份长格式调查数据集，包含2个国家（奥地利、法国）、3个子群体（child、teenager、adult）、2个问题及对应答案，需要转换为指定的宽格式。尝试过reshape2::dcast和tidyr::spread但操作出错。

原始长格式数据 #

country   subset                      Questions          Answers
1  Austria    child            Do you feel lonely?            agree
2  Austria    child            Do you feel lonely?         disagree
3  Austria    child            Do you feel lonely?    totally agree
4  Austria teenager            Do you feel lonely?       don't know
5  Austria teenager            Do you feel lonely?         disagree
6  Austria teenager            Do you feel lonely? totally disagree
7  Austria    adult            Do you feel lonely?            agree
8  Austria    adult            Do you feel lonely?         disagree
9  Austria    adult            Do you feel lonely?    totally agree
10  France    child            Do you feel lonely?       don't know
11  France    child            Do you feel lonely?         disagree
12  France    child            Do you feel lonely? totally disagree
13  France teenager            Do you feel lonely?            agree
14  France teenager            Do you feel lonely?         disagree
15  France teenager            Do you feel lonely?    totally agree
16  France    adult            Do you feel lonely?       don't know
17  France    adult            Do you feel lonely?         disagree
18  France    adult            Do you feel lonely? totally disagree
19 Austria    child Do you prefer staying at home?            agree
20 Austria    child Do you prefer staying at home?         disagree
21 Austria    child Do you prefer staying at home?    totally agree
22 Austria teenager Do you prefer staying at home?       don't know
23 Austria teenager Do you prefer staying at home?         disagree
24 Austria teenager Do you prefer staying at home? totally disagree
25 Austria    adult Do you prefer staying at home?            agree
26 Austria    adult Do you prefer staying at home?         disagree
27 Austria    adult Do you prefer staying at home?    totally agree
28  France    child Do you prefer staying at home?       don't know
29  France    child Do you prefer staying at home?         disagree
30  France    child Do you prefer staying at home? totally disagree
31  France teenager Do you prefer staying at home?            agree
32  France teenager Do you prefer staying at home?         disagree
33  France teenager Do you prefer staying at home?    totally agree
34  France    adult Do you prefer staying at home?       don't know
35  France    adult Do you prefer staying at home?         disagree
36  France    adult Do you prefer staying at home? totally disagree

目标宽格式数据 #

country   subset Do_you_feel_lonely Do_you_prefer_staying_at_home
1  Austria    child              agree                         agree
2  Austria    child           disagree                      disagree
3  Austria    child      totally agree                 totally agree
4  Austria teenager         don't know                    don't know
5  Austria teenager           disagree                      disagree
6  Austria teenager   totally disagree              totally disagree
7  Austria    adult              agree                         agree
8  Austria    adult           disagree                      disagree
9  Austria    adult      totally agree                 totally agree
10  France    child         don't know                    don't know
11  France    child           disagree                      disagree
12  France    child   totally disagree              totally disagree
13  France teenager              agree                         agree
14  France teenager           disagree                      disagree
15  France teenager      totally agree                 totally agree
16  France    adult         don't know                    don't know
17  France    adult           disagree                      disagree
18  France    adult   totally disagree              totally disagree

解决方案 #

方法1：使用tidyr包的pivot_wider（推荐） #

pivot_wider是tidyr新版本中替代spread的函数，功能更灵活。核心是先给每组内的记录添加唯一标识，确保转宽时答案正确匹配，再处理问题列的格式。

library(tidyr)
library(dplyr)

# 读取原始数据（若数据已存在，可跳过此步）
df <- read.table(text = "country   subset                      Questions          Answers
1  Austria    child            Do you feel lonely?            agree
2  Austria    child            Do you feel lonely?         disagree
3  Austria    child            Do you feel lonely?    totally agree
4  Austria teenager            Do you feel lonely?       don't know
5  Austria teenager            Do you feel lonely?         disagree
6  Austria teenager            Do you feel lonely? totally disagree
7  Austria    adult            Do you feel lonely?            agree
8  Austria    adult            Do you feel lonely?         disagree
9  Austria    adult            Do you feel lonely?    totally agree
10  France    child            Do you feel lonely?       don't know
11  France    child            Do you feel lonely?         disagree
12  France    child            Do you feel lonely? totally disagree
13  France teenager            Do you feel lonely?            agree
14  France teenager            Do you feel lonely?         disagree
15  France teenager            Do you feel lonely?    totally agree
16  France    adult            Do you feel lonely?       don't know
17  France    adult            Do you feel lonely?         disagree
18  France    adult            Do you feel lonely? totally disagree
19 Austria    child Do you prefer staying at home?            agree
20 Austria    child Do you prefer staying at home?         disagree
21 Austria    child Do you prefer staying at home?    totally agree
22 Austria teenager Do you prefer staying at home?       don't know
23 Austria teenager Do you prefer staying at home?         disagree
24 Austria teenager Do you prefer staying at home? totally disagree
25 Austria    adult Do you prefer staying at home?            agree
26 Austria    adult Do you prefer staying at home?         disagree
27 Austria    adult Do you prefer staying at home?    totally agree
28  France    child Do you prefer staying at home?       don't know
29  France    child Do you prefer staying at home?         disagree
30  France    child Do you prefer staying at home? totally disagree
31  France teenager Do you prefer staying at home?            agree
32  France teenager Do you prefer staying at home?         disagree
33  France teenager Do you prefer staying at home?    totally agree
34  France    adult Do you prefer staying at home?       don't know
35  France    adult Do you prefer staying at home?         disagree
36  France    adult Do you prefer staying at home? totally disagree", header = TRUE, stringsAsFactors = FALSE)

# 1. 给每个country+subset+Questions组内添加行号，确保转宽时答案对应正确
df <- df %>%
  group_by(country, subset, Questions) %>%
  mutate(row_id = row_number()) %>%
  ungroup()

# 2. 转换问题列的格式：去掉问号，空格替换为下划线
df$Questions <- gsub("\\?", "", df$Questions)
df$Questions <- gsub(" ", "_", df$Questions)

# 3. 转宽格式
wide_df <- df %>%
  pivot_wider(
    id_cols = c(country, subset, row_id),
    names_from = Questions,
    values_from = Answers
  ) %>%
  select(-row_id) # 移除临时行号

# 查看结果
print(wide_df)

方法2：使用reshape2包的dcast #

如果习惯使用reshape2，同样需要先处理行号和列名，再进行转换：

library(reshape2)
library(dplyr)

# 读取数据（若数据已存在，可跳过此步）
df <- read.table(text = "country   subset                      Questions          Answers
1  Austria    child            Do you feel lonely?            agree
2  Austria    child            Do you feel lonely?         disagree
3  Austria    child            Do you feel lonely?    totally agree
4  Austria teenager            Do you feel lonely?       don't know
5  Austria teenager            Do you feel lonely?         disagree
6  Austria teenager            Do you feel lonely? totally disagree
7  Austria    adult            Do you feel lonely?            agree
8  Austria    adult            Do you feel lonely?         disagree
9  Austria    adult            Do you feel lonely?    totally agree
10  France    child            Do you feel lonely?       don't know
11  France    child            Do you feel lonely?         disagree
12  France    child            Do you feel lonely? totally disagree
13  France teenager            Do you feel lonely?            agree
14  France teenager            Do you feel lonely?         disagree
15  France teenager            Do you feel lonely?    totally agree
16  France    adult            Do you feel lonely?       don't know
17  France    adult            Do you feel lonely?         disagree
18  France    adult            Do you feel lonely? totally disagree
19 Austria    child Do you prefer staying at home?            agree
20 Austria    child Do you prefer staying at home?         disagree
21 Austria    child Do you prefer staying at home?    totally agree
22 Austria teenager Do you prefer staying at home?       don't know
23 Austria teenager Do you prefer staying at home?         disagree
24 Austria teenager Do you prefer staying at home? totally disagree
25 Austria    adult Do you prefer staying at home?            agree
26 Austria    adult Do you prefer staying at home?         disagree
27 Austria    adult Do you prefer staying at home?    totally agree
28  France    child Do you prefer staying at home?       don't know
29  France    child Do you prefer staying at home?         disagree
30  France    child Do you prefer staying at home? totally disagree
31  France teenager Do you prefer staying at home?            agree
32  France teenager Do you prefer staying at home?         disagree
33  France teenager Do you prefer staying at home?    totally agree
34  France    adult Do you prefer staying at home?       don't know
35  France    adult Do you prefer staying at home?         disagree
36  France    adult Do you prefer staying at home? totally disagree", header = TRUE, stringsAsFactors = FALSE)

# 1. 添加组内行号
df <- df %>%
  group_by(country, subset, Questions) %>%
  mutate(row_id = row_number()) %>%
  ungroup()

# 2. 处理问题列格式
df$Questions <- gsub("\\?", "", df$Questions)
df$Questions <- gsub(" ", "_", df$Questions)

# 3. 转宽格式
wide_df <- dcast(df, country + subset + row_id ~ Questions, value.var = "Answers")
wide_df <- wide_df[, -which(names(wide_df) == "row_id")]

# 查看结果
print(wide_df)

常见出错原因 #

之前操作失败大概率是两个原因：

• 没有给每个country+subset+Questions组内的记录添加唯一标识（row_id），导致转宽时无法正确匹配同一组内的对应答案；
• 未处理问题列的格式，生成的列名不符合目标要求。

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