在UNIX中创建数据透视表 [英] Creating Pivot table in UNIX
本文介绍了在UNIX中创建数据透视表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
下面是我的输入数据,我正在尝试创建到数据透视表.
Below is my input data, I'm trying create to a Pivot table.
input.txt
input.txt
ID,CreateDate,Category,Region,PublishDate,Code,Listing,Type,ModifiedDate
FRU426131598,22-Aug-16,SELLING,COUNTRY,22-Aug-16,1,SAMPLE,GRAPE,22-Aug-16
FRU426175576,23-Aug-16,SELLING,COUNTRY,23-Aug-16,1,SAMPLE,APPLE,23-Aug-16
FRU427163049,26-Aug-16,SELLING,COUNTRY,26-Aug-16,1,SAMPLE,APPLE,26-Aug-16
FRU427163049,26-Aug-16,SELLING,COUNTRY,26-Aug-16,1,SAMPLE,APPLE,26-Aug-16
FRU427163049,26-Aug-16,SELLING,COUNTRY,26-Aug-16,1,SAMPLE,GRAPE,26-Aug-16
FRU427163049,26-Aug-16,SELLING,COUNTRY,26-Aug-16,1,SAMPLE,GRAPE,26-Aug-16
FRU427163049,26-Aug-16,SELLING,COUNTRY,26-Aug-16,1,SAMPLE,APPLE,26-Aug-16
FRU427163049,26-Aug-16,SELLING,COUNTRY,26-Aug-16,1,SAMPLE,APPLE,26-Aug-16
FRU426972836,26-Aug-16,SELLING,COUNTRY,26-Aug-16,1,SAMPLE,ORANGE,26-Aug-16
FRU427322180,28-Aug-16,SELLING,COUNTRY,28-Aug-16,1,SAMPLE,GRAPE,28-Aug-16
FRU427032658,26-Aug-16,SELLING,COUNTRY,26-Aug-16,1,SAMPLE,APPLE,26-Aug-16
FRU427373494,29-Aug-16,SELLING,COUNTRY,29-Aug-16,1,SAMPLE,GRAPE,29-Aug-16
FRU427373069,29-Aug-16,SELLING,COUNTRY,29-Aug-16,1,SAMPLE,GRAPE,29-Aug-16
FRU425669484,19-Aug-16,SELLING,COUNTRY,19-Aug-16,1,SAMPLE,APPLE,19-Aug-16
FRU425616815,18-Aug-16,SELLING,COUNTRY,18-Aug-16,1,SAMPLE,APPLE,18-Aug-16
FRU420018273,25-Sep-16,SELLING,COUNTRY,25-Sep-16,1,SAMPLE,ORANGE,25-Sep-16
FRU435018589,25-Sep-16,SELLING,COUNTRY,25-Sep-16,1,SAMPLE,ORANGE,25-Sep-16
FRU421375128,26-Sep-16,SELLING,COUNTRY,26-Sep-16,1,SAMPLE,APPLE,26-Sep-16
FRU434911933,21-Sep-16,SELLING,COUNTRY,21-Sep-16,1,SAMPLE,ORANGE,21-Sep-16
FRU434594125,21-Sep-16,SELLING,COUNTRY,21-Sep-16,1,SAMPLE,ORANGE,21-Sep-16
类型列为行,createDate列为列.以及ID字段的值之和.
Type filed as Row, createDate filed as Columns. And Sum of the values of ID field.
所需的输出:
Row Labels 18-Aug-16 19-Aug-16 22-Aug-16 23-Aug-16 26-Aug-16 28-Aug-16 29-Aug-16 21-Sep-16 25-Sep-16 26-Sep-16 Grand Total
APPLE 1 1 1 5 1 9
GRAPE 1 2 1 2 6
ORANGE 1 2 2 5
Grand Total 1 1 1 1 8 1 2 2 2 1 20
有什么办法吗?我可以使用awk获取createdDate的计数.但是无法创建具有行和列的数据透视表.
Is there any way to do it? I can get the count of createdDate using awk. But unable to create the pivot table with row and columns.
推荐答案
awk
进行救援!
这可以帮助您入门...
This can get you started...
$ awk -F, -v OFS='\t' 'NR>1 {k=$(NF-1); d=$2; keys[k]; dates[d]; a[k,d]++}
END {line="Row Labels";
for(d in dates) line = line OFS d;
print line;
for(k in keys)
{{line=k;
for(d in dates) line=line OFS a[k,d]}
print line}}' file
Row Labels 19-Aug-16 29-Aug-16 23-Aug-16 18-Aug-16 28-Aug-16 22-Aug-16 26-Aug-16 26-Sep-16 21-Sep-16 25-Sep-16
APPLE 1 1 1 5 1
ORANGE 1 2 2
GRAPE 2 1 1 2
您可能想对日期进行排序(不那么容易),并且可以总计(简单).
you may want to sort the dates (not that easily) and can add totals (easy).
这篇关于在UNIX中创建数据透视表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文