Cleaning Dirty Customer Data with Python and Pandas on Raspberry Pi

Customer data rarely arrives perfectly clean.

A business might collect customer information from web forms, spreadsheets, sales systems, or manually entered records. Eventually somebody needs that data. A manager may want to open it in Excel. A developer may want to feed it into another program.

Before either happens, we need to clean it.

For this example, we'll work entirely from the Linux terminal using Python and Pandas. We'll start with a simple CSV file containing customer names, cities, and ages.

Our basic pipeline is simple:

dirty CSV → Pandas → clean data → CSV + JSON

The Dirty Customer Data

Suppose we have a file called customers.csv:

first_name,last_name,city,age
 Alice ,Johnson,DALLAS,34
bob,smith,dallas,29
Charlie,BROWN,Austin,N/A
diana,Garcia,HOUSTON,41
 Alice ,Johnson,DALLAS,34
Evan,Williams, austin ,27
frank,Miller,,38
Grace,Davis,Dallas,

There are only eight records, but several things have gone wrong.

Some names have extra spaces. Capitalization is inconsistent. Dallas appears as DALLAS, dallas, and Dallas. One record has no city. Two records have missing ages. And Alice Johnson appears twice.

That's dirty data.

Installing Pandas

From the Linux terminal on your Raspberry Pi, we'll create a virtual environment and install Pandas:

python3 -m venv customer-env
source customer-env/bin/activate
python3 -m pip install pandas

Now create a program:

nano clean_customers.py

Cleaning the Data

Here's our program:

import pandas as pd

# INPUT
df = pd.read_csv("customers.csv")

# PROCESSING

# Remove extra spaces and normalize capitalization
df["first_name"] = (
    df["first_name"]
    .str.strip()
    .str.title()
)

df["last_name"] = (
    df["last_name"]
    .str.strip()
    .str.title()
)

df["city"] = (
    df["city"]
    .str.strip()
    .str.title()
)

# Convert age to numeric values
df["age"] = pd.to_numeric(
    df["age"],
    errors="coerce"
)

# Make missing cities explicit
df["city"] = df["city"].fillna("Unknown")

# Remove duplicate customer records
df = df.drop_duplicates()

# Reset row numbers after removing duplicates
df = df.reset_index(drop=True)

# OUTPUT
df.to_csv(
    "customers_clean.csv",
    index=False
)

df.to_json(
    "customers_clean.json",
    orient="records",
    indent=2
)

print(df)
print("\nCreated customers_clean.csv")
print("Created customers_clean.json")

Run it:

python3 clean_customers.py

The program first loads the CSV into a Pandas DataFrame.

It then strips extra spaces from the name and city fields and converts their capitalization into a consistent form. DALLAS, dallas, and Dallas, for example, all become Dallas.

The age column is converted to numeric data. The important part is errors="coerce". Values that can't be converted into numbers become missing values rather than stopping the entire pipeline.

We're also careful not to invent information we don't have. A missing city becomes Unknown, while a missing age remains missing. Cleaning data doesn't mean guessing what the data should have been.

Finally, the program removes the exact duplicate customer record and resets the row numbers.

One Dataset, Two Audiences

After cleaning, the useful customer records look like this:

Alice    Johnson     Dallas     34
Bob      Smith       Dallas     29
Charlie  Brown       Austin     missing
Diana    Garcia      Houston    41
Evan     Williams    Austin     27
Frank    Miller      Unknown    38
Grace    Davis       Dallas     missing

But we're saving them twice.

customers_clean.csv is useful for someone who wants to open the information in Excel and work with familiar rows and columns.

customers_clean.json is aimed at software. JSON gives another Python program, web application, API, or other system a structured representation of the same cleaned customer records.

The important point isn't the file format.

It's the pipeline.

We started with messy customer information containing inconsistent text, missing values, and a duplicate record. Python and Pandas converted it into clean, predictable data. Then we produced two outputs for two different consumers.

dirty customer CSV → Python/Pandas → clean data → CSV for people + JSON for software

That's a small but complete data pipeline.

And that clean customer data is now ready for whatever comes next.

Search This Blog

Tech-Reader.blog