I have a column of dates. I need to filter out those dates that fall between today’s date and end of the current month. If the dates fall between these dates then the next column showns “Y”
| Date | Column |
|---|---|
| 01/02/2021 | |
| 03/02/2021 | |
| 31/03/2021 | Y |
| 01/03/2021 | |
| 07/03/2021 | Y |
| 08/03/2021 | Y |
Since today’s date is 07/03/2021 three dates fall between 07/03/2021 and 31/03/2021.
Advertisement
Answer
Convert into datetime column using specific time format and compare with today‘s timestamp
df.Date = pd.to_datetime(df.Date, format='%d/%m/%Y')
today = pd.to_datetime('today').normalize()
end_of_month = today + pd.tseries.offsets.MonthEnd(1)
df['Column'] = np.where((df.Date >= today) & (df.Date <= end_of_month), 'Y', '')
Output
Date Column 0 2021-02-01 1 2021-02-03 2 2021-03-31 Y 3 2021-03-01 4 2021-03-07 Y 5 2021-03-08 Y 6 2021-04-02