...

Grouping with Adjacent Data

 

Example

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

051000

55.74

055000

61.47


056000

44.53


062000

142.11


081000

142.11

088000

44.53


092000

52.89


095100

118.37

096500

354.8


096700

6.49

099100

44.53


102600

162.74

The task is to group records by the first column and sum the values in the second column. The grouping condition is: if the current record is not empty and the previous record is empty, meanwhile, the first two digits of the record before the previous record and the current record are different, a new group will be created. The calculation results are as follows:

051000

161.74

062000

142.11

081000

186.64

092000

577.08

102600

162.74

Write SPL script:

A

1

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

2

=A1.group@i(if(#1 && #1[-1]==null && left(#1,2)!=left(#1[-2],2)))

3

=A2.new(#1,round(~.sum(#2),2))

4

=file("result.xlsx").xlsexport@(A3)

A1 Read the excel file

A2 Group records according to the condition (the current record is not empty and the previous record is empty, also, the first two digits of the record before the previous record are different from those of the current record)

A3 Calculate the sum of the values in the second column of each group, and the result keeps two decimal places. #1 represents the first value in the first column of each group

A4 Export the result to result.xlsx


 
 •  199 View  •