Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

I want to compare File1 and File2 (Separated by spaces) using five fields (Column 1,2,4,5,6).

*Logic:*If column 1 and 2 of File1 and File2 match exactly and if the File2 has the same characters as any of the characters present in column 4 and 5 of file1 then those lines of file1 and file2 are concatenated and redirected as output.

File1:

s2/80   20      .       A       T       86      N=2     F=5;U=4
s2/20   10      .       G       T       90      N=2     F=5;U=4
s2/90   60      .       C       G       30      N=2     F=5;U=4

File2:

s2/90   60      .       G       G       97      N=2     F=5;U=4
s2/80   20      .       A       A       20      N=2     F=5;U=4
s2/15   11      .       A       A       22      N=2     F=5;U=4
s2/90   21      .       C       C       82      N=2     F=5;U=4
s2/20   10      .       G       G       99      N=2     F=5;U=4
s2/80   10      .       T       G       11      N=2     F=5;U=4
s2/90   60      .       G       T       55      N=2     F=5;U=4

Output:

s2/80  20 . A   T   86  N=2 F=5;U=4  s2/80  20  . A   A   20   N=2     F=5;U=4
s2/20  10 . G   T   90  N=2 F=5;U=4  s2/20  10  . G   G   99   N=2     F=5;U=4
s2/90  60 . C   G   30  N=2 F=5;U=4  s2/90  60  . G   G   97   N=2     F=5;U=4

I'm new in this field and would appreciate any guidance.

share|improve this question
    
About how many lines are you talking? Are both files the same size or is one rather small? –  ott-- Aug 13 '13 at 15:58
1  
Can fields 4 and 5 be more than one character long? Do you mean that you want to check whether the set intersection of {file1:fields 4,5} and {file2:fields 4,5} is not empty? Why do you mention field 6, since it doesn't seem to fit into your matching algorithm? Do you care about the order of the lines in the output? (Writing a precise problem description is the first step to writing a program.) –  rici Aug 13 '13 at 17:37
    
Is there a typo in your 3rd line of your sample output? The bit from the 2nd file shows "G T", but your sample output shows "G G". –  slm Aug 13 '13 at 20:00
    
@ott: No both files do not have the same number of lines. –  Namrata Aug 14 '13 at 10:19
    
@slm : No, in the output file there is no typo. The File 2 has s2/90 60 . G G 97 N=2 F=5;U=4 and s2/90 60 . G T 55 N=2 F=5;U=4. But the output file should have the one before "G G" because the Logic is if column 1 and 2 matches exactly then it goes to column 4 and 5. If column 4 and 5 of file 1 has "X Y" then from file2 it should only capture those with "X X" or "Y Y". –  Namrata Aug 14 '13 at 10:29

1 Answer 1

up vote 4 down vote accepted

Here's a solution using just awk. Put the below code in a file called ex.awk:

BEGIN{}
FNR==NR{
    k=$1" "$2
    a[k]=$4" "$5
    b[k]=$0
    c[k]=$4
    d[k]=$5
    next
}

{ k=$1" "$2
  lc=c[k]
  ld=d[k]
  # file1 file2
  if ((k in a) && ($4==$5) && (lc==$4) || (ld==$5)) print b[k]" "$0
}

And then run it like this with the above 2 files:

$ awk -f ex.awk file1 file2

Example

The sed is just to format the output for StackExchange!

$ awk -f ex.awk file1 file2 | sed 's/[ ]\+/  /g'
s2/90  60  .  C  G  30  N=2  F=5;U=4  s2/90  60  .  G  G  97  N=2  F=5;U=4
s2/80  20  .  A  T  86  N=2  F=5;U=4  s2/80  20  .  A  A  20  N=2  F=5;U=4
s2/20  10  .  G  T  90  N=2  F=5;U=4  s2/20  10  .  G  G  99  N=2  F=5;U=4

A change in requirements

The OP mentioned in the comments below that he'd like the ultimate solution to drop any lines where the 4th and 5th columns from file1 matched the 4th and 5th columns from file2.

For example, add this line to both file1 & file2:

s2/40   40      .       S       S       90      N=2     F=5;U=4

A single line addition to the original solution can address this particular change in the requirements.

if ((k in a) && (lc==$4) && (ld==$5)) next

New Example

ex2.awk:

BEGIN{}
FNR==NR{
  k=$1" "$2
  a[k]=$4" "$5
  b[k]=$0
  c[k]=$4
  d[k]=$5
  next
}

{ k=$1" "$2
  lc=c[k]
  ld=d[k]
  if ((k in a) && (lc==$4) && (ld==$5)) next
  if ((k in a) && ($4==$5) && (lc==$4) || (ld==$5)) print b[k]" "$0
}

Rerunning the new awk script, ex2.awk:

$ awk -f ex2.awk file1 file2 | sed 's/[ ]\+/  /g'
s2/90  60  .  C  G  30  N=2  F=5;U=4  s2/90  60  .  G  G  97  N=2  F=5;U=4
s2/80  20  .  A  T  86  N=2  F=5;U=4  s2/80  20  .  A  A  20  N=2  F=5;U=4
s2/20  10  .  G  T  90  N=2  F=5;U=4  s2/20  10  .  G  G  99  N=2  F=5;U=4
share|improve this answer
    
Hi slm, Thanks for your quick answer. But my last line should be s2/90 60 . C G 30 N=2 F=5;U=4 s2/90 60 . G G 97 N=2 F=5;U=4. Logic: Column 1 and Column 2 of file 1 and 2 should exactly match and if column 4 and 5 of File 1 has "X Y" then it should look for either "X X" or "Y Y" in file 2. No other combination. –  Namrata Aug 14 '13 at 10:33
    
It works perfect. Thanks Slm. I appreciate your help. –  Namrata Aug 15 '13 at 12:28
    
@Namrata - this code doesn't work if you have more than one match in file1, BTW. So if you have 2 "s2/90 60" the 2nd one will be the one that's used. That's a limitation of your algorithm. You probably know this but just letting you know. –  slm Aug 15 '13 at 12:29
    
I found out, if there is an overlap between the File1 and File2 it also printing that as output. I added another line "s2/40 40 . S S 90 N=2 F=5;U=4" to both File1 and File2 and I get this line concatenated in the output. But I do not want to have this kind of intersection in my output file. –  Namrata Aug 16 '13 at 11:47
    
Can you please help me getting the correct output file. –  Namrata Aug 16 '13 at 18:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.