1

I need to compare two files and based on comparison I need to add new column in first file. Column C is the primary key.

A,B,C,D
1,1990,I001,2473264
2,1991,I002,2473265
3,1992,I004,2473266
4,1993,6050,912432
5,1994,6003,912433

SECOND FILE

A,B
I001,2.3 GHz
I002,2.3 GHz
I004,2.3 GHz
6050,1.8 GHz
6003,850 MHz

EXPECTED OUTPUT

A,B,C,D,E
1,1990,2.3 GHz,I001,2473264
2,1991,2.3 GHz,I002,2473265
3,1992,2.3 GHz,I004,2473266
4,1993,1.8 GHz,6050,912432
5,1994,850 MHz,6003,912433

This code is working fine, but if the record is not matched it is skipping the row from first file. But I don’t want it to get skipped and append 0 or NA in that column.

awk -F, 'NR==FNR{a[$1]=$2;next}a[$3]{print $0","a[$3]}' test2 test1
A,B,C,D
1,1990,I001,2473264
2,1991,I002,2473265
3,1992,I004,2473266
4,1993,6050,912432
5,1994,6003,912433
6,1995,6004,21234

Expected Output:

1,1990,I001,2473264,2.3 GHz
2,1991,I002,2473265,2.3 GHz
3,1992,I004,2473266,2.3 GHz
4,1993,6050,912432,1.8 GHz
5,1994,6003,912433,850 MHz
6,1995,6004,21234,0
0

Change your awk to

$ awk -F, 'NR==FNR{a[$1]=$2;next} FNR!=1{print $0","(a[$3]?a[$3]:"NA")}' SECOND FIRST
1,1990,I001,2473264,2.3 GHz
2,1991,I002,2473265,2.3 GHz
3,1992,I004,2473266,2.3 GHz
4,1993,6050,912432,1.8 GHz
5,1994,6003,912433,850 MHz
6,1995,6004,21234,NA

To get your first desired result:

$ awk -F, 'NR==FNR{a[$1]=$2;next} FNR!=1{$3=(a[$3]?a[$3]:"NA")","$3; print}' OFS=, SECOND FIRST
1,1990,2.3 GHz,I001,2473264
2,1991,2.3 GHz,I002,2473265
3,1992,2.3 GHz,I004,2473266
4,1993,1.8 GHz,6050,912432
5,1994,850 MHz,6003,912433
6,1995,NA,6004,21234
0

Your awk program is correct if we modify it to unconditionally use

print $0","( a[$3] ? a[$3] : 0 )

instead of using

print $0","a[$3]

when a[$3] is non-zero or empty. That is, use a zero if the a[$3] field is zero or empty, otherwise use a[$3].

In other words,

awk -F, -v OFS=',' 'FNR==NR { a[$1]=$2; next } FNR > 1 { print $0, (a[$3] ? a[$3] : 0) }' fileB fileA

We use FNR > 1 here to skip the header.


Using join:

$ join -t , -1 3 -o 1.1,1.2,1.3,1.4,2.2 fileA fileB
1,1990,I001,2473264,2.3 GHz
2,1991,I002,2473265,2.3 GHz
3,1992,I004,2473266,2.3 GHz
4,1993,6050,912432,1.8 GHz
5,1994,6003,912433,850 MHz

If the files are not sorted on the joining key, then (in a shell that support process substitutions):

$ join -t , -1 3 -o 1.1,1.2,1.3,1.4,2.2 <( sort -t, -k3,3 fileA ) <( sort fileB )
5,1994,6003,912433,850 MHz
4,1993,6050,912432,1.8 GHz
1,1990,I001,2473264,2.3 GHz
2,1991,I002,2473265,2.3 GHz
3,1992,I004,2473266,2.3 GHz

The join will perform an INNER JOIN operation on the two files. The -t , tells join that commas are the field delimiter and we pick the third field as the join key in the first file with -1 3 (the first field in the second file is assumed to be the key, unless we use -2 N for same other field N in that file).

The -o flag tells join what fields we'd like to include in the output and from what file (x.y means column y from file x).

join is a very fast operation, but it requires that the input files are sorted on the joining key (we were just lucky with the first example above). The final output in the second example above is unsorted with respect to the first field, but you may easily fix that by piping it through sort -k1,1n.


For the second scenario, with a mismatch,

$ join -t, -1 3 -o1.1,1.2,1.3,1.4,2.2 -a 1 -e 0  <( sort -t, -k3,3 fileA ) <( sort fileB )
5,1994,6003,912433,850 MHz
6,1995,6004,21234,0
4,1993,6050,912432,1.8 GHz
A,B,C,D,0
1,1990,I001,2473264,2.3 GHz
2,1991,I002,2473265,2.3 GHz
3,1992,I004,2473266,2.3 GHz

By adding -a 1 -e 0 we ask join to always output all fields from the first file (-a 1) and to insert zeroes for every field that is missing in the second file.

As you can see, we now get the correct result, but the header from the first file is also included (because we asked for it). If you want to remove the headers from the data of both files (and sort the result), then

$ join -t, -1 3 -o1.1,1.2,1.3,1.4,2.2 -e 0 -a1  <( tail -n +2 fileA | sort -t, -k3,3 ) <( tail -n +2 fileB | sort ) | sort -k1,1n
1,1990,I001,2473264,2.3 GHz
2,1991,I002,2473265,2.3 GHz
3,1992,I004,2473266,2.3 GHz
4,1993,6050,912432,1.8 GHz
5,1994,6003,912433,850 MHz
6,1995,6004,21234,0

This command would actually work for both scenarios.

  • fairly your awk solution is almost in my answer – αғsнιη Apr 21 '18 at 7:29
  • The awk code outputs an unexpected 0 in the header: A,B,C,D,0 – agc Apr 21 '18 at 7:31
  • @agc So it does. I have fixed that by ignoring the header of the second file. – Kusalananda Apr 21 '18 at 7:41
  • @αғsнιη There's only so many ways to do this in awk. At least I'm using slightly different syntax and OFS, which makes it a bit different. I did not read your answer before answering (and wrote it initially as a join-only answer, not awk). – Kusalananda Apr 21 '18 at 7:43
0

Using GNU join with the extended data and the first output format, plus echo and tail to replace the header, (because join gets confused by inconsistent headers):

echo A,B,C,D,E
join -t ',' --header --nocheck-order test1 test2 \
     -a 1 -e NA -1 3 -2 1 -o 1.1,1.2,2.2,1.3,1.4 | tail -n +2

Output:

A,B,C,D,E
1,1990,2.3 GHz,I001,2473264
2,1991,2.3 GHz,I002,2473265
3,1992,2.3 GHz,I004,2473266
4,1993,1.8 GHz,6050,912432
5,1994,850 MHz,6003,912433
6,1995,NA,6004,21234

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.