DOCSEARCHbyAlgolia
Submit Docs

messytables

repository·master·Indexed 18 days ago

https://github.com/okfn/messytables

A Python library for parsing messy tabular data. It provides tools to automatically detect headers and guess data types across various file formats, including CSV, XLS, XLSX, HTML, and ZIP. The library uses a system of stream processors registered to a RowSet to clean and transform inconsistent datasets, with support for exporting processed data to the JSON Table Schema format.

Tokens
1.5K
Snippets
3
Records
7
Agent score
14%

What's inside messytables

  1. Overview of messytables

    master
    messytables is a Python library designed to handle messy tabular data across various formats. Its primary capabilities include guessing data types and detecting table headers automatically, making it useful for processing unstructured or semi-structured tabular datasets.

  2. How stream processors work in messytables

    master

    Stream processors are functions used to apply transformations to a RowSet during iteration. To use one, you call row_set.register_processor(processor_function).

    A processor is a function that takes two arguments:

    1. The RowSet.
    2. The current row (a list of Cell objects).

    The processor must return:

    • A modified version of the row (a list of Cell objects).
    • None to indicate that the row should be dropped from the iteration.

    Commonly used processors (often implemented as closures) include:

    • types_processor: Applies guessed column types to rows.
    • offset_processor: Skips a specific number of rows.
    • null_processor: Handles null values.
    • headers_processor: Applies guessed header names to cells.

  3. Core entities: Cell, TableSet, and RowSet

    master

    Messytables uses three primary abstractions to represent tabular data:

    • Cell: Represents a single data point. It contains the value, the column name, and the type (CellType).
    • TableSet: A collection of tables (e.g., multiple sheets in an Excel workbook).
    • RowSet: An iterator over the rows of a single table. It provides a .sample for peeking at data and a .register_processor() method to apply transformations. Note that a RowSet can only be iterated over once.

    Common TableSet implementations include:

    • CSVTableSet: For CSV files (uses Python's dialect sniffer).
    • XLSTableSet: For Microsoft Excel 2003 format.
    • XLSXTableSet: For XML-based Excel format.
    • HTMLTableSet: For HTML documents (requires lxml).
    • ZIPTableSet: For loading CSV or Excel files from within ZIP archives.

  4. Parse messy tabular data with messytables

    master

    Messytables is designed to handle poorly formatted tabular data, such as CSVs with text fragments before the header, or files where column types are not explicitly defined. It uses a toolbox of independent heuristic methods (processors) that you register to a RowSet to clean and transform data during iteration.

    To process a file:

    1. Load it using a specific TableSet (e.g., CSVTableSet) or use any_tableset to auto-detect the format.
    2. Access a RowSet from the tables collection.
    3. Use row_set.sample to peek at data for guessing headers or types.
    4. Register processors (like headers_processor, offset_processor, or types_processor) to the RowSet to apply transformations.
    5. Iterate over the RowSet to get cleaned data.
    from messytables import CSVTableSet, type_guess, \\
    types_processor, headers_guess, headers_processor, \\
    offset_processor, any_tableset

fh = open('messy.csv', 'rb')

# Load a file object
table_set = CSVTableSet(fh)

# A table set is a collection of tables:
row_set = table_set.tables[0]

# A row set is an iterator; use .sample to peek at data
print row_set.sample.next()

# 1. Guess header names and the offset of the header
offset, headers = headers_guess(row_set.sample)
row_set.register_processor(headers_processor(headers))

# 2. Skip the header rows (add 1 to begin with content, not the header)
row_set.register_processor(offset_processor(offset + 1))

# 3. Guess column types
types = type_guess(row_set.sample, strict=True)

# 4. Apply these types to each row during iteration
row_set.register_processor(types_processor(types))

# 5. Run operations on the cleaned data
for row in row_set:
    do_something(row)

  6. Detect column types with type_guess

    master

    The type_guess function performs brute-force type detection by attempting to convert column members into various types and finding the best match. This is useful for CSV data where type information is missing.

    Supported Types:

    • StringType
    • IntegerType
    • FloatType
    • DecimalType
    • BoolType
    • DateType
    • DateUtilType
    from messytables import type_guess

# types = type_guess(sample, strict=True)
# strict=True ensures a more rigorous match

  7. Detect headers with headers_guess

    master

    Because many tabular files contain titles or metadata in the first few rows, headers_guess scans the initial rows of a RowSet to determine which row actually contains the column headers and what those header names are.

    from messytables import headers_guess

# offset, headers = headers_guess(row_set.sample)
# offset: the integer index where the header is located
# headers: the list of header names