# Average calculation

I'm testing the influence of the time period on the end result of my calculations. To do this, I'm using a for loop, that is detailed below. In the current state of affairs, this code works well, but it takes more than 10 minutes to go through my complete data set. I believe the use of apply instead of a loop could speed up the process, but I can't work out how to do this. Some assistance would be more than welcome.

  ## result vector
HLClist<-vector()
Ts=3600

for(i in 1:length(TimeSpan[,1])){

StartTime=TimeSpan[i,]
EndTime=TimeSpan[i,]+TimeInterval

Xbis<-Choixintervalle(X,StartTime,EndTime)
Xtierce <- resampleDF(Xbis, Ts)
HLC<-CalcAverage(Xtierce$Ph,Xtierce$Ti,Xtierce$Te) HLC<-HLC[length(HLC)] HLClist<-append(HLClist,HLC) }  Where • TimeSpan is a list that contains all the startimes (format : double), defined as follows: InitTime <-as.POSIXct("16/02/2014 0:00", format="%d/%m/%Y %H:%M") FinalTime <-as.POSIXct("16/03/2014 0:00", format="%d/%m/%Y %H:%M") TimeInterval <-144*3600 SEndTime <- FinalTime - TimeInterval TimeSpan<-data.frame(seq(InitTime, SEndTime, by=3600))  • TimeInterval is the number of seconds between Start and endtime (format : double) • X is the dataframe containing all my data: X t Ph Elec Sol Ti Te DHW 1 16/02/2014 0:00 0.0000 612 0.0 22.70 4.600000 0.0000 2 16/02/2014 0:05 0.0000 612 0.0 22.70 4.600000 0.0000 3 16/02/2014 0:10 0.0000 516 0.0 22.79 4.600000 0.0000 4 16/02/2014 0:15 0.0000 480 0.0 22.70 4.600000 0.0000 5 16/02/2014 0:20 0.0000 540 0.0 22.70 4.600000 0.0000 6 16/02/2014 0:25 0.0000 528 0.0 22.60 4.600000 0.0000 7 16/02/2014 0:30 0.0000 492 0.0 22.60 4.600000 0.0000 8 16/02/2014 0:35 0.0000 528 0.0 22.50 4.600000 0.0000 9 16/02/2014 0:40 0.0000 492 0.0 22.49 4.600000 0.0000 10 16/02/2014 0:45 0.0000 456 0.0 22.43 4.600000 0.0000 11 16/02/2014 0:50 0.0000 480 0.0 22.50 4.600000 0.0000 12 16/02/2014 0:55 0.0000 540 0.0 22.50 4.600000 0.0000 13 16/02/2014 1:00 0.0000 528 0.0 22.46 5.270000 0.0000 14 16/02/2014 1:05 0.0000 516 0.0 22.45 5.170000 0.0000 15 16/02/2014 1:10 0.0000 552 0.0 22.45 5.070000 0.0000 16 16/02/2014 1:15 0.0000 480 0.0 22.40 4.980000 0.0000 17 16/02/2014 1:20 0.0000 420 0.0 22.40 4.900000 0.0000 18 16/02/2014 1:25 0.0000 504 0.0 22.34 4.920000 0.0000 19 16/02/2014 1:30 0.0000 408 0.0 22.30 5.000000 0.0000 20 16/02/2014 1:35 0.0000 468 0.0 22.21 5.000000 0.0000 21 16/02/2014 1:40 0.0000 540 0.0 22.20 5.000000 0.0000 22 16/02/2014 1:45 0.0000 276 0.0 22.09 5.020000 0.0000 23 16/02/2014 1:50 0.0000 252 0.0 21.99 5.080000 0.0000 24 16/02/2014 1:55 0.0000 312 0.0 21.90 5.020000 0.0000 25 16/02/2014 2:00 0.0000 336 0.0 21.89 5.070000 0.0000 26 16/02/2014 2:05 0.0000 312 0.0 21.79 5.040000 0.0000 27 16/02/2014 2:10 0.0000 264 0.0 21.70 5.150000 0.0000 28 16/02/2014 2:15 0.0000 300 0.0 21.67 5.200000 0.0000 29 16/02/2014 2:20 0.0000 264 0.0 21.57 5.200000 0.0000 30 16/02/2014 2:25 0.0000 264 0.0 21.50 5.200000 0.0000 31 16/02/2014 2:30 0.0000 360 0.0 21.46 5.200000 0.0000 32 16/02/2014 2:35 0.0000 360 0.0 21.40 5.150000 0.0000 33 16/02/2014 2:40 0.0000 264 0.0 21.35 5.100000 0.0000 34 16/02/2014 2:45 0.0000 252 0.0 21.30 5.100000 0.0000 35 16/02/2014 2:50 0.0000 444 0.0 21.24 5.100000 0.0000 36 16/02/2014 2:55 0.0000 372 0.0 21.20 5.100000 0.0000 37 16/02/2014 3:00 0.0000 372 0.0 21.11 5.100000 0.0000 38 16/02/2014 3:05 0.0000 420 0.0 21.01 5.180000 0.0000 39 16/02/2014 3:10 0.0000 324 0.0 21.00 5.120000 0.0000 40 16/02/2014 3:15 0.0000 300 0.0 21.00 5.020000 0.0000 41 16/02/2014 3:20 0.0000 420 0.0 20.91 5.000000 0.0000 42 16/02/2014 3:25 0.0000 312 0.0 20.90 4.840000 0.0000 43 16/02/2014 3:30 0.0000 300 0.0 20.80 4.880000 0.0000 44 16/02/2014 3:35 0.0000 384 0.0 20.80 4.820000 0.0000 45 16/02/2014 3:40 0.0000 324 0.0 20.79 4.800000 0.0000 46 16/02/2014 3:45 0.0000 324 0.0 20.70 4.880000 0.0000 47 16/02/2014 3:50 0.0000 432 0.0 20.70 4.980000 0.0000 48 16/02/2014 3:55 0.0000 420 0.0 20.66 5.000000 0.0000 49 16/02/2014 4:00 0.0000 336 0.0 20.60 4.920000 0.0000 50 16/02/2014 4:05 0.0000 372 0.0 20.60 4.900000 0.0000 51 16/02/2014 4:10 0.0000 384 0.0 20.56 4.980000 0.0000 52 16/02/2014 4:15 0.0000 276 0.0 20.50 4.920000 0.0000 53 16/02/2014 4:20 0.0000 276 0.0 20.45 4.900000 0.0000 54 16/02/2014 4:25 0.0000 396 0.0 20.40 4.900000 0.0000 55 16/02/2014 4:30 0.0000 288 0.0 20.40 4.900000 0.0000 56 16/02/2014 4:35 0.0000 276 0.0 20.33 4.900000 0.0000 57 16/02/2014 4:40 0.0000 444 0.0 20.30 4.800000 0.0000 58 16/02/2014 4:45 0.0000 348 0.0 20.30 4.800000 0.0000 59 16/02/2014 4:50 0.0000 372 0.0 20.30 4.700000 0.0000 60 16/02/2014 4:55 0.0000 456 0.0 20.21 4.500000 0.0000 61 16/02/2014 5:00 0.0000 384 0.0 20.20 4.480000 0.0000 62 16/02/2014 5:05 0.0000 324 0.0 20.20 4.370000 0.0000 63 16/02/2014 5:10 0.0000 360 0.0 20.10 4.300000 0.0000 64 16/02/2014 5:15 0.0000 348 0.0 20.10 4.300000 0.0000 65 16/02/2014 5:20 0.0000 324 0.0 20.10 4.330000 0.0000 66 16/02/2014 5:25 0.0000 336 0.0 20.08 4.400000 0.0000 67 16/02/2014 5:30 0.0000 360 0.0 20.00 4.400000 0.0000 68 16/02/2014 5:35 0.0000 312 0.0 20.00 4.400000 0.0000 69 16/02/2014 5:40 0.0000 384 0.0 20.00 4.400000 0.0000 70 16/02/2014 5:45 0.0000 372 0.0 20.00 4.440000 0.0000 71 16/02/2014 5:50 0.0000 360 0.0 19.96 4.500000 0.0000 72 16/02/2014 5:55 0.0000 480 0.0 19.90 4.500000 0.0000 73 16/02/2014 6:00 0.0000 384 0.0 19.90 4.540000 0.0000 74 16/02/2014 6:05 0.0000 312 0.0 19.90 4.520000 0.0000 75 16/02/2014 6:10 0.0000 396 0.0 19.84 4.400000 0.0000 76 16/02/2014 6:15 0.0000 324 0.0 19.80 4.360000 0.0000 77 16/02/2014 6:20 0.0000 312 0.0 19.80 4.260000 0.0000 78 16/02/2014 6:25 0.0000 360 0.0 19.80 4.200000 0.0000 79 16/02/2014 6:30 0.0000 372 0.0 19.80 4.200000 0.0000 80 16/02/2014 6:35 0.0000 312 0.0 19.71 4.200000 0.0000 81 16/02/2014 6:40 0.0000 324 0.0 19.70 4.150000 0.0000 82 16/02/2014 6:45 0.0000 444 0.0 19.70 4.150000 0.0000 83 16/02/2014 6:50 0.0000 408 0.0 19.69 4.150000 0.0000 84 16/02/2014 6:55 0.0000 420 0.0 19.60 4.050000 0.0000 85 16/02/2014 7:00 0.0000 408 0.0 19.60 4.000000 0.0000 86 16/02/2014 7:05 0.0000 312 0.0 19.60 4.060000 0.0000 87 16/02/2014 7:10 0.0000 384 0.0 19.60 4.040000 0.0000 88 16/02/2014 7:15 0.0000 360 0.0 19.57 3.940000 0.0000 89 16/02/2014 7:20 0.0000 288 0.0 19.50 3.840000 0.0000 90 16/02/2014 7:25 0.0000 408 0.0 19.50 3.800000 0.0000 91 16/02/2014 7:30 0.0000 336 11.5 19.50 3.800000 0.0000 92 16/02/2014 7:35 0.0000 300 32.6 19.50 3.800000 0.0000 93 16/02/2014 7:40 0.0000 360 67.6 19.45 3.730000 0.0000 94 16/02/2014 7:45 0.0000 360 100.7 19.40 3.630000 0.0000 95 16/02/2014 7:50 0.0000 408 125.9 19.40 3.750000 0.0000 96 16/02/2014 7:55 0.0000 516 142.2 19.40 3.800000 0.0000 97 16/02/2014 8:00 0.0000 552 162.6 19.47 3.950000 0.0000 98 16/02/2014 8:05 0.0000 576 1386.7 19.50 4.000000 0.0000 99 16/02/2014 8:10 0.0000 1032 1550.6 19.57 4.090000 0.0000 100 16/02/2014 8:15 0.0000 1620 1705.0 19.64 4.100000 0.0000 101 16/02/2014 8:20 0.0000 1236 1846.5 19.70 4.100000 0.0000 102 16/02/2014 8:25 0.0000 876 1976.9 19.70 4.300000 0.0000 103 16/02/2014 8:30 0.0000 912 2097.2 19.70 4.300000 0.0000 104 16/02/2014 8:35 0.0000 756 2206.5 19.80 4.390000 0.0000 105 16/02/2014 8:40 0.0000 780 2307.5 19.81 4.490000 0.0000 106 16/02/2014 8:45 0.0000 912 2398.9 19.90 4.500000 0.0000 107 16/02/2014 8:50 0.0000 816 2483.3 19.90 4.600000 2357.5261 108 16/02/2014 8:55 0.0000 732 2559.9 19.93 4.600000 5087.2931 109 16/02/2014 9:00 0.0000 744 2628.5 20.00 4.700000 0.0000 110 16/02/2014 9:05 0.0000 684 2689.3 20.00 4.890000 0.0000 111 16/02/2014 9:10 0.0000 636 2741.2 20.05 4.900000 0.0000 112 16/02/2014 9:15 0.0000 372 2798.5 20.10 4.910000 0.0000 113 16/02/2014 9:20 0.0000 240 2872.2 20.10 5.010000 0.0000 114 16/02/2014 9:25 0.0000 240 2947.3 20.10 5.110000 0.0000 115 16/02/2014 9:30 0.0000 204 3017.2 20.17 5.200000 0.0000 116 16/02/2014 9:35 0.0000 216 3077.6 20.20 5.220000 0.0000 117 16/02/2014 9:40 0.0000 228 3135.5 20.20 5.400000 0.0000 118 16/02/2014 9:45 0.0000 192 3192.8 20.20 5.430000 0.0000 119 16/02/2014 9:50 0.0000 132 3241.5 20.20 5.530000 0.0000 120 16/02/2014 9:55 0.0000 108 3287.1 20.20 5.630000 12904.3533 121 16/02/2014 10:00 0.0000 60 3330.0 20.20 5.700000 9554.1846 122 16/02/2014 10:05 0.0000 72 3369.2 20.20 5.730000 1116.7229 123 16/02/2014 10:10 0.0000 36 3405.4 20.18 5.840000 2233.4458 124 16/02/2014 10:15 0.0000 36 2983.5 20.10 5.940000 5831.7750 125 16/02/2014 10:20 0.0000 0 2428.1 20.10 6.000000 5583.6144 126 16/02/2014 10:25 0.0000 0 2237.7 20.10 6.000000 0.0000 127 16/02/2014 10:30 0.0000 12 2269.7 20.10 6.050000 0.0000 128 16/02/2014 10:35 0.0000 0 2738.2 20.10 6.100000 0.0000 129 16/02/2014 10:40 0.0000 0 3422.9 20.10 6.100000 0.0000 130 16/02/2014 10:45 0.0000 0 3565.9 20.10 6.160000 0.0000 131 16/02/2014 10:50 0.0000 0 3576.7 20.10 6.260000 0.0000 132 16/02/2014 10:55 0.0000 0 3584.8 20.10 6.360000 0.0000 133 16/02/2014 11:00 0.0000 0 3318.6 20.10 6.340000 0.0000 134 16/02/2014 11:05 0.0000 0 2930.1 20.10 6.360000 0.0000 135 16/02/2014 11:10 0.0000 24 2718.4 20.10 6.530000 0.0000 136 16/02/2014 11:15 0.0000 144 2708.2 20.10 6.670000 0.0000 137 16/02/2014 11:20 0.0000 360 2713.0 20.10 6.560000 0.0000 138 16/02/2014 11:25 0.0000 312 2711.7 20.10 6.500000 0.0000 139 16/02/2014 11:30 0.0000 252 2707.5 20.10 6.580000 0.0000 140 16/02/2014 11:35 0.0000 492 2702.2 20.10 6.510000 0.0000 141 16/02/2014 11:40 0.0000 144 2698.8 20.00 6.940000 0.0000 142 16/02/2014 11:45 0.0000 564 2688.3 20.00 7.260000 0.0000  • Choixintervalle is the following function: Choixintervalle <-function(X,startTime=NA,endTime=NA) { ## set the start time if not specified if(is.na(startTime)){startTime <- as.POSIXct(X[1,1], format="%d/%m/%Y %H:%M")} else{startTime<-as.POSIXct(startTime, format="%d/%m/%Y %H:%M")} ## set the end time if not specified if(is.na(endTime)){ endTime <- as.POSIXct(X[nrow(X),1], format="%d/%m/%Y %H:%M")} else{endTime<-as.POSIXct(endTime, format="%d/%m/%Y %H:%M")} X<-X[(as.POSIXct(X$t,format="%d/%m/%Y %H:%M"))>startTime,]
X<-X[(as.POSIXct(X$t,format="%d/%m/%Y %H:%M"))<endTime,] return(X) }  • ResempleEDF is the following function: resampleDF <- function(X,Ts,startTime = NA, endTime =NA,timeName="t",includeNA=TRUE,quantizeTime=TRUE,meanNaRm=FALSE) { ## Split into periods of length Ts, and take the mean of each period X[,timeName] <-as.POSIXct(X[,timeName], format="%d/%m/%Y %H:%M")-startTime iSplit <- as.integer(X[,timeName]) %/% Ts ## Do the resampling Xres <- aggregate(X, list(iSplit), mean, na.rm=meanNaRm) ## Remove the "Group" column Xres <- Xres[,-1] ## Convert time to POSIXct Corr<- as.integer(Xres[1,timeName]) Xres[,timeName] <- startTime + as.integer(Xres[,timeName])-Corr return(Xres) }  • CalcAverage is the following function: CalcAverage <- function(Q, Ti,Te) { Solution = vector(length(Q), mode="double" ) for (i in 1:length(Q)){ Temp<-sum(Ti[1:i]-Te[1:i]) Heat<-sum(Q[1:i]) #création du vecteur R Solution[i]<-Heat/Temp } return(Solution) }  • Welcome to Code Review! Your current question title describes how the code is structured, but what we really want in a good title is the purpose of the code. You can improve it with a small edit to summarise the task accomplished. Please also see How to get the best value out of Code Review: Asking Questions for guidance on writing good question titles. – Toby Speight Jul 16 '18 at 8:07 • Can you provide example X data using dput or simulating large enough data that we can run the code by simply copying and pasting the code, so wee can see the speed problem? – minem Jul 16 '18 at 8:18 • What is Ts object? – minem Jul 16 '18 at 8:19 • @minem, The X data frame is too big to be posted on line, the best I could do was to include the example code I did. As for Ts, it's the number of hours for which I take the average, format = double, value = 6*3600 – K. De Sloover Jul 16 '18 at 9:05 • @K.DeSloover please check your code if it can be run, because, I currently cant run it, it yields an error in resampleDF  – minem Jul 16 '18 at 9:27 ## 1 Answer # GENERATE data nmax <- TimeSpan[nrow(TimeSpan), ] tSEQ <- seq(as.POSIXct("16/02/2014 0:00", format = "%d/%m/%Y %H:%M"), to = nmax, by = '5 s') n <- length(tSEQ) X <- data.frame(tSEQ) set.seed(42) X <- cbind(X, replicate(6, rnorm(n))) colnames(X) <- c("t", "Ph", "Elec", "Sol", "Ti", "Te", "DHW") head(X)  My approach using data.table: require(data.table) setDT(X) # convert to POSIXct outside of the loop: X[, t := as.POSIXct(t, format = "%d/%m/%Y %H:%M")] setkey(X, t) # adds key for possibly faster subsetting Choixintervalle2 <- function(X, startTime = NA, endTime = NA) { if (is.na(startTime)) { startTime <- X[1, 1] } else { startTime <- as.POSIXct(startTime, format = "%d/%m/%Y %H:%M") } if (is.na(endTime)) { endTime <- X[nrow(X), 1] } else { endTime <- as.POSIXct(endTime, format = "%d/%m/%Y %H:%M") } X <- X[t > startTime & t < endTime] return(X) } resampleDF2 <- function(X, Ts, startTime = NA, endTime = NA, timeName = "t", includeNA = TRUE, quantizeTime = TRUE, meanNaRm = FALSE) { X[[timeName]] <- X[[timeName]] - startTime # column names to be agreagated: j <- c("t", "Ph", "Elec", "Sol", "Ti", "Te", "DHW") # aggregate using data.table: Xres <- X[, lapply(.SD, mean, na.rm = meanNaRm), by = as.integer(X[[timeName]]) %/% Ts, .SDcols = j] ## Remove the "Group" column: set(Xres, j = 1L, value = NULL) Corr <- as.integer(Xres[[timeName]][1]) Xres[[timeName]] <- startTime + as.integer(Xres[[timeName]]) - Corr return(Xres) } CalcAverage2 <- function(Q, Ti, Te) { Solution <- cumsum(Q)/cumsum(Ti - Te) return(Solution) } HLClist2 <- vector() ii <- length(TimeSpan[, 1]) for (i in 1:ii) { StartTime = TimeSpan[i,] EndTime = TimeSpan[i,] + TimeInterval Xbis <- Choixintervalle2(X, StartTime, EndTime) Xtierce <- resampleDF2(Xbis, Ts, StartTime, EndTime) HLC <- CalcAverage2(Xtierce$Ph, Xtierce$Ti, Xtierce$Te)
HLC <- HLC[length(HLC)]
HLClist2 <- append(HLClist2, HLC)
}


Should be much faster, because, the slowest part of your code was aggregate function. It is possible that the code could be speed up even more..., but I think that it should suffice now.

@t3chb0t Mainly I used data.table instead of data.frame for faster aggregation. Also, I converted the dates to PosixCt outside of the loop + some other minor things was changed, but they did not impact the speed a lot.

• Could you explain your solution in more details? What did you do differently besides getting rid of the aggregate? – t3chb0t Jul 16 '18 at 10:45
• @t3chb0t Mainly I used data.table instead of data.frame for faster aggregation. Also, I converted the dates to PosixCt outside of the loop + some other minor things was changed, but they did not impact the speed. – minem Jul 16 '18 at 10:54
• I'd be great if you could edit your answer and add this information to it ;-) – t3chb0t Jul 16 '18 at 10:55