How to Select Values Between Two Times in Pandas


Often you may want to select rows in a pandas DataFrame based on values in a column that fall between two specific times.

The most efficient way to do so is by using the between_time() function, which is designed to perform this exact task.

The between_time() function uses the following syntax:

pandas.DataFrame.between_time(start_time, end_time, inclusive=’both’, axis=None)

where:

  • start_time: The initial time as a time filter limit
  • end_time: The end time as a time filter limit
  • inclusive: Whether to include boundaries or not (both, neither, left, right)
  • axis: Whether to determine range time on index or column values

The following example shows how to use the between_time() function in practice with a pandas DataFrame.

Example: How to Select Values Between Two Times in Pandas

Suppose we create the following pandas DataFrame that contains information about the total sales made by an employee at a company during nine consecutive days:

import pandas as pd

#create DataFrame
df = pd.DataFrame({'sales': [2, 5, 5, 4, 7, 8, 9, 12, 10]},
                   index=pd.date_range('1/1/2018 4:00:00', periods=9, freq='30min'))

#view DataFrame
print(df)

                     sales
2018-01-01 04:00:00      2
2018-01-01 04:30:00      5
2018-01-01 05:00:00      5
2018-01-01 05:30:00      4
2018-01-01 06:00:00      7
2018-01-01 06:30:00      8
2018-01-01 07:00:00      9
2018-01-01 07:30:00     12
2018-01-01 08:00:00     10

We can see that the index of the DataFrame is represented by intervals at 30 minutes apart.

Suppose that we would like to extract all rows from the DataFrame whose index value falls between the times of 4:30 AM and 6:30 AM.

We can use the between_time() function with the following syntax to do so:

#extract all rows with time between 4:30 and 6:30
df.between_time('4:30', '6:30')

                    sales
2018-01-01 04:30:00	5
2018-01-01 05:00:00	5
2018-01-01 05:30:00	4
2018-01-01 06:00:00	7
2018-01-01 06:30:00	8

Notice that each of the rows that are returned contain a time between 4:30 and 6:30 in the index column of the DataFrame.

Also note that the date is not important when using the between_time() function since we’re only looking at the time portion of the datetime index value.

Note that we could also specify whether we’d like to include the start and end time by providing specific values to the inclusive argument of the function:

For example, we can use the following syntax to extract rows with a time between 4:30 and 6:30, including only the start time:

#extract all rows with time between 4:30 and 6:30 (only including start time)
df.between_time('4:30', '6:30', inclusive='left')

	            sales
2018-01-01 04:30:00	5
2018-01-01 05:00:00	5
2018-01-01 05:30:00	4
2018-01-01 06:00:00	7

This returns all rows with a time between 4:30 and 6:30, including the start time of 4:30 but not including the end time of 6:30.

We could also perform the opposite of this procedure and only include the end time.

For example, we can use the following syntax to extract rows with a time between 4:30 and 6:30, including only the end time:

#extract all rows with time between 4:30 and 6:30 (only including end time)
df.between_time('4:30', '6:30', inclusive='right')

                    sales
2018-01-01 05:00:00	5
2018-01-01 05:30:00	4
2018-01-01 06:00:00	7
2018-01-01 06:30:00	8

This returns all rows with a time between 4:30 and 6:30, not including the start time of 4:30 but including the end time of 6:30.

Feel free to specify ‘right’, ‘left’ or ‘neither’ for the inclusive argument depending on which time boundaries you would like to include in the output.

Note: You can find the complete documentation for the between_time() function in pandas here.

Additional Resources

The following tutorials explain how to perform other common tasks in pandas:

How to Use the Rolling.apply() Function in Pandas
How to Use the nunique() Function in Pandas
How to Use the get_loc() Function in Pandas
How to Use idxmin() Function in Pandas

Featured Posts

Leave a Reply

Your email address will not be published. Required fields are marked *