Repository: pandas-dev/pandas
Pull request: BUG: Groupby ops on empty objects loses index, columns, dtypes
- [x] closes #26411
- [x] closes #13483
- [x] tests added / passed
- [x] Ensure all linting tests pass, see [here](https://pandas.pydata.org/pandas-docs/dev/development/contributing.html#code-standards) for how to run them
- [x] whatsnew entry


Problem statement:
Dataframe pivot_table() returning a multi-index for a single value, empty dataframe
#### Code Sample, a copy-pastable example if possible

```
In [1]: import pandas as pd

In [2]: df = pd.DataFrame({'a': ['a1', 'a2', 'a3'], 'b': [1,2,3], 'c':['c1', 'c2', 'c3']})

In [3]: x = df[df['c'] == 'c1']

In [4]: y = x.pivot_table(index='a', columns='c', values='b')

In [5]: y.columns
Out[5]: Index([u'c1'], dtype='object', name=u'c')
In [6]: x = df[df['c'] == 'c4']

In [7]: y = x.pivot_table(index='a', columns='c', values='b')

In [8]: y.columns  # returns a multi-index, should not
Out[8]:
MultiIndex(levels=[[u'b'], []],
           labels=[[], []],
           names=[None, u'c'])

# a check was added for empty dataframe that is causing the behavior, works in 0.16.2
> /auto/energymdl2/anaconda/envs/commod_20160516_pd18/lib/python2.7/site-packages/pandas/tools/pivot.py(161)pivot_table()
    159
    160     # discard the top level
--> 161     if values_passed and not values_multi and not table.empty:
    162         table = table[values[0]]
    163


```
#### Expected Output

```
In [5]: y.columns
Out[5]: Index([], dtype='object', name=u'c')

```
#### output of `pd.show_versions()`

```

INSTALLED VERSIONS
------------------
commit: None
python: 2.7.11.final.0
python-bits: 64
OS: Linux
OS-release: 2.6.32-573.7.1.el6.x86_64
machine: x86_64
processor: x86_64
byteorder: little
LC_ALL: C
LANG: en_US.UTF-8

pandas: 0.18.1
nose: 1.3.7
pip: 8.1.1
setuptools: 20.7.0
Cython: 0.24
numpy: 1.10.4
scipy: 0.17.0
statsmodels: 0.6.1
xarray: 0.7.2
IPython: 4.1.2
sphinx: 1.3.5
patsy: 0.4.1
dateutil: 2.5.2
pytz: 2016.4
blosc: None
bottleneck: 1.0.0
tables: 3.2.2
numexpr: 2.5.2
matplotlib: 1.4.3
openpyxl: 2.3.2
xlrd: 0.9.4
xlwt: 1.0.0
xlsxwriter: 0.8.4
lxml: 3.6.0
bs4: 4.3.2
html5lib: 0.999
httplib2: 0.9.2
apiclient: 1.5.0
sqlalchemy: 1.0.12
pymysql: None
psycopg2: None
jinja2: 2.8
boto: 2.39.0
pandas_datareader: None

```


Grouping, then resampling empty DataFrame leads to it losing column names and index
#### Code Sample

```python
import pandas as pd

empty_df = pd.DataFrame([], columns=["a", "b"], index=pd.TimedeltaIndex([]))

resampled_df = empty_df.groupby("a").resample(rule=pd.to_timedelta("00:00:01")).mean()

print resampled_df
print resampled_df["b"]
```
#### Problem description

After grouping and subsequently resampling as shown above, the returned data frame seems to lose all its meta information, such as column names and what type of index it uses. This leads to a key error when trying to access columns of the data frame that were existent, but empty, before doing the grouping and resampling.

Perhaps somewhere in the code a "generic" empty data frame is returned without the attached information about column names and indices?

#### Expected Output

```
Empty DataFrame
Columns: [a, b]
Index: []
Series([], Name: b, dtype: object) 
```

or similar, and `empty_df["b"] == resampled_df["b"]` would make sense to me.

#### Actual Output

```
Empty DataFrame
Columns: []
Index: []
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/lib/python2.7/dist-packages/pandas/core/frame.py", line 2927, in __getitem__
    indexer = self.columns.get_loc(key)
  File "/usr/local/lib/python2.7/dist-packages/pandas/core/indexes/base.py", line 2659, in get_lo
    return self._engine.get_loc(self._maybe_cast_indexer(key))
  File "pandas/_libs/index.pyx", line 108, in pandas._libs.index.IndexEngine.get_loc
  File "pandas/_libs/index.pyx", line 132, in pandas._libs.index.IndexEngine.get_loc
  File "pandas/_libs/hashtable_class_helper.pxi", line 1601, in pandas._libs.hashtable.PyObjectHashTable.get_item
  File "pandas/_libs/hashtable_class_helper.pxi", line 1608, in pandas._libs.hashtable.PyObjectHashTable.get_item
KeyError: 'b'  
```

#### Output of ``pd.show_versions()``

<details>
INSTALLED VERSIONS                                                                                                                                                                                                                             ------------------                                                                                                                                                                                                                             commit: None                                                                                                                                                                                                                                   python: 2.7.12.final.0                                                                                                                                                                                                                         python-bits: 64                                                                                                                                                                                                                                OS: Linux                                                                                                                                                                                                                                      OS-release: 4.9.93-linuxkit-aufs                                                                                                                                                                                                               machine: x86_64                                                                                                                                                                                                                                processor: x86_64                                                                                                                                                                                                                              byteorder: little                                                                                                                                                                                                                              LC_ALL: C.UTF-8                                                                                                                                                                                                                                LANG: C.UTF-8                                                                                                                                                                                                                                  LOCALE: None.None                                                                                                                                                                                                                                                                                                                                                                                                                                                                             pandas: 0.24.2                                                                                                                                                                                                                                 pytest: None                                                                                                                                                                                                                                   pip: 8.1.1                                                                                                                                                                                                                                     setuptools: 20.7.0                                                                                                                                                                                                                             Cython: None                                                                                                                                                                                                                                   numpy: 1.16.2                                                                                                                                                                                                                                  scipy: 0.17.0                                                                                                                                                                                                                                  pyarrow: None                                                                                                                                                                                                                                  xarray: None                                                                                                                                                                                                                                   IPython: None                                                                                                                                                                                                                                  sphinx: 1.3.6                                                                                                                                                                                                                                  patsy: None                                                                                                                                                                                                                                    dateutil: 2.8.0                                                                                                                                                                                                                                pytz: 2018.9                                                                                                                                                                                                                                   blosc: None                                                                                                                                                                                                                                    bottleneck: None                                                                                                                                                                                                                               tables: 3.5.1                                                                                                                                                                                                                                  numexpr: 2.6.9                                                                                                                                                                                                                                 feather: None                                                                                                                                                                                                                                  matplotlib: 1.5.1                                                                                                                                                                                                                              openpyxl: None                                                                                                                                                                                                                                 xlrd: None                                                                                                                                                                                                                                     xlwt: None                                                                                                                                                                                                                                     xlsxwriter: None                                                                                                                                                                                                                               lxml.etree: 3.5.0                                                                                                                                                                                                                              bs4: 4.4.1                                                                                                                                                                                                                                     html5lib: 0.999                                                                                                                                                                                                                                sqlalchemy: 1.3.1                                                                                                                                                                                                                              pymysql: 0.7.2.None                                                                                                                                                                                                                            psycopg2: None                                                                                                                                                                                                                                 jinja2: 2.10                                                                                                                                                                                                                                   s3fs: None                                                                                                                                                                                                                                     fastparquet: None
pandas_gbq: None
pandas_datareader: None
gcsfs: None
</details>

Hints from issue discussion:
Is there a reason for having the additional check for empty table?  Would the fix be to remove the additional condition?

```
    159
    160     # discard the top level
--> 161     if values_passed and not values_multi and not table.empty:
    162         table = table[values[0]]
    163
```

This was added in #12287 and simply removing it causes another problem. May better to reconsider whole block (related to #13554).

Confirmed the bug on master, haven't looked at it any further.
I have traced this down to this bit of code in  **core/groupby/generic.py** (line 273):
```
if len(keys) == 0:
    return DataFrame(index=keys)
```

I have managed to preserve columns and dtypes by replacing it with this:
```
if len(keys) == 0:
    result = DataFrame(index=keys, columns=self.obj.columns)
    result = result.astype(self.obj.dtypes.to_dict())
    return result
```

Regarding the index, it could be kept with something like:
```
result = DataFrame(index=self.obj.index[:0], columns=self.obj.columns)
```

but this (even after modifications) breaks test_agg_apply_corner() in **tests/groupby/aggregate/test_aggregate.py** which expects groupby performed on float64 values to result in a Float64Index. In other words, it expects the index to be consistent with the values, not with the index of the starting DataFrame.

Feels like a trade-off and I am not quite sure how to proceed. Any thoughts?


 

@krsnik93 thanks for digging into this. It may be easiest to discuss over a PR.

Commit message: Remove request argument

Patch to review:
diff --git a/doc/source/whatsnew/v1.3.0.rst b/doc/source/whatsnew/v1.3.0.rst
index cbe53edaf12b5..094b62b900fc4 100644
--- a/doc/source/whatsnew/v1.3.0.rst
+++ b/doc/source/whatsnew/v1.3.0.rst
@@ -438,6 +438,7 @@ Groupby/resample/rolling
 - Bug in :meth:`core.window.rolling.RollingGroupby.corr` and :meth:`core.window.expanding.ExpandingGroupby.corr` where the groupby column would return 0 instead of ``np.nan`` when providing ``other`` that was longer than each group (:issue:`39591`)
 - Bug in :meth:`core.window.expanding.ExpandingGroupby.corr` and :meth:`core.window.expanding.ExpandingGroupby.cov` where 1 would be returned instead of ``np.nan`` when providing ``other`` that was longer than each group (:issue:`39591`)
 - Bug in :meth:`.GroupBy.mean`, :meth:`.GroupBy.median` and :meth:`DataFrame.pivot_table` not propagating metadata (:issue:`28283`)
+- Bug in :class:`SeriesGroupBy` and :class:`DataFrameGroupBy` on an empty ``Series`` or ``DataFrame`` would lose index, columns, and/or data types when directly using the methods ``idxmax``, ``idxmin``, ``mad``, ``min``, ``max``, ``sum``, ``prod``, and ``skew`` or using them through ``apply``, ``aggregate``, or ``resample`` (:issue:`26411`)
 -
 
 Reshaping
@@ -453,6 +454,7 @@ Reshaping
 - Bug in :meth:`DataFrame.sort_values` not reshaping index correctly after sorting on columns, when ``ignore_index=True`` (:issue:`39464`)
 - Bug in :meth:`DataFrame.append` returning incorrect dtypes with combinations of ``ExtensionDtype`` dtypes (:issue:`39454`)
 - Bug in :meth:`DataFrame.append` returning incorrect dtypes with combinations of ``datetime64`` and ``timedelta64`` dtypes (:issue:`39574`)
+- Bug in :meth:`DataFrame.pivot_table` returning a ``MultiIndex`` for a single value when operating on and empty ``DataFrame`` (:issue:`13483`)
 
 Sparse
 ^^^^^^
diff --git a/pandas/core/groupby/generic.py b/pandas/core/groupby/generic.py
index c1a277925de2a..40a1f8d1a0439 100644
--- a/pandas/core/groupby/generic.py
+++ b/pandas/core/groupby/generic.py
@@ -450,13 +450,19 @@ def _wrap_transformed_output(
         return result
 
     def _wrap_applied_output(
-        self, keys: Index, values: Optional[List[Any]], not_indexed_same: bool = False
+        self,
+        data: Series,
+        keys: Index,
+        values: Optional[List[Any]],
+        not_indexed_same: bool = False,
     ) -> FrameOrSeriesUnion:
         """
         Wrap the output of SeriesGroupBy.apply into the expected result.
 
         Parameters
         ----------
+        data : Series
+            Input data for groupby operation.
         keys : Index
             Keys of groups that Series was grouped by.
         values : Optional[List[Any]]
@@ -471,7 +477,10 @@ def _wrap_applied_output(
         if len(keys) == 0:
             # GH #6265
             return self.obj._constructor(
-                [], name=self._selection_name, index=keys, dtype=np.float64
+                [],
+                name=self._selection_name,
+                index=self.grouper.result_index,
+                dtype=data.dtype,
             )
         assert values is not None
 
@@ -1229,9 +1238,13 @@ def _aggregate_item_by_item(self, func, *args, **kwargs) -> DataFrame:
 
         return self.obj._constructor(result, columns=result_columns)
 
-    def _wrap_applied_output(self, keys, values, not_indexed_same=False):
+    def _wrap_applied_output(self, data, keys, values, not_indexed_same=False):
         if len(keys) == 0:
-            return self.obj._constructor(index=keys)
+            result = self.obj._constructor(
+                index=self.grouper.result_index, columns=data.columns
+            )
+            result = result.astype(data.dtypes.to_dict())
+            return result
 
         # GH12824
         first_not_none = next(com.not_none(*values), None)
diff --git a/pandas/core/groupby/groupby.py b/pandas/core/groupby/groupby.py
index e939c184d501a..b9310794d7caa 100644
--- a/pandas/core/groupby/groupby.py
+++ b/pandas/core/groupby/groupby.py
@@ -981,7 +981,7 @@ def _python_apply_general(
         keys, values, mutated = self.grouper.apply(f, data, self.axis)
 
         return self._wrap_applied_output(
-            keys, values, not_indexed_same=mutated or self.mutated
+            data, keys, values, not_indexed_same=mutated or self.mutated
         )
 
     def _iterate_slices(self) -> Iterable[Series]:
@@ -1058,7 +1058,7 @@ def _wrap_aggregated_output(
     def _wrap_transformed_output(self, output: Mapping[base.OutputKey, np.ndarray]):
         raise AbstractMethodError(self)
 
-    def _wrap_applied_output(self, keys, values, not_indexed_same: bool = False):
+    def _wrap_applied_output(self, data, keys, values, not_indexed_same: bool = False):
         raise AbstractMethodError(self)
 
     @final
diff --git a/pandas/core/reshape/pivot.py b/pandas/core/reshape/pivot.py
index 8feb379a82ada..d0026d7acbe65 100644
--- a/pandas/core/reshape/pivot.py
+++ b/pandas/core/reshape/pivot.py
@@ -236,14 +236,8 @@ def __internal_pivot_table(
         )
 
     # discard the top level
-    if (
-        values_passed
-        and not values_multi
-        and not table.empty
-        and (table.columns.nlevels > 1)
-    ):
-        table = table[values[0]]
-
+    if values_passed and not values_multi and table.columns.nlevels > 1:
+        table = table.droplevel(0, axis=1)
     if len(index) == 0 and len(columns) > 0:
         table = table.T
 
@@ -650,7 +644,6 @@ def crosstab(
         **dict(zip(unique_colnames, columns)),
     }
     df = DataFrame(data, index=common_idx)
-    original_df_cols = df.columns
 
     if values is None:
         df["__dummy__"] = 0
@@ -660,7 +653,7 @@ def crosstab(
         kwargs = {"aggfunc": aggfunc}
 
     table = df.pivot_table(
-        ["__dummy__"],
+        "__dummy__",
         index=unique_rownames,
         columns=unique_colnames,
         margins=margins,
@@ -669,12 +662,6 @@ def crosstab(
         **kwargs,
     )
 
-    # GH18321, after pivoting, an extra top level of column index of `__dummy__` is
-    # created, and this extra level should not be included in the further steps
-    if not table.empty:
-        cols_diff = df.columns.difference(original_df_cols)[0]
-        table = table[cols_diff]
-
     # Post-process
     if normalize is not False:
         table = _normalize(
diff --git a/pandas/tests/groupby/aggregate/test_aggregate.py b/pandas/tests/groupby/aggregate/test_aggregate.py
index 92b7aefa6dd8c..f2f9cfee178d9 100644
--- a/pandas/tests/groupby/aggregate/test_aggregate.py
+++ b/pandas/tests/groupby/aggregate/test_aggregate.py
@@ -147,11 +147,13 @@ def test_agg_apply_corner(ts, tsframe):
     # DataFrame
     grouped = tsframe.groupby(tsframe["A"] * np.nan)
     exp_df = DataFrame(
-        columns=tsframe.columns, dtype=float, index=Index([], dtype=np.float64)
+        columns=tsframe.columns,
+        dtype=float,
+        index=Index([], name="A", dtype=np.float64),
     )
-    tm.assert_frame_equal(grouped.sum(), exp_df, check_names=False)
-    tm.assert_frame_equal(grouped.agg(np.sum), exp_df, check_names=False)
-    tm.assert_frame_equal(grouped.apply(np.sum), exp_df.iloc[:, :0], check_names=False)
+    tm.assert_frame_equal(grouped.sum(), exp_df)
+    tm.assert_frame_equal(grouped.agg(np.sum), exp_df)
+    tm.assert_frame_equal(grouped.apply(np.sum), exp_df)
 
 
 def test_agg_grouping_is_list_tuple(ts):
diff --git a/pandas/tests/groupby/test_groupby.py b/pandas/tests/groupby/test_groupby.py
index 4dce7e8553be4..6731790c89384 100644
--- a/pandas/tests/groupby/test_groupby.py
+++ b/pandas/tests/groupby/test_groupby.py
@@ -10,6 +10,7 @@
 
 import pandas as pd
 from pandas import (
+    Categorical,
     DataFrame,
     Grouper,
     Index,
@@ -18,6 +19,7 @@
     Timestamp,
     date_range,
     read_csv,
+    to_datetime,
 )
 import pandas._testing as tm
 from pandas.core.base import SpecificationError
@@ -1716,15 +1718,48 @@ def test_pivot_table_values_key_error():
         )
 
 
-def test_empty_dataframe_groupby():
-    # GH8093
-    df = DataFrame(columns=["A", "B", "C"])
-
-    result = df.groupby("A").sum()
-    expected = DataFrame(columns=["B", "C"], dtype=np.float64)
-    expected.index.name = "A"
-
-    tm.assert_frame_equal(result, expected)
+@pytest.mark.parametrize("columns", ["C", ["C"]])
+@pytest.mark.parametrize("keys", [["A"], ["A", "B"]])
+@pytest.mark.parametrize(
+    "values",
+    [
+        [True],
+        [0],
+        [0.0],
+        ["a"],
+        [Categorical([0])],
+        [to_datetime(0)],
+        [date_range(0, 1, 1, tz="US/Eastern")],
+        [pd.array([0], dtype="Int64")],
+    ],
+)
+@pytest.mark.parametrize("method", ["attr", "agg", "apply"])
+@pytest.mark.parametrize(
+    "op", ["idxmax", "idxmin", "mad", "min", "max", "sum", "prod", "skew"]
+)
+def test_empty_groupby(columns, keys, values, method, op):
+    # GH8093 & GH26411
+
+    override_dtype = None
+    if isinstance(values[0], bool) and op in ("prod", "sum") and method != "apply":
+        # sum/product of bools is an integer
+        override_dtype = "int64"
+
+    df = DataFrame([3 * values], columns=list("ABC"))
+    df = df.iloc[:0]
+
+    gb = df.groupby(keys)[columns]
+    if method == "attr":
+        result = getattr(gb, op)()
+    else:
+        result = getattr(gb, method)(op)
+
+    expected = df.set_index(keys)[columns]
+    if override_dtype is not None:
+        expected = expected.astype(override_dtype)
+    if len(keys) == 1:
+        expected.index.name = keys[0]
+    tm.assert_equal(result, expected)
 
 
 def test_tuple_as_grouping():
diff --git a/pandas/tests/resample/test_resampler_grouper.py b/pandas/tests/resample/test_resampler_grouper.py
index a17ed44c4011a..50775b9ef3a47 100644
--- a/pandas/tests/resample/test_resampler_grouper.py
+++ b/pandas/tests/resample/test_resampler_grouper.py
@@ -10,6 +10,7 @@
 from pandas import (
     DataFrame,
     Series,
+    TimedeltaIndex,
     Timestamp,
 )
 import pandas._testing as tm
@@ -398,6 +399,18 @@ def test_resample_groupby_agg():
     tm.assert_frame_equal(result, expected)
 
 
+@pytest.mark.parametrize("keys", [["a"], ["a", "b"]])
+def test_empty(keys):
+    # GH 26411
+    df = pd.DataFrame([], columns=["a", "b"], index=TimedeltaIndex([]))
+    result = df.groupby(keys).resample(rule=pd.to_timedelta("00:00:01")).mean()
+    expected = DataFrame(columns=["a", "b"]).set_index(keys, drop=False)
+    if len(keys) == 1:
+        expected.index.name = keys[0]
+
+    tm.assert_frame_equal(result, expected)
+
+
 @pytest.mark.parametrize("consolidate", [True, False])
 def test_resample_groupby_agg_object_dtype_all_nan(consolidate):
     # https://github.com/pandas-dev/pandas/issues/39329
diff --git a/pandas/tests/reshape/test_crosstab.py b/pandas/tests/reshape/test_crosstab.py
index 86cde3eee874d..1ecb408d49813 100644
--- a/pandas/tests/reshape/test_crosstab.py
+++ b/pandas/tests/reshape/test_crosstab.py
@@ -240,7 +240,10 @@ def test_crosstab_no_overlap(self):
         s2 = Series([4, 5, 6], index=[4, 5, 6])
 
         actual = crosstab(s1, s2)
-        expected = DataFrame()
+        expected = DataFrame(
+            index=Index([], dtype="int64", name="row_0"),
+            columns=Index([], dtype="int64", name="col_0"),
+        )
 
         tm.assert_frame_equal(actual, expected)
 
diff --git a/pandas/tests/reshape/test_pivot.py b/pandas/tests/reshape/test_pivot.py
index 19eba4305fdf6..8d2b4f2b325c2 100644
--- a/pandas/tests/reshape/test_pivot.py
+++ b/pandas/tests/reshape/test_pivot.py
@@ -2040,7 +2040,7 @@ def test_pivot_table_aggfunc_scalar_dropna(self, dropna):
         tm.assert_frame_equal(result, expected)
 
     def test_pivot_table_empty_aggfunc(self):
-        # GH 9186
+        # GH 9186 & GH 13483
         df = DataFrame(
             {
                 "A": [2, 2, 3, 3, 2],
@@ -2050,7 +2050,8 @@ def test_pivot_table_empty_aggfunc(self):
             }
         )
         result = df.pivot_table(index="A", columns="D", values="id", aggfunc=np.size)
-        expected = DataFrame()
+        expected = DataFrame(index=Index([], dtype="int64", name="A"))
+        expected.columns.name = "D"
         tm.assert_frame_equal(result, expected)
 
     def test_pivot_table_no_column_raises(self):


Code review comments:
diff_hunk: @@ -1229,9 +1238,13 @@ def _aggregate_item_by_item(self, func, *args, **kwargs) -> DataFrame:
 
         return self.obj._constructor(result, columns=result_columns)
 
-    def _wrap_applied_output(self, keys, values, not_indexed_same=False):
+    def _wrap_applied_output(self, data, keys, values, not_indexed_same=False):
         if len(keys) == 0:
-            return self.obj._constructor(index=keys)
+            result = self.obj._constructor(
+                index=self.grouper.result_index, columns=data.columns
+            )
+            result = result.astype(data.dtypes.to_dict())
path: pandas/core/groupby/generic.py
text: @user1:
suppose could add `copy=False` here