Unable to retrieve same-index dataframe with the last item per group

[amelio-vazquez-reina]

The original thread is here.

> df
                   C         D  E
A    B                           
bar  one   -1.350006  0.260339  2
     three -0.236451 -0.056614  0
flux six   -0.515571 -0.155078  1
     three -0.365032  1.055669  2
foo  five   1.145811  1.514191  0
     one   -0.108427 -0.643262  2
     two    0.286897  0.405798  2
     two   -1.452286  1.149264  2

The goal is to get a dataframe with the same index with each entry having the last value in the groups associated with A, i.e.

> df
                   C         D  E
A    B                           
bar  one   -0.236451 -0.056614  2
     three -0.236451 -0.056614  2
flux six   -0.365032  1.055669  2
     three -0.365032  1.055669  2
foo  five  -1.452286  1.149264  2
     one   -1.452286  1.149264  2
     two   -1.452286  1.149264  2
     two   -1.452286  1.149264  2

However, none of the following work:

• df.groupby(level='A').apply(lambda x: x[-1])
• df.groupby(level='A').transform(pd.Series.last)
• df.groupby(level='A').transform(lambda x: x.iloc[-1])

[cpcloud]

There's one thing you didn't try:

In [37]: df
Out[37]:
                 C       D       E
A    B
bar  one   -0.9384  0.3450 -0.8715
     three  1.1107  1.9896 -0.4797
flux six    0.5059  0.0494  0.0993
     three  0.3148 -0.7710  0.2409
foo  five   2.3012 -0.3600 -0.9216
     one   -0.3965 -1.0410 -0.0600
     two    1.2348  0.6059 -0.8019
     two    0.0912  0.5812 -0.2724

In [38]: df.groupby(level='A').transform(lambda x: x.iloc[-1])
Out[38]:
                 C       D       E
A    B
bar  one    1.1107  1.9896 -0.4797
     three  1.1107  1.9896 -0.4797
flux six    0.3148 -0.7710  0.2409
     three  0.3148 -0.7710  0.2409
foo  five   0.0912  0.5812 -0.2724
     one    0.0912  0.5812 -0.2724
     two    0.0912  0.5812 -0.2724
     two    0.0912  0.5812 -0.2724

[cpcloud]

These lines from EdChum's answer

last = lambda x: x.iloc[-1]
df.groupby(level='A').transform(last)

are not the same as doing

df.groupby(level='A').transform(last = lambda x: x.iloc[-1])

In the second one you're passing a keyword argument to transform which will of course raise a TypeError because it expects at least two positional arguments and you're only passing a keyword argument and no positionals (except the implicit self that comes with every method)

[amelio-vazquez-reina]

Thanks @cpcloud I actually mistyped it at the beginning of this issue. I did try (and just tried again):

last = lambda x: x.iloc[-1]
df.groupby(level='A').transform(last)

but I do not get the desired dataframe. I get the original frame.

[cpcloud]

@ribonoous Can you show a reproducible example? Hard to debug without seeing the data. Can make random data too, if you can't show the data where it's failing, I just need to be able to reproduce it.

[amelio-vazquez-reina]

Thanks @cpcloud . The example I included above is from my current session. I understand that your answer should work, but it's literally not working on my machine as I type it:

N.In <121>: df
N.Out<121>: 
                   C         D  E
A    B                           
bar  one   -0.507507 -0.719877  0
     three  0.735206  0.476516  0
flux six    0.347799 -0.201347  2
     three -0.271447 -0.936737  2
foo  five   0.930560 -0.237395  2
     one   -1.759220 -0.564822  1
     two    1.759313  0.277731  2
     two   -0.506662  0.758981  1

N.In <122>: df.groupby(level='A').transform(lambda x: x.iloc[-1])
N.Out<122>: 
                   C         D  E
A    B                           
bar  one   -0.507507 -0.719877  0
     three  0.735206  0.476516  0
flux six    0.347799 -0.201347  2
     three -0.271447 -0.936737  2
foo  five   0.930560 -0.237395  2
     one   -1.759220 -0.564822  1
     two    1.759313  0.277731  2
     two   -0.506662  0.758981  1

I am on Python 3 in an embedded shell in IPython with the following packages:

$ pip freeze
Cython==0.20.1
DataShape==0.2
Flask==0.10.1
Jinja2==2.7.2
MarkupSafe==0.18
Pillow==2.4.0
PyYAML==3.11
Pygments==1.6
SQLAlchemy==0.9.4
Sphinx==1.2.2
Werkzeug==0.9.6
XlsxWriter==0.5.5
appnope==0.0.6
argcomplete==0.6.7
astropy==0.3.2
backports.ssl-match-hostname==3.4.0.2
beautifulsoup4==4.3.1
binstar==0.5.3
bitarray==0.8.1
blaze==0.5
blz==0.6.2
bokeh==0.4.4
cdecimal==2.3
colorama==0.2.7
configobj==5.0.5
docutils==0.11
elpy==1.4.2
flake8==2.2.2
future==0.12.1
greenlet==0.4.2
h5py==2.3.0
ipython==2.1.0
itsdangerous==0.24
jdcal==1.0
llvmpy==0.12.6
lpsolve55==5.5.2.0
lxml==3.3.5
matplotlib==1.3.1
mccabe==0.2.1
mock==1.0.1
multipledispatch==0.4.3
networkx==1.8.1
nose==1.3.3
numba==0.13.2
numexpr==2.3.1
numpy==1.8.1
openpyxl==1.8.5
pandas==0.14.1
patsy==0.2.1
pep8==1.5.7
ply==3.4
psutil==2.1.1
psycopg2==2.5.3
py==1.4.20
py4j==0.8.1
pycosat==0.6.1
pycparser==2.10
pycrypto==2.6.1
pyflakes==0.8.1
pymc==2.3.4
pyparsing==2.0.1
pytest==2.5.2
python-dateutil==2.1
pytz==2014.4
pyzmq==14.3.0
redis==2.9.1
requests==2.3.0
rope-py3k==0.9.4-1
runipy==0.1.0
scikit-image==0.10.0
scikit-learn==0.15.0
scipy==0.14.0
seaborn==0.4.dev
six==1.7.3
statsmodels==0.5.0
sympy==0.7.5
tables==3.1.1
tornado==3.2.1
ujson==1.33
xlrd==0.9.3

[amelio-vazquez-reina]

@cpcloud I just tried it on a fresh IPython session (no terminal embedded):

def create_random_multi_index():
  df = pd.DataFrame({'A' : ['foo', 'bar', 'foo', 'bar',
                            'foo', 'flux', 'foo', 'flux'],  
                     'B' : ['one', 'one', 'two', 'three',
                            'two', 'six', 'five', 'three'],
                     'C' : randn(8), 'D' : randn(8), 'E': randint(0,3, size=(8,))})
  df.set_index(['A', 'B'], inplace=True)
  df.sort_index(inplace=True)
  return df

In [8]: df = create_random_multi_index()

In [9]: df
Out[9]: 
                   C         D  E
A    B                           
bar  one    0.113720  1.000103  1
     three  0.778840 -0.546913  0
flux six    1.688509  1.541020  2
     three  2.973229 -0.063245  1
foo  five   0.208310  1.268056  0
     one    0.404996 -1.332461  2
     two   -1.038567  0.376829  1
     two    1.528148  1.386106  0

In [10]: df.groupby(level='A').transform(lambda x: x.iloc[-1])
Out[10]: 
                   C         D  E
A    B                           
bar  one    0.113720  1.000103  1
     three  0.778840 -0.546913  0
flux six    1.688509  1.541020  2
     three  2.973229 -0.063245  1
foo  five   0.208310  1.268056  0
     one    0.404996 -1.332461  2
     two   -1.038567  0.376829  1
     two    1.528148  1.386106  0

[cpcloud]

ok let me try

[cpcloud]

ok now i can repro

[cpcloud]

@ribonoous thx for the example!

[cpcloud]

interesting seems to be related to types