Skip to content

Instantly share code, notes, and snippets.

@alimanfoo

alimanfoo/.gitignore

Last active August 29, 2015 14:01
Show Gist options
  • Save alimanfoo/926868c73e18ca7f77b4 to your computer and use it in GitHub Desktop.
Save alimanfoo/926868c73e18ca7f77b4 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
.gitignore
.ipynb_checkpoints
*~
Display the source blob
Display the rendered blob
Raw
tables.ipynb
{
"metadata": {
"name": "",
"signature": "sha256:9921188f544d082f494587feca1aaec00f09d00db1c4d23104445a0d4f8b9066"
},
"nbformat": 3,
"nbformat_minor": 0,
"worksheets": [
{
"cells": [
{
"cell_type": "heading",
"level": 1,
"metadata": {},
"source": [
"Viewing tabular data in the IPython notebook"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Some examples using two libraries ([pandas](http://pandas.pydata.org) and [petl](http://petl.readthedocs.org)) which automatically format table-like objects as HTML tables in the IPython notebook."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"First, set up a table of data in an SQLite database..."
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"import sqlite3\n",
"connection = sqlite3.connect('test.db')\n",
"data = [['a', 1],\n",
" ['b', 2],\n",
" ['c', 2.0]]\n",
"c = connection.cursor()\n",
"c.execute('drop table if exists foobar')\n",
"c.execute('create table foobar (foo text, bar float)')\n",
"for row in data:\n",
" c.execute('insert into foobar values (?, ?)', row)\n",
"\n",
"connection.commit()\n",
"c.close()"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 1
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Example using [pandas](http://pandas.pydata.org)..."
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"import pandas\n",
"df = pandas.io.sql.read_frame('select * from foobar', connection)\n",
"df.head()"
],
"language": "python",
"metadata": {},
"outputs": [
{
"html": [
"<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>foo</th>\n",
" <th>bar</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td> a</td>\n",
" <td> 1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td> b</td>\n",
" <td> 2</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td> c</td>\n",
" <td> 2</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>3 rows \u00d7 2 columns</p>\n",
"</div>"
],
"metadata": {},
"output_type": "pyout",
"prompt_number": 2,
"text": [
" foo bar\n",
"0 a 1\n",
"1 b 2\n",
"2 c 2\n",
"\n",
"[3 rows x 2 columns]"
]
}
],
"prompt_number": 2
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Example using [petl](http://petl.readthedocs.org)..."
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"import petl.interactive as etl\n",
"tbl = etl.fromdb(connection, 'select * from foobar')\n",
"tbl.head()"
],
"language": "python",
"metadata": {},
"outputs": [
{
"html": [
"<table>\r\n",
"<thead>\r\n",
"<tr>\r\n",
"<th>foo</th>\r\n",
"<th>bar</th>\r\n",
"</tr>\r\n",
"</thead>\r\n",
"<tbody>\r\n",
"<tr>\r\n",
"<td>a</td>\r\n",
"<td style='text-align: right'>1.0</td>\r\n",
"</tr>\r\n",
"<tr>\r\n",
"<td>b</td>\r\n",
"<td style='text-align: right'>2.0</td>\r\n",
"</tr>\r\n",
"<tr>\r\n",
"<td>c</td>\r\n",
"<td style='text-align: right'>2.0</td>\r\n",
"</tr>\r\n",
"</tbody>\r\n",
"</table>\r\n"
],
"metadata": {},
"output_type": "pyout",
"prompt_number": 3,
"text": [
"+-------+-------+\n",
"| 'foo' | 'bar' |\n",
"+=======+=======+\n",
"| u'a' | 1.0 |\n",
"+-------+-------+\n",
"| u'b' | 2.0 |\n",
"+-------+-------+\n",
"| u'c' | 2.0 |\n",
"+-------+-------+"
]
}
],
"prompt_number": 3
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Example using [petl](http://petl.readthedocs.org) with the [petlx.ipython](http://petlx.readthedocs.org/en/latest/ipython.html) extension to provide a table caption..."
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"import petlx.ipython\n",
"tbl.display(caption='example')"
],
"language": "python",
"metadata": {},
"outputs": [
{
"html": [
"<table>\r\n",
"<caption>example</caption>\r\n",
"<thead>\r\n",
"<tr>\r\n",
"<th>foo</th>\r\n",
"<th>bar</th>\r\n",
"</tr>\r\n",
"</thead>\r\n",
"<tbody>\r\n",
"<tr>\r\n",
"<td>a</td>\r\n",
"<td style='text-align: right'>1.0</td>\r\n",
"</tr>\r\n",
"<tr>\r\n",
"<td>b</td>\r\n",
"<td style='text-align: right'>2.0</td>\r\n",
"</tr>\r\n",
"<tr>\r\n",
"<td>c</td>\r\n",
"<td style='text-align: right'>2.0</td>\r\n",
"</tr>\r\n",
"</tbody>\r\n",
"</table>\r\n"
],
"metadata": {},
"output_type": "display_data"
}
],
"prompt_number": 4
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Using the petlx.ipython extension you can also display two or more tables in the output of a single cell, and can customise the way values are represented...:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"tbl.display(caption='example (str)')\n",
"tbl.display(caption='example (repr)', representation=repr)"
],
"language": "python",
"metadata": {},
"outputs": [
{
"html": [
"<table>\r\n",
"<caption>example (str)</caption>\r\n",
"<thead>\r\n",
"<tr>\r\n",
"<th>foo</th>\r\n",
"<th>bar</th>\r\n",
"</tr>\r\n",
"</thead>\r\n",
"<tbody>\r\n",
"<tr>\r\n",
"<td>a</td>\r\n",
"<td style='text-align: right'>1.0</td>\r\n",
"</tr>\r\n",
"<tr>\r\n",
"<td>b</td>\r\n",
"<td style='text-align: right'>2.0</td>\r\n",
"</tr>\r\n",
"<tr>\r\n",
"<td>c</td>\r\n",
"<td style='text-align: right'>2.0</td>\r\n",
"</tr>\r\n",
"</tbody>\r\n",
"</table>\r\n"
],
"metadata": {},
"output_type": "display_data"
},
{
"html": [
"<table>\r\n",
"<caption>example (repr)</caption>\r\n",
"<thead>\r\n",
"<tr>\r\n",
"<th>foo</th>\r\n",
"<th>bar</th>\r\n",
"</tr>\r\n",
"</thead>\r\n",
"<tbody>\r\n",
"<tr>\r\n",
"<td>u'a'</td>\r\n",
"<td style='text-align: right'>1.0</td>\r\n",
"</tr>\r\n",
"<tr>\r\n",
"<td>u'b'</td>\r\n",
"<td style='text-align: right'>2.0</td>\r\n",
"</tr>\r\n",
"<tr>\r\n",
"<td>u'c'</td>\r\n",
"<td style='text-align: right'>2.0</td>\r\n",
"</tr>\r\n",
"</tbody>\r\n",
"</table>\r\n"
],
"metadata": {},
"output_type": "display_data"
}
],
"prompt_number": 5
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Using the [petlx.dataframe](http://petlx.readthedocs.org/en/latest/dataframe.html) extension you can move between petl and pandas..."
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"import petlx.dataframe\n",
"tbl2 = etl.fromdataframe(df)\n",
"tbl2"
],
"language": "python",
"metadata": {},
"outputs": [
{
"html": [
"<table>\r\n",
"<thead>\r\n",
"<tr>\r\n",
"<th>foo</th>\r\n",
"<th>bar</th>\r\n",
"</tr>\r\n",
"</thead>\r\n",
"<tbody>\r\n",
"<tr>\r\n",
"<td>a</td>\r\n",
"<td style='text-align: right'>1.0</td>\r\n",
"</tr>\r\n",
"<tr>\r\n",
"<td>b</td>\r\n",
"<td style='text-align: right'>2.0</td>\r\n",
"</tr>\r\n",
"<tr>\r\n",
"<td>c</td>\r\n",
"<td style='text-align: right'>2.0</td>\r\n",
"</tr>\r\n",
"</tbody>\r\n",
"</table>\r\n"
],
"metadata": {},
"output_type": "pyout",
"prompt_number": 6,
"text": [
"+-------+-------+\n",
"| 'foo' | 'bar' |\n",
"+=======+=======+\n",
"| u'a' | 1.0 |\n",
"+-------+-------+\n",
"| u'b' | 2.0 |\n",
"+-------+-------+\n",
"| u'c' | 2.0 |\n",
"+-------+-------+"
]
}
],
"prompt_number": 6
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"df2 = tbl.todataframe()\n",
"df2"
],
"language": "python",
"metadata": {},
"outputs": [
{
"html": [
"<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>foo</th>\n",
" <th>bar</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td> a</td>\n",
" <td> 1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td> b</td>\n",
" <td> 2</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td> c</td>\n",
" <td> 2</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>3 rows \u00d7 2 columns</p>\n",
"</div>"
],
"metadata": {},
"output_type": "pyout",
"prompt_number": 7,
"text": [
" foo bar\n",
"0 a 1\n",
"1 b 2\n",
"2 c 2\n",
"\n",
"[3 rows x 2 columns]"
]
}
],
"prompt_number": 7
}
],
"metadata": {}
}
]
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment