Calculate daily average from irregular hourly data using xts

Question

I have two column data set: Date and Ozone. Time series is irregular hourly data, I need to calculate daily average data.

My problem is the final file has only one value instead of whole Ozone column. I have tried several different versions but I cannot solve the problem.

dataset1 <- structure(list(Date = structure(1:3, .Label = c("7/11/2013 18:33",
  "7/11/2013 18:34", "7/11/2013 18:35"), class = "factor"), ozone = c(40.7, 40.4,
  40.9)), .Names = c("Date", "ozone"), row.names = c(NA, 3L), class = "data.frame")
#convert dataframe to xts object
library(xts)
xt1 <- xts(dataset1[,-1],
           order.by = as.POSIXct(dataset1$Date, format= "%m/%d/%Y %H:%M"))
x_updated <- apply.daily(xt1, colMeans)
#convert back the dataset to data.frame
write.csv(as.data.frame(x_updated), file="daily")

Answer 1

Here is the dplyr solution:

library(dplyr)

df2 <- dataset1 %>% 
     mutate(date2 = as.Date(dataset1$Date)) %>% 
     group_by(date2) %>% 
     mutate(avg_ozone = mean(ozone)) %>%
     select(Date, ozone, avg_ozone)

Answer 2

# Get 'day' from 'datetime'
# dataset1$day = as.POSIXct(dataset1$Date, format='%d/%m/%Y') # Apparently this caused problem
dataset1$day = do.call(rbind, strsplit(as.character(dataset1$Date)," "))[,1]

# Get Daily Averages
daily_averages = aggregate(ozone~day, dataset1, mean)

Answer 3

There's nothing in your reproducible example that suggests to me that there would be a problem with your output (after I corrected the typos in the format argument to your as.POSIXct call--it was originally "%m\%d\%y %H:%M"). That said, your example only had one day of data, so it should only have one line of output in the file.

Here's a reproducible example that shows the "daily" file will have more than one observation.

# re-create example data
set.seed(21)
Data <- data.frame(Date=seq(ISOdate(2017, 1, 1), ISOdate(2017, 1, 3), by="hours"))
Data$Ozone <- rnorm(NROW(Data))
Data$Date <- format(Data$Date, "%m/%d/%Y %H:%M")
# convert to xts
library(xts)
xt1 <- xts(Data[,-1], as.POSIXct(Data$Date, format="%m/%d/%Y %H:%M"))
x_updated <- apply.daily(xt1, colMeans)
# convert to data.frame
write.csv(as.data.frame(x_updated))
# "","V1"
# "2017-01-01 23:00:00",0.038108046611944
# "2017-01-02 23:00:00",-0.0380170075916872
# "2017-01-03 12:00:00",0.612294079665861