如何将R语言宽格式数据集转换为含二进制choice列的长格式

需求说明 #

• 将宽格式数据转换为长格式，每个id按t1-t4分组，每组生成3行记录
• 基于原行的选择值生成二进制choice列：原选择值为1时对应组第一行choice=1，值为2时对应组第二行choice=1，值为3时对应组第三行choice=1

初始宽格式数据 #

long5<-structure(list(id = c("R_88j7lG37gLfxk22", "R_6DK8lERVf8lSQf4", 
"R_eG8g4wMm8JsqNlI", "R_9TCgsW0sLA4xHOm", "R_6J5Obu2AvpCeu9w"
), .choice.t1 = c("2", "3", "3", "2", "1"), .choice.t2 = c("1", 
"3", "1", "3", "1"), .choice.t3 = c("1", "2", "2", "2", "3"), 
    .choice.t4 = c("2", "1", "3", "2", "1")), row.names = c(NA, 
-5L), class = c("tbl_df", "tbl", "data.frame"))

原代码问题分析 #

原代码通过多次reshape和model.matrix操作实现，逻辑冗余且未直接匹配需求，导致转换失败。

解决方案1：tidyverse工具链实现 #

library(tidyverse)

# 宽转长，提取时间标识和选择值
long_data <- long5 %>%
  pivot_longer(cols = starts_with(".choice."),
               names_to = "time",
               names_prefix = ".choice.",
               values_to = "selected") %>%
  mutate(selected = as.integer(selected))

# 生成每个id-time的3个选项行，创建choice列
final_data <- long_data %>%
  group_by(id, time) %>%
  expand_grid(option = 1:3) %>%
  mutate(choice = if_else(option == selected, 1, 0)) %>%
  ungroup() %>%
  select(id, time, option, choice)

解决方案2：base R实现 #

# 宽转长
long_base <- reshape(long5, 
                    varying = grep("\\.choice\\.", names(long5)),
                    v.names = "selected",
                    timevar = "time",
                    times = sub("\\.choice\\.", "", names(long5)[-1]),
                    direction = "long")
long_base$selected <- as.integer(long_base$selected)
row.names(long_base) <- NULL

# 生成二进制choice列
final_base <- do.call(rbind, lapply(split(long_base, list(long_base$id, long_base$time)), function(x) {
  data.frame(id = x$id,
             time = x$time,
             option = 1:3,
             choice = as.integer(1:3 == x$selected))
}))
row.names(final_base) <- NULL

结果示例 #

以第一个id的t1分组为例，转换后结果如下：

# A tibble: 3 × 4
  id                  time  option choice
  <chr>               <chr>  <int>  <dbl>
1 R_88j7lG37gLfxk22   t1         1      0
2 R_88j7lG37gLfxk22   t1         2      1
3 R_88j7lG37gLfxk22   t1         3      0

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