如何批量转换Likert矩阵格式？（R语言dplyr实现）

我正在使用Likert包，该包要求Likert矩阵表采用特定格式。已完成Q9_1的格式转换，代码如下：

LikertQ9_1 <- SurveyClean2 |>
  dplyr::select(Q9_1) |>
  mutate(Question = "Grazing or Forage Production") |>
  group_by(Question, Q9_1) |>
  count() |>
  ungroup() |>
  pivot_wider(names_from = Q9_1, values_from = n)

但Q9_2至Q9_10也需要执行相同的格式转换，尝试用合并、左连接方法时出现格式混乱，求批量处理或正确合并的方法。

附数据框样本代码：

SurveyClean2 <- dplyr::tibble(
      Q9_1 = c("Not Interested", "Not Interested", "NA", "Slightly Interested", "NA"),
      Q9_2 = c("Extremely Interested", "Not Interested", "Somewhat Interested", "NA", "NA"),
      Q9_3 = c("Not Interested", "Extremely Interested", "Slightly Interested", "Somewhat Interested", "Not Interested"),
      Q9_4 = c("Not Interested", "Extremely Interested", "Slightly Interested", "Somewhat Interested", "Not Interested"), 
      Q9_5 = c("Slightly Interested", "Extremely Interested", "Slightly Interested", "Somewhat Interested", "Not Interested"),
      Q9_6 = c("Not Interested", "Extremely Interested", "Slightly Interested", "Somewhat Interested", "NA"),
      Q9_7 = c("Not Interested", "Extremely Interested", "Slightly Interested", "Extremely Interested", "NA"),
      Q9_8 = c("Not Interested", "Extremely Interested", "Somewhat Interested", "Somewhat Interested", "NA"),
      Q9_9 = c("NA", "Extremely Interested", "Slightly Interested", "Somewhat Interested", "NA"),
      Q9_10 = c("Not Interested", "Slightly Interested", "Slightly Interested", "Somewhat Interested", "NA"))

解决方案 #

使用tidyverse的流水线操作可批量处理所有Q9系列列，无需逐个转换后合并，步骤如下：

代码实现 #

library(tidyverse)

# 预定义每个Q9问题对应的文本（根据实际需求修改）
question_labels <- c(
  Q9_1 = "Grazing or Forage Production",
  Q9_2 = "问题2名称",
  Q9_3 = "问题3名称",
  Q9_4 = "问题4名称",
  Q9_5 = "问题5名称",
  Q9_6 = "问题6名称",
  Q9_7 = "问题7名称",
  Q9_8 = "问题8名称",
  Q9_9 = "问题9名称",
  Q9_10 = "问题10名称"
)

LikertQ9_all <- SurveyClean2 |>
  # 将所有Q9开头的宽列转为长格式
  pivot_longer(cols = starts_with("Q9_"), names_to = "QuestionID", values_to = "Response") |>
  # 将字符串"NA"转换为R原生缺失值，避免统计误差
  mutate(Response = ifelse(Response == "NA", NA, Response)) |>
  # 匹配问题文本
  mutate(Question = question_labels[QuestionID]) |>
  # 按问题和响应分组统计数量
  group_by(Question, Response) |>
  count() |>
  ungroup() |>
  # 转回宽格式，缺失的响应选项填充为0（适配Likert包要求）
  pivot_wider(names_from = Response, values_from = n, values_fill = 0)

关键逻辑说明 #

• pivot_longer：一次性将所有Q9列合并为两列，统一处理所有问题，避免重复代码
• 字符串"NA"转原生缺失值：原数据中的"NA"是字符型，需转为R认可的缺失值才能正确统计
• values_fill = 0：确保每个问题的所有响应选项都有对应列，缺失的选项填充0，符合Likert包的输入格式要求

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