streamlit-pivot

# Streamlit Pivot Table Component `streamlit-pivot` provides `st_pivot_table` — a BI-focused pivot table component built with Streamlit Components V2, React, and TypeScript. It supports multi-dimensional pivoting, interactive sorting/filtering, subtotals with collapse/expand, conditional formatting, Excel/CSV/TSV/clipboard export, drill-down detail panels, drag-and-drop field configuration, synthetic (derived) measures with a formula engine, date/time hierarchies with period-over-period comparisons, hierarchical row layouts, values axis placement (put measures on rows for income-statement-style reports), multi-field chained sorting, Top N / Bottom N and value-predicate analytical filters, column resize, fullscreen mode, and server-side pre-aggregation for large datasets. **Current version:** 0.6.0 **Requirements:** Python >= 3.10, Streamlit >= 1.51 ## When to Use - User wants to add a pivot table to a Streamlit app - User mentions `streamlit_pivot` or `st_pivot_table` - User needs interactive data summarization with row/column dimensions and aggregated measures - User wants to **group dimension members** — e.g. combine "Northeast" + "Southeast" into "East Coast", or bundle product SKUs into a custom segment (`member_groups`, 0.6.0+) - User wants to deploy a Streamlit app that includes the pivot table, whether locally, on **Streamlit Community Cloud**, in a **container** (Docker / ECS / Cloud Run / Kubernetes), or as **Streamlit in Snowflake (SiS) on SPCS** ## Table of Contents 1. [Quick Start](#quick-start) 2. [What `st_pivot_table` Returns](#what-st_pivot_table-returns) 3. [Deployment Paths](#deployment-paths) — [Local](#path-a--local-development) · [Community Cloud](#path-b--streamlit-community-cloud) · [Container](#path-c--custom-container-docker--ecs--cloud-run--k8s) · [SiS on SPCS](#deploying-to-sis-on-spcs) 4. [API Reference](#api-reference) 5. [Feature Guide](#feature-guide) 6. [Frontend UX: Toolbar, Settings Panel, and Interactions](#frontend-ux-toolbar-settings-panel-and-interactions) 7. [Theming](#theming) 8. [Keyboard Accessibility](#keyboard-accessibility) 9. [Callbacks and State](#callbacks-and-state) 10. [Integration Patterns](#integration-patterns) — [Widget filters](#pattern-1--sidebar--widget-filters-driving-a-pivot) · [Multiple pivots](#pattern-2--multiple-pivots-on-one-page) · [Linked pivots](#pattern-3--linked-pivots-drill-from-one-into-another) · [Tabs / columns / expanders](#pattern-4--tabs-columns-and-expanders) · [Persisting config](#pattern-5--persisting-config-across-sessions) 11. [Performance: `@st.fragment`](#performance-use-stfragment-with-multiple-pivots) 12. [Deploying to SiS on SPCS](#deploying-to-sis-on-spcs) (with end-to-end example) 13. [Replicating an Existing Pivot (Excel / Sigma / Tableau / Power BI)](#replicating-an-existing-pivot-excel--sigma--tableau--power-bi) 14. [Quick Reference Recipes](#quick-reference-recipes) --- ## Quick Start ```sh pip install streamlit-pivot ``` ```python import pandas as pd import streamlit as st from streamlit_pivot import st_pivot_table df = pd.read_csv("sales.csv") result = st_pivot_table( df, key="my_pivot", rows=["Region"], columns=["Year"], values=["Revenue"], aggregation="sum", ) ``` - `data` accepts the same inputs as `st.dataframe` (Pandas/Polars DataFrames, NumPy arrays, dicts, lists of records, PyArrow Tables, Pandas `Styler`). - If `rows`, `columns`, and `values` are all omitted, the component auto-detects dimensions (categorical + low-cardinality numeric) and measures (high-cardinality numeric). - **Every call must pass a unique `key`** — required for state persistence across reruns and for multiple pivots on the same page. ## What `st_pivot_table` Returns `st_pivot_table` is primarily a **display** component. The return value and `st.session_state[key]` give you the current **configuration and metadata**, not the aggregated pivot data. ```python result = st_pivot_table(df, key="my_pivot", rows=["Region"], values=["Revenue"]) result["config"] # current pivot config (rows, columns, values, sort, filters, ...) result.get("perf_metrics") # render/pivot timing and shape stats (may be None) # Cell-click payload is a *trigger value* — read it from session_state, not the return dict. st.session_state["my_pivot"].get("cell_click") ``` Key consequences: - **To get the aggregated data out of the app**, use the toolbar **Export Data** action (Excel / CSV / TSV / clipboard). There is no Python method that returns the pivoted DataFrame. - **To get the source records for a cell**, use `on_cell_click` + the `cell_click` payload's `filters` dict and apply them yourself to your source DataFrame (or let the built-in drill-down panel do it). - **To persist user customizations**, serialize `result["config"]` (matches the format produced by **Copy Config** in the toolbar) and pass it back as `**config` on the next run — see [Pattern 5](#pattern-5--persisting-config-across-sessions). --- ## Deployment Paths In every environment, the component is installed **from PyPI as `streamlit-pivot`**. There is no custom-wheel workflow — `pip install streamlit-pivot` is the supported path everywhere, including Streamlit in Snowflake on SPCS (where PyPI access is granted via `PYPI_ACCESS_INTEGRATION`). | Path | Environment | Install | |------|-------------|---------| | A | Local development | `pip install streamlit-pivot` | | B | Streamlit Community Cloud | `streamlit-pivot` in `requirements.txt` | | C | Custom container (Docker / ECS / Cloud Run / K8s) | `pip install streamlit-pivot` in the image | | D | **Streamlit in Snowflake (SiS) on SPCS** | `streamlit-pivot` in `requirements.txt` + `PYPI_ACCESS_INTEGRATION` on the SiS entity. SPCS runtime is required. [Full workflow →](#deploying-to-sis-on-spcs) | > **Decision question to ask the user:** > > "Where will your app run? (a) locally, (b) Streamlit Community Cloud, (c) a container you control (Docker / ECS / Cloud Run / K8s), or (d) Streamlit in Snowflake on SPCS?" ### Path A — Local Development ```sh pip install streamlit-pivot # or: uv pip install streamlit-pivot streamlit run streamlit_app.py ``` ### Path B — Streamlit Community Cloud 1. Push the repo to GitHub with a top-level `streamlit_app.py` (or point the Community Cloud config to its path). 2. Add `requirements.txt`: ``` streamlit>=1.51 streamlit-pivot>=0.3.0 pandas ``` 3. Create the app at [share.streamlit.io](https://share.streamlit.io). The component works out of the box on Community Cloud — no additional configuration, asset upload, or proxy setup is needed. ### Path C — Custom Container (Docker / ECS / Cloud Run / K8s) ```dockerfile FROM python:3.11-slim WORKDIR /app COPY requirements.txt ./ RUN pip install --no-cache-dir -r requirements.txt COPY . . EXPOSE 8501 CMD ["streamlit", "run", "streamlit_app.py", "--server.address=0.0.0.0", "--server.port=8501"] ``` `requirements.txt`: ``` streamlit>=1.51 streamlit-pivot>=0.3.0 pandas ``` The build environment needs outbound PyPI access. For air-gapped builds, mirror `streamlit-pivot` into your internal PyPI index and point `pip` at it. ### Path D — Streamlit in Snowflake (SiS) on SPCS See the full guided workflow in [Deploying to SiS on SPCS](#deploying-to-sis-on-spcs) below. --- ## API Reference ### `st_pivot_table(data, *, ...)` Creates a pivot table component. All parameters except `data` are keyword-only. Returns a `PivotTableResult` dict. #### Core Parameters | Parameter | Type | Default | Description | |-----------|------|---------|-------------| | `data` | DataFrame-like | *(required)* | Source data. Accepts the same types as `st.dataframe`: Pandas/Polars DataFrame or Series, NumPy array, dict, list of records, PyArrow Table, Pandas `Styler`, etc. | | `key` | `str` | *(required)* | Unique component key. Each pivot on a page must have a distinct key. | | `rows` | `list[str] \| None` | `None` | Row dimensions. | | `columns` | `list[str] \| None` | `None` | Column dimensions. | | `values` | `list[str] \| None` | `None` | Measure columns. | | `synthetic_measures` | `list[dict] \| None` | `None` | Derived measures. See [Synthetic Measures](#synthetic-measures). | | `aggregation` | `str \| dict[str, str]` | `"sum"` | Raw-value aggregation. See [Aggregation Functions](#aggregation-functions). | | `auto_date_hierarchy` | `bool` | `True` | Auto-group typed date/datetime fields placed on rows or columns. | | `date_grains` | `dict[str, str \| None] \| None` | `None` | Per-field grain: `"year"`, `"quarter"`, `"month"`, `"week"`, `"day"`, or `None` (raw). | | `interactive` | `bool` | `True` | `False` hides the toolbar and disables header-menu sort/filter/show-values-as. | #### Totals and Subtotals | Parameter | Type | Default | Description | |-----------|------|---------|-------------| | `show_totals` | `bool` | `True` | Grand totals default for row/column totals. | | `show_row_totals` | `bool \| list[str] \| None` | `None` | `True`/`False`/list of measure names. | | `show_column_totals` | `bool \| list[str] \| None` | `None` | Same semantics. | | `show_subtotals` | `bool \| list[str]` | `False` | `True` all parent dimensions, list names specific levels. | | `subtotal_position` | `"top" \| "bottom"` | `"bottom"` | Where subtotal rows appear: `"bottom"` after members (Excel default), `"top"` before members as group headers. No effect when `show_subtotals=False` or `row_layout="hierarchy"`. | | `repeat_row_labels` | `bool` | `False` | Repeat row labels instead of merging. Ignored when `row_layout="hierarchy"`. | #### Sorting | Parameter | Type | Default | Description | |-----------|------|---------|-------------| | `row_sort` | `dict \| list[dict] \| None` | `None` | Initial row sort. Single dict or list for multi-field chained sorting. See [Sort Configuration](#sort-configuration). | | `col_sort` | `dict \| list[dict] \| None` | `None` | Initial column sort (same shape, no `col_key`). Accepts a list too. | | `sorters` | `dict[str, list[str]] \| None` | `None` | Custom sort ordering per dimension. | #### Display and Formatting | Parameter | Type | Default | Description | |-----------|------|---------|-------------| | `number_format` | `str \| dict[str, str] \| None` | `None` | d3-style pattern(s) for value cells. `"__all__"` = default. | | `dimension_format` | `str \| dict[str, str] \| None` | `None` | Same, for row/column dimension labels. | | `column_alignment` | `dict[str, str] \| None` | `None` | `"left"` / `"center"` / `"right"`. | | `show_values_as` | `dict[str, str] \| None` | `None` | Per-field display mode. See [Show Values As](#show-values-as). |