Repository: pandas-dev/pandas
Pull request: BUG `DataFrameGroupBy.agg` with list not respecting `as_index=False`
- [x] Closes #52849(
- [x] [Tests added and passed](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#writing-tests) if fixing a bug or adding a new feature
- [x] All [code checks passed](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#pre-commit)
- [x] Added [type annotations](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#type-hints)
- [x] Added an entry in the latest `doc/source/whatsnew/v2.1.0.rst` file

Dealing with non-categorical data is fairly easy, but dealing with categorical data is kind of tricky. I'm currently taking a pretty dirty approach, so if maintainers have better solutions please let me know and I'll make changes correspondingly.

Problem statement:
BUG: DataFrameGroupBy.agg with lists doesn't respect as_index=False
```
df = DataFrame({"a1": [0, 0, 1], "a2": [2, 3, 3], "b": [4, 5, 6]})
# df = df.astype({"a1": "category", "a2": "category"})
gb = df.groupby(by=["a1", "a2"], as_index=False)
result = gb.agg(["sum"])
print(result)
#         b
#       sum
# a1 a2
# 0  2    4
#    3    5
# 1  3    6
```

The expected output is:

```
  a1 a2   b
        sum
0  0  2   4
1  0  3   5
2  1  3   6
```

In particular, the index is a RangeIndex and `a1` as well as `a2` are columns.

Hints from issue discussion:
This looks a bit hard, but I'll give it a try. If I won't be able to solve this within a week, I'll re-assign
take
There is also missing data in a1
The single missing number aside (from a1 column) - as this seems like a separate issue.

What should be the expected output for gb.agg(["sum", "min"]) ? 
It outputs something this, which looks reasonable - since how otherwise can we output multiple agg columns ?
```
#         b    
#       sum min
# a1 a2        
# 0  2    4   4
#    3    5   5
# 1  3    6   6
```

Therefore, if I understand correctly - if we're passing only one argument to list, we want to overwrite the list behaviour & output sth similar to gb.agg("sum"), right ? My concern is that some could argue that current output for gb.agg(["sum"]) is correct - that if you're passing in a list, you do want the output df to include "sum" annotation.
> The single missing number aside (from a1 column) - as this seems like a separate issue.

Are you meaning something like the following?

```
#         b    
#       sum min
# a1 a2        
# 0  2    4   4
# 0  3    5   5
# 1  3    6   6
```

pandas prints a sparse representation for the index. A missing entry implicitly means it is repeated from above. 

> What should be the expected output for gb.agg(["sum", "min"]) ?

I originally had the incorrect columns in the OP; it should be a MultiIndex. The OP has been updated.
Hey, I'm sorry but I'll need to drop this issue. Pandas is a bit too much for me right now, I'll come back to this in the future
I believe this is because returning too early. As shown below, it didn't go through the `as_index` check. I will postpone its return until after checking `as_index`.

https://github.com/pandas-dev/pandas/blob/935244a9b9c0fe796de315cd381d6364719bfdc1/pandas/core/groupby/generic.py#L1331-L1333
take
But Categorical data seems to be tricky. The current 
```python
result = self._insert_inaxis_grouper(result)
result.index = default_index(len(result))
```
seems unable to deal with that.

Commit message: Merge branch 'main' into grpby-agg-asindex

Patch to review:
diff --git a/doc/source/whatsnew/v2.1.0.rst b/doc/source/whatsnew/v2.1.0.rst
index 299adc5af8aea..3acf9b2098510 100644
--- a/doc/source/whatsnew/v2.1.0.rst
+++ b/doc/source/whatsnew/v2.1.0.rst
@@ -415,6 +415,7 @@ Groupby/resample/rolling
   grouped :class:`Series` or :class:`DataFrame` was a :class:`DatetimeIndex`, :class:`TimedeltaIndex`
   or :class:`PeriodIndex`, and the ``groupby`` method was given a function as its first argument,
   the function operated on the whole index rather than each element of the index. (:issue:`51979`)
+- Bug in :meth:`DataFrameGroupBy.agg` with lists not respecting ``as_index=False`` (:issue:`52849`)
 - Bug in :meth:`DataFrameGroupBy.apply` causing an error to be raised when the input :class:`DataFrame` was subset as a :class:`DataFrame` after groupby (``[['a']]`` and not ``['a']``) and the given callable returned :class:`Series` that were not all indexed the same. (:issue:`52444`)
 - Bug in :meth:`DataFrameGroupBy.apply` raising a ``TypeError`` when selecting multiple columns and providing a function that returns ``np.ndarray`` results (:issue:`18930`)
 - Bug in :meth:`GroupBy.groups` with a datetime key in conjunction with another key produced incorrect number of group keys (:issue:`51158`)
diff --git a/pandas/core/groupby/generic.py b/pandas/core/groupby/generic.py
index 838e4d39dee10..acd4d5bbe81cd 100644
--- a/pandas/core/groupby/generic.py
+++ b/pandas/core/groupby/generic.py
@@ -1396,9 +1396,7 @@ def aggregate(self, func=None, *args, engine=None, engine_kwargs=None, **kwargs)
 
         op = GroupByApply(self, func, args=args, kwargs=kwargs)
         result = op.agg()
-        if not is_dict_like(func) and result is not None:
-            return result
-        elif relabeling:
+        if relabeling and (is_dict_like(func) or result is None):
             # this should be the only (non-raising) case with relabeling
             # used reordered index of columns
             result = cast(DataFrame, result)
@@ -1450,7 +1448,7 @@ def aggregate(self, func=None, *args, engine=None, engine_kwargs=None, **kwargs)
                     result.columns = self._obj_with_exclusions.columns.copy()
 
         if not self.as_index:
-            result = self._insert_inaxis_grouper(result)
+            result = self._insert_inaxis_grouper(result, finalize=True)
             result.index = default_index(len(result))
 
         return result
diff --git a/pandas/core/groupby/groupby.py b/pandas/core/groupby/groupby.py
index bdab641719ded..c44db4e25f2f3 100644
--- a/pandas/core/groupby/groupby.py
+++ b/pandas/core/groupby/groupby.py
@@ -1222,15 +1222,39 @@ def _set_result_index_ordered(
         return result
 
     @final
-    def _insert_inaxis_grouper(self, result: Series | DataFrame) -> DataFrame:
+    def _insert_inaxis_grouper(
+        self, result: Series | DataFrame, finalize: bool = False
+    ) -> DataFrame:
         if isinstance(result, Series):
             result = result.to_frame()
 
+        # GH #52849: when called with finalize=True, this means we are dealing
+        # with as_index=False after the result has been set. For categorical data,
+        # the result would have already included unused categories, so calling
+        # get_group_levels is not feasible.
+        group_levels: Sequence[ExtensionArray | np.ndarray]
+        if (
+            finalize
+            and not self.observed
+            and len(self.grouper.groupings) != 1
+            and any(
+                isinstance(ping.grouping_vector, (Categorical, CategoricalIndex))
+                for ping in self.grouper.groupings
+            )
+        ):
+            from pandas.core.reshape.util import cartesian_product
+
+            group_levels = cartesian_product(
+                [ping.group_index for ping in self.grouper.groupings]
+            )
+        else:
+            group_levels = self.grouper.get_group_levels()
+
         # zip in reverse so we can always insert at loc 0
         columns = result.columns
         for name, lev, in_axis in zip(
             reversed(self.grouper.names),
-            reversed(self.grouper.get_group_levels()),
+            reversed(group_levels),
             reversed([grp.in_axis for grp in self.grouper.groupings]),
         ):
             # GH #28549
diff --git a/pandas/tests/groupby/test_categorical.py b/pandas/tests/groupby/test_categorical.py
index 6ce17e406db65..c0704d9684574 100644
--- a/pandas/tests/groupby/test_categorical.py
+++ b/pandas/tests/groupby/test_categorical.py
@@ -2067,11 +2067,8 @@ def test_agg_list(request, as_index, observed, reduction_func, test_series, keys
     if as_index and (test_series or reduction_func == "size"):
         expected = expected.to_frame(reduction_func)
     if not test_series:
-        if not as_index:
-            # TODO: GH#52849 - as_index=False is not respected
-            expected = expected.set_index(keys)
-        expected.columns = MultiIndex(
-            levels=[["b"], [reduction_func]], codes=[[0], [0]]
+        expected.columns = MultiIndex.from_tuples(
+            [(ind, "") for ind in expected.columns[:-1]] + [("b", reduction_func)]
         )
     elif not as_index:
         expected.columns = keys + [reduction_func]


Code review comments:
diff_hunk: @@ -1396,9 +1396,7 @@ def aggregate(self, func=None, *args, engine=None, engine_kwargs=None, **kwargs)
 
         op = GroupByApply(self, func, args=args, kwargs=kwargs)
         result = op.agg()
-        if not is_dict_like(func) and result is not None:
-            return result
path: pandas/core/groupby/generic.py
text: @user1:
Can we just call `.reset_index()` when `as_index=False` here?

@author:
I think you are right @user1, this seems to be the right way to go. However, simply doing this is failing certain test cases. I will try to investigate later.

@author:
Update: according to my investigation, it needs to (at least) be something like
```python
return result if self.as_index or not is_list_like(func) else result.reset_index()
```
I will modify the PR in this way for now.