Biology heatmaps are useful to summarize results, such as in this example:
Need: create a heatmap from values in a database table. The table has three fields, one for names of rows, another for names of columns, and value.
Solution:
- Make a table in HTML
- Set the background color of each cell depending upon the value of each database record.
- The color coding preferred by my boss is from blue to black and then to yellow:
- Find out what the minimum and maximum value are supposed to be. In my case it was 0.9 and 1.1
- Make a list of colors such as colors.php (if you use this file, rename it from .php.txt to .php, I appended .txt for easy display/view on browser).
- Use a formula that will get you a given color depending upon an input value.
- My formula requires to know the ‘start’ value, in this case 0.9; it also needs to know ‘half’ of the heatmap, in this case 0.1
- Example of getting the index value in php, where $value is the input value:
- $index = round( ( ( $value ) – $start ) / ( $half * 2 / ( count( $colors ) – 1 ) ) )
- Then the color to use as background color in each cell would be: $color = $colors[ $index ]
- Display a ‘legend’ that tells the user the range of colors for given values.
- My legend is automatically generated using ‘start’ and ‘half’ values, here’s an example for a legend that ranges from -1 to 1
- Actually, I have it as a file that gets included into the file containing the heatmap. Legend php file: legend.php
- I should mention that the file containing the heatmap must include the colors.php before including legend.php
- When things go OK, heatmap should look pretty. Example:
- Missing values should use a different color, such as gray.
- Last note: it was not necessary to care about efficiency when displaying the heatmap. One query was used to remember the rows and columns. Then nested loops performed queries to retrieve the value of each cell in the table. That is, for ‘c’ columns and ‘r’ rows, there were c * r queries to the database, which was OK in heatmaps on the range of 100 rows by 20 columns.
Later, the database table had to be updated via a text file. These steps were done to update it:
- Truncate the existing table
- Make sure that there is no header line (such as cross1 cross2 value)
- Since we’re in linux, make sure that end-of-line characters are not an issue (‘vi’ tells me if file is DOS, if so, do: set ff=unix).
- Load the new file into the table via SQL command: load data local infile ‘heatmap.txt’ into table heatmap
- Verify the minimum and maximum value in the table so that the heatmap can display colors correctly