Working with Pandas: Fixing messy column names

Chaim Gluck

Sometimes you load in that DataFrame from a csv or excel file that some unlucky excel user created and you just wish everyone used Python. Why do they have to make the column names uppercase, with spaces, and whitespace all around? Do they like doing this to you? They probably hate you, that’s it. They did this:

The nerve!

Now you can’t reference the columns with the convenient .{column name here} notation. You’ll have to do the [''] thing. It’s not the most horrible thing that ever happened, you’ve been through a lot. You can take it. But you’re used to the other way. Plus, what’s with those parentheses? You can’t have that.

Luckily, pandas has a convenient .str method that you can use on text data. Since the column names are an ‘index’ type, you can use .str on them too. You can fix all these lapses of judgement by chaining together a bunch of these .str functions. Like so:

df.columns = df.columns.str.strip().str.lower().str.replace(' ', '_').str.replace('(', '').str.replace(')', '')

Voila! Here it is:

Celebrate your victory with some tequila

And that’s one way to fix messy column names.

Chaim Gluck

Written by

I write about Machine Learning, Natural Language Processing, and other things that cross my mind. Come say hi at www.linkedin.com/in/chaimgluck

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade