Python pandas - get index of rows

Question

I have a dataframe which looks like this:

df_raw.head()
    Ticker   FY Periodicity  Measure     Val                Date
0  BP9DL90  2009         ANN     CPX  1000.00 2008-03-31 00:00:00
1  BP9DL90  2010         ANN     CPX   600.00 2009-03-25 00:00:00
2  BPRTD89  2010         ANN     CPX   600.00 2009-09-16 00:00:00
3  BP9DL90  2011         ANN     CPX   570.00 2010-03-17 00:00:00
4  BPSRD74  2011         ANN     GRM    57.09 2010-09-06 00:00:00

[5 rows x 6 columns]

How do I filter out the table such that I get the rows with the max Date for each Ticker (irrespective of other column values)? The Date is in the Timestamp format.

Thanks

behzad.nouri · Accepted Answer · 2014-09-17 13:11:19Z

2

use idxmax:

>>> df['Date'] = pd.to_datetime(df['Date']) # in case `Date` column is string
>>> i = df.groupby('Ticker')['Date'].idxmax().values
>>> df.loc[i,:]
    Ticker    FY Periodicity Measure     Val       Date
3  BP9DL90  2011         ANN     CPX  570.00 2010-03-17
2  BPRTD89  2010         ANN     CPX  600.00 2009-09-16
4  BPSRD74  2011         ANN     GRM   57.09 2010-09-06

answered Sep 17, 2014 at 13:11

behzad.nouri

78.4k18 gold badges130 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3294195 Over a year ago

Thanks! Is it possible to get the rows containing the max Date for a given combination of Ticker and Measure?

behzad.nouri Over a year ago

@user3294195 you can group by ['Ticker', 'Measure']

Collectives™ on Stack Overflow

Python pandas - get index of rows

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related