Task description

The following Excel file book1.xlsxhas two columns: Car and Color. Below is part of its data:

We need to rearrange data in the following layout. That is, Use car types as new column names and list all unique colors for each type of car. 

Directions:

1.  Start esProc

Download esProc installation package and free DSK edition license HERE. You will be prompted to load the license file when you run esProc for the first time.

2.Write script in esProc:

Let’s write the code separately for easy viewing:

A1   Importdata of book1.xlsx; @t option enables reading the first row as column headers. 

A2    Group rows by Car and for each group union the car type name and its colors into a sequence; ~.id(Color)gets the unique colors from each group. 

A3   Calculate the maximum length among these sequences. 

A4   Extend each sequence to the maximum length by appending null(s) to it for the convenience of transposition. 

A5   Perform row-to-column transposition over A4’s table. 

A6   Export A5’s result to book2.xlsx; @w option enables writing data as a sequence of sequences. 

3. Press F9 to execute the script. Then you can open book2.xlsxto view the target table. 

【Reference】 car.zip