RaqForum 22 No.
1 Reply  •  364 View  • 
     ...

Summarize duplicates with combining rows and columns

 

When performing such an operation, the key functions are pivot and groups respectively, which are used to convert columns to rows, summarize the duplicate records, and then convert rows to columns.

Example

There is a csv file csv1.csv, as follows:

..

There is a csv file csv2.csv, as follows:

..

Now we need to merge these two tables by rows and columns and at the same time summarize the duplicates. The results are as follows:

..

Write the SPL script:

A

1

=file("csv1.csv").import@tc()

2

=file("csv2.csv").import@tc()

3

=A1.pivot@r(id;col,val)

4

=A2.pivot@r(id;col,val)

5

=(A3|A4).groups(id,col;sum(val):val)

6

=A5.pivot(id;col,val)

7

=file("result.csv").export@ct(A6)

A1 Read data from csv 

A2 Read data from csv

A3 Convert columns to rows

A4 Convert columns to rows

A5 Summarize the duplicates. When there are multiple sets of data, use conj function to merge them; here | is able to merge two sets

A6 Convert the summarized result from rows to columns

A7 Export the result to result.csv


 
 •  364 View  •