我希望能够对以逗号分隔的输入 csv 文件进行排序,该文件由在额外列中创建的值分隔。以下是输入 csv 文件的示例
Timestamp,Email,Name,Year,Make,Model,Car_ID,Judge_ID,Judge_Name,Racer_Turbo,Racer_Supercharged,Racer_Performance,Racer_Horsepower,Car_Overall,Engine_Modifications,Engine_Performance,Engine_Chrome,Engine_Detailing,Engine_Cleanliness,Body_Frame_Undercarriage,Body_Frame_Suspension,Body_Frame_Chrome,Body_Frame_Detailing,Body_Frame_Cleanliness,Mods_Paint,Mods_Body,Mods_Wrap,Mods_Rims,Mods_Interior,Mods_Other,Mods_ICE,Mods_Aftermarket,Mods_WIP,Mods_Overall
8/5/2018 14:10,[email protected],Hernando,2015,Acura,TLX,48,J04,Bob,0,0,2,2,4,4,0,2,4,4,2,4,2,2,2,2,2,0,4,4,4,6,2,0,4
8/5/2018 15:11,[email protected],Noel,2015,Jeep,Wrangler,124,J02,Carl,0,6,4,2,4,6,6,4,4,4,6,6,6,6,6,4,6,6,6,6,6,4,6,4,6
8/5/2018 17:10,[email protected],Edan,2015,Lexus,Is250,222,J05,Adrian,0,0,0,0,0,0,0,0,6,6,6,0,0,6,6,6,0,0,0,0,0,0,0,0,4
8/5/2018 17:34,[email protected],Hieronymus,1993,Honda,Civic eG,207,J06,Aaron,0,0,2,2,2,2,2,2,0,4,2,2,2,2,2,2,4,2,2,0,0,0,2,2,0
8/5/2018 14:30,[email protected],Nickolas,2016,Ford,Mystang,167,J02,Carl,0,0,2,2,0,2,2,0,0,0,0,2,0,2,2,2,0,0,2,0,0,0,0,0,2
8/5/2018 16:12,[email protected],Martin,2013,Hyundai,Gen coupe,159,J04,Bob,0,0,2,0,0,0,2,0,0,0,0,2,0,2,2,0,2,0,2,0,0,0,0,0,0
8/5/2018 17:00,[email protected],Aldridge,2009,Infiniti,G37,20,J06,Aaron,2,0,2,2,0,0,2,0,0,2,2,2,2,2,2,2,2,2,4,2,2,0,2,0
我的代码目前所做的是筛选 csv 文件,并挑选出 car_id 列、年份、品牌和型号列。然后它遍历从racer_turbo 到最后一列的每一列,并且对于每一行,它将这些列中的值相加成一个总值,并将其与其他值(id、make、model 等)一起打印。打印时还有一个排名列在其他 5 个之前。下面是我的代码。
BEGIN {
FS = ",";
OFS = "\t";
print "Ranking", "Car_ID", "Year", "Make", "Model", "Total";
}
{
rank;
total = 0;
if(NR > 1) {
for(i = 8; i < NF; i++) {
total += $i;
}
print ++rank,$7, $4, $5, $6, total;
}
rows[$5][total][$0]
}
END {
print "\n";
print "Ranking", "Car_ID", "Year", "Make", "Model", "Total";
ranking;
PROCINFO["sorted_in"] = "@ind_str_asc"
for (m in rows) {
n = asorti(rows[m], t, "@ind_num_desc");
n = (n>3) ? 3 : n
for(i = 1; i <= n; i++) for(s in rows[m][t[i]]) {
$0 = s;
$1 = ++r;
print ++ranking, $7, $4, $5, $6, total;
}
}
}
我想在 END 块中做的是再次打印输出,但是,使用在前面的代码块中创建的总列将每个品牌的汽车排名前三。但是,我现在运行代码的输出如下所示
Ranking Car_ID Year Make Model Total
1 48 2015 Acura TLX 58
2 124 2015 Jeep Wrangler 118
3 222 2015 Lexus Is250 36
4 207 1993 Honda Civic eG 40
5 167 2016 Ford Mystang 18
6 159 2013 Hyundai Gen coupe 14
7 20 2009 Infiniti G37 36
...
Ranking Car_ID Year Make Model Total
1 113 2012 Acura Tsx sportwagon 10
2 112 2008 Acura TL 10
3 50 2015 Acura TLX 10
4 15 2014 Audi S4 10
5 18 2015 Audi S3 10
6 116 2008 Audi A4 10
7 2 2016 Bmw M2 10
8 172 2014 Bmw 4 10
9 28 1995 Bmw 318xi 10
...
看看在第二个打印部分的总列中,它如何显示每辆打印的汽车的总数为 10,而不是与每辆汽车的第一个打印部分中的值相同,并且每个制造商的最高 3 个总数被显示.
下面是预期的输出
Ranking Car_ID Year Make Model Total
1 48 2015 Acura TLX 58
2 124 2015 Jeep Wrangler 118
3 222 2015 Lexus Is250 36
4 207 1993 Honda Civic eG 40
5 167 2016 Ford Mystang 18
6 159 2013 Hyundai Gen coupe 14
7 20 2009 Infiniti G37 36
8 178 2009 Honda Oddesy 66
...
Ranking Car_ID Year Make Model Total
1 112 2008 Acura TL 110
2 50 2015 Acura TLX 102
3 127 2013 Acura Tsx 86
4 15 2014 Audi S4 120
5 18 2015 Audi S3 38
6 116 2008 Audi A4 28
7 2 2016 Bmw M2 24
8 172 2014 Bmw 4 22
9 111 2007 Bmw 328i 10
10 218 2010 Chevy Camaro 64
11 170 2014 Chevy Cruze 50
12 0 2015 Chevy Camaro 0
...
这可以用我当前的代码来挽救吗?或者更好的方法是创建一个单独的 awk 文件,该文件将对生成的输出进行排序并生成另一个按前 3 名排序的文件?
我正在运行 GNU AWK v4.0.2。
假设Car_ID
(以下简称id
)在各行中是唯一的,请您尝试:
BEGIN {
FS = ","
OFS = "\t"
print "Ranking", "Car_ID", "Year", "Make", "Model", "Total"
}
{
rank
total = 0
if (NR > 1) {
for (i = 8; i < NF; i++) {
total += $i
}
print ++rank, $7, $4, $5, $6, total
ttl[$5][$7] = total
row[$7] = $0
}
}
END {
print "\n"
print "Ranking", "Car_ID", "Year", "Make", "Model", "Total"
ranking
id
PROCINFO["sorted_in"] = "@ind_str_asc"
for (m in ttl) {
n = asorti(ttl[m], t, "@val_num_desc")
n = (n>3) ? 3 : n
for (i = 1; i <= n; i++) {
id = t[i]
total = ttl[m][id]
$0 = row[id]
print ++ranking, $7, $4, $5, $6, total
}
}
}
我稍微修改了数据结构,将 指定id
为主键。然后创建一个二维数组ttl
,其中保存由和total
键控的值。在循环中,我们可以使用. 作为旁注,您的原始数据结构用作索引。如果具有相同 make 的多行碰巧具有相同的 值,则其中一个索引将被覆盖。make
id
END
id
total
total
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句