Keep only the rows which contain some entries in at least one of its columns

user_1306892 0
Viewed 43

I have the following dataframe df

structure(list(Names = c("James", "Diana", "Dr. Michel", "Mr. George", "Hello world", "Janis","JJK"), code = c("xx34-2111", "7789-2869", "dg67-5555", "1555-2uuy", "fhgj-2jj8", "2199-7kk8","1111-1111"), `13/A1` = c(NA, "‡(2017)", NA, "‡(2017)", "A", 
"A(2019)", "A(2016)"), `13/A2` = c(NA, "‡(2017)", NA, "‡(2017)", 
"A", "A(2019)", "A(2016)"), `13/A3` = c(NA, "‡(2017)", NA, "‡(2017)", 
"A", "A(2019)", "A(2016)"), `13/A4` = c(NA, "‡(2017)", NA, "‡(2017)", 
"A", "A(2019)", "A(2016)"), `13/A5` = c(NA, "‡(2017)", NA, "‡(2017)", 
"A", "A(2019)", "A(2016)"), `13/B1` = c("A", "A", "A(2017)", 
"‡(2017)", "A", "A(2019)", NA), `13/B2` = c("A", "A", "A(2017)", 
"‡(2017)", "A", "A(2019)", NA), `13/B3` = c("A", "A", "A(2017)", 
"‡(2017)", "A", "A(2019)", NA), `13/B4` = c("A", "A", "A(2017)", 
"‡(2017)", "A", "A(2019)", NA), `13/B5` = c("A", "A", "A(2017)", 
"‡(2017)", "A", "A(2019)", NA), `13/C1` = c(NA, "A", NA, "‡(2017)", 
"A", "A(2019)", NA), `13/D1` = c(NA, "‡(2017)", NA, "‡(2017)", 
NA, "A", NA), `13/D2` = c(NA, "‡(2017)", NA, "‡(2017)", NA, "A", 
NA), `13/D3` = c(NA, "‡(2017)", NA, "‡(2017)", NA, "A", "A(2017)"
), `13/D4` = c("A", "‡(2017)", NA, "‡(2017)", NA, "A", NA)), row.names = c(1L, 
6L, 11L, 39L, 101L, 483L, 667L), class = "data.frame")

I would like to keep only the rows of df which contain in at least one of the columns 3:17, any of the following entries: A, A(2016), A(2017), A(2019).

For the example above, this means to delete only the row containing Mr. George in the field Names.

My attempt

df[,3:17]%in% c("A","A(2016)","A(2017)","A(2019)")

does not give the desired result.

dataframe r
3 Answers

With a fully dplyr approach:

library(dplyr)

df %>% 
  filter(if_any(3:17, ~ .x %in% c("A","A(2016)","A(2017)","A(2019)")))

#>         Names      code   13/A1   13/A2   13/A3   13/A4   13/A5   13/B1   13/B2
#> 1       James xx34-2111    <NA>    <NA>    <NA>    <NA>    <NA>       A       A
#> 2       Diana 7789-2869 ‡(2017) ‡(2017) ‡(2017) ‡(2017) ‡(2017)       A       A
#> 3  Dr. Michel dg67-5555    <NA>    <NA>    <NA>    <NA>    <NA> A(2017) A(2017)
#> 4 Hello world fhgj-2jj8       A       A       A       A       A       A       A
#> 5       Janis 2199-7kk8 A(2019) A(2019) A(2019) A(2019) A(2019) A(2019) A(2019)
#> 6         JJK 1111-1111 A(2016) A(2016) A(2016) A(2016) A(2016)    <NA>    <NA>
#>     13/B3   13/B4   13/B5   13/C1   13/D1   13/D2   13/D3   13/D4
#> 1       A       A       A    <NA>    <NA>    <NA>    <NA>       A
#> 2       A       A       A       A ‡(2017) ‡(2017) ‡(2017) ‡(2017)
#> 3 A(2017) A(2017) A(2017)    <NA>    <NA>    <NA>    <NA>    <NA>
#> 4       A       A       A       A    <NA>    <NA>    <NA>    <NA>
#> 5 A(2019) A(2019) A(2019) A(2019)       A       A       A       A
#> 6    <NA>    <NA>    <NA>    <NA>    <NA>    <NA> A(2017)    <NA>

Another possible solution:

library(magrittr)

df[((df[,3:17] == "A") + (df[,3:17] == "A(2016)") +
    (df[,3:17] == "A(2017)") + (df[,3:17] == "A(2019)")) %>% 
       apply(1, \(x) sum(x[3:17], na.rm = T)) != 0, ]

#>           Names      code   13/A1   13/A2   13/A3   13/A4   13/A5   13/B1
#> 1         James xx34-2111    <NA>    <NA>    <NA>    <NA>    <NA>       A
#> 6         Diana 7789-2869 ‡(2017) ‡(2017) ‡(2017) ‡(2017) ‡(2017)       A
#> 11   Dr. Michel dg67-5555    <NA>    <NA>    <NA>    <NA>    <NA> A(2017)
#> 101 Hello world fhgj-2jj8       A       A       A       A       A       A
#> 483       Janis 2199-7kk8 A(2019) A(2019) A(2019) A(2019) A(2019) A(2019)
#> 667         JJK 1111-1111 A(2016) A(2016) A(2016) A(2016) A(2016)    <NA>
#>       13/B2   13/B3   13/B4   13/B5   13/C1   13/D1   13/D2   13/D3   13/D4
#> 1         A       A       A       A    <NA>    <NA>    <NA>    <NA>       A
#> 6         A       A       A       A       A ‡(2017) ‡(2017) ‡(2017) ‡(2017)
#> 11  A(2017) A(2017) A(2017) A(2017)    <NA>    <NA>    <NA>    <NA>    <NA>
#> 101       A       A       A       A       A    <NA>    <NA>    <NA>    <NA>
#> 483 A(2019) A(2019) A(2019) A(2019) A(2019)       A       A       A       A
#> 667    <NA>    <NA>    <NA>    <NA>    <NA>    <NA>    <NA> A(2017)    <NA>
user_115644870

Using grepl

> rowSums(matrix(grepl("^A$|A\\(2019\\)|A\\(2017\\)|A\\(2016\\)",unlist(df)),nrow(df)))>0

[1]  TRUE  TRUE  TRUE FALSE  TRUE  TRUE  TRUE
user_29749510

Loop through columns - sapply, check if values matches - %in%, then check if any column TRUE per row - rowSums(...) > 0

df[ rowSums(sapply(df[, 3:17], function(i) i %in% c("A","A(2016)","A(2017)","A(2019)"))) > 0, ]
user_6800680
Related