Filtering dataframe based on list of dates in python

Question

I have the following dataframe

                             response
01/09/2020 07:00:00 AM         1.345
01/09/2020 07:01:00 AM         2.567
01/09/2020 07:02:22 AM         5.999
01/09/2020 07:03:30 AM         4.800
01/09/2020 07:04:07 AM         8.720
01/09/2020 07:05:09 AM         0.999
... 
02/05/2020 11:57:42 PM         8.213
02/05/2020 11:58:03 PM         2.873
02/05/2020 11:59:55 PM         3.875

I want to filter out rows that correspond to this list of dates

list =  [01/10/2020, 01/11/2020, 01/14/2020, 01/16/2020, 01/20/2020, 02/01/2020, 02/03/2020, 02/04/2020]

There are multiple times corresponding to days and I want to keep all of them and drop only the rows that are not in the list of dates.

I am new to Python and have been struggling with this for quite some time, any help will be greatly appreciated. Thank you!

Tip: never ever name your list, list. Call it list1 or something. It will overwrite what a list is to Python. — John Stud
– John Stud, Commented Nov 24, 2020 at 3:57

Tom · Accepted Answer · 2020-11-24 04:08:18Z

3

You can use isin to check for membership in the list of dates. Ensure that the list of dates is datetime, and that the floor of the DataFrame dates are used:

dates = pd.to_datetime(dates) # list in your original
df = df[df.index.floor('D').isin(dates)]

Full example:

import pandas as pd
import numpy as np

dr = pd.date_range('01-01-2020', '01-10-2020', freq='D')
df = pd.DataFrame({'response':np.random.rand(len(dr))}, index=dr)

dates = ['01-01-2020', '01-05-2020', '01-07-2020']
dates = pd.to_datetime(dates)

df = df[df.index.floor('D').isin(dates)]

answered Nov 24, 2020 at 4:08

Tom

8,8402 gold badges20 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

drogon2 · Accepted Answer · 2020-11-24 04:12:44Z

2

first you need to change your date_time column to date_only (or add a new date_only column) and then apply appropriate filter, assuming you are using pandas you could do something like

# assuming your first column name is 'date_time'
df['date_only'] = df['date_time'].dt.date
df = df[~df.date_only.isin(list_of_dates)]

answered Nov 24, 2020 at 4:12

drogon2

6715 silver badges3 bronze badges

1 Comment

Sandeepa Kariyawasam Over a year ago

apparently, this didn't work. it creates the date_only column but does not match correctly to the list

Collectives™ on Stack Overflow

Filtering dataframe based on list of dates in python

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related