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

Convert rows and columns in a category

 

Example

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

UK

Data 1

Instruction 1

UK

Data 2

Instruction 2

UK

Data 3

Instruction 3

USA

Data 4

Instruction 4

USA

Data 5

Instruction 5

India

Data 6

Instruction 6

UAE

Data 7

Instruction 7

UAE

Data 8

Instruction 8

Now we need to convert every column (column B, C) under each category (column A) except the category column into a row, the result is as follows:

UK

Data 1

Data 2

Data 3

UK

Instruction 1

Instruction 2

Instruction 3

USA

Data 4

Data 5

USA

Instruction 4

Instruction 5

India

Data 6

India

Instruction 6

UAE

Data 7

Data 8

UAE

Instruction 7

Instruction 8

Write SPL script:

A

1

=file("country.xlsx").xlsimport@w()

2

=A1.group@u(~(1))

3

=A2.(transpose(~.(~.to(2,))))

4

=A3.(~.(A2.(~(1)(1))(A3.#)|~)).conj()

5

=file("result.xlsx").xlsexport@w(A4)

A1 Read the excel file and read it as a sequence composed of sequences 

A2 Group by the first column (country) 

A3 Except for the country column, transpose in each group 

A4 Put the country together and merge 

A5 Export the result to result.xlsx 

The above is the processing method of column to row, and the same is done when switching back (row to column).

 
 •  345 View  •