Repository: pandas-dev/pandas
Pull request: BUG: Series groupby does not include nan counts for all categorical labels (#17605)
- [x] closes #17605
- [x] tests added / passed
- [x] passes `black pandas`
- [x] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [x] whatsnew entry

This is a simple, low-impact fix for #17605.

Problem statement:
Series groupby does not include zero or nan counts for all categorical labels, unlike DataFrame groupby
#### Steps to reproduce

```
In [1]: import pandas

In [2]: df = pandas.DataFrame({'type': pandas.Categorical(['AAA', 'AAA', 'B', 'C']),
   ...:                        'voltage': pandas.Series([1.5, 1.5, 1.5, 1.5]),
   ...:                        'treatment': pandas.Categorical(['T', 'C', 'T', 'C'])})

In [3]: df.groupby(['treatment', 'type']).count()
Out[3]:
                voltage
treatment type
C         AAA       1.0
          B         NaN
          C         1.0
T         AAA       1.0
          B         1.0
          C         NaN

In [4]: df.groupby(['treatment', 'type'])['voltage'].count()
Out[4]:
treatment  type
C          AAA     1
           C       1
T          AAA     1
           B       1
Name: voltage, dtype: int64
```

#### Problem description
When performing a groupby on categorical columns, categories with empty groups should be present in output.  That is, the multi-index of the object returned by `count()` should contain the Cartesian product of all the labels of the first categorical column (`"treatment"` in the example above) and the second categorical column (`"type"`) by which the grouping was performed.

The behavior in cell [3] above is correct.  But in cell [4], after obtaining a `pandas.core.groupby.SeriesGroupBy` object, the series returned by the `count()` method does not have entries for all levels of the `"type"` categorical.

#### Expected Output
The output from cell [4] should be equivalent to this output, with length 6, and include values for the index values `(C, B)` and `(T, C)`.
```
In [5]: df.groupby(['treatment', 'type']).count().squeeze()
Out[5]:
treatment  type
C          AAA     1.0
           B       NaN
           C       1.0
T          AAA     1.0
           B       1.0
           C       NaN
Name: voltage, dtype: float64
```

#### Workaround

Perform column access after calling `count()`:
```
In [7]: df.groupby(['treatment', 'type']).count()['voltage']
Out[7]:
treatment  type
C          AAA     1.0
           B       NaN
           C       1.0
T          AAA     1.0
           B       1.0
           C       NaN
Name: voltage, dtype: float64
```

#### Output of ``pd.show_versions()``

<details>

```
INSTALLED VERSIONS
------------------
commit: None
python: 3.4.5.final.0
python-bits: 64
OS: Windows
OS-release: 7
machine: AMD64
processor: Intel64 Family 6 Model 60 Stepping 3, GenuineIntel
byteorder: little
LC_ALL: None
LANG: en_US.UTF-8
LOCALE: None.None

pandas: 0.19.1
nose: 1.3.7
pip: 9.0.1
setuptools: 27.2.0
Cython: 0.24.1
numpy: 1.11.2
scipy: 0.18.1
statsmodels: 0.6.1
xarray: 0.8.2
IPython: 5.1.0
sphinx: 1.4.8
patsy: 0.4.1
dateutil: 2.6.0
pytz: 2016.7
blosc: 1.5.0
bottleneck: 1.2.0
tables: 3.2.2
numexpr: 2.6.1
matplotlib: 1.5.1
openpyxl: 2.4.0
xlrd: 1.0.0
xlwt: 1.1.2
xlsxwriter: 0.9.3
lxml: 3.6.4
bs4: 4.5.3
html5lib: 0.999
httplib2: 0.9.2
apiclient: None
sqlalchemy: 1.1.3
pymysql: None
psycopg2: 2.6.2 (dt dec pq3 ext lo64)
jinja2: 2.8
boto: 2.43.0
pandas_datareader: None
```

</details>

Hints from issue discussion:
Worth noting that `.size()` also drops the 0-counts for both Series and GroupBy. I know `size` and `count` are different but I would expect them to make the same indexing decision here.
The desired output is the Series one. You definitely don't want the cartesian product of your groupby cols. The dataframe one is wrong or at least I can't see why you would want that as the default behaviour. You will explode memory. 
  
And to be more constructive, I would imagine in your use case you want some sort of reindex first using something like this stuff:

https://pandas.pydata.org/pandas-docs/stable/generated/pandas.MultiIndex.from_product.html
This issue was created before the introduction of the `observed` keyword in `groupby()`, but it seems to me that with the default of `observed=False` all categories should be present in the output as @nmusolino expects. 

On first sight this is true for most aggregations, but `count()` seems to be an exception:

```python
pdf = pd.DataFrame({
    "category_1": pd.Categorical(list("AABBCC"), categories=list("ABCDEF")),
    "category_2": pd.Categorical(list("ABC") * 2, categories=list("ABCDEF")),
    "value": [0.1] * 6
})

pdf.groupby(["category_1", "category_2"])["value"].sum()  # All categories present
pdf.groupby(["category_1", "category_2"])["value"].mean()  # All categories present
pdf.groupby(["category_1", "category_2"])["value"].min()  # All categories present
pdf.groupby(["category_1", "category_2"])["value"].count()  # Only observed present!
``` 

So I do think this is a bug that's still present.

Commit message: DOC: Added whatsnew entry.

Patch to review:
diff --git a/doc/source/whatsnew/v1.0.0.rst b/doc/source/whatsnew/v1.0.0.rst
index cb68bd0e762c4..b95562da7b647 100644
--- a/doc/source/whatsnew/v1.0.0.rst
+++ b/doc/source/whatsnew/v1.0.0.rst
@@ -438,6 +438,7 @@ Groupby/resample/rolling
 - Bug in :meth:`DataFrameGroupby.agg` not able to use lambda function with named aggregation (:issue:`27519`)
 - Bug in :meth:`DataFrame.groupby` losing column name information when grouping by a categorical column (:issue:`28787`)
 - Bug in :meth:`DataFrameGroupBy.rolling().quantile()` ignoring ``interpolation`` keyword argument (:issue:`28779`)
+- Bug in :meth:`SeriesGroupBy.count` missing unobserved categories when ``observed=False`` (:issue:`17605`)
 
 Reshaping
 ^^^^^^^^^
diff --git a/pandas/core/groupby/generic.py b/pandas/core/groupby/generic.py
index 6376dbefcf435..903ba447c910c 100644
--- a/pandas/core/groupby/generic.py
+++ b/pandas/core/groupby/generic.py
@@ -721,12 +721,13 @@ def count(self) -> Series:
         minlength = ngroups or 0
         out = np.bincount(ids[mask], minlength=minlength)
 
-        return Series(
+        result = Series(
             out,
             index=self.grouper.result_index,
             name=self._selection_name,
             dtype="int64",
         )
+        return self._reindex_output(result)
 
     def _apply_to_column_groupbys(self, func):
         """ return a pass thru """
diff --git a/pandas/tests/groupby/test_categorical.py b/pandas/tests/groupby/test_categorical.py
index 663e03aa1bc81..8931a42bd8fdd 100644
--- a/pandas/tests/groupby/test_categorical.py
+++ b/pandas/tests/groupby/test_categorical.py
@@ -1252,3 +1252,29 @@ def test_get_nonexistent_category():
                 {"var": [rows.iloc[-1]["var"]], "val": [rows.iloc[-1]["vau"]]}
             )
         )
+
+
+@pytest.mark.parametrize("aggregation", ["sum", "mean", "min", "count"])
+@pytest.mark.parametrize("observed", [True, False])
+def test_series_groupby_on_2_categoricals_unobserved(aggregation: str, observed: bool):
+    # GH 17605
+    df = pd.DataFrame(
+        {
+            "cat_1": pd.Categorical(list("AABB"), categories=list("ABCD")),
+            "cat_2": pd.Categorical(list("AB") * 2, categories=list("ABCD")),
+            "value": [0.1] * 4,
+        }
+    )
+
+    # Expect 1 observation for each combination of categories
+    if observed:
+        expected_length = 4
+    else:
+        expected_length = len(df["cat_1"].cat.categories) * len(
+            df["cat_2"].cat.categories
+        )
+
+    series_groupby = df.groupby(["cat_1", "cat_2"], observed=observed)["value"]
+    agg = getattr(series_groupby, aggregation)
+    result = agg()
+    assert len(result) == expected_length


Code review comments:
diff_hunk: @@ -1252,3 +1252,29 @@ def test_get_nonexistent_category():
                 {"var": [rows.iloc[-1]["var"]], "val": [rows.iloc[-1]["vau"]]}
             )
         )
+
+
+@pytest.mark.parametrize("aggregation", ["sum", "mean", "min", "count"])
+@pytest.mark.parametrize("observed", [True, False])
+def test_series_groupby_on_2_categoricals_unobserved(aggregation: str, observed: bool):
+    # GH 17605
+    df = pd.DataFrame(
+        {
+            "cat_1": pd.Categorical(list("AABB"), categories=list("ABCD")),
+            "cat_2": pd.Categorical(list("AB") * 2, categories=list("ABCD")),
+            "value": [0.1] * 4,
+        }
+    )
+
+    # Expect 1 observation for each combination of categories
+    if observed:
+        expected_length = 4
+    else:
+        expected_length = len(df["cat_1"].cat.categories) * len(
path: pandas/tests/groupby/test_categorical.py
text: @user1:
Can you hardcode this as the expected? 16 right?

diff_hunk: @@ -1252,3 +1252,29 @@ def test_get_nonexistent_category():
                 {"var": [rows.iloc[-1]["var"]], "val": [rows.iloc[-1]["vau"]]}
             )
         )
+
+
+@pytest.mark.parametrize("aggregation", ["sum", "mean", "min", "count"])
path: pandas/tests/groupby/test_categorical.py
text: @user1:
Instead of doing this can you use the `reduction_func` fixture that already exists in the groupby conftest?

@author:
Cool, didn't know about that one! This exposed some other reductions that don't respect the`observed` keyword, so I'll try to correct those as well.