How to drop rows with condition pandas

Drop or delete the row in python pandas with conditions

In this tutorial we will learn how to drop or delete the row in python pandas by index, delete row by condition in python pandas and drop rows by position. Dropping a row in pandas is achieved by using .drop() function. Lets see example of each.

Syntax of drop() function in pandas :

Create Dataframe:

the dataframe will be

Simply drop a row or observation:

Dropping the second and third row of a dataframe is achieved as follows

The above code will drop the second and third row.
0 – represents 1st row
1- represnts 2nd row and so on. So the resultant dataframe will be

Drop a row or observation by condition:

we can drop a row when it satisfies a specific condition

The above code takes up all the names except Alisa, thereby dropping the row with name ‘Alisa’. So the resultant dataframe will be

Drop a row or observation by index:

We can drop a row by index as shown below

The above code drops the row with index number 2. So the resultant dataframe will be

Drop the row by position:

Now let’s drop the bottom 3 rows of a dataframe as shown below

The above code selects all the rows except bottom 3 rows, there by dropping bottom 3 rows, so the resultant dataframe will be

Drop Duplicate rows of the dataframe in pandas

now lets simply drop the duplicate rows in pandas as shown below

In the above example first occurrence of the duplicate row is kept and subsequent duplicate occurrence will be deleted, so the output will be

For further detail on drop duplicates one can refer our page on Drop duplicate rows in pandas python drop_duplicates()

Drop rows with NA values in pandas python

Drop the rows even with single NaN or single missing values.

so the resultant table on which rows with NA values dropped will be

Outputs:

For further detail on drop rows with NA values one can refer our page

Other related topics :

for documentation on drop() function kindly refer here

dropping rows from dataframe based on a «not in» condition [duplicate]

I want to drop rows from a pandas dataframe when the value of the date column is in a list of dates. The following code doesn’t work:

I get the following error:

ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

2 Answers 2

Trending sort

Trending sort is based off of the default sorting method — by highest score — but it boosts votes that have happened recently, helping to surface more up-to-date answers.

It falls back to sorting by highest score if no posts are trending.

Switch to Trending sort

pandas.Dateframe.isin will return boolean values depending on whether each element is inside the list a or not. You then invert this with the

to convert True to False and vice versa.

df.isin(a)] SystemError: returned a result with an error set

While the error message suggests that all() or any() can be used, they are useful only when you want to reduce the result into a single Boolean value. That is however not what you are trying to do now, which is to test the membership of every values in the Series against the external list, and keep the results intact (i.e., a Boolean Series which will then be used to slice the original DataFrame).

You can read more about this in the Gotchas.

Not the answer you’re looking for? Browse other questions tagged python pandas or ask your own question.

Linked

Related

Hot Network Questions

By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy.

Deleting DataFrame row in Pandas based on column value

I have the following DataFrame:

16 Answers 16

Trending sort

Trending sort is based off of the default sorting method — by highest score — but it boosts votes that have happened recently, helping to surface more up-to-date answers.

It falls back to sorting by highest score if no posts are trending.

Switch to Trending sort

If I’m understanding correctly, it should be as simple as:

df[‘DATE’].isin([‘2015-10-30.1’, ‘2015-11-30.1’, ‘2015-12-31.1’])]

Doesn’t do anything:

just to add another solution, particularly useful if you are using the new pandas assessors, other solutions will replace the original pandas and lose the assessors

If you want to delete rows based on multiple values of the column, you could use:

The best way to do this is with boolean masking:

In case of multiple values and str dtype

I used the following to filter out given values in a col:

In a DataFrame I want to remove rows which have values «b» and «c» in column «str»

invert operator df[

One of the efficient and pandaic way is using eq() method:

Another way of doing it. May not be the most efficient way as the code looks a bit more complex than the code mentioned in other answers, but still alternate way of doing the same thing.

Adding one more way to do this.

I compiled and run my code. This is accurate code. You can try it your own.

If you have any special character or space in column name you can write it in » like in the given code:

If there is just a single string column name without any space or special character you can directly access it.

Just adding another way for DataFrame expanded over all columns:

Just in case you need to delete the row, but the value can be in different columns. In my case I was using percentages so I wanted to delete the rows which has a value 1 in any column, since that means that it’s the 100%

Is not optimal if your df have too many columns.

Here’s a scalable syntax that is easy to understand and can handle complicated logic:

Python Pandas : How to Drop rows in DataFrame by conditions on column values

In this article we will discuss how to delete rows based in DataFrame by checking multiple conditions on column values.

DataFrame provides a member function drop() i.e.

It accepts a single or list of label names and deletes the corresponding rows or columns (based on value of axis parameter i.e. 0 for rows or 1 for columns).

Let’s use this do delete multiple rows by conditions.

Let’s create a dataframe object from dictionary

Delete rows based on condition on a column

Contents of dataframe object dfObj will be,

Original DataFrame pointed by dfObj

Let’s delete all rows for which column ‘Age’ has value 30 i.e.

Contents of updated dataframe object dfObj will be,

DataFrame rows with value 30 in Column Age are deleted

It will give Series object with True and False. True for entries which has value 30 and False for others i.e.

Let’s create a new DataFrame object with this series and existing DataFrame object dfObj i.e.

It will give a new dataframe object that has only that row for which column ‘Age’ has value 30 i.e.

Name Age City Country
b Riti 30 Delhi India

Now, this dataframe contains the rows which we want to delete from original dataframe. So, let’s get the index names from this dataframe object i.e.

It will give an Index object containing index labels for which column ‘Age’ has value 30 i.e.

Now pass this to dataframe.drop() to delete these rows i.e.

It will delete the all rows for which column ‘Age’ has value 30.

Delete rows based on multiple conditions on a column

Suppose Contents of dataframe object dfObj is,

Original DataFrame pointed by dfObj

Let’s delete all rows for which column ‘Age’ has value between 30 to 40 i.e.

Contents of modified dataframe object dfObj will be,

Rows with column ‘Age’ value 30 to 40 deleted

basically we need to use & between multiple conditions.

Delete rows based on multiple conditions on different columns

Suppose Contents of dataframe object dfObj is,

Original DataFrame pointed by dfObj

Let’s delete all rows for which column ‘Age’ has value greater than 30 and country is ‘India’

Contents of modified dataframe object dfObj will be,

Rows deleted whose Age > 30 & country is India

We need to use & between multiple conditions.

Complete Example is as follows,

Output:

Are you looking to make a career in Data Science with Python?

Data Science is the future, and the future is here now. Data Scientists are now the most sought-after professionals today. To become a good Data Scientist or to make a career switch in Data Science one must possess the right skill set. We have curated a list of Best Professional Certificate in Data Science with Python. These courses will teach you the programming tools for Data Science like Pandas, NumPy, Matplotlib, Seaborn and how to use these libraries to implement Machine learning models.

Checkout the Detailed Review of Best Professional Certificate in Data Science with Python.

Remember, Data Science requires a lot of patience, persistence, and practice. So, start learning today.

How to delete rows from a pandas DataFrame based on a conditional expression [duplicate]

I have a pandas DataFrame and I want to delete rows from it where the length of the string in a particular column is greater than 2.

I expect to be able to do this (per this answer):

but I just get the error:

What am I doing wrong?

6 Answers 6

Trending sort

Trending sort is based off of the default sorting method — by highest score — but it boosts votes that have happened recently, helping to surface more up-to-date answers.

It falls back to sorting by highest score if no posts are trending.

Switch to Trending sort

To directly answer this question’s original title «How to delete rows from a pandas DataFrame based on a conditional expression» (which I understand is not necessarily the OP’s problem but could help other users coming across this question) one way to do this is to use the drop method:

Example

To remove all rows where column ‘score’ is 20

You can assign the DataFrame to a filtered version of itself:

This is faster than drop :

I will expand on @User’s generic solution to provide a drop free alternative. This is for folks directed here based on the question’s title (not OP ‘s problem)

Say you want to delete all rows with negative values. One liner solution is:-

Step by step Explanation:—

Let’s generate a 5×5 random normal distribution data frame

Let the condition be deleting negatives. A boolean df satisfying the condition:-

A boolean series for all rows satisfying the condition Note if any element in the row fails the condition the row is marked false

Finally filter out rows from data frame based on the condition

You can assign it back to df to actually delete vs filter ing done above
df = df[(df > 0).all(axis=1)]

This can easily be extended to filter out rows containing NaN s (non numeric entries):-
df = df[(

This can also be simplified for cases like: Delete all rows where column E is negative

I would like to end with some profiling stats on why @User’s drop solution is slower than raw column based filtration:-

A column is basically a Series i.e a NumPy array, it can be indexed without any cost. For folks interested in how the underlying memory organization plays into execution speed here is a great Link on Speeding up Pandas: