How to Split Multiple Long Rows of Different Structures and Reorganize Them according to the Specified Format
Problem description & analysis
The following is data in CSV file csv.csv:
A number of (N) records are stored in rows in the CSV file. Row 1 contains Country field values. Row 2 contains names of all the other fields. Row 3 contains values of all the other fields. Now we are trying to reorganize data in the file to make it standard rowwise data. The desired result is as follows:
Solution
We write the following script (p1.dfx) in esProc:
| A |
|
| 1 |
=file("csv.csv").read() |
| 2 |
=A1.split@n(",") |
| 3 |
=transpose(A2).run(if(~(1)=="",~(1)=~[-1](1))) |
| 4 |
=create(country,cate,value) |
| 5 |
>A3.(A4.record(~)) |
| 6 |
=A4.pivot(country;cate,value) |
| 7 |
=file("result.csv").export@ct(A6) |
Explanation:
A1 Read in the CSV data as a string.
A2 Split A1’s string into a sequence of sequences.
A3 Perform row-to-column transposition on A2’s sequence of sequences and add missing values to the Country column.
A4 Create an empty table sequence made up of three fields: country, cate, and value.
A5 Populate A3’s records to A4’s empty table sequence.
A6 Use pivot() function to transpose rows to columns.
A7 Export A6’s result to result.csv.
Execute the program and result.csv is what we desired.
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_Desktop/
SPL Learning Material 👉 https://c.scudata.com
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProcDesktop
Linkedin Group 👉 https://www.linkedin.com/groups/14419406/
