Python Pandas: How to Filter DataFrame Rows Based on the Date in Pandas
Working with dates is one of the most common tasks in data analysis. Whether you're analyzing sales trends, tracking user activity, or processing time-series data, you'll frequently need to filter DataFrame rows based on date conditions, selecting data within a specific range, for a particular month, or on certain days of the week.
Pandas provides powerful tools for date-based filtering, but the first critical step is ensuring your date column is in the correct datetime64 format. This guide walks you through converting date strings, then demonstrates multiple methods to filter rows by date with clear examples and outputs.
Preparing Date Data: Converting Strings to Datetime
Different data sources use different date formats (YYYY-MM-DD, DD/MM/YY, MM-DD-YYYY, etc.). Before filtering, convert date strings to Pandas' datetime64 type using pd.to_datetime():
import pandas as pd
df = pd.DataFrame({
"num_posts": [4, 6, 3, 9, 1, 14, 2, 5, 7, 2],
"date": [
"2020-08-09", "2020-08-25", "2020-09-05", "2020-09-12",
"2020-09-29", "2020-10-15", "2020-11-21", "2020-12-02",
"2020-12-10", "2020-12-18"
]
})
# Convert string dates to datetime64
df["date"] = pd.to_datetime(df["date"], format="%Y-%m-%d")
print(df)
print(f"\nDate column dtype: {df['date'].dtype}")
Output:
num_posts date
0 4 2020-08-09
1 6 2020-08-25
2 3 2020-09-05
3 9 2020-09-12
4 1 2020-09-29
5 14 2020-10-15
6 2 2020-11-21
7 5 2020-12-02
8 7 2020-12-10
9 2 2020-12-18
Date column dtype: datetime64[ns]
If you try to filter dates that are still stored as strings, comparisons may produce incorrect results because string comparison is lexicographic, not chronological:
# ❌ Wrong: dates are strings, so comparison is alphabetical
df_str = pd.DataFrame({"date": ["2020-12-01", "2020-09-15", "2020-08-25"]})
filtered = df_str[df_str["date"] > "2020-10-01"]
print(filtered)
This may appear to work for YYYY-MM-DD format, but will fail for formats like DD/MM/YYYY. Always convert to datetime64 first.
Method 1: Filtering with DataFrame.loc[]
The loc[] accessor is the most common and flexible way to filter rows by date. Use boolean conditions to define your date range.
Filter Between Two Dates
import pandas as pd
df = pd.DataFrame({
"num_posts": [4, 6, 3, 9, 1, 14, 2, 5, 7, 2],
"date": pd.to_datetime([
"2020-08-09", "2020-08-25", "2020-09-05", "2020-09-12",
"2020-09-29", "2020-10-15", "2020-11-21", "2020-12-02",
"2020-12-10", "2020-12-18"
])
})
# Filter rows where date is between Sep 1 and Sep 15 (inclusive start, exclusive end)
filtered = df.loc[(df["date"] >= "2020-09-01") & (df["date"] < "2020-09-15")]
print(filtered)
Output:
num_posts date
2 3 2020-09-05
3 9 2020-09-12
Filter After a Specific Date
import pandas as pd
df = pd.DataFrame({
"num_posts": [4, 6, 3, 9, 1, 14, 2, 5, 7, 2],
"date": pd.to_datetime([
"2020-08-09", "2020-08-25", "2020-09-05", "2020-09-12",
"2020-09-29", "2020-10-15", "2020-11-21", "2020-12-02",
"2020-12-10", "2020-12-18"
])
})
# All rows from October onward
filtered = df.loc[df["date"] >= "2020-10-01"]
print(filtered)
Output:
num_posts date
5 14 2020-10-15
6 2 2020-11-21
7 5 2020-12-02
8 7 2020-12-10
9 2 2020-12-18
Filter for an Exact Date
import pandas as pd
df = pd.DataFrame({
"num_posts": [4, 6, 3, 9, 1, 14, 2, 5, 7, 2],
"date": pd.to_datetime([
"2020-08-09", "2020-08-25", "2020-09-05", "2020-09-12",
"2020-09-29", "2020-10-15", "2020-11-21", "2020-12-02",
"2020-12-10", "2020-12-18"
])
})
filtered = df.loc[df["date"] == "2020-09-29"]
print(filtered)
Output:
num_posts date
4 1 2020-09-29
When specifying dates as strings in conditions, Pandas automatically converts them to datetime64 for comparison - so you don't need to wrap them in pd.to_datetime() explicitly.
Method 2: Filtering with the .dt Accessor
The .dt accessor lets you extract individual date components - year, month, day, weekday, hour, etc. - and use them as filter conditions. This is especially useful when you want to filter by a specific month, day of the week, or quarter.
Filter by Month
import pandas as pd
df = pd.DataFrame({
"num_posts": [4, 6, 3, 9, 1, 14, 2, 5, 7, 2],
"date": pd.to_datetime([
"2020-08-09", "2020-08-25", "2020-09-05", "2020-09-12",
"2020-09-29", "2020-10-15", "2020-11-21", "2020-12-02",
"2020-12-10", "2020-12-18"
])
})
# All posts from September (month = 9)
september_posts = df.loc[df["date"].dt.month == 9]
print("September posts:")
print(september_posts)
Output:
September posts:
num_posts date
2 3 2020-09-05
3 9 2020-09-12
4 1 2020-09-29
Filter by Day of the Week
import pandas as pd
df = pd.DataFrame({
"num_posts": [4, 6, 3, 9, 1, 14, 2, 5, 7, 2],
"date": pd.to_datetime([
"2020-08-09", "2020-08-25", "2020-09-05", "2020-09-12",
"2020-09-29", "2020-10-15", "2020-11-21", "2020-12-02",
"2020-12-10", "2020-12-18"
])
})
# All posts from September (month = 9)
september_posts = df.loc[df["date"].dt.month == 9]
print("September posts:")
print(september_posts)
# All posts on Wednesdays (weekday 2 = Wednesday)
# Monday=0, Tuesday=1, Wednesday=2, ..., Sunday=6
wednesday_posts = df.loc[df["date"].dt.weekday == 2]
print("Wednesday posts:")
print(wednesday_posts)
Output:
September posts:
num_posts date
2 3 2020-09-05
3 9 2020-09-12
4 1 2020-09-29
Wednesday posts:
num_posts date
7 5 2020-12-02
Filter by Year and Quarter
import pandas as pd
df = pd.DataFrame({
"num_posts": [4, 6, 3, 9, 1, 14, 2, 5, 7, 2],
"date": pd.to_datetime([
"2020-08-09", "2020-08-25", "2020-09-05", "2020-09-12",
"2020-09-29", "2020-10-15", "2020-11-21", "2020-12-02",
"2020-12-10", "2020-12-18"
])
})
# Posts in Q4 of 2020 (October, November, December)
q4_posts = df.loc[(df["date"].dt.year == 2020) & (df["date"].dt.quarter == 4)]
print("Q4 2020 posts:")
print(q4_posts)
Output:
Q4 2020 posts:
num_posts date
5 14 2020-10-15
6 2 2020-11-21
7 5 2020-12-02
8 7 2020-12-10
9 2 2020-12-18
Available .dt Attributes
| Attribute | Description | Example Values |
|---|---|---|
.dt.year | Year | 2020, 2021 |
.dt.month | Month (1–12) | 1, 6, 12 |
.dt.day | Day of the month | 1, 15, 31 |
.dt.weekday | Day of the week (0=Mon, 6=Sun) | 0, 3, 6 |
.dt.quarter | Quarter (1–4) | 1, 2, 3, 4 |
.dt.hour | Hour (for datetime with time) | 0, 12, 23 |
.dt.day_name() | Day name as string | "Monday", "Friday" |
.dt.month_name() | Month name as string | "January", "December" |
Method 3: Filtering with DataFrame.query()
The query() method lets you write filter conditions as a string expression, which can be more readable - especially for complex conditions.
import pandas as pd
df = pd.DataFrame({
"num_posts": [4, 6, 3, 9, 1, 14, 2, 5, 7, 2],
"date": pd.to_datetime([
"2020-08-09", "2020-08-25", "2020-09-05", "2020-09-12",
"2020-09-29", "2020-10-15", "2020-11-21", "2020-12-02",
"2020-12-10", "2020-12-18"
])
})
# Filter posts in August 2020
august_posts = df.query("date >= '2020-08-01' and date < '2020-09-01'")
print("August posts:")
print(august_posts)
Output:
August posts:
num_posts date
0 4 2020-08-09
1 6 2020-08-25
Using Variables in query()
You can reference Python variables using the @ prefix:
import pandas as pd
df = pd.DataFrame({
"num_posts": [4, 6, 3, 9, 1, 14, 2, 5, 7, 2],
"date": pd.to_datetime([
"2020-08-09", "2020-08-25", "2020-09-05", "2020-09-12",
"2020-09-29", "2020-10-15", "2020-11-21", "2020-12-02",
"2020-12-10", "2020-12-18"
])
})
start_date = "2020-09-01"
end_date = "2020-10-01"
september_posts = df.query("date >= @start_date and date < @end_date")
print("September posts:")
print(september_posts)
Output:
September posts:
num_posts date
2 3 2020-09-05
3 9 2020-09-12
4 1 2020-09-29
query() is particularly useful when building dynamic filters where the conditions are constructed at runtime from user input or configuration.
Method 4: Filtering with pd.Series.between()
The between() method provides a clean, concise way to filter for values within a range (inclusive by default):
import pandas as pd
df = pd.DataFrame({
"num_posts": [4, 6, 3, 9, 1, 14, 2, 5, 7, 2],
"date": pd.to_datetime([
"2020-08-09", "2020-08-25", "2020-09-05", "2020-09-12",
"2020-09-29", "2020-10-15", "2020-11-21", "2020-12-02",
"2020-12-10", "2020-12-18"
])
})
# Posts between September 1 and October 31 (inclusive)
filtered = df.loc[df["date"].between("2020-09-01", "2020-10-31")]
print(filtered)
Output:
num_posts date
2 3 2020-09-05
3 9 2020-09-12
4 1 2020-09-29
5 14 2020-10-15
You can control inclusivity with the inclusive parameter:
import pandas as pd
df = pd.DataFrame({
"num_posts": [4, 6, 3, 9, 1, 14, 2, 5, 7, 2],
"date": pd.to_datetime([
"2020-08-09", "2020-08-25", "2020-09-05", "2020-09-12",
"2020-09-29", "2020-10-15", "2020-11-21", "2020-12-02",
"2020-12-10", "2020-12-18"
])
})
# Exclusive on both ends
filtered = df.loc[df["date"].between("2020-09-01", "2020-10-15", inclusive="neither")]
print(filtered)
Output:
num_posts date
2 3 2020-09-05
3 9 2020-09-12
4 1 2020-09-29
Method 5: Filtering with dt.strftime()
You can convert datetime values to formatted strings using dt.strftime() and then filter based on string matching. This is useful for non-standard filtering like selecting all dates with a specific month-year pattern:
import pandas as pd
df = pd.DataFrame({
"num_posts": [4, 6, 3, 9, 1, 14, 2, 5, 7, 2],
"date": pd.to_datetime([
"2020-08-09", "2020-08-25", "2020-09-05", "2020-09-12",
"2020-09-29", "2020-10-15", "2020-11-21", "2020-12-02",
"2020-12-10", "2020-12-18"
])
})
# Filter posts from August 2020 using string formatting
filtered = df[df["date"].dt.strftime("%Y-%m-%d").between("2020-08-01", "2020-08-31")]
print(filtered)
Output:
num_posts date
0 4 2020-08-09
1 6 2020-08-25
Filter by Year-Month Pattern
import pandas as pd
df = pd.DataFrame({
"num_posts": [4, 6, 3, 9, 1, 14, 2, 5, 7, 2],
"date": pd.to_datetime([
"2020-08-09", "2020-08-25", "2020-09-05", "2020-09-12",
"2020-09-29", "2020-10-15", "2020-11-21", "2020-12-02",
"2020-12-10", "2020-12-18"
])
})
# All December 2020 posts
december_posts = df[df["date"].dt.strftime("%Y-%m") == "2020-12"]
print("December 2020 posts:")
print(december_posts)
Output:
December 2020 posts:
num_posts date
7 5 2020-12-02
8 7 2020-12-10
9 2 2020-12-18
Bonus: Using a DatetimeIndex for Faster Filtering
If you frequently filter by date, setting the date column as the index enables powerful slicing with .loc[]:
import pandas as pd
df = pd.DataFrame({
"num_posts": [4, 6, 3, 9, 1, 14, 2, 5, 7, 2],
"date": pd.to_datetime([
"2020-08-09", "2020-08-25", "2020-09-05", "2020-09-12",
"2020-09-29", "2020-10-15", "2020-11-21", "2020-12-02",
"2020-12-10", "2020-12-18"
])
})
# Set date as the index
df_indexed = df.set_index("date")
# Slice by date range (inclusive on both ends)
print("Sep to Oct 2020:")
print(df_indexed.loc["2020-09":"2020-10"])
print("\nSpecific date:")
print(df_indexed.loc["2020-08-09"])
print("\nAll of December:")
print(df_indexed.loc["2020-12"])
Output:
Sep to Oct 2020:
num_posts
date
2020-09-05 3
2020-09-12 9
2020-09-29 1
2020-10-15 14
Specific date:
num_posts 4
Name: 2020-08-09 00:00:00, dtype: int64
All of December:
num_posts
date
2020-12-02 5
2020-12-10 7
2020-12-18 2
A DatetimeIndex is especially beneficial for time-series analysis - it enables partial string indexing ("2020-09" for all of September), resampling, and time-based joins.
Comparison of Methods
| Method | Readability | Flexibility | Performance | Best For |
|---|---|---|---|---|
df.loc[] with conditions | ✅ Good | ✅ High | ✅ Fast | General-purpose date filtering |
.dt accessor | ✅ Good | ✅ High | ✅ Fast | Filtering by components (month, weekday) |
df.query() | ✅ Excellent | ✅ High | ✅ Fast | Readable, dynamic conditions |
.between() | ✅ Excellent | ⚠️ Range only | ✅ Fast | Simple range filtering |
dt.strftime() | ⚠️ Moderate | ✅ High | ⚠️ Slower | Pattern matching on formatted dates |
DatetimeIndex slicing | ✅ Excellent | ⚠️ Moderate | ✅ Fastest | Time-series with frequent date access |
Conclusion
Pandas offers multiple flexible methods to filter DataFrame rows based on dates:
- Convert date strings to
datetime64withpd.to_datetime()before any filtering - this is the essential first step. df.loc[]with boolean conditions is the most versatile and commonly used approach.- The
.dtaccessor lets you filter by individual date components like year, month, weekday, or quarter. df.query()provides readable string-based conditions, ideal for dynamic filtering..between()offers a concise syntax for date range filtering.DatetimeIndexenables the fastest and most expressive date slicing for time-series data.
Choose the method that best matches your filtering needs and coding style, all of them integrate seamlessly with Pandas' powerful data manipulation ecosystem.