joining tables by row based on overlap of columns

Question

I want to align file1 with file2, first by matching the 3rd, 5th, 7th columns from each file and placing rows in file1 beside the corresponding rows on file2

This is the code I have to keep overlap between the two, but I would like to append file2 with file1. Any help is appreciated!

awk 'NR==FNR {a[$3$5$7];next}($3$5$7 in a){print $0}' 

file1

#Scaffold   Position    Ref. Nuclueotide    Pid allele count    Pid allele  Non-pid allele count    Non-pid allele  Overlapping gene/region
NW_006532398.1  202035  C   35:0:0:0:0:0    A   0:0:29:0:0:0    C   KCND2
NW_006532656.1  289646  C   0:26:0:0:0:0    T   0:0:26:0:0:0    C   CNTN1
NW_006532656.1  289656  C   0:26:0:0:0:0    T   0:0:26:0:0:0    C   CNTN1
NW_006532656.1  289666  A   0:26:0:0:0:0    T   0:0:26:0:0:0    C   CNTN1
NW_006532656.1  289676  A   0:26:0:0:0:0    T   0:0:26:0:0:0    C   CNTN1
NW_006532657.1  636 A   0:26:0:0:0:0    T   0:0:26:0:0:0    C   CNTN1
NW_006532657.1  646 C   0:26:0:0:0:0    T   0:0:26:0:0:0    C   CNTN1
NW_006532657.1  656 C   0:26:0:0:0:0    T   0:0:26:0:0:0    C   CNTN1
NW_006532658.1  345 C   0:26:0:0:0:0    T   0:0:26:0:0:0    C   CNTN1

file2

#Scaffold   Position    Ref. Nuclueotide    Pid allele count    Pid allele  Non-pid allele count    Non-pid allele
    HiC_scaffold_7  49526089    C   0:0:28:0:0:0    A   0:31:0:0:0:0    C
    HiC_scaffold_7  49539537    C   27:0:0:0:0:0    T   0:0:0:29:0:0    C
    HiC_scaffold_7  49972546    C   20:0:0:0:0:0    T   0:0:0:27:0:0    C
    HiC_scaffold_7  49972556    A   20:0:0:0:0:0    T   0:0:0:27:0:0    C
    HiC_scaffold_7  49972566    A   20:0:0:0:0:0    T   0:0:0:27:0:0    C
    HiC_scaffold_7  49972576    A   20:0:0:0:0:0    T   0:0:0:27:0:0    C
    HiC_scaffold_7  49972586    C   20:0:0:0:0:0    T   0:0:0:27:0:0    C
    HiC_scaffold_7  49972596    C   20:0:0:0:0:0    T   0:0:0:27:0:0    C
    HiC_scaffold_7  49972599    A   20:0:0:0:0:0    A   0:0:0:27:0:0    C
    HiC_scaffold_7  49972646    C   20:0:0:0:0:0    T   0:0:0:27:0:0    C

file3

#Scaffold   Position    Ref. Nuclueotide    Pid allele count    Pid allele  Non-pid allele count    Non-pid allele   Scaffold   Position    Ref. Nuclueotide    Pid allele count    Pid allele  Non-pid allele count    Non-pid allele  
    HiC_scaffold_7  49526089    C   0:0:28:0:0:0    A   0:31:0:0:0:0    C   NW_006532398.1  202035  C   35:0:0:0:0:0    A   0:0:29:0:0:0    C   KCND2
    HiC_scaffold_7  49539537    C   27:0:0:0:0:0    T   0:0:0:29:0:0    C   NW_006532656.1  289646  C   0:26:0:0:0:0    T   0:0:26:0:0:0    C   CNTN1
    HiC_scaffold_7  49972546    C   20:0:0:0:0:0    T   0:0:0:27:0:0    C   NW_006532656.1  289656  C   0:26:0:0:0:0    T   0:0:26:0:0:0    C   CNTN1
    HiC_scaffold_7  49972556    A   20:0:0:0:0:0    T   0:0:0:27:0:0    C   NW_006532656.1  289666  A   0:26:0:0:0:0    T   0:0:26:0:0:0    C   CNTN1
    HiC_scaffold_7  49972566    A   20:0:0:0:0:0    T   0:0:0:27:0:0    C   NW_006532656.1  289676  A   0:26:0:0:0:0    T   0:0:26:0:0:0    C   CNTN1
    HiC_scaffold_7  49972576    A   20:0:0:0:0:0    T   0:0:0:27:0:0    C   NW_006532657.1  636 A   0:26:0:0:0:0    T   0:0:26:0:0:0    C   CNTN1
    HiC_scaffold_7  49972586    C   20:0:0:0:0:0    T   0:0:0:27:0:0    C   NW_006532657.1  646 C   0:26:0:0:0:0    T   0:0:26:0:0:0    C   CNTN1
    HiC_scaffold_7  49972596    C   20:0:0:0:0:0    T   0:0:0:27:0:0    C   NW_006532657.1  656 C   0:26:0:0:0:0    T   0:0:26:0:0:0    C   CNTN1
    HiC_scaffold_7  49972599    A   20:0:0:0:0:0    A   0:0:0:27:0:0    C                               
    HiC_scaffold_7  49972646    C   20:0:0:0:0:0    T   0:0:0:27:0:0    C   NW_006532658.1  345 C   0:26:0:0:0:0    T   0:0:26:0:0:0    C   CNTN1

Answer 1

First, edit file2 and remove the leading tabs or the fields 3,5,7 won't match.

awk -F"\t" '
  NR==FNR{
    a[FNR]=$3","$5","$7                  # save fields 3,5,7
    b[FNR]=(FNR==1 ? substr($0, 2) : $0) # save the line (remove first char on 1st line)
    last=FNR                             # save the last line nr
    next                                 # continue with next line
  }
  {
    printf $0                            # print current line
    for (i=1;i<=last;i++){
      if(a[i]==$3","$5","$7){            # fields 3,5,7 match?
        printf FS b[i]                   # append matching line
        a[i]=""                          # clear value
        break                            # break loop
      }
    }
    print ""
  }
' file1 file2

Save fields 3,5,7 in array a and the current line $0 in array b (or $0 minus the first character # if it's the header).
When file2 is processed, find the first matching element in array a and use its index to append the line of array b and clear the element of array a afterwards.

Answer 2

I have done similar things in the past by combining egrep with sort; something like:

egrep '[A-z0-9]' file1 file2 | xargs -L1 | sed -e's/:/ /g' | sort -k4,4 -k6,6 -k8,8 -k1,1 

(with the last 1,1 key of the sort being the first column, which contains the names of the files.) Of course, if any of the columns need to be sorted by numerical value, say column 3 in the original file, you can do -k4,4g or -k4,4gr to reverse the sort order. I don't know how you need the sorting to be in the other columns. Once you have interweaving from "sort" you can continue the pipe to awk, with print conditions based on the contents of the first column (the file names). On my own computers, I have always avoided blank spaces in file names, since it makes these kind of manipulations easier. "egrep" is used to get all lines and is just a way to get the filename printed at the beginning of the line. This way, you can use the filename in "sort" to interweave in a way that lets you get the columns you want on the left. Have not tested the following (maybe bugs, maybe clunky) but it might be a start.

egrep '[A-z0-9]' $file1 $file2 | xargs -L1 | sed -e's/:/ /g' | sort -k4,4 -k6,6 -k8,8 -k1,1 | awk -vf1="${file1}" -vf2="${file2}" 'BEGIN {last="XXX"} { if ($1==f1) { if (last != "XXX") printf("\n"); } else { if (last==f2) printf("\n blank blank blank "); } ; printf("%s %s %s",$4,$6,$8); last=$1 } END {printf("\n");} ' > output.txt

sed meant to remove the ":" that egrep puts in, leaving the filename in a separate first column. You may need -1,1r, in the "sort" if file2 filename comes first in alphabetical order. Meant to have file1 at the left of the output.txt file

Also presumes that none of the columns in either file have blank spaces in the stored information. "sort" will mess up the position of the column header, so you would need to deal with that separately.