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:48:59 UTC
(superset) branch elizabeth/fix-pandas-pivot-null created (now fea72c51a1)
This is an automated email from the ASF dual-hosted git repository.
elizabeth pushed a change to branch elizabeth/fix-pandas-pivot-null
in repository https://gitbox.apache.org/repos/asf/superset.git
at fea72c51a1 fix pandas pivot null values
This branch includes the following new commits:
new fea72c51a1 fix pandas pivot null values
The 1 revisions listed above as "new" are entirely new to this
repository and will be described in separate emails. The revisions
listed as "add" were already present in the repository and have only
been added to this reference.
(superset) 01/01: fix pandas pivot null values
Posted by el...@apache.org.
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.