In column dataframe, how do I find the date just before a given date-CodePudding

I have the following DF :

Date
01/07/2022
10/07/2022
20/07/2022

The date x is

12/07/2022

So basically the function should return

10/07/2022

I am trying to avoid looping over the whole column but I don't know how to specify that I want the max date before a given date.

max(DF['Dates']) #Returns 20/07/2022

CodePudding user response：

Try this:

d = '12/07/2022'
f = '%d/%m/%Y'

(pd.to_datetime(df['Date'],format=f)
.where(lambda x: x.lt(pd.to_datetime(d,format=f)))
.max())

CodePudding user response：

You can filter dates by index:

df[df.Date < pd.to_datetime('12/07/2022')]

Then find max:

max(df[df.Date < pd.to_datetime('12/07/2022')].Date)

CodePudding user response：

# Setting some stuff up
Date = ["01/07/2022", "10/07/2022", "20/07/2022"]
df = pd.DataFrame({"Date":Date})
df.Date = pd.to_datetime(df.Date, format='%d/%m/%Y')
target_date = pd.to_datetime("12/07/2022", format='%d/%m/%Y')



df = df.sort_values(by=["Date"]) # Sort by date
# Find all dates that are before target date, then choose the last one (i.e. the most recent one)
df.Date[df.Date < target_date][-1:].dt.date.values[0]

Output:

datetime.date(2022, 7, 10)