Repository: pandas-dev/pandas
Pull request: BUG: Dataframe.groupby aggregations with categorical columns lead to incorrect results.
- [x] closes #32494 
- [x] tests added / passed
- [x] passes `black pandas`
- [x] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [x] whatsnew entry


Problem statement:
Dataframe.groupby aggregations with categorical columns lead to incorrect results.
#### Code Sample

```python
# In[2]:

import pandas as pd

def create_df():
    df = pd.DataFrame(
        {
            'major_id': [1, 2, 1, 2, 2],
            'minor_id': ['a', 'b', 'c', 'd', 'e'],
            'values': [1, 2, 3, 4, 5]
        }
    )
    return df

def groupby(df):
    df['max_value'] = (
        df
        .groupby(['major_id', 'minor_id'])
        ['values']
        .transform('max')
    )
    
    return df


# In[3]:

# correct result
df = create_df()
groupby(df)

# Out[3]
       "   major_id minor_id  values  max_value\n",
       "0         1        a       1          1\n",
       "1         2        b       2          2\n",
       "2         1        c       3          3\n",
       "3         2        d       4          4\n",
       "4         2        e       5          5"

# In[4]:

# incorrect result: groupby with one non-categorical column and one categorical column
df = create_df()
df = df.astype({'minor_id': 'category'})
groupby(df)

# Out[4]

       "   major_id minor_id  values  max_value\n",
       "0         1        a       1        1.0\n",
       "1         2        b       2        3.0\n",
       "2         1        c       3        NaN\n",
       "3         2        d       4        NaN\n",
       "4         2        e       5        NaN"
```
#### Problem description

`groupby` with one non-categorical column and one categorical column leads to incorrect aggregations (wrong values, or `NAN`s).

#### Output of ``pd.show_versions()``

<details>

INSTALLED VERSIONS
------------------
commit           : None
python           : 3.7.4.final.0
python-bits      : 64
OS               : Darwin
OS-release       : 18.7.0
machine          : x86_64
processor        : i386
byteorder        : little
LC_ALL           : None
LANG             : en_GB.UTF-8
LOCALE           : en_GB.UTF-8

pandas           : 1.0.1
numpy            : 1.17.3
pytz             : 2019.3
dateutil         : 2.8.0
pip              : 19.2.2
setuptools       : 41.0.1
Cython           : None
pytest           : 5.2.1
hypothesis       : 5.5.4
sphinx           : None
blosc            : None
feather          : None
xlsxwriter       : None
lxml.etree       : None
html5lib         : None
pymysql          : None
psycopg2         : 2.8.4 (dt dec pq3 ext lo64)
jinja2           : 2.10.1
IPython          : 7.7.0
pandas_datareader: None
bs4              : None
bottleneck       : None
fastparquet      : 0.3.3
gcsfs            : None
lxml.etree       : None
matplotlib       : 3.1.1
numexpr          : None
odfpy            : None
openpyxl         : None
pandas_gbq       : None
pyarrow          : 0.16.0
pytables         : None
pytest           : 5.2.1
pyxlsb           : None
s3fs             : 0.4.0
scipy            : 1.3.1
sqlalchemy       : 1.3.10
tables           : None
tabulate         : 0.8.5
xarray           : None
xlrd             : None
xlwt             : None
xlsxwriter       : None
numba            : 0.48.0
</details>

Hints from issue discussion:
Thanks for the report. In a transform, the output shape will match the input shape. But Categorical are special since they expand to include unobserved categories in the output.

This may be similar to https://github.com/pandas-dev/pandas/issues/28788, but that issue is hard to follow.
A bit simpler reproducer:

```python
In [25]: df = pd.DataFrame({"A": pd.Categorical(['a', 'b', 'a'], categories=['a', 'b', 'c']), "B": [1, 2, 3], "C": ['a', 'b', 'a']})

In [26]: df.groupby(['A', 'C']).transform('sum')
Out[26]:
     B
0  4.0
1  NaN
2  4.0
```

Commit message: is_categorical -> is_categorical_dtype

Patch to review:
diff --git a/doc/source/whatsnew/v1.1.0.rst b/doc/source/whatsnew/v1.1.0.rst
index 95cb4ccbbb796..e236ed369d093 100644
--- a/doc/source/whatsnew/v1.1.0.rst
+++ b/doc/source/whatsnew/v1.1.0.rst
@@ -715,6 +715,7 @@ Groupby/resample/rolling
 
 - Bug in :meth:`GroupBy.apply` raises ``ValueError`` when the ``by`` axis is not sorted and has duplicates and the applied ``func`` does not mutate passed in objects (:issue:`30667`)
 - Bug in :meth:`DataFrameGroupby.transform` produces incorrect result with transformation functions (:issue:`30918`)
+- Bug in :meth:`Groupby.transform` was returning the wrong result when grouping by multiple keys of which some were categorical and others not (:issue:`32494`)
 - Bug in :meth:`GroupBy.count` causes segmentation fault when grouped-by column contains NaNs (:issue:`32841`)
 - Bug in :meth:`DataFrame.groupby` and :meth:`Series.groupby` produces inconsistent type when aggregating Boolean series (:issue:`32894`)
 - Bug in :meth:`SeriesGroupBy.quantile` raising on nullable integers (:issue:`33136`)
diff --git a/pandas/core/groupby/generic.py b/pandas/core/groupby/generic.py
index 438babda9ff7a..53aa6d81d4290 100644
--- a/pandas/core/groupby/generic.py
+++ b/pandas/core/groupby/generic.py
@@ -44,6 +44,7 @@
     ensure_int64,
     ensure_platform_int,
     is_bool,
+    is_categorical_dtype,
     is_integer_dtype,
     is_interval_dtype,
     is_numeric_dtype,
@@ -539,6 +540,11 @@ def _transform_fast(self, result, func_nm: str) -> Series:
         builtin/cythonizable functions
         """
         ids, _, ngroup = self.grouper.group_info
+
+        # in categorical case there may be unobserved categories in index
+        if any(is_categorical_dtype(ping.grouper) for ping in self.grouper.groupings):
+            result = result.reindex(self.grouper.result_index)
+
         cast = self._transform_should_cast(func_nm)
         out = algorithms.take_1d(result._values, ids)
         if cast:
@@ -1475,6 +1481,11 @@ def _transform_fast(self, result: DataFrame, func_nm: str) -> DataFrame:
         # for each col, reshape to to size of original frame
         # by take operation
         ids, _, ngroup = self.grouper.group_info
+
+        # in categorical case there may be unobserved categories in index
+        if any(is_categorical_dtype(ping.grouper) for ping in self.grouper.groupings):
+            result = result.reindex(self.grouper.result_index)
+
         output = []
         for i, _ in enumerate(result.columns):
             res = algorithms.take_1d(result.iloc[:, i].values, ids)
diff --git a/pandas/tests/groupby/transform/test_transform.py b/pandas/tests/groupby/transform/test_transform.py
index e1042bf35acc4..24a0e7acad632 100644
--- a/pandas/tests/groupby/transform/test_transform.py
+++ b/pandas/tests/groupby/transform/test_transform.py
@@ -1195,3 +1195,27 @@ def test_transform_lambda_indexing():
         ),
     )
     tm.assert_frame_equal(result, expected)
+
+
+def test_categorical_and_not_categorical_key():
+    # GH 32494
+    df_with_categorical = pd.DataFrame(
+        {
+            "A": pd.Categorical(["a", "b", "a"], categories=["a", "b", "c"]),
+            "B": [1, 2, 3],
+            "C": ["a", "b", "a"],
+        }
+    )
+    df_without_categorical = pd.DataFrame(
+        {"A": ["a", "b", "a"], "B": [1, 2, 3], "C": ["a", "b", "a"]}
+    )
+
+    # DataFrame case
+    result = df_with_categorical.groupby(["A", "C"]).transform("sum")
+    expected = df_without_categorical.groupby(["A", "C"]).transform("sum")
+    tm.assert_frame_equal(result, expected)
+
+    # Series case
+    result = df_with_categorical.groupby(["A", "C"])["B"].transform("sum")
+    expected = df_without_categorical.groupby(["A", "C"])["B"].transform("sum")
+    tm.assert_series_equal(result, expected)


Code review comments:
diff_hunk: @@ -1475,6 +1481,11 @@ def _transform_fast(self, result: DataFrame, func_nm: str) -> DataFrame:
         # for each col, reshape to to size of original frame
         # by take operation
         ids, _, ngroup = self.grouper.group_info
+
+        # in categorical case there may be unobserved categories in index
path: pandas/core/groupby/generic.py
text: @user1:
does this depend on if observed=True is passed?

@author:
It's unnecessary if `observed` is True - have taken care of that now, thanks

@user1:
can we *always just do
``result = result.reindex(self.groupber.result_index, copy=False)`` ?

what breaks if we do that

@author:
@user1 that's fine, we can do that always, I just thought it would be better not to if it wasn't necessary. Green now