I have an awk file that I've created to sort some data in a csv file. Here's a snippet of the data
|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 |2 |
|8/5/2018 16:11|[email protected] |Ambros |2009|Honda |Oddesy |178 |J06 |Aaron |2 |0 |2 |2 |2 |2 |2 |0 |4 |4 |2 |2 |2 |4 |4 |4 |2 |2 | |6 |4 |4 |6 |4 |6 |
The output data I was able to produce looks like this
Ranking Car_ID Year Make Model Total
1 48 2015 Acura TLX 62
2 124 2015 Jeep Wrangler 124
3 222 2015 Lexus Is250 40
...
I want to be able to sort the output above in descending order based on the total column, but I can't figure out how to do so in awk. The total and ranking column are not part of the original csv data and are only produced during output. Here's my code so far
BEGIN {
FS = ",";
OFS = "\t\t";
}
NR==1 {
$35 = "Ranking";
$36 = "Total";
}
NR>1 {
$35 = 1;
for(i = 1; i < NR - 1; i ) { $35 = 1 }
$36 = $10 $11 $12 $13 $14 $15 $16 $17 $18 $19 $20 $21 $22 $23 $24 $25 $26 $27 $28 $29 $30 $31 $32 $33 $34;
}
{
print $35, $7, $4, $5, $6, $36;
}
When I run this and add "|sort -nk36|" at the end of the command it doesn't seem to do change the output or sort it in any way. Perhaps I'm getting my commands confused.
The expected output should be something like this
Ranking Car_ID Year Make Model Total
1 48 2015 Jeep Wrangler 124
2 124 2015 Acura TLX 62
3 222 2015 Lexus Is250 40
CodePudding user response：
Assumptions:
- input fields are comma-delimited (while OP's sample input is displayed as fixed-width with pipe boundaries, OP's
awkcode stipulates"FS=",", and since OP claims theawkcode is running and generating output, we'll stick withFS=",") - the 2nd line in OP's sample input (solid line of hyphens) does not actually exist in OP's file (per fact OP's
awkcode does not code forNR==2) - output will be tab-delimited (while OP's
awkcode mentionsOFS="\t\t", the sample outputs appear to be ... fixed-width?) Rankingassigments are based on the sorted results (ie, not based on the input ordering as shown in OP'sawkcode)
Setup:
$ cat raw.dat
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,CiviceG,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,Gencoupe,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,2
8/5/2018 16:11,[email protected],Ambros,2009,Honda,Oddesy,178,J06,Aaron,2,0,2,2,2,2,2,0,4,4,2,2,2,4,4,4,2,2,,6,4,4,6,4,6
One GNU awk (for PROCINFO["sorted_in"] support) idea:
awk '
BEGIN { FS=","; OFS="\t" }
FNR==1 { print "Ranking",$7,$4,$5,$6,"Total"; next }
{ totals[FNR]=0
for (i=10;i<=34;i )
totals[FNR] = $i
lines[FNR]=$7 OFS $4 OFS $5 OFS $6
}
END { PROCINFO["sorted_in"]="@val_num_desc" # sort totals[] array by numeric value (descending order)
ranking=0
for (i in totals) # loop through indices of the totals[] array
print ranking,lines[i],totals[i]
}
' raw.dat
This generates:
Ranking Car_ID Year Make Model Total
1 124 2015 Jeep Wrangler 124
2 178 2009 Honda Oddesy 72
3 48 2015 Acura TLX 62
4 207 1993 Honda CiviceG 40
5 222 2015 Lexus Is250 40
6 20 2009 Infiniti G37 38
7 167 2016 Ford Mystang 20
8 159 2013 Hyundai Gencoupe 14
If OP needs the output pretty printed with all columns lined up nicely then that can be done with a bit more code, or we can pipe the results through column (assumes no embedded spaces in the Make or Model columns), eg:
$ awk 'BEGIN ... ' raw.dat | column -t
Ranking Car_ID Year Make Model Total
1 124 2015 Jeep Wrangler 124
2 178 2009 Honda Oddesy 72
3 48 2015 Acura TLX 62
4 207 1993 Honda CiviceG 40
5 222 2015 Lexus Is250 40
6 20 2009 Infiniti G37 38
7 167 2016 Ford Mystang 20
8 159 2013 Hyundai Gencoupe 14
NOTES:
- no additional sorting requirements have been provided for the case where we have duplicate values in the
Totalcolumn so we'll print in whatever orderawkprocesses the data in theEND/forloop