counting repetitions for uncertain columns

Example

There is an Excel file mat.xlsx, part of the data is as follows:

The range of numbers is from 1 to 10. To calculate the number of occurrences of each number in each column, the results are as follows (a total of 10 rows, corresponding to numbers 1 to 10, and each column is the number of times that number appears in the corresponding column):

Write SPL script:

A1 Read excel file

A2 Loop each column and count the number of repeated occurrences of each value in each column (group@n, group by sequence number, use count to find the number of repeated occurrences of each number, @b only returns aggregated columns)

A3 Transposition, row-to-column

A4 Export the result to result.xlsx

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store