How To Drop Rows From Pandas Dataframe Based on Column Values – Definitive Guide

Pandas Dataframe allows you to store data in rows and columns format.

You can drop rows from Pandas dataframe based on column values using df.drop(df.index[df[‘Column name’] == ‘value’], inplace=True) statement.

This tutorial teaches you the different methods to drop rows from pandas dataframe based on column values.

If You’re in Hurry…

You can use the drop() method and pass the indexes of the rows with the specific column values to be deleted, as demonstrated in the following code.

df.drop(df.index[df['Available_Quantity'] == 'Not Available'], inplace=True)

df

It deletes the rows in which the column Available_Quantity has the value of Not Available.

If You Want to Understand Details, Read on…

You need to delete rows in the pandas dataframe while cleaning up the data.

To just select rows based on column values instead of deleting them from the dataframe, read: How To Select Rows From Pandas Dataframe Based On Column Values

Sample Dataframe

You’ll create a pandas dataframe using the following code.

It contains rows with different types of values. You can use it to demonstrate the dropping of the columns.

import pandas as pd

data = {"product_name":["Keyboard","Mouse", "Monitor", "CPU","CPU", "Speakers",pd.NaT],
        "Unit_Price":[500,200, 5000.235, 10000.550, 10000.550, 250.50,None],
        "No_Of_Units":[5,6, 10, 20, 20, 8,0],
        "Available_Quantity":[5,6,10,10,"Not Available", pd.NaT,pd.NaT],
        "Available_Since_Date":['11/5/2021', '4/23/2021', '08/21/2021','09/18/2021','09/18/2021','01/05/2021',pd.NaT]
       }

df = pd.DataFrame(data)

df

Dataframe Will Look Like

product_nameUnit_PriceNo_Of_UnitsAvailable_QuantityAvailable_Since_Date
0Keyboard500.0005511/5/2021
1Mouse200.000664/23/2021
2Monitor5000.235101008/21/2021
3CPU10000.550201009/18/2021
4CPU10000.55020Not Available09/18/2021
5Speakers250.5008NaT01/05/2021
6NaTNaN0NaTNaT

Drop Rows Based On a Column Value

This section teaches you how to drop rows based on a specific value in the column.

The drop() method is used to delete rows from the dataframe.

It accepts the following parameters.

  • Index – Index of the rows that need to be deleted
  • inplace – To specify whether the delete operation is to be performed in the same dataframe or the copy of the dataframe. True deletes in the same dataframe object. False returns a new dataframe object after deleting specific rows

Index Attribute

To fetch the index of the rows to be deleted, you can use the index[] property. It returns the indexes based on the condition specified.

Code

The following code demonstrates how to select the indexes of the rows containing Available_quantity = Not Available and delete them from the dataframe using the drop() method.

df.drop(df.index[df['Available_Quantity'] == 'Not Available'], inplace=True)

df

Dataframe Will Look Like

All the rows with Not Available values are dropped from the dataframe, and the dataframe looks like the one below.

product_nameUnit_PriceNo_Of_UnitsAvailable_QuantityAvailable_Since_Date
0Keyboard500.0005511/5/2021
1Mouse200.000664/23/2021
2Monitor5000.235101008/21/2021
3CPU10000.550201009/18/2021
5Speakers250.5008NaT01/05/2021
6NaTNaN0NaTNaT

Drop Rows Where Column Value is in List

This section teaches you how to drop rows based on the column values available in the list.

Here also, you need to use the drop() method and the index attribute.

  • Use the isin() method to fetch the rows with values available in the list
  • Pass the rows from the isin() method to the index attribute, and it’ll return the indexes of the rows
  • Pass those indexes to the drop() method, and those rows will be dropped from the dataframe.

Code

The following code demonstrates how to drop rows where column values are available in a list.

df.drop(df.index[df['No_Of_Units'].isin([8,10])], inplace = True)

df

Dataframe Will Look Like

The rows with column values 8 and 10 for the column Number of units are deleted, and the dataframe looks like the one below.

product_nameUnit_PriceNo_Of_UnitsAvailable_QuantityAvailable_Since_Date
0Keyboard500.005511/5/2021
1Mouse200.00664/23/2021
3CPU10000.55201009/18/2021
4CPU10000.5520Not Available09/18/2021
6NaTNaN0NaTNaT

Drop Rows Where Column Value Not In List

This section teaches you how to drop rows where the column values are NOT available in the list.

Here also, you need to use the drop() method and the index attribute.

  • Use the tilde~ operator and isin() method to fetch the rows with values that are NOT available in the list
  • Pass the rows from the ~ and isin() method to the index attribute, and it’ll return the indexes of the rows
  • Pass those indexes to the drop() method, and those rows will be dropped from the dataframe.

Code

The following code demonstrates how to drop rows where column values are available in a list.

df.drop(df.index[~df['No_Of_Units'].isin([8,10])], inplace = True)

df

Dataframe Will Look Like

The rows with column values other than 8 and 10 for the column Number of units are deleted, and the dataframe looks like the one below.

product_nameUnit_PriceNo_Of_UnitsAvailable_QuantityAvailable_Since_Date
2Monitor5000.235101008/21/2021
5Speakers250.5008NaT01/05/2021

Drop Rows Where Column Value is NaN

This section teaches you how to drop rows where a column value is NaN.

NaN is used to denote the missing values.

To drop the rows with NaN values,

  • Use the notnull() method to select the rows with a column that doesn’t have any missing value.
  • Pass the notnull() result to the dataframe again to select the subset of the rows that don’t contain missing values.

Code

The following code demonstrates how to delete the rows from the dataframe with NaN values in the column Available_Quantity.

df = df[df.Available_Quantity.notnull()]

df

Dataframe Will Look Like

product_nameUnit_PriceNo_Of_UnitsAvailable_QuantityAvailable_Since_Date
0Keyboard500.0005511/5/2021
1Mouse200.000664/23/2021
2Monitor5000.235101008/21/2021
3CPU10000.550201009/18/2021
4CPU10000.55020Not Available09/18/2021

Drop Rows Where Column Value is Zero

This section teaches you how to drop rows where the column value is zero.

To delete rows where the column value is zero,

  • Select the rows where the no_of_units is Zero
  • Using the index attribute, select the index of those rows
  • Using the drop() method, drop those indexes

Code

The following code demonstrates how to drop rows where a specific column value is Zero.

df.drop(df.index[df['No_Of_Units']==0], inplace=True)

df

Dataframe Will Look Like

The rows with Column values Zero are deleted, and the dataframe will look like the one below.

product_nameUnit_PriceNo_Of_UnitsAvailable_QuantityAvailable_Since_Date
0Keyboard500.0005511/5/2021
1Mouse200.000664/23/2021
2Monitor5000.235101008/21/2021
3CPU10000.550201009/18/2021
4CPU10000.55020Not Available09/18/2021
5Speakers250.5008NaT01/05/2021

Drop Rows Based On Multiple Column Values

This section teaches you to drop rows based on multiple column values.

You can combine the multiple conditions using the & operator and select the index of the rows matching the specific conditions.

Code

The following code demonstrates how to drop rows based on multiple conditions and column values.

df.drop(df[(df['Unit_Price'] >400) & (df['Unit_Price'] < 600)].index, inplace=True)

df

Dataframe Will Look Like

product_nameUnit_PriceNo_Of_UnitsAvailable_QuantityAvailable_Since_Date
1Mouse200.000664/23/2021
2Monitor5000.235101008/21/2021
3CPU10000.550201009/18/2021
4CPU10000.55020Not Available09/18/2021
5Speakers250.5008NaT01/05/2021

Conclusion

You’ve learned how to drop rows from pandas dataframe based on column values.

You’ve also learned how to delete rows based on a list of values, where the value is NaNor where the value is Zero.

If you have any questions, please feel free to comment below.

You May Also Like

Leave a Comment