How to check a date column falls in a specific month in pandas?

Question

I'm processing a file which has a date coulmn. I want to check that file contains records for the last month's all days.

That is, if I process the September file, I would like to know which contain rows for September 1st -September 30th. Check data of the previous month. Not for current month. How can I do this in Pandas?

    def is_delivery_ready_to_process():

        dateCols = ['Document Date']
        data = pd.read_excel(os.path.join(file_path, f), parse_dates=dateCols,
                                   dayfirst=True, sheet_name='Refined')
.....?

score 1 · Answer 1 · answered Oct 10 '19 at 01:24

If I understand your question correctly, you want to filter rows that are within a specific month?

First convert your date string to a datetime object and then do the filtering:

import pandas as pd
from datetime import datetime


d = {'date': ["Sep 1 2005", "Sep 1 2005", "Nov 1 2005", "Dec 1 2005", "Apr 1 2005",  "Aug 1 2005", "Aug 1 2005"], 'group': ["A", "A", "B", "B", "C", "C", "C"], "value": [5, 6, 7, 8, 9, 10, 11]}

df = pd.DataFrame(data= d)
df["datetime"] = pd.to_datetime(df['date'], format='%b %d %Y')

startdate = datetime(2005, 9, 1)
enddate = datetime(2005, 10, 1)

df[(df.datetime >= startdate) & (df.datetime < enddate)]

Output:

   date       group value   datetime
0   Sep 1 2005  A   5   2005-09-01
1   Sep 1 2005  A   6   2005-09-01

score 0 · Accepted Answer · answered Oct 10 '19 at 01:33

First you have to get the start date and end date of previous month

find today.
use that to find the first day of this month.
use timedelta to get the last day of the previous month.
use replace(day=1) to get first day of the previous month

import datetime

today = datetime.date.today()
previous_month_end = today.replace(day=1) - datetime.timedelta(days=1)
previous_month_start = previous_month_end.replace(day=1)
print(previous_month_start) # 2019-09-01
print(previous_month_end) # 2019-09-30

Next, select dataframe rows between 2 dates using a boolean mask, for example

import pandas as pd
df = pd.DataFrame(columns=['Document Date'],
                  data=[datetime.date(2019, 10, 1),
                        datetime.date(2019, 9, 1),
                        datetime.date(2019, 9, 3)])

df.loc[(df['Document Date'] >= previous_month_start) & (df['Document Date'] <= previous_month_end)]

gives you

  Document Date
1    2019-09-01
2    2019-09-03

How to check a date column falls in a specific month in pandas?

2 Answers2