Filter dataframe on sequence of rows conditional on two columns

user_8039978 0
Viewed 73

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_ifand 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")
tidyr r
1 Answers

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))
user_65740380
Related