RaqForum 28 No.
366 View  • 
     ...

Column-to-Row Transposition

 
column-to-row transposition(2)

Problem description & analysis

We have an Excel file Book1.xlsx:

A

B

C

D

1

Name

Fruit1

Fruit2

Fruit3

2

Alice

apple

banana

orange

3

Bob

apple

pear

plum

4

Cate

banana

pear

plum

We are trying to transpose columns to rows. Below is the expected result:

A

B

C

1

Fruit

Name1

Name2

2

apple

Alice

Bob

3

banana

Alice

Cate

4

orange

Alice

5

pear

Bob

Cate

6

plum

Bob

Cate

Solution

Write the following script p1.dfx in esProc:

A

1

=file("Book1.xlsx").xlsimport@t()

2

=A1.pivot@r(Name;Fruit,Cate)

3

=A2.group(Cate).run(~=~.Cate|~.(Name))

4

="Fruit"|A3.max(~.len()-1).("Name"/~)

5

=file("result.xlsx").xlsexport@w([A4]|A3)

Explanation:

A1   Import the Excel file as a table sequence.

A2   Perform column-to-row transposition.

A3   Group A2’s table sequence by Cate, get the Cate value for each group and concatenate it with the Name value.

A4   Get the column headers for the result table according to the maximum length of the Name value.

A5   Concatenate the result column headers with the result detailed data and export the result table to result.xlsx.

Q & A Collection

https://stackoverflow.com/questions/63600495/how-do-i-make-a-new-table-where-the-rows-are-based-on-the-entries-in-another-tab


column-to-row transposition(2)
 
 •  366 View  •