在R中从非结构化数据集提取ID、项目及时间信息的通用实现

需求说明 #

需要从非结构化的数据集里提取学生ID、Item编号和对应的时间数据：

• 当第一列Text_1出现Item #: Time in seconds格式的内容时，提取对应学生列（如School.2、School.4）的数值
• 实际数据集包含多列学生数据，需要通用化的实现方式

示例数据集 #

df <- data.frame(Text_1 = c("Scoring", "1 = Incorrect","Text1","Text2","Text3","Text4", "Demo 1: Color Naming","Amarillo","Azul","Verde","Azul",
                            "Demo 1: Errors","Item 1: Color naming","Amarillo","Azul","Verde","Azul",
                            "Item 1: Time in seconds","Item 1: Errors",
                            "Item 2: Shape Naming","Cuadrado/Cuadro","Cuadrado/Cuadro","Círculo","Estrella","Círculo","Triángulo",
                            "Item 2: Time in seconds","Item 2: Errors"),
                  School.2 = c("Teacher:","DC Name:","Date (mm/dd/yyyy):","Child Grade:","Student Study ID:",NA, NA,NA,NA,NA,NA,
                             0,"1 = Incorrect responses",0,1,NA,NA,NA,0,"1 = Incorrect responses",0,NA,NA,1,1,0,NA,0),
                 X_Elementary_School..3 = c("Bill:","X District","10/7/21","K","123-2222-2:",NA, NA,NA,NA,NA,NA,
                               NA,"Child response",NA,NA,NA,NA,NA,NA,"Child response",NA,NA,NA,NA,NA,NA,NA,NA),
                 School.4 = c("Teacher:","DC Name:","Date (mm/dd/yyyy):","Child Grade:","Student Study ID:",NA, 0,NA,1,NA,NA,0,"1 = Incorrect responses",0,1,NA,NA,120,0,"1 = Incorrect responses",NA,1,0,1,NA,1,110,0),
                 Y_Elementary_School..2 = c("John:","X District","11/7/21","K","112-1111-3:",NA, NA,NA,NA,NA,NA,NA,"Child response",NA,NA,NA,NA,NA,NA,"Child response",NA,NA,NA,NA,NA,NA, NA,NA))

数据集结构预览：

> df
                    Text_1                School.2 X_Elementary_School..3                School.4 Y_Elementary_School..2
1                  Scoring                Teacher:                  Bill:                Teacher:                  John:
2            1 = Incorrect                DC Name:             X District                DC Name:             X District
3                    Text1      Date (mm/dd/yyyy):                10/7/21      Date (mm/dd/yyyy):                11/7/21
4                    Text2            Child Grade:                      K            Child Grade:                      K
5                    Text3       Student Study ID:            123-2222-2:       Student Study ID:            112-1111-3:
6                    Text4                    <NA>                   <NA>                    <NA>                   <NA>
7     Demo 1: Color Naming                    <NA>                   <NA>                       0                   <NA>
8                 Amarillo                    <NA>                   <NA>                    <NA>                   <NA>
9                     Azul                    <NA>                   <NA>                       1                   <NA>
10                   Verde                    <NA>                   <NA>                    <NA>                   <NA>
11                    Azul                    <NA>                   <NA>                    <NA>                   <NA>
12          Demo 1: Errors                       0                   <NA>                       0                   <NA>
13    Item 1: Color naming 1 = Incorrect responses         Child response 1 = Incorrect responses         Child response
14                Amarillo                       0                   <NA>                       0                   <NA>
15                    Azul                       1                   <NA>                       1                   <NA>
16                   Verde                    <NA>                   <NA>                    <NA>                   <NA>
17                    Azul                    <NA>                   <NA>                    <NA>                   <NA>
18 Item 1: Time in seconds                    <NA>                   <NA>                     120                   <NA>
19          Item 1: Errors                       0                   <NA>                       0                   <NA>
20    Item 2: Shape Naming 1 = Incorrect responses         Child response 1 = Incorrect responses         Child response
21         Cuadrado/Cuadro                       0                   <NA>                    <NA>                   <NA>
22         Cuadrado/Cuadro                    <NA>                   <NA>                       1                   <NA>
23                 Círculo                    <NA>                   <NA>                       0                   <NA>
24                Estrella                       1                   <NA>                       1                   <NA>
25                 Círculo                       1                   <NA>                    <NA>                   <NA>
26               Triángulo                       0                   <NA>                       1                   <NA>
27 Item 2: Time in seconds                    <NA>                   <NA>                     110                   <NA>
28          Item 2: Errors                       0                   <NA>                       0                   <NA>

期望输出 #

> time
          id itemid time
1 123-2222-2 Item 1   NA
2 123-2222-2 Item 2   NA
3 112-1111-3 Item 1  120
4 112-1111-3 Item 2  110

当前尝试代码（未完成ID提取） #

time.data <- df %>%
  filter(str_detect(Text_1, 'Time in seconds')) # %>%
# select(time = 4)

select_time_cols <- seq(from = 2, to = ncol(time.data), by = 2)

time <- time.data %>%
  select(time = select_time_cols)

time.t<-as.data.frame(t(time))
rownames(time.t)<-seq(1,nrow(time.t),1)
colnames(time.t)<-paste0('i',seq(1,ncol(time.t),1))
time.t<-apply(time.t,2,as.numeric)
time.t<-as.data.frame(time.t)

> time.t
  item1 item2
1    NA    NA
2   120   110

解决方案 #

通过以下步骤实现通用化提取：

1. 提取所有学生的ID信息，对应行是Text_1 == "Text3"的行
2. 提取时间数据行，即Text_1包含Time in seconds的行
3. 将数据整理成长格式，匹配ID和Item信息

完整代码：

library(dplyr)
library(tidyr)
library(stringr)

# 1. 提取学生ID：定位ID行，提取学生数据列并清理格式
student_ids <- df %>%
  filter(Text_1 == "Text3") %>%
  select(seq(2, ncol(df), by = 2)) %>%
  t() %>%
  as.data.frame() %>%
  rename(id = V1) %>%
  mutate(id = str_remove(id, ":"), # 移除ID末尾的冒号
         student_id = row_number())

# 2. 提取时间数据行，同时提取Item编号
time_rows <- df %>%
  filter(str_detect(Text_1, "Time in seconds")) %>%
  mutate(itemid = str_extract(Text_1, "Item \\d+")) %>%
  select(itemid, all_of(seq(2, ncol(df), by = 2)))

# 3. 将时间数据转成长格式，合并学生ID并整理最终结构
result <- time_rows %>%
  pivot_longer(cols = -itemid, names_to = "student_col", values_to = "time") %>%
  mutate(student_id = row_number() %% nrow(student_ids) %>% replace(. == 0, nrow(student_ids))) %>%
  left_join(student_ids, by = "student_id") %>%
  mutate(time = as.numeric(time)) %>%
  select(id, itemid, time) %>%
  arrange(id, itemid)

# 查看结果
print(result)

运行后输出：

id itemid time
1 123-2222-2 Item 1   NA
2 123-2222-2 Item 2   NA
3 112-1111-3 Item 1  120
4 112-1111-3 Item 2  110

代码说明 #

• student_ids部分：定位存储ID的行，提取学生数据列（偶数列），转置后清理ID格式，给每个学生分配唯一编号
• time_rows部分：筛选时间行，提取Item编号，保留学生数据列
• pivot_longer将宽格式时间数据转成长格式，通过学生编号匹配ID，最终整理为期望的结构

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