matching columns one separate files and appending matches to file

user_3793378 0
Viewed 92

I'm trying to merge two files filtered on a single column using awk. What I'd then like to do is append the relevant columns from file2 into file 1.

Easier to explain with dummy example.

File1

name   fruit   animal
bob    apple   dog
jim    orange  cat
gary   mango   snake
daisy  peach   mouse

File 2:

 animal number  shape
 cat    eight   square
 dog    nine    circle
 mouse  eleven  sphere

Desired output:

 name   fruit   animal  shape   
 bob    apple   dog     circle
 jim    orange  cat     square
 gary   mango   snake   NA
 daisy  peach   mouse   sphere

Step 1: Need to filter on column 3 in file1 and column 1 in file2

awk -F'\t' 'NR==FNR{c[$3]++;next};c[$1] > 0' file1 file2

This gives me output:

cat    eight   square
dog    nine    circle
mouse eleven   sphere

This helps me somewhat, however I can't simply cut the third column (shape) from the output above and append it to to file1 since there is no entry for 'snake' in file2. I need to be able to append column 3 of output to file 1 where a match is successful, and where it is not to put 'NA'. It's essential that all the lines in file1 are retained so I can't just omit them. This is where I'm stuck!

I'd appreciate any help please.... E

awk
1 Answers

Could you please try following, written and tested based on shown samples in GNU awk.

awk '
BEGIN{
  OFS="\t"
}
FNR==NR{
  a[$1]=$NF
  next
}
{
  print $0,($3 in a?a[$3]:"NA")
}'  Input_file2   Input_file1

Explanation: Adding detailed explanation for above.

awk '                               ##Starting awk program from here.
BEGIN{                              ##Starting BEGIN section from here.
  OFS="\t"                          ##Setting TAB as output field separator here.
}
FNR==NR{                            ##Checking condition FNR==NR which will be TRUE when first Input_file file2 is being read.
  a[$1]=$NF                         ##Creating array a with index $1 and value is $NF for current line.
  next                              ##next will skip all further statements from here.
}
{
  print $0,($3 in a?a[$3]:"NA")     ##Printing current line and checking if 3rd field is present in array a then print its value OR print NA.
}'  file2  file1                    ##Mentioning Input_file names here.
user_58665800
Related