Comparing Two Tables And Display...

Question

[Empyrean]

Hello all.. i am new to shell scripting.. learning the things..i am struck at a place here..

I have divided one table in two two tables for comparision..

Query Type Start Stop
X1 Left 1000 1025
X1 Left 2000 2025
X1 Left 10000 10025
X2 Left 1050 1075
X2 Left 3050 3075
X3 Left 9000 9025
Y1 Left 3000 3025
Y1 Left 4500 4525
Y2 Left 7000 7025
Y2 Left 8200 8225
Y2 Left 2000 2025



Q2 T2 start2 stop2
X1 Right 1050 1075
X1 Right 3000 3025
X1 Right 20000 20025
X2 Right 2050 2075
X2 Right 7050 7075
X3 Right 9050 9075
Y1 Right 4000 4025
Y1 Right 5500 5525
Y2 Right 7700 7725
Y2 Right 9200 9225
Y2 Right 14000 14025

Now I want to compare all X1's in first table with X1's in second table, X2's with X2's like that and Subtract first table stop column from second table and Out of all combinations display results of pairs which have difference less than 3000

Query Type Start Stop Q2 T2 start2 stop2 Difference
X1 Left 1000 1025    X1 Right 1050 1075 50
X1 Left 1000 1025 X1 Right 3000 3025 2000
X1 Left 2000 2025 X1 Right 3000 3025 1000
X2 Left 1050 1075 X2 Right 2050 2075 1000
X2 Left 3050 3075 X2 Right 2050 2075 2000
X3 Left 9000 9025 X3 Right 9050 9075 50
Y1 Left 3000 3025 Y1 Right 4000 4025 1000
Y1 Left 3000 3025 Y1 Right 5500 5525 2500
Y1 Left 4500 4525 Y1 Right 4000 4025 500
Y1 Left 4500 4525 Y1 Right 5500 5525 1000
Y2 Left 7000 7025 Y2 Right 7700 7725 700
Y2 Left 8200 8225 Y2 Right 9200 9225 1000
Y2 Left 7000 7025 Y2 Right 9200 9225 2200
Y2 Left 8200 8225 Y2 Right 7700 7725 500

Answer 1

[Pierre Lindenbaum]

• sort table 1 on first column using unix 'sort'
• sort table 2 on first column using unix 'sort'
• join both tables on first column using unix 'join'
• pipe the result into awk to calculate the difference and print the result

hope it helps.

Edit: your problem is not strictly related to bioinformatics; You should ask this kind of question on http://stackoverflow.com/ or http://unix.stackexchange.com/

[Empyrean]

actually it is.. i generalized it.. i wanted to compare the sequences for primers here..

[Empyrean]

Thank you for help.. i am working on it now

[lh3]

If the input always have identical number of lines and in the same order, "paste" is the right tool.

Answer 2

[Casey Bergman]

What you have is a basic join & filter task on your hands. This is a very common problem/task and there are many ways to solve this problem, including using the "Join", "Text Manipulation" and "Filter" functions available in Galaxy.

To show how your data can be processed reproducibly in Galaxy, see the workflow I've made for your data. To run this again, you'll need to "Get Data->Upload File" for both files and then use as input to the biostar-7746 workflow.

From performing this analysis, I see that your textual specification to "display results of pairs which have difference less than 3000" is not exactly what you display in your output table, which appears to include rows with an absolute value < 3000. If, as you say in your comment to Pierre, you are working on primer mapping, then you'll need to think carefully about whether you want to include values between -3000 and 0. If left and right are forward and reverse primers, then I don't think you'll get an amplification product for these primer pairs.

Answer 3

[Tim Webster]

If you are secure in your unixy-ness, it is OK to go ahead and use excel for this.

[Pierre Lindenbaum]

No, make the world a better place . Please, don't use excel. A: What Are The Most Common Stupid Mistakes In Bioinformatics?

[Pierre Poulain]

Indeed, Excel can be really annoying sometimes.

"Mistaken Identifiers: Gene name errors can be introduced inadvertently when using Excel in bioinformatics"

[Madelaine Gogol]

If you do decide to use evil Excel, you would want to use vlookup to join the columns. Of course, I would never do such a thing...

Make sure you import any gene names as text in Excel so they don't get converted to dates or whatever.