Pandas: "Exception: Column(s) <cols> already selected" when using groupby, resample, and agg

Created on 6 Nov 2018  路  6Comments  路  Source: pandas-dev/pandas

Code Sample, a copy-pastable example if possible

from datetime import datetime, timedelta
import pandas as pd

times = [datetime.now() + timedelta(hours=i) for i in range(10)] * 2
ids = ['foo', 'bar'] * 10
df = pd.DataFrame({'time': times, 'id': ids, 'value': range(20)})

Problem description

This line:

df.set_index('time').groupby('id').resample('H').agg(['mean', 'std'])

causes this error:

Exception: Column(s) id already selected

however invoking mean and std individually work as expected: 

df.set_index('time').groupby('id').resample('H').mean()
df.set_index('time').groupby('id').resample('H').std()

Expected Output

A dataframe with the combined outputs of:

>>> df.set_index('time').groupby('id').resample('H').mean()
                         value
id  time                      
bar 2018-11-06 12:00:00    6.0
    2018-11-06 13:00:00    NaN
    2018-11-06 14:00:00    8.0
    2018-11-06 15:00:00    NaN
    2018-11-06 16:00:00   10.0
    2018-11-06 17:00:00    NaN
    2018-11-06 18:00:00   12.0
    2018-11-06 19:00:00    NaN
    2018-11-06 20:00:00   14.0
foo 2018-11-06 11:00:00    5.0
    2018-11-06 12:00:00    NaN
    2018-11-06 13:00:00    7.0
    2018-11-06 14:00:00    NaN
    2018-11-06 15:00:00    9.0
    2018-11-06 16:00:00    NaN
    2018-11-06 17:00:00   11.0
    2018-11-06 18:00:00    NaN
    2018-11-06 19:00:00   13.0

>>> df.set_index('time').groupby('id').resample('H').std()
                            value
id  time                         
bar 2018-11-06 12:00:00  7.071068
    2018-11-06 13:00:00       NaN
    2018-11-06 14:00:00  7.071068
    2018-11-06 15:00:00       NaN
    2018-11-06 16:00:00  7.071068
    2018-11-06 17:00:00       NaN
    2018-11-06 18:00:00  7.071068
    2018-11-06 19:00:00       NaN
    2018-11-06 20:00:00  7.071068
foo 2018-11-06 11:00:00  7.071068
    2018-11-06 12:00:00       NaN
    2018-11-06 13:00:00  7.071068
    2018-11-06 14:00:00       NaN
    2018-11-06 15:00:00  7.071068
    2018-11-06 16:00:00       NaN
    2018-11-06 17:00:00  7.071068
    2018-11-06 18:00:00       NaN
    2018-11-06 19:00:00  7.071068

Output of pd.show_versions()

pd.show_versions()

INSTALLED VERSIONS

commit: None
python: 3.6.6.final.0
python-bits: 64
OS: Darwin
OS-release: 16.7.0
machine: x86_64
processor: i386
byteorder: little
LC_ALL: en_US.UTF-8
LANG: en_US.UTF-8
LOCALE: en_US.UTF-8

pandas: 0.23.3
pytest: 3.7.1
pip: 18.0
setuptools: 39.1.0
Cython: None
numpy: 1.14.5
scipy: 1.1.0
pyarrow: None
xarray: None
IPython: 6.4.0
sphinx: 1.7.5
patsy: None
dateutil: 2.7.3
pytz: 2018.5
blosc: None
bottleneck: None
tables: None
numexpr: None
feather: None
matplotlib: 2.2.2
openpyxl: None
xlrd: None
xlwt: None
xlsxwriter: None
lxml: None
bs4: None
html5lib: 1.0.1
sqlalchemy: 1.2.12
pymysql: None
psycopg2: None
jinja2: 2.10
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: None

Duplicate
Source
picture trianta2

Most helpful comment

@otmezger I'm avoiding agg and invoking individual methods until this is resolved. Not the best solution unfortunately.

picture trianta2 on 16 Jan 2019
👍3

All 6 comments

this is fixed in master pls try

jreback picture jreback on 6 Nov 2018

I've installed master from source and now I get this error when invoking agg:

RecursionError: maximum recursion depth exceeded while calling a Python object

however invoking mean and std individually work fine. Here's a chunk of stack trace:

  File "REDACTED/envs/py36/lib/python3.6/site-packages/pandas-0.24.0.dev0+936.gcf4c0b616-py3.6-macosx-10.7-x86_64.egg/pandas/core/base.py", line 608, in _aggregate_multiple_funcs
    results.append(colg.aggregate(arg))

  File "REDACTED/envs/py36/lib/python3.6/site-packages/pandas-0.24.0.dev0+936.gcf4c0b616-py3.6-macosx-10.7-x86_64.egg/pandas/core/resample.py", line 258, in aggregate
    result, how = self._aggregate(func, *args, **kwargs)

  File "REDACTED/envs/py36/lib/python3.6/site-packages/pandas-0.24.0.dev0+936.gcf4c0b616-py3.6-macosx-10.7-x86_64.egg/pandas/core/base.py", line 562, in _aggregate
    _axis=_axis), None

  File "REDACTED/envs/py36/lib/python3.6/site-packages/pandas-0.24.0.dev0+936.gcf4c0b616-py3.6-macosx-10.7-x86_64.egg/pandas/core/base.py", line 608, in _aggregate_multiple_funcs
    results.append(colg.aggregate(arg))

  File "REDACTED/envs/py36/lib/python3.6/site-packages/pandas-0.24.0.dev0+936.gcf4c0b616-py3.6-macosx-10.7-x86_64.egg/pandas/core/resample.py", line 258, in aggregate
    result, how = self._aggregate(func, *args, **kwargs)

  File "REDACTED/envs/py36/lib/python3.6/site-packages/pandas-0.24.0.dev0+936.gcf4c0b616-py3.6-macosx-10.7-x86_64.egg/pandas/core/base.py", line 562, in _aggregate
    _axis=_axis), None

  File "REDACTED/envs/py36/lib/python3.6/site-packages/pandas-0.24.0.dev0+936.gcf4c0b616-py3.6-macosx-10.7-x86_64.egg/pandas/core/base.py", line 608, in _aggregate_multiple_funcs
    results.append(colg.aggregate(arg))

  File "REDACTED/envs/py36/lib/python3.6/site-packages/pandas-0.24.0.dev0+936.gcf4c0b616-py3.6-macosx-10.7-x86_64.egg/pandas/core/resample.py", line 258, in aggregate
    result, how = self._aggregate(func, *args, **kwargs)

  File "REDACTED/envs/py36/lib/python3.6/site-packages/pandas-0.24.0.dev0+936.gcf4c0b616-py3.6-macosx-10.7-x86_64.egg/pandas/core/base.py", line 562, in _aggregate
    _axis=_axis), None

  File "REDACTED/envs/py36/lib/python3.6/site-packages/pandas-0.24.0.dev0+936.gcf4c0b616-py3.6-macosx-10.7-x86_64.egg/pandas/core/base.py", line 607, in _aggregate_multiple_funcs
    subset=obj.iloc[:, index])

  File "REDACTED/envs/py36/lib/python3.6/site-packages/pandas-0.24.0.dev0+936.gcf4c0b616-py3.6-macosx-10.7-x86_64.egg/pandas/core/indexing.py", line 1498, in __getitem__
    return self._getitem_tuple(key)

  File "REDACTED/envs/py36/lib/python3.6/site-packages/pandas-0.24.0.dev0+936.gcf4c0b616-py3.6-macosx-10.7-x86_64.egg/pandas/core/indexing.py", line 2149, in _getitem_tuple
    return self._getitem_lowerdim(tup)

  File "REDACTED/envs/py36/lib/python3.6/site-packages/pandas-0.24.0.dev0+936.gcf4c0b616-py3.6-macosx-10.7-x86_64.egg/pandas/core/indexing.py", line 990, in _getitem_lowerdim
    section = self._getitem_axis(key, axis=i)

  File "REDACTED/envs/py36/lib/python3.6/site-packages/pandas-0.24.0.dev0+936.gcf4c0b616-py3.6-macosx-10.7-x86_64.egg/pandas/core/indexing.py", line 2236, in _getitem_axis
    return self._get_loc(key, axis=axis)

  File "REDACTED/envs/py36/lib/python3.6/site-packages/pandas-0.24.0.dev0+936.gcf4c0b616-py3.6-macosx-10.7-x86_64.egg/pandas/core/indexing.py", line 153, in _get_loc
    return self.obj._ixs(key, axis=axis)

  File "REDACTED/envs/py36/lib/python3.6/site-packages/pandas-0.24.0.dev0+936.gcf4c0b616-py3.6-macosx-10.7-x86_64.egg/pandas/core/frame.py", line 2764, in _ixs
    result = self._box_col_values(values, label)

  File "REDACTED/envs/py36/lib/python3.6/site-packages/pandas-0.24.0.dev0+936.gcf4c0b616-py3.6-macosx-10.7-x86_64.egg/pandas/core/frame.py", line 3229, in _box_col_values
    return klass(values, index=self.index, name=items, fastpath=True)

  File "REDACTED/envs/py36/lib/python3.6/site-packages/pandas-0.24.0.dev0+936.gcf4c0b616-py3.6-macosx-10.7-x86_64.egg/pandas/core/series.py", line 260, in __init__
    self.name = name

  File "REDACTED/envs/py36/lib/python3.6/site-packages/pandas-0.24.0.dev0+936.gcf4c0b616-py3.6-macosx-10.7-x86_64.egg/pandas/core/generic.py", line 4742, in __setattr__
    object.__getattribute__(self, name)

  File "REDACTED/envs/py36/lib/python3.6/site-packages/pandas-0.24.0.dev0+936.gcf4c0b616-py3.6-macosx-10.7-x86_64.egg/pandas/core/series.py", line 382, in name
    return self._name

  File "REDACTED/envs/py36/lib/python3.6/site-packages/pandas-0.24.0.dev0+936.gcf4c0b616-py3.6-macosx-10.7-x86_64.egg/pandas/core/generic.py", line 4726, in __getattr__
    return object.__getattribute__(self, name)

RecursionError: maximum recursion depth exceeded while calling a Python object


pd.show_versions()

INSTALLED VERSIONS

commit: None
python: 3.6.6.final.0
python-bits: 64
OS: Darwin
OS-release: 16.7.0
machine: x86_64
processor: i386
byteorder: little
LC_ALL: en_US.UTF-8
LANG: en_US.UTF-8
LOCALE: en_US.UTF-8

pandas: 0.24.0.dev0+936.gcf4c0b616
pytest: 3.7.1
pip: 18.0
setuptools: 39.1.0
Cython: 0.29
numpy: 1.14.5
scipy: 1.1.0
pyarrow: None
xarray: None
IPython: 6.4.0
sphinx: 1.7.5
patsy: None
dateutil: 2.7.3
pytz: 2018.5
blosc: None
bottleneck: None
tables: None
numexpr: None
feather: None
matplotlib: 2.2.2
openpyxl: None
xlrd: None
xlwt: None
xlsxwriter: None
lxml: None
bs4: None
html5lib: 1.0.1
sqlalchemy: 1.2.12
pymysql: None
psycopg2: None
jinja2: 2.10
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: None
gcsfs: None

trianta2 picture trianta2 on 6 Nov 2018

@trianta2 I can reproduce your second issue. Can you please create that as a separate issue though? Typically would like to keep every issue related to one issue at a time so as to keep things less confusing when looking back.

Closing this for now as resolved on master but again please open up your second problem as a new issue

WillAyd picture WillAyd on 8 Nov 2018

Sure, see https://github.com/pandas-dev/pandas/issues/23568

trianta2 picture trianta2 on 8 Nov 2018

@trianta2 I'm having the same issue with 

dfr = df_sel.groupby('client_id').resample('5T').agg({
            'column_A': 'last',
            'column_B': 'last',
            'seen_time': ['min','max']
        })

have you found a workaround other than installing the release candidate for pandas 0.24?

otmezger picture otmezger on 15 Jan 2019

@otmezger I'm avoiding agg and invoking individual methods until this is resolved. Not the best solution unfortunately.

trianta2 picture trianta2 on 16 Jan 2019
👍3
Was this page helpful?
0 / 5 - 0 ratings

Related issues

matthiasroder picture matthiasroder  路  3Comments
scls19fr picture scls19fr  路  3Comments
marcelnem picture marcelnem  路  3Comments
songololo picture songololo  路  3Comments
hiiwave picture hiiwave  路  3Comments