petl - Extract, Transform and Load (Tables of Data)¶

petl is a tentative Python package for extracting, transforming and loading tables of data.

Documentation: http://packages.python.org/petl
Source Code: https://github.com/alimanfoo/petl
Download: http://pypi.python.org/pypi/petl
Mailing List: http://groups.google.com/group/python-etl

For examples of petl in use, see the case studies below:

Case Study 1 - Comparing Tables

Overview¶

The tables below gives an overview of the main functions in the petl module.

See also the alphabetic Index of all functions in the package.

Functions for extracting tables from files and databases:

	Extract
`fromcsv()`	`fromtsv()`	`frompickle()`
`fromsqlite3()`	`fromdb()`	`fromtext()`
`fromxml()`	`fromjson()`	`fromdicts()`

Functions for transforming tables:

	Transform
`rename()`	`setheader()`	`extendheader()`
`pushheader()`	`skip()`	`skipcomments()`
`rowslice()`	`head()`	`tail()`
`cut()`	`cutout()`	`select()`
`selectop()`	`selecteq()`	`selectne()`
`selectlt()`	`selectle()`	`selectgt()`
`selectge()`	`selectrangeopen()`	`selectrangeopenleft()`
`selectrangeopenright()`	`selectrangeclosed()`	`selectin()`
`selectnotin()`	`selectis()`	`selectisnot()`
`selectisinstance()`	`selectre()`	`rowselect()`
`recordselect()`	`rowlenselect()`	`fieldselect()`
`facet()`	`rangefacet()`	`replace()`
`replaceall()`	`convert()`	`convertall()`
`fieldconvert()`	`convertnumbers()`	`sub()`
`addfield()`	`capture()`	`split()`
`unpack()`	`fieldmap()`	`rowmap()`
`recordmap()`	`rowmapmany()`	`recordmapmany()`
`cat()`	`duplicates()`	`conflicts()`
`sort()`	`join()`	`leftjoin()`
`rightjoin()`	`outerjoin()`	`crossjoin()`
`antijoin()`	`complement()`	`diff()`
`recordcomplement()`	`recorddiff()`	`intersection()`
`aggregate()`	`rangeaggregate()`	`rangecounts()`
`rowreduce()`	`recordreduce()`	`rangerowreduce()`
`rangerecordreduce()`	`mergeduplicates()`	`mergesort()`
`merge()`	`melt()`	`recast()`
`transpose()`	`pivot()`	`hashjoin()`
`hashleftjoin()`	`hashrightjoin()`	`hashantijoin()`
`hashcomplement()`	`hashintersection()`	`flatten()`
`unflatten()`	`annex()`	`unpackdict()`
`unique()`	`fold()`	`addrownumbers()`
`selectcontains()`	`search()`	`addcolumn()`
`lookupjoin()`	`hashlookupjoin()`	`filldown()`
`fillright()`	`fillleft()`

Functions for loading (writing) data to files and databases:

	Load
`tocsv()`	`appendcsv()`	`totsv()`
`appendtsv()`	`topickle()`	`appendpickle()`
`tosqlite3()`	`appendsqlite3()`	`todb()`
`appenddb()`	`totext()`	`appendtext()`
`tojson()`	`tojsonarrays()`

Other utility functions:

	Utilities
`header()`	`data()`	`dataslice()`
`records()`	`fieldnames()`	`nrows()`
`look()`	`lookall()`	`see()`
`itervalues()`	`values()`	`valueset()`
`valuecount()`	`valuecounts()`	`valuecounter()`
`columns()`	`facetcolumns()`	`isunique()`
`limits()`	`stats()`	`lenstats()`
`stringpatterns()`	`stringpatterncounter()`	`rowlengths()`
`typecounts()`	`typecounter()`	`typeset()`
`parsecounts()`	`parsecounter()`	`dateparser()`
`timeparser()`	`datetimeparser()`	`boolparser()`
`parsenumber()`	`lookup()`	`lookupone()`
`recordlookup()`	`recordlookupone()`	`statsum()`
`adler32sum()`	`crc32sum()`	`expr()`
`strjoin()`	`randomtable()`	`dummytable()`
`diffheaders()`	`diffvalues()`	`heapqmergesorted()`
`shortlistmergesorted()`	`progress()`	`clock()`
`isordered()`	`rowgroupby()`	`nthword()`

Introduction¶

Installation¶

This module is available from the Python Package Index. On Linux distributions you should be able to do easy_install petl or pip install petl. On Windows or Mac you can download manually, extract and run python setup.py install.

Dependencies and extensions¶

This package has been written with no dependencies other than the Python core modules, for ease of installation and maintenance. However, there are many third party packages which could usefuly be used with petl, e.g., providing access to data from Excel or other file types. Some extensions with these additional dependencies are provided by the petlx package, a companion package to petl.

Conventions - row containers and row iterators¶

This package defines the following convention for objects acting as containers of tabular data and supporting row-oriented iteration over the data.

A row container (also referred to here informally as a table) is any object which satisfies the following:

implements the __iter__ method
__iter__ returns a row iterator (see below)
all row iterators returned by __iter__ are independent, i.e., consuming items from one iterator will not affect any other iterators

A row iterator is an iterator which satisfies the following:

each item returned by the iterator is either a list or a tuple
the first item returned by the iterator is a header row comprising a list or tuple of fields
each subsequent item returned by the iterator is a data row comprising a list or tuple of data values
a field is typically a string (str or unicode) but may be an object of any type as long as it implements __str__, is immutable (can be used as a dictionary key) and is pickleable
a data value is any pickleable object

So, for example, the list of lists shown below is a row container:

>>> table = [['foo', 'bar'], ['a', 1], ['b', 2]]

Note that, under this convention, an object returned by the csv.reader() function from the standard Python csv module is a row iterator and not a row container, because it can only be iterated over once, e.g.:

>>> from StringIO import StringIO
>>> import csv
>>> csvdata = """foo,bar
... a,1
... b,2
... """
>>> rowiterator = csv.reader(StringIO(csvdata))
>>> for row in rowiterator:
...     print row
...
['foo', 'bar']
['a', '1']
['b', '2']
>>> for row in rowiterator:
...     print row
...
>>> # can only iterate once

However, it is straightforward to define functions that support the above convention for row containers and provide access to data from CSV or other types of file or data source, see e.g. the fromcsv() function in this package.

The main reason for requiring that row containers support independent row iterators (point 3) is that data from a table may need to be iterated over several times within the same program or interactive session. E.g., when using petl in an interactive session to build up a sequence of data transformation steps, the user might want to examine outputs from several intermediate steps, before all of the steps are defined and the transformation is executed in full.

Note that this convention does not place any restrictions on the lengths of header and data rows. A table may return a header row and/or data rows of varying lengths.

Transformation pipelines¶

Note that this package makes extensive use of lazy evaluation and iterators. This means, generally, that a transformation will not actually be executed until data is requested.

E.g., given the following data in a file at ‘example1.csv’ in the current working directory:

foo,bar,baz
a,1,3.4
b,2,7.4
c,6,2.2
d,9,8.1

...the following code does not actually read the file, nor does it load any of its contents into memory:

>>> from petl import *
>>> table1 = fromcsv('example1.csv')

Rather, table1 is a row container object, which can be iterated over.

Similarly, if one or more transformation functions are applied, e.g.::

>>> table2 = convert(table1, 'foo', 'upper')
>>> table3 = convert(table2, 'bar', int)
>>> table4 = convert(table3, 'baz', float)
>>> table5 = addfield(table4, 'quux', expr('{bar} * {baz}'))

...no actual transformation work will be done, until data are requested from table5 or any of the other row containers returned by the intermediate steps.

So in effect, a 5 step transformation pipeline has been set up, and rows will pass through the pipeline on demand, as they are pulled from the end of the pipeline via iteration.

A call to a function like look(), or any of the functions which write data to a file or database (e.g., tocsv(), totext(), tosqlite3(), todb()), will pull data through the pipeline and cause all of the transformation steps to be executed on the requested rows, e.g.:

>>> look(table5)
+-------+-------+-------+--------------------+
| 'foo' | 'bar' | 'baz' | 'quux'             |
+=======+=======+=======+====================+
| 'A'   | 1     | 3.4   | 3.4                |
+-------+-------+-------+--------------------+
| 'B'   | 2     | 7.4   | 14.8               |
+-------+-------+-------+--------------------+
| 'C'   | 6     | 2.2   | 13.200000000000001 |
+-------+-------+-------+--------------------+
| 'D'   | 9     | 8.1   | 72.89999999999999  |
+-------+-------+-------+--------------------+

...although note that look() will by default only request the first 10 rows, and so at most only 10 rows will be processed. Calling look() to inspect the first few rows of a table is often an efficient way to examine the output of a transformation pipeline, without having to execute the transformation over all of the input data.

Caching / memoization¶

This package tries to make efficient use of memory by using iterators and lazy evaluation where possible. However, some transformations cannot be done without building data structures, either in memory or on disk.

An example is the sort() function, which will either sort a table entirely in memory, or will sort the table in memory in chunks, writing chunks to disk and performing a final merge sort on the chunks. Which strategy is used will depend on the arguments passed into the sort() function when it is called.

In either case, the sorting can take some time, and if the sorted data will be used more than once, it is obviously undesirable to throw away the sorted data and start again from scratch each time. It is better to cache (a.k.a., memoize) the sorted data, if possible, so it can be re-used. However, if a table upstream of the sort is mutable, there needs to be some way of discovering whether data have been changed since the last sort was performed, and hence whether the cached data are still fresh or not.

There are also cases where, even though data are generated in a purely iterative fashion, some programs may still want to cache some or all of the data. E.g., where data are calculated dynamically and are relatively expensive to compute, or require scanning many rows, or where data are being retrieved via a network and there is latency. Again, there needs to be some way of finding out whether cached data are fresh or not.

To support caching, it is recommended (but not required) that row container objects also implement the cachetag method. This method should return an integer which changes whenever the table’s fields or data changes (i.e., a row iterator would yield a different sequence of items). All petl row container objects implement this method, where applicable.

Note in particular that the functions fromcsv(), frompickle() and fromsqlite3() return tables that implement the cachetag method, and that the implementation of cachetag in these cases depends on a checksum function operating on the underlying data file. By default, statsum() is used as the checksum function, which is cheap to compute but crude because it relies only on file name, size and time of modification, and on some systems this will not reveal changes within the same second that preserve file size. If you need a finer level of granularity, use either adler32sum() or crc32sum() instead. These can be passed in as arguments to fromcsv(), frompickle() and fromsqlite3(), or can be set globally, e.g.:

>>> import petl.io
>>> petl.io.defaultsumfun = petl.io.adler32sum

Care must be taken to ensure correct implementation of the cachetag method where a table is generating data dynamically from another table or tables. In these cases, the state of the upstream tables must be considered when generating a cachetag value. One strategy is to construct cachetag values by hashing the internal configuration attributes of a table, along with the cachetag values from any tables immediately upstream.

In some cases, it may be difficult to determine whether data have changed, e.g., where data are being retrieved from a database. In these cases it is suggested that table objects use a user-configurable time to live (TTL) to generate cachetag values. E.g., where a database table is updated once a day at most, a TTL of a few hours would enable data to be cached during an interactive session, which might improve usability for someone exploring the data or developing a transformation script.

Extract - reading tables from files, databases and other sources¶

petl.fromcsv(source=None, dialect=<class csv.excel at 0xa9ae3ec>, **kwargs)¶

Wrapper for the standard csv.reader() function. Returns a table providing access to the data in the given delimited file. E.g.:

>>> import csv
>>> # set up a CSV file to demonstrate with
... with open('test.csv', 'wb') as f:
...     writer = csv.writer(f)
...     writer.writerow(['foo', 'bar'])
...     writer.writerow(['a', 1])
...     writer.writerow(['b', 2])
...     writer.writerow(['c', 2])
...
>>> # now demonstrate the use of petl.fromcsv
... from petl import fromcsv, look
>>> testcsv = fromcsv('test.csv')
>>> look(testcsv)
+-------+-------+
| 'foo' | 'bar' |
+=======+=======+
| 'a'   | '1'   |
+-------+-------+
| 'b'   | '2'   |
+-------+-------+
| 'c'   | '2'   |
+-------+-------+

The filename argument is the path of the delimited file, all other keyword arguments are passed to csv.reader(). So, e.g., to override the delimiter from the default CSV dialect, provide the delimiter keyword argument.

Note that all data values are strings, and any intended numeric values will need to be converted, see also convert().

The returned table object implements the cachetag() method using the default checksum function (whatever petl.io.defaultsumfun is currently set to).

Supports transparent reading from URLs, .gz and .bz2 files.

petl.fromtsv(source=None, dialect=<class csv.excel_tab at 0xa9ae41c>, **kwargs)¶

Convenience function, as fromcsv() but with different default dialect (tab delimited).

Supports transparent reading from URLs, .gz and .bz2 files.

New in version 0.9.

petl.frompickle(source=None)¶

Returns a table providing access to the data pickled in the given file. The rows in the table should have been pickled to the file one at a time. E.g.:

>>> import pickle
>>> # set up a file to demonstrate with
... with open('test.dat', 'wb') as f:
...     pickle.dump(['foo', 'bar'], f)
...     pickle.dump(['a', 1], f)
...     pickle.dump(['b', 2], f)
...     pickle.dump(['c', 2.5], f)
...
>>> # now demonstrate the use of petl.frompickle
... from petl import frompickle, look
>>> testdat = frompickle('test.dat')
>>> look(testdat)
+-------+-------+
| 'foo' | 'bar' |
+=======+=======+
| 'a'   | 1     |
+-------+-------+
| 'b'   | 2     |
+-------+-------+
| 'c'   | 2.5   |
+-------+-------+

The returned table object implements the cachetag() method using the default checksum function (whatever petl.io.defaultsumfun is currently set to).

Supports transparent reading from URLs, .gz and .bz2 files.

petl.fromsqlite3(source, query, *args, **kwargs)¶

Provides access to data from an sqlite3 database file via a given query. E.g.:

>>> import sqlite3
>>> from petl import look, fromsqlite3    
>>> # set up a database to demonstrate with
>>> data = [['a', 1],
...         ['b', 2],
...         ['c', 2.0]]
>>> connection = sqlite3.connect('test.db')
>>> c = connection.cursor()
>>> c.execute('create table foobar (foo, bar)')
<sqlite3.Cursor object at 0x2240b90>
>>> for row in data:
...     c.execute('insert into foobar values (?, ?)', row)
... 
<sqlite3.Cursor object at 0x2240b90>
<sqlite3.Cursor object at 0x2240b90>
<sqlite3.Cursor object at 0x2240b90>
>>> connection.commit()
>>> c.close()
>>>
>>> # now demonstrate the petl.fromsqlite3 function
... foobar = fromsqlite3('test.db', 'select * from foobar')
>>> look(foobar)    
+-------+-------+
| 'foo' | 'bar' |
+=======+=======+
| u'a'  | 1     |
+-------+-------+
| u'b'  | 2     |
+-------+-------+
| u'c'  | 2.0   |
+-------+-------+

The returned table object implements the cachetag() method using the default checksum function (whatever petl.io.defaultsumfun is currently set to).

Changed in version 0.10.2.

Either a database file name or a connection object can be given as the first argument. (Note that cachetag() is only implemented if a file name is given.)

petl.fromdb(connection, query, *args, **kwargs)¶

Provides access to data from any DB-API 2.0 connection via a given query. E.g., using sqlite3:

>>> import sqlite3
>>> from petl import look, fromdb
>>> connection = sqlite3.connect('test.db')
>>> table = fromdb(connection, 'select * from foobar')
>>> look(table)

E.g., using psycopg2 (assuming you’ve installed it first):

>>> import psycopg2
>>> from petl import look, fromdb
>>> connection = psycopg2.connect("dbname=test user=postgres")
>>> table = fromdb(connection, 'select * from test')
>>> look(table)

E.g., using MySQLdb (assuming you’ve installed it first):

>>> import MySQLdb
>>> from petl import look, fromdb
>>> connection = MySQLdb.connect(passwd="moonpie", db="thangs")
>>> table = fromdb(connection, 'select * from test')
>>> look(table)

The returned table object does not implement the cachetag() method.

Changed in version 0.10.2.

The first argument may also be a function that creates a cursor. E.g.:

>>> import psycopg2
>>> from petl import look, fromdb
>>> connection = psycopg2.connect("dbname=test user=postgres")
>>> mkcursor = lambda: connection.cursor(cursor_factory=psycopg2.extras.DictCursor)
>>> table = fromdb(mkcursor, 'select * from test')
>>> look(table)

N.B., each call to the function should return a new cursor.

petl.fromtext(source=None, header=['lines'], strip=None)¶

Construct a table from lines in the given text file. E.g.:

>>> # example data
... with open('test.txt', 'w') as f:
...     f.write('a\t1\n')
...     f.write('b\t2\n')
...     f.write('c\t3\n')
... 
>>> from petl import fromtext, look
>>> table1 = fromtext('test.txt')
>>> look(table1)
+--------------+
| 'lines'      |
+==============+
| 'a\t1'      |
+--------------+
| 'b\t2'      |
+--------------+
| 'c\t3'      |
+--------------+

The fromtext() function provides a starting point for custom handling of text files. E.g., using capture():

>>> from petl import capture
>>> table2 = capture(table1, 'lines', '(.*)\\t(.*)$', ['foo', 'bar'])
>>> look(table2)
+-------+-------+
| 'foo' | 'bar' |
+=======+=======+
| 'a'   | '1'   |
+-------+-------+
| 'b'   | '2'   |
+-------+-------+
| 'c'   | '3'   |
+-------+-------+

Supports transparent reading from URLs, .gz and .bz2 files.

Changed in version 0.4.

The strip() function is called on each line, which by default will remove leading and trailing whitespace, including the end-of-line character - use the strip keyword argument to specify alternative characters to strip.

petl.fromxml(source, *args, **kwargs)¶

Access data in an XML file. E.g.:

>>> from petl import fromxml, look
>>> data = """<table>
...     <tr>
...         <td>foo</td><td>bar</td>
...     </tr>
...     <tr>
...         <td>a</td><td>1</td>
...     </tr>
...     <tr>
...         <td>b</td><td>2</td>
...     </tr>
...     <tr>
...         <td>c</td><td>2</td>
...     </tr>
... </table>"""
>>> with open('example1.xml', 'w') as f:    
...     f.write(data)
...     f.close()
... 
>>> table1 = fromxml('example1.xml', 'tr', 'td')
>>> look(table1)
+-------+-------+
| 'foo' | 'bar' |
+=======+=======+
| 'a'   | '1'   |
+-------+-------+
| 'b'   | '2'   |
+-------+-------+
| 'c'   | '2'   |
+-------+-------+

If the data values are stored in an attribute, provide the attribute name as an extra positional argument, e.g.:

>>> data = """<table>
...     <tr>
...         <td v='foo'/><td v='bar'/>
...     </tr>
...     <tr>
...         <td v='a'/><td v='1'/>
...     </tr>
...     <tr>
...         <td v='b'/><td v='2'/>
...     </tr>
...     <tr>
...         <td v='c'/><td v='2'/>
...     </tr>
... </table>"""
>>> with open('example2.xml', 'w') as f:    
...     f.write(data)
...     f.close()
... 
>>> table2 = fromxml('example2.xml', 'tr', 'td', 'v')
>>> look(table2)
+-------+-------+
| 'foo' | 'bar' |
+=======+=======+
| 'a'   | '1'   |
+-------+-------+
| 'b'   | '2'   |
+-------+-------+
| 'c'   | '2'   |
+-------+-------+

Data values can also be extracted by providing a mapping of field names to element paths, e.g.:

>>> data = """<table>
...     <row>
...         <foo>a</foo><baz><bar v='1'/><bar v='3'/></baz>
...     </row>
...     <row>
...         <foo>b</foo><baz><bar v='2'/></baz>
...     </row>
...     <row>
...         <foo>c</foo><baz><bar v='2'/></baz>
...     </row>
... </table>"""
>>> with open('example3.xml', 'w') as f:    
...     f.write(data)
...     f.close()
... 
>>> table3 = fromxml('example3.xml', 'row', {'foo': 'foo', 'bar': ('baz/bar', 'v')})
>>> look(table3)
+-------+------------+
| 'foo' | 'bar'      |
+=======+============+
| 'a'   | ('1', '3') |
+-------+------------+
| 'b'   | '2'        |
+-------+------------+
| 'c'   | '2'        |
+-------+------------+

Note that the implementation is currently not streaming, i.e., the whole document is loaded into memory.

Supports transparent reading from URLs, .gz and .bz2 files.

New in version 0.4.

Changed in version 0.6: If multiple elements match a given field, all values are reported as a tuple.

petl.fromjson(source, *args, **kwargs)¶

Extract data from a JSON file. The file must contain a JSON array as the top level object, and each member of the array will be treated as a row of data. E.g.:

>>> from petl import fromjson, look
>>> data = '[{"foo": "a", "bar": 1}, {"foo": "b", "bar": 2}, {"foo": "c", "bar": 2}]'
>>> with open('example1.json', 'w') as f:
...     f.write(data)
... 
>>> table1 = fromjson('example1.json')
>>> look(table1)
+--------+--------+
| u'foo' | u'bar' |
+========+========+
| u'a'   | 1      |
+--------+--------+
| u'b'   | 2      |
+--------+--------+
| u'c'   | 2      |
+--------+--------+

If your JSON file does not fit this structure, you will need to parse it via json.load() and select the array to treat as the data, see also fromdicts().

Supports transparent reading from URLs, .gz and .bz2 files.

New in version 0.5.

petl.fromdicts(dicts, header=None)¶

View a sequence of Python dict as a table. E.g.:

>>> from petl import fromdicts, look
>>> dicts = [{"foo": "a", "bar": 1}, {"foo": "b", "bar": 2}, {"foo": "c", "bar": 2}]
>>> table = fromdicts(dicts)
>>> look(table)
+-------+-------+
| 'foo' | 'bar' |
+=======+=======+
| 'a'   | 1     |
+-------+-------+
| 'b'   | 2     |
+-------+-------+
| 'c'   | 2     |
+-------+-------+

Transform - transforming tables¶

petl.rename(table, *args)¶

Replace one or more fields in the table’s header row. E.g.:

>>> from petl import look, rename
>>> look(table1)
+-------+-------+
| 'sex' | 'age' |
+=======+=======+
| 'M'   | 12    |
+-------+-------+
| 'F'   | 34    |
+-------+-------+
| '-'   | 56    |
+-------+-------+

>>> # rename a single field
... table2 = rename(table1, 'sex', 'gender')
>>> look(table2)
+----------+-------+
| 'gender' | 'age' |
+==========+=======+
| 'M'      | 12    |
+----------+-------+
| 'F'      | 34    |
+----------+-------+
| '-'      | 56    |
+----------+-------+

>>> # rename multiple fields by passing a dictionary as the second argument
... table3 = rename(table1, {'sex': 'gender', 'age': 'age_years'})
>>> look(table3)
+----------+-------------+
| 'gender' | 'age_years' |
+==========+=============+
| 'M'      | 12          |
+----------+-------------+
| 'F'      | 34          |
+----------+-------------+
| '-'      | 56          |
+----------+-------------+

>>> # the returned table object can also be used to modify the field mapping using the suffix notation
... table4 = rename(table1)
>>> table4['sex'] = 'gender'
>>> table4['age'] = 'age_years'
>>> look(table4)
+----------+-------------+
| 'gender' | 'age_years' |
+==========+=============+
| 'M'      | 12          |
+----------+-------------+
| 'F'      | 34          |
+----------+-------------+
| '-'      | 56          |
+----------+-------------+

Changed in version 0.4.

Function signature changed to support the simple 2 argument form when renaming a single field.

petl.setheader(table, fields)¶

Override fields in the given table. E.g.:

>>> from petl import setheader, look
>>> look(table1)
+-------+-------+
| 'foo' | 'bar' |
+=======+=======+
| 'a'   | 1     |
+-------+-------+
| 'b'   | 2     |
+-------+-------+

>>> table2 = setheader(table1, ['foofoo', 'barbar'])
>>> look(table2)
+----------+----------+
| 'foofoo' | 'barbar' |
+==========+==========+
| 'a'      | 1        |
+----------+----------+
| 'b'      | 2        |
+----------+----------+

petl.extendheader(table, fields)¶

Extend fields in the given table. E.g.:

>>> from petl import extendheader, look
>>> look(table1)
+-------+---+-------+
| 'foo' |   |       |
+=======+===+=======+
| 'a'   | 1 | True  |
+-------+---+-------+
| 'b'   | 2 | False |
+-------+---+-------+

>>> table2 = extendheader(table1, ['bar', 'baz'])
>>> look(table2)
+-------+-------+-------+
| 'foo' | 'bar' | 'baz' |
+=======+=======+=======+
| 'a'   | 1     | True  |
+-------+-------+-------+
| 'b'   | 2     | False |
+-------+-------+-------+

petl.pushheader(table, fields)¶

Push rows down and prepend a header row. E.g.:

>>> from petl import pushheader, look    
>>> look(table1)
+-----+---+
| 'a' | 1 |
+=====+===+
| 'b' | 2 |
+-----+---+

>>> table2 = pushheader(table1, ['foo', 'bar'])
>>> look(table2)
+-------+-------+
| 'foo' | 'bar' |
+=======+=======+
| 'a'   | 1     |
+-------+-------+
| 'b'   | 2     |
+-------+-------+

Useful, e.g., where data are from a CSV file that has not included a header row.

petl.skip(table, n)¶

Skip n rows (including the header row). E.g.:

>>> from petl import skip, look
>>> look(table1)
+--------+-------+-------+
| '#aaa' | 'bbb' | 'ccc' |
+========+=======+=======+
| '#mmm' |       |       |
+--------+-------+-------+
| 'foo'  | 'bar' |       |
+--------+-------+-------+
| 'a'    | 1     |       |
+--------+-------+-------+
| 'b'    | 2     |       |
+--------+-------+-------+

>>> table2 = skip(table1, 2)
>>> look(table2)
+-------+-------+
| 'foo' | 'bar' |
+=======+=======+
| 'a'   | 1     |
+-------+-------+
| 'b'   | 2     |
+-------+-------+

petl.skipcomments(table, prefix)¶

Skip any row where the first value is a string and starts with prefix. E.g.:

>>> from petl import skipcomments, look
>>> look(table1)
+---------+-------+-------+
| '##aaa' | 'bbb' | 'ccc' |
+=========+=======+=======+
| '##mmm' |       |       |
+---------+-------+-------+
| '#foo'  | 'bar' |       |
+---------+-------+-------+
| '##nnn' | 1     |       |
+---------+-------+-------+
| 'a'     | 1     |       |
+---------+-------+-------+
| 'b'     | 2     |       |
+---------+-------+-------+

>>> table2 = skipcomments(table1, '##')
>>> look(table2)
+--------+-------+
| '#foo' | 'bar' |
+========+=======+
| 'a'    | 1     |
+--------+-------+
| 'b'    | 2     |
+--------+-------+

New in version 0.4.

petl.rowslice(table, *sliceargs)¶

Choose a subsequence of data rows. E.g.:

>>> from petl import rowslice, look
>>> look(table1)
+-------+-------+
| 'foo' | 'bar' |
+=======+=======+
| 'a'   | 1     |
+-------+-------+
| 'b'   | 2     |
+-------+-------+
| 'c'   | 5     |
+-------+-------+
| 'd'   | 7     |
+-------+-------+
| 'f'   | 42    |
+-------+-------+

>>> table2 = rowslice(table1, 2)
>>> look(table2)
+-------+-------+
| 'foo' | 'bar' |
+=======+=======+
| 'a'   | 1     |
+-------+-------+
| 'b'   | 2     |
+-------+-------+

>>> table3 = rowslice(table1, 1, 4)
>>> look(table3)
+-------+-------+
| 'foo' | 'bar' |
+=======+=======+
| 'b'   | 2     |
+-------+-------+
| 'c'   | 5     |
+-------+-------+
| 'd'   | 7     |
+-------+-------+

>>> table4 = rowslice(table1, 0, 5, 2)
>>> look(table4)
+-------+-------+
| 'foo' | 'bar' |
+=======+=======+
| 'a'   | 1     |
+-------+-------+
| 'c'   | 5     |
+-------+-------+
| 'f'   | 42    |
+-------+-------+

Changed in version 0.3.

Positional arguments can be used to slice the data rows. The sliceargs are passed to itertools.islice().

petl.head(table, n=10)¶

Choose the first n data rows. E.g.:

>>> from petl import head, look
>>> look(table1)
+-------+-------+
| 'foo' | 'bar' |
+=======+=======+
| 'a'   | 1     |
+-------+-------+
| 'b'   | 2     |
+-------+-------+
| 'c'   | 5     |
+-------+-------+
| 'd'   | 7     |
+-------+-------+
| 'f'   | 42    |
+-------+-------+
| 'f'   | 3     |
+-------+-------+
| 'h'   | 90    |
+-------+-------+

>>> table2 = head(table1, 4)
>>> look(table2)    
+-------+-------+
| 'foo' | 'bar' |
+=======+=======+
| 'a'   | 1     |
+-------+-------+
| 'b'   | 2     |
+-------+-------+
| 'c'   | 5     |
+-------+-------+
| 'd'   | 7     |
+-------+-------+

Syntactic sugar, equivalent to rowslice(table, n).

petl.tail(table, n=10)¶

Choose the last n data rows. E.g.:

>>> from petl import tail, look
>>> look(table1)
+-------+-------+
| 'foo' | 'bar' |
+=======+=======+
| 'a'   | 1     |
+-------+-------+
| 'b'   | 2     |
+-------+-------+
| 'c'   | 5     |
+-------+-------+
| 'd'   | 7     |
+-------+-------+
| 'f'   | 42    |
+-------+-------+
| 'f'   | 3     |
+-------+-------+
| 'h'   | 90    |
+-------+-------+
| 'k'   | 12    |
+-------+-------+
| 'l'   | 77    |
+-------+-------+
| 'q'   | 2     |
+-------+-------+

>>> table2 = tail(table1, 4)
>>> look(table2)    
+-------+-------+
| 'foo' | 'bar' |
+=======+=======+
| 'h'   | 90    |
+-------+-------+
| 'k'   | 12    |
+-------+-------+
| 'l'   | 77    |
+-------+-------+
| 'q'   | 2     |
+-------+-------+

petl.cut(table, *args, **kwargs)¶

Choose and/or re-order columns. E.g.:

>>> from petl import look, cut    
>>> look(table1)
+-------+-------+-------+
| 'foo' | 'bar' | 'baz' |
+=======+=======+=======+
| 'A'   | 1     | 2.7   |
+-------+-------+-------+
| 'B'   | 2     | 3.4   |
+-------+-------+-------+
| 'B'   | 3     | 7.8   |
+-------+-------+-------+
| 'D'   | 42    | 9.0   |
+-------+-------+-------+
| 'E'   | 12    |       |
+-------+-------+-------+

>>> table2 = cut(table1, 'foo', 'baz')
>>> look(table2)
+-------+-------+
| 'foo' | 'baz' |
+=======+=======+
| 'A'   | 2.7   |
+-------+-------+
| 'B'   | 3.4   |
+-------+-------+
| 'B'   | 7.8   |
+-------+-------+
| 'D'   | 9.0   |
+-------+-------+
| 'E'   | None  |
+-------+-------+

>>> # fields can also be specified by index, starting from zero
... table3 = cut(table1, 0, 2)
>>> look(table3)
+-------+-------+
| 'foo' | 'baz' |
+=======+=======+
| 'A'   | 2.7   |
+-------+-------+
| 'B'   | 3.4   |
+-------+-------+
| 'B'   | 7.8   |
+-------+-------+
| 'D'   | 9.0   |
+-------+-------+
| 'E'   | None  |
+-------+-------+

>>> # field names and indices can be mixed
... table4 = cut(table1, 'bar', 0)
>>> look(table4)
+-------+-------+
| 'bar' | 'foo' |
+=======+=======+
| 1     | 'A'   |
+-------+-------+
| 2     | 'B'   |
+-------+-------+
| 3     | 'B'   |
+-------+-------+
| 42    | 'D'   |
+-------+-------+
| 12    | 'E'   |
+-------+-------+

>>> # select a range of fields
... table5 = cut(table1, *range(0, 2))
>>> look(table5)    
+-------+-------+
| 'foo' | 'bar' |
+=======+=======+
| 'A'   | 1     |
+-------+-------+
| 'B'   | 2     |
+-------+-------+
| 'B'   | 3     |
+-------+-------+
| 'D'   | 42    |
+-------+-------+
| 'E'   | 12    |
+-------+-------+

Note that any short rows will be padded with None values (or whatever is provided via the missing keyword argument).

Load - writing tables to files and databases¶

petl.tocsv(table, source=None, dialect=<class csv.excel at 0xa9ae3ec>, **kwargs)¶

Write the table to a CSV file. E.g.:

>>> from petl import tocsv, look
>>> look(table)
+-------+-------+
| 'foo' | 'bar' |
+=======+=======+
| 'a'   | 1     |
+-------+-------+
| 'b'   | 2     |
+-------+-------+
| 'c'   | 2     |
+-------+-------+

>>> tocsv(table, 'test.csv')
>>> # look what it did
... from petl import fromcsv
>>> look(fromcsv('test.csv'))
+-------+-------+
| 'foo' | 'bar' |
+=======+=======+
| 'a'   | '1'   |
+-------+-------+
| 'b'   | '2'   |
+-------+-------+
| 'c'   | '2'   |
+-------+-------+

The filename argument is the path of the delimited file, all other keyword arguments are passed to csv.writer(). So, e.g., to override the delimiter from the default CSV dialect, provide the delimiter keyword argument.

Note that if a file already exists at the given location, it will be overwritten.

Supports transparent writing to .gz and .bz2 files.

petl.appendcsv(table, source=None, dialect=<class csv.excel at 0xa9ae3ec>, **kwargs)¶

Append data rows to an existing CSV file. E.g.:

>>> # look at an existing CSV file
... from petl import look, fromcsv
>>> testcsv = fromcsv('test.csv')
>>> look(testcsv)
+-------+-------+
| 'foo' | 'bar' |
+=======+=======+
| 'a'   | '1'   |
+-------+-------+
| 'b'   | '2'   |
+-------+-------+
| 'c'   | '2'   |
+-------+-------+

>>> # append some data
... look(table)
+-------+-------+
| 'foo' | 'bar' |
+=======+=======+
| 'd'   | 7     |
+-------+-------+
| 'e'   | 42    |
+-------+-------+
| 'f'   | 12    |
+-------+-------+

>>> from petl import appendcsv 
>>> appendcsv(table, 'test.csv')
>>> # look what it did
... look(testcsv)
+-------+-------+
| 'foo' | 'bar' |
+=======+=======+
| 'a'   | '1'   |
+-------+-------+
| 'b'   | '2'   |
+-------+-------+
| 'c'   | '2'   |
+-------+-------+
| 'd'   | '7'   |
+-------+-------+
| 'e'   | '42'  |
+-------+-------+
| 'f'   | '12'  |
+-------+-------+

The filename argument is the path of the delimited file, all other keyword arguments are passed to csv.writer(). So, e.g., to override the delimiter from the default CSV dialect, provide the delimiter keyword argument.

Note that no attempt is made to check that the fields or row lengths are consistent with the existing data, the data rows from the table are simply appended to the file. See also the cat() function.

Supports transparent writing to .gz and .bz2 files.

petl.totsv(table, source=None, dialect=<class csv.excel_tab at 0xa9ae41c>, **kwargs)¶

Convenience function, as tocsv() but with different default dialect (tab delimited).

Supports transparent writing to .gz and .bz2 files.

New in version 0.9.

petl.appendtsv(table, source=None, dialect=<class csv.excel_tab at 0xa9ae41c>, **kwargs)¶

Convenience function, as appendcsv() but with different default dialect (tab delimited).

Supports transparent writing to .gz and .bz2 files.

New in version 0.9.

petl.topickle(table, source=None, protocol=-1)¶

Write the table to a pickle file. E.g.:

>>> from petl import topickle, look
>>> look(table)
+-------+-------+
| 'foo' | 'bar' |
+=======+=======+
| 'a'   | 1     |
+-------+-------+
| 'b'   | 2     |
+-------+-------+
| 'c'   | 2     |
+-------+-------+

>>> topickle(table, 'test.dat')
>>> # look what it did
... from petl import frompickle
>>> look(frompickle('test.dat'))
+-------+-------+
| 'foo' | 'bar' |
+=======+=======+
| 'a'   | 1     |
+-------+-------+
| 'b'   | 2     |
+-------+-------+
| 'c'   | 2     |
+-------+-------+

Note that if a file already exists at the given location, it will be overwritten.

The pickle file format preserves type information, i.e., reading and writing is round-trippable.

Supports transparent writing to .gz and .bz2 files.

petl.appendpickle(table, source=None, protocol=-1)¶

Append data to an existing pickle file. E.g.:

>>> from petl import look, frompickle
>>> # inspect an existing pickle file
... testdat = frompickle('test.dat')
>>> look(testdat)
+-------+-------+
| 'foo' | 'bar' |
+=======+=======+
| 'a'   | 1     |
+-------+-------+
| 'b'   | 2     |
+-------+-------+
| 'c'   | 2     |
+-------+-------+

>>> # append some data
... from petl import appendpickle
>>> look(table)
+-------+-------+
| 'foo' | 'bar' |
+=======+=======+
| 'd'   | 7     |
+-------+-------+
| 'e'   | 42    |
+-------+-------+
| 'f'   | 12    |
+-------+-------+

>>> appendpickle(table, 'test.dat')
>>> # look what it did
... look(testdat)
+-------+-------+
| 'foo' | 'bar' |
+=======+=======+
| 'a'   | 1     |
+-------+-------+
| 'b'   | 2     |
+-------+-------+
| 'c'   | 2     |
+-------+-------+
| 'd'   | 7     |
+-------+-------+
| 'e'   | 42    |
+-------+-------+
| 'f'   | 12    |
+-------+-------+

Note that no attempt is made to check that the fields or row lengths are consistent with the existing data, the data rows from the table are simply appended to the file. See also the cat() function.

Supports transparent writing to .gz and .bz2 files.

petl.tosqlite3(table, filename_or_connection, tablename, create=True, commit=True)¶

Load data into a table in an sqlite3 database. Note that if the database table exists, it will be truncated, i.e., all existing rows will be deleted prior to inserting the new data. E.g.:

>>> from petl import tosqlite3, look
>>> look(table)
+-------+-------+
| 'foo' | 'bar' |
+=======+=======+
| 'a'   | 1     |
+-------+-------+
| 'b'   | 2     |
+-------+-------+
| 'c'   | 2     |
+-------+-------+

>>> # by default, if the table does not already exist, it will be created
... tosqlite3(table, 'test.db', 'foobar')
>>> # look what it did
... from petl import fromsqlite3
>>> look(fromsqlite3('test.db', 'select * from foobar'))
+-------+-------+
| 'foo' | 'bar' |
+=======+=======+
| u'a'  | 1     |
+-------+-------+
| u'b'  | 2     |
+-------+-------+
| u'c'  | 2     |
+-------+-------+

Changed in version 0.10.2.

Either a database file name or a connection object can be given as the second argument. (Note that cachetag() is only implemented if a file name is given.)

petl.appendsqlite3(table, filename_or_connection, tablename, commit=True)¶

Load data into an existing table in an sqlite3 database. Note that the database table will be appended, i.e., the new data will be inserted into the table, and any existing rows will remain. E.g.:

>>> from petl import appendsqlite3, look
>>> look(moredata)
+-------+-------+
| 'foo' | 'bar' |
+=======+=======+
| 'd'   | 7     |
+-------+-------+
| 'e'   | 9     |
+-------+-------+
| 'f'   | 1     |
+-------+-------+

>>> appendsqlite3(moredata, 'test.db', 'foobar') 
>>> # look what it did
... from petl import look, fromsqlite3
>>> look(fromsqlite3('test.db', 'select * from foobar'))
+-------+-------+
| 'foo' | 'bar' |
+=======+=======+
| u'a'  | 1     |
+-------+-------+
| u'b'  | 2     |
+-------+-------+
| u'c'  | 2     |
+-------+-------+
| u'd'  | 7     |
+-------+-------+
| u'e'  | 9     |
+-------+-------+
| u'f'  | 1     |
+-------+-------+

Changed in version 0.10.2.

Either a database file name or a connection object can be given as the second argument. (Note that cachetag() is only implemented if a file name is given.)

petl.todb(table, connection_or_cursor, tablename, commit=True)¶

Load data into an existing database table via a DB-API 2.0 connection or cursor. Note that the database table will be truncated, i.e., all existing rows will be deleted prior to inserting the new data.

E.g.:

>>> from petl import look, todb
>>> look(table)
+-------+-------+
| 'foo' | 'bar' |
+=======+=======+
| 'a'   | 1     |
+-------+-------+
| 'b'   | 2     |
+-------+-------+
| 'c'   | 2     |
+-------+-------+

... using sqlite3:

>>> import sqlite3
>>> connection = sqlite3.connect('test.db')
>>> # assuming table "foobar" already exists in the database
... todb(table, connection, 'foobar')    

... using psycopg2:

>>> import psycopg2 
>>> connection = psycopg2.connect("dbname=test user=postgres")
>>> # assuming table "foobar" already exists in the database
... todb(table, connection, 'foobar')    

... using MySQLdb:

>>> import MySQLdb
>>> connection = MySQLdb.connect(passwd="moonpie", db="thangs")
>>> # tell MySQL to use standard quote character
... connection.cursor().execute('SET SQL_MODE=ANSI_QUOTES')
>>> # load data, assuming table "foobar" already exists in the database
... todb(table, connection, 'foobar')    

N.B., for MySQL the statement SET SQL_MODE=ANSI_QUOTES is required to ensure MySQL uses SQL-92 standard quote characters.

Changed in version 0.10.2.

A cursor can also be provided instead of a connection, e.g.:

>>> import psycopg2 
>>> connection = psycopg2.connect("dbname=test user=postgres")
>>> cursor = connection.cursor()
>>> todb(table, cursor, 'foobar')    

petl.appenddb(table, connection_or_cursor, tablename, commit=True)¶

Load data into an existing database table via a DB-API 2.0 connection or cursor. Note that the database table will be appended, i.e., the new data will be inserted into the table, and any existing rows will remain.

E.g.:

>>> from petl import look, appenddb
>>> look(table)
+-------+-------+
| 'foo' | 'bar' |
+=======+=======+
| 'a'   | 1     |
+-------+-------+
| 'b'   | 2     |
+-------+-------+
| 'c'   | 2     |
+-------+-------+

... using sqlite3:

>>> import sqlite3
>>> connection = sqlite3.connect('test.db')
>>> # assuming table "foobar" already exists in the database
... appenddb(table, connection, 'foobar')    

... using psycopg2:

>>> import psycopg2 
>>> connection = psycopg2.connect("dbname=test user=postgres")
>>> # assuming table "foobar" already exists in the database
... appenddb(table, connection, 'foobar')    

... using MySQLdb:

>>> import MySQLdb
>>> connection = MySQLdb.connect(passwd="moonpie", db="thangs")
>>> # tell MySQL to use standard quote character
... connection.cursor().execute('SET SQL_MODE=ANSI_QUOTES')
>>> # load data, appending rows to table "foobar" 
... appenddb(table, connection, 'foobar')    

N.B., for MySQL the statement SET SQL_MODE=ANSI_QUOTES is required to ensure MySQL uses SQL-92 standard quote characters.

Changed in version 0.10.2.

A cursor can also be provided instead of a connection, e.g.:

>>> import psycopg2 
>>> connection = psycopg2.connect("dbname=test user=postgres")
>>> cursor = connection.cursor()
>>> appenddb(table, cursor, 'foobar')    

petl.totext(table, source=None, template=None, prologue=None, epilogue=None)¶

Write the table to a text file. E.g.:

>>> from petl import totext, look    
>>> look(table)
+-------+-------+
| 'foo' | 'bar' |
+=======+=======+
| 'a'   | 1     |
+-------+-------+
| 'b'   | 2     |
+-------+-------+
| 'c'   | 2     |
+-------+-------+

>>> prologue = """{| class="wikitable"
... |-
... ! foo
... ! bar
... """
>>> template = """|-
... | {foo}
... | {bar}
... """
>>> epilogue = "|}"
>>> totext(table, 'test.txt', template, prologue, epilogue)
>>> 
>>> # see what we did
... with open('test.txt') as f:
...     print f.read()
...     
{| class="wikitable"
|-
! foo
! bar
|-
| a
| 1
|-
| b
| 2
|-
| c
| 2
|}

The template will be used to format each row via str.format.

Supports transparent writing to .gz and .bz2 files.

petl.appendtext(table, source=None, template=None, prologue=None, epilogue=None)¶

As totext() but the file is opened in append mode.

Supports transparent writing to .gz and .bz2 files.

petl.tojson(table, source=None, prefix=None, suffix=None, *args, **kwargs)¶

Write a table in JSON format, with rows output as JSON objects. E.g.:

>>> from petl import tojson, look
>>> look(table)
+-------+-------+
| 'foo' | 'bar' |
+=======+=======+
| 'a'   | 1     |
+-------+-------+
| 'b'   | 2     |
+-------+-------+
| 'c'   | 2     |
+-------+-------+

>>> tojson(table, 'example.json')
>>> # check what it did
... with open('example.json') as f:
...     print f.read()
... 
[{"foo": "a", "bar": 1}, {"foo": "b", "bar": 2}, {"foo": "c", "bar": 2}]

Note that this is currently not streaming, all data is loaded into memory before being written to the file.

Supports transparent writing to .gz and .bz2 files.

New in version 0.5.

petl.tojsonarrays(table, source=None, prefix=None, suffix=None, output_header=False, *args, **kwargs)¶

Write a table in JSON format, with rows output as JSON arrays. E.g.:

>>> from petl import tojsonarrays, look
>>> look(table)
+-------+-------+
| 'foo' | 'bar' |
+=======+=======+
| 'a'   | 1     |
+-------+-------+
| 'b'   | 2     |
+-------+-------+
| 'c'   | 2     |
+-------+-------+

>>> tojsonarrays(table, 'example.json')
>>> # check what it did
... with open('example.json') as f:
...     print f.read()
... 
[["a", 1], ["b", 2], ["c", 2]]

Note that this is currently not streaming, all data is loaded into memory before being written to the file.

Supports transparent writing to .gz and .bz2 files.

New in version 0.11.

Utility functions¶

petl.header(table)¶

Return the header row for the given table. E.g.:

>>> from petl import header
>>> table = [['foo', 'bar'], ['a', 1], ['b', 2]]
>>> header(table)
['foo', 'bar']

petl - Extract, Transform and Load (Tables of Data)¶

Overview¶

Introduction¶

Installation¶

Dependencies and extensions¶

Conventions - row containers and row iterators¶

Transformation pipelines¶

Caching / memoization¶

Extract - reading tables from files, databases and other sources¶

Transform - transforming tables¶

Load - writing tables to files and databases¶

Utility functions¶

Further Reading¶

Indices and tables¶

Table Of Contents

Next topic

This Page

Navigation

petl - Extract, Transform and Load (Tables of Data)¶

Overview¶

Introduction¶

Installation¶

Dependencies and extensions¶

Conventions - row containers and row iterators¶

Transformation pipelines¶

Caching / memoization¶

Extract - reading tables from files, databases and other sources¶

Transform - transforming tables¶

Load - writing tables to files and databases¶

Utility functions¶

Further Reading¶

Indices and tables¶

Table Of Contents

Next topic

This Page

Quick search

Navigation