You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@superset.apache.org by el...@apache.org on 2024/08/08 23:49:00 UTC
(superset) 01/01: fix pandas pivot null values
This is an automated email from the ASF dual-hosted git repository.
elizabeth pushed a commit to branch elizabeth/fix-pandas-pivot-null
in repository https://gitbox.apache.org/repos/asf/superset.git
commit fea72c51a19850b8529bb7a31e0c82e2491e211c
Author: Elizabeth Thompson <es...@gmail.com>
AuthorDate: Thu Aug 8 16:48:31 2024 -0700
fix pandas pivot null values
---
superset/charts/post_processing.py | 5 +-
tests/unit_tests/charts/test_post_processing.py | 170 ++++++++++++++++++++++++
2 files changed, 174 insertions(+), 1 deletion(-)
diff --git a/superset/charts/post_processing.py b/superset/charts/post_processing.py
index ebcae32f8f..42a8c56445 100644
--- a/superset/charts/post_processing.py
+++ b/superset/charts/post_processing.py
@@ -29,6 +29,7 @@ for these chart types.
from io import StringIO
from typing import Any, Optional, TYPE_CHECKING, Union
+import numpy as np
import pandas as pd
from flask_babel import gettext as __
@@ -150,6 +151,8 @@ def pivot_df( # pylint: disable=too-many-locals, too-many-arguments, too-many-s
if show_rows_total:
# add subtotal for each group and overall total; we start from the
# overall group, and iterate deeper into subgroups
+ # Ensure "NULL" strings are replaced with NaN
+ df.replace("NULL", np.nan, inplace=True)
groups = df.columns
for level in range(df.columns.nlevels):
subgroups = {group[:level] for group in groups}
@@ -171,7 +174,7 @@ def pivot_df( # pylint: disable=too-many-locals, too-many-arguments, too-many-s
for subgroup in subgroups:
slice_ = df.index.get_loc(subgroup)
subtotal = pivot_v2_aggfunc_map[aggfunc](
- df.iloc[slice_, :].apply(pd.to_numeric), axis=0
+ df.iloc[slice_, :].apply(pd.to_numeric, errors="coerce"), axis=0
)
depth = df.index.nlevels - len(subgroup) - 1
total = metric_name if level == 0 else __("Subtotal")
diff --git a/tests/unit_tests/charts/test_post_processing.py b/tests/unit_tests/charts/test_post_processing.py
index 790c494516..df724b0a14 100644
--- a/tests/unit_tests/charts/test_post_processing.py
+++ b/tests/unit_tests/charts/test_post_processing.py
@@ -407,6 +407,176 @@ def test_pivot_df_single_row_two_metrics():
)
+def test_pivot_df_single_row_null_values():
+ """
+ Pivot table when a single column and 2 metrics are selected.
+ """
+ df = pd.DataFrame.from_dict(
+ {
+ "gender": {0: "girl", 1: "boy"},
+ "SUM(num)": {0: 118065, 1: None},
+ "MAX(num)": {0: 2588, 1: None},
+ }
+ )
+ assert (
+ df.to_markdown()
+ == """
+| | gender | SUM(num) | MAX(num) |
+|---:|:---------|-----------:|-----------:|
+| 0 | girl | 118065 | 2588 |
+| 1 | boy | nan | nan |
+ """.strip()
+ )
+
+ pivoted = pivot_df(
+ df,
+ rows=["gender"],
+ columns=[],
+ metrics=["SUM(num)", "MAX(num)"],
+ aggfunc="Sum",
+ transpose_pivot=False,
+ combine_metrics=False,
+ show_rows_total=False,
+ show_columns_total=False,
+ apply_metrics_on_rows=False,
+ )
+ assert (
+ pivoted.to_markdown()
+ == """
+| | ('SUM(num)',) | ('MAX(num)',) |
+|:----------|:----------------|:----------------|
+| ('boy',) | NULL | NULL |
+| ('girl',) | 118065.0 | 2588.0 |
+ """.strip()
+ )
+
+ # transpose_pivot
+ pivoted = pivot_df(
+ df,
+ rows=["gender"],
+ columns=[],
+ metrics=["SUM(num)", "MAX(num)"],
+ aggfunc="Sum",
+ transpose_pivot=True,
+ combine_metrics=False,
+ show_rows_total=False,
+ show_columns_total=False,
+ apply_metrics_on_rows=False,
+ )
+ assert (
+ pivoted.to_markdown()
+ == """
+| | ('SUM(num)', 'boy') | ('SUM(num)', 'girl') | ('MAX(num)', 'boy') | ('MAX(num)', 'girl') |
+|:-----------------|:----------------------|-----------------------:|:----------------------|-----------------------:|
+| ('Total (Sum)',) | NULL | 118065 | NULL | 2588 |
+
+ """.strip()
+ )
+
+ # combine_metrics does nothing in this case
+ pivoted = pivot_df(
+ df,
+ rows=["gender"],
+ columns=[],
+ metrics=["SUM(num)", "MAX(num)"],
+ aggfunc="Sum",
+ transpose_pivot=False,
+ combine_metrics=True,
+ show_rows_total=False,
+ show_columns_total=False,
+ apply_metrics_on_rows=False,
+ )
+ assert (
+ pivoted.to_markdown()
+ == """
+| | ('SUM(num)',) | ('MAX(num)',) |
+|:----------|:----------------|:----------------|
+| ('boy',) | NULL | NULL |
+| ('girl',) | 118065.0 | 2588.0 |
+ """.strip()
+ )
+
+ # show totals
+ pivoted = pivot_df(
+ df,
+ rows=["gender"],
+ columns=[],
+ metrics=["SUM(num)", "MAX(num)"],
+ aggfunc="Sum",
+ transpose_pivot=False,
+ combine_metrics=False,
+ show_rows_total=True,
+ show_columns_total=True,
+ apply_metrics_on_rows=False,
+ )
+ assert (
+ pivoted.to_markdown()
+ == """
+| | ('SUM(num)',) | ('MAX(num)',) | ('Total (Sum)',) |
+|:-----------------|----------------:|----------------:|-------------------:|
+| ('boy',) | nan | nan | 0 |
+| ('girl',) | 118065 | 2588 | 120653 |
+| ('Total (Sum)',) | 118065 | 2588 | 120653 |
+ """.strip()
+ )
+
+ # apply_metrics_on_rows
+ pivoted = pivot_df(
+ df,
+ rows=["gender"],
+ columns=[],
+ metrics=["SUM(num)", "MAX(num)"],
+ aggfunc="Sum",
+ transpose_pivot=False,
+ combine_metrics=False,
+ show_rows_total=True,
+ show_columns_total=True,
+ apply_metrics_on_rows=True,
+ )
+ assert (
+ pivoted.to_markdown()
+ == f"""
+| | ('{_("Total")} (Sum)',) |
+|:-------------------------|-------------------:|
+| ('SUM(num)', 'boy') | nan |
+| ('SUM(num)', 'girl') | 118065 |
+| ('SUM(num)', 'Subtotal') | 118065 |
+| ('MAX(num)', 'boy') | nan |
+| ('MAX(num)', 'girl') | 2588 |
+| ('MAX(num)', 'Subtotal') | 2588 |
+| ('{_("Total")} (Sum)', '') | 120653 |
+ """.strip()
+ )
+
+ # apply_metrics_on_rows with combine_metrics
+ pivoted = pivot_df(
+ df,
+ rows=["gender"],
+ columns=[],
+ metrics=["SUM(num)", "MAX(num)"],
+ aggfunc="Sum",
+ transpose_pivot=False,
+ combine_metrics=True,
+ show_rows_total=True,
+ show_columns_total=True,
+ apply_metrics_on_rows=True,
+ )
+ assert (
+ pivoted.to_markdown()
+ == f"""
+| | ('{_("Total")} (Sum)',) |
+|:---------------------|-------------------:|
+| ('boy', 'SUM(num)') | nan |
+| ('boy', 'MAX(num)') | nan |
+| ('boy', 'Subtotal') | 0 |
+| ('girl', 'SUM(num)') | 118065 |
+| ('girl', 'MAX(num)') | 2588 |
+| ('girl', 'Subtotal') | 120653 |
+| ('{_("Total")} (Sum)', '') | 120653 |
+ """.strip()
+ )
+
+
def test_pivot_df_complex():
"""
Pivot table when a column, rows and 2 metrics are selected.