Data Analysis in Python
A DataCamp track teaching pandas, NumPy and data cleaning techniques for analysts moving from spreadsheets or SQL into Python-based.
Data cleaning is the process of finding and fixing problems in a dataset (duplicates, missing values, inconsistent labels, wrong data types, impossible values and broken links between tables) before you analyze it. It usually takes longer than the analysis itself, and skipping it is the most common reason a correct-looking report gives wrong numbers.
Course datasets are usually clean. Real ones rarely are. This checklist goes through the problems you'll actually meet, in the order that's safest to fix them, with short pandas examples. The same logic applies in Excel, SQL or Power Query.
The running example is an e-commerce orders export with columns order_id, customer_id, order_date, city, product, quantity and amount.
Never clean the original file in place. Load it, work on a copy, and save the cleaned version separately.
import pandas as pd
raw = pd.read_csv("orders_raw.csv")
df = raw.copy()
If something goes wrong three steps later, you can always start again from the raw data. This matters even more in team settings, where someone will eventually ask what the original data said.
Before changing anything, answer these questions: what does one row represent (one order? one order line?), how many rows and columns are there, and what does each column mean?
df.shape
df.info()
df.head(10)
df.describe(include="all")
You can't judge whether a row is a "duplicate" or a value is "wrong" until you know the row's grain, meaning exactly what one row represents.
Consistent, code-friendly names prevent a whole class of small errors.
df.columns = (df.columns.str.strip()
.str.lower()
.str.replace(" ", "_"))
Order Date (with a trailing space) becomes order_date.
Numbers stored as text won't sum, and dates stored as text won't sort correctly. df.info() from Step 2 shows the problem columns.
df["amount"] = pd.to_numeric(
df["amount"].astype(str).str.replace(",", ""),
errors="coerce")
df["order_date"] = pd.to_datetime(df["order_date"], errors="coerce", dayfirst=True)
errors="coerce" turns unparseable values into missing values instead of crashing. Count how many values were coerced, because a sudden jump in missing values means the format assumption was wrong. dayfirst=True matters for data from countries (including Vietnam) that write dates as day/month/year. Otherwise 03/04 turns into March 4 instead of April 3.
df.duplicated().sum() # fully identical rows
df.duplicated(subset=["order_id"]).sum() # same order ID appearing twice
Exact duplicate rows are usually safe to drop. Duplicate IDs with different values need investigation first. They may be legitimate (an order with two product lines) or a real error (the same order exported twice with an updated status). Decide on a rule, such as keeping the latest record, and write it down:
df = df.sort_values("order_date").drop_duplicates(subset=["order_id"], keep="last")
First measure, then decide column by column:
df.isna().sum().sort_values(ascending=False)
| Situation | Reasonable approach |
|---|---|
| Critical field missing (order amount, ID) | Drop the row, or fix it at the source |
| Missing means "none" (discount code) | Fill with a clear value such as "none" or 0 |
| Numeric field, few gaps, not critical | Fill with the median (it's robust to outliers) |
| Many gaps in a column | Question whether the column is usable at all |
Don't fill in values automatically. Filling a missing income with the average changes the distribution and can hide a data collection problem. Always record how many values you filled and how.
This is where real data gets messy. A single city column might contain:
Ho Chi Minh City, HCMC, TP.HCM, tp hcm, Saigon, Ho Chi Minh
To a computer, that's six different cities. Look at the distinct values, then map them:
df["city"].value_counts()
city_map = {"hcmc": "Ho Chi Minh City", "tp.hcm": "Ho Chi Minh City",
"tp hcm": "Ho Chi Minh City", "saigon": "Ho Chi Minh City",
"ho chi minh": "Ho Chi Minh City", "ho chi minh city": "Ho Chi Minh City"}
df["city"] = df["city"].str.strip().str.lower().replace(city_map).str.title()
The final .str.title() restores normal capitalization for cities that weren't in the map ("hanoi" becomes "Hanoi").
Then check value_counts() again. Keep the mapping in a file or table so it's reused next month instead of rebuilt from memory.
Trailing spaces, mixed case and invisible characters break joins and group-bys quietly:
df["product"] = df["product"].str.strip().str.replace(r"\s+", " ", regex=True)
"Laptop Stand" and "Laptop Stand " (with a trailing space) group as two products until you do this.
df["order_date"].min(), df["order_date"].max()
df.set_index("order_date").resample("W")["order_id"].count()
Separate impossible values (errors) from extreme values (possibly real):
df[(df["quantity"] <= 0) | (df["amount"] < 0)] # impossible for a normal sale
df["amount"].describe(percentiles=[.01, .5, .99])
A negative amount might be a refund that belongs in a different analysis. An order worth 50 times the median might be a genuine bulk purchase, or someone typing 5,000,000 instead of 500,000. Investigate before deleting. Removing real extreme values distorts totals just as much as keeping errors does. If extreme values are real, prefer reporting the median over the mean.
If the data will be joined to other tables, check that the links actually work:
customers = pd.read_csv("customers.csv")
orphans = ~df["customer_id"].isin(customers["customer_id"])
orphans.sum()
Orders pointing to customers who don't exist, or products missing from the product list, will vanish in an inner join or show up as blanks in a left join. See SQL Joins Explained for why that matters.
Write down every rule you applied: what you dropped, what you filled, what you mapped, and the counts before and after. Then turn the steps into a reusable function or script, so next month's file is cleaned the same way:
def clean_orders(raw: pd.DataFrame) -> pd.DataFrame:
df = raw.copy()
# steps 3-11
return df
A short cleaning log at the top of your notebook ("removed 214 duplicate order IDs, keeping the latest; mapped 6 city spellings; 38 rows with negative amounts moved to refunds") is also what makes a portfolio project look professional to a recruiter.
| # | Step | Quick check |
|---|---|---|
| 1 | Keep raw data | Working on a copy? |
| 2 | Understand the grain | What is one row? |
| 3 | Standardize column names | Lowercase, no spaces |
| 4 | Fix data types | df.info() looks right? |
| 5 | Duplicates | Exact and by ID |
| 6 | Missing values | Decided per column, documented |
| 7 | Inconsistent categories | value_counts() clean? |
| 8 | Text cleanup | Whitespace and case |
| 9 | Dates | Range, time zones, gaps |
| 10 | Impossible values and outliers | Investigated, not auto-deleted |
| 11 | Keys and relationships | No orphans |
| 12 | Document and automate | Log plus reusable script |
If you're coming from spreadsheets, start with How to Transition from Excel to Python, and keep our Python cheat sheet for data analysts nearby. For structured practice, Data Analysis with Python (DataCamp) covers pandas cleaning hands-on, and the Applied Data Science with Python Specialization (Michigan) goes deeper with messier, realistic datasets. Browse all Data Analysis courses.
How long does data cleaning take? It's commonly said to take the majority of an analyst's time on a new dataset. The exact share varies, but it's often more than the analysis itself, especially the first time you work with a source.
What's the difference between data cleaning and data transformation? Cleaning fixes errors and inconsistencies. Transformation reshapes correct data for analysis (aggregating, pivoting, joining, deriving new columns). In practice the two happen in the same pipeline.
Should I clean data in SQL, Excel or Python? Wherever it will be repeatable. SQL or dbt suits warehouse data, pandas suits files and exploratory work, and Power Query suits Power BI and Excel users. The steps are the same everywhere.
Is it OK to drop rows with missing values? Sometimes. If very few rows are affected and they're missing at random, dropping them is fine. If many rows are affected, or they're missing for a reason (such as one region's system failing), dropping them biases your results.
Good data cleaning is systematic, not heroic. Keep the raw data, understand the grain, fix types, handle duplicates and missing values deliberately, standardize categories, check dates, outliers and keys, then document and automate what you did. Follow the same 12 steps every time and the numbers you report will hold up when someone checks them.
A DataCamp track teaching pandas, NumPy and data cleaning techniques for analysts moving from spreadsheets or SQL into Python-based.
Five University of Michigan courses teaching Python for data analysis, visualization, machine learning, text mining and network analysis.