← Back to Blog

How to Drop Columns Containing a Specific String from Pandas DataFrame

As a data scientist or software engineer, you may come across a situation where you need to drop columns from a Pandas DataFrame that contain a specific string. This could be because these columns are not relevant to your analysis, or they may contain sensitive information that needs to be removed.
By Saturn Cloud | | Miscellaneous | Updated:

As a data scientist or software engineer, you may come across a situation where you need to drop columns from a Pandas DataFrame that contain a specific string. This could be because these columns are not relevant to your analysis, or they may contain sensitive information that needs to be removed.

In this tutorial, we will show you how to drop columns containing a specific string from a Pandas DataFrame using Python.

Table of Contents

  1. Introduction
  2. How to Drop Columns Containing a Specific String from Pandas DataFrame
  3. Using List Comprehension
  4. Using filter method
  5. Considerations
  6. Conclusion

What is Pandas?

Pandas is a Python library that is commonly used for data manipulation and analysis. It provides data structures and functions for working with structured data, such as tables and time series.

One of the most popular data structures in Pandas is the DataFrame. A DataFrame is a two-dimensional table that can store data of different types, such as integers, floats, and strings. It is similar to a spreadsheet or SQL table.

How to Drop Columns Containing a Specific String from Pandas DataFrame

To drop columns containing a specific string from a Pandas DataFrame, we will use the drop() function. This function allows us to remove one or more columns from a DataFrame.

The drop() function takes two main arguments: labels and axis. The labels argument specifies the columns to remove, while the axis argument specifies the axis to remove them from. By default, the axis argument is set to 0, which means that the function will remove rows. To remove columns, we need to set the axis argument to 1.

Here is the basic syntax of the drop() function:

df.drop(labels, axis=1, inplace=True)

Let’s break down each argument:

  • labels: This argument specifies the columns to remove. It can be a single column name, a list of column names, or a slice of column names. We can also use a boolean array to specify which columns to remove based on their values.
  • axis: This argument specifies the axis to remove the columns from. We set it to 1 to remove columns.
  • inplace: This argument specifies whether to modify the original DataFrame or return a new one. If we set it to True, the original DataFrame will be modified. If we set it to False (the default), a new DataFrame will be returned.

Now, let’s see how we can use the drop() function to remove columns containing a specific string from a Pandas DataFrame.

Step 1: Import the Pandas Library

First, we need to import the Pandas library using the import statement:

import pandas as pd

Step 2: Create a Sample DataFrame

Next, we will create a sample DataFrame to work with. For this example, we will create a DataFrame with five columns:

data = {
    'Name': ['John', 'Alice', 'Bob', 'Charlie', 'David'],
    'Age': [25, 30, 35, 40, 45],
    'City': ['New York', 'Los Angeles', 'Chicago', 'Houston', 'Philadelphia'],
    'Email': ['john@example.com', 'alice@example.com', 'bob@example.com', 'charlie@example.com', 'david@example.com'],
    'Phone Number': ['555-1234', '555-5678', '555-9012', '555-3456', '555-7890']
}

df = pd.DataFrame(data)

The resulting DataFrame looks like this:

      Name  Age          City                Email Phone Number
0     John   25      New York     john@example.com     555-1234
1    Alice   30   Los Angeles    alice@example.com     555-5678
2      Bob   35       Chicago      bob@example.com     555-9012
3  Charlie   40       Houston  charlie@example.com     555-3456
4    David   45  Philadelphia    david@example.com     555-7890

Step 3: Drop Columns Containing a Specific String

Suppose we want to remove all columns containing the string “Email”. To do this, we can use the str.contains() method to create a boolean array indicating which columns contain the string “Email”, and then pass this array to the drop() function.

Here is the code to accomplish this:

# Create a boolean array indicating which columns contain the string "Email"
cols_to_drop = df.columns[df.columns.str.contains('Email')]

# Drop the columns containing the string "Email"
df.drop(cols_to_drop, axis=1, inplace=True)

The resulting DataFrame looks like this:

       Name  Age          City Phone Number
0      John   25      New York     555-1234
1     Alice   30   Los Angeles     555-5678
2       Bob   35       Chicago     555-9012
3   Charlie   40       Houston     555-3456
4     David   45  Philadelphia     555-7890

As you can see, the columns containing the string “Email” have been removed from the DataFrame.

Step 4: Drop Multiple Columns Containing a Specific String

Suppose we want to remove all columns containing either the string “Email” or the string “Phone”. To do this, we can create a boolean array indicating which columns contain either string, and then pass this array to the drop() function.

Here is the code to accomplish this:

# Create a boolean array indicating which columns contain either "Email" or "Phone"
cols_to_drop = df.columns[df.columns.str.contains('Email|Phone')]

# Drop the columns containing either "Email" or "Phone"
df.drop(cols_to_drop, axis=1, inplace=True)

The resulting DataFrame looks like this:

       Name  Age          City
0      John   25      New York
1     Alice   30   Los Angeles
2       Bob   35       Chicago
3   Charlie   40       Houston
4     David   45  Philadelphia

As you can see, the columns containing either “Email” or “Phone” have been removed from the DataFrame.

Pros

  • Clarity and Simplicity: The method is straightforward and easy to understand, making it accessible to users at various skill levels.

  • Applicability: Suitable for scenarios where the condition involves a simple string match, such as dropping columns containing a specific string.

Cons

  • Limited to String Matching: The method is primarily designed for scenarios involving direct string matching. It may not be as versatile for more complex conditions.

  • In-Place Modification: The inplace=True parameter modifies the original DataFrame, which might not be desirable in all situations.

  • Requires Knowledge of Column Names: Users need to know the exact column names or patterns, which might be challenging for dynamically changing datasets.

Using List Comprehension

If you prefer a concise and Pythonic approach, you can leverage list comprehension to create a new DataFrame excluding columns containing a specified string. Here’s an example:

import pandas as pd

# Create a sample DataFrame
data = {
    'Name': ['John', 'Alice', 'Bob', 'Charlie', 'David'],
    'Age': [25, 30, 35, 40, 45],
    'City': ['New York', 'Los Angeles', 'Chicago', 'Houston', 'Philadelphia'],
    'Email': ['john@example.com', 'alice@example.com', 'bob@example.com', 'charlie@example.com', 'david@example.com'],
    'Phone Number': ['555-1234', '555-5678', '555-9012', '555-3456', '555-7890']
}

df = pd.DataFrame(data)

# Specify the string to exclude
string_to_exclude = 'Email'

# Create a new DataFrame with columns not containing the specified string
new_df = df[[col for col in df.columns if string_to_exclude not in col]]

The resulting DataFrame looks like this:

      Name  Age          City Phone Number
0     John   25      New York     555-1234
1    Alice   30   Los Angeles     555-5678
2      Bob   35       Chicago     555-9012
3  Charlie   40       Houston     555-3456
4    David   45  Philadelphia     555-7890

Pros

  • Conciseness and Readability: List comprehension is concise and often considered more readable, especially for simple conditions.

  • Pythonic Approach: It follows a Pythonic coding style, making it familiar to those well-versed in Python.

Cons

  • Limited Functionality: List comprehension might be less suitable for complex conditions or situations that require more advanced functionality.

  • Creation of Intermediate List: It creates an intermediate list in memory, which can be a concern for large datasets.

Using filter method

Alternatively, the filter method provides a convenient way to select columns based on a specified condition. Here’s how you can use it to create a new DataFrame without columns containing a particular string:

import pandas as pd

# Create a sample DataFrame
data = {
    'Name': ['John', 'Alice', 'Bob', 'Charlie', 'David'],
    'Age': [25, 30, 35, 40, 45],
    'City': ['New York', 'Los Angeles', 'Chicago', 'Houston', 'Philadelphia'],
    'Email': ['john@example.com', 'alice@example.com', 'bob@example.com', 'charlie@example.com', 'david@example.com'],
    'Phone Number': ['555-1234', '555-5678', '555-9012', '555-3456', '555-7890']
}

df = pd.DataFrame(data)

# Specify the string to exclude
string_to_exclude = 'Email'

# Create a new DataFrame with columns not containing the specified string
new_df = df.filter(regex=f'^(?!.*{string_to_exclude}).*$', axis=1)

The resulting DataFrame looks like this:

      Name  Age          City Phone Number
0     John   25      New York     555-1234
1    Alice   30   Los Angeles     555-5678
2      Bob   35       Chicago     555-9012
3  Charlie   40       Houston     555-3456
4    David   45  Philadelphia     555-7890

Pros

  • Built-in Pandas Functionality: Utilizes the built-in filter method in Pandas, providing a more Pandas-centric approach.

  • Regex Patterns: The filter method allows the use of regex patterns for more flexible column selection.

Cons

  • Regex Complexity: The regex pattern used in the filter method might be more complex for users unfamiliar with regular expressions.

  • Less Intuitive for Some: For users less familiar with Pandas, the filter method might seem less intuitive compared to list comprehension.

Considerations

  1. Complexity of Condition:
  • If your condition involves a simple string match, the original method using str.contains() and df.drop() is straightforward and sufficient.
  • For more complex conditions, such as excluding columns based on multiple strings or patterns, list comprehension and the filter method may provide more flexibility.
  1. Readability and Familiarity:
  • Consider the familiarity of your team with different coding styles and approaches. The original method may be more familiar to those accustomed to Pandas.
  • List comprehension is concise and Pythonic, suitable for those who appreciate a more compact coding style.
  • The filter method might be preferred by users who are already comfortable with Pandas and want to leverage its built-in functionality.
  1. Performance:
  • For small to medium-sized datasets, performance differences between the methods may be negligible. However, consider performance implications for large datasets.
  • The original method and the filter method may have slight performance advantages over list comprehension due to their more direct use of Pandas functions.
  1. Versatility:
  • List comprehension and the filter method offer more versatility in handling complex conditions and dynamic column selection.
  • The original method is well-suited for specific string matching scenarios but may be less versatile in handling more intricate conditions.
  1. In-Place Modification:
  • If modifying the original DataFrame in-place is a concern, consider the inplace=False option for the original method or using assignments for the alternatives.
  1. Regex Usage:
  • If your condition involves regular expressions or more intricate pattern matching, the filter method provides a convenient way to incorporate regex patterns.
  1. Memory Efficiency:
  • The original method may be more memory-efficient for large datasets since it directly operates on the DataFrame without creating intermediate lists.
  1. User Expertise:
  • Consider the expertise level of the users who will be working with the code. The original method may be more accessible for beginners, while list comprehension and the filter method might be preferred by more experienced users.

Conclusion

In this tutorial, we have shown you how to drop columns containing a specific string from a Pandas DataFrame using Python. This is a useful technique for data scientists and software engineers who need to remove irrelevant or sensitive columns from their data.

We hope that you have found this tutorial helpful. If you have any questions or comments, please feel free to leave them below.

About Saturn Cloud

Saturn Cloud is your all-in-one solution for data science & ML development, deployment, and data pipelines in the cloud. Spin up a notebook with 4TB of RAM, add a GPU, connect to a distributed cluster of workers, and more. Request a demo today to learn more.

Start for free