BUG: GroupBy.count() and GroupBy.sum() incorreclty return NaN instead of 0 for missing categories (Version 2)
State: closed

- [x] closes #31422 
- [x] closes #35028 
- [x] tests added / passed
- [x] passes `black pandas`
- [x] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [x] whatsnew entry

_Behavioural Changes_
Fixing two related bugs: when grouping on multiple categoricals, .sum() and .count() would return NaN for the missing categories, but they are expected to return 0 for the missing categories. Both these bugs are fixed.

_Tests_
Tests were added in PR #35022 when these bugs were discovered and the tests were marked with an xfail. For this PR the xfails are removed and the tests are passing normally. As well, a few other existing tests were expecting `sum()` to return `NaN`; these have been updated so that the tests now expect to get `0` (which is the desired behaviour).

One new test is added to ensure that the exception handling of the new `try-except-finally` block behaves as expected. 

_df.pivot_table_
The changes to `.sum()` & `.count()` also impacts the `df.pivot_table()` if it is called with `aggfunc=sum/count` and is pivoted on a Categorical column with observed=False. This is not explicitly mentioned in either of the bugs, but it does make the behaviour consistent (i.e. the sum of a missing category is zero, not NaN). Two tests on test_pivot.py was updated to reflect this change.

Labels:

Comments:
it’s better to push to the original PR i guess it’s ok this time
> it’s better to push to the original PR i guess it’s ok this time

Ok. Is pushing to the existing PR the best practice even if all the new changes are on a new branch? 
_GroupBy.apply(sum)_
One issue to note is that `GroupBy.agg(sum)` and `GroupBy.apply(sum)` now produce different outputs. `.agg(sum)` produces the desired behaviour of returning `0` for missing categories, while `.apply(sum)` still has the old behaviour of returning `NaN`. 

    import pandas as pd
    
    df = pd.DataFrame(
        {
            "cat_1": pd.Categorical(list("AABB"), categories=list("ABC")),
            "cat_2": pd.Categorical(list("1111"), categories=list("12")),
            "value": [0.1, 0.1, 0.1, 0.1],
        }
    )
    df_grp = df.groupby(["cat_1", "cat_2"], observed=False)
    
    # Using branch from this PR:
    # .sum() returns zeros 
    print( df_grp.sum() )
    
    # agg returns zeros  
    print( df_grp.agg(sum) )
    
    # apply still returns NaN 
    print( df_grp.apply(sum) )

Calling `.apply(sum)` never actually calls the `GroupBy.sum()` method, it just applies the passed in function. `.agg(sum)` does call the `GroupBy.sum()` method (happening here https://github.com/pandas-dev/pandas/blob/master/pandas/core/groupby/generic.py#L253). Thes means that `.agg(sum)` does get the special behaviour to return zero for missing categories, while `.apply(sum)` misses out on the special behaviour and still returns `NaN`. 

This change necessitated an update to `groupby\test_categorical.py::test_seriesgroupby_observed_false_or_none`. 

@jreback , what are your thoughts on this? Is it fine to leave `.apply(sum)` as it is (returning NaN for missing categories). 

I am not inclined to address it in this PR, but if we do want it fixed, I'll raise an issue for it.