...

Convert categories into columns

 

Example

The Excel file book1.xlsx has two columns of Car and Color. Part of the data is shown in the figure below:

Now you need to convert the data into the following format: name the Car type as a column, list all the colors of this car and remove the duplicate colors.

Write SPL script:

A

1

=T("E:/car/book1.xlsx")

2

=A1.group(Car).(Car|~.id(Color))

3

=A2.max(~.len())

4

=A2.(~.pad(null,A3))

5

=transpose(A4)

6

=file("E:/car/book2.xlsx").xlsexport@w(A5)

A1 Read in the book1.xlsx file data, the option @t means that the first row is the column name

A2 Group by Car, in each group the Car and its different colors form a sequence, ~.id(Color) means to take the color that is not repeated in this group

A3 Calculate the maximum length of each group sequence

A4 Fill each group sequence with null to the maximum length for transposing

A5 Transpose the ranks of A4

A6 Save A5 to the file book2.xlsx, the option @w means that the written A5 is a sequence composed of sequences

 
 •  292 View  •