0

I have 2 files need to compare file1 2ns and 3 ed columns with file2 first and the second column if matches leave it if not match append the data in file1 by adding 2019 as the first column and 0 as the last column. is this possible?

File1

2019  ABCD 1 10
2019  DEF  2 11
2019  GHI  1 20
2019  jkl  2 25

File2

ABCD 1
jkl  1
mnop  2
qrst  1

expected Result

2019  ABCD 1 10
2019  DEF  2 11
2019  GHI  1 20
2019  jkl  2 25
2019  jkl  1  0
2019  mnop  2  0
2019  qrst  1  0
3

Try this:

awk '
     FNR==NR {seen[$2,$3]=1; print $0 }
     FNR!=NR && !seen[$1,$2] { print 2019,$0,0 }
' file1 file2

Explanation:

  • FNR==NR { ... } Run the commands in brackets only for first file.
  • seen[$2,$3]=1, set array seen with key $2,$3 to 1.
  • print $0 print the full row.
  • FNR!=NR && !seen[$1,$2] { ... } Run the commands in brackets only if not first file and if fields $1,$2 are not keys in seen array.
  • print 2019,$0,0 print the row surrounded by new columns 2019 and 0.

Add | column -t for aligned output.

Output:

$ awk 'FNR==NR{seen[$2,$3]++; print $0} FNR!=NR && !seen[$1,$2]{print 2019,$0,0}' file1 file2 | column -t
2019  ABCD  1  10
2019  DEF   2  11
2019  GHI   1  20
2019  jkl   2  25
2019  jkl   1  0
2019  mnop  2  0
2019  qrst  1  0
  • what you mean by full date? Where should it come from? And for creating new file from a command you can simply redirect the output to a file, add > file3 at the end of the command – pLumo Jan 22 at 9:09
0

This should work:

$ awk 'NR==FNR{a[$2$3]++; print; next}!($1$2 in a){print "2019",$0,"0"}' file1 file2 
2019  ABCD 1 10
2019  DEF  2 11
2019  GHI  1 20
2019  jkl  2 25
2019 jkl  1 0
2019 mnop  2 0
2019 qrst  1 0

Explanation

  • NR==FNR : NR is the current line number, FNR is the line number of the current file. The two will be identical only while the 1st file is being read.
  • {a[$2$3]++; print; next} : while reading the first file (file1), use the 2nd and 3rd fields as a key for th associative array a. This is just used to keep track of which ones we have already seen. Then, print this line and move on to the next. This next ensures the rest of the script will only be run for the second file, when NR is not the same as FNR.
  • !($1$2 in a) : if the first and second fields of this line are not used as keys in the a array (this means the 1st and 2nd fields concatenated, so if the 1st is foo ad the second bar, $1$2 will be foobar).
  • {print "2019",$0,"0"}: print 2019, the current line from file2 and 0.
0

Here's a very different way to do it:

mkns() {
    # make keys from parameters $2 and $3 for joining, then sort the keys
    sort -k 1b,1 <(awk "{print \$$2\$$3, \$0}" $1)
}

Then run

join -j 1 -v 2 <(mkns file1 2 3) <(mkns file2 1 2) | awk '{print 2019, $2, $3, 0}' | cat file1 - | column -t

This is not as short as the purely awk solutions by RoVo and tendon, but I think this is an interesting solution because join can be used to "filter out" rows that you want.

protected by Community Jan 22 at 11:56

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

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