...

How to Split Multiple Long Rows of Different Structures and Reorganize Them according to the Specified Format

 
csv(4) row-to-column transposition(2)

Problem description & analysis

The following is data in CSV file csv.csv:

undefined

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:

undefined

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.

Q & A Collection

https://stackoverflow.com/questions/63698177/how-to-split-a-long-row-by-specific-range-and-add-it-as-a-new-rows-excel


csv(4) row-to-column transposition(2)
 
 •  164 View  •