Common records after matching on different columns

jacobs.smith · February 9, 2012, 4:20pm

Hi,

I have the following files.

cat 1.txt

cat 2.txt

output.txt

The logic is as follows....

chr1 in column1 of file1 should be matched to chr1 in column1 of file2.

Any value that is equal or 300 plus/minus range of the value in column2 of file1 matches to column2 of file2, (i.e., if column2 of file1 is 500, then the value in column2 of file2 can be 500, or between 200 and 500, or between 500 and 800) they should be printed.

Any value that is equal or 300 plus/minus range of the value in column3 of file1 matches to column3 of file2, (i.e., if column3 of file1 is 800, then the value in column3 of file2 can be 800, or between 500 and 800, or between 800 and 1100) they should be printed.

Also, anything that is in the range of column2 and column3 should be printed.
Ex: If file 1 has this record chr2 300 400, and file2 has this record chr1 301 383, both of them should be printed.

Each record is matched to each record in both these files.

I am looking for something that can be used across multiple files that are more than two.

Thanks a ton in advance. I know it is a pain. But, please help me.

---------- Post updated 02-09-12 at 09:59 AM ---------- Previous update was 02-08-12 at 02:18 PM ----------

Please guys. Someone help me out. :(:(:(:(:(:(:(

---------- Post updated at 04:19 PM ---------- Previous update was at 09:59 AM ----------

Any thoughts by anyone?

---------- Post updated at 04:20 PM ---------- Previous update was at 04:19 PM ----------

Any thoughts by anyone?

Shell_Life · February 9, 2012, 4:31pm

You had a very similar post that I provided a solution using shell scripts:

Change it according to your new specifications.

jacobs.smith · February 9, 2012, 4:32pm

That shell script for my earlier solution didn't output some of the records.

Is there another alternative?

Corona688 · February 9, 2012, 4:50pm

If you go back and explain exactly how it didn't work, and show the data which doesn't work, it can probably be fixed.

Asking for a whole new solution might leave you with the same problem as before.

jacobs.smith · February 9, 2012, 5:08pm

Thanks to both of you for leading me in some way or the other.

The shell script produces the following output.

opfromshell.txt

But, I was looking for

Originaloutput.txt

Please, no offense. Since the earlier replies in that post worked, I didn't want to bother Shell_life.

Any help is highly appreciated.

Thanks in advance.

Shell_Life · February 10, 2012, 10:28am

jacobs.smith:

The shell script produces the following output.

opfromshell.txt
chr1 300 400 1.txt
chr1 350 467 1.txt 
But, I was looking for

Originaloutput.txt
chr1 300 400 1.txt
chr1 350 467 1.txt
chr1 201 299 2.txt
chr2 800 1000 2.txt
chr2 100 200 2.txt
chr3 500 600 2.txt 
Please, no offense. Since the earlier replies in that post worked, I didn't want to bother Shell_life.

If you had noticed, I made the two file names as variables for the shell.

This way, to have your desired output, you just run it twice:
1)

mF1='1.txt'
mF2='2.txt'

2)

mF1='2.txt'
mF2='1.txt'

Here is the same solution again:

#!/bin/ksh
typeset -i mFromA mToA mFromB mToB
mF1='1.txt'      ### <======= Change file name here (I)
mF2='2.txt'      ### <======= Change file name here (II)
mPrevTag=''
#### sort is used to reduce the number of "grep"
sort ${mF1} | while read mTagA mFromA mToA; do
  if [[ "${mTagA}" != "${mPrevTag}" ]]; then
    grep "${mTagA}" ${mF2} > ${mF2}.tmp
  fi
  mFound="N"
  while read mTagB mFromB mToB; do
    if [[ ${mToA} -ge ${mFromB} && ${mFromA} -le ${mToB} ]]; then
      mFound="Y"
      break
    fi
  done < ${mF2}.tmp
  if [[ "${mFound}" = "N" ]]; then
    echo ${mTagA} ${mFromA} ${mToA} ${mF1}
  fi
  mPrevTag=${mTagA}
done

jacobs.smith · February 15, 2012, 10:36am

Hi guys,

Somehow I managed to request one of my other friends for a perl script. He was able to write one that could do my task. But, it was only for 2 files.

I would like to request any of you to edit the following code so that it lets me do the task for multiple number of files and multiple cut-offs.

To be clear, I would like to specify at STDIN or while running the code the number of files and the different cutoff.

Thanks to all of you in advance.

#!/usr/bin/perl
$file1="1.txt";
$file2="2.txt";
open(FILE, "$file1") || die "can't: $!";

open(OUTPUT, ">op.txt") || die "can't: $!";
%hash='';
$i=1;
while(<FILE>)
{
$line=$;
chomp($line);
@a=split(/\s+/,"$line");
open(FILE1,"$file2") || die "can't: $!";
while(<FILE1>)
{
$line1=$;
chomp($line1);
@b=split(/\s+/,"$line1");
if($a[0] eq $b[0])
{
if($a[1] == $b[1])
{
if($a[2] == $b[2])
{
@x=split(/\./,"$file1");
@y=split(/\./,"$file2");
if(!exists $hash{$line})
{
$hash{$line}=1;
print OUTPUT "$a[0]\t$a[1]\t$a[2]\t$x[0]$y[0].txt\n";
}
}
}
}

if($a[0] eq $b[0])
{
if($a[1] < $b[2])
{
if($a[2] > $b[1])
{
if(!exists $hash{$line})
{
$hash{$line}=1;
print OUTPUT "$a[0]\t$a[1]\t$a[2]\t$file1\n";
}
if(!exists $hash{$line1})
{
$hash{$line1}=1;
print OUTPUT "$b[0]\t$b[1]\t$b[2]\t$file2\n";
}
}
}
}

if($a[0] eq $b[0])
{
if(abs($a[1] - $b[2]) <= 300 || abs($a[2] - $b[1]) <=300)
{
if(!exists $hash{$line})
{
$hash{$line}=1;
print OUTPUT "$a[0]\t$a[1]\t$a[2]\t$file1\n";
}
if(!exists $hash{$line1})
{
$hash{$line1}=1;
print OUTPUT "$b[0]\t$b[1]\t$b[2]\t$file2\n";
}
}
}

}
}

---------- Post updated 02-15-12 at 10:36 AM ---------- Previous update was 02-14-12 at 11:28 AM ----------

Please help me. It is a very important task:wall::wall::wall::wall::wall::wall:

jacobs.smith · February 16, 2012, 10:49am

Please, atleast give me a thought on how to deal this.

I shall try.

jacobs.smith · February 17, 2012, 10:55am

Please check the attachment. I have given a detailed explanation. Any helps are highly appreciated.

Shell_Life · February 17, 2012, 11:03am

Not true - you are not trying at all.

I gave you a complete solution in this thread and you continue to ask for alternative solutions.

What do you really want to accomplish with so many postings in this thread?

jacobs.smith · February 17, 2012, 3:45pm

I am a novice at shell scripting.

That was why I asked if someone can help me out in Perl.

Just because you gave me something that I dont even know the basics, doesn't mean that I have to bang my head with it.

We come here just for solutions with prior tries. I got a try in Perl through someone who are good at programming like you and posted a more detailed version of my task to accomplish things.

FYI, my replies are not to annoy anyone or bug someone. I know how fast people help in this forum when they know how to deal things. Keeping that in mind, I posted a more detailed description for their thinking.

Thanks in advance.