rollups

An iterable of dictionaries, with typed columns.

DataSet is a list of dictionary rows. Each row is an ordinary dict addressed by field name, and each column declares a Python type that its values convert to on first read. The container adds filtering, sorting, grouping, joining, pivoting, and summarizing.

A DataFrame stores each column as an array, so a row is a view built on demand and operations are written as vectors and masks. A DataSet stores each row as a dict, so for row in rows reads the stored object.

Use a DataFrame for rolling windows, matrix operations, and large row counts. Use a DataSet for records that arrive as dictionaries - a database cursor, a json payload, a csv - and for SQL-shaped operations on them. dataframe() and from_dataframe() convert between the two.

from rollups import DataSet

rows = DataSet([
    {'name': 'ana', 'group': 'a', 'amount': 120.5},
    {'name': 'bo',  'group': 'b', 'amount':  80.0},
    {'name': 'cy',  'group': 'a', 'amount':  45.25},
    ])
rows.columns = [('name', str), ('group', str), ('amount', float)]

for row in rows:
    if row.amount > 100:                       # a float, parsed on read
        print(f'{row.name} is over by {row.amount - 100:.2f}')

rows[0].name                       # 'ana'
rows[0]['name']                    # 'ana' - attribute or key
rows.sort_data('-amount')          # SQL order by, descending
rows.bucket('group', ['amount'])   # a: 165.75, b: 80.0

Install

From PyPI:

pip install rollups

Python 3.11 or later. It depends on pandas, libb-util, opendate, and prettytable.

Source: github.com/bissli/rollups.

To work on it:

git clone https://github.com/bissli/rollups
cd rollups
poetry install --extras test
python -m pytest -q

Typed columns

A column declares the type its values convert to, so a '120.5' read from a csv comes back as a float. The rows remain dicts. The types are metadata on the container.

A column is a (name, type) pair, declared or inferred:

records = [{'name': 'ana', 'amount': '120.5'}]

DataSet(records, columns=[('name', str), ('amount', float)])
DataSet(records)                             # types inferred from the rows
DataSet(records, infer_numeric_strings=True)   # '120.5' infers as float

Conversion is lazy: nothing converts at construction, and the first read of any row converts the whole container. Any class taking one argument serves as a column type.

Getting started covers the type system, the column operations, and the three kinds of copy.

What it does

Every one of these is documented under docs/.

Grouping

bucket is the SQL GROUP BY, and takes any callable as the aggregation:

rows.bucket('group', ['amount'])                 # sum, skipping None
rows.bucket('group', [('amount', max)])          # any callable
rows.bucket('group', [('amount', sum, 'total')]) # name the result
rows.bucket([], ['amount'])                      # one row, everything

pivot turns a column's values into columns of their own, flatten reverses that, and transpose swaps rows for columns. See Aggregating.

Joining

DataSet.join(left, 'key', right, 'key', 'left', bcol=['amount'])

Four join types, key columns that may be named differently on each side, per-side column selection and renaming, and a first flag for one-to-one matching. Where both sides carry a column, the left value wins unless it is None. See Joining.

Screening

A screen is a query language with one query per column. A screen is text, so it can come from a config file or a web form:

from rollups import apply_screen

apply_screen(rows, {'group': 'a|b', 'amount': '>50,<200'})
# rows is filtered in place

Comparison operators, regex search, None handling, and references to another column in the same row. See Screening.

Reading and writing

rows = DataSet.read('input.csv')      # type suffixes: name:s, age:i
rows.write_csv('output.csv')
rows.json(raw=True)                   # '[{"name": "ana"}]'

A csv header field may carry a type suffix - name:s, age:i, score:f, on:b, when:d - and a field without one reads as str. A line that will not decode is logged and skipped, and the read continues.

Excel goes through a backend the caller registers. This package imports no workbook library:

import rollups

rollups.register_excel_backend(my_excel_module)

See Reading and writing.

Presenting

totals = DataSet([
    {'name': 'ana', 'amount': 120.5},
    {'name': 'bo',  'amount':  80.0},
    ], columns=[('name', str), ('amount', float)])
totals.add_summary_row(label='Total')
print(totals.pp)

+-------+--------+
|  name | amount |
+-------+--------+
|  ana  | 120.50 |
|   bo  | 80.00  |
+-------+--------+
| Total | 200.50 |
+-------+--------+

The summary recomputes on every read, so filtering the rows and reading again gives the current total. See Summaries and output.

Working with pandas

DataSet.dataframe() and DataSet.from_dataframe() convert between the two representations, so a columnar step can run inside row-shaped work.

rollups.frame holds four functions that take and return pandas.DataFrame and do not reference DataSet:

from rollups import bucket_dataframe, join_dataframes

They answer differently from the DataSet versions on the same inputs: a malformed key raises where DataSet matches nothing silently, inner keeps left row order, and no result column is coerced to a reconstructed type. DataFrame-native join and group-by tabulates every difference.

Extending

Subclass DataSet, and every operation returns the subclass:

class Report(DataSet):

    def totals(self):
        return self.bucket([], [c for c, t in self.columns if t is float])

Every algorithm behind a method is also an exported function, so bucket_dataset(rows, ...) and rows.bucket(...) do the same thing. See Extending for the class-propagation rule, the workbook backend, and custom column types.

Documentation

Development

python -m pytest -q          # the suite
ruff check src/rollups/     # lint
bump2version patch           # never hand-edit the version

License

MIT. See LICENSE.