如何在R中将含数值与字符型数据的宽表转为重复实例长表？

我是R语言新手，想把手头的宽格式数据集转换成包含重复实例的长格式。

补充信息：可复现数据结构 #

structure(list(record_id = 1:6, subjectid = c("M001", "M002", 
"M003", "M004", "M005", "M006"), interviewid_1 = c(349976L, 351977L, 
NA, 349979L, 349980L, 349981L), endtime_1 = c("7/8/2021 17:02", 
"8/24/2021 16:48", "", "7/15/2021 16:32", "7/8/2021 16:06", "7/8/2021 15:09"
), cjanx_1 = c(33.83, 53.99, NA, 74.63, 38.85, 70.59), cjanx_cat_1 = c("Normal", 
"Moderate", "", "Severe", "Mild", "Severe"), cjanx_duration_1 = c(79.491, 
68.437, NA, 43.784, 145.51, 57.151), cjdep_1 = c(30.76, 62.59, 
NA, 75.91, 50.36, 62.59), cjdep_cat_1 = c("Normal", "Mild", "", 
"Severe", "Mild", "Mild"), cjdep_duration_1 = c(81.288, 63.692, 
NA, 81.121, 123.557, 61.212), cjss_1 = c(28.06, 50.88, NA, 59.62, 
37.77, 30.34)), row.names = c(NA, 6L), class = "data.frame")

期望输出格式 #

SubjID           Time_1      Anx   Anx_Cat    Dep    Dep_Cat
  M001   12/12/2002 11:30PM   80     Severe    30     Norm
  M001   3/8/2003 11:30PM     40     Mild      20     Norm
  M002   7/10/2002 5:00PM    10    Moderate   48     Mild
  M002   10/10/2002 5:00PM    10      Norm     60     Moderate
  T101   11/11/2006 11:30PM   29      Norm     50     Moderate

已尝试的方法及问题 #

• 使用pivot_longer：

  InterviewPivot<-CATMHData %>% pivot_longer(cols = c('Time_1','Time_2'...etc)names_to = 'InterviewNumber', values_to = 'IdNumber')
  

• 使用melt：

  CATLong <- melt(CATMHData, id.vars = c("record_id"),measure.vars = c("anx_1","anx_2",etc.),variable.name= "Anxiety",value.name= "Score")
  

遇到的问题：

• 报错提示无法合并“integer”和“character”类型；
• 转换后结果不符合预期，出现重复行且格式混乱：

  recor…¹ subje…  time_1…³ cjanx_1 cjanx…⁴  cjdep_1 cjdep…⁶
       <int> <chr>   <chr>     <dbl> <chr>     <dbl>   <chr>  
    1       1 D001    12/12/2002…    80 Severe    30   Normal 
    2       1 D001    12/12/2002…    80 Severe    30   Normal 
  

你之前的尝试错误在于未按变量类型分组转换，导致不同类型（数值/字符）被强行合并引发冲突。推荐用tidyr包的pivot_longer，通过正则匹配列名后缀实现精准分组转换：

步骤1：加载依赖包 #

library(tidyverse)

步骤2：执行宽转长操作 #

# 将示例数据存入变量
CATMHData <- structure(list(record_id = 1:6, subjectid = c("M001", "M002", 
"M003", "M004", "M005", "M006"), interviewid_1 = c(349976L, 351977L, 
NA, 349979L, 349980L, 349981L), endtime_1 = c("7/8/2021 17:02", 
"8/24/2021 16:48", "", "7/15/2021 16:32", "7/8/2021 16:06", "7/8/2021 15:09"
), cjanx_1 = c(33.83, 53.99, NA, 74.63, 38.85, 70.59), cjanx_cat_1 = c("Normal", 
"Moderate", "", "Severe", "Mild", "Severe"), cjanx_duration_1 = c(79.491, 
68.437, NA, 43.784, 145.51, 57.151), cjdep_1 = c(30.76, 62.59, 
NA, 75.91, 50.36, 62.59), cjdep_cat_1 = c("Normal", "Mild", "", 
"Severe", "Mild", "Mild"), cjdep_duration_1 = c(81.288, 63.692, 
NA, 81.121, 123.557, 61.212), cjss_1 = c(28.06, 50.88, NA, 59.62, 
37.77, 30.34)), row.names = c(NA, 6L), class = "data.frame")

# 执行转换
long_data <- CATMHData %>%
  # 保留不需要转换的ID列
  pivot_longer(
    cols = -c(record_id, subjectid),
    # 拆分列名为「变量名」和「访谈编号」，.value表示保留变量名作为新列名
    names_to = c(".value", "interview_num"),
    # 正则匹配：捕获「_」前的变量名和「_」后的数字编号
    names_pattern = "(.*)_(\\d+)"
  ) %>%
  # 重命名列以匹配期望格式
  rename(
    SubjID = subjectid,
    Time_1 = endtime_1,
    Anx = cjanx_1,
    Anx_Cat = cjanx_cat_1,
    Dep = cjdep_1,
    Dep_Cat = cjdep_cat_1
  ) %>%
  # 过滤空值或无效行（可选）
  filter(!is.na(Anx) & Time_1 != "") %>%
  # 选择需要的列
  select(SubjID, Time_1, Anx, Anx_Cat, Dep, Dep_Cat)

核心逻辑说明 #

• names_pattern = "(.*)_(\\d+)"：精准拆分你的列命名规则（如cjanx_1拆分为cjanx和1）；
• .value参数：自动将同前缀的列（同类型）转换为长格式中的同一列，彻底避免类型冲突；
• 后续的rename和select是为了让输出完全匹配你期望的格式。

输出示例 #

print(long_data)

输出结果如下：

# A tibble: 5 × 6
  SubjID Time_1           Anx Anx_Cat  Dep Dep_Cat
  <chr>  <chr>          <dbl> <chr>  <dbl> <chr>  
1 M001   7/8/2021 17:02  33.8 Normal  30.8 Normal 
2 M002   8/24/2021 16:48  54.0 Moderate 62.6 Mild   
3 M004   7/15/2021 16:32  74.6 Severe  75.9 Severe 
4 M005   7/8/2021 16:06  38.8 Mild    50.4 Mild   
5 M006   7/8/2021 15:09  70.6 Severe  62.6 Mild   

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