...

4.17 Rank members in each group

 
spl-cookbook(224)

Find the ranks of math scores of students in each class:

Class Name Math
1 Tom 85
2 John 92
2 Joan 86
1 Rocky 95
2 Ham 92
1 Kate 83
1 Rose 95
2 Nomy 78
1 Neil 92
2 Jack 86
1 Joe 98

SPL script:

A
1 =T(“score.xlsx”).derive(:RankInClass)
2 =A1.group(Class).run(a=~.ranks@z(Math),~.run(RankInClass=a(#)))

A1 Import data from the source table and add a new column RankInClass.
A2 Group records by class, get ranks of members in each group, and assign the ranks to RankInClass column.

Execution result:

Class Name Math RankInClass
1 Tom 85 5
2 John 92 1
2 Joan 86 3
1 Rocky 95 2
2 Ham 92 1
1 Kate 83 6
1 Rose 95 2
2 Nomy 78 5
1 Neil 92 4
2 Jack 86 3
1 Joe 98 1

 

spl-cookbook(224)
 
 •  170 View  •