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

awkcsvlinuxsort

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.

Best Answer

$ 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

Related Solutions

Awk – Match Values Between Two Files and Create a New File

Here's one way:

$ awk -F"[, ]" 'NR==FNR{a[$1]=$1","$2; next} ($2 in a){print a[$2]","$1}' file1 file2 
1000,Brian,3044
400,Nick,4466
1010,Jason,1206

The -F"[, ]" sets the field separator to either a space or a comma. FNR is the current line number and NR the current line number of the current file. The two will be equal only while the 1st file is being read. Therefore, NR==FNR{a[$1]=$1","$2; next} will be run only on the lines of the first file and will save the 1st and 2nd fields (with a comma in between) as values in the array a whose keys are the 1st fields. Then, when the 2nd file is being read, if the 2nd field is in a, we print the value associated with it (the 1st and 2nd fields of the first file) and the 1st field of the second file.

That said, there's actually an app for this! This sort of thing is what join was made for. Sadly, since your two files are unsorted and have different delimiters, we need some tricks. If your shell supports <(), you can do:

$ join -t, -1 1 -2 2 <(sort file1) <(sed 's/ /,/g' file2 | sort -t"," -k2) 
1000,Brian,3044
1010,Jason,1206
400,Nick,4466

The join -t, -1 1 -2 2 means use , as the delimiter and join on the 1st field of file1 and the 2nd field of file2. The sed just replaces spaces with commas so we have the same delimiter in both files. The sort does what it says on the bottle: it sorts its input.

Joining entries based off of column using awk/join

With your approach you have to use join twice (or change your approach to do it with a single join invocation) :

print the common lines and the unpairable lines from file1 with join -t'|' -e0 -a1 -o 1.2,1.3,1.5,2.5 <(<file1 awk -F'|' '{print $1"-"$2"|"$0}' | sort -t'|' -k1,1) <(<file2 awk -F'|' '{print $1"-"$2"|"$0}' | sort -t'|' -k1,1)
print the unpairable lines from file2 with join -t'|' -e0 -v2 -o 2.2,2.3,1.5,2.5 <(<file1 awk -F'|' '{print $1"-"$2"|"$0}' | sort -t'|' -k1,1) <(<file2 awk -F'|' '{print $1"-"$2"|"$0}' | sort -t'|' -k1,1)

You can do the same with a single awk invocation, storing $4 in two arrays indexed by e.g. $1|$2 and then in the END block iterating over each array indices, comparing them and printing accordingly:

awk -F'|' 'NR==FNR{z[$1"|"$2]=$4;next}{x[$1"|"$2]=$4}
END{for (j in x){if (!(j in z)){print j, "0", x[j]}};
for (i in z){if (i in x){print i, z[i], x[i]} else {print i, z[i], "0"}}
}' OFS="|"  file1 file2

Best Answer

Related Solutions

Awk – Match Values Between Two Files and Create a New File

Joining entries based off of column using awk/join

Related Question