Technical Tidbit of the Day

Monday, July 1, 2013

nbconvert: PDF from iPython Notebook

UPDATE 2013-07-19: For details on the alternative to nbconvert briefly mentioned below, long custom PDF page sizes, see On Mac, only Firefox can PDF without page breaks.

UPDATE 2013-07-15: See Quick link to download nbconvert for updated download location.

The official iPython Notebook documentation states that to convert a notebook to PDF, you should use your browser's "Print to PDF" capability. The problem is that that chops charts and graphs in half due to PDF pagination (unless you are able to configure a custom PDF paper size e.g. 60 inches long).

A command-line utility, nbconvert, which will eventually be merged into IPython but is not yet, nicely converts Notebooks to PDF. It even includes nice instructions on installing on a Mac, but the instructions are only 98% complete. Below are the missing steps:

Download the nbconvert package as a Zip and unzip to your home directory.
Follow the steps from https://github.com/ipython/nbconvert/blob/173bb08dd86d02a7485801969c94d4816913cd09/README.rst, specifically:
1. pip install jinja2
2. pip install markdown
3. curl http://docutils.svn.sourceforge.net/viewvc/docutils/trunk/docutils/?view=tar > docutils.tgz
4. pip install -U docutils.tgz
5. pip install pygments
6. sudo easy_install -U sphinx
7. Install PanDoc via the installer http://code.google.com/p/pandoc/downloads/list
8. Install MacTex via the .pkg http://www.tug.org/mactex/

Execute to convert your .ipynb to .pdf:

export PYTHONPATH=~/nbconvert-master/nbconvert/utils
python ~/nbconvert-master/nbconvert1/nbconvert.py --format=pdf MyNotebook.ipynb

Wednesday, June 26, 2013

Create empty DataFrame in Pandas

It seems like it should be a simple thing: create an empty DataFrame in the Pandas Python Data Analysis Library. But if you want to create a DataFrame that

is empty (has no records)
has datatypes
has columns in a specific order

...i.e. the equivalent of SQL's CREATE TABLE, then it's not obvious how to do it in Pandas, and I wasn't able to find any one web page that laid it all out. The trick is to use an empty Numpy ndarray in the DataFrame constructor:

df=DataFrame(np.zeros(0,dtype=[
('ProductID', 'i4'),
('ProductName', 'a50')]))

Then, to insert a single record:

df = df.append({'ProductID':1234, 'ProductName':'Widget'})

UPDATE 2013-07-18: Append is missing a parameter:

df = df.append({'ProductID':1234, 'ProductName':'Widget'},ignore_index=True)

Tuesday, June 18, 2013

Histogram thumbnails inside YUI3 data grids

Sparklines are tiny graphs added inline to data tables or lists. Sparklines have no units or gridlines; the purpose is just to quickly convey the shape of the data, which is usually presented adjacent.

A powerful variant on sparklines are sparkline histograms, or "sparkgrams" as I like to call them. Sparkgrams can quickly and compactly convey data distributions: outliers, right/left skew, normal vs. uniform distribution, etc. Because they are small, they can be included right inside a data table for every column or row. Such a presentation is useful either for completely known data when embarking upon data analysis, or for newly received data of a known data type in, for example, a manufacturing quality control scenario.

Below is an example of how it might look. The first column demonstrates an outlier, while the second column conveys a normal distribution.

Below is the YUI3 code to render the above.

<!DOCTYPE html>
<head>
 <script src="yui/build/yui/yui-min.js"></script>
 <script>
  YUI().use('datatable', 'charts', function(Y) {
   var fmt = function(o) {
    if (o.value.substring && o.value.substring(0,5) === "chart")
     o.cell.set("innerHTML", "<div id='" + o.value +
                "' style='width:75px;height:75px'></div>");
    else
     o.cell.set("text", o.value);
    return false;
   }

   var table = new Y.DataTable({columns: [{"key":"Indoors",  "nodeFormatter":fmt},
                                          {"key":"Outdoors", "nodeFormatter":fmt}],
                                data: [{"Indoors": 73.3, "Outdoors": 86.2},
                                       {"Indoors": 73.5, "Outdoors": 86.5},
                                       {"Indoors": 73.5, "Outdoors": 86.6},
                                       {"Indoors": 50.7, "Outdoors": 86.8},
                                       {"Indoors": "chart0", "Outdoors": "chart1"}],
                                render: "#table"});

   for (col = 0; col < table._displayColumns.length; col++) {
    var a = Array();
    for (row = 0; row < table.get("data").size()-1; row++)
     a.push(table.getCell([row,col]).get("text"));
    var NUM_BINS = 3;
    var amin = Math.min.apply(Math, a);
    var binwidth = 1.001 * (Math.max.apply(Math, a)-amin) / NUM_BINS;
    var data=Array();
    for (i=0; i<NUM_BINS; i++)
     data.push({category:amin+i*binwidth, values:0});
    for (i=0; i<a.length; i++)
     data[Math.floor((a[i]-amin)/binwidth)].values++;
    new Y.Chart({dataProvider: data, type:"column",
                 axes: {values: {position:"none"}, category: {position:"none"}},
                 render:"#chart"+col});
   }
  });
 </script>
</head>
<body>
 <div id="table" class="yui3-skin-sam"></div>
</body>
</html>

Thursday, June 13, 2013

Query Hive from iPython Notebook

iPython Notebook together with pandas comprise a data analysis system that is essentially a clone of R. The advantage over R is that Python code can be more easily converted into production code and executed, for example, on a web server.

One limitation is that the Apache-recommended Python interface to Hive requires installing Hive locally, which can be problematic or inconvenient from, say, a laptop used for analysis. A nice alternative is remotely executing Hive on the Hadoop cluster (or adjacent Linux server with access to the cluster). The alternative does require installing sshpass. At the end of the code below, the query results are stored in a pandas DataFrame with column names and automatic detection of data types.

from pandas import *
from StringIO import StringIO

s = "sshpass -f myfilewithpassword ssh myusername@myhostname \"hive -S -e \\\"" \
"set hive.cli.print.header=true;" \
"SELECT * from mytable;\\\"\""

t = !$s
df = read_csv(StringIO(t.n), sep='\t')

UPDATE 2013-08-01: It's a bit cleaner if one uses Python's triple-quoting mechanism as below. This allows one to copy and paste queries between iPython Notebook and Hive and Hue without having to reformat with quotes and backslashes.

from pandas import *
from StringIO import StringIO

s = """
sshpass -f myfilewithpassword ssh myusername@myhostname \"
hive -S -e \\\"
set hive.cli.print.header=true;
SELECT * from mytable;
\\\"\"
"""

t = !$s
df = read_csv(StringIO(t.n), sep='\t')

Tuesday, May 28, 2013

New in Hive 0.11: Windowing and analytics functions

It's not getting a lot of press, but Hive 0.11, released May 15, 2013, introduced a ton of new keywords. Hive 0.11 supports the SQL analytic functions similar to what popular RDBMS's introduced a decade ago with the SQL2003 standard, with the following new Hive keywords:

LEAD
LAG
FIRST_VALUE
LAST_VALUE
OVER
WINDOW
UNBOUNDED
PRECEDING
FOLLOWING
RANK
ROW_NUMBER
DENSE_RANK
CUME_DIST
PERCENT_RANK
NTILE

Sadly, the documentation is scanty, to say the least. For now, documentation and tutorials (e.g. sqlatoms.com) on equivalent keywords in Oracle and SQL Server, for example, will have to suffice.

Being able to window obviates some of the need for subqueries (specifically, pre-SQL2003 subqueries that just provided a limited GROUP BY in a separate SELECT column), which of course Hive does not support.

Monday, May 20, 2013

Arbitrary cell formatting in YUI3 DataTable

YUI is an underrated JavaScript framework, in my opinion. I suspect it isn't very popular because it had dependencies on Flash as late as 2011, and because there were major API changes between YUI2 and YUI3, resulting in a split community and a paucity of documentation and forum help for YUI3. But YUI3 is a rich and free collection of rich web front-end components.

An example of weak documentation and examples is custom formatting of DataTable cells. What was the "formatter" property in YUI2 has been split in YUI3 into "formatter" for changing the just cell text and "nodeFormatter" for changing (actually, supplying) the cell text and the cell property. Since when using a nodeFormatter, one must explicitly transfer the contents of the cell data to the cell display, I decided: why not just embed the formatting right into the cell data? Normally, it is expected that you would maintain a separate external array containing the formatting (e.g. an array of Booleans indicating which cells should be highlighted in red), but with the technique below, the formatting information is embedded right with the cell data in the form of a JavaScript/JSON object for each cell.


<!DOCTYPE html>
<head>
 <script src="yui/build/yui/yui-min.js"></script>
 <script>
  var fmt = function(o) {
      if (o.value) {
          if (o.value.value)
              o.cell.set('text', o.value.value);
          if (o.value.classname)
              o.td.setAttribute("class", o.value.classname);
      }
      return false;
  }

  var columns = [{"key":"Part #", "nodeFormatter":fmt},
                 {"key":"Part Name", "nodeFormatter":fmt}];
  var data = [{"Part #":{"value":1234},
               "Part Name":{"value":"Capacitor"}},
              {"Part #":{"value":5678},
               "Part Name":{"value":"Resistor", "classname":"redbackground"}}];

  YUI().use('datatable', function(Y) {
   new Y.DataTable({columns: columns, data: data, render: "#table"});
  });
 </script>
 <style>
  .redbackground { background: red; }
 </style>
</head>
<body>
 <div id="table" class="yui3-skin-sam"></div>
</body>
</html>

UPDATE 2013-05-23: Thanks to Luke Smith's post at the YUI forum, it turns out it is possible to use formatter, which is more performant than nodeFormatter. The trick, though, is using formatter requires a more specific CSS selector. Full code below.

<!DOCTYPE html>
<head>
 <script src="yui/build/yui/yui-min.js"></script>
 <script>
  var fmt = function(o) {
      if (o.value.classname)
          o.className = o.value.classname;
      return o.value.value;
  }

  var columns = [{"key":"Part #", "formatter":fmt},
                 {"key":"Part Name", "formatter":fmt}];
  var data = [{"Part #":{"value":1234},
               "Part Name":{"value":"Capacitor"}},
              {"Part #":{"value":5678},
               "Part Name":{"value":"Resistor", "classname":"redbackground"}}];

  YUI().use('datatable', function(Y) {
   new Y.DataTable({columns: columns, data: data, render: "#table"});
  });
 </script>
 <style>
  #table .redbackground { background: red; }
 </style>
</head>
<body>
 <div id="table" class="yui3-skin-sam"></div>
</body>
</html>

Monday, May 13, 2013

Escaping the HTML5 sandbox with Qt

HTML5 has a large number of powerful APIs that make it a candidate for cross-platform development of rich desktop-type apps. One last missing piece stands in the way: unfettered access to the local filesystem: local files, thumb drives, and network shares. Perhaps someday there will be a facility to "sign" HTML5 apps to allow them access to the local filesystem, but it is not the case today.

Qt is a stopgap measure to allow you to keep 98% of your app code in HTML5, where the other 2% is Qt C++ to access the local filesystem. This preserves cross-platform capacity and potential, including to the cloud. (In contrast, a conventional Qt app without HTML5 would not be able to be hosted on the cloud.)

Below are the slides from the presentation I gave on May 13, 2013 at the Denver HTML5 Meetup group. And below that is a YouTube with audio narration, recreating the presentation.

Escaping thesandboxdenverhtml5meetup20130514 from michaelmalak