Dreaming about bio-spreadsheet

19 May

One of the often occuring task in my work is to present results of an analysis in some kind of table. I have used for such purpose quite a number of approaches, starting from generating simple HTML file, through fetching of SQL data into table stored in a wiki, up to using Rails. One of the dreams I have recently is a web-based spreadsheet that would allow me to apply some specific piece of code over every row/column and show resulting table.

ScreenshotA simple mockup is shown above. In this example, a code:

print " <img src="{column_1}_bio_r_250.jpg>"

… iterated over first column containing PDB codes, would substitute these codes with an image of a protein from PDB server.

In other words I dream about simple (single file would be the best – I like the approach Sinatra framework is taking) web-based programmable spreadsheet. Something like Resolver One, but simpler. Is there anything like that available?


Posted by on May 19, 2009 in bioinformatics, Software


6 responses to “Dreaming about bio-spreadsheet

  1. Marcin Cieslik

    May 19, 2009 at 16:10

    So data is in rows and columns are “transformations”. Why do you think this is superior to a flow-chart with vector(i.e. rows) input?

  2. Burke Squires

    May 20, 2009 at 18:18

    I am not sure is this fits the bill…but some folks at the EBI have created a java-based platform for data storage and annotation. It is called ISAcreator and can be found here:


  3. Tyler

    May 23, 2009 at 22:30

    It seems like you could probably throw something together with dabbleDB that would fit the bill…

  4. Bogdan

    June 5, 2009 at 10:56

    I’m mostly stuck with Python doing this kind of things. Each time I just copy over the older script, change the number of items parsed from a single line of data file, and then change print statements 🙂

    That’s fairly easy, but I guess this could be extended to a more-or-less universal script, counting columns on its own, and accepting string parameters with $1, $2 … embedded to denote column contents.

    Also, there’s awk (gawk), which I guess could do the job best, but my knowledge of gawk is limited to simple column extraction, like
    gawk '{print $2"\t"$5}' datafile.tsv

  5. Bogdan

    June 5, 2009 at 10:57

    oops, missed a closing ‘ after right curly brace above

  6. Pawel Szczesny

    June 22, 2009 at 13:28

    Thank you all for the comments.

    Marcin, it’s all about presentation to the non-programming colleagues and looking for possibility to avoid Excel files :).

    Burke, thanks a lot, I wasn’t aware of ISAcreator. Need to have a look.

    Tyler, duh!, DableDB 🙂 I’ve used it so long ago I forgot about its existence.

    Bogdan, I have a similar workflow (but use Perl one-liners instead of gawk) however as I pointed to Marcin, it’s the “presentation” part that dictates other requirements :). I’ve corrected your gawk snippet.

%d bloggers like this: