Imported from streamlit/streamlit-pivot-table (SKILL.md). Install upstream with npx skills add streamlit/streamlit-pivot-table. Copyright stays with the author.

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
2. What st_pivot_table Returns
3. Deployment Paths — Local · Community Cloud · Container · SiS on SPCS
4. API Reference
5. Feature Guide
6. Frontend UX: Toolbar, Settings Panel, and Interactions
7. Theming
8. Keyboard Accessibility
9. Callbacks and State
10. Integration Patterns — Widget filters · Multiple pivots · Linked pivots · Tabs / columns / expanders · Persisting config
11. Performance: @st.fragment
12. Deploying to SiS on SPCS (with end-to-end example)
13. Replicating an Existing Pivot (Excel / Sigma / Tableau / Power BI)
14. Quick Reference Recipes

Quick Start

pip install streamlit-pivot

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.

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.

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).

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

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.

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)

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 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

Totals and Subtotals

Sorting

Display and Formatting

Layout

Interactivity and Callbacks

Filters

Data Control

Feature Guide

Aggregation Functions

Supported: "sum", "avg", "count", "min", "max", "count_distinct", "median", "percentile_90", "first", "last".

st_pivot_table(
    df,
    key="agg_demo",
    rows=["Region"],
    columns=["Year"],
    values=["Revenue", "Units", "Price"],
    aggregation={"Revenue": "sum", "Units": "count", "Price": "avg"},
)

"sum_over_sum" is not a valid table-wide aggregation — use synthetic_measures instead.

Synthetic Measures

Three operations:

• sum_over_sum — sum(numerator) / sum(denominator) (empty when denominator is 0)
• difference — sum(numerator) - sum(denominator)
• formula — arbitrary expression referencing aggregated fields; each field uses its configured aggregation

st_pivot_table(
    df,
    key="synth_demo",
    rows=["Region"],
    columns=["Year"],
    values=["Revenue", "Cost", "Units"],
    synthetic_measures=[
        {
            "id": "revenue_per_unit",
            "label": "Revenue / Unit",
            "operation": "sum_over_sum",
            "numerator": "Revenue",
            "denominator": "Units",
            "format": "$,.2f",
        },
        {
            "id": "margin_pct",
            "label": "Margin %",
            "operation": "formula",
            "formula": 'if("Revenue" > 0, ("Revenue" - "Cost") / "Revenue", 0)',
            "format": ".1%",
        },
    ],
)

Formula syntax: arithmetic (+ - * / ^ %), comparisons, and / or / not, if(cond, then, else), and abs() / min() / max() / round(x, n). CSP-safe (no eval). Division by zero returns null; if() short-circuits.

Sort Configuration

# Single sort
row_sort = {
    "by": "value",             # "key" or "value"
    "direction": "desc",       # "asc" or "desc"
    "value_field": "Revenue",  # required when by="value"
    "col_key": ["2023"],       # optional: sort within a specific column
    "dimension": "Category",   # optional: scope sort to a specific level
}

# Multi-field chained sort (0.5.0+)
row_sort = [
    {"by": "value", "direction": "desc", "value_field": "Revenue"},
    {"by": "key",   "direction": "asc"},   # secondary: breaks ties alphabetically
]

Multi-field sorting: pass a list; configs are applied as a chained comparator — config[0] is primary, config[1] activates only on ties, etc. Guarantees deterministic order.

Scoped sorting (dimension set) only reorders that level and below when subtotals are enabled. Header-menu sort sets dimension automatically.

Show Values As

Analytical mode semantics (0.5.0+):

• Totals / subtotals show raw aggregate for running_total, pct_running_total, rank; index shows empty; pct_of_parent shows relative to the subtotal's own parent.
• Null denominators → empty_cell_value.
• Export matches display (transformed values).
• Mutually exclusive with period-comparison modes per field.

Period-comparison modes require an active grouped temporal axis (auto hierarchy or explicit date_grains). Synthetic measures render as raw values and ignore show_values_as.

Date Hierarchy and Time Comparisons

Typed date and datetime fields on rows or columns are hierarchy-capable.

• Adaptive grain (default): year (>2y) · quarter (>1y) · month (>2mo) · day (shorter).
• Drill ladder: Year → Quarter → Month → Day. Week is available via header menu.
• Override precedence: explicit date_grains[field] > interactive state > auto default.
• date_grains[field] = None preserves raw values for that field.

st_pivot_table(
    df,
    key="date_demo",
    rows=["region"],
    columns=["order_date"],
    values=["Revenue"],
    date_grains={"order_date": "quarter"},
    show_values_as={"Revenue": "diff_from_prev_year"},
)

Parent headers (e.g. 2024, Q1 2024) collapse/expand inline on both axes. Exports always emit the full expanded leaf-level table.

Row Layout Modes

row_layout controls how row dimensions are visually arranged in the row header. Both modes display the same data — only the visual structure changes.

When to use Table: 1–2 row dimensions; when each dimension should be visually distinct in its own column; or when repeat_row_labels=True is needed.

When to use Hierarchy: 3+ row dimensions or when horizontal space is limited. The compact single-column layout keeps value columns visible without side-scrolling. Subtotals are auto-enabled so every group node shows its aggregate out of the box.

Key behavior differences:

• Auto-subtotals: when row_layout="hierarchy" and show_subtotals is not explicitly set, subtotals are automatically enabled for all grouping levels. Pass show_subtotals=False or show_subtotals=[...] to override. In "table" mode, show_subtotals defaults to False as usual.
• repeat_row_labels is ignored in hierarchy mode — the row axis is a single column, so there are no repeated labels to merge or repeat. The Settings Panel disables the Repeat Labels toggle automatically when hierarchy mode is active.
• Temporal date hierarchies (Year → Quarter → Month → Day) work in both layouts. In "table", each date level expands as a separate row-header column; in "hierarchy", the same levels render as nested tree levels within the single column.
• Export parity is preserved — CSV, TSV, clipboard, and XLSX outputs follow the selected row layout, including hierarchy indentation.
• Start collapsed: pass collapse_row_groups=True to render all groups folded on first load. Works in both layouts whenever collapsible groups exist.

# Default: one dedicated column per row dimension
st_pivot_table(
    df,
    key="table_mode",
    rows=["Region", "Category", "Product"],
    columns=["Year"],
    values=["Revenue"],
    show_subtotals=True,
    row_layout="table",   # default; can be omitted
)

# Hierarchy: single indented tree column
# show_subtotals auto-enables when not explicitly set
st_pivot_table(
    df,
    key="hierarchy_mode",
    rows=["Region", "Category", "Product"],
    columns=["Year"],
    values=["Revenue"],
    row_layout="hierarchy",
)

# Hierarchy with subtotals explicitly disabled
st_pivot_table(
    df,
    key="hierarchy_no_sub",
    rows=["Region", "Category", "Product"],
    columns=["Year"],
    values=["Revenue"],
    row_layout="hierarchy",
    show_subtotals=False,   # override the auto-enable
)

Number Format Patterns

d3-style. Examples: $,.0f → $12,345; ,.2f → 12,345.67; .1% → 34.5%; €,.2f → €12,345.67.

Use "__all__" in a dict for a default. Streamlit printf-style (%,.2f) works via column_config.

st_pivot_table(
    df,
    key="fmt_demo",
    rows=["Region"],
    columns=["Year"],
    values=["Revenue", "Profit"],
    number_format={"Revenue": "$,.0f", "Profit": ",.2f"},
    dimension_format={"Year": ".0f"},
)

Auto-formats from upstream sources. Precedence for format fields: explicit number_format / dimension_format > column_config > Pandas Styler. For alignment: explicit column_alignment > column_config.alignment > default (measures right, dims left). label, help, and width are column_config-driven only (no legacy kwargs). pinned unions with frozen_columns.

styled = df.style.format({"Revenue": "${:,.0f}"})
st_pivot_table(styled, key="styler_demo", rows=["Region"], values=["Revenue"])

Display-oriented column_config keys (Tier 1).

st_pivot_table(
    df,
    key="column_config_display",
    rows=["Region"],
    columns=["Year"],
    values=["Revenue"],
    column_config={
        "Region": {"label": "Area", "help": "Geographic region", "width": "large", "pinned": True},
        "Revenue": {"label": "Rev", "help": "Total revenue (USD)", "width": 180, "format": "$,.0f", "alignment": "right"},
    },
)

• label overrides display text only. The canonical field id is unchanged in the serialized config; sort, filter, and conditional-formatting rules still target the id. Empty / whitespace-only labels fall back to the id.
• help renders as a native title tooltip on the dim/measure header.
• width presets: "small"=100px, "medium"=120px, "large"=200px. Integers must be in [20, 2000]; out-of-range values warn once per field and are skipped. Interactive resize drags override the configured width at runtime but are not persisted to config (width returns to the configured value after rerun/remount).
• pinned=True (or "left") locks the field in the config UI — no drag, no remove. It does not create a visually sticky column. "right" warns and is ignored.
• alignment accepts "left", "center", or "right". It unions with the column_alignment kwarg; if both set a value for the same field, the explicit column_alignment kwarg wins. Invalid values warn once per field and are skipped (unlike the column_alignment kwarg, which still raises on invalid input).
• Unknown keys in dict literals warn once per (field, key); internal defaults on st.column_config.* objects (disabled, required, default) are silently ignored; recognized-but-unsupported column types (e.g. line_chart, selectbox) warn once per (field, type).

Row-dim cell renderers (Tier 2). A small set of type values change how row-dim cell values render. These apply only to row-dim cells; measure cells are numeric aggregates and ignore them. On Total / Subtotal rows, link / image / checkbox fall back to plain text (cell value is a label, not data); text with max_chars still truncates.

st_pivot_table(
    df,
    key="column_config_renderers",
    rows=["Homepage", "Poster", "Active", "Description"],
    values=["Revenue"],
    column_config={
        "Homepage":    st.column_config.LinkColumn("Homepage", display_text="Visit {}"),
        "Poster":      st.column_config.ImageColumn("Poster"),
        "Active":      st.column_config.CheckboxColumn("Active"),
        "Description": st.column_config.TextColumn("Description", max_chars=40),
    },
)

• type="link" — href = <raw value>. display_text can be a plain string or a {} template (substituted with the cell value, matching Streamlit's LinkColumn). Empty / null values fall back to plain text. Scheme allowlist: only http:, https:, mailto:, tel:, and schemeless (relative / protocol-relative) URLs render as anchors; javascript:, data:, file:, vbscript:, etc. fall back to plain text.
• type="image" — src = <raw value>. Uses loading="lazy" and a max-height guard so images don't blow out row height. In row_layout="hierarchy", the breadcrumb variant applies a tighter 1em cap. Src allowlist: http: / https: / schemeless URLs plus raster data:image/<mime> (png, jpeg, gif, webp, avif, bmp, ico); everything else — notably data:image/svg+xml and non-image data: — falls back to plain text.
• type="checkbox" — truthy → ☑, falsy → ☐. Accepts booleans, "true"/"false"/"yes"/"no"/"1"/"0" (case-insensitive), and the numbers 0 / 1. Unrecognized values fall back to plain text.
• type="text" + max_chars — truncates to max_chars UTF-16 code units (matches JS String.length / Streamlit's TextColumn) with an ellipsis; full text is preserved in the cell's title attribute. Truncation applies on every row, including Totals. Invalid max_chars (non-positive, non-integer, bool) warns once and is skipped.

Analytical Filters: Top N / Value Filters

(0.5.0+) Both filter types are display-only — they hide rows/columns in the rendered table but do not re-aggregate totals or subtotals. They apply after sorting, per-parent group.

Top N / Bottom N — top_n_filters

top_n_filters=[
    {
        "field": "Product",      # dimension to rank (must be in rows or columns)
        "n": 5,                  # how many members to keep (positive int)
        "by": "Revenue",         # measure to rank by (must be in values)
        "direction": "top",      # "top" (default) or "bottom"
        "axis": "rows",          # "rows" (default) or "columns"
    }
]

• Multiple filters are allowed; each applies independently to its named field.
• Ranking is within each parent group — e.g., top 5 Products within each Region.
• Ties are broken by the natural sort order of member names.

Value Predicate — value_filters

value_filters=[
    {
        "field": "Product",        # dimension member to evaluate
        "by": "Revenue",           # measure to evaluate
        "operator": "gte",         # see operator table below
        "value": 1000,             # primary threshold
        "value2": None,            # secondary threshold (required for "between")
        "axis": "rows",            # "rows" (default) or "columns"
    }
]

Interactive usage

Both filter types can also be configured interactively via the column header menu (click any dimension header → "Top / Bottom N" or "Filter by value"). The current config is reflected in the Settings Panel and returned by st_pivot_table in result["config"].

Key constraints

• field must be a dimension already in rows / columns.
• by must be a measure already in values.
• n (Top N) must be a positive integer.
• between requires both value and value2.
• Filters excluded from _build_sidecar_fingerprint — changing them never triggers a server re-aggregate in hybrid mode.

Values Axis Placement (0.5.0+)

values_axis="rows" moves all value fields onto the row axis. Each dimension row expands into N sub-rows (one per measure). A Values label column appears at the left of the row-header section to identify each measure. The "Σ Values" column group is suppressed from the column header.

# Income-statement layout
st_pivot_table(
    income_df,
    key="income_stmt",
    rows=["Account"],
    columns=["Quarter"],
    values=["Revenue", "COGS", "Gross Profit"],
    values_axis="rows",      # <-- moves metrics onto rows
    show_totals=False,
    number_format={"__all__": "$,.0f"},
)

# Multi-dimension with subtotals — sub-rows appear within each subtotal block too
st_pivot_table(
    df,
    key="regional",
    rows=["Region", "Category"],
    columns=["Year"],
    values=["Revenue", "Units"],
    values_axis="rows",
    show_subtotals=True,
    show_totals=True,
)

Incompatibilities — raises ValueError if combined with:

• Period-comparison show_values_as modes (diff_from_prev, pct_diff_from_prev, diff_from_prev_year, pct_diff_from_prev_year)
• Active temporal hierarchies (auto_date_hierarchy=True with a date/datetime axis field, or explicit date_grains on an axis field)

UI: Settings Panel → Layout section → Values axis segmented control ("Rows" option auto-disabled with tooltip when any incompatibility is active).

Conditional Formatting

st_pivot_table(
    df,
    key="cond_fmt",
    values=["Revenue", "Profit", "Units"],
    conditional_formatting=[
        {"type": "data_bars", "apply_to": ["Revenue"], "color": "#1976d2", "fill": "gradient"},
        {"type": "color_scale", "apply_to": ["Profit"],
         "min_color": "#fff", "mid_color": "#ffffff", "max_color": "#2e7d32", "mid_value": 0},
        {"type": "threshold", "apply_to": ["Units"], "conditions": [
            {"operator": "gt", "value": 250, "background": "#bbdefb", "color": "#0d47a1", "bold": True},
        ]},
    ],
)

Color-scale mid_value anchors the gradient at a specific raw aggregate (e.g. 0 for PnL). Threshold operators: gt, gte, lt, lte, eq, between. Conditional formatting rules are translated into native Excel conditional formatting on export.

scope ("per_column" | "global", optional) — controls color_scale/data_bars min/max range in XLSX. No effect on threshold rules. With values_axis="columns", default "per_column" (independent scale per year/category column). With values_axis="rows", omitting scope gives per-field behavior: each measure in apply_to gets its own independent scale — correct for mixed-unit fields (Revenue, Units, Margin). Set scope="global" to force a single scale across all selected measures; scope="per_column" for one scale per data column.

include_totals (bool, default False) — include mid-table subtotal rows in the CF range. Consistent across both values_axis modes.

Null Handling

"exclude" (default; null-dimension rows dropped) · "zero" (null measures → 0) · "separate" (null dimensions → "(null)" group).

st_pivot_table(df, key="null_demo", null_handling={"Region": "separate", "Revenue": "zero"})

Subtotals and Row Grouping

With 2+ row dimensions, show_subtotals=True exposes collapsible group subtotals with visual hierarchy cues (bold tinted group cells, indented leaves, group-boundary borders, inline +/− toggles). Pass a list to enable subtotals only for specific levels (show_subtotals=["Region"]).

Column Group Collapse/Expand

With 2+ column dimensions (or an expanded temporal hierarchy), parent column headers show an inline +/− toggle on hover.

Data Export

Excel (.xlsx), CSV, TSV, or clipboard via the toolbar download icon. Excel export is fully styled (merged headers, frozen panes, banded rows, number formats, native conditional formatting). Available in locked mode. Exports always emit the full expanded table.

Use export_filename to customize the base name (date + extension auto-appended).

Drill-Down Detail Panel

Click any data or total cell to open an inline panel of source records (up to 500 per page, paginated for larger sets). Column headers sort the full filtered set before paging. Set enable_drilldown=False to disable the panel while keeping on_cell_click.

Execution Mode

Auto-hybrid thresholds (0.5.0+): Three independent checks, evaluated in order:

1. Row ceiling — ≥ 500,000 rows → always pre-aggregate
2. Payload size — estimated Arrow payload ≥ 50 MB → pre-aggregate to reduce transfer cost
3. Pivot shape — visible cells > 5,000 or pivot groups > 10,000 → pre-aggregate

All checks produce machine-readable reason codes (auto:row_ceiling, auto:payload, auto:pivot_shape, auto:client_only) in the executionReason metric. Forced and incompatible modes produce forced:* and incompatible:* prefixes respectively.

All 10 aggregations are supported in hybrid mode; non-decomposable aggregations use a server-computed sidecar for correct totals. Synthetic measures (including formulas) evaluate client-side, even in hybrid mode.

Frontend rendering budget: Independently of execution mode, the frontend applies a 5,000-cell DOM budget (rows × columns × measures). Above this threshold the component switches to a virtualized renderer; column alignment is preserved in both modes. When the non-virtual path would exceed the budget the visible row count is capped and a warning banner is shown: "Showing X of Y rows. Reduce dimensions or apply filters to display all rows." This message also surfaces in perf_metrics["warnings"]. Fix by reducing column cardinality, adding source_filters, or letting execution_mode="auto" pre-aggregate so the post-aggregation cell count stays within budget. To hide informational banners (virtualization notices, hybrid execution-mode reason, perf timing) without hiding data-integrity messages, pass suppress_warnings=True.

Locked Mode

locked=True renders a viewer-mode experience: config controls are locked, but export, expand/collapse, drill-down, and header-menu sort/filter/show-values-as remain available.

Non-Interactive Mode

interactive=False hides the toolbar and disables header-menu config actions. Cell clicks and drill-down still work.

Frontend UX: Toolbar, Settings Panel, and Interactions

These are purely frontend behaviors — no Python parameters needed. All are active when interactive=True and disabled or restricted when locked=True or interactive=False.

Toolbar

When interactive=True, the toolbar at the top of the pivot shows read-only Rows, Columns, and Values zones. Each chip has a drag handle (⋮⋮). Hovering the top-right reveals a utility menu:

In locked mode, Reset, Swap, and config import/export are hidden; Export Data and the Settings gear remain visible but the Settings Panel is read-only for field layout.

Settings Panel (staged-commit UX)

The Settings Panel is the primary authoring surface for pivot configuration. Changes are staged locally and only applied when the user clicks Apply (Escape / Cancel discards). Contents:

• Available Fields — unassigned columns as draggable chips. A search input appears when there are more than 8 fields.
• Rows / Columns / Values drop zones — drag to reorder within, drag to move between zones, or use the x to remove. Value chips expose an aggregation picker on the badge. Synthetic fx chips appear inline with raw value chips and can be reordered alongside them (saved as value_order).
• Synthetic Measures — + Add measure opens the builder (Sum over Sum, Difference, or Formula). Formula mode provides an expression input, clickable field chips, and a ? tooltip listing supported operators. Existing chips expose an ✎ Edit action.
• Display Toggles — Row Totals, Column Totals, Subtotals, Repeat Labels, Row Layout, Sticky Headers.
• Invalid-drop feedback — dropping a non-numeric field into Values (for example) turns the zone red with an inline hint explaining why.

External changes while the panel is open (toolbar DnD, Reset, Swap, Import) close the panel and discard uncommitted edits.

Drag-and-Drop Field Configuration

Two contexts:

• Toolbar DnD — the main toolbar chips can be reordered or moved between Rows / Columns / Values zones directly. Changes apply immediately (not staged).
• Settings Panel DnD — inside the panel, the same behavior, but changes are staged until Apply.

Constraints:

• frozen_columns render without drag handles and cannot be dragged or removed.
• Non-numeric fields are rejected from the Values zone (invalid-drop feedback appears).
• Rows and Columns are mutually exclusive for a given field.
• A field can be in Values and one of Rows / Columns simultaneously.
• Synthetic fx chips can be reordered within Values but cannot leave it.
• When locked=True, all drag-and-drop is disabled.
• A 5 px activation distance distinguishes clicks from drags.
• Space + arrow keys provide a keyboard-accessible DnD path (see Keyboard Accessibility).

Config cleanup on move: when fields move between zones, related config properties (aggregation, sort, collapsed groups, subtotals, conditional formatting, show-values-as, per-measure totals, and value_order) are automatically synchronized. Orphan entries are dropped; newly added measures are appended.

Column Resize

Drag the right edge of any column header (cursor becomes col-resize). Minimum width 40 px. Double-click the resize handle to auto-size to content. Works with both virtualized and non-virtualized rendering. Widths are tracked per slot position.

Fullscreen

Click the expand icon (⤢) in the toolbar utility menu. The pivot fills the browser viewport as a fixed overlay; press Escape or click the collapse icon to exit. The table re-measures to fill the available height.

Field Search

When the Settings Panel has more than 8 available fields, a search input appears at the top of the Available Fields list.

Theming

The component inherits the Streamlit theme automatically. Its stylesheets are built on Streamlit's CSS custom properties — --st-text-color, --st-background-color, --st-secondary-background-color, --st-dataframe-border-color, --st-font, --st-base-radius, and related tokens — so the pivot picks up:

• Light / dark mode from theme.base in .streamlit/config.toml (or the user's Streamlit theme toggle).
• Custom theme colors from primaryColor, backgroundColor, secondaryBackgroundColor, textColor, and font in [theme].
• Border radius and font family from the app's base settings, so the pivot matches the look of st.dataframe and other native widgets.

No Python parameters or theme configuration are needed inside st_pivot_table — it just works.

One caveat for user-supplied colors. conditional_formatting rules take literal colors (min_color, max_color, mid_color, data-bar color, threshold background / color). These are not theme-aware — they render exactly as specified in both light and dark mode. If you expect the app to switch themes, pick colors that read on both backgrounds, or keep intense backgrounds paired with explicit foreground color overrides so text stays legible.

# Good: explicit text color paired with a vivid background so it works in dark mode too
{"type": "threshold", "apply_to": ["Profit"], "conditions": [
    {"operator": "gt", "value": 5000, "background": "#2e7d32", "color": "#ffffff", "bold": True},
]}

Styling

The style= parameter is a per-table layer over the Streamlit theme. It does not replace theming — it lets individual pivot tables deviate from the app theme without breaking dark mode or custom theme compatibility. style=None (the default) means the pivot tracks the app theme exactly.

Always use var(--st-...) tokens for theme-aware custom colors:

# ✅ Theme-aware: picks up dark mode and custom [theme] colors
style={"column_header": {"background_color": "var(--st-primary-color)"}}
style={"border_color": "color-mix(in srgb, var(--st-text-color) 20%, transparent)"}

# ❌ Not theme-aware: fixed hex breaks dark mode
style={"column_header": {"background_color": "#1a73e8"}}

Presets (theme-aware, no raw hex):

PivotStyle and RegionStyle shapes:

from streamlit_pivot import PivotStyle, RegionStyle, PIVOT_STYLE_PRESETS

class RegionStyle(TypedDict, total=False):
    background_color: str
    text_color: str
    font_weight: str   # "normal" | "bold"
    vertical_align: str  # "top" | "middle" | "bottom"

class PivotStyle(TypedDict, total=False):
    # Table-wide
    density: str           # "compact" | "default" | "comfortable"
    font_size: str         # e.g. "13px", "0.875rem"
    background_color: str  # cascades to all regions
    text_color: str
    stripe_color: str | None   # None = disable striping entirely
    row_hover_color: str | None  # None = disable hover entirely
    borders: str           # "all" | "outer" | "rows" | "columns" | "none"
    border_color: str
    # Region overrides
    column_header: RegionStyle
    row_header: RegionStyle
    data_cell: RegionStyle
    row_total: RegionStyle      # grand totals per row (rightmost col → .totalsCol)
    column_total: RegionStyle   # grand totals per col (bottom row → .totalsRow)
    subtotal: RegionStyle
    # Per-measure overrides (non-total data cells only)
    data_cell_by_measure: dict[str, RegionStyle]

Naming note: row_total styles cells in the rightmost (.totalsCol) column; column_total styles cells in the bottom (.totalsRow) row. This is intentional — API names follow user intent ("totals of each row"), not CSS class names.

Precedence (highest → lowest):

1. Conditional formatting (per-cell CF rule)
2. data_cell_by_measure[field] (per-cell; non-total data cells only)
3. Region overrides (column_header, row_header, data_cell, row_total, column_total, subtotal)
4. Table-wide cascade (background_color, text_color)
5. Streamlit theme (--st-* fallbacks)

Composition (list merging): Pass a list to merge presets and custom dicts left-to-right:

# Compact layout + contrast headers + custom row-total tint
style=[
    "compact",
    "contrast",
    PivotStyle(row_total=RegionStyle(
        background_color="color-mix(in srgb, var(--st-primary-color) 15%, var(--st-background-color))"
    )),
]

Keyboard Accessibility

The component follows WAI-ARIA patterns:

• Toolbar — arrow keys navigate between toolbar buttons (roving tabindex); Space/Enter activates.
• Drag-and-drop — Space to pick up a chip; arrow keys to move; Space again to drop. Screen reader announcements are provided by dnd-kit.
• Header menus — Escape closes; arrow keys navigate options; Space/Enter selects.
• Export / Import popovers — focus moves to the first control on open; tabbing out closes the popover.
• Settings Panel — focus moves in on open; Escape closes and discards staged changes; Tab navigates fields, zones, toggles, and buttons. Aggregation pickers respond to Enter/Space.
• Radio groups (export format/content) — arrow keys cycle; Space/Enter selects.
• Drill-down panel — focus moves to the close button on open; Escape closes; column headers are buttons that cycle sort (asc → desc → none).
• Data cells — focusable via Tab; Space/Enter triggers on_cell_click.

Callbacks and State

Streamlit Components V2 callbacks are called with no arguments. Read updated state from st.session_state[key] after the callback fires.

def on_click():
    payload = st.session_state["my_pivot"].get("cell_click")
    st.write("Clicked:", payload)

def on_config():
    config = st.session_state["my_pivot"].get("config")
    st.write("New config:", config)

result = st_pivot_table(
    df,
    key="my_pivot",
    rows=["Region"],
    columns=["Year"],
    values=["Revenue"],
    on_cell_click=on_click,
    on_config_change=on_config,
)

Cell Click Payload

{
    "rowKey": ["East"],
    "colKey": ["2023"],
    "value": 12345.0,
    "valueField": "Revenue",
    "filters": {"Region": "East", "Year": "2023"},
}

Total cells use ["Total"] for rowKey or colKey; the corresponding dimension is omitted from filters.

Config State

result["config"] contains the live configuration (rows, columns, values, aggregations, totals, sort, filters, collapsed groups, value_order, display options). Serialize it to persist layouts or sync multiple pivots — this is the same shape the toolbar's Copy Config / Import Config produces.

Performance Metrics

result["perf_metrics"] exposes render/pivot stats: parseMs, pivotComputeMs, renderMs, firstMountMs, sourceRows, sourceCols, totalRows, totalCols, totalCells, executionMode, needsVirtualization, columnsTruncated / truncatedColumnCount, warnings, lastAction.

metrics = result.get("perf_metrics") or {}
if metrics.get("executionMode") == "threshold_hybrid":
    st.caption(f"Pre-aggregated {metrics['sourceRows']} rows on the server.")

Integration Patterns

These are the most common real-world shapes a Streamlit app takes around st_pivot_table.

Pattern 1 — Sidebar / Widget Filters Driving a Pivot

The typical dashboard pattern: Streamlit widgets filter the source data; the pivot table visualizes the result.

You have two equivalent choices for applying the filters:

A. Filter the DataFrame before passing it in — simplest, works with any data source.

import streamlit as st
from streamlit_pivot import st_pivot_table

df = load_sales()

with st.sidebar:
    st.header("Filters")
    regions = st.multiselect("Region", sorted(df["Region"].unique()))
    years = st.multiselect("Year", sorted(df["Year"].unique()))
    min_rev = st.number_input("Min revenue", value=0)

filtered = df
if regions:
    filtered = filtered[filtered["Region"].isin(regions)]
if years:
    filtered = filtered[filtered["Year"].isin(years)]
filtered = filtered[filtered["Revenue"] >= min_rev]

st_pivot_table(
    filtered,
    key="sales_pivot",
    rows=["Region", "Category"],
    columns=["Year"],
    values=["Revenue", "Profit"],
)

B. Push the filters into source_filters — applied inside the component before pivot aggregation. Useful when you want the unfiltered DataFrame to remain available for other UI (e.g. a total-count metric).

source_filters = {}
if regions:
    source_filters["Region"] = {"include": regions}
if years:
    source_filters["Year"] = {"include": years}

st_pivot_table(
    df,
    key="sales_pivot",
    rows=["Region", "Category"],
    columns=["Year"],
    values=["Revenue", "Profit"],
    source_filters=source_filters,
)

source_filters is categorical/set-membership only — it does not express numeric ranges like Revenue >= X. Use option A for numeric range filters.

Pattern 2 — Multiple Pivots on One Page

Each pivot needs a distinct key. Wrap each in @st.fragment so interactions in one don't rerun the others.

df = load_data()

@st.fragment
def by_region():
    st.subheader("By Region")
    st_pivot_table(df, key="pivot_region", rows=["Region"], values=["Revenue"])

@st.fragment
def by_product():
    st.subheader("By Product")
    st_pivot_table(df, key="pivot_product", rows=["Product"], values=["Units"])

by_region()
by_product()

Pattern 3 — Linked Pivots (Drill From One Into Another)

Use on_cell_click on the summary pivot to drive filters on a detail pivot.

if "drill" not in st.session_state:
    st.session_state["drill"] = None

def handle_click():
    payload = st.session_state["summary"].get("cell_click")
    if payload:
        st.session_state["drill"] = payload.get("filters") or {}

st_pivot_table(
    df,
    key="summary",
    rows=["Region"],
    columns=["Year"],
    values=["Revenue"],
    on_cell_click=handle_click,
    enable_drilldown=False,  # we roll our own detail view
)

drill = st.session_state["drill"]
if drill:
    detail = df
    for field, value in drill.items():
        detail = detail[detail[field] == value]
    st.subheader("Detail")
    st_pivot_table(
        detail,
        key="detail",
        rows=["Category", "Product"],
        values=["Revenue", "Units"],
    )

Pattern 4 — Tabs, Columns, and Expanders

st_pivot_table renders correctly inside st.tabs, st.columns, st.expander, and st.container. A few practical notes:

• st.columns — the pivot will respect the column width. If the table has many columns, prefer a single full-width column or enable fullscreen.
• st.sidebar — technically supported but the sidebar is narrow; usually a bad fit for a multi-column pivot. Keep the pivot in the main area and put controls in the sidebar.
• st.tabs — each tab is rendered up-front, so pivots in hidden tabs still pay their compute cost unless wrapped in @st.fragment and gated behind an explicit trigger.
• st.expander — fine, but pivot height measurement runs on first render; collapsing/reopening re-measures automatically.

tab_summary, tab_detail = st.tabs(["Summary", "Detail"])

with tab_summary:
    st_pivot_table(df, key="t_summary", rows=["Region"], values=["Revenue"])

with tab_detail:
    st_pivot_table(df, key="t_detail", rows=["Region", "Product"], values=["Revenue", "Units"])

Pattern 5 — Persisting Config Across Sessions

The config dict is serializable. Save it (to st.session_state, a file, or a database keyed by user) and pass it back on the next run.

import json, pathlib

CONFIG_PATH = pathlib.Path("user_pivot_config.json")

def load_saved():
    if CONFIG_PATH.exists():
        return json.loads(CONFIG_PATH.read_text())
    return {}

def save_current():
    cfg = st.session_state["my_pivot"].get("config")
    if cfg:
        CONFIG_PATH.write_text(json.dumps(cfg, indent=2))

saved = load_saved()

result = st_pivot_table(
    df,
    key="my_pivot",
    on_config_change=save_current,
    **saved,  # unpack rows / columns / values / ... from the saved config
)

For multi-user apps, swap the file for a keyed row in your database and retrieve by user id.

Performance: Use @st.fragment With Multiple Pivots

Streamlit reruns the entire script on any widget change. With multiple pivots or expensive data prep, wrap each in @st.fragment so interactions scope reruns to that fragment.

@st.fragment
def sales_pivot():
    st_pivot_table(df, key="sales", rows=["Region"], values=["Revenue"])
    payload = st.session_state.get("sales", {}).get("cell_click")
    if payload:
        st.info(f"Clicked: {payload}")

sales_pivot()

Caveats:

• Streamlit ignores fragment return values during fragment reruns. Read results via st.session_state[key] if they need to be accessed outside the fragment.
• Keep random / non-deterministic data generation outside the fragment.
• on_config_change and on_cell_click fire on fragment reruns — as expected.

The reference example app (streamlit_app.py in this repo) has 23 numbered sections, each wrapped in @st.fragment.

Deploying to SiS on SPCS

Use this workflow for a Streamlit in Snowflake app that uses st_pivot_table. The component is installed straight from PyPI via PYPI_ACCESS_INTEGRATION — there is no custom wheel to copy into the project.

SPCS runtime is required. SiS apps on the legacy Warehouse runtime cannot install third-party PyPI packages. The app entity must set runtime.compute_pool.

Prerequisites

• Snow CLI (pip install snowflake-cli or brew install snowflake-cli). Verify with snow connection test.
• SiS on SPCS enabled for the account.
• PYPI_ACCESS_INTEGRATION available (confirm: snow sql -q "SHOW EXTERNAL ACCESS INTEGRATIONS"). If it does not exist, ask the user's Snowflake admin to create one — see Troubleshooting for the SQL.
• An SPCS compute pool for the app runtime. A small CPU_X64_XS pool is fine for most pivots; for interactive use with 100K+ row DataFrames, move to CPU_X64_S or larger so the React runtime has enough memory.
• A warehouse for SQL queries the app runs.
• Streamlit version ≥ 1.51 available on the target SiS runtime. SPCS runtimes regularly ship recent Streamlit versions, but verify after scaffolding — see Step 2.

Workflow

Step 1 — Gather Information (STOP and ask the user)

To set up your SiS on SPCS app with the pivot table component, I need:
1. Do you have an existing SiS project directory with snowflake.yml, or should I scaffold one?
2. What Snowflake table(s) will the app query?
3. What compute pool should the app run on? (e.g. MY_COMPUTE_POOL)
4. What warehouse should be used for queries? (e.g. MY_WAREHOUSE)
5. Is `PYPI_ACCESS_INTEGRATION` (or an equivalent EAI allowing pypi.org) already created in the account?

Do not proceed until the user answers Q1 and confirms Q5.

Step 2 — Scaffold the SiS Project (skip if they already have one)

snow init --template streamlit-python <app-name>
cd <app-name>

Minimal snowflake.yml — runtime.compute_pool makes it an SPCS app; external_access_integrations enables PyPI:

definition_version: "2"
entities:
  my_streamlit_app:
    type: streamlit
    identifier:
      name: my_streamlit_app
    title: "My Streamlit App"
    query_warehouse: <WAREHOUSE_NAME>
    main_file: streamlit_app.py
    artifacts:
      - streamlit_app.py
      - pyproject.toml
      - requirements.txt
    external_access_integrations:
      - PYPI_ACCESS_INTEGRATION
    runtime:
      compute_pool: <COMPUTE_POOL>

Minimal pyproject.toml:

[project]
name = "my-sis-app"
version = "0.1.0"
requires-python = ">=3.10"
dependencies = [
    "streamlit>=1.51",
    "streamlit-pivot>=0.3.0",
]

Minimal requirements.txt:

streamlit>=1.51
streamlit-pivot>=0.3.0

Pin streamlit>=1.51 explicitly. The component requires it, and the SiS runtime's default Streamlit version may be older than

Truncated - read the full file at https://github.com/streamlit/streamlit-pivot-table/blob/b4f822d471c82f5f5800078db1fd71f76543b63a/SKILL.md.