Let's see how to select/filter rows between two dates in Pandas DataFrame, in real-time applications you would often be required to select rows between two dates (similar to a greater than a start date and less than an end date), In pandas, you can do this in several ways, for example, using between(), between_time(), date_range() e.t.c.
In this article, I will explain how to select Pandas DataFrame rows between two dates by using the boolean mask with the loc[] attribute and DataFrame indexing. You can also use DataFrame.query(), DataFrame.isin(), and pandas.Series.between() methods. These methods are used to select rows based on the date in Pandas.
Key Points -
- Convert the date column to datetime format using
pd.to_datetime()if necessary. - Use boolean conditions to filter rows between two dates, e.g.,
(df['date'] >= start_date) & (df['date'] <= end_date). - Apply conditions within
df.loc[]for flexible row and column filtering. - The
.query()method can make date filtering syntax more concise. - If the date is the index, use
df.loc[start_date:end_date]for direct slicing. df['date'].between(start_date, end_date)provides a concise alternative for date filtering.
<a id="h-quick-examples-of-select-rows-between-two-dates"></a>
Quick Examples of Select Rows Between Two Dates
If you are in a hurry, below are some quick examples of how to select rows between two dates from DataFrame.
# Quick examples of select rows between two dates
# Example 1: Select DataFrame rows between two dates
mask = (df['InsertedDates'] > start_date) & (df['InsertedDates'] <= end_date)
df2 = df.loc[mask]
# Example 2: Using pandas.DataFrame.loc[]
# To Filter Rows by Dates
df2 = df.loc[between_two_dates]
# Example 3: Using pandas.DataFrame.query()
# To select dataframe rows
start_date = '2021-11-15'
end_date = '2021-11-18'
df2 = df.query('InsertedDates >= @start_date and InsertedDates <= @end_date')
# Example 4: Select rows between two dates
# Using DataFrame.query()
start_date = '2021-11-15'
end_date = '2021-11-18'
df2 = df.query('InsertedDates > @start_date and InsertedDates < @end_date')
# Example 5: Pandas.Series.between() function
# Using two dates
df2 = df.loc[df["InsertedDates"].between("2021-11-16", "2021-11-18")]
# Example 6: Select DataFrame rows between two dates
# Using DataFrame.isin()
df2 = df[df["InsertedDates"].isin(pd.date_range("2021-11-15", "2021-11-17"))]
Now, let's create a DataFrame with a few rows and columns, execute these examples, and validate the results. Our DataFrame contains column names Courses, Fee, Duration,Discount and Inserted Dates.
import pandas as pd
technologies = ({
'Courses':["Spark","PySpark","Hadoop","Python","Pandas","Hadoop","Spark"],
'Fee' :[22000,25000,23000,24000,26000,25000,25000],
'Duration':['30days','50days','55days','40days','60days','35days','55days'],
'Discount':[1000,2300,1000,1200,2500,1300,1400],
'InsertedDates':["2021-11-14","2021-11-15","2021-11-16","2021-11-17","2021-11-18","2021-11-19","2021-11-20"]
})
df = pd.DataFrame(technologies)
print("Create DataFrame:\n", df)
Yields below output.
# Output:
Courses Fee Duration Discount InsertedDates
0 Spark 22000 30days 1000 2021-11-14
1 PySpark 25000 50days 2300 2021-11-15
2 Hadoop 23000 55days 1000 2021-11-16
3 Python 24000 40days 1200 2021-11-17
4 Pandas 26000 60days 2500 2021-11-18
5 Hadoop 25000 35days 1300 2021-11-19
6 Spark 25000 55days 1400 2021-11-20
<a id="h-select-dataframe-rows-between-two-dates"></a>
Select DataFrame Rows Between Two Dates
In order to select rows between two dates in Pandas DataFrame, first, create a boolean mask using mask = (df['InsertedDates'] > start_date) & (df['InsertedDates'] <= end_date) to represent the start and end of the date range. Then you select the DataFrame that lies within the range using the DataFrame.loc[] method.
# Select DataFrame rows between two dates
df = pd.DataFrame(technologies)
start_date = '2021-11-15'
end_date = '2021-11-18'
# Select DataFrame rows between two dates
mask = (df['InsertedDates'] > start_date) & (df['InsertedDates'] <= end_date)
df2 = df.loc[mask]
print("After selecting rows between the two dates:\n", df2)
Yields below output. This returns the rows greater and less than the date selected
# Output:
Courses Fee Duration Discount InsertedDates
2 Hadoop 23000 55days 1000 2021-11-16
3 Python 24000 40days 1200 2021-11-17
4 Pandas 26000 60days 2500 2021-11-18
<a id="h-filter-rows-by-dates-using-dataframe-loc-method"></a>
Filter Rows by Dates Using DataFrame.loc[] Method
Use DataFrame.loc[] method to return a boolean array with start_date ane end_date (date is in between 15th and 19th September) . Then the loc[] function filters the rows only having between two dates.
# Filter rows by dates
# Using DataFrame.loc[] method
df = pd.DataFrame(technologies)
start_date = '2021-11-15'
end_date = '2021-11-19'
after_start_date = df["InsertedDates"] >= start_date
before_end_date = df["InsertedDates"] <= end_date
between_two_dates = after_start_date & before_end_date
# Using pandas.DataFrame.loc
# To filter rows by dates
df2 = df.loc[between_two_dates]
print("After selecting rows between the two dates:\n", df2)
Yields below output.
<a id="h-select-rows-between-two-dates-using-dataframe-query"></a>
Select Rows Between Two Dates Using DataFrame.query()
You can also select DataFrame rows between two dates in Pandas using the pandas.DataFrame.query() method. This method returns a DataFrame result from the provided query expression. For instance df.query('Dates >= @start_date and Dates <= @end_date').
# Using pandas.DataFrame.query()
# To select DataFrame rows
start_date = '2021-11-15'
end_date = '2021-11-18'
df2 = df.query('InsertedDates >= @start_date and InsertedDates <= @end_date')
print("After selecting the rows between the two dates:\n", df2)
Yields below output.
If you don't want to include boundaries, just change the condition like below the following syntax: df.query('Dates > @start_date and Dates < @end_date').
# Select rows between two dates
# Using DataFrame.query()
start_date = '2021-11-15'
end_date = '2021-11-18'
df2 = df.query('InsertedDates > @start_date and InsertedDates < @end_date')
print("After selecting the rows between the two dates:\n", df2)
Yields below output.
# Output:
# After selecting the rows between the two dates:
Courses Fee Duration Discount InsertedDates
2 Hadoop 23000 55days 1000 2021-11-16
3 Python 24000 40days 1200 2021-11-17
<a id="h-use-pandas-series-between-to-two-dates"></a>
Use pandas.Series.between() to Two Dates
You can use pandas.Series.between() method to select DataFrame rows between two dates. This method returns a boolean vector representing whether a series element lies in the specified range or not. We have to pass the obtained boolean vector to the DataFrame.loc() method for extracting DataFrame.
# Pandas.Series.between() function Using two dates
df2 = df.loc[df["InsertedDates"].between("2021-11-16", "2021-11-18")]
print("After selecting the rows between the two dates:\n", df2)
Yields below output.
# Output:
# After selecting the rows between the two dates:
Courses Fees Duration Discount InsertedDates
2 Hadoop 23000 55days 1000 2021-11-16
3 Python 24000 40days 1200 2021-11-17
4 Pandas 26000 60days 2500 2021-11-18
<a id="h-select-dataframe-rows-between-two-dates-using-dataframe-isin"></a>
Select DataFrame Rows Between Two Dates Using DataFrame.isin()
Use pandas.DataFrame.isin() to filter DataFrame rows based on the date in Pandas. pandas.date_range() returns a fixed DateTimeIndex. Its first and second parameters are starting and ending dates.
import pandas as pd
import numpy as np
import datetime
Courses =["Spark","PySpark","Hadoop","Python","Pandas","Hadoop","Spark"]
Fee =[22000,25000,23000,24000,26000,25000,25000]
Duration =['30days','50days','55days','40days','60days','35days','55days']
Discount =[1000,2300,1000,1200,2500,1300,1400]
InsertedDates =["2021-11-14","2021-11-15","2021-11-16","2021-11-17","2021-11-18","2021-11-19","2021-11-20"]
df = pd.DataFrame({"Courses":Courses,"Fees":Fee,"Duration":Duration,"Discount":Discount,'InsertedDates': pd.to_datetime(InsertedDates)})
# Select DataFrame rows between two dates using DataFrame.isin()
df2 = df[df["InsertedDates"].isin(pd.date_range("2021-11-15", "2021-11-17"))]
print("After selecting the rows between the two dates:\n", df2)
Yields below output.
# Output:
# After selecting the rows between the two dates:
Courses Fees Duration Discount InsertedDates
1 PySpark 25000 50days 2300 2021-11-15
2 Hadoop 23000 55days 1000 2021-11-16
3 Python 24000 40days 1200 2021-11-17
<a id="h-complete-example-for-select-rows-between-two-dates"></a>
Complete Example For Select Rows Between Two Dates
import pandas as pd
technologies = ({
'Courses':["Spark","PySpark","Hadoop","Python","Pandas","Hadoop","Spark"],
'Fee' :[22000,25000,23000,24000,26000,25000,25000],
'Duration':['30days','50days','55days','40days','60days','35days','55days'],
'Discount':[1000,2300,1000,1200,2500,1300,1400],
'InsertedDates':["2021-11-14","2021-11-15","2021-11-16","2021-11-17","2021-11-18","2021-11-19","2021-11-20"]
})
df = pd.DataFrame(technologies)
print(df)
# Select DataFrame rows between two dates
mask = (df['InsertedDates'] > start_date) & (df['InsertedDates'] <= end_date)
df2 = df.loc[mask]
print(df2)
start_date = '2021-11-15'
end_date = '2021-11-19'
after_start_date = df["InsertedDates"] >= start_date
before_end_date = df["InsertedDates"] <= end_date
between_two_dates = after_start_date & before_end_date
# Using pandas.DataFrame.loc
# To filter rows by dates
df2 = df.loc[between_two_dates]
print(df2)
# Using pandas.DataFrame.query()
# To select DataFrame Rows
start_date = '2021-11-15'
end_date = '2021-11-18'
df2 = df.query('InsertedDates >= @start_date and InsertedDates <= @end_date')
print(df2)
# Select rows between two dates
# Using DataFrame.query()
start_date = '2021-11-15'
end_date = '2021-11-18'
df2 = df.query('InsertedDates > @start_date and InsertedDates < @end_date')
print(df2)
# Pandas.Series.between() function
# Using two dates
df2 = df.loc[df["InsertedDates"].between("2021-11-16", "2021-11-18")]
print(df2)
# Select DataFrame rows between two dates
# Using DataFrame.isin()
df2 = df[df["InsertedDates"].isin(pd.date_range("2021-11-15", "2021-11-17"))]
print(df2)
<a id="h-faq-on-pandas-select-dataframe-rows-between-two-dates"></a>
FAQ on Pandas Select DataFrame Rows Between Two Dates
To select rows between two dates, you can use boolean indexing with a condition that compares the datetime column against the start and end dates.
It’s a good practice to ensure the column is in datetime format before filtering. You can use pd.to_datetime() to convert the column if it’s not already in datetime format.
To select rows where the date is after a specific date in a Pandas DataFrame, you can use boolean indexing with the > operator. This filters the DataFrame by checking if the date is greater than the specified date.
To select rows where the date is before a specific date in a Pandas DataFrame, you can use boolean indexing with the < operator. This allows you to filter rows where the date is less than the specified date.
You can extract the year, month, or other components from a datetime column using the .dt accessor, and then filter based on that.
<a id="h-conclusion"></a>
Conclusion
In this article, you have learned how to select DataFrame rows between two dates in pandas using DataFrame.loc[], DataFrame.query(), DataFrame.isin() and pandas.Series.between() with more examples.
Happy Learning !!
<a id="h-related-articles"></a>
Related Articles
- Pandas Convert String Column To DateTime
- Convert String to Float in Pandas DataFrame
- How to Format Pandas Datetime?
- Pandas Convert Column To DateTime
- Pandas DatetimeIndex Usage Explained
- Convert Pandas DatetimeIndex to String
- pandas Convert Datetime to Seconds
- Pandas Extract Year from Datetime
- Pandas Get Day, Month and Year from DateTime
- Pandas Extract Month and Year from Datetime
- Pandas Convert Integer to Datetime Type
- Pandas Convert Datetime to Date Column
- Pandas Convert Date (datetime) to String Format
- Pandas Convert Multiple Columns To DateTime Type
<a id="h-references"></a>