如何在R语言中将空缺原因列转换为多列宽表格式

你需要先给每个school_id+subject组内的空缺原因生成序号，再用pivot_wider完成宽表转换，具体步骤如下：

1. 加载依赖包：使用tidyverse集合包（包含dplyr用于分组、tidyr用于数据重塑）
2. 生成组内序号：按school_id和subject分组，为每个原因添加从1开始的序号，并格式化为Reason1、Reason2的形式
3. 转换为宽表：通过pivot_wider将长表转为宽表，自动补全不足4个原因的位置为NA

完整代码 #

library(tidyverse)

# 修正语法错误后的原始数据
data <- data.frame(school_id = c("1", "1", "2", "3", "3"), 
                   subject = c("English", "English",  "Maths", "French", "English"),
                   vacancies = c(40, 40, 20, 40, 10),
                   Reason = c("Pay", "Location", "Pay", "Experience", "Location"))

# 数据转换
wide_data <- data %>%
  group_by(school_id, subject, vacancies) %>%
  mutate(reason_num = paste0("Reason", row_number())) %>%
  ungroup() %>%
  pivot_wider(
    id_cols = c(school_id, subject, vacancies),
    names_from = reason_num,
    values_from = Reason,
    values_fill = NA,
    names_expand = TRUE,
    names_glue = "{reason_num}"
  )

# 查看结果
print(wide_data)

输出结果 #

# A tibble: 4 × 7
  school_id subject vacancies Reason1  Reason2  Reason3 Reason4
  <chr>     <chr>       <dbl> <chr>    <chr>    <lgl>   <lgl>  
1 1         English        40 Pay      Location NA      NA     
2 2         Maths          20 Pay      NA       NA      NA     
3 3         French         40 Experience NA       NA      NA     
4 3         English        10 Location NA       NA      NA     

关键说明 #

• 分组时必须包含vacancies，因为同一学校科目下的空缺数可能不同
• row_number()用于生成组内的原因序号，配合paste0生成目标列名
• values_fill = NA确保没有对应原因的列填充为缺失值，names_expand保证即使组内不足4个原因，也会生成Reason1到Reason4的所有列

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