How to sum rows based on multiple conditions - R?

r sum dataframe summary multiple-conditions
Borealis picture Borealis · Mar 8, 2015 · Viewed 35.5k times · Source

I have a dataframe that contains a plot ID (plotID), tree species code (species), and a cover value (cover). You can see there are multiple records of tree species within one of the plots. How can I sum the "cover" field if there are duplicate "species" rows within each plot?

For example, here is some sample data:

# Sample Data
plotID = c( "SUF200001035014", "SUF200001035014", "SUF200001035014", "SUF200001035014", "SUF200001035014", "SUF200046012040",
       "SUF200046012040", "SUF200046012040", "SUF200046012040", "SUF200046012040", "SUF200046012040", "SUF200046012040")
species = c("ABBA",  "BEPA",  "PIBA2", "PIMA",  "PIRE",  "PIBA2", "PIBA2", "PIMA",  "PIMA",  "PIRE",  "POTR5", "POTR5")
cover = c(26.893939,  5.681818,  9.469697, 16.287879,  1.893939, 16.287879,  4.166667, 10.984848, 16.666667, 11.363636, 18.181818,
          13.257576)
df_original = data.frame(plotID, species, cover)

enter image description here

And here is the intended output:

# Intended Output
plotID2 = c( "SUF200001035014", "SUF200001035014", "SUF200001035014", "SUF200001035014", "SUF200001035014", "SUF200046012040",
            "SUF200046012040", "SUF200046012040", "SUF200046012040")
species2 = c("ABBA",  "BEPA",  "PIBA2", "PIMA",  "PIRE",  "PIBA2", "PIMA",  "PIRE",  "POTR5")
cover2 = c(26.893939,  5.681818,  9.469697, 16.287879,  1.893939, 20.454546, 18.651515, 11.363636, 31.439394)
df_intended_output = data.frame(plotID2, species2, cover2)

enter image description here

Answer

ExperimenteR picture ExperimenteR · Mar 8, 2015

Easy with aggregate

aggregate(cover~species+plotID, data=df_original, FUN=sum)

Easier with data.table

as.data.table(df_original)[, sum(cover), by = .(plotID, species)]

Related questions

How to sum data.frame column values?

I have a data frame with several columns; some numeric and some character. How to compute the sum of a specific column? I’ve googled for this and I see numerous functions (sum, cumsum, rowsum, rowSums, colSums, aggregate, apply) but …

Read More
Efficiently sum across multiple columns in R

I have the following condensed data set: a<-as.data.frame(c(2000:2005)) a$Col1<-c(1:6) a$Col2<-seq(2,12,2) colnames(a)<-c("year","Col1","Col2") for (i in 1:2){ a[[paste("Var_", i, sep="")]]<-i*a[[paste("…

Read More
ignore NA in dplyr row sum

is there an elegant way to handle NA as 0 (na.rm = TRUE) in dplyr? data <- data.frame(a=c(1,2,3,4), b=c(4,NA,5,6), c=c(7,8,9,NA)) data %>% mutate(sum = a + b + c) a b c sum 1 4 7 12 2 NA 8 NA 3 5 9 17 4 6 …

Read More