I have this type of data, where the numerical values in column Sequ
define a sequence of rows and the character value in Q
names the type of sequence:
df <- data.frame(
Line = 1:12,
Speaker = c(NA, "ID01.A", NA, "ID01.B", "ID07.A", NA, "ID33.B",
"ID33.A", "ID33.C", NA, "ID77.A", "ID77.C"),
Utterance = c(NA, "Who did it?", "(1.99)", "Peter did.", "Hello!", NA, "So you're coming?",
"erm", "Yes, sure.", "(0.22)", "Good night?", "Yeah, sleep well"),
Sequ = c(NA,1,1,1, NA,NA, 2,2,2, NA, 3,3),
Q = c(NA, "q_wh", "", "", NA, NA, "q_decl", "", "", NA, "q_wh", "")
)
I want to subset the dataframe on those Sequ
values that are numerical (rather than NA
) AND where Q == q_wh
. I can do this task by using na_if
and then fill
:
library(tidyr)
df %>%
mutate(Q = na_if(Q, "")) %>%
fill(Q, .direction = "down") %>%
filter(!is.na(Sequ) & Q == "q_wh")
Line Speaker Utterance Sequ Q
1 2 ID01.A Who did it? 1 q_wh
2 3 <NA> (1.99) 1 q_wh
3 4 ID01.B Peter did. 1 q_wh
4 11 ID77.A Good night? 3 q_wh
5 12 ID77.C Yeah, sleep well 3 q_wh
But is there another, more direct way, without the detour to na_if
and fill
, to filter df
?
EDIT:
I've found a solution that is without fill
and na_if
:
df %>%
group_by(Sequ) %>%
mutate(Q = Q[!Q==""]) %>%
filter(!is.na(Sequ) & Q == "q_wh")
CodePudding user response:
Using ave
to replace by "Sequ"
with first respective Q
, finally subset
.
df[!is.na(df$Sequ), ] |>
transform(Q=ave(Q, Sequ, FUN=\(x) x[1])) |>
subset(!is.na(Sequ) & Q == 'q_wh')
# Line Speaker Utterance Sequ Q
# 2 2 ID01.A Who did it? 1 q_wh
# 3 3 <NA> (1.99) 1 q_wh
# 4 4 ID01.B Peter did. 1 q_wh
# 11 11 ID77.A Good night? 3 q_wh
# 12 12 ID77.C Yeah, sleep well 3 q_wh
Note: R version 4.1.2 (2021-11-01)
.
Or using tidyverse dialect.
library(magrittr)
df[!is.na(df$Sequ), ] %>%
dplyr::mutate(Q=ave(Q, Sequ, FUN=\(x) x[1])) %>%
dplyr::filter(!is.na(Sequ) & Q == 'q_wh')
# Line Speaker Utterance Sequ Q
# 1 2 ID01.A Who did it? 1 q_wh
# 2 3 <NA> (1.99) 1 q_wh
# 3 4 ID01.B Peter did. 1 q_wh
# 4 11 ID77.A Good night? 3 q_wh
# 5 12 ID77.C Yeah, sleep well 3 q_wh
Data:
df <- structure(list(Line = 1:12, Speaker = c(NA, "ID01.A", NA, "ID01.B",
"ID07.A", NA, "ID33.B", "ID33.A", "ID33.C", NA, "ID77.A", "ID77.C"
), Utterance = c(NA, "Who did it?", "(1.99)", "Peter did.", "Hello!",
NA, "So you're coming?", "erm", "Yes, sure.", "(0.22)", "Good night?",
"Yeah, sleep well"), Sequ = c(NA, 1, 1, 1, NA, NA, 2, 2, 2, NA,
3, 3), Q = c(NA, "q_wh", "", "", NA, NA, "q_decl", "", "", NA,
"q_wh", "")), class = "data.frame", row.names = c(NA, -12L))