Select DataFrame rows between two dates Select DataFrame rows between two dates python python

Select DataFrame rows between two dates


There are two possible solutions:

  • Use a boolean mask, then use df.loc[mask]
  • Set the date column as a DatetimeIndex, then use df[start_date : end_date]

Using a boolean mask:

Ensure df['date'] is a Series with dtype datetime64[ns]:

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

Make a boolean mask. start_date and end_date can be datetime.datetimes,np.datetime64s, pd.Timestamps, or even datetime strings:

#greater than the start date and smaller than the end datemask = (df['date'] > start_date) & (df['date'] <= end_date)

Select the sub-DataFrame:

df.loc[mask]

or re-assign to df

df = df.loc[mask]

For example,

import numpy as npimport pandas as pddf = pd.DataFrame(np.random.random((200,3)))df['date'] = pd.date_range('2000-1-1', periods=200, freq='D')mask = (df['date'] > '2000-6-1') & (df['date'] <= '2000-6-10')print(df.loc[mask])

yields

            0         1         2       date153  0.208875  0.727656  0.037787 2000-06-02154  0.750800  0.776498  0.237716 2000-06-03155  0.812008  0.127338  0.397240 2000-06-04156  0.639937  0.207359  0.533527 2000-06-05157  0.416998  0.845658  0.872826 2000-06-06158  0.440069  0.338690  0.847545 2000-06-07159  0.202354  0.624833  0.740254 2000-06-08160  0.465746  0.080888  0.155452 2000-06-09161  0.858232  0.190321  0.432574 2000-06-10

Using a DatetimeIndex:

If you are going to do a lot of selections by date, it may be quicker to set thedate column as the index first. Then you can select rows by date usingdf.loc[start_date:end_date].

import numpy as npimport pandas as pddf = pd.DataFrame(np.random.random((200,3)))df['date'] = pd.date_range('2000-1-1', periods=200, freq='D')df = df.set_index(['date'])print(df.loc['2000-6-1':'2000-6-10'])

yields

                   0         1         2date                                    2000-06-01  0.040457  0.326594  0.492136    # <- includes start_date2000-06-02  0.279323  0.877446  0.4645232000-06-03  0.328068  0.837669  0.6085592000-06-04  0.107959  0.678297  0.5174352000-06-05  0.131555  0.418380  0.0257252000-06-06  0.999961  0.619517  0.2061082000-06-07  0.129270  0.024533  0.1547692000-06-08  0.441010  0.741781  0.4704022000-06-09  0.682101  0.375660  0.0099162000-06-10  0.754488  0.352293  0.339337

While Python list indexing, e.g. seq[start:end] includes start but not end, in contrast, Pandas df.loc[start_date : end_date] includes both end-points in the result if they are in the index. Neither start_date nor end_date has to be in the index however.


Also note that pd.read_csv has a parse_dates parameter which you could use to parse the date column as datetime64s. Thus, if you use parse_dates, you would not need to use df['date'] = pd.to_datetime(df['date']).


I feel the best option will be to use the direct checks rather than using loc function:

df = df[(df['date'] > '2000-6-1') & (df['date'] <= '2000-6-10')]

It works for me.

Major issue with loc function with a slice is that the limits should be present in the actual values, if not this will result in KeyError.


You can also use between:

df[df.some_date.between(start_date, end_date)]


matomo