How to aggregate a data frame and keep missing values
I have a dataframe (data_test) of bird data where multiple counts of the same species can occur in the same location (ponds or sections). I would like to combine the data so that the total of each species per location should be one total. I am missing values for the coordinates of some locations, but I do not want these records to be excluded from the dataset. Here's the data:
# Create data table
location <- c("pondA","pondA","transect1","pondB","pondB","transect2","pondC","transect3","pondD","transect4")
type <- c("ground","ground","air","ground","ground","air","ground","air","ground","air")
easting <- c(NA,NA,18264,NA,NA,46378,NA,86025,NA,46295)
northing <-c(NA,NA,96022,NA,NA,85766,NA,21233,NA,23090)
species <- c("NOPI","NOPI","SCAU","GWTE","GWTE","RUDU","NOPI","GADW","NOPI","MALL")
count <- c(10,23,50,1,2,43,12,3,7,9)
data_test <- data.frame(location=location,type=type,easting=easting,northing=northing,species=species,count=count)
data_test
When I use the aggregate function, it falls out of the records with missing values to east and north:
aggregate(count ~ species + location + type + easting + northing, data=data_test, FUN=sum)
Results in:
species location type easting northing count
1 GADW transect3 air 86025 21233 3
2 MALL transect4 air 46295 23090 9
3 RUDU transect2 air 46378 85766 43
4 SCAU transect1 air 18264 96022 50
Using na.action = NULL does not work because the summation does not work on east or north fields. I want to:
species location type easting northing count
1 NOPI pondA ground NA NA 33
2 SCAU transect1 air 18264 96022 50
3 GWTE pondB ground NA NA 3
4 RUDU transect2 air 46378 85766 43
5 NOPI pondC ground NA NA 12
6 GADW transect3 air 86025 21233 3
7 NOPI pondD ground NA BA 7
8 MALL transect4 air 46295 23090 9
Any help is greatly appreciated.
source to share
One route data.table
is to use the remaining names withc()
library(data.table)
dt <- as.data.table(data_test)
dt[, .(count = sum(count)), by = c(names(dt)[-6])]
# location type easting northing species count
# 1: pondA ground NA NA NOPI 33
# 2: transect1 air 18264 96022 SCAU 50
# 3: pondB ground NA NA GWTE 3
# 4: transect2 air 46378 85766 RUDU 43
# 5: pondC ground NA NA NOPI 12
# 6: transect3 air 86025 21233 GADW 3
# 7: pondD ground NA NA NOPI 7
# 8: transect4 air 46295 23090 MALL 9
source to share
With aggregate
one option would be converting the grouping columns to "factor" and adding "NA" as one of the levels withaddNA
nm1 <- setdiff(names(data_test), 'count')
data_test[nm1] <- lapply(data_test[nm1], addNA)
aggregate(count~., data_test, FUN=sum)
# location type easting northing species count
#1 transect3 air 86025 21233 GADW 3
#2 pondB ground <NA> <NA> GWTE 3
#3 transect4 air 46295 23090 MALL 9
#4 pondA ground <NA> <NA> NOPI 33
#5 pondC ground <NA> <NA> NOPI 12
#6 pondD ground <NA> <NA> NOPI 7
#7 transect2 air 46378 85766 RUDU 43
#8 transect1 air 18264 96022 SCAU 50
Or using dplyr
library(dplyr)
data_test %>%
group_by_(.dots=nm1) %>%
summarise(count=sum(count))
source to share