1

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.

Improve this question
2
  • Is the column labelled Column to be added, or does that column already exist in the data frame? Commented Mar 7, 2021 at 6:19
  • That's a new Column Commented Mar 7, 2021 at 6:23

1 Answer 1

Reset to default
3

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
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Good answer, but OP requires between today and the "end of the current month". This includes future dates beyond the current month.
Totally missed that. Thanks for reminding @mhawke
df.date column is in 44270 (float64) format and with pd.to_datetime it gets converted to 01-01-1970 00:00:00 . It does not take any format
With the data that you provided, the code works. With float64 format it cannot look like the sample data given. I can't help until you provide correct data.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.