CODEWITHSUNDEEP
linuxparsingunixtextawk
arupgsh
arupgsh

Reputation: 103

Comparing two files using Awk

I have two text files one with a list of ids and another one with some id and corresponding values.

File 1

abc
abcd
def
cab
kac

File 2

abcd   100
def    200
cab    500
kan    400

So, I want to compare both the files and fetch the value of matching columns and also keep all the id from File 1 and assign "NA" to the ids that don't have a value in File2

Desired output

abc     NA
abcd    100
def     200
cab     500
kac     NA

PS: Only Awk script/One-liners

The code I'm using to print matching columns:

awk 'FNR==NR{a[$1]++;next}a[$1]{print $1,"\t",$2}'

Upvotes: 0

Views: 424

Answers (2)

rudimeier
rudimeier

Reputation: 884

Using join and sort (hopefully portable):

export LC_ALL=C
sort -k1 file1 > /tmp/sorted1
sort -k1 file2 > /tmp/sorted2
join -a 1 -e NA -o 0,2.2 /tmp/sorted1 /tmp/sorted2

In bash you can use here-files in a single line:

LC_ALL=C join -a 1 -e NA -o 0,2.2 <(LC_ALL=C sort -k1 file1) <(LC_ALL=C sort -k1 file2)

Note 1, this gives output sorted by 1st column:

abc NA
abcd 100
cab 500
def 200
kac NA

Note 2, the commands may work even without LC_ALL=C. Important is that all sort and join commands are using the same locale.

Upvotes: 0

James Brown
James Brown

Reputation: 37394

$ awk 'NR==FNR{a[$1]=$2;next} {print $1,  ($1 in a? a[$1]: "NA") }' file2 file1
abc NA
abcd 100
def 200
cab 500
kac NA

Upvotes: 3

Related Questions