Find index of most recent DateTime in Pandas dataframe

user_14125020 0
Viewed 702

I have a dataframe that includes a column of datetimes, past and future. Is there a way to find the index of the most recent datetime?

I can not assume that each datetime is unique, nor that they are in order.

In the event that the most recent datetime is not unique, all the relevant indeces should be returned.

import pandas as pd
from datetime import datetime as dt

df = pd.read_excel('file.xlsx')
# Create datetime column
df['datetime'] = pd.to_datetime(df['Date'].astype(str) + ' ' + df['Time'].astype(str))
print(df['datetime'])

Out[1]: 
0   2021-02-13 09:00:00
1   2021-02-13 11:00:00
2   2021-02-13 12:00:00
3   2021-02-13 15:00:00
4   2021-02-13 18:00:00
5   2021-02-13 16:45:00
6   2021-02-13 19:00:00
7   2021-02-13 19:00:00
8   2021-02-13 20:30:00
9   2021-02-14 01:30:00
Name: datetime, dtype: datetime64[ns]
pandas datetime python
2 Answers

unique datetimes...

a convenient option would be to use get_loc method of DatetimeIndex. Ex:

import pandas as pd

df = pd.DataFrame({'datetime': pd.to_datetime(['2021-01-01', '2021-02-01', '2021-02-14'])})

# today is 2021-2-13, so most recent would be 2021-2-1:
pd.DatetimeIndex(df['datetime']).get_loc(pd.Timestamp('now'), method='pad')
# 1

You could also set the datetime column as index, simplifying the above to df.index.get_loc(pd.Timestamp('now'), method='pad')

duplicates in datetime column...

The datetime index method shown above won't work here. Instead, you can obtain the value first an then get the indices:

df = pd.DataFrame({'datetime': pd.to_datetime(['2021-02-01', '2021-01-01', '2021-02-01', '2021-02-14'])})

# most recent datetime would be 2021-2-1 at indices 0 and 2
mr_date = df['datetime'].loc[(df['datetime'] - pd.Timestamp('now') <= pd.Timedelta(0))].max()
mr_idx = df.index[df['datetime'] == mr_date]

mr_idx
# Int64Index([0, 2], dtype='int64')
user_101974180

For excluding the future dates, you can compare with todays date then filter them out, then take idxmax

df.loc[(pd.to_datetime('today').day -df['datetime'].dt.day).ge(0),'datetime'].idxmax()
user_98406370
Related