0

I have a 3-column table with plots, species and occurrences. I would like to summarise the data in that individual occurrences are grouped by species within plots. I have tried the following code:

aggregate(occurrence ~ species, AbundTGLMSOn, FUN=sum)

but this groups by species only and not species within plots, to then look like this

   species occurrence
1   albleb          1
2   albodo         17
3   antgha          1
4   apovil         65
5   artlak          3
6   briret         18

Any advice on how to do this would be much appreciated.

This is a data:

> dput(AbundTGLMSOn)
structure(list(plot = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 3L, 3L, 
3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 
3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 4L, 4L, 4L, 
4L, 4L, 4L, 4L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 6L, 6L, 6L, 6L, 
6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 
6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 
6L, 6L, 6L, 6L, 7L, 7L, 7L, 7L, 7L, 7L, 7L, 7L, 7L, 7L, 7L, 7L, 
7L, 7L, 7L, 7L, 7L, 7L, 7L, 7L, 7L, 7L, 7L, 7L, 7L, 7L, 7L, 7L, 
7L, 7L, 7L, 7L, 7L, 7L, 7L, 7L, 7L, 7L, 7L, 7L, 7L, 7L, 7L, 7L, 
8L, 8L, 8L, 8L, 8L, 8L, 8L, 8L, 8L, 8L, 8L, 8L, 8L, 8L, 8L, 8L, 
8L, 8L, 8L, 8L, 8L, 8L, 8L, 8L, 8L, 8L, 8L, 8L, 8L, 8L, 8L, 8L, 
8L, 8L, 8L, 8L, 8L, 8L, 8L, 8L, 8L, 8L, 8L, 8L, 8L, 8L, 8L, 8L, 
8L, 8L, 8L, 8L, 8L, 8L, 8L, 8L, 8L, 8L, 8L, 8L, 8L, 8L, 8L, 8L, 
8L, 8L, 8L, 9L, 9L, 9L, 9L, 9L, 9L, 9L, 9L, 9L, 9L, 9L, 9L, 9L, 
9L, 9L, 9L, 9L, 9L, 9L, 9L, 9L, 9L, 9L, 9L, 9L, 9L, 9L, 9L, 9L, 
9L, 9L, 9L, 9L, 9L, 9L, 9L, 9L, 9L, 9L, 9L, 9L, 9L, 9L, 9L, 9L, 
9L, 9L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 
10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 
10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 
10L, 10L, 10L, 10L, 10L, 10L, 11L, 11L, 11L, 11L, 11L, 11L, 11L, 
11L, 11L, 11L, 11L, 11L, 11L, 11L, 11L, 11L, 11L, 11L, 11L, 11L, 
12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 
12L, 12L, 12L, 12L, 12L, 12L, 12L, 13L, 13L, 13L, 13L, 13L, 13L, 
13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 
13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 
13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 
13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 
13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 
13L, 13L, 13L, 13L, 14L, 14L, 14L, 14L, 14L, 14L, 14L, 14L, 14L, 
14L, 14L, 14L, 14L, 14L, 14L, 14L, 14L, 14L, 14L, 14L, 14L, 14L, 
14L, 14L, 14L, 14L, 14L, 14L, 14L, 14L, 14L, 14L, 14L, 14L, 14L, 
14L, 14L, 14L, 14L, 14L, 14L, 14L, 14L, 14L, 14L, 14L, 14L, 14L, 
14L, 14L, 14L, 14L, 14L, 14L, 14L, 14L, 14L, 14L, 14L, 15L, 15L, 
15L, 15L, 15L, 15L, 15L, 15L, 15L, 15L, 15L, 15L, 15L, 15L, 15L, 
15L, 15L, 15L, 15L, 15L, 15L, 15L, 15L, 15L, 15L, 15L, 15L, 15L, 
15L, 15L, 15L, 15L, 15L, 15L, 15L, 15L, 15L), .Label = c("H01", 
"H02", "H03", "H04", "H05", "L01", "L02", "L03", "L04", "L05", 
"O01", "O02", "O03", "O04", "O05"), class = "factor"), species = structure(c(30L, 
14L, 32L, 34L, 32L, 34L, 34L, 32L, 32L, 30L, 32L, 32L, 34L, 41L, 
30L, 30L, 32L, 34L, 34L, 29L, 30L, 30L, 30L, 32L, 32L, 30L, 30L, 
34L, 35L, 30L, 30L, 31L, 27L, 30L, 32L, 30L, 30L, 34L, 34L, 34L, 
32L, 41L, 41L, 41L, 41L, 32L, 32L, 32L, 41L, 40L, 41L, 10L, 41L, 
1L, 39L, 1L, 20L, 32L, 32L, 35L, 14L, 29L, 32L, 39L, 39L, 40L, 
40L, 1L, 40L, 40L, 41L, 22L, 40L, 32L, 32L, 37L, 10L, 10L, 32L, 
10L, 32L, 32L, 34L, 32L, 32L, 10L, 41L, 41L, 41L, 41L, 41L, 32L, 
32L, 40L, 32L, 32L, 10L, 35L, 7L, 32L, 32L, 32L, 32L, 3L, 32L, 
1L, 21L, 30L, 32L, 18L, 32L, 32L, 32L, 32L, 32L, 34L, 34L, 32L, 
34L, 34L, 30L, 30L, 32L, 32L, 32L, 32L, 34L, 24L, 30L, 34L, 18L, 
30L, 32L, 34L, 18L, 34L, 30L, 32L, 34L, 32L, 32L, 34L, 32L, 32L, 
14L, 32L, 32L, 32L, 32L, 34L, 32L, 32L, 32L, 41L, 41L, 32L, 32L, 
4L, 32L, 34L, 34L, 39L, 39L, 41L, 34L, 41L, 32L, 6L, 30L, 34L, 
34L, 39L, 30L, 34L, 30L, 34L, 34L, 30L, 30L, 32L, 34L, 30L, 34L, 
32L, 34L, 32L, 34L, 32L, 32L, 32L, 34L, 34L, 34L, 34L, 1L, 32L, 
32L, 32L, 34L, 34L, 32L, 32L, 34L, 34L, 18L, 32L, 34L, 34L, 24L, 
27L, 31L, 32L, 34L, 34L, 34L, 34L, 35L, 39L, 10L, 25L, 32L, 32L, 
34L, 18L, 30L, 32L, 34L, 34L, 34L, 34L, 34L, 34L, 32L, 30L, 34L, 
34L, 34L, 34L, 41L, 3L, 32L, 30L, 30L, 31L, 30L, 30L, 1L, 30L, 
30L, 32L, 32L, 32L, 32L, 32L, 34L, 1L, 32L, 34L, 32L, 1L, 24L, 
4L, 32L, 32L, 34L, 1L, 32L, 32L, 32L, 34L, 34L, 34L, 41L, 41L, 
34L, 13L, 17L, 32L, 34L, 34L, 34L, 34L, 34L, 34L, 34L, 34L, 41L, 
12L, 34L, 18L, 34L, 34L, 34L, 34L, 34L, 30L, 30L, 9L, 32L, 32L, 
14L, 1L, 14L, 32L, 32L, 32L, 32L, 1L, 26L, 32L, 32L, 11L, 13L, 
32L, 32L, 32L, 32L, 41L, 41L, 23L, 32L, 32L, 34L, 34L, 1L, 4L, 
32L, 41L, 4L, 13L, 32L, 34L, 24L, 41L, 32L, 33L, 30L, 32L, 34L, 
34L, 25L, 24L, 28L, 2L, 7L, 36L, 19L, 7L, 30L, 7L, 12L, 24L, 
30L, 6L, 24L, 30L, 12L, 35L, 7L, 35L, 14L, 28L, 14L, 33L, 28L, 
30L, 30L, 30L, 34L, 34L, 35L, 30L, 32L, 32L, 34L, 30L, 30L, 34L, 
34L, 34L, 32L, 32L, 32L, 34L, 28L, 32L, 32L, 32L, 32L, 32L, 32L, 
32L, 32L, 34L, 32L, 32L, 32L, 32L, 32L, 32L, 34L, 34L, 3L, 32L, 
32L, 34L, 34L, 34L, 16L, 32L, 32L, 32L, 34L, 34L, 34L, 32L, 32L, 
32L, 34L, 32L, 32L, 34L, 34L, 34L, 31L, 34L, 34L, 34L, 29L, 34L, 
34L, 34L, 34L, 34L, 41L, 8L, 34L, 3L, 30L, 34L, 34L, 34L, 34L, 
34L, 34L, 10L, 34L, 34L, 34L, 34L, 34L, 34L, 34L, 34L, 34L, 32L, 
32L, 32L, 24L, 32L, 34L, 32L, 32L, 32L, 32L, 11L, 32L, 32L, 32L, 
32L, 32L, 5L, 32L, 32L, 32L, 38L, 3L, 32L, 32L, 34L, 3L, 31L, 
32L, 34L, 34L, 6L, 13L, 31L, 32L, 32L, 32L, 32L, 32L, 34L, 34L, 
13L, 15L, 32L, 32L, 32L, 32L, 34L, 34L, 34L, 34L, 34L, 34L, 34L, 
32L, 34L, 34L, 34L, 34L, 34L, 1L, 32L, 7L, 11L, 32L, 32L, 32L, 
24L, 32L, 32L, 32L, 30L, 32L, 32L, 32L, 32L, 15L, 32L, 34L, 18L, 
32L, 32L, 32L, 34L, 7L, 7L, 34L, 32L, 32L, 34L, 7L, 35L, 7L, 
12L, 34L, 30L, 34L), .Label = c("apovil", "briret", "cansub", 
"carsph", "catspa", "dalcul", "dallan", "dalnig", "dilobo", "diptub", 
"elltom", "flesoo", "garcow", "garobt", "garsoo", "greeri", "halcor", 
"irvoli", "lancor", "lopdup", "mancal", "memscu", "milleu", "mitrot", 
"morcor", "ochint", "parama", "pavtom", "phyemb", "ptemac", "rotwit", 
"shoobt", "shorox", "shosia", "sinsia", "stegut", "strnux", "symrac", 
"syz001", "terche", "xylxyl"), class = "factor"), occurrence = c(1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L)), .Names = c("plot", "species", "occurrence"
), class = "data.frame", row.names = c(NA, -551L))
tabtimm
  • 411
  • 3
  • 6
  • 17
  • You could try `aggregate(occurrence ~ species + plot, AbundTGLMSOn, FUN=sum)` but this gives a data frame with 140 rows, not 15. Are you sure it should be 15 rows? – Rui Barradas Oct 19 '17 at 10:34
  • That was my mistake about the 15 rows, your solution works, thank you. Is there a way to prevent that an additional column of numbers is being produced, i.e. that a 3-column table is retained? – tabtimm Oct 19 '17 at 11:04
  • In this case, I don't think so, no. Youa have three columns in the `aggregate` instruction so the result has those 3 columns. You can retain only the columns you want after, using standard subsetting ways. – Rui Barradas Oct 19 '17 at 11:24

1 Answers1

0

You are aggregating by species only, if you want to group by species within plots you must use both variables in the rhs of the formula. Note that the two commands below give the same results, the only difference is the way the results are presented.

sp1 <- aggregate(occurrence ~ species + plot, AbundTGLMSOn, FUN=sum)
head(sp1)
#  species plot occurrence
#1  garobt  H01          1
#2  parama  H01          1
#3  phyemb  H01          1
#4  ptemac  H01         14
#5  rotwit  H01          1
#6  shoobt  H01         10

sp2 <- aggregate(occurrence ~ plot + species, AbundTGLMSOn, FUN=sum)
head(sp2)
#  plot species occurrence
#1  H03  apovil          3
#2  L01  apovil          1
#3  L03  apovil          2
#4  L04  apovil          3
#5  L05  apovil          3
#6  O05  apovil          1
Rui Barradas
  • 70,273
  • 8
  • 34
  • 66