How to select the rows based in the counts in one column and group by anothe column

Question

I have one data frame with several columns. I want to select only the rows that have especific information (species from one specific genus) that appear at least three times in the whole data frame and group by column "code". In the species columns (spp) I have the species name for the interested species and "NA" for all others species that are not my interest here. I need to count the number that each species of the genus appear and exclude the other sites (code column) that only have species that appear less than 3 times in the whole dataset.

df <- data.frame(
  spp = c("sp1", "sp1", "sp1","NA", "NA", "sp3", "sp3", "sp3", "sp3", "NA", "NA",
          "NA", "NA", "NA"),
  code = c("a", "b", "c","a", "e", "d", "a", 
           "b", "c", "f", "b","a","b","c"),
  va1 = c(1, 2, 2, 2,4, 3, 3, 4, 5, 5, 5,6,7,8)
)



   spp code va1
1  sp1    a   1
2  sp1    b   2
3  sp1    c   2
4   NA    a   2
5   NA    e   4
6  sp3    d   3
7  sp3    a   3
8  sp3    b   4
9  sp3    c   5
10 sp4    f   5
11  NA    b   5
12  NA    a   6
13  NA    b   7
14  NA    c   8

filtered_df <- df %>%
  group_by(code) %>%
  filter(n() >= 3)

I'm trying to estipulate to not to count NAs, but is not working.

I'm expecting (below) that only appear the species and the code sites that have species that occur at leat 3 times. So it would be only the spp that appear 3 or more times and the sites that have these species (the site that have only species that appear less than 3 times should be excluded from the dataset).

   spp code va1
1  sp1    a   1
2  sp1    b   2
3  sp1    c   2
4   NA    a   2
5  sp3    d   4
6  sp3    a   3
7  sp3    b   3
8  sp3    c   3
9   NA    b   4
10  NA    a   5
11  NA    b   6
12  NA    c   7

The codes e and f were excluded, because the species of interest did not appear at least 3 times.

TarJae · Answer 1 · 2023-05-23T16:26:00.167

1

We could this way:

library(dplyr)
library(tidyr)

df %>% 
  mutate(helper = spp,
         helper = na_if(spp, "NA")) %>% 
  fill(helper, .direction = "down") %>% 
  group_by(code) %>%
  filter(n() >= 3) %>% 
  select(-helper)

  spp   code    va1
   <chr> <chr> <dbl>
 1 sp1   a         1
 2 sp1   b         2
 3 sp1   c         2
 4 NA    a         2
 5 sp3   a         3
 6 sp3   b         4
 7 sp3   c         5
 8 NA    b         5
 9 NA    a         6
10 NA    b         7
11 NA    c         8

edited May 23 '23 at 16:26

answered May 23 '23 at 15:59

TarJae

72,363
6
19
66

but one thing, I would like to keep the other species for sites (code column) which the species of interest (sp1,sp3...) appear at least 3 times. I notice that I have to edit the example. – pmp May 23 '23 at 16:07
Could you show your expected output? – TarJae May 23 '23 at 16:13
1

I just updated. Thank you – pmp May 23 '23 at 16:16

How to select the rows based in the counts in one column and group by anothe column

1 Answers1