R语言宽表转长表：提取列字符串并生成二进制虚拟列

假设你的宽格式数据long1结构类似下面的示例（包含id、t1_p1到t4_p3的属性列、t1_choice到t4_choice列，以及需要转虚拟变量的原始列）：

# 构造示例数据
set.seed(123)
long1 <- tibble(
  id = 1:2,
  t1_p1 = sample(c("low_env", "mid_env", "high_env"), 2, replace = TRUE),
  t1_p2 = sample(c("low_env", "mid_env", "high_env"), 2, replace = TRUE),
  t1_p3 = sample(c("low_env", "mid_env", "high_env"), 2, replace = TRUE),
  t2_p1 = sample(c("low_env", "mid_env", "high_env"), 2, replace = TRUE),
  t2_p2 = sample(c("low_env", "mid_env", "high_env"), 2, replace = TRUE),
  t2_p3 = sample(c("low_env", "mid_env", "high_env"), 2, replace = TRUE),
  t3_p1 = sample(c("low_env", "mid_env", "high_env"), 2, replace = TRUE),
  t3_p2 = sample(c("low_env", "mid_env", "high_env"), 2, replace = TRUE),
  t3_p3 = sample(c("low_env", "mid_env", "high_env"), 2, replace = TRUE),
  t4_p1 = sample(c("low_env", "mid_env", "high_env"), 2, replace = TRUE),
  t4_p2 = sample(c("low_env", "mid_env", "high_env"), 2, replace = TRUE),
  t4_p3 = sample(c("low_env", "mid_env", "high_env"), 2, replace = TRUE),
  t1_choice = sample(1:3, 2, replace = TRUE),
  t2_choice = sample(1:3, 2, replace = TRUE),
  t3_choice = sample(1:3, 2, replace = TRUE),
  t4_choice = sample(1:3, 2, replace = TRUE)
)

接下来用tidyverse工具链完成转换：

library(tidyverse)

# 步骤1：拆分t和p的属性列，转为长格式
df_attr <- long1 %>%
  select(id, starts_with("t")) %>%
  select(-ends_with("choice")) %>%  # 排除choice列，先处理属性列
  pivot_longer(
    cols = -id,
    names_to = c("t", "p"),
    names_pattern = "t(\\d+)_p(\\d+)",  # 正则提取t和p的数字
    values_to = "env_type"
  ) %>%
  mutate(
    t = as.integer(t),
    p = as.integer(p)
  )

# 步骤2：处理choice列，转为二进制长格式
df_choice <- long1 %>%
  select(id, ends_with("choice")) %>%
  pivot_longer(
    cols = -id,
    names_to = "t",
    names_pattern = "t(\\d+)_choice",
    values_to = "selected_p"
  ) %>%
  mutate(
    t = as.integer(t),
    # 生成每个t对应的3个p的行，匹配selected_p标记choice=1
    p = list(1:3)
  ) %>%
  unnest(p) %>%
  mutate(choice = ifelse(p == selected_p, 1, 0)) %>%
  select(id, t, p, choice)

# 步骤3：合并属性和choice数据，生成虚拟变量
final_df <- df_attr %>%
  left_join(df_choice, by = c("id", "t", "p")) %>%
  # 生成指定虚拟列，这里以示例的3种类型为例，你可替换为实际9种类别
  mutate(
    low_env = ifelse(env_type == "low_env", 1, 0),
    mid_env = ifelse(env_type == "mid_env", 1, 0),
    high_env = ifelse(env_type == "high_env", 1, 0)
    # 剩余6种虚拟列按相同格式添加即可
  ) %>%
  # 若需一键生成所有类别虚拟列，可用fastDummies包：
  # fastDummies::dummy_cols(., select_columns = "env_type") %>%
  # select(-env_type)
  select(id, t, p, choice, low_env, mid_env, high_env)  # 按需调整列顺序

# 查看结果
head(final_df, 12)

关键步骤说明 #

• 步骤1：通过pivot_longer结合正则表达式拆分tN_pM列，精准提取t（时间点）和p（选项编号），同时保留对应属性值。
• 步骤2：先将choice列转长，再通过unnest生成每个时间点对应的3个选项行，根据原choice值标记choice=1或0，确保每个id对应12行（4个时间点×3个选项）。
• 步骤3：合并数据集后，通过mutate手动生成指定虚拟列，或用fastDummies包一键生成所有类别对应的虚拟列，满足9个虚拟列的需求。

适配调整提示 #

• 若时间点t的数量不是4，或选项p的数量不是3，只需修改p = list(1:3)中的数字范围，以及正则表达式的匹配规则。
• 确保id是样本的唯一标识，避免合并时出现数据错位。

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