How to clean and normalize text data in an ETL with pandas

Text data from forms, exports or APIs almost always arrives messy: extra spaces, inconsistent capitalisation, accents and the same category written in several ways. Learning to clean and normalize text data in an ETL with pandas makes sure these values become consistent before they are loaded into the destination, and it is one of the steps that most prevents errors and duplicates downstream. We will use a simple example with city and status names, but the same approach works for any text column.

Prerequisites

• Python 3.10 or later installed.
• The pandas library installed (pip install pandas).
• Basic knowledge of DataFrame and how to run a Python script.

Step 1: Prepare sample data

To see the effect of each transformation, start by creating a small DataFrame that mimics real "dirty" data: the same city written in different ways and a status with inconsistent capitalisation. Data like this appears when several people fill in the same field by hand or when files from different sources are combined.

import pandas as pd

df = pd.DataFrame({
    "cidade": ["  Lisboa ", "PORTO", "porto ", "Évora", " lisboa"],
    "estado": ["Ativo", "ativo ", "INATIVO", " Ativo", "inativo"],
})
print(df)

Note that "Lisboa", " lisboa" and "PORTO" represent only two cities, but the computer sees them as different values. That is what we are going to fix.

Step 2: Remove extra spaces

The first problem is spaces at the start, at the end and repeated inside the text. The str.strip() method trims the ends and str.replace() with a regular expression collapses several spaces into one. We apply it to every text column at once.

for col in ["cidade", "estado"]:
    df[col] = df[col].str.strip()
    df[col] = df[col].str.replace(r"\s+", " ", regex=True)

Tip: since pandas 2.0, the regex parameter of str.replace() defaults to False. Always pass regex=True when you use a pattern.

Step 3: Standardise upper and lower case

"PORTO", "porto" and "Porto" should be the same value. We apply str.title() to the city names, so they start with a capital letter, and str.lower() to the status, which we will standardise next. We use str.title() instead of str.upper() because we want readable names, not everything in capitals.

df["cidade"] = df["cidade"].str.title()
df["estado"] = df["estado"].str.lower()

Step 4: Remove accents

To create consistent keys — for example, to join tables by city name — it is worth removing accents. NFKD normalization separates the letter from the accent; by encoding to ASCII and ignoring what does not fit, the accents disappear and "Évora" becomes "Evora".

df["cidade"] = (
    df["cidade"]
    .str.normalize("NFKD")
    .str.encode("ascii", "ignore")
    .str.decode("utf-8")
)

Step 5: Standardise categories with a dictionary

Finally, we map each variation to an official label using a dictionary and the map() method. A value that is not in the dictionary becomes NaN — which is useful, because it reveals unexpected categories you have not handled yet. If you prefer to keep unknown values instead of turning them into NaN, use replace() instead of map().

mapa_estado = {"ativo": "Ativo", "inativo": "Inativo"}
df["estado"] = df["estado"].map(mapa_estado)

Check the result

Confirm the result by printing the unique values of each column and the count of missing values. If the cleaning went well, each city and each status appears written in a single way.

print(df["cidade"].unique())
print(df["estado"].unique())
print(df.isna().sum())

You should get ['Lisboa' 'Porto' 'Evora'] for the cities and ['Ativo' 'Inativo'] for the statuses, with no missing values. If any NaN appears, a category was left unmapped in Step 5.

Conclusion

With just a few pandas methods you turned inconsistent text into clean, predictable data, ready for the loading stage of your ETL. The natural next step is to gather these transformations into a reusable function and apply it to every file you process, ensuring the same treatment across the whole pipeline. Which other column in your dataset would benefit from being normalized this way?