Combine rows from different files, if pattern is matched

Question

Input: two tab-delimited files, File1 and File2. File2 ($2) has to be parsed by patterns found in File1 ($1).

Expected output: tab-delimited file, File3. File3 has to contain the same rows as File2, plus the corresponding value in File1 if the pattern is matched (end of the line, tab-separated).

File1 (tab-delimited):

ABC1    1    3
ABC2    4    3
ABC3    3    2
ABC4    3    3

File2 (tab-delimited):

text1   ABC1-AB   text2   text3
text2   ABC2-AB   text1   
text3   ABC1-CD   text2
text4   ABC5-AB   text3   text4

File3:

text1   ABC1-AB   text2   text3    1    3
text2   ABC2-AB   text1    4    3
text3   ABC1-CD   text2    1    3
text4   ABC5-AB   text3   text4

Answer 1

With awk:

awk 'FNR==NR{a[$1]=$2"\t"$3} FNR!=NR{split($2,b,"-"); $0=$0"\t"a[b[1]];print}' file1 file2

• FNR==NR applies to the first file file1:
  • a[$1]=$2"\t"$3 fill the array a with the 2nd and 3rd field and as index use the 1st field.
• FNR!=NR applies to the second file file2:
  • split($2,b,"-") splits the 2nd field at -.
  • $0=$0"\t"a[b[1]] appends the two values to the line.
  • print prints the line.

The output:

text1   ABC1-AB text2   text3   1   3
text2   ABC2-AB text1   4   3
text3   ABC1-CD text2   1   3
text4   ABC5-AB text3   text4

Answer 2

Tested using BASH.

while read LINEFILE2
do
  printf "$LINEFILE2" >> File3
  while read LINEFILE1
  do
    read PATTERN ADDITIONS <<EOF
$LINEFILE1
EOF
    [ ! -z "$PATTERN" ] && [ ! -z $(echo "$LINEFILE2" | cut -f2 | grep "$PATTERN") ] && {
      printf "\t$ADDITIONS\n" >> File3
    }
  done < File1
done < File2

If you would like the pattern to be found inside the entire line and not only in column 2 you could get the cut command out of the pipe construction.