Pandas Trick: How to Clean Up Column Headers

Introduction

Cleaning up your pandas dataframe headers is essential for making your dataframes more readable and easier to understand. A messy header can lead to potential errors when processing data, especially when using dot notation for selecting columns. In this post, I’ll share how to tidy up your column headers effectively.

Custom Function

To start, I can create a pandas dataframe that showcases a messy header. The header may contain empty spaces, special characters, and inconsistent styling. To tackle this, I can create a custom function to clean up the header. This function will:

• Check if the value is a string.
• Iterate over each character in the string.
• Remove any non-alphanumeric characters, except for spaces and underscores.
• Strip leading and trailing white spaces.
• Transform the string to lowercase and replace spaces with underscores.

By applying this function using the rename method on the dataframe, I can achieve a much cleaner header.

Dataprep

Sometimes, headers can be even messier than expected, with values like “Not a number” or duplicates. While I could tweak my custom function, there are also Python packages that can help. The first package is dataprep, which can be installed with:

pip install -U dataprep

After installing, I can import clean headers from the dataprep clean module. This package does a fantastic job of cleaning headers and can convert them to snake case by default. Additionally, it offers options to convert header names to different cases, including camel case. If I want to replace specific characters, I can use the replace argument with a dictionary.

Skimpy

For those who prefer a lighter alternative, I can use another library called skimpy, which can be installed with:

pip install skimpy

Upon installation, I can import clean headers from skimpy. This library essentially uses similar code to dataprep, allowing for the same functionalities such as specifying case or replacing values.

Outro

In summary, cleaning up your pandas dataframe headers is vital for data integrity and readability. Whether you choose to implement a custom function, use the dataprep library, or opt for the lighter skimpy library, each method offers a way to enhance your dataframe headers. If you have any questions or need further clarification, feel free to ask in the comments!

Further Links

• Source Code