Skip to content

Work with DataFrames

A Store (cf Sharing state) can hold lists and dictionaries, but what if your data is a pandas DataFrame, or a Spark query over millions of rows ? Converting everything into a list of dicts and sending it to the browser would be expensive.

Put a pret.DataFrame in the Store instead. Widgets then request the rows they display, filtering and sorting run where the rows live, and edits are checked row by row. What you can do with it depends on where the rows come from

You add Rows live in Editable ?
Records, pandas or Arrow A SQLite copy owned by the Store Yes
A Spark or Ibis query The original source No, it is a read-only view of the query
A query result saved with save_store The SQLite file of the saved Store Yes

A small editable table

Run this notebook with Pret and Metanno installed, Metanno supplies the table widget.

import pandas as pd

from pret import DataFrame, create_store

todos = pd.DataFrame(
    [
        {"todo_id": 1, "text": "My first todo", "done": True},
        {"todo_id": 2, "text": "My second todo", "done": False},
        {"todo_id": 3, "text": "My third todo", "done": False},
    ]
)
store = create_store({"todos": DataFrame(todos)}, sync=True)

sync=True connects browser edits to this Python session.

from metanno import DataWidgetFactory

factory = DataWidgetFactory(store)
factory.create_table_widget(
    store_key="todos",
    primary_key="todo_id",
    fields=[
        {"key": "todo_id", "kind": "text"},
        {"key": "text", "kind": "text", "filterable": True},
        {"key": "done", "kind": "boolean", "editable": True},
    ],
    style={"height": "200px"},
)

Check a box, then read the rows from Python

store["todos"].materialize().to_pandas()
todo_id text done
0 1 My first todo True
1 2 My second todo False
2 3 My third todo False

materialize() collects the rows as an Arrow table, to_pandas() converts it. Now finish the second todo from Python

store["todos"].update(identity={"todo_id": 2}, values={"done": True})
store["todos"].materialize().to_pandas()
todo_id text done
0 1 My first todo True
1 2 My second todo True
2 3 My third todo False

The checkbox of the second todo changes in the table. identity names the row by a column value, so sorting or filtering the table does not change which row is edited.

Where did the edits go ?

print(todos["done"].tolist())
print(store["todos"].materialize()["done"].to_pylist())

[True, False, False]
[True, True, False]

The pandas table is unchanged. When you added it, Pret copied its rows into a SQLite database owned by the Store, and edits change that copy. SQLite is a database stored in a single file, here kept in memory, so the edits last as long as the Store. Save the Store to keep them in a file.

Browse a query without copying it

A Spark DataFrame or an Ibis table describes a query, its result can be too large to collect. Pret keeps it connected to its source: widgets send their filters and sorts to the source and receive a preview of at most 500 rows. This Ibis example needs pip install 'ibis-framework[duckdb]'

import ibis

backend = ibis.duckdb.connect()
backend.create_table("todos", todos)
query = backend.table("todos")
external = create_store({"todos": DataFrame(query)}, sync=True)

DataWidgetFactory(external).create_table_widget(
    store_key="todos",
    primary_key="todo_id",
    fields=[
        {"key": "todo_id", "kind": "text"},
        {"key": "text", "kind": "text", "filterable": True},
    ],
    source_requery=True,
    style={"height": "200px"},
)

DataFrame(spark_df) works the same way. These query DataFrames are read-only, the source database is never modified.

Make an editable copy of a query

Select what you want to work on, then save it

from pret import save_store

unfinished = query.filter(~query.done)
preview = create_store({"todos": DataFrame(unfinished)}, sync=True)
saved = save_store(preview, "unfinished-todos.pret", bounds={"todos": {"max_rows": 100}})
saved["todos"].update(identity={"todo_id": 3}, values={"done": True})
saved["todos"].materialize().to_pandas()
todo_id text done
0 2 My second todo False
1 3 My third todo True

save_store runs unfinished, copies its rows into unfinished-todos.pret and returns a Store connected to that file, where they are editable. max_rows rejects a larger result instead of truncating it, filter the query to choose the rows.

How much data reaches the browser ?

A connected Store sends pages of rows as the table scrolls, after filtering and sorting them where they live. Lists and dictionaries are sent whole, they remain the simplest choice for small collections and application values.

Next, save and reopen the Store. To use DataFrames in your own components, or connect another kind of source, see How widgets query DataFrames.