[求助]awk能否实现类似excel数据透视表功能

hgx126

一文本如下:

1. 
   
2. 机构编号,商户号,姓名,金额,等级
   
3. 5411,54117648,张斌,67,100
   
4. 5412,60519607,胡蓉,57,60
   
5. 5412,54119444,罗鑫,17,30
   
6. 5412,54119445,陈延德,38,60
   
7. 5411,54119477,杨晓荣,13,30
   
8. 5411,54119476,郑元兵,58,60
   
9. 5411,54119476,郑元兵,98,100
   
10. 5416,54119471,罗小平,21,30
    
11. 5416,54119470,何燕,30,60
    
12. 5416,54119470,何燕,30,60
    
13. 5416,54119468,罗国江,74,100
    

复制代码

按机构号分为行,等级为列,统计商户号数量,金额求和,类似excel数据透视表功能，结果如下:

1. 机构编号,30,30,60,60,100,100,合计,合计
   
2. 机构编号,商户号数量,金额,商户号数量,金额,商户号数量,金额,商户号数量,金额
   
3. 5411,1,13,1,58,2,165,4,236
   
4. 5412,1,17,2,95,0,0,3,112
   
5. 5416,1,21,2,60,1,74,4,155
   
6. 总计,3,51,5,213,3,239,11,503
   

复制代码

cao627

第2个表和第一个表之间的逻辑关系真心看不懂！@hgx126

rubyish

回复 1# hgx126

awk ~ bd
shishi perl:

perl toss.pl file

toss.pl

1. #!/usr/bin/perl
   
2. use 5.010;
   
3. 
   
4. open my $DATA, shift or die $!;
   
5. readline $DATA;
   
6. 
   
7. my ( $O, $o ) = ( [ 0, 0 ], ',' );
   
8. my ( %G, %X, %H, %V, $V );
   
9. 
   
10. while (<$DATA>) {
    
11.     chomp;
    
12.     my ( $n, undef, undef, $m, $g ) = split $o;
    
13.     $G{$g} ||= 1;
    
14.     $_->[0]++, $_->[1] += $m for $X{$n}{$g}, $H{$n}, $V{$g}, $V;
    
15. }
    
16. 
    
17. my @G = sort { $a <=> $b } keys %G;
    
18. 
    
19. say join $o, '机构编号', map { ($_) x 2 } @G, '合计';
    
20. say join $o, '机构编号', ( '商户号数量', '金额' ) x ( @G + 1 );
    
21. 
    
22. for my $n ( sort { $a <=> $b } keys %X ) {
    
23.     say join $o, $n, map( @{ $X{$n}{$_} || $O }, @G ), @{ $H{$n} };
    
24. }
    
25. 
    
26. say join $o, '总计', map( @{ $V{$_} }, @G ), @$V;
    

复制代码

jcdiy0601

awk -F, '{a[$1" "$2]+=$4;b[$1" "$2]++}END{for(i in a)print i,a[i],b[i]}' file|awk '{print $1,$4,$3}' |awk '{a[$1]=a[$1]","$2","$3}END{for(i in a)print i,a[i]}'
5416 ,2,60,1,21,1,74
5411 ,1,67,2,156,1,13
5412 ,1,17,1,38,1,57