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