awk compare columns in three files and print match non match with NA prefix along with their content

user_13444505 0
Viewed 66

hi i have a problem to solve comparison of three files to get desired output, where file1 column $2 is compared with file2 column $4 along with file3 column $2 where is result that it will append to output filename of file1 plus will print non matched column from file1 along with added NA to reflect remaining columns to keep them in tact / consistent

file1

4 FIX VAL1 32254720
0 AA SILO_T 4294967290
16 RS SILO 2684560000
3 DD SILO_A 1041824000
2 BB SILO_B 4294729600

file2

377 le377 4 FIX cell 0x
514 le514 3 DD cell 0c
0 le0 2 BB cell 2a
516 le516 0 AA cell 8c

file3

3 DD SILO_A 100 on 0 yes
2 BB SILO_B 400 on 0 no
0 AA SILO_T 3 on 0 yes
4 FIX VAL1 30 on 0 no

output should be:

file1 4 FIX VAL1 32254720 377 le377 4 FIX cell 0x 4 FIX 30 on 0 no
file1 0 AA SILO_T 4294967290 516 le516 AA cell 8c 0 AA 3 on 0 yes
file1 16 RS SILO 2684560000 NA NA NA NA NA NA NA NA NA NA NA
file1 3 DD SILO_A 1041824000 514 le514 3 DD cell 0c DD 100 on 0 yes
file1 2 BB SILO_B 4294729600 0 le0 2 BB cell 2a BB 400 on 0 no

partially working code

awk 'FNR==NR{a[$3]=$0;next}; \
{printf FILENAME "%s %s %s %s %s %s\n","",$1,$2,$3,$4,$5 (($1 in a)?a[$1]: "NA NA NA NA NA NA")}' file2 file1

file1 4 FIX VAL1 32254720 377 le377 4 FIX cell 0x
file1 0 AA SILO_T 4294967290 516 le516 0 AA cell 8c
file1 16 RS SILO 2684560000 NA NA NA NA NA NA
file1 3 DD SILO_A 1041824000 514 le514 3 DD cell 0c
file1 2 BB SILO_B 4294729600 0 le0 2 BB cell 2a

i dont know how to pass file3 for next comparison to get things done to get desired output, will be glad if provided solution will be with explanation so i can fully understand how in case of need exchange column numbers in case of need of another needed comparisons in future, thank you for any help, how to extend current code or to write it simpler

multiple-columns compare awk
2 Answers

You may use this awk script:

cat mergeall.php

BEGIN {
   fill = "NA NA NA NA NA NA NA NA NA NA NA NA NA"
}
ARGIND == 1 {      # while processing 1st file in arguments
   map[$4] = $0
   next
}
ARGIND == 2 {      # while processing 2nd file in arguments
   map[$2] = ($2 in map ? map[$2] OFS : "") $0
   next
}
{                  # while processing 3rd file in arguments
   print FILENAME, $0, ($2 in map ? map[$2] : fill)
}

Then use it as:

awk -f mergeall.awk file2 file3 file1 | column -t

file1  4   FIX  VAL1    32254720    377  le377  4   FIX  cell  0x  4   FIX  VAL1    30   on  0   no
file1  0   AA   SILO_T  4294967290  516  le516  0   AA   cell  8c  0   AA   SILO_T  3    on  0   yes
file1  16  RS   SILO    2684560000  NA   NA     NA  NA   NA    NA  NA  NA   NA      NA   NA  NA  NA
file1  3   DD   SILO_A  1041824000  514  le514  3   DD   cell  0c  3   DD   SILO_A  100  on  0   yes
file1  2   BB   SILO_B  4294729600  0    le0    2   BB   cell  2a  2   BB   SILO_B  400  on  0   no

Note that we have input files in this order: file2 file3 file1

user_5482250

Taking inspiration from @anubhava sir's solution here, adding a bit more generic solution where NA values will be created as per Input_file's number of fields and we need not to hardcode it. Could you please try following, written and tested with shown samples in GNU awk.

awk '
ARGIND<=2{
  fill[ARGIND]=(fill[ARGIND]>NF?fill[ARGIND]:NF)
}
ARGIND == 1 {
   map[$4] = $0
   next
}
ARGIND == 2 {
   map[$2] = ($2 in map ? map[$2] OFS : "") $0
   next
}
ARGIND == 3 && file==""{ file = FILENAME }
{
  if(!arr[$2]++){ ind[++count] = $2 }
  val[$2]=$0
}
END{
  for(j=1;j<=ARGIND;j++){
     s=sprintf("%"fill[j]"s","");gsub(/ /,"NA ",s);sub(/ +$/,"",s)
     fillVal=(fillVal?fillVal OFS:"")s
     s=""
  }
  for(i=1;i<=count;i++){ 
     print file, val[ind[i]], (ind[i] in map ? map[ind[i]] : fillVal)
  }
}' Input_file2  Input_file3  Input_file1
user_58665800
Related