Collapsing entries with duplicate index values in DataFrame

Question

import pandas as pd
bids = [100, 101, 101, 102]
offers = [101, 102, 102.25, 103]
data = {'bids': bids, 'offers': offers}
index = [0, 1, 1, 2]
df = pd.DataFrame(data=data, index=index)
print df

   bids  offers
0   100  101.00
1   101  102.00
1   101  102.25
2   102  103.00

How can I reindex df so that the latest value for a given index in each column is used? In this example, I'd want index 1 to have [101, 102.25]

Answer 1

You can call reset_index and then drop_duplicates and pass param take_last=True and then set the index back

In [181]:
df.reset_index().drop_duplicates('index',take_last=True).set_index('index')

Out[181]:
       bids  offers
index              
0       100  101.00
1       101  102.25
2       102  103.00

A more elegant way is to groupby on the index and call last:

In [183]:    
df.groupby(df.index).last()

Out[183]:
   bids  offers
0   100  101.00
1   101  102.25
2   102  103.00

Answer 2

From what you have described, I take a wild guess you want the "last" row in the result. In this case you can simply use .tail:

In [1]: %paste
import pandas as pd
bids = [100, 101, 101, 102]
offers = [101, 102, 102.25, 103]
data = {'bids': bids, 'offers': offers}
index = [0, 1, 1, 2]
df = pd.DataFrame(data=data, index=index)

In [2]: df
Out[2]:
   bids  offers
0   100  101.00
1   101  102.00
1   101  102.25
2   102  103.00

In [3]: df.ix[1]
Out[3]:
   bids  offers
1   101  102.00
1   101  102.25

In [4]: df.ix[1].tail(1)
Out[4]:
   bids  offers
1   101  102.25

from the docs:

DataFrame.tail(n=5)¶

Returns last n rows