Count the Number of Duplicates for Uncertain Columns
Example
Part of the data in Excel file mat.xlsx is as follows:
5 |
10 |
2 |
1 |
9 |
5 |
2 |
10 |
3 |
9 |
7 |
4 |
8 |
6 |
10 |
5 |
3 |
7 |
1 |
1 |
5 |
2 |
5 |
4 |
8 |
7 |
4 |
2 |
7 |
1 |
9 |
9 |
10 |
4 |
8 |
6 |
1 |
7 |
2 |
7 |
10 |
6 |
7 |
8 |
3 |
... |
... |
... |
... |
... |
From 1 to 10, calculate the occurrences of each number in each column, and the results are as follows (there are 10 rows in total, corresponding to numbers from 1 to 10, and each column is the number of times that number appears in the corresponding column):
11 |
10 |
10 |
7 |
11 |
9 |
8 |
7 |
5 |
9 |
5 |
9 |
4 |
9 |
8 |
9 |
15 |
5 |
11 |
7 |
13 |
6 |
11 |
15 |
10 |
11 |
9 |
14 |
12 |
8 |
15 |
11 |
13 |
5 |
9 |
7 |
11 |
7 |
12 |
15 |
11 |
9 |
9 |
12 |
11 |
9 |
12 |
20 |
12 |
12 |
Write SPL script:
A |
|
1 |
=file("mat.xlsx").xlsimport() |
2 |
=A1.fno().(A1.field(~).groups@nb(~;count(~)).(#1)) |
3 |
=transpose(A2) |
4 |
=file("result.xlsx").xlsexport@w(A3) |
A1 Read the data of excel file
A2 Loop through each column and count the occurrence number of each value in each column (group@n function groups the data by sequence number, count is used to calculate the occurrence number of each number, and @b option is used to return only the aggregate columns)
A3 Transpose the rows to columns
A4 Export the result to result.xlsx
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
