Pandas Delete DataFrame Rows Based on Column Value

You can use pandas.DataFrame.drop() method to delete rows based on column value, as part of the data cleansing, you would be required to drop rows from the DataFrame when a column value matches with a static value or on another column value.

In my earlier article, I have covered how to drop rows by index label from DataFrame, and in this article, I will cover several examples of dropping rows based on applying conditions on column value.

Alternatively, you can also achieve dropping rows by filtering rows and assigning it to another DataFrame.

1. Quick Examples of Delete Pandas DataFrame Rows Based on Column Value

If you are in a hurry, below are some quick examples removing the DataFrame rows based on column value. Note that bottom of the article I have covered multiple other examples.


# Quick Examples

#Using drop() to delete rows based on column value
df.drop(df[df['Fee'] >= 24000].index, inplace = True)

# Remove rows
df2 = df[df.Fee >= 24000]

# If you have space in column name
# Specify column name with in single quotes
df2 = df[df['column name']]

# Using loc
df2 = df.loc[df["Fee"] >= 24000 ]

# Delect rows based on multiple column value
df2 = df[ (df['Fee'] >= 22000) & (df['Discount'] == 2300)]

# Drop rows with None/NaN
df2 = df[df.Discount.notnull()]

Let’s create a DataFrame with a few rows and columns and execute some examples to learn how to drop DataFrame row. Our DataFrame contains column names Courses, Fee, Duration, and Discount.


# Create pandas DataFrame
import pandas as pd
import numpy as np
technologies = {
    'Courses':["Spark","PySpark","Hadoop","Python"],
    'Fee' :[22000,25000,np.nan,24000],
    'Duration':['30day',None,'55days',np.nan],
    'Discount':[1000,2300,1000,np.nan]
          }
df = pd.DataFrame(technologies)
print(df)

Yields below output.


   Courses      Fee Duration  Discount
0    Spark  22000.0    30day    1000.0
1  PySpark  25000.0     None    2300.0
2   Hadoop      NaN   55days    1000.0
3   Python  24000.0      NaN       NaN

2. Using DataFrame.drop() to Delete Rows Based on Column Values

drop() method takes several params that help you to delete rows from DataFrame by checking conditions on columns. When condition expression satisfies it returns True which actually removes the rows.


df.drop(df[df['Fee'] >= 24000].index, inplace = True)
print(df)

Yields below output.


  Courses      Fee Duration  Discount
0   Spark  22000.0    30day    1000.0
2  Hadoop      NaN   55days    1000.0

After removing rows, it is always recommended to reset the row index.

2. Using loc[] to Remove Rows

Alternatively, you can also try another most used approach to drop rows based on column values using loc[] and df[].

Note that thess methods actually filters the data, by negating this you will get the desired output.


# Remove row
df2 = df[df.Fee >= 24000]
print(df2)

#Using loc[]
df2 = df.loc[df["Fee"] >= 24000 ]
print(df2)

Yields same output as above.


   Courses      Fee Duration  Discount
1  PySpark  25000.0     None    2300.0
3   Python  24000.0      NaN       NaN

3. Delete Rows Based on Multiple Column Values

Sometimes it may require you to delete the rows based on matching values of multiple columns.


# Delect rows based on multiple column value
df = pd.DataFrame(technologies)
df = df[ (df['Fee'] >= 22000) & (df['Discount'] == 2300)]
print(df)

Yields below output.


   Courses      Fee Duration  Discount
1  PySpark  25000.0     None    2300.0

4. Delete Rows Based on None or NaN Column Values

When you have None or NaN values on columns, you may need to remove these before you applying some calculations. you can do this using notnull() function.

Note: With None or NaN values you cannot use == or != operators.


# Drop rows with None/NaN values
df2 = df[df.Discount.notnull()]
print(df2)

Yields below output


   Courses      Fee Duration  Discount
0    Spark  22000.0    30day    1000.0
1  PySpark  25000.0     None    2300.0
2   Hadoop      NaN   55days    1000.0

5. Other Ways to Delete Rows from pandas DataFrame

You can also remove rows by using query() method. Note that these methods actually filters the rows from pandas DataFrame, by negating this you can drop the rows.



# Delete rows using DataFrame.query()
df2=df.query("Courses == 'Spark'")

#Using variable
value='Spark'
df2=df.query("Courses == @value")

#inpace
df.query("Courses == 'Spark'",inplace=True)

#Not equals, in & multiple conditions
df.query("Courses != 'Spark'")
df.query("Courses in ('Spark','PySpark')")
df.query("`Courses Fee` >= 23000")
df.query("`Courses Fee` >= 23000 and `Courses Fee` <= 24000")

# Other ways to Delete Rows
df.loc[df['Courses'] == value]
df.loc[df['Courses'] != 'Spark']
df.loc[df['Courses'].isin(values)]
df.loc[~df['Courses'].isin(values)]
df.loc[(df['Discount'] >= 1000) & (df['Discount'] <= 2000)]
df.loc[(df['Discount'] >= 1200) & (df['Fee'] >= 23000 )]

df[df["Courses"] == 'Spark'] 
df[df['Courses'].str.contains("Spark")]
df[df['Courses'].str.lower().str.contains("spark")]
df[df['Courses'].str.startswith("P")]

# Using lambda
df.apply(lambda row: row[df['Courses'].isin(['Spark','PySpark'])])
df.dropna()

6. Delete Rows Based on Inverse of Column Values

If you need to drop() all rows which are not equal to a value given for a column. pandas offer negation (~) operation to perform this feature. For E.x: df.drop(df1,inplace=True).


# Delect rows based on inverse of column values
df1 = df[~(df['Courses'] == "PySpark")].index 
df.drop(df1, inplace = True)
print(df)

Yields below output.


   Courses    Fee Duration  Discount
b  PySpark  25000   50days      2300
f  PySpark  25000   50days      2000

7. Complete Example


import pandas as pd
import numpy as np
technologies = {
    'Courses':["Spark","PySpark","Hadoop","Python"],
    'Fee' :[22000,25000,np.nan,24000],
    'Duration':['30day',None,'55days',np.nan],
    'Discount':[1000,2300,1000,np.nan]
          }
df = pd.DataFrame(technologies)
print(df)

# Using drop() to remove rows
df.drop(df[df['Fee'] >= 24000].index, inplace = True)
print(df)

# Remove rows
df = pd.DataFrame(technologies)
df2 = df[df.Fee >= 24000]
print(df2)

# Reset index after deleting rows
df2 = df[df.Fee >= 24000].reset_index()
print(df2)

# If you have space in column name.
# Surround the column name with single quote
df2 = df[df['column name']]

# Using loc
df2 = df.loc[df["Fee"] >= 24000 ]
print(df2)

# Delect rows based on multiple column value
df2 = df[(df['Fee'] >= 22000) & (df['Discount'] == 2300)]
print(df2)

# Drop rows with None/NaN
df2 = df[df.Discount.notnull()]
print(df2)

Conclusion

In this article, you have learned how to delete pandas DataFrame rows based on column value using different ways

Happy Learning !!

You May Also Like

References

NNK

SparkByExamples.com is a Big Data and Spark examples community page, all examples are simple and easy to understand and well tested in our development environment Read more ..

Leave a Reply

Pandas Delete DataFrame Rows Based on Column Value