remove rows with certain dates in pandas

Question

Using python 3.7, pandas 1.1.1

I have dataframe with datetimeindex and list of date_list =["2020-07-19", "2020-07-24", ... etc]

I want to remove all rows in my dataframe that contain dates in date_list.

My datetimeindex contains variety of hh:mm:ss as well and no matter its time I want to remove if its dates are in date_list

dataframe looks like this:

      time              data
2020-07-19 23:52:02       1
2020-07-20 13:44:02       1
2020-07-22 23:52:02       1
2020-07-24 08:52:02       1
2020-07-24 21:52:02       1

desired output would be:

      time              data
2020-07-20 13:44:02       1
2020-07-22 23:52:02       1

deleting dates in date_list

following How can I delete rows for a particular Date in a Pandas dataframe? I've tried df.drop(pd.to_datetime("2020-07-19")) which give KeyError: "[Timestamp('2020-07-19 00:00:00')] not found in axis"

How can I remove dates without considering its time?

Quang Hoang · Accepted Answer · 2020-09-15 02:15:39Z

4

Since your time are not exact 00:00:00 on the day, you can use normalize() to extract the date. Then you can use isin:

date_list = pd.to_datetime(date_list)

df[~df['time'].dt.normalize().isin(date_list)]

If time is the index:

df[~df.index.normalize().isin(date_list)]

edited Sep 15, 2020 at 2:15

answered Sep 15, 2020 at 2:09

Quang Hoang

151k11 gold badges64 silver badges86 bronze badges

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

4 Comments

haneulkim Over a year ago

Thanks this works but I need to df.reset_index() and then use it, can I do it with index?

Quang Hoang Over a year ago

Is time column the index of your df?

haneulkim Over a year ago

Amazing! could you explain why we need extra .dt when time is set as column and without .dt when time is set as index?

Quang Hoang Over a year ago

@Ambleu not sure why. Probably a good question to Pandas' dev. I personally find it confusing sometimes.

BENY · Accepted Answer · 2020-09-15 02:27:27Z

2

Check

df[~df.index.to_series().dt.date.isin(date_list)]

edited Sep 15, 2020 at 2:27

answered Sep 15, 2020 at 2:11

BENY

324k22 gold badges176 silver badges250 bronze badges

3 Comments

haneulkim Over a year ago

Thanks! Though this outputs TypeError: 'Series' object is not callable

BENY Over a year ago

@QuangHoang ah that is right ~ I should test before posted, thank you man ~

haneulkim Over a year ago

This outputs AttributeError: 'numpy.ndarray' object has no attribute 'isin'

Agaz Wani · Accepted Answer · 2020-09-15 02:35:49Z

0

Or using str.contains

df[~df["time"].str.contains('|'.join(date_list))]

answered Sep 15, 2020 at 2:35

Agaz Wani

5,7549 gold badges45 silver badges62 bronze badges

Collectives™ on Stack Overflow

remove rows with certain dates in pandas

3 Answers 3

4 Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related