1

I have a data set from april to october with registered data every 5 minutes per day. I want to get the average temperature and RH of day and night for every day, considering "day" from 7:30 to 18:30 and "night" for the rest of hours, The table looks like this: 

    Date        Time        Temp    RH 
    18/04/2018  00:00:00    21.9    73
    18/04/2018  00:05:00    21.9    73
    18/04/2018  00:10:00    21.8    73
    18/04/2018  00:15:00    21.6    73
    18/04/2018  00:20:00    21.6    72
    18/04/2018  00:25:00    21.5    72
    18/04/2018  00:30:00    21.4    74

And so on till october. I have tried codes from similar questions but for some reason or the other, I always get an error. In one example I saw that there is a column with "AM/PM" values to make this simpler, but then I'd have to create this new column for all the rows. Also tried with "hourly.apply" but it seems that the function doesn't exist.

What I want to obtain is this:

    Date       Time     Temp    RH 
    18/04/2018  day     25.8    80
    18/04/2018  night   17.3    43
    19/04/2018  day     24.2    73
    19/04/2018  night   15.1    42

I typed the code: 

> n=287
> T24_GH111 <- aggregate(GH111[,3],list(rep(1:nrow(GH111%%n+1), each=n, leng=nrow(GH111))),mean)[-1];`

But this will give me the average of 24 hours.

Thanks in advance!

Miriam Hernández
  • 11
  • 2

1 Answers1

0

Let's start with a simple example and create a dateframe with datetimes.

library(lubridate) # for datetime manipulation
# Creating simple example
Datetime <- c(as.POSIXct("2018-04-17 22:00", tz="Europe/Berlin"),
              as.POSIXct("2018-04-18 01:00", tz="Europe/Berlin"),
              as.POSIXct("2018-04-18 10:00", tz="Europe/Berlin"),
              as.POSIXct("2018-04-18 13:00", tz="Europe/Berlin"),
              as.POSIXct("2018-04-18 22:00", tz="Europe/Berlin"),
              as.POSIXct("2018-04-19 01:00", tz="Europe/Berlin")
              )
x <- c(1,3,10,20,2,5)
df <- data.frame(Datetime,x)

Now, we are using local_time() from the lubridate package to define a new day/night variable.

# Getting local time in hours
df$time <- local_time(df$Datetime, units ="hours")
# Setting day night parameter
t1 <- 7.5  # 07:30
t2 <- 18.5 # 18:30
df$dayNight <- ""
idx <- xor(t1 < df$time ,df$time < t2)
df$dayNight[idx] <- "day"
df$dayNight[!idx] <- "night"

To aggregate by day, we need to change the dates for all datetimes < 07:30. Fortunately, we have already set up the local time. So, let's use this for setting up a dummyDate variable. (This will be the resulting Date)

cond <- df$time < t1
# Using dummyDate for aggregate for dayNight values per day
df$dummyDate <- df$Datetime
df$dummyDate[nightCondition] <- df$Datetime[nightCondition] - days(1)
df$dummyDate <- floor_date(df$dummyDate, unit = "day") # flooring date for aggregation
df

             Datetime  x     time dayNight  dummyDate
1 2018-04-17 22:00:00  1 22 hours      day 2018-04-17
2 2018-04-18 01:00:00  3  1 hours      day 2018-04-17
3 2018-04-18 10:00:00 10 10 hours    night 2018-04-18
4 2018-04-18 13:00:00 20 13 hours    night 2018-04-18
5 2018-04-18 22:00:00  2 22 hours      day 2018-04-18
6 2018-04-19 01:00:00  5  1 hours      day 2018-04-18

Now, we have set up all variables to use the aggregate function to calculate the mean of x by dayNight and dummyDate

# Aggregating x value per dummyDate and daynight variables
dfAgg <- aggregate(df[,2], list(Date = df$dummyDate, Time = df$dayNight), mean)
dfAgg

        Date  Time    x
1 2018-04-17   day  2.0
2 2018-04-18   day  3.5
3 2018-04-18 night 15.0
Liv-Con
  • 21
  • 5