0

I have data in date and time format dd/mm/yyy hh:mm:ss, that is minute by minute, i.e. 01/01/2017 09:30:00 in one cell, followed by 01/01/2017 09:31:00. However, I need the data to be in 5 minute intervals, meaning it will look like; 01/01/2017 09:30:00, 01/01/2017 09:35:00.

I also need this new 5 minute interval value, 01/01/2017 09:35:00, to take the median of the minutes that between 09:30 and 09:35. Any help is appreciated!

Ronan Dunne
  • 1
  • 1

1 Answers1

0

You can do it in one line by using trs function from FQOAT, just like:

#mean    
df_5mins=trs(df, "5 mins")
#median
aqi_5mins_mid=trs(aqi, "5 mins", fun="median")

Below is a repeatable example:

library(foqat)
head(aqi)
#                Time        NO     NO2       CO SO2      O3
#1 2017-05-01 01:00:00 0.0376578 2.79326 0.256900  NA 56.5088
#2 2017-05-01 01:01:00 0.0341483 2.76094 0.254692  NA 57.0546
#3 2017-05-01 01:02:00 0.0310285 2.65239 0.265178  NA 57.6654
#4 2017-05-01 01:03:00 0.0357016 2.60257 0.269691  NA 58.7863
#5 2017-05-01 01:04:00 0.0337507 2.59527 0.273395  NA 59.0342
#6 2017-05-01 01:05:00 0.0238120 2.57260 0.276464  NA 59.2240

#mean
aqi_5mins=trs(aqi, "5 mins")
head(aqi_5mins)
#                 Time         NO      NO2        CO SO2       O3
#1 2017-05-01 01:00:00 0.03445738 2.680886 0.2639712  NA 57.80986
#2 2017-05-01 01:05:00 0.02261826 2.623464 0.2723840  NA 60.00010
#3 2017-05-01 01:10:00 0.02502082 2.570958 0.2696268  NA 59.66690
#4 2017-05-01 01:15:00 0.03375518 2.247818 0.2895834  NA 59.30200
#5 2017-05-01 01:20:00 0.03193006 2.098754 0.2943894  NA 59.18252
#6 2017-05-01 01:25:00 0.03166350 1.932102 0.2931572  NA 60.08012

#median
aqi_5mins_mid=trs(aqi, "5 mins", fun="median")
head(aqi_5mins_mid)
#                 Time        NO     NO2       CO SO2      O3
#1 2017-05-01 01:00:00 0.0341483 2.65239 0.265178  NA 57.6654
#2 2017-05-01 01:05:00 0.0229326 2.57260 0.274745  NA 60.1708
#3 2017-05-01 01:10:00 0.0250651 2.58947 0.266071  NA 59.7058
#4 2017-05-01 01:15:00 0.0342059 2.23572 0.291460  NA 59.3507
#5 2017-05-01 01:20:00 0.0314651 2.11591 0.295807  NA 59.1917
#6 2017-05-01 01:25:00 0.0326181 1.93464 0.297308  NA 60.0279

Here, the value of 2017-05-01 01:00 is the mean (median) of the minutes that between 01:00 and 01:04 (5 mins). If you want the value of 2017-05-01 01:04 is the mean (median) of the minutes that between 01:00 and 01:04 (5 mins), you just need to shift the timestamps. Such as:

aqi_5mins$Time=c(NA, aqi_5mins$Time[1:(nrow(aqi_5mins)-1)]
TichPi
  • 146
  • 5