Aggregate by a character vector

Question

I am trying to aggregate my data (sample):

     Julian_Date Week Site Color_Combo Singing Building Incubating Nestlings Empty_Nest
1            5    1   BV                   1        0          0         0          0
2           21    3   BV                   0        0          0         0          0
3           25    4   BV                   1        1          0         0          0
4           32    5   BV NB/SI:NB/DG       0        0          0         0          0
5           32    5   BV NB/SI:NB/MV       0        0          0         0          0
6           32    5   BV NB/NB:NB/NB       0        0          0         0          0
7           32    5   BV NB/NB:NB/NB       0        0          0         0          0
8           33    5   BV NB/NB:NB/NB       0        0          0         0          0
9           33    5   BV NB/NB:NB/NB       0        0          0         0          0
10          33    5   BV NB/NB:NB/NB       0        0          0         0          0
11          33    5   BV NB/NB:NB/NB       0        0          0         0          0
12          33    5   BV NB/NB:NB/NB       0        0          0         0          0
13          33    5   BV NB/NB:NB/NB       0        0          0         0          0
14          33    5   BV NB/NB:NB/NB       0        0          0         0          0
15          33    5   BV NB/NB:NB/NB       0        0          0         0          0
16          33    5   BV NB/NB:NB/NB       0        0          0         0          0
17          33    5   BV NB/NB:NB/NB       0        0          0         0          0
18          34    5   BV NB/NB:NB/NB       0        0          0         0          0
19          34    5   BV NB/NB:NB/NB       0        0          0         0          0
20          35    5   BV NB/NB:NB/SI       0        0          0         0          0
21          35    5   BV NB/RD:NB/SI       0        0          0         0          0
22          35    5   BV NB/YE:NB/SI       0        0          0         0          0
23          35    5   BV NB/NB:NB/NB       0        0          0         0          0
24          35    5   BV NB/NB:NB/NB       0        0          0         0          0
25          35    5   BV NB/NB:NB/NB       0        0          0         0          0
26          35    5   BV NB/NB:NB/NB       0        0          0         0          0
27          35    5   BV NB/NB:NB/NB       0        0          0         0          0
28          35    5   BV NB/NB:NB/NB       0        0          0         0          0
29          35    5   BV NB/NB:NB/NB       0        0          0         0          0
30          35    5   BV NB/NB:NB/NB       0        0          0         0          0

...so that I can get a sum of the number of each behavioral observation (last five columns) with a unique Color_Combo in each Week. Each color combination represents an individual bird, and I would like to know how many different birds sang during a week (it doesn't matter how many times that week an individual bird sang). I'm having trouble using aggregate because color_combo is a character vector, and I tried using the code from this question: R: Aggregate character strings but couldn't get it to work. What I would like is something like this:

     Week Singing Building Incubating Nestlings Empty_Nest
 [1,]    1       1        0          0         0          0
 [2,]    3       0        0          0         0          0
 [3,]    4       1        1          0         0          0
 [4,]    5       0        0          0         0          0
 [5,]    6       0        0          0         0          0
 [6,]    7       0        0          0         0          0
 [7,]    8       0        0          0         0          0
 [8,]    9       0        0          0         0          0
 [9,]   10       0        0          0         0          0
[10,]   11       0        0          0         0          0

I have quite a bit more data so eventually there will be entries greater than 1. Any help would be appreciated!

`sample %>% group_by(Week) %>% summarise(across(Singing:Empty_Nest, sum))` using `dplyr` library. — Ronak Shah, Jan 13 '21 at 01:39
Sorry, I though this had worked but on closer inspection it's still off. Right now I have the sum of all observations of a given behavior in a given week, but what I need to know is how many different birds performed those behaviors. For example, in my data week 14 has 12 observations of hatchlings, but only 5 birds accounted for all twelve observations of hatchlings (i.e. I saw the same birds with hatchlings more than once that week). Any suggestions for how to modify your code to get behavior sums only from unique color combos, which represent individual birds? — ElizaBeso000, Jan 13 '21 at 02:14

score 0 · Accepted Answer · answered Jan 13 '21 at 02:22

You can count number of unique Color_Combo for each behaviour in each Week.

Using dplyr this can be done as :

library(dplyr)

sample %>%
  group_by(Week) %>%
  summarise(across(Singing:Empty_Nest, ~n_distinct(Color_Combo[. > 0])))-> result

result

#   Week Singing Building Incubating Nestlings Empty_Nest
#  <int>   <int>    <int>      <int>     <int>      <int>
#1     1       1        0          0         0          0
#2     3       0        0          0         0          0
#3     4       1        1          0         0          0
#4     5       0        0          0         0          0

That did it! Thanks so much, sorry for the confusion. – ElizaBeso000 Jan 13 '21 at 02:33 — ElizaBeso000, Jan 13 '21 at 02:33

Aggregate by a character vector

1 Answers1