Pythonic way to filter a pandas dataframe to only records that were active between two dates

user_12287342 0
Viewed 60

This is something that I come across repeatedly and I wondered if there is an easier way to do it that what I'm doing. This is an artificial example for the sake of illustration.

I have a pandas dataframe. It's a log of when a number of machines started up and shut down as so:

| MachineID | StartDate  | EndDate    |
|-----------|------------|------------|
| 1         | 2020-01-01 | 2020-06-01 |
| 2         | 2020-01-01 | 2020-02-01 |
| 3         | 2020-03-01 | 2020-07-15 |
| 4         | 2020-04-01 | 2020-05-01 |
| 5         | 2020-04-01 | 2020-07-15 |
| 6         | 2020-05-01 |            |
| 7         | 2020-07-01 |            |

Null values in the EndDate column indicate that the machine has not yet shutdown. Dates can be any day, I've just used the first for simplicity.

Now say I want to filter the dataframe to only machines that were active at any point between two dates, in this case let's say that the start date for the active period is 2020-04-01 and the end date is 2020-06-30.

                     start                    end
                   2020-04-01             2020-06-30
                       |                       |
Machine 1  xxxxxxxxxxxxxxxxxxxxxxxx
Machine 2  xxx
Machine 3         xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx      
Machine 4              xxxxxxxxxxxxxxxxxx
Machine 5              xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Machine 6                               xxxxxxxxxxxxxxxxxxx...(still going)
Machine 7                                        xxxxxxxxxx...(still going)
                       |                       |

If I was to use just (df["StartDate"]>=start) & (df["EndDate"]<=end) I'd filter out machines 3 and 6 which I definitely don't want as they were active during the period. What I'm doing just now is constructing a very long-winded filter like so:

start = "2020-04-01"
end = "2020-06-30"

dff = df[
    # Machines that started before the start date
    ((df["StartDate"]<=start) & ((df["EndDate"]>=start) & (df["End date"] <= end))) | \ 
    ((df["StartDate"]<=start) & (df["EndDate"]>=end)) | \  
    ((df["StartDate"]<=start) & (df["EndDate"].isnull())) | \ 

    # Machines that started after the start date
    ((df["StartDate"]>=start) & ((df["EndDate"]>=start) & (df["End date"] <= end))) | \
    ((df["StartDate"]>=start) & (df["EndDate"]>=end)) | \
    ((df["StartDate"]>=start) & (df["EndDate"].isnull()))
              ]

This seems to get the job done but there must be a better way. I'm fully prepared to facepalm at something obvious (I'm pretty new to this) if anyone cares can offer an alternative?

pandas filter date python
2 Answers

One trick I've learned is to flip the comparison: compare one range's start with the other's end and vice versa:

start = pd.Timestamp('2020-04-01')
end = pd.Timestamp('2020-06-30')

cond = (df['StartDate'] < end) & (df['EndDate'].fillna(pd.Timestamp('2099-01-01')) > start)
dff = df[cond]

If you use this repeatedly, turn it into a function.

user_25389390

The easiest way would be to just filter out the cases you do not want to select.

df[~((df['EndDate'] < start) | (df['StartDate'] > end))]

Explanation

There are five different scenarios

         start            end         
1)  xxx    |               | 
2)       xx|xxxx           |
3)         |     xxxxx     |  
4)         |             xx|xxxx
5)         |               |  xxx

From all the cases you want to select 2, 3 and 4. With less amount of code you can write filter for cases 1 & 5 and then just invert the selection. The or operator | is used to match cases 2 or 3 or 4. Then, not operator ~ is used to invert the mask.

Output with example data

Using the data given in the question, and

start = pd.Timestamp('2020-04-01')
end = pd.Timestamp('2020-06-30')

This would output

   MachineID  StartDate    EndDate
0          1 2020-01-01 2020-06-01
2          3 2020-03-01 2020-07-15
3          4 2020-04-01 2020-05-01
4          5 2020-04-01 2020-07-15
5          6 2020-05-01        NaT
user_30151860
Related