You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@superset.apache.org by ru...@apache.org on 2026/08/29 03:34:22 UTC
(superset) branch master updated: feat(postprocessing): teach pivot() to compute percent-of-row/col/total (#42809) (#42976)
This is an automated email from the ASF dual-hosted git repository.
rusackas pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/superset.git
The following commit(s) were added to refs/heads/master by this push:
new 6f69fc6dba0 feat(postprocessing): teach pivot() to compute percent-of-row/col/total (#42809) (#42976)
6f69fc6dba0 is described below
commit 6f69fc6dba037282b656e37aedec3cb9675171d3
Author: Abdul Rehman <76...@users.noreply.github.com>
AuthorDate: Sat Aug 29 08:34:15 2026 +0500
feat(postprocessing): teach pivot() to compute percent-of-row/col/total (#42809) (#42976)
Co-authored-by: Evan Rusackas <ev...@preset.io>
Co-authored-by: Claude Opus 4.7 <no...@anthropic.com>
---
superset/utils/pandas_postprocessing/pivot.py | 190 ++++++++-
.../unit_tests/pandas_postprocessing/test_pivot.py | 425 +++++++++++++++++++++
2 files changed, 614 insertions(+), 1 deletion(-)
diff --git a/superset/utils/pandas_postprocessing/pivot.py b/superset/utils/pandas_postprocessing/pivot.py
index 3f8883bfb56..e18382935a3 100644
--- a/superset/utils/pandas_postprocessing/pivot.py
+++ b/superset/utils/pandas_postprocessing/pivot.py
@@ -16,6 +16,7 @@
# under the License.
from typing import Any, Optional
+import pandas as pd
from flask_babel import gettext as _
from pandas import DataFrame
@@ -26,6 +27,116 @@ from superset.utils.pandas_postprocessing.utils import (
validate_column_args,
)
+_PERCENT_MODES = frozenset({"percent_row", "percent_col", "percent_total"})
+
+# Aggregate operator names that produce additive results across groups —
+# the sum of the per-cell values equals the row/column/grand rollup the
+# database would compute over the underlying rows. ``show_values_as``
+# percent transforms divide each cell by that sum, so they are only
+# meaningful when the sum IS the rollup. For non-additive operators
+# (mean, median, min, max, etc.) the "percent of row" the exports would
+# show is not the "percent of row" the chart's DB rollup would show,
+# and the two disagree. See sadpandajoe's finding on #42976.
+_ADDITIVE_OPERATORS = frozenset({"sum", "nansum", "count", "count_nonzero"})
+
+
+def _div_preserving_nan(numerator: DataFrame, denominator: Any, axis: int) -> DataFrame:
+ """Divide ``numerator`` by ``denominator``, preserving NaN numerators.
+
+ A genuine SQL NULL numerator must stay NaN (rendered blank) rather than
+ become ``0.0`` — matches the client-side #42810 semantics guarding
+ against measured "0.0%" values for values that should stay blank.
+ """
+ result = numerator.div(denominator, axis=axis)
+ return result.mask(numerator.isna(), other=float("nan"))
+
+
+def _apply_percent_transform_to_group(g: DataFrame, mode: str) -> DataFrame:
+ """Apply a percent-of-{row,col,total} transform to a single-metric block.
+
+ Called both for the whole DataFrame when it holds one metric, and
+ per-metric-group for MultiIndex / flat-multi-metric pivots. A zero or
+ NaN denominator produces NaN cells rather than ``Infinity``/``NaN``
+ from division-by-zero, matching the client's ``if (acc === null)
+ return null`` guard in ``fractionOf``.
+ """
+ if mode == "percent_row":
+ row_totals = g.sum(axis=PandasAxis.COLUMN, skipna=True).replace(0, float("nan"))
+ return _div_preserving_nan(g, row_totals, axis=PandasAxis.ROW)
+ if mode == "percent_col":
+ col_totals = g.sum(axis=PandasAxis.ROW, skipna=True).replace(0, float("nan"))
+ return _div_preserving_nan(g, col_totals, axis=PandasAxis.COLUMN)
+ # percent_total
+ grand = g.sum(skipna=True).sum(skipna=True)
+ if pd.isna(grand) or grand == 0:
+ return g * float("nan")
+ return _div_preserving_nan(g, grand, axis=PandasAxis.ROW)
+
+
+def _apply_show_values_as(df: DataFrame, mode: str) -> DataFrame:
+ """Divide each metric cell by the appropriate rollup total.
+
+ Mirrors the client-side ``fractionOf`` semantic in
+ ``plugin-chart-pivot-table/src/react-pivottable/utilities.ts:739``:
+
+ - ``percent_row``: cell / row-total (sum across the columns axis)
+ - ``percent_col``: cell / column-total (sum across the rows axis)
+ - ``percent_total``: cell / grand-total (sum of the metric block)
+
+ Per-metric isolation — the totals are computed *within each metric* so
+ one metric's numerator is never divided by another metric's total,
+ matching the client's ``metricAxis`` handling. This applies to both
+ shapes ``pivot_table`` can produce:
+
+ - **MultiIndex columns** (level 0 = metric): iterate the level-0
+ groups explicitly. Explicit iteration avoids the deprecated
+ ``df.groupby(level=0, axis=1)`` pattern (removed in pandas 3.x).
+ - **Flat columns with >1 column** (multi-metric pivot with no
+ ``columns`` groupby — each column IS a metric): treat each column
+ as its own single-column metric block.
+ - **Flat columns with 1 column** (single-metric pivot with no
+ ``columns`` groupby): the whole block is one metric.
+
+ NULL preservation is *structural* only: cells that ``pivot_table``
+ left as ``NaN`` because the (row, column) group had no input rows
+ at all stay ``NaN`` in the output. Cells whose input rows all held
+ SQL NULL values have already collapsed to ``0.0`` inside
+ ``pivot_table`` (pandas ``sum([NaN]) == 0``), so they render as
+ ``0%``, not blank — reconstructing "blank vs measured zero" from an
+ aggregated value is not possible at this layer.
+ """
+ if df.empty:
+ # Empty pivots can carry an empty ``MultiIndex`` with zero level-0
+ # groups; iterating and concatenating produces
+ # ``ValueError: No objects to concatenate``. Nothing to transform.
+ return df
+
+ is_multi_metric_wide = isinstance(df.columns, pd.MultiIndex)
+ is_flat_multi_metric = not is_multi_metric_wide and df.shape[1] > 1
+
+ if is_multi_metric_wide:
+ # Iterate level-0 groups explicitly (pandas-3-safe).
+ metrics = df.columns.get_level_values(0).unique()
+ parts = []
+ for metric in metrics:
+ block = df.xs(metric, axis=PandasAxis.COLUMN, level=0, drop_level=False)
+ parts.append(_apply_percent_transform_to_group(block, mode))
+ # ``concat`` along columns preserves the MultiIndex; reorder to
+ # match the original column layout deterministically.
+ combined = pd.concat(parts, axis=PandasAxis.COLUMN)
+ return combined[df.columns]
+
+ if is_flat_multi_metric:
+ # Each flat column is its own metric — process independently.
+ parts = []
+ for col in df.columns:
+ block = df[[col]]
+ parts.append(_apply_percent_transform_to_group(block, mode))
+ return pd.concat(parts, axis=PandasAxis.COLUMN)[df.columns]
+
+ # Flat single-metric block — the whole DataFrame is one metric.
+ return _apply_percent_transform_to_group(df, mode)
+
def _restore_dropped_metric_columns(
df: DataFrame,
@@ -77,7 +188,7 @@ def _restore_dropped_metric_columns(
@validate_column_args("index", "columns")
-def pivot( # pylint: disable=too-many-arguments
+def pivot( # pylint: disable=too-many-arguments # noqa: C901
df: DataFrame,
index: list[str],
aggregates: dict[str, dict[str, Any]],
@@ -88,6 +199,7 @@ def pivot( # pylint: disable=too-many-arguments
combine_value_with_metric: bool = False,
marginal_distributions: Optional[bool] = None,
marginal_distribution_name: Optional[str] = None,
+ show_values_as: Optional[str] = None,
) -> DataFrame:
"""
Perform a pivot operation on a DataFrame.
@@ -111,6 +223,13 @@ def pivot( # pylint: disable=too-many-arguments
:param marginal_distributions: Add totals for row/column. Default to False
:param marginal_distribution_name: Name of row/column with marginal distribution.
Default to 'All'.
+ :param show_values_as: Optional post-pivot transform that expresses each
+ metric cell as a fraction of the row / column / grand total.
+ One of ``"percent_row"``, ``"percent_col"``, ``"percent_total"`` or
+ ``None`` / ``"actual"`` (no-op, default). Mirrors the pivot chart's
+ client-side ``fractionOf`` semantic so server-side rendering paths
+ (CSV / XLSX exports, scheduled reports) can reproduce the browser
+ output. See #42809.
:return: A pivot table
:raises InvalidPostProcessingError: If the request in incorrect
"""
@@ -123,6 +242,62 @@ def pivot( # pylint: disable=too-many-arguments
_("Pivot operation must include at least one aggregate")
)
+ # Fail fast on ``show_values_as`` misconfiguration *before* running the
+ # (potentially expensive) ``pivot_table`` call: an unknown mode should
+ # not silently perform a full pivot only to raise at the end, and the
+ # ``marginal_distributions`` combination should reject before pandas
+ # gets a chance to raise its own margins-related errors. ``None`` /
+ # ``""`` / ``"actual"`` are the no-op sentinels — anything else must
+ # be a known percent mode.
+ percent_mode: Optional[str] = None
+ if show_values_as not in (None, "", "actual"):
+ if show_values_as not in _PERCENT_MODES:
+ raise InvalidPostProcessingError(
+ _(
+ "Unsupported show_values_as value: %(mode)s. "
+ "Expected one of: percent_row, percent_col, percent_total, actual.",
+ mode=show_values_as,
+ )
+ )
+ if marginal_distributions:
+ # The pivot would carry an "All" margin row and/or column;
+ # summing across the axis would double-count by including the
+ # margin as part of its own denominator. Combining ``margins``
+ # with ``show_values_as`` needs a first-class design (probably
+ # computing percentages on the non-margin subset and then
+ # re-inserting the margin totals as-is), which is out of scope
+ # here. Reject explicitly rather than silently returning wrong
+ # numbers.
+ raise InvalidPostProcessingError(
+ _(
+ "show_values_as is not yet supported when "
+ "marginal_distributions is enabled."
+ )
+ )
+ # ``show_values_as`` divides each cell by the sum of its axis, so
+ # it is only meaningful when that sum equals the rollup the DB
+ # would compute. For non-additive aggregates (mean, median, min,
+ # max, distinct count, …) the summed per-cell values are not the
+ # row/column/grand rollup, and the exports would disagree with
+ # the chart. Reject up front rather than emit numbers that mix
+ # with the DB rollup incorrectly.
+ non_additive = [
+ name
+ for name, cfg in aggregates.items()
+ if not isinstance(cfg.get("operator"), str)
+ or cfg["operator"] not in _ADDITIVE_OPERATORS
+ ]
+ if non_additive:
+ raise InvalidPostProcessingError(
+ _(
+ "show_values_as is only supported for additive aggregates "
+ "(sum, count); got non-additive operator(s) for: "
+ "%(metrics)s.",
+ metrics=", ".join(non_additive),
+ )
+ )
+ percent_mode = show_values_as
+
if columns and column_fill_value:
df[columns] = df[columns].fillna(value=column_fill_value)
@@ -163,6 +338,19 @@ def pivot( # pylint: disable=too-many-arguments
elif pivot_key_set and not df.empty:
df = df.drop(df.columns.difference(pivot_key_set), axis=PandasAxis.COLUMN)
+ # Apply the ``show_values_as`` percent transform BEFORE the
+ # ``combine_value_with_metric`` reshape, not after. The reshape below
+ # swaps the column ``MultiIndex`` level order from ``(metric, category)``
+ # to ``(category, metric)``; if the percent transform runs against the
+ # post-reshape shape, its per-metric iteration
+ # (``df.columns.get_level_values(0).unique()``) walks categories thinking
+ # they are metrics, mixing metrics and producing wrong percentages. See
+ # sadpandajoe's finding on #42976. Running percent first keeps the
+ # per-metric-isolation invariant intact; the reshape then runs on the
+ # already-normalized values without changing them further.
+ if percent_mode is not None:
+ df = _apply_show_values_as(df, percent_mode)
+
if combine_value_with_metric:
# dropna=False preserves restored all-NaN metric rows that would otherwise
# be silently dropped by stack's default dropna=True behavior.
diff --git a/tests/unit_tests/pandas_postprocessing/test_pivot.py b/tests/unit_tests/pandas_postprocessing/test_pivot.py
index 48743f62707..77767698e44 100644
--- a/tests/unit_tests/pandas_postprocessing/test_pivot.py
+++ b/tests/unit_tests/pandas_postprocessing/test_pivot.py
@@ -446,3 +446,428 @@ def test_pivot_only_entirely_absent_metrics_are_restored():
assert ("metric_partial", "A") in df.columns
assert ("metric_partial", "B") not in df.columns
assert df[("metric_partial", "A")].iloc[0] == 1.0
+
+
+# --- show_values_as regression tests (#42809) --------------------------------
+#
+# ``show_values_as`` expresses each metric cell as a fraction of the row,
+# column, or grand total after pivoting. Mirrors the client-side
+# ``fractionOf`` semantic in
+# ``plugin-chart-pivot-table/src/react-pivottable/utilities.ts:739`` so
+# server-side rendering paths (CSV / XLSX exports, scheduled reports)
+# match the browser output. See #42809.
+#
+# Fixture: a tiny 3-column DataFrame that keeps row/col/grand totals easy
+# to eyeball. Two rows (``r1``, ``r2``), two columns (``c1``, ``c2``),
+# single metric ``v``. Grand total is 100 so every percent-of-total
+# assertion is trivially checkable.
+
+
+def _show_values_as_fixture() -> DataFrame:
+ """Long-format input that pivots to::
+
+ v
+ col c1 c2
+ row
+ r1 10 20
+ r2 30 40
+
+ row totals: r1=30, r2=70; col totals: c1=40, c2=60; grand=100.
+ """
+ return DataFrame(
+ {
+ "row": ["r1", "r1", "r2", "r2"],
+ "col": ["c1", "c2", "c1", "c2"],
+ "v": [10, 20, 30, 40],
+ }
+ )
+
+
+def test_pivot_show_values_as_actual_is_noop() -> None:
+ """``show_values_as='actual'`` (and ``None``) leaves values unchanged."""
+ df = _show_values_as_fixture()
+ aggregates = {"v": {"operator": "sum"}}
+ baseline = pivot(df=df, index=["row"], columns=["col"], aggregates=aggregates)
+
+ for mode in (None, "actual"):
+ result = pivot(
+ df=df,
+ index=["row"],
+ columns=["col"],
+ aggregates=aggregates,
+ show_values_as=mode,
+ )
+ pd.testing.assert_frame_equal(result, baseline)
+
+
+def test_pivot_show_values_as_percent_row() -> None:
+ """Each cell = cell / row-total; each row sums to 1.0."""
+ result = pivot(
+ df=_show_values_as_fixture(),
+ index=["row"],
+ columns=["col"],
+ aggregates={"v": {"operator": "sum"}},
+ show_values_as="percent_row",
+ )
+ # r1: 10/30, 20/30; r2: 30/70, 40/70
+ assert result.loc["r1", ("v", "c1")] == pytest.approx(10 / 30)
+ assert result.loc["r1", ("v", "c2")] == pytest.approx(20 / 30)
+ assert result.loc["r2", ("v", "c1")] == pytest.approx(30 / 70)
+ assert result.loc["r2", ("v", "c2")] == pytest.approx(40 / 70)
+ assert result.sum(axis=1).tolist() == pytest.approx([1.0, 1.0])
+
+
+def test_pivot_show_values_as_percent_col() -> None:
+ """Each cell = cell / column-total; each column sums to 1.0."""
+ result = pivot(
+ df=_show_values_as_fixture(),
+ index=["row"],
+ columns=["col"],
+ aggregates={"v": {"operator": "sum"}},
+ show_values_as="percent_col",
+ )
+ # c1 total=40: 10/40, 30/40; c2 total=60: 20/60, 40/60
+ assert result.loc["r1", ("v", "c1")] == pytest.approx(10 / 40)
+ assert result.loc["r2", ("v", "c1")] == pytest.approx(30 / 40)
+ assert result.loc["r1", ("v", "c2")] == pytest.approx(20 / 60)
+ assert result.loc["r2", ("v", "c2")] == pytest.approx(40 / 60)
+ assert result.sum(axis=0).tolist() == pytest.approx([1.0, 1.0])
+
+
+def test_pivot_show_values_as_percent_total() -> None:
+ """Each cell = cell / grand-total; the whole frame sums to 1.0."""
+ result = pivot(
+ df=_show_values_as_fixture(),
+ index=["row"],
+ columns=["col"],
+ aggregates={"v": {"operator": "sum"}},
+ show_values_as="percent_total",
+ )
+ # grand=100: each cell divided by 100
+ assert result.loc["r1", ("v", "c1")] == pytest.approx(0.10)
+ assert result.loc["r1", ("v", "c2")] == pytest.approx(0.20)
+ assert result.loc["r2", ("v", "c1")] == pytest.approx(0.30)
+ assert result.loc["r2", ("v", "c2")] == pytest.approx(0.40)
+ assert result.values.sum() == pytest.approx(1.0)
+
+
+def test_pivot_show_values_as_preserves_nan_numerator() -> None:
+ """A NaN/NULL numerator stays NaN — matches the client-side #42810 guard
+ that a genuine SQL NULL should render blank, not "0.0%".
+
+ The fixture uses a **missing** (row, col) combination — ``r1`` has no
+ ``c2`` row — so ``pivot_table`` produces a genuine NaN cell for
+ (``r1``, ``c2``). Using a ``NaN`` *input value* with ``operator='sum'``
+ would not exercise this path because ``pandas`` ``.sum(skipna=True)``
+ on a single-value ``[NaN]`` group returns ``0.0``, not ``NaN``.
+ """
+ df = DataFrame(
+ {
+ "row": ["r1", "r2", "r2"], # r1 has no c2 row → post-pivot NaN
+ "col": ["c1", "c1", "c2"],
+ "v": [10, 30, 40],
+ }
+ )
+ result = pivot(
+ df=df,
+ index=["row"],
+ columns=["col"],
+ aggregates={"v": {"operator": "sum"}},
+ show_values_as="percent_row",
+ )
+ # The genuinely-NaN cell stays NaN through the percent transform.
+ assert pd.isna(result.loc["r1", ("v", "c2")])
+ # The other cell in the same row divides correctly against just its
+ # own value (row total is 10 since c2 is NaN and skipna=True).
+ assert result.loc["r1", ("v", "c1")] == pytest.approx(1.0)
+
+
+def test_pivot_show_values_as_percent_total_zero_grand_total_yields_nan() -> None:
+ """Grand total of zero yields NaN cells rather than Infinity — matches the
+ client's ``if (acc === null) return null`` division-by-zero guard."""
+ df = DataFrame(
+ {
+ "row": ["r1", "r1", "r2", "r2"],
+ "col": ["c1", "c2", "c1", "c2"],
+ "v": [0, 0, 0, 0],
+ }
+ )
+ result = pivot(
+ df=df,
+ index=["row"],
+ columns=["col"],
+ aggregates={"v": {"operator": "sum"}},
+ show_values_as="percent_total",
+ )
+ # No cell should be Infinity or a real number; all should be NaN.
+ assert result.isna().values.all()
+
+
+def test_pivot_show_values_as_percent_row_multi_metric_keeps_metrics_separate() -> None:
+ """On a multi-metric pivot (``MultiIndex`` columns), per-row totals are
+ computed *within each metric*. Metric A's percentages must sum to 1.0
+ per row independent of metric B's values."""
+ df = DataFrame(
+ {
+ "row": ["r1", "r1", "r2", "r2"],
+ "col": ["c1", "c2", "c1", "c2"],
+ "a": [10, 20, 30, 40],
+ "b": [1, 3, 5, 7],
+ }
+ )
+ result = pivot(
+ df=df,
+ index=["row"],
+ columns=["col"],
+ aggregates={"a": {"operator": "sum"}, "b": {"operator": "sum"}},
+ show_values_as="percent_row",
+ )
+ # Metric ``a``: row totals 30 and 70; each row of ``a`` sums to 1.
+ assert result["a"].sum(axis=1).tolist() == pytest.approx([1.0, 1.0])
+ # Metric ``b``: row totals 4 and 12; each row of ``b`` sums to 1.
+ assert result["b"].sum(axis=1).tolist() == pytest.approx([1.0, 1.0])
+ # Metric ``a`` percentages must not be contaminated by metric ``b`` values.
+ assert result.loc["r1", ("a", "c1")] == pytest.approx(10 / 30)
+ assert result.loc["r1", ("b", "c1")] == pytest.approx(1 / 4)
+
+
+def test_pivot_show_values_as_invalid_mode_raises() -> None:
+ """An unknown ``show_values_as`` value raises ``InvalidPostProcessingError``
+ rather than silently falling through to a no-op."""
+ with pytest.raises(InvalidPostProcessingError):
+ pivot(
+ df=_show_values_as_fixture(),
+ index=["row"],
+ columns=["col"],
+ aggregates={"v": {"operator": "sum"}},
+ show_values_as="percent_of_moon",
+ )
+
+
+def test_pivot_show_values_as_empty_string_is_noop() -> None:
+ """Empty-string ``show_values_as`` is treated as a no-op alongside
+ ``None`` and ``"actual"`` — it must NOT reach the percent-mode
+ validator (which would raise on it) or silently divide.
+ """
+ df = _show_values_as_fixture()
+ aggregates = {"v": {"operator": "sum"}}
+ baseline = pivot(df=df, index=["row"], columns=["col"], aggregates=aggregates)
+
+ result = pivot(
+ df=df,
+ index=["row"],
+ columns=["col"],
+ aggregates=aggregates,
+ show_values_as="",
+ )
+ pd.testing.assert_frame_equal(result, baseline)
+
+
+def test_pivot_show_values_as_percent_total_flat_multi_metric() -> None:
+ """A multi-metric pivot with **no** ``columns`` groupby produces a
+ **flat** column index — each column IS its own metric. ``percent_total``
+ must divide each metric column by its OWN grand total (never mixing
+ metrics), otherwise one metric's magnitude changes another metric's
+ percentages.
+ """
+ df = DataFrame({"row": ["r1", "r2"], "a": [10, 30], "b": [1, 3]})
+ result = pivot(
+ df=df,
+ index=["row"],
+ aggregates={"a": {"operator": "sum"}, "b": {"operator": "sum"}},
+ show_values_as="percent_total",
+ )
+ # Each metric column sums to 1.0 independently.
+ assert result["a"].sum() == pytest.approx(1.0)
+ assert result["b"].sum() == pytest.approx(1.0)
+ # And metric a's magnitude (10, 30 → grand 40) doesn't leak into
+ # metric b's percentages (which use grand 4).
+ assert result.loc["r1", "a"] == pytest.approx(10 / 40)
+ assert result.loc["r1", "b"] == pytest.approx(1 / 4)
+
+
+def test_pivot_show_values_as_percent_row_zero_row_total_yields_nan() -> None:
+ """A row whose values sum to zero yields NaN cells in that row rather
+ than ``Infinity``/``NaN`` from division-by-zero. Other rows still
+ divide correctly.
+ """
+ df = DataFrame(
+ {
+ "row": ["r1", "r1", "r2", "r2"],
+ "col": ["c1", "c2", "c1", "c2"],
+ "v": [0, 0, 30, 40], # r1's row-total is 0
+ }
+ )
+ result = pivot(
+ df=df,
+ index=["row"],
+ columns=["col"],
+ aggregates={"v": {"operator": "sum"}},
+ show_values_as="percent_row",
+ )
+ assert pd.isna(result.loc["r1", ("v", "c1")])
+ assert pd.isna(result.loc["r1", ("v", "c2")])
+ # r2 still divides correctly against its own row-total (70).
+ assert result.loc["r2", ("v", "c1")] == pytest.approx(30 / 70)
+
+
+def test_pivot_show_values_as_percent_col_zero_col_total_yields_nan() -> None:
+ """A column whose values sum to zero yields NaN cells in that column
+ rather than ``Infinity``/``NaN`` from division-by-zero. Other columns
+ still divide correctly.
+ """
+ df = DataFrame(
+ {
+ "row": ["r1", "r1", "r2", "r2"],
+ "col": ["c1", "c2", "c1", "c2"],
+ "v": [0, 20, 0, 40], # c1's column-total is 0
+ }
+ )
+ result = pivot(
+ df=df,
+ index=["row"],
+ columns=["col"],
+ aggregates={"v": {"operator": "sum"}},
+ show_values_as="percent_col",
+ )
+ assert pd.isna(result.loc["r1", ("v", "c1")])
+ assert pd.isna(result.loc["r2", ("v", "c1")])
+ # c2 still divides correctly against its own column-total (60).
+ assert result.loc["r1", ("v", "c2")] == pytest.approx(20 / 60)
+
+
+def test_pivot_show_values_as_with_marginal_distributions_raises() -> None:
+ """``show_values_as`` combined with ``marginal_distributions`` would
+ include the ``All`` margin row/column in the row/column/grand-total
+ denominators, producing wrong percentages. Combining the two needs a
+ first-class design; for now the combination raises loudly rather than
+ silently returning wrong numbers.
+ """
+ with pytest.raises(InvalidPostProcessingError, match="marginal_distributions"):
+ pivot(
+ df=_show_values_as_fixture(),
+ index=["row"],
+ columns=["col"],
+ aggregates={"v": {"operator": "sum"}},
+ marginal_distributions=True,
+ show_values_as="percent_row",
+ )
+
+
+def test_pivot_show_values_as_with_combine_value_with_metric_preserves_per_metric() -> (
+ None
+):
+ """Regression test for sadpandajoe's finding on #42976.
+
+ ``combine_value_with_metric`` reshapes the column ``MultiIndex`` from
+ ``(metric, category)`` to ``(category, metric)``. Historically the
+ ``show_values_as`` transform ran *after* this reshape, so its per-metric
+ iteration walked categories thinking they were metrics — mixing metric
+ magnitudes and producing wrong percentages (e.g. metric ``a``'s row
+ would sum to ~1.78 instead of 1.0 because metric ``b``'s values leaked
+ into ``a``'s denominators).
+
+ The transform now runs *before* the reshape so per-metric isolation
+ stays intact regardless of the final column layout.
+ """
+ df = DataFrame(
+ {
+ "row": ["r1", "r1", "r2", "r2"],
+ "col": ["c1", "c2", "c1", "c2"],
+ "a": [10, 20, 30, 40],
+ "b": [1, 3, 5, 7],
+ }
+ )
+ result = pivot(
+ df=df,
+ index=["row"],
+ columns=["col"],
+ aggregates={"a": {"operator": "sum"}, "b": {"operator": "sum"}},
+ combine_value_with_metric=True,
+ show_values_as="percent_row",
+ )
+
+ # After combine_value_with_metric, the column MultiIndex is
+ # ``(category, metric)``. Per-metric row sums are pulled via cross-section
+ # on level 1 (the metric axis).
+ for metric, expected in (("a", [1.0, 1.0]), ("b", [1.0, 1.0])):
+ per_metric = result.xs(metric, axis=1, level=1)
+ assert per_metric.sum(axis=1).tolist() == pytest.approx(expected), (
+ f"metric {metric!r} rows must each sum to 1.0 after "
+ "percent_row on a combined pivot; got contamination from "
+ "other metrics"
+ )
+
+ # And the actual values match the natural per-metric percentages,
+ # not the mixed-metric ones that the bug produced.
+ assert result.loc["r1", ("c1", "a")] == pytest.approx(10 / 30)
+ assert result.loc["r1", ("c1", "b")] == pytest.approx(1 / 4)
+
+
+def test_pivot_show_values_as_rejects_non_additive_aggregate() -> None:
+ """``show_values_as`` requires additive aggregates.
+
+ For a ``mean`` aggregate, the summed per-cell values are not the
+ row/column/grand rollup the DB would compute over the underlying
+ rows, so ``cell / sum(cells)`` disagrees with the "share of the
+ real row total" the chart shows. Reject up front rather than emit
+ numbers that mix with the DB rollup incorrectly.
+ """
+ with pytest.raises(InvalidPostProcessingError, match="additive"):
+ pivot(
+ df=_show_values_as_fixture(),
+ index=["row"],
+ columns=["col"],
+ aggregates={"v": {"operator": "mean"}},
+ show_values_as="percent_row",
+ )
+
+
+def test_pivot_show_values_as_on_empty_pivot_returns_empty_frame() -> None:
+ """Empty inputs must not crash the percent transform.
+
+ An empty pivot with a column grouping has a ``MultiIndex`` with zero
+ level-0 groups; the metric-iteration loop then feeds ``pd.concat``
+ an empty list and raises ``ValueError: No objects to concatenate``.
+ The empty frame should pass through unchanged.
+ """
+ empty = DataFrame({"row": [], "col": [], "v": []}).astype(
+ {"row": str, "col": str, "v": float}
+ )
+ result = pivot(
+ df=empty,
+ index=["row"],
+ columns=["col"],
+ aggregates={"v": {"operator": "sum"}},
+ show_values_as="percent_row",
+ )
+ assert result.empty
+
+
+def test_pivot_show_values_as_preserves_structural_nan() -> None:
+ """Structurally-missing cells (no input rows for that (row, col)) stay NaN.
+
+ NULL preservation is scoped to the structural case: cells that
+ ``pivot_table`` left as ``NaN`` because no input row exists for that
+ (row, column) group must render as blank (``NaN``), not as ``0%``.
+ Value-is-NULL cells are a separate case documented on
+ ``_apply_show_values_as``.
+ """
+ df = DataFrame(
+ {
+ "row": ["r1", "r2", "r2"],
+ "col": ["c1", "c1", "c2"],
+ "v": [10.0, 30.0, 40.0],
+ }
+ )
+ result = pivot(
+ df=df,
+ index=["row"],
+ columns=["col"],
+ aggregates={"v": {"operator": "sum"}},
+ show_values_as="percent_row",
+ )
+ # r1 has no c2 row → cell is structurally missing → stays NaN.
+ assert pd.isna(result.loc["r1", ("v", "c2")])
+ # r1's row-total is just c1 (10.0), so c1 is 100%.
+ assert result.loc["r1", ("v", "c1")] == pytest.approx(1.0)