Filling in with the last entry of the group

Question

Say I have the following dataframe:

> 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

If I do:

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

I get:

KeyError: -1

Why? I thought I could pass any function to apply on a group.

What I want is the following (get the last entry in every group):

> 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

Thanks @EdChum. Not quite. I want to fill a dataframe something with the same exact index — Amelio Vazquez-Reina, Jul 30 '14 at 20:28
`df.groupby(level='A').transform(pd.Series.last)` does this work? — EdChum, Jul 30 '14 at 20:30
@EdChum I just tried that but I get: `TypeError: last() missing 1 required positional argument: 'offset'` — Amelio Vazquez-Reina, Jul 30 '14 at 20:31
how about `last = lambda x: x.iloc[-1] df.groupby(level='A').transform(last)`? — EdChum, Jul 30 '14 at 20:33
Thanks @EdChum. That doesn't throw an error, but it simply returns the original dataframe. Strange — Amelio Vazquez-Reina, Jul 30 '14 at 20:34
I was looking at this: http://stackoverflow.com/questions/15001237/how-to-apply-first-and-last-functions-to-columns-while-using-group-by-in-pan and the docs: http://pandas.pydata.org/pandas-docs/stable/groupby.html#transformation. You definitely want to use transform in my opinion — EdChum, Jul 30 '14 at 20:36
@EdCHum I agree about `transform`, although I thought you can mimic the behavior of `transform` with apply (which is more generic) — Amelio Vazquez-Reina, Jul 30 '14 at 20:37
@EdChum Turns out this is a bug: https://github.com/pydata/pandas/issues/7883 I just created a ticket. — Amelio Vazquez-Reina, Jul 30 '14 at 21:14
Ah, that's good. Hopefully it'll get fixed in next release, there is probably a workaround I imagine — EdChum, Jul 30 '14 at 21:15
For future reference: If you paste your dataframe, please paste it as `df.reset_index()` - otherwise, `pd.read_clipboard()` will not function properly. — FooBar, Jul 31 '14 at 11:58

Filling in with the last entry of the group

0 Answers0