How to Filter DataFrame by Date in Pandas

Here are several approaches to filter rows in Pandas DataFrame by date:

1) Filter rows between two dates

df[(df['date'] > '2019-12-01') & (df['date'] < '2019-12-31')]

2) Filter rows by date in index

df2.loc['2019-12-01':'2019-12-31']

3) Filter rows by date with Pandas query

df.query('20191201 < date < 20191231')

In the next section, you'll see several examples of how to apply the above approaches using simple examples. As a bonus you can find information how to filter rows per month, week, year, quarter etc.

The DataFrame for the examples below is available from Kaggle.

If you like to learn more about how to read Kaggle as a Pandas DataFrame check this article: How to Search and Download Kaggle Dataset to Pandas DataFrame

Note: For all examples we need to convert column date to datetime with method - to_datetime:

df['date'] = pd.to_datetime(df['date'])

Option 1: Filter DataFrame by date in Pandas

To start with a simple example, let's filter the DataFrame by two dates:

• '2019-12-01'
• '2019-12-31'

We would like to get all rows which have date between those two dates.

So filtering the rows which meet the above requirement can be done:

df[(df['date'] > '2019-12-01') & (df['date'] < '2019-12-31')]

result is:

614 rows

Note: The format is YYYY-MM-DD

Note 2: the filtering above is exclusive. If you like to get inclusive filtering just add =:

df[(df['date'] >= '2019-12-01') & (df['date'] <= '2019-12-31')]

Option 2: Filter DataFrame by date using the index

For this example we will change the original index of the DataFrame in order to have a index which is a date:

df = df.set_index('date')

Now having a DataFrame with index which is a datetime we can filter the rows by:

df.loc['2019-12-01':'2019-12-31']

the result is the same:

614 rows

2.1 MultiIndex filtering by date

The example below shows how to do filtering when you have a date in hierarchical index:

import datetime
df3 = df.set_index(['publication', 'date'])
df3[(df3.index.get_level_values('date') > '2019-12-01')]

result:

614 rows

Find all article written on Sunday-s:

df3[df3.index.get_level_values('date').dayofweek == 6]

Option 3: Filter rows by date and pd.Timestamp

You can use pd.Timestamp in order to construct your dates and compare the value with each row. The syntax for creating a date with Pandas is:

pd.Timestamp(2019,12,1)

So the comparison will be:

df[(df['date'] > pd.Timestamp(2019,12,1)) & (df['date'] < pd.Timestamp(2019,12,31))]

You can pass a string like in option 1 and Pandas will do the conversion for you.

This can be useful when conversion is not explicit or you like to have control on the format.

Option 4: Pandas filter rows by date with Query

Pandas offers a simple way to query rows by method query.

The syntax is minimalistic and self-explanatory:

df.query('20191201 < date < 20191231')

result:

614 rows

Option 5: Pandas filter rows by day, month, week, quarter etc

Finally let's check several useful and frequently used filters.

Filter rows by day

df[df['date'].dt.strftime('%Y-%m-%d') == '2019-12-30']

result:

165 rows

Filter rows by day of the week

df[df['date'].dt.dayofweek == 6]

result:

609 rows

Filter rows by month and year

df[df['date'].dt.to_period('M') == '2019-12']

result:

614 rows

Filter rows by month

df[df['date'].dt.month == 12]

result:

614 rows

Filter rows by quarter

df[df['date'].dt.to_period('Q') == '2019Q4']

or

df[df['date'].dt.to_period('Q') == '2019-12']

result:

1915 rows

Filter by year

df[df['date'].dt.to_period('Y') == '2019']

result:

6508 rows

Resources

• Notebook
• Time series / date functionality
• pandas.DataFrame.query
• Indexing and selecting data