...

Grouping by Data Interval

 

Example

There is an Excel file Book1.xlsx, and the data is as follows:

dt

val

2000/1/1

61.47

2000/1/1

19.57

2000/1/1

44.53

2000/1/4

65.98

2000/1/5

142.11

2000/1/6

25.87

2000/1/6

142.11

2000/1/8

44.53

2000/1/9

873.2

2000/1/10

52.89

2000/1/11

82.4

2000/1/11

118.37

2000/1/11

354.8

2000/1/14

90.6

2000/1/15

6.49

2000/1/16

44.53

2000/1/17

33.76

2000/1/18

162.74

The dt column is the date, and the val column is the number. The task is to divide every 5 days into groups and calculate the average value of val in each group. The results are as follows:

dt

avg

2000/1/1

66.73

2000/1/6

227.72

2000/1/11

130.53

2000/1/16

80.34

Write SPL script:

A

1

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

2

>st=A1(1).dt

3

=A1.group@i(if(interval(st,dt)==5,st=dt,false))

4

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

5

=file("result.xlsx").xlsexport@t(A4)

A1 Read the excel file

A2 Set the start time as st, and the initial value is the first dt

A3 Group every 5 days

A4 Calculate the average value of val in each group, and dt is the first datetime of each group

A5 Export results to result.xlsx


 
 •  206 View  •