如何按列值分组为行和列标题，然后将值求和 [英] How to group by column values into Row and column header and then sum the value

问题描述

下面是我的输入和输出.txt文件.

Below is my input and output .txt files.

我想按StatusDate和Method对数据进行分组. 然后根据StatusDate和Method对值求和.

I want to group by the data by StatusDate and Method. And then sum the values based on the StatusDate and Method.

Input.txt

No,Date,MethodStatus,Key,StatusDate,Hit,CallType,Method,LastMethodType
112,12/15/16,Suceess,Geo,12/15/16,1,Static,GET,12/15/16
113,12/18/16,Suceess,Geo,12/18/16,1,Static,GET,12/18/16
114,12/19/16,AUTHORIZED,Geo,12/19/16,1,Static,GET,12/19/16
115,12/19/16,AUTHORIZED,Geo,12/19/16,1,Static,GET,12/19/16
116,12/19/16,Suceess,Geo,12/19/16,1,Static,PUT,12/19/16
117,12/19/16,Suceess,Geo,12/19/16,1,Static,PUT,12/19/16
118,12/19/16,Waiting,Geo,12/19/16,1,Static,GET,12/19/16
119,12/19/16,AUTHORIZED,Geo,12/19/16,1,Static,GET,12/19/16
120,12/17/16,Suceess,Geo,12/17/16,1,Static,GET,12/17/16
121,12/17/16,Suceess,Geo,12/17/16,1,Static,GET,12/17/16
130,12/16/16,Suceess,Geo,12/16/16,1,Static,GET,12/16/16

Out.txt

StatusDate,12/15/16,12/16/16,12/17/16,12/17/16,12/18/16,12/19/16,12/19/16,12/19/16,12/19/16,12/19/16,12/19/16,Grand Total
GET,1,1,1,1,1,1,1,1,1,,,9
PUT,,,,,,,,,,1,1,2
Grand Total,1,1,1,1,1,1,1,1,1,1,1,11

我正在使用awk，并将数据除以awk -F, '{if($8=="GET") print }'，然后计算总和值. 由于文件很大，因此会有延迟.

I'm using awk and splitting the data by awk -F, '{if($8=="GET") print }', then calculating the sum value. Since the file size is huge, there is a delay.

是否可以一步一步完成所有操作?这样文件操作会减少吗?

Is it possible to do everything in one step? So the file operation will be reduced?

推荐答案

$ awk -F, 'NR==1 {printf "%s",$8$2; t="Total"; v[t]; next} 
                 {d[$2]; v[$8]; a[$2,$8]++; a[$2,t]++} 
           END   {n=asorti(d,ds); 
                  for(i=1;i<=n;i++) printf "%s", FS ds[i]; 
                  print ""; 
                  for(k in v) 
                    {printf "%s", k; 
                     for(i=1;i<=n;i++) printf "%s", FS a[ds[i],k]; 
                     print ""}}' file


MethodDate,12/15/16,12/16/16,12/17/16,12/18/16,12/19/16
PUT,,,,,2
GET,1,1,2,1,4
Total,1,1,2,1,6

这篇关于如何按列值分组为行和列标题，然后将值求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！