"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 .."

blackduckie RaqForum 28 No.
1 Reply • 410 View • 3 Years ago

Compare Adjacent Rows and Group Data by a Specific Interval

excel(115) group(7) order-based calculation(12)

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