groupby + shift drops group columns when as_index is False

[tjader]

Using groupby + shift seems to have changed behaviour in 0.17 and 0.18 compared to 0.16.
With as_index=False, I would expect the columns that the groupby is made over to remain in the output dataframe, but they are no longer present.

Code Sample, a copy-pastable example if possible

>>>import pandas as pd
>>>import numpy as np
>>>df = pd.DataFrame({'A': [1.0, 1, 1, 2, 2, 3], 'B': [1.0, 2, 3, 4, 5, 6]})
>>>df
     A    B
0  1.0  1.0
1  1.0  2.0
2  1.0  3.0
3  2.0  4.0
4  2.0  5.0
5  3.0  6.0
>>>df_sorted.groupby('A', as_index=False).shift(1)
     B
0  NaN
1  1.0
2  2.0
3  NaN
4  4.0
5  NaN

Expected Output

>>>pd.DataFrame({'A':[np.nan, 1, 1, np.nan, 2, np.nan], 'B':[np.nan, 1, 2, np.nan, 4, np.nan]})
     A    B
0  NaN  NaN
1  1.0  1.0
2  1.0  2.0
3  NaN  NaN
4  2.0  4.0
5  NaN  NaN

output of pd.show_versions()

INSTALLED VERSIONS
------------------
commit: None
python: 2.7.11.final.0
python-bits: 64
OS: Windows
OS-release: 10
machine: AMD64
processor: Intel64 Family 6 Model 58 Stepping 9, GenuineIntel
byteorder: little
LC_ALL: None
LANG: None

pandas: 0.18.1
nose: 1.3.7
pip: 8.1.2
setuptools: 23.0.0
Cython: 0.24
numpy: 1.10.4
scipy: 0.17.1
statsmodels: None
xarray: 0.7.2
IPython: None
sphinx: None
patsy: None
dateutil: 2.4.1
pytz: 2016.4
blosc: None
bottleneck: 1.0.0
tables: 3.2.2
numexpr: 2.5.2
matplotlib: 1.5.1
openpyxl: 2.3.2
xlrd: None
xlwt: None
xlsxwriter: None
lxml: None
bs4: None
html5lib: None
httplib2: None
apiclient: None
sqlalchemy: 1.0.13
pymysql: None
psycopg2: None
jinja2: 2.8
boto: 2.40.0
pandas_datareader: None

I have also confirmed the issue on an similar install Linux installation using pandas 0.18.1

[halfmoonhalf]

I found the same issue with groupby.shift and groupby.apply. Depending on different arg, the result is pretty confusing: sometimes the groupby columns is dropped, sometimes it is still a column.
code:

import pandas as pd

df = pd.DataFrame({
    'name': ['a', 'a', 'b', 'b'],
    'col_1': [0, 1, 2, 3],
    'col_2': [4, 5, 6, 7]
})

print df

print '+++++++++++++++++++++++++++++++++++++++++++++++++++'
print '+++++++++++++++++++++++++++++++++++++++++++++++++++'


def f(group):
    return group.ewm(alpha=0.5).mean()


print df.groupby('name', as_index=True).apply(f)
print '-' * 20
print df.groupby('name', as_index=False).apply(f)
print '-' * 20
print df.groupby('name', as_index=True)['col_1'].apply(f)
print '-' * 20
print df.groupby('name', as_index=False)['col_1'].apply(f)

print '+++++++++++++++++++++++++++++++++++++++++++++++++++'
print '+++++++++++++++++++++++++++++++++++++++++++++++++++'

print df.groupby('name', as_index=True).shift(1)
print '-' * 20
print df.groupby('name', as_index=False).shift(1)
print '-' * 20
print df.groupby('name', as_index=True)['col_1'].shift(1)
print '-' * 20
print df.groupby('name', as_index=False)['col_1'].shift(1)
print '-' * 20

output is:

   col_1  col_2 name
0      0      4    a
1      1      5    a
2      2      6    b
3      3      7    b
+++++++++++++++++++++++++++++++++++++++++++++++++++
+++++++++++++++++++++++++++++++++++++++++++++++++++
      col_1     col_2 name  # <<-------------------------------name is still a column?
0  0.000000  4.000000    a
1  0.666667  4.666667    a
2  2.000000  6.000000    b
3  2.666667  6.666667    b
--------------------
      col_1     col_2 name
0  0.000000  4.000000    a
1  0.666667  4.666667    a
2  2.000000  6.000000    b
3  2.666667  6.666667    b
--------------------
0    0.000000  # <<-------------------------------name should be an index?
1    0.666667
2    2.000000
3    2.666667
Name: col_1, dtype: float64
--------------------
0  0    0.000000  # <<-------------------------------multiindex now?
   1    0.666667
1  2    2.000000
   3    2.666667
Name: col_1, dtype: float64
+++++++++++++++++++++++++++++++++++++++++++++++++++
+++++++++++++++++++++++++++++++++++++++++++++++++++
   col_1  col_2
0    NaN    NaN
1    0.0    4.0
2    NaN    NaN
3    2.0    6.0
--------------------
   col_1  col_2
0    NaN    NaN
1    0.0    4.0
2    NaN    NaN
3    2.0    6.0
--------------------
0    NaN
1    0.0
2    NaN
3    2.0
Name: col_1, dtype: float64
--------------------
   col_1  # <<-------------------------------why is this a dataframe?
0    NaN
1    0.0
2    NaN
3    2.0
--------------------

pd.show_versions()

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

pandas: 0.20.3
pytest: 2.8.5
pip: 9.0.1
setuptools: 20.3
Cython: 0.23.4
numpy: 1.13.1
scipy: 0.19.1
xarray: None
IPython: 4.1.2
sphinx: 1.3.5
patsy: 0.4.0
dateutil: 2.5.1
pytz: 2016.2
blosc: None
bottleneck: 1.0.0
tables: 3.2.2
numexpr: 2.6.2
feather: None
matplotlib: 2.0.2
openpyxl: 2.3.2
xlrd: 0.9.4
xlwt: 1.0.0
xlsxwriter: 0.8.4
lxml: 3.6.0
bs4: 4.4.1
html5lib: None
sqlalchemy: 1.0.12
pymysql: None
psycopg2: None
jinja2: 2.8
s3fs: None
pandas_gbq: None
pandas_datareader: None

[MrRobot2211]

I am having the exact same issue. Is tere any workaround?

[vladu]

Ran into the same issue today. There is a workaround. If you reset_index before the groupby, then assign still works correctly after the shift. So, if you're only trying to shift one column, you can do:

frm = frm.assign(shifted_val=frm.groupby('key').shift(1)['val'])

Or, if you're trying to shift the whole frame, you can assign the group col back:

shifted_frm = frm.groupby('key').shift(1)
shifted_frm = shifted_frm.assign(key=frm['key'])

[vladu]

Also, not sure if anything changed since the original report, but now in v0.25, the grouping cols disappear after a groupby + shift regardless of the value of as_index.

[thoughtfuldata]

has there been any progress on this? I'm getting the same issue as @vladu

[jreback]

@thoughtfuldata you or anyone else can solve this by contributing a pull request