How to sort a first file (csv) based on the second file keys

Question

I am trying to find a solution where I can sort my first file using the first column based on the second file keys

First file example (file1.csv)

COLUMN1 COlUMN2
apple fruit
dog animal
cat animal
cow animal

Second file example (sort_keys.txt)

cat
dog
apple
cow

Expected output (sorted.txt)

COLUMN1 COlUMN2
cat animal
dog animal
apple fruit
cow animal

So far I have found a sort command and awk commands might be able to help but I do not have any working code.

$> awk 'NR==FNR{o[FNR]=$1; next} {t[$1]=$0} END{for(x=1; x<=FNR; x++){y=o[x]; print t[y]}}' sort_key.txt file1.csv

However, this command is not working as expected, and would request any expert advice on this. P.S I do have Linux commands knowledge but this is something very specific and I do not have any idea how to achieve this.

Any help or hint is highly appreciated.

Answer 1

$ awk 'NR==1; NR==FNR{a[$1]=$2; next} {print $1, a[$1]}' file1 sort_keys.txt
COLUMN1 COlUMN2
cat animal
dog animal
apple fruit
cow animal

Answer 2

If you have GNU awk (aka gawk) you can define and use your own custom sort function.

For example, assuming GNU awk > 4.0 for the PROCINFO array traversal feature:

$ gawk '
  function mysort(ia,va,ib,vb){return o[ia] - o[ib]}
NR==FNR{o[$1]=FNR; next}      # map keys to numerical order
FNR==1{print; next}           # print + skip the header line
  {a[$1]=$0}
END{
    PROCINFO["sorted_in"] = "mysort"
    for(i in a) print a[i]
  }
' sort_key.txt file1.csv
COLUMN1 COlUMN2
cat animal
dog animal
apple fruit
cow animal

(With older GNU awks, you should be able to achieve the same using asorti.)

Answer 3

If your data isn't exceedingly large, this is a simple solution with quadratic complexity:

cat sort_keys.txt | while read key ; do egrep "^$key " file1.csv ; done

For adding / removing the header, add head and tail commands as needed.

Answer 4

Just for the sake of adding a variant, one can start the steeldriver's route but simplify the logic by stopping short of using both a function or an awk built-in array such as PROCINFO. This works only for sorting keys that are not repeated.

$ gawk '
        NR==FNR {o[$1]=FNR; next}  # map keys to numerical order in 1st input file `sort_key.txt`
        FNR==1 {print; next}       # print header of 2nd input file `file.csv`; go to next record
        {a[$1]=$0}                 # after header, place each record of `file.csv` in array `a`.
        END {
            for(i in o) b[o[i]]=i; # make new array, `b`, with swapped keys and values from array `o`.
            n=length(o) 
            for (j=1;j<=n;j++) print a[b[j]]
        }
       ' sort_key.txt file.csv
COLUMN1 COlUMN2
cat animal
dog animal
apple fruit
cow animal

Answer 5

You can use join for that. From man join:

For each pair of input lines with identical join fields, write a line to standard output. The default join field is the first, delimited by blanks.

Note that the the first line mustn't be sorted.

TLDR :

head -n 1 file1.csv; join -1 2 <(cat -n sort_keys.txt | sort -k 2) <(tail -n +2 file1.csv | sort) | sort -n -k 2 | awk '{ print $1, $3 }' will do the job.

Explanations

We'll basically :

• extract the first line of file1.csv
• join the remaining of file1.csv with sort_keys, on the first field
• sort the result on the order of sort_keys

Additionaly, join need the files to be sorted.

This will lead us with :

• as first input, number (in a prepended field) the sort_keys file (to be able to resort on this original order at the end), and sort on the 2nd field

cat -n sort_keys.txt | sort -k 2

  3 apple
  1 cat
  4 cow
  2 dog

• as second input, we take the csv file, skipping the first line, and sort it on the first field.

tail -n +2 file1.csv | sort

 apple fruit
 cat animal
 cow animal
 dog animal

• we can then join this alltogether, using the second field for the first process (-1 2) :

join -1 2 <(cat -n sort_keys.txt | sort -k 2) <(tail -n +2 file1.csv | sort)

 apple 3 fruit
 cat 1 animal
 cow 4 animal
 dog 2 animal

• the join result can now be sorted on the second field, numericaly (in case sort_keys has more than 9 entries), and we keep only 1st and 3rd fields

`... | sort -n -k 2 | awk '{ print $1, $3 }'

 cat animal
 dog animal
 apple fruit
 cow animal

• finally, prepend that with the first line of file1.csv

head -n 1 file1.csv; join -1 2 <(cat -n sort_keys.txt | sort -k 2) <(tail -n +2 file1.csv | sort) | sort -n -k 2 | awk '{ print $1, $3 }'

 COLUMN1 COlUMN2
 cat animal
 dog animal
 apple fruit
 cow animal

Going further

Depending on your real data, you will have to adjust the field numbers, and the field separator.

You may also want to keep data lines whose key is not in sort_keys and or keep lines of sort_keys having no corresponding data lines (see -a option of join).

Enjoy using join !