[IPython-dev] Visual representation of SQL extracted data

[Mark Voorhies]

On 05/14/2014 10:55 PM, Simon Cropper wrote:
> Hi,
>
> New to iPython and the standard set of tools bound to the server (e.g.
> Pandas, etc).
>
> What I was wondering is there a library, or a notebook showing the use
> of the standard libraries bound to iPython, that allows tabular data to
> be formatted.
>
> Most examples I can find are essentially text dumps to the notebook. In
> contrast, graphs and images have great formatting ability.
>
> Why? I am looking at using iPython to data wrangle or munge large
> biological datasets (plant names, biological and ecological attributes,
> locational data). The notebook feature will allow others to visualize
> and follow steps used to collate, convert, merge, summarize and analyze
> this data.
>
> Is this doable, or am I trying to use the notebook in the wrong way?
>
> Any feedback would be appreciated.
>

I think Pandas is good at this, but I haven't played with it.

A general strategy is to use HTML from IPython.core.display to render pretty HTML tables.

Here's an example for tabular display of protein sequence alignments (with a horizontal
scrollbar and green/red coloring of gaps/mutations):

def tabformat(seqs, names = None):
     """Return the given sequence alignment as a pretty HTML table.
     seqs = list of strings with a common length (one letter amino-acid codes or "-" for gap)
     names = sequence names as list of strings, same length as seqs
     """
     if(names is None):
         names = [str(i) for i in xrange(len(seqs))]
     retval = '<DIV style="font-family: monospace; font-size: small">\n'
     retval += '<DIV><TABLE style="overflow: auto; position: relative; float: left; width: 8em">\n'
     for n in names:
         retval += "<TR><TD>%s</TD></TR>\n" % n
     retval += "</TABLE></DIV>\n"
     retval += '<DIV style="overflow: auto; width: 90em; position: relative; float: left"><TABLE>\n'
     for i in seqs:
         retval += "<TR>\n"
         for (j,k) in zip(i,seqs[0]):
             if(j != k):
                 if(j == "-"):
                     c = 'style="background-color: green"'
                 else:
                     c = 'style="background-color: red"'
             else:
                 c = ""
             retval += '<TD %s>%s</TD>' % (c,j)
         retval += "</TR>\n"
     retval += "</TABLE></DIV>\n"
     retval += "</DIV>\n"
     return retval

from IPython.core.display import HTML
HTML(data = tabformat([i*100 for i in ("SPAM", "SPAM", "SCAM", "SPAM", "SPA-", "SPAM", "SPAN")]))

--Mark