Home > database >  Remove groups if all NA
Remove groups if all NA

Time:01-19

Let's say I have a table like so:

df <- data.frame("Group" = c("A","A","A","B","B","B","C","C","C"),
              "Num" = c(1,2,3,1,2,NA,NA,NA,NA))

  Group Num
1     A   1
2     A   2
3     A   3
4     B   1
5     B   2
6     B  NA
7     C  NA
8     C  NA
9     C  NA

In this case, because group C has Num as NA for all entries, I would like to remove rows in group C from the table. Any help is appreciated!

CodePudding user response:

You could group_by on you Group and filter the groups with all values that are NA. You can use the following code:

library(dplyr)
df %>%
  group_by(Group) %>%
  filter(!all(is.na(Num)))
#> # A tibble: 6 × 2
#> # Groups:   Group [2]
#>   Group   Num
#>   <chr> <dbl>
#> 1 A         1
#> 2 A         2
#> 3 A         3
#> 4 B         1
#> 5 B         2
#> 6 B        NA

Created on 2023-01-18 with reprex v2.0.2

CodePudding user response:

In base R you could index based on all the groups that have at least one non-NA value:

idx <- df$Group %in% unique(df[!is.na(df$Num),"Group"])
idx 
df[idx,]

# or in one line

df[df$Group %in% unique(df[!is.na(df$Num),"Group"]),]

output

  Group Num
1     A   1
2     A   2
3     A   3
4     B   1
5     B   2
6     B  NA

CodePudding user response:

Using ave.

df[with(df, !ave(Num, Group, FUN=\(x) all(is.na(x)))), ]
#   Group Num
# 1     A   1
# 2     A   2
# 3     A   3
# 4     B   1
# 5     B   2
# 6     B  NA
  • Related