How to calculate daily means, medians, from weather variables data collected hourly in R?

Question

I have this dataframe, "Data", containing one full year of data collected about every half-hour, but for some days only a few hours of data were collected.

Dates are in the format: 31.01.2010 00:30 (all in one cell) Variables are: Temperature, humidity, PM10, windspeed, etc.

First question: How can I calculate the daily means, medians, max, min, values of these variables, so I can test each of them in further analysis such as survival analysis with GAM),instead of the hourly/half-hourly data?

Obviously, the calculated daily average/median should be assigned to its corresponding date.

Second question: the DATES column contains both date and time together, separated by one space in the same cell. in R, its type is 'Factor' and I cannot do any calculations, because the error "dates" is missing, appears.

My guess is that I need to convert it first from Factor into date/time so it can be recognized and then to calculate means/medians. But how do I do this?

Can you please indicate what would be the arguments/functions to use?

I think that I have solved the conversion of date from 'Factor' to POSIXlt: I used the function strptime (Data$DATES, format="%d.%m.%Y %H:%M") and now $DATES are recognized as POSIXlt, format "2010-01-01 00:00:00" ....

But I still need to find the function that calculates daily means or averages or medians or whatever.

Answer 1

First, convert your time series into a xts object. Then compute the data you want using xts functions such as apply.daily() See, the xts vignette here.

I feel that the following snippet should work:

# Load library xts
require(xts)

# Create example dataframe 
datetime <- c('31.01.2010 00:30', '31.01.2010 00:31', '31.01.2010 10:32', '01.02.2010 10:00', '01.02.2010 11:03', '01.03.2011 08:09', '01.03.2011 21:00', '01.03.2011 22:00')
value <- c(1.5, 2, 2.5, 7, 3.5, 9, 4.5, 7.5)
df <- data.frame(datetime, value)

# Create xts object
df.xts <- as.xts(df[,2], order.by=as.Date(df[,1], format='%d.%m.%Y %H:%M'))

# Daily mean
d.mean <- apply.daily(df.xts, mean)

# Daily median
d.median <- apply.daily(df.xts, median)

# Daily min
d.min <- apply.daily(df.xts, min)

# Daily max
d.max <- apply.daily(df.xts, max)

(alternatively, see RFiddle)

Answer 2

There are several parts to the problem. Before calculating the median statistics, you need to massage the dataframe so that it has the appropriate types.

For these explanations I'm going to assume you have a dataframe named dt.

Part 1: Converting the datatypes of the dataframe

date factor to datetime StackOverflow

datetime POSIXct conversion StackOverflow

First you need to convert the Date column from the factor type to the datetime type.

dt$Date <- strptime(x = as.character(dt$Date),
                    format = "%d.%m.%Y %H:%M")

dt$date_alt <- as.POSIXct(dt$date_alt) # convert the format of datetime to allow use with ddply

Then, since I'm assuming you want the median statistics by day-month-year, not including the time, we'll need to extract that info. You'll want to put it in a new column to preserve the times.

dt$date_alt <- strptime(x = as.character(dt$Date),
       format = "%d.%m.%Y")

Part 2: Calculating summary statistics grouped by a particular field

Now that we have the dataframe looking the way we want it, you can calculate the average statistics grouped by the day-month-year, which in our case is the date_alt column.

The plyr package provides a really nice funciton for doing this: ddply

library(plyr) # need this library for the plyr call

summ <- ddply(dt, .(date_alt), summarize, 
              med_temp = median(!is.na(Temperature)),
              mean_temp = mean(!is.na(Temperature)), # you can also calc mean if you want
              med_humidity = median(!is.na(humidity)),
              med_windspeed = median(!is.na(windspeed))
              # etc for the rest of your vars
          )

Breaking down the ddply call:

ddply cookbook explanation

ddply is essentially a function which acts over a dataframe. Here's a breakdown of the arguments to the function call:

1. dt -- the name of the dataframe you want to iterate over
2. .(date_alt) -- the names of the columns you want to group by. Conceptually, this splits the dataframe up into a bunch of subdataframes whose rows consist of rows from the original dataframe which share the same values in the columns listed in the parentheses.
3. summarize -- this tells the ddply call that you want to calculate aggregate statistics on the subdataframes
4. med_temp = median(Temperature) and all similar lines -- defines a column in the result data frame. this says that you want the new dataframe to have a column called med_temp that contains the median(Tempurature) results for each sub-dataframe. Keep in mind that instead of median you can use whatever function you want for aggregate values.