行列转换

例题描述和简单分析

Excel文件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

需要行列转换,结果如下: 


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

解法及简要说明

在集算器中编写脚本p1.dfx,如下所示: 


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)

简要说明:

A1   Excel读成序表

A2  列转行

A3  A2Cate分组,每组计算出Cate值并Name列的结果

A4  根据最大Name值,拼出结果表头

A5  结果表头并结果数据,导出至result.xlsx

问答搜集

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