Find Last Available Date in Pandas Data Frame

user_10083382 0
Viewed 361

Suppose that I have a Pandas DataFrame as below:

+------------+-------+
|    Date    | Price |
+------------+-------+
| 01/01/2021 |    10 |
| 01/02/2021 |    20 |
| 01/03/2021 |    30 |
| 01/05/2021 |    40 |
| 01/08/2021 |    20 |
| 01/09/2021 |    10 |
+------------+-------+

The above data frame can be generated using code below:

df = pd.DataFrame({'Date': ['2021-01-01', '2021-01-02', '2021-01-03', '2021-01-05', '2021-01-08', '2021-01-09'],
                   'Price': [10, 20, 30, 40, 20, 10]})
df['Date'] = pd.to_datetime(df['Date'])

Now given a date stored in variable end_date. The first step is to find if the date exists in the data frame. It can be done using the below code:

if end_date in df.Date.values:
    pass
else:
    # find last available date.

What would be the most elegant way to find the last available date in data frame.

E.g. if end_date = '2021-01-10'. Since it does not exists in data frame I want end_date value to be set at 2021-01-09. Similarly, if end_date = 2021-01-07 I want end_date value to be set at 2021-01-05.

Alternatively if end_date = 2021-01-08 end_date won't be overwritten and would remain as is i.e. end_date = 2021-01-08.

pandas datetime python
2 Answers

You could try with loc and iloc:

end_date = "2021-01-07"
print(df.loc[df['Date'] <= end_date, 'Date'].iloc[-1].date())

Output:

2021-01-05

Or:

end_date = "2021-01-07"
print(df.loc[df['Date'] <= end_date, 'Date'].max().date())
user_87083640

The other answers are assuming the dates are always in order in your dataframe.

Since your dates are sortable, you can just use comparison operators (note that this will work even if you keep them as strings, as the format you are using is lexicographically sortable).

To get the last available date, first filter out dates after end_date and then find the max:

end_date = df[df['Date'] <= end_date]['Date'].max()
user_148105110
Related