0

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
Improve this question

2 Answers 2

Reset to default
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.

Improve this answer
3
  • This is great! But I found a bug, some match more than once. Can it be modified such that once a row from file1 matches with file2 it isn't reused if it matches again with another row in file2?
    – Age87
    May 29, 2020 at 17:44
  • I've edited the question. What if file1 is compared to file2 in chunks. First going through all the rows with a particular name (column 1), saw NW_006532398.1, and in ascending order down column two. Then the rows with NW_006532656.1 get compared. I think that would fix it.
    – Age87
    May 29, 2020 at 20:01
  • Updated my answer.
    – Freddy
    May 29, 2020 at 21:12
0

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.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .