1

I have a dataset ('DF1') that I want to know how many times each instance of 'Species' appears per 'Date' per 'Site'. My actual data set has five dates per site and there are ten sites, but I've condensed it quite a bit here.

Site Date Minute Segment Species Vtype
SVC 5/27/2021 5 1 AMCR Call
SVC 5/27/2021 5 1 LISP Song
SVC 5/27/2021 5 2 AMCR Call
SVC 5/27/2021 5 2 LISP Song
SVC 5/27/2021 5 2 LEGO Call
SVC 5/27/2021 11 1 LISP Song
SVC 5/27/2021 11 1 SAVS Song
SVC 5/27/2021 11 1 RCKI Song
SVC 5/27/2021 11 2 SAVS Song
SVC 5/27/2021 11 2 LISP Song
SVC 5/27/2021 11 2 AMCR Call
SVC 5/27/2021 12 1 LISP Song
SVC 5/27/2021 12 1 SAVS Song
SVC 5/27/2021 12 1 AMCR Call
SVC 5/27/2021 12 2 LISP Song
SVC 5/27/2021 12 2 SAVS Song
SVC 5/27/2021 12 2 YRWA Song
SVC 5/27/2021 13 1 SAVS Song
SVC 5/27/2021 13 1 AMCR Call
SVC 5/27/2021 13 1 LISP Song
SVC 5/27/2021 13 1 RCKI Song
SVC 5/27/2021 13 2 YRWA Song
SVC 5/27/2021 13 2 LISP Song
SVC 5/27/2021 13 2 SAVS Song
SVC 5/27/2021 15 1 SAVS Song
SVC 5/27/2021 15 1 YRWA Song
SVC 5/27/2021 15 2 SAVS Song
SVC 5/27/2021 15 2 YRWA Song
SVC 5/27/2021 17 1 SAVS Song
SVC 5/27/2021 17 1 YRWA Song
SVC 5/27/2021 17 2 YRWA Song
SVC 5/27/2021 17 2 SAVS Song
SVC 5/27/2021 17 2 AMCR Call
SVC 5/27/2021 18 1 YRWA Song
SVC 5/27/2021 18 1 SAVS Song
SVC 5/27/2021 18 2 YRWA Song
SVC 5/27/2021 18 2 SAVS Song
SVC 5/27/2021 20 1 SAVS Song
SVC 5/27/2021 20 1 YRWA Song
SVC 5/27/2021 20 2 SAVS Song
SVC 5/27/2021 20 2 YRWA Song
SVC 5/27/2021 20 2 AMCR Call
SVC 5/27/2021 21 1 AMCR Call
SVC 5/27/2021 21 1 SAVS Song
SVC 5/27/2021 21 1 YRWA Song
SVC 5/27/2021 21 2 YRWA Song
SVC 5/27/2021 21 2 AMCR Call
SVC 5/27/2021 21 2 SAVS Song
SVC 5/27/2021 25 1 AMCR Call
SVC 5/27/2021 25 1 SAVS Song
SVC 5/27/2021 25 2 AMCR Call
SVC 5/27/2021 25 2 SAVS Song
TMC 6/1/2021 2 1 DEJU Song
TMC 6/1/2021 2 1 PISI Call
TMC 6/1/2021 2 1 STJA Call
TMC 6/1/2021 2 2 DEJU Song
TMC 6/1/2021 2 2 STJA Call
TMC 6/1/2021 2 2 RCKI Song
TMC 6/1/2021 3 1 DEJU Song
TMC 6/1/2021 3 1 RECR Call
TMC 6/1/2021 3 1 RCKI Song
TMC 6/1/2021 3 1 YRWA Song
TMC 6/1/2021 3 2 MOCH Call
TMC 6/1/2021 3 2 RCKI Song
TMC 6/1/2021 3 2 RECR Call
TMC 6/1/2021 3 2 DEJU Song
TMC 6/1/2021 3 2 YRWA Song
TMC 6/1/2021 3 2 AMRO Call
TMC 6/1/2021 13 1 RCKI Song
TMC 6/1/2021 13 1 YRWA Call
TMC 6/1/2021 13 1 DEJU Song
TMC 6/1/2021 13 1 LISP Song
TMC 6/1/2021 13 2 YRWA Call
TMC 6/1/2021 13 2 RECR Call
TMC 6/1/2021 13 2 DEJU Song
TMC 6/1/2021 13 2 RCKI Song
TMC 6/1/2021 13 2 LISP Song
TMC 6/1/2021 14 1 RCKI Song
TMC 6/1/2021 14 1 YRWA Call
TMC 6/1/2021 14 1 DEJU Song
TMC 6/1/2021 14 1 LISP Song
TMC 6/1/2021 14 2 YRWA Call
TMC 6/1/2021 14 2 DEJU Song
TMC 6/1/2021 14 2 LISP Call
TMC 6/1/2021 17 1 LISP Song
TMC 6/1/2021 17 1 DEJU Song
TMC 6/1/2021 17 1 YRWA Song
TMC 6/1/2021 17 1 AMRO Call
TMC 6/1/2021 17 1 PISI Song
TMC 6/1/2021 17 2 LISP Call
TMC 6/1/2021 17 2 YRWA Song
TMC 6/1/2021 17 2 DEJU Song
TMC 6/1/2021 18 1 YRWA Song
TMC 6/1/2021 18 1 LISP Call
TMC 6/1/2021 18 1 DEJU Song
TMC 6/1/2021 18 2 DEJU Song
TMC 6/1/2021 18 2 LISP Song
TMC 6/1/2021 18 2 YRWA Call
TMC 6/1/2021 18 2 RECR Call
TMC 6/1/2021 21 1 YRWA Call
TMC 6/1/2021 21 1 RCKI Song
TMC 6/1/2021 21 1 LISP Song
TMC 6/1/2021 21 1 PISI Song
TMC 6/1/2021 21 2 RCKI Song
TMC 6/1/2021 21 2 YRWA Call
TMC 6/1/2021 21 2 LISP Song
TMC 6/1/2021 22 1 RCKI Song
TMC 6/1/2021 22 1 YRWA Call
TMC 6/1/2021 22 1 LISP Song
TMC 6/1/2021 22 2 YRWA Call
TMC 6/1/2021 22 2 RCKI Song
TMC 6/1/2021 22 2 LISP Song
TMC 6/1/2021 22 2 AMRO Call
TMC 6/1/2021 22 2 HAFL Song
TMC 6/1/2021 23 1 YRWA Call
TMC 6/1/2021 23 1 RCKI Song
TMC 6/1/2021 23 1 HAFL Song
TMC 6/1/2021 23 1 LISP Song
TMC 6/1/2021 23 2 DEJU Song
TMC 6/1/2021 23 2 RECR Call
TMC 6/1/2021 23 2 LISP Song
TMC 6/1/2021 23 2 RCKI Song
TMC 6/1/2021 23 2 AMRO Call
TMC 6/1/2021 23 2 YRWA Call
TMC 6/1/2021 25 1 DEJU Song
TMC 6/1/2021 25 1 RCKI Song
TMC 6/1/2021 25 1 HAFL Song
TMC 6/1/2021 25 1 LISP Call
TMC 6/1/2021 25 1 YRWA Song
TMC 6/1/2021 25 2 DEJU Song
TMC 6/1/2021 25 2 LISP Call
TMC 6/1/2021 25 2 HAFL Song
TMC 6/1/2021 25 2 RCKI Song

I can summarize the counts for one Species at a time using this code:

DF1 %>% group_by(Site,Date) %>% 
  filter(Species=="AMCR") %>%
  summarise(count_Species = n())

This will return the count for 'AMCR' for each site on each date, but it would be too tedious to do this for each Species and then combine all those results into one dataframe.

Is there a way to loop this for each unique instance of species and then paste them together into one dataframe?

Thank you!

Jacob
  • 329
  • 2
  • 10
  • 1
    Remove the `filter` and add `Species` to your `group_by()`? – joran Oct 11 '21 at 21:17
  • That gives me the total count for each Species over the entire dataframe, not the counts per day per site. – Jacob Oct 11 '21 at 21:19
  • 1
    When I say "add Species to your `group_by()` I mean `group_by(Site,Date,Species)`" which will give you a count for each site/date/species combination, not for the whole data frame. – joran Oct 11 '21 at 21:20
  • That worked just great! I can't believe it was that simple. I just needed to add one more term. Thank you. – Jacob Oct 11 '21 at 21:21

1 Answers1

2

We could use count from dplyr package!

library(dplyr)
df %>% 
  as_tibble() %>%
  count(Species, Date, Site)

   Species Date      Site      n
   <chr>   <chr>     <chr> <int>
 1 AMCR    5/27/2021 SVC      11
 2 AMRO    6/1/2021  TMC       4
 3 DEJU    6/1/2021  TMC      15
 4 HAFL    6/1/2021  TMC       4
 5 LEGO    5/27/2021 SVC       1
 6 LISP    5/27/2021 SVC       8
 7 LISP    6/1/2021  TMC      16
 8 MOCH    6/1/2021  TMC       1
 9 PISI    6/1/2021  TMC       3
10 RCKI    5/27/2021 SVC       2
11 RCKI    6/1/2021  TMC      14
12 RECR    6/1/2021  TMC       5
13 SAVS    5/27/2021 SVC      18
14 STJA    6/1/2021  TMC       2
15 YRWA    5/27/2021 SVC      12
16 YRWA    6/1/2021  TMC      17
TarJae
  • 72,363
  • 6
  • 19
  • 66
  • 1
    This also works seemingly just as well as the other solution in the comments of my question. – Jacob Oct 11 '21 at 21:28
  • 1
    `count` is really powerful. See its argument with `?count` (sort, weight, name)! – TarJae Oct 11 '21 at 21:30
  • 1
    I will say that answer automatically sorts the dataframe alphabetically which is an added bonus. – Jacob Oct 11 '21 at 21:33