Compare Adjacent Rows and Group Data by a Specific Interval


Below is part of the Excel file data.xlsx:

dt

val

2000/1/1 0:04

2339

2000/1/1 0:08

8724

2000/1/1 0:33

8712

2000/1/1 0:49

8748

2000/1/1 0:52

3795

2000/1/1 0:58

8089

2000/1/1 1:02

1205

2000/1/1 1:07

2326

2000/1/1 1:32

9059

dt column stores datetimes; val column stores numbers. Task: group rows every 10 minutes and calculate average of val alues in each group. Below is part of the expected result:

dt

avg

2000/1/1 0:04

5531.5

2000/1/1 0:33

8712

2000/1/1 0:49

6877.33

2000/1/1 1:02

1765.5

2000/1/1 1:32

4733.25

It’s convenient to get the task done with esProc. Download esProc installation package and free license file HERE.

1. Write script avg.dfx in esProc:

A

B

1

=file("data.xlsx").xlsimport@t()

/ Import the Excel file

2

>st=A1(1).dt

/ Define the start time,   whose initial value is the first dt value

3

=A1.group@i(if(interval@s(st,dt)>600,st=dt,false))

/ Perform grouping. If the interval   between the current time and the start time is 10 or more minutes, create a   new group

4

=A3.new(dt:dt,round(~.avg(val),2):avg)

/ Calculate average val in each   group where dt is the first time

2. Execute the script and A4 is the expected result.