How can I sort output based on created column?

Question

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

Can you use CSV-specific tools like 'csv toolkit' (Python), or GoCSV (compiled for many platforms)? — Zach Young
– Zach Young, Commented Mar 14, 2022 at 18:00
please update the question with exact copies of your inputs/outputs; your awk code suggests the input data is comma delimited while the output is delimited by double tabs, but your sample inputs/outputs (in the question) are shown as fixed width with additional pipe boundaries — markp-fuso
– markp-fuso, Commented Mar 14, 2022 at 18:03
@ZachYoung unfortunately no, I can only use awk itself for this, I was given very narrow latitude — Stephen
– Stephen, Commented Mar 14, 2022 at 18:04

markp-fuso · Accepted Answer · 2022-03-14 22:09:32Z

Assumptions:

input fields are comma-delimited (while OP's sample input is displayed as fixed-width with pipe boundaries, OP's awk code stipulates "FS=",", and since OP claims the awk code is running and generating output, we'll stick with FS=",")
the 2nd line in OP's sample input (solid line of hyphens) does not actually exist in OP's file (per fact OP's awk code does not code for NR==2)
output will be tab-delimited (while OP's awk code mentions OFS="\t\t", the sample outputs appear to be ... fixed-width?)
Ranking assigments are based on the sorted results (ie, not based on the input ordering as shown in OP's awk code)

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 Total column so we'll print in whatever order awk processes the data in the END/for loop

Collectives™ on Stack Overflow

How can I sort output based on created column?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related