Search code examples
rdplyrgrouping

Count rows and assign zero for NA observations in a specific column

I have a data set containing ecological survey data on nine transects. Five quadrats were placed randomly along the length of each transect, and individuals within each quadrat were counted. Here is a subset of the data:

df <- structure(list(Date = c("8/22/2022", "8/22/2022", "8/22/2022", "8/22/2022", "8/22/2022", "8/22/2022", "8/22/2022", "8/22/2022", "8/22/2022", "8/22/2022", "8/22/2022", "8/22/2022", "8/22/2022", "8/23/2022", "8/23/2022", "8/23/2022", "8/23/2022", "8/23/2022"), Transect.No = c(3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 5L, 5L, 5L, 5L, 5L), Quadrat.No = c(1L, 1L, 2L, 2L, 2L, 3L, 4L, 4L, 5L, 5L, 5L, 5L, 5L, 1L, 2L, 3L, 4L, 5L), Species = c("CUNN", "CUNN", "DOL", "DOL", "CUNN", NA, "CUNN", "CUNN", "CUNN", "CUNN", "CUNN", "CUNN", "CUNN", NA, NA, "FLOU", "DOL", NA)), class = "data.frame", row.names = c(NA, -18L))
head(df)

       Date Transect.No Quadrat.No Species
1 8/22/2022           3          1    CUNN
2 8/22/2022           3          1    CUNN
3 8/22/2022           3          2     DOL
4 8/22/2022           3          2     DOL
5 8/22/2022           3          2    CUNN
6 8/22/2022           3          3    <NA>

An NA in the Species variable implies that there were zero individuals in the corresponding quadrat. I am looking to create a new data frame with a variable Abundance counting the total number of individuals in each quadrat. This new data frame would look like this:

enter image description here

I tried the following code, but it gives Abundance = 1 for those quadrats with Species = NA:

library(dplyr)
df %>% count(Quadrat.No, Transect.No, Date, name = "Abundance")

I want Abundance = 0 in that case. How can I do that?

Solution

  • you can use a weighted counts with the wt argument in the dplyr count functionand use not missing Species as weight

    df %>%
  count(Quadrat.No, Transect.No, Date,wt=!is.na(Species), name= "Abundance") 

   Quadrat.No Transect.No      Date Abundance
1           1           3 8/22/2022         2
2           1           5 8/23/2022         0
3           2           3 8/22/2022         3
4           2           5 8/23/2022         0
5           3           3 8/22/2022         0
6           3           5 8/23/2022         1
7           4           3 8/22/2022         2
8           4           5 8/23/2022         1
9           5           3 8/22/2022         5
10          5           5 8/23/2022         0