I am trying to calculate the mean of my results column based on month, year, and WBID. There are several other columns in my dataset that I need to maintain so that I may row bind my aggregated dataset to another dataset with the same columns. Here is an example of my dataset:
library(dplyr)
library(lubridate)
# Sample data on a copy/paste friendly format
monthlymean <- data.frame(
stringsAsFactors = FALSE,
AnalyteName = c("chemx","chemx","chemx",
"chemx","chemx","chemx","chemx","chemx","chemx","chemx","chemx"),
SampleDate = c("2012-01-09","2012-08-14",
"2013-05-21","2014-09-09","2010-12-28","2013-03-28",
"2010-10-12","2010-03-14","2010-09-16","2012-01-21","2010-10-30"),
WBID = c(12345,
12345,12345,12345,12345,12345,12345,12345,12345,12345,12345),
ProjectName = c("Water", "Water", "Water", "Water", "Water", "Water", "Water",
"Water", "Water", "Water", "Water")
day = c(9L, 14L, 21L, 9L, 28L, 28L, 12L, 14L, 16L,21L, 30L),
month = c(1L, 8L, 5L, 9L, 12L, 3L, 10L, 3L, 9L,1L,10L)
year=c(2012,2012,2013,2014,2010,2013,2010,2010,2012,2010),
Result = c(0.23, 0.56, 9.56, 3.67, 2.67, 4.67, 2.56, 3.57, 2.45, 6.92, 2.56)
)
I have tried the following code, but it only returns a results column along with the other variables included in group_by()
monthlymean <- monthlymean %>%
select(AnalyteName, SampleDate, WBID, ProjectName, Result, day, month, year) %>%
group_by(month, year, WBID) %>%
summarise_at(vars(Result), mean, na.rm=TRUE)
For this example I should end up with a dataset with 9 rows and 8 columns.