Hi all,
I've been sitting with this problem for a few hours now and can't seem to come up with a good solution - I hope you can help me!
The data are structured like so:
Speaker StartTimeMS EndTimeMS
1 John 0 2009
2 Mother 4203 6316
3 John 6687 7221
4 Mother 6676 8092
5 Mother 9309 16623
6 John 17036 18220
7 Mother 17930 18940
8 John 19399 20026
9 Mother 20020 21192
10 Mother 22808 23458
What I want to do is combine the StartTimeMS from one row and the EndTimeMS from another row, if two of the same Speakers appear after each other; that is, the goal is to combine the time data in the 'Mother' cells appearing immediately after each other. The goal is the following:
Speaker StartTimeMS EndTimeMS
1 John 0 2009
2 Mother 4203 6316
3 John 6687 7221
4 Mother 6676 16623
5 John 17036 18220
7 Mother 17930 18940
8 John 19399 20026
9 Mother 20020 23458
I've been trying with the following code, but I can't seem to find the final step of combining the data from other columns:
data <- data %>%
mutate(identity = lag(Speaker, 1),
decide = if_else(Speaker != identity, 1, 0),
keep = lead(decide, 1, default = 1)) %>%
filter(keep == 1) %>%
select(-identity, -decide, -keep)
I'd really appreciate your help on this!!
Here is some code for a subset of the data:
Speaker <- c("John", "Mother", "John", "Mother", "Mother", "John", "Mother", "John", "Mother", "Mother")
StartTimeMS <- c(0, 4203, 6687, 6676, 9309, 17036, 17930, 19399, 20020, 22808)
EndTimeMS <- c(2009, 6316, 7221, 8092, 16623, 18220, 18940, 20026, 21192, 23458)
data <- data.frame(Speaker, StartTimeMS,EndTimeMS)