命令行编辑.CSV文件

Question

我有一个.CSV文件，其中行具有相同的ID。

下面是一个例子。CSV文件：

Alias,Date,Volume,Type,Type2,MSI 
ID1,20170418,0.516006112,CHART,GAS,10-10-10-1 
ID2,20170418,4.03900528,CHART,GAS,10-10-10-2 
ID2,20170418,0.399999887,METER,OIL,10-10-10-2 
ID2,20170418,0,METER,CONDY,10-10-10-2 
ID3,20170418,0.399999887,CHART,GAS,10-10-10-3 
ID4,20170418,6.599591255,CHART,GAS,10-10-10-4 
ID5,20170418,4.6501894,CHART,GAS,10-10-10-5 
ID6,20170418,1.831178546,CHART,GAS,10-10-10-6 
ID6,20170418,0.190000013,METER,OIL,10-10-10-6 
ID6,20170418,0,METER,CONDY,10-10-10-6 

我需要创建一个Linux脚本编辑这个.csv文件，看起来像这样：

Alias,Date,Volume,Type,Type2,MSI,Volume Oil, Volume Condy 
ID1,20170418,0.516006112,CHART,GAS,10-10-10-1,, 
ID2,20170418,4.03900528,CHART,GAS,10-10-10-2,0.399999887,0 
ID3,20170418,0.399999887,CHART,GAS,10-10-10-3,, 
ID4,20170418,6.599591255,CHART,GAS,10-10-10-4,, 
ID5,20170418,4.6501894,CHART,GAS,10-10-10-5,, 
ID6,20170418,1.831178546,CHART,GAS,10-10-10-6,0.190000013,0 

如果别名域重复...

并且行具有METER OIL：将卷移至上一行中的'Volume Oil'，删除此行

和行有METER CONDY：移动卷上一行 '卷Condy'，删除此行

Answer 1

AWK方法（假设只能有三个记录有相同ID）：

awk -F, 'BEGIN{print "Alias,Date,Volume,Type,Type2,MSI,Volume Oil,Volume Condy"; } 
     NR>1{ (a[$1])? gsub(/,*$/, FS$3, a[$1]) : a[$1]=$0 FS FS} 
     END{ for(i in a) print a[i] }' file.csv 

输出：

Alias,Date,Volume,Type,Type2,MSI,Volume Oil,Volume Condy 
ID1,20170418,0.516006112,CHART,GAS,10-10-10-1,, 
ID2,20170418,4.03900528,CHART,GAS,10-10-10-2,0.399999887,0 
ID3,20170418,0.399999887,CHART,GAS,10-10-10-3,, 
ID4,20170418,6.599591255,CHART,GAS,10-10-10-4,, 
ID5,20170418,4.6501894,CHART,GAS,10-10-10-5,, 
ID6,20170418,1.831178546,CHART,GAS,10-10-10-6,0.190000013,0