0-1-2 Matrix from heatmap table

Question

My goal is to get a 0-1-2 matrix from the information provided in the mysql table.

Example:

Table 'Dom'

Origin|Species | Domain KMT1 | blabla_1234 | Kringel KMT1 | blabla_1234 | Helix KMT1 | wobble_4556 | Kringel KMT2 | blabla_1234 | Helix KMT2 | piffi_876 | Kringel 

Now I want to have the following matrix: on the x-axis there will be all kinds - Something like this:

 blabla_1234 | wobble_4556 | piffi_876 

On the y axis there will be all Domains from the House table, but they should be grouped by their agreement with KMT (Origin). For example, the y axis would look like this:

 KMT1 Helix KMT1 Kringel KMT2 Helix KMT2 Kringel 

The result should be as follows:

Orange means that for this species there are both a domain and a source protein. Yellow means that only protein is found in the species, but not in the domain. I just found out how to use MySQL, and I never wrote scripts. Can you give me a hint so that the program / script I could complete this task?

Thank you very much in advance!

Answer 1

Given that your Dom table contains only positive, not negative, values, you want to generate negatives so that your output table can display them.

This query will give you all possible combinations of origin, domain and species:

 SELECT ds.domain, os.origin, ss.species FROM (SELECT DISTINCT domain FROM Dom) ds CROSS JOIN (SELECT DISTINCT origin FROM Dom) os CROSS JOIN (SELECT DISTINCT species FROM Dom) ss 

So, to get the "expression values" for each combination, including negatives, do the following:

 SELECT completelist.domain, completelist.origin, completelist.species, COALESCE(Dom.species, FALSE) AS found FROM (SELECT ds.domain, os.origin, ss.species FROM (SELECT DISTINCT domain FROM Dom) ds CROSS JOIN (SELECT DISTINCT origin FROM Dom) os CROSS JOIN (SELECT DISTINCT species FROM Dom) ss ) AS completelist LEFT JOIN Dom ON (completelist.domain = Dom.domain && completelist.origin = Dom.origin && completelist.species = Dom.species) 

Once you have an array of records from your database, you can then output the table as an html . You would style (using css ) <td> according to the value of the expression. Thus, your result will look something like this:

 <table> <thead> <tr> <th>Origin</th> <th>Domain</th> <th>Blahbla_1234</th> <th>wobble_4556</th> <th>piffi_876</th> </tr> </thead> <tbody> <tr> <th>KMT1</th> <th>Kringel</th> <td class='bothexist'>&nbsp;</td> <td class='bothexist'>&nbsp;</td> <td class='onlyprotein'>&nbsp;</td> </tr> <tr> <th>KMT1</th> <th>Helix</th> <td class='bothexist'>&nbsp;</td> <td class='onlyprotein'>&nbsp;</td> <td>&nbsp;</td> </tr> <!-- etc. --> </tbody> <tfoot></tfoot> <table> 

The part that is inconvenient reorganizes the data that comes from your request into a structure from which it is easy to display a table.

In any case, although you can use a shell script to do all this, you will probably find it easier to use a higher level language. perl is traditionally used in bioinformatics, and there are many good libraries, including perhaps the most important BioPerl . python and ruby are also popular. php is a very popular and common language, especially adapted to websites and considered very easy to learn (but many programmers object to its various weaknesses).

Hope this points to a useful direction.