delete rows from dataframe python

Question

EXPIRY_DT column of dataframe contains unique dates, i want to delete dates other than

24-Jun-21. when i run this code all rows gets deleted.

df1.drop(df1[df1['EXPIRY_DT'] != '24-Jun-21'].index, inplace=True)

i am unable to understand why this is happening. because i have used this same method on one other column and it worked fine.

df1.drop(df1[df1['INSTRUMENT'] != 'FUTSTK'].index, inplace=True)            // this worked

only difference between this two columns that i can see is as follows
some leading space is there in case of dates

how to solve this issue?

update:

{'INSTRUMENT': {0: 'FUTIDX', 1: 'FUTIDX', 2: 'FUTIDX', 3: 'FUTIDX', 4: 'FUTIDX'}, 'SYMBOL': {0: 'BANKNIFTY', 1: 'BANKNIFTY', 2: 'BANKNIFTY', 3: 'FINNIFTY', 4: 'FINNIFTY'}, 'EXPIRY_DT': {0: '24-Jun-2021', 1: '29-Jul-2021', 2: '26-Aug-2021', 3: '03-Jun-2021', 4: '10-Jun-2021'}, 'STRIKE_PR': {0: 0.0, 1: 0.0, 2: 0.0, 3: 0.0, 4: 0.0}, 'OPTION_TYP': {0: 'XX', 1: 'XX', 2: 'XX', 3: 'XX', 4: 'XX'}, 'OPEN': {0: 35280.0, 1: 
35371.6, 2: 35515.15, 3: 16400.05, 4: 16492.2}, 'HIGH': {0: 35650.0, 1: 35769.7, 2: 35886.55, 3: 16676.5, 4: 16703.85}, 'LOW': {0: 35063.15, 1: 35205.1, 2: 35325.0, 3: 16400.05, 4: 16469.45}, 'CLOSE': {0: 35602.9, 1: 35724.95, 2: 35848.3, 3: 16657.35, 4: 16663.3}, 'SETTLE_PR': 
{0: 35602.9, 1: 35724.95, 2: 35848.3, 3: 16657.35, 4: 16663.3}, 'CONTRACTS': {0: 141916, 1: 2603, 2: 540, 3: 84, 4: 8}, 'VAL_INLAKH': {0: 1257088.18, 1: 23143.88, 2: 4819.29, 3: 555.97, 4: 53.09}, 'OPEN_INT': {0: 1723450, 1: 61975, 2: 5700, 3: 1640, 4: 240}, 'CHG_IN_OI': {0: -21550, 1: 1000, 2: 500, 3: 360, 4: -40}, 'TIMESTAMP': {0: '31-MAY-2021', 1: '31-MAY-2021', 2: '31-MAY-2021', 3: '31-MAY-2021', 4: '31-MAY-2021'}, 'Unnamed: 15': {0: nan, 1: nan, 2: nan, 3: nan, 4: nan}}

For debugging you could print df1[df1['EXPIRY_DT'] != '24-Jun-21'] to see what is actually in the selection. My guess is that the date is of type datetime and not str. Could print df.dtypes? — Rafael-WO
– Rafael-WO, Commented May 31, 2021 at 15:05
printing df1[df1['EXPIRY_DT'] != '24-Jun-21'] prints all the data including 24 -jun-21 — Akshad Patil
– Akshad Patil, Commented May 31, 2021 at 15:10

Sentinel · Accepted Answer · 2021-05-31 15:18:23Z

1

You are writing the year as 21 instead of 2021

df1.drop(df1[df1['EXPIRY_DT'] != '24-Jun-2021'].index, inplace=True)

answered May 31, 2021 at 15:18

Sentinel

2611 silver badge8 bronze badges

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

2 Comments

astrochun Over a year ago

I agree. It's an issue of str comparison.

Akshad Patil Over a year ago

yes this was the issue, but as you can see the image year was 21

ThePyGuy · Accepted Answer · 2021-05-31 15:18:52Z

0

Since there is leading space, you can first strip off the spaces and then perform the action

df1.drop(df1[df1['EXPIRY_DT'].str.strip() != '24-Jun-21'].index, inplace=True)

PS: '24-Jun-21' is not same as ' 24-Jun-21'

EDIT: If the method mentioned above doesn't work, do this: df1[:5].to_dict() and add the output data to the question, Can't assist further until you add the exact data as plain text

UPDATE:

The data you have added doesn't have the value like '24-Jun-21' the value is like 24-Jun-2021 which is obviously different.

Change the value to 24-Jun-2021 and it will work:

df1.drop(df1[df1['EXPIRY_DT'].str.strip() != '24-Jun-2021'].index, inplace=True)

OUTPUT:

df1
  INSTRUMENT     SYMBOL    EXPIRY_DT  ...  CHG_IN_OI    TIMESTAMP  Unnamed: 15
0     FUTIDX  BANKNIFTY  24-Jun-2021  ...     -21550  31-MAY-2021          NaN

edited May 31, 2021 at 15:18

answered May 31, 2021 at 15:04

ThePyGuy

18.5k5 gold badges24 silver badges55 bronze badges

3 Comments

Akshad Patil Over a year ago

this didnt work, i thought there is leading space, because i can see it visually, i dont know exactly

ThePyGuy Over a year ago

Do this: df1[:5].to_dict() and add the output data to the question, Can't assist further until you add the exact data as plain text, and also add what you mean by it didn't work, did it threw any error?

Akshad Patil Over a year ago

i have added the output data to question

Collectives™ on Stack Overflow

delete rows from dataframe python

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related