linux - 命令行编辑 .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:将音量移动到上一行中的“Volume 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