7.25 Cross Apply operation
Traverse multiple data files to perform Cross Apply operation on a table sequence and a sequence of values, and generate a new table sequence.
One task is to traverse all online learning terminals survey tables about a certain primary school stored in a folder, and calculate the proportion of each type of terminal.
| ID | STUDENT_NAME | TERMINAL |
|---|---|---|
| 1 | Rebecca Moore | Phone |
| 2 | Ashley Wilson | Phone,PC,Pad |
| 3 | Rachel Johnson | Phone,PC,Pad |
| 4 | Emily Smith | Phone,Pad |
| 5 | Ashley Smith | Phone,PC |
| 6 | Matthew Johnson | Phone |
| 7 | Alexis Smith | Phone,PC |
| 8 | Megan Wilson | Phone,PC,Pad |
| … | … | … |
SPL offers A.news() function to perform Cross Apply operation on the table sequence and the sequence of terminals generated through splitting.
SPL script:
| A | B | |
|---|---|---|
| 1 | =directory@ps(“D:/Primary School”) | |
| 2 | for A1 | =T(A2) |
| 3 | =@+=B2.len() | |
| 4 | =B2.news(B2.TERMINAL.split@c(); ID, STUDENT_NAME, ~:TERMINAL) | |
| 5 | =B4.groups(TERMINAL; count(~):Count)|@ | |
| 6 | =B5.groups(TERMINAL;string(sum(Count)/B3, “#.##%”):PERCENTAGE) | |
A1 Recursively traverse the target directory and list all files.
B2 Import Excel format survey files of all classes of all grades by loop.
B3 Calculate the total number of rows in these files, which is the total number of students.
B4 Use news() function to perform Cross Apply operation on the survey table and the sequence of split terminals.
B5 Group B4’s records by terminal, count terminals in each group and concatenate each result to the current cell. Concatenating the final result of B4 to the cell at once may result in insufficient memory.
A6 Group B5’s records by terminal again and calculate each terminal’s proportion.
Execution result:
| TERMINAL | PERCENTAGE |
|---|---|
| PC | 70% |
| Pad | 56.67% |
| Phone | 93.33% |
SPL Official Website 👉 http://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc
SPL Learning Material 👉 http://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/ydhVnFH9
Youtube 👉 https://www.youtube.com/@esProc_SPL
