petl

Warnings

• breaking Version 2.0 of petl will drop support for Python 2.7, with the minimum supported Python version becoming 3.6. Code running on older Python environments will require an upgrade.
  Fix: Ensure your Python environment is 3.6 or newer before upgrading to petl 2.0. Update any Python 2.7 specific syntax.
• gotcha When creating custom generator functions within a petl pipeline, directly raising `StopIteration` to signal exhaustion will result in a `RuntimeError` in Python 3.7 and later, due to PEP 479. petl itself has addressed this internally in `v1.7.8` and `v1.7.14`.
  Fix: Replace explicit `raise StopIteration` with a simple `return` statement in your custom generator functions. Alternatively, catch `StopIteration` if using `next()` calls within your generator.
• gotcha While petl emphasizes lazy evaluation, certain operations like `sort()`, `tojson()` (without `lines=True`), `look()`, or `see()` can load entire tables into memory, potentially leading to high memory consumption for very large datasets.
  Fix: Be mindful of operations that require materializing the entire table. For large datasets, consider chunking, using `lines=True` for JSON output, or exploring `petlx` extensions or alternative packages (e.g., Dask, PyTables) for memory-intensive tasks if petl's built-in options are insufficient.
• gotcha Prior to `v1.7.16`, joining tables with uneven rows could produce incorrect results. Users performing join operations on such tables with older versions might experience data integrity issues.
  Fix: Upgrade to petl `v1.7.16` or newer to benefit from the fix for joining tables with uneven rows.
• gotcha When using `etl.fromdicts()` without explicitly providing a header, the order of fields inferred from sampling the input dictionaries might not be stable or consistent across runs.
  Fix: Always provide an explicit header argument to `etl.fromdicts()` for predictable column ordering, e.g., `etl.fromdicts(dicts, header=['field1', 'field2'])`. Alternatively, use `etl.sortheader()` on the resulting table.
• gotcha In `v1.7.12`, a fix clarified that `to*()` functions (e.g., `tocsv()`, `tojson()`) should output to `stdout` by default if no specific output file/source is provided. Code on older versions or code expecting a different default behavior might need adjustment.
  Fix: Explicitly specify an output file for `to*()` functions (e.g., `etl.tocsv(table, 'output.csv')`) to avoid unintended output to `stdout`.

Install

• pip install petl Install stable version

Imports

• petl

  import petl as etl

  The convention is to import petl with the alias `etl` for brevity and clarity in ETL operations.

Quickstart

This quickstart demonstrates a basic ETL pipeline using petl: extracting data from a CSV, filtering rows based on conditions, adding a new field, and loading the result into another CSV file. petl tables are lazy, meaning operations are defined but not executed until data is requested (e.g., when writing to a file or viewing with `look()`).

import petl as etl
import os

# Simulate an input CSV file
csv_data = """name,age,city
alice,30,new york
bob,24,london
charlie,35,paris
diana,28,london
"""
with open('input.csv', 'w') as f:
    f.write(csv_data)

# Extract: Read data from a CSV file
table1 = etl.fromcsv('input.csv')

# Transform: Filter rows where age is > 25 and city is 'london'
table2 = etl.select(table1, lambda row: row.age > 25 and row.city == 'london')

# Add a new column 'status'
table3 = etl.addfield(table2, 'status', 'eligible')

# Load: Write the transformed data to a new CSV file
etl.tocsv(table3, 'output.csv')

# Verify the output
with open('output.csv', 'r') as f:
    print(f.read())

# Expected output:
# name,age,city,status
# bob,24,london,eligible - Correction: This should be: bob,24,london,eligible (if age > 20 for example)
# Corrected expected output (age > 25 AND city == 'london'):
# name,age,city,status
# diana,28,london,eligible