I have Excel sheets I want to analyze in R to examine elapsed time between each teacher and student turns. I am using this code to import data:
data_raw_all <- list.files(path ="coded_data", pattern = ".xlsx", full.names= T) %>% #all Excel files in a folder called "coded_data"
setNames(nm = .) %>%
lapply(read_excel, range = cell_cols(2:9)) %>% #select relevant columns
lapply((x) mutate(x, across(everything(), tolower))) %>%
lapply((x) mutate(x, across(Turn, as.character))) %>%
bind_rows(.id = "Transcript") %>%
mutate(Transcript = str_extract(Transcript, "TalkBack\\S"))
When imported into R, the time variable from Excel is coded in 2 formats (separate data frames provided as examples):
In sheets like "Year1_Fall", time follows HH:MM:SS. The format stays similar when imported into R (00:02:16 -> 00:02:16).
In the sheets like "Year1_Parker", time follows MM:SS:msms. When imported into R, it is converted into a number(08:16:00 -> 0.344) or time becomes the next day when it exceeds the 24 mark corresponding to 24 minute. Then when it becomes the 48th mark/minute, it continues to the next day or converts back to presumably MM:SS. I will note that in Excel, the format is also wrong when exceeding the 24, 48 etc. mark (24:01 is 1900-01-01 12:01:00 AM in Excel and becomes 24:01:00 pm in R). Because these are all math lessons in a single session, time is within the same day and so should not cut across days.
I need help making the time format consistent into HH:MM:SS (image example). I have tried other solutions about functions related to reading time and time display but to no avail.
I need the time format consistent so that I can calculate the elapsed time between rows, and then total time per speaker. Using the first 14 rows in "Year1_Fall" as an example, elapsed time between rows 1 and 2 = 3 seconds of teacher; rows 2 and 3 = 4 seconds of student; rows 3 - 13 = 34 seconds for teacher (image attached). Total teacher time = 37 seconds and total student time = 4 seconds. I think I can use something like How to calculate time elapsed between rows in R but would appreciate any help.
Year1_Fall = structure(list(TimeStamp = c("00:00:05", "00:00:08", "00:00:12",
"00:00:12", "00:00:12", "00:00:12", "00:00:12", "00:00:12", "00:00:12",
"00:00:12", "00:00:12", "00:00:12", "00:00:12", "00:00:46", "00:00:55",
"00:00:55", "00:00:55", "00:01:11", "00:01:14", "00:01:16", "00:01:20",
"00:01:20", "00:01:20", "00:01:20", "00:01:20", "00:01:20", "00:01:20",
"00:01:20", "00:01:20", "00:01:20", "00:01:20", "00:01:20", "00:01:20",
"00:02:13", "00:02:16", "00:02:16", "00:02:16", "00:02:16", "00:02:16",
"00:02:16", "00:02:16", "00:02:16", "00:02:16", "00:02:16", "00:02:16",
"00:02:16", "00:02:16", "00:02:16", "00:02:16", "00:02:16", "00:02:16",
"00:02:16", "00:02:16", "00:03:30", "00:03:31", "00:03:31", "00:03:31",
"00:03:31", "00:03:31", "00:03:31", "00:03:31", "00:03:31", "00:03:31",
"00:03:31", "00:03:31", "00:03:31", "00:03:31", "00:04:35", "00:04:36",
"00:04:36", "00:04:36", "00:04:36", "00:04:36", "00:05:03", "00:05:08",
"00:05:08", "00:05:12", "00:05:16", "00:05:17", "00:05:19", "00:05:19",
"00:05:19", "00:05:19", "00:05:19", "00:05:19", "00:05:19", "00:05:19",
"00:06:11", "00:06:11", "00:06:11", "00:06:11", "00:06:18", "00:06:20",
"00:06:20", "00:06:20", "00:06:20", "00:06:20", "00:06:20", "00:06:20",
"00:06:20", "00:06:20", "00:06:20", "00:06:20", "00:06:20", "00:06:20",
"00:06:20", "00:06:20", "00:06:20", "00:07:21", "00:07:21", "00:07:21",
"00:07:42", "00:07:42"), Turn = c(1, 2, 3, 3, 3, 3, 3, 3, 3,
3, 3, 3, 4, 4, 5, 5, 5, 6, 7, 8, 9, 9, 9, 9, 9, 9, 9, 9, 10,
10, 10, 10, 10, 10, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11,
12, 12, 12, 12, 12, 12, 12, 12, 12, 13, 13, 13, 13, 13, 13, 13,
14, 14, 14, 14, 14, 14, 14, 15, 15, 15, 15, 15, 16, 17, 17, 18,
19, 20, 21, 21, 21, 21, 21, 21, 21, 21, 22, 22, 22, 22, 23, 24,
24, 24, 24, 24, 24, 24, 24, 25, 25, 25, 25, 25, 25, 25, 25, 25,
25, 25, 26, 26), Speaker = c("T", "Standley", "T", "T", "T",
"T", "T", "T", "T", "T", "T", "T", "T", "SS", "T", "T", "T",
"Paulina", "T", "Lindsey", "T", "T", "T", "T", "T", "T", "T",
"T", "T", "T", "T", "T", "T", "Asher", "T", "T", "T", "T", "T",
"T", "T", "T", "T", "T", "T", "T", "T", "T", "T", "T", "T", "T",
"T", "Asher", "T", "T", "T", "T", "T", "T", "T", "T", "T", "T",
"T", "T", "T", "SS", "T", "T", "T", "T", "T", "S", "T", "T",
"Standley", "T", "Standley", "T", "T", "T", "T", "T", "T", "T",
"T", "T", "T", "T", "T", "SS", "T", "T", "T", "T", "T", "T",
"T", "T", "T", "T", "T", "T", "T", "T", "T", "T", "T", "T", "T",
"T", "T")), class = c("tbl_df", "tbl", "data.frame"), row.names = c(NA,
-113L))
Year1.Parker = structure(list(TimeStamp = c("00:00:00", "00:00:00", "00:00:00",
"00:24:00", "00:47:00", "00:47:00", "00:47:00", "00:47:00", "00:47:00",
"01:33:00", "01:33:00", "01:33:00", "01:33:00", "01:33:00", "02:07:00",
"02:07:00", "02:07:00", "02:07:00", "02:32:00", "03:06:00", "03:29:00",
"03:50:00", "03:55:00", "03:55:00", "20:16:00", "20:23:00", "20:23:00",
"20:23:00", "20:23:00", "20:23:00", "20:23:00", "20:23:00", "20:50:00",
"20:50:00", "20:53:00", "20:53:00", "20:53:00", "20:53:00", "20:53:00",
"20:53:00", "21:18:00", "21:18:00", "21:18:00", "21:18:00", "21:18:00",
"21:18:00", "21:18:00", "21:18:00", "21:18:00", "21:18:00", "21:45:00",
"21:53:00", "21:53:00", "21:53:00", "21:53:00", "21:53:00", "21:53:00",
"21:53:00", "21:53:00", "21:53:00", "21:53:00", "22:27:00", "22:32:00",
"22:33:00", "22:37:00", "22:37:00", "22:37:00", "22:46:00", "22:46:00",
"22:46:00", "22:46:00", "22:46:00", "22:46:00", "22:46:00", "22:46:00",
"22:46:00", "22:46:00", "22:46:00", "22:46:00", "23:21:00", "23:23:00",
"23:23:00", "23:23:00", "23:23:00", "23:31:00", "23:35:00", "23:35:00",
"23:35:00", "23:35:00", "23:35:00", "23:35:00", "23:35:00", "23:35:00",
"23:35:00", "23:35:00", "1.005555555555556", "1.006944444444444",
"1.006944444444444", "1.006944444444444", "1.006944444444444",
"1.006944444444444", "1.006944444444444", "1.028472222222222",
"1.028472222222222", "1.028472222222222", "1.028472222222222",
"1.028472222222222", "1.028472222222222", "1.028472222222222",
"1.028472222222222", "1.028472222222222", "1.056944444444444",
"1.056944444444444", "1.056944444444444", "1.056944444444444",
"1.056944444444444", "1.056944444444444", "1.056944444444444",
"1.056944444444444", "1.056944444444444", "1.056944444444444",
"1.0819444444444439", "1.083333333333333", "1.083333333333333",
"1.0847222222222219", "1.0847222222222219", "1.0847222222222219",
"1.0847222222222219", "1.0847222222222219", "1.0847222222222219",
"1.0847222222222219", "1.0847222222222219", "1.0847222222222219",
"1.0847222222222219", "1.0847222222222219", "1.0847222222222219",
"1.115277777777778", "2.318055555555556", "2.318055555555556",
"2.318055555555556", "2.318055555555556", "2.3284722222222221",
"2.3326388888888889", "2.3326388888888889", "2.334027777777778",
"2.334027777777778", "2.334027777777778", "2.334027777777778",
"2.334027777777778", "2.334027777777778", "2.334027777777778",
"2.334027777777778", "2.334027777777778", "2.334027777777778",
"2.334027777777778", "2.334027777777778", "2.334027777777778",
"2.3631944444444439", "2.3631944444444439", "2.3631944444444439",
"2.3631944444444439", "2.3631944444444439", "2.3631944444444439",
"2.3631944444444439", "2.3631944444444439", "2.3631944444444439",
"2.3631944444444439", "2.3631944444444439", "2.3631944444444439",
"2.3875000000000002", "2.3875000000000002", "2.3875000000000002",
"2.3875000000000002", "2.3875000000000002", "2.402083333333334",
"2.402083333333334", "2.4076388888888891", "2.4076388888888891",
"2.4076388888888891", "2.410416666666666", "2.410416666666666",
"2.410416666666666", "2.410416666666666", "2.4194444444444438",
"2.4194444444444438", "2.4194444444444438", "2.4194444444444438",
"2.4194444444444438", "2.4194444444444438", "2.4194444444444438",
"2.4194444444444438", "2.4194444444444438", "2.4395833333333332",
"2.4395833333333332", "2.4395833333333332", "2.4395833333333332",
"2.4395833333333332", "2.4395833333333332", "2.4395833333333332",
"2.4395833333333332", "2.4395833333333332", "2.4395833333333332",
"2.4395833333333332", "2.464583333333334", "2.464583333333334",
"2.464583333333334", "2.464583333333334", "2.464583333333334",
"2.464583333333334", "2.464583333333334", "2.464583333333334",
"2.464583333333334", "2.464583333333334", "2.4916666666666671",
"2.4979166666666668", "01:00:12", "01:00:12", "01:00:12", "01:00:42",
"01:00:42", "01:00:42", "01:00:42", "01:00:42", "01:00:42", "01:00:42",
"01:00:42", "01:00:42", "01:00:42", "01:01:19", "01:01:19", "01:01:19",
"01:01:19", "01:01:19", "01:01:19", "01:01:34", "01:01:37", "01:01:37",
"01:01:40"), Turn = c(1, 1, 1, 2, 3, 3, 3, 3, 3, 4, 4, 4, 4,
4, 5, 5, 5, 5, 6, 7, 8, 9, 10, 10, 89, 90, 90, 90, 90, 90, 90,
90, 91, 92, 93, 94, 94, 94, 94, 94, 95, 96, 96, 96, 96, 96, 96,
96, 96, 96, 97, 98, 98, 98, 98, 98, 98, 98, 98, 98, 98, 99, 100,
101, 102, 102, 102, 103, 104, 104, 104, 104, 104, 104, 104, 104,
104, 104, 104, 105, 106, 106, 106, 106, 107, 108, 108, 108, 108,
108, 108, 108, 108, 108, 108, 109, 110, 110, 110, 110, 110, 110,
111, 112, 112, 112, 112, 112, 112, 112, 112, 113, 114, 114, 114,
114, 114, 114, 114, 114, 114, 115, 116, 116, 117, 118, 118, 118,
118, 118, 118, 118, 118, 118, 118, 118, 119, 284, 284, 284, 284,
285, 286, 287, 288, 288, 288, 288, 288, 288, 288, 288, 288, 288,
288, 288, 288, 289, 290, 290, 290, 290, 290, 290, 290, 290, 290,
290, 290, 291, 292, 292, 292, 292, 293, 293, 294, 295, 295, 296,
297, 297, 297, 298, 299, 299, 299, 299, 299, 299, 299, 299, 300,
300, 300, 300, 300, 300, 300, 300, 300, 300, 300, 301, 301, 301,
301, 301, 301, 301, 301, 301, 301, 302, 303, 304, 304, 304, 305,
305, 305, 305, 305, 305, 305, 305, 305, 305, 306, 306, 306, 306,
306, 306, 307, 308, 308, 309), Speaker = c("T", "T", "T", "T",
"T", "T", "T", "T", "T", "T", "T", "T", "T", "T", "T", "T", "T",
"T", "T", "T", "T", "S", "SS", "SS", "S", "T", "T", "T", "T",
"T", "T", "T", "SS", "T", "SS", "T", "T", "T", "T", "T", "SS",
"T", "T", "T", "T", "T", "T", "T", "T", "T", "S", "T", "T", "T",
"T", "T", "T", "T", "T", "T", "T", "S", "T", "S", "T", "T", "T",
"SS", "T", "T", "T", "T", "T", "T", "T", "T", "T", "T", "T",
"S", "T", "T", "T", "T", "S", "T", "T", "T", "T", "T", "T", "T",
"T", "T", "T", "S", "T", "T", "T", "T", "T", "T", "S", "T", "T",
"T", "T", "T", "T", "T", "T", "S", "T", "T", "T", "T", "T", "T",
"T", "T", "T", "S", "T", "T", "S", "T", "T", "T", "T", "T", "T",
"T", "T", "T", "T", "T", "SS", "T", "T", "T", "T", "S", "T",
"S", "T", "T", "T", "T", "T", "T", "T", "T", "T", "T", "T", "T",
"T", "SS", "T", "T", "T", "T", "T", "T", "T", "T", "T", "T",
"T", "S", "T", "T", "T", "T", "T", "T", "SS", "T", "T", "SS",
"T", "T", "T", "SS", "T", "T", "T", "T", "T", "T", "T", "T",
"T", "T", "T", "T", "T", "T", "T", "T", "T", "T", "T", "T", "T",
"T", "T", "T", "T", "T", "T", "T", "T", "T", "T", "T", "T", "T",
"T", "T", "T", "T", "T", "T", "T", "T", "T", "T", "T", "T", "T",
"T", "T", "T", "S", "T", "T", "S")), class = c("tbl_df", "tbl",
"data.frame"), row.names = c(NA, -238L))