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

Convert row-column upper classification

 

Example1:Column to row

There is an Excel file csv.csv, the data is as follows:

C1

C2

C3

Meat

oil

Vegetable

Meat

Vegetable

oil

Meat

oil

Vegetable

8

6

4

18

62

24

11

12

13

In the csv file, N pieces of data are placed horizontally. The first row is the value of the country field of each data, the second row is the other column names of each data, and the third row is the value corresponding to each column. Now we need to organize the file into standardized line data, and the results are as follows:

country

Meat

Vegetable

oil

C1

8

4

6

C2

18

62

24

C3

11

13

12

Write SPL script:

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)

A1 Read csv data into text

A2 Split the text into a sequence of sequences 

A3 Transpose the sequence of the sequence, complete the first column of countries

A4 Create empty sequence table (country,cate,value) 

A5 Fill in the data of A3 in the sequence table one by one

A6 Use pivot function to turn rows to columns 

A7 Export the result to result.csv

 

Example2:Row to column

There is an Excel file csv.csv, the data is as follows:

country

Meat

Vegetable

oil

C1

8

4

6

C2

18

62

24

C3

11

13

12

The first row is the column name, which is the country and other information columns. Now we need to organize the file into the following form:

C1

C2

C3

Meat

Vegetable

oil

Meat

Vegetable

oil

Meat

Vegetable

oil

8

4

6

18

62

24

11

13

12

Write SPL script:

A

1

=file("csv.csv").import@ct()

2

=A1.pivot@r(country;cate,value;Meat,Vegetable,oil)

3

=A2.group(country).(~.run(country=if(#==1,country,""))).conj()

4

=transpose(A3.(#1|#2|#3)).concat@nc()

5

=file("result.csv").write(A4)

A1 Read csv data

A2 Columns to rows, and the column names Meat, Vegetable, and oil are used as the values of cate, and the corresponding values of the original Meat, Vegetable, and oil columns are used as the data in the value column.

A3 Group by country, and merge each group that is not the first row of countries with empty values

A4 Combine the columns to become a sequence of sequences. After transposing the sequence of the sequence, it will be converted into text according to the comma and carriage return.

A5 Write the result text of A4 to result.csv

 
 •  399 View  •