Data Cleaning in Python: 7 Steps for Messy Data
You export sales data from your company system, open it in Python, and nothing works. The amounts refuse to add up. Two customers who are clearly the same person appear as different people. Half your dates are text. If this sounds familiar, you are not doing anything wrong. You are just meeting real data for the first time.
Data cleaning in Python is the work of turning that mess into something you can actually analyse. It is the least glamorous part of the job and the part you will spend the most time on. It is also the skill that separates people who finish projects from people who abandon them.
In this article you will clean one messy Nigerian sales file from start to finish using pandas. Seven steps, in the order a working analyst actually does them. Every code block here was run before publishing, so you can copy it and it will work.
The messy dataset we will fix
Meet ShopNG, a small retail business selling across Lagos, Kano, Enugu and Ibadan. Their point of sale system exports something like this.
import pandas as pd
import numpy as np
raw = pd.DataFrame({
' Customer Name ': ['Chidi Okeke','Aisha Bello','emeka nwosu','Chidi Okeke',
'Ngozi Eze',None,'Tunde Balogun','aisha bello'],
'City': ['Lagos','kano','Enugu ','Lagos','LAGOS','Kano','ibadan','kano'],
'Amount': ['₦12,500','₦8,000','₦15,750','₦12,500','₦3,200','₦20,000','-500','₦9,900'],
'Quantity': [2, 1, 3, 2, 1, 4, 1, 2],
'Order Date': ['2025-01-05','2025/01/06','06-01-2025','2025-01-05',
'2025-01-08',None,'2025-01-09','2025-01-10'],
})
print(raw.shape)
print(raw)
Eight rows, five columns, and at least six separate problems hiding inside. Count them before you scroll. Column names have stray spaces. One customer name is missing. Amounts are text with Naira symbols and commas. One amount is negative. City names use four different capitalisations. Dates come in three different formats. One row is an exact duplicate.
This is not an exaggerated example. This is what a real export looks like.
1. Look at the data before you touch it
The most common beginner mistake is cleaning immediately. Do not. Spend two minutes understanding what you have, because every decision after this depends on it.
df = raw.copy()
print(df.shape) # how many rows and columns
print(df.dtypes) # what Python thinks each column is
print(df.isnull().sum()) # how many values are missing per column
print(df.describe()) # quick statistics on numeric columns
Notice we used .copy(). That keeps the original untouched so you can always start over. When you look at dtypes you will see that Amount is listed as object, which is how pandas says text. That single line tells you why your sums were failing.
2. Clean the column names
Column names with spaces and capital letters make every later line of code painful. Fix them once, immediately.
df.columns = df.columns.str.strip().str.lower().str.replace(' ', '_')
print(list(df.columns))
# ['customer_name', 'city', 'amount', 'quantity', 'order_date']
Three operations chained together. .strip() removes the spaces at the edges, .lower() makes everything lowercase, and .replace() swaps spaces for underscores. Now you can type df.customer_name instead of df[' Customer Name '].
3. Remove duplicate rows
Duplicates inflate your totals, and inflated totals get reported to someone who will eventually notice.
before = len(df)
df = df.drop_duplicates()
print(f"Dropped {before - len(df)} duplicate rows, {len(df)} remaining")
# Dropped 1 duplicate rows, 7 remaining
By default drop_duplicates() only removes rows where every single value matches. If you want to treat two rows as duplicates based on certain columns only, name them explicitly with df.drop_duplicates(subset=['customer_name', 'order_date']). Be careful with that. A customer genuinely can buy twice in one day.
4. Deal with missing values
There is no universal rule for missing data. What you do depends on what the column means, and that is a judgement call, not a formula.
print(df.isnull().sum())
# A sale with no customer name is not useful to us, so remove those rows
df = df.dropna(subset=['customer_name'])
# A missing date can reasonably carry forward from the row above
df['order_date'] = df['order_date'].ffill()
print(f"Rows remaining: {len(df)}")
# Rows remaining: 6
Note the method here. Older tutorials will tell you to write fillna(method='ffill'). That form has been removed from current pandas and will raise a TypeError. Use .ffill() directly. If you have been copying code from old blog posts and wondering why it breaks, this is one of the reasons.
Your three realistic options for missing values are to remove the rows, fill them with something sensible such as a median or a carried forward value, or leave them alone and handle them at analysis time. Removing rows is safest when the missing field is essential, as a customer name is here.
Working through this and want the full path rather than one article? Python for Data Analysts: From Zero to Your First Real Data Project takes you from setup to a finished project you can show an employer, using Nigerian datasets throughout.
5. Fix the data types
This is where most sales data actually breaks. Your amounts are text because of the Naira symbol and the comma. You cannot do arithmetic on text.
df['amount'] = (df['amount']
.str.replace('₦', '', regex=False)
.str.replace(',', '', regex=False)
.astype(float))
print(df['amount'].dtype)
# float64
Strip the symbol, strip the comma, then convert. Order matters. If you call .astype(float) first it will fail, because Python has no idea what to do with the character ₦.
Dates need the same treatment, and this is where you need to be careful.
df['order_date'] = pd.to_datetime(df['order_date'],
format='mixed',
dayfirst=False,
errors='coerce')
A warning that most tutorials skip. Our data has three date formats mixed together, and format='mixed' will guess. Guessing goes wrong silently. Here is what actually happens to the value 06-01-2025, which was meant to be the sixth of January.
# With dayfirst=False
'2025-01-05' -> 2025-01-05 correct
'06-01-2025' -> 2025-06-01 WRONG, read as 1 June
# With dayfirst=True
'2025-01-05' -> 2025-05-01 WRONG, now the ISO dates break
'06-01-2025' -> 2025-01-06 correct
Neither setting is safe. There is no flag that rescues you here. The real fix is to know your source format and parse it explicitly with something like pd.to_datetime(col, format='%d-%m-%Y'), or to fix the export so it produces one consistent format. Whatever you do, always print the result and look at it. Setting errors='coerce' turns anything unparseable into NaT rather than crashing, which means broken dates fail quietly. Check for them with df['order_date'].isnull().sum().
6. Standardise your text
Lagos, LAGOS and "Lagos " are three different cities as far as Python is concerned. If you group by city right now you will get a mess.
df['city'] = df['city'].str.strip().str.title()
df['customer_name'] = df['customer_name'].str.strip().str.title()
print(sorted(df['city'].unique()))
# ['Enugu', 'Ibadan', 'Kano', 'Lagos']
Four cities, as it should be. This step also quietly fixes your customer list, because "aisha bello" and "Aisha Bello" now match. Run df['column'].unique() on every text column before and after. It is the fastest way to spot problems you did not know you had.
7. Validate the values that survived
Clean formatting does not mean correct data. A negative sale amount is perfectly formatted and still impossible.
invalid = df[df['amount'] < 0]
print(f"Found {len(invalid)} rows with negative amounts")
df = df[df['amount'] >= 0]
print(df)
print(df.dtypes)
Here is the final result.
customer_name city amount quantity order_date
Chidi Okeke Lagos 12500.0 2 2025-01-05
Aisha Bello Kano 8000.0 1 2025-01-06
Emeka Nwosu Enugu 15750.0 3 2025-06-01
Ngozi Eze Lagos 3200.0 1 2025-01-08
Aisha Bello Kano 9900.0 2 2025-01-10
Five clean rows from eight messy ones. Amounts are numbers you can sum. Cities group correctly. Names match across rows. Notice that Emeka Nwosu still shows June, because we chose not to guess at his ambiguous date. That is the honest outcome, and flagging it beats inventing a value.
Ask yourself what is impossible in your data. Negative quantities, ages above 120, dates in the future, prices of zero. Write a check for each one.
The seven steps at a glance
| Step | Problem it solves | Key pandas method |
|---|---|---|
| 1. Inspect | You do not know what is wrong yet | .info(), .isnull().sum() |
| 2. Column names | Spaces and capitals make coding painful | .str.strip().str.lower() |
| 3. Duplicates | Totals are inflated | .drop_duplicates() |
| 4. Missing values | Gaps break calculations | .dropna(), .ffill() |
| 5. Data types | Numbers stored as text | .astype(), pd.to_datetime() |
| 6. Text consistency | Same value counted as different | .str.title(), .unique() |
| 7. Validation | Values that are formatted but impossible | Boolean filtering |
Where to go next
Run these seven steps on your own file today. Not a practice dataset, your own. Export something from your workplace, your side business, or any public Nigerian dataset, and work through the list in order.
You will hit problems this article did not cover, and that is the point. Cleaning is a skill you build by meeting new mess, not by reading about it. When you get stuck, the pandas documentation linked below is genuinely readable, and far more reliable than a random Stack Overflow answer from 2018.
If you are still setting up your environment, start with how to install Jupyter Notebook, which is where most of this work happens. If you are working out where this fits in the bigger picture, read your pathway to becoming a data analyst.
Ready to practise properly? Reading cleaning code is not the same as writing it under pressure. 50 Python Exercises for Data Analysts gives you graded problems with worked solutions, so you build the reflexes that make this automatic.
Post a Comment