Your Scraped Data Is Messy
You ran a web scraper. Congratulations! You've got a CSV file full of book data. But when you open it, you see: "£51.77" as text, not a number. Duplicate rows everywhere. Extra spaces in book titles. Dates that look like "Aug 22 2026" but Python can't read them. Sound familiar?
Raw scraped data is like vegetables straight from the garden—dirty, uneven, and not quite ready to eat. You need to clean it before you can do anything useful with it.
In this guide, I'll show you how to clean scraped data with pandas. We'll take a messy CSV from books.toscrape.com and turn it into a clean, analysis-ready file. No fancy statistics. Just practical steps that fix the most common problems.
What Is Data Cleaning Anyway
Think of data cleaning like washing and chopping vegetables before cooking. You wouldn't throw a dirty potato into a stew. Same with data—you want it consistent, complete, and in the right format before you analyze it.
Data cleaning means:
- Removing duplicate rows (you don't want the same book counted twice)
- Fixing text formatting (stripping extra spaces, standardizing capitalization)
- Converting text to proper data types (turning "£51.77" into a number you can sum)
- Handling missing values (deciding what to do with empty cells)
- Filtering out junk (removing rows that don't make sense)
pandas is the tool for this job. It's a Python library built specifically for working with tabular data—rows and columns, just like a spreadsheet.
I'm running Python 3.8.10 on Ubuntu 20.04 with pandas 2.0.3. The same code works on Windows and macOS.
Step 1: Load Your Scraped CSV
First, you need to get your data into pandas. This assumes you already have a CSV file from a web scraping project. If you haven't scraped anything yet, check out my guide on how to scrape website data with Python first.
Create a file called books_raw.csv with some sample data. Here's what ours looks like:
title,price,rating,availability
"A Light in the Attic","£51.77",Three,"In stock"
"Tipping the Velvet","£53.74",One,"In stock"
" A Light in the Attic ","£51.77",Three,"In stock"
"Soumission","£50.10",One,"In stock"
"Sharp Objects","£47.82",Four,"In stock"
"Sharp Objects","£47.82",Four,"In stock"
,,,
Notice the problems: duplicate rows, extra spaces in some titles, and prices that are text strings with £ symbols.
Now load it with pandas:
import pandas as pd
# Read the CSV file
df = pd.read_csv("books_raw.csv")
# Take a first look
print(df.head())
print("\n--- DataFrame info ---")
print(df.info())
df.head() shows the first five rows. df.info() tells you about the columns—their names, how many non-null entries they have, and their data types. You'll see that price is object (text), not a number.
Step 2: Drop Duplicate Rows
Duplicate rows are a common problem. Maybe your scraper ran twice, or the website showed the same product in multiple categories.
pandas has a simple method: drop_duplicates().
# Remove all duplicate rows
df = df.drop_duplicates()
print(f"Rows after dropping duplicates: {len(df)}")
Sometimes you only want to consider specific columns when checking for duplicates. For example, two books might have the same price but different titles. You usually care about duplicate titles:
# Remove duplicates based only on the 'title' column
df = df.drop_duplicates(subset=['title'])
print(f"Rows after dropping title duplicates: {len(df)}")
In our sample data, the duplicate "Sharp Objects" row is gone. But the extra " A Light in the Attic " with leading spaces is still there — to pandas, that's a different string than "A Light in the Attic", so a duplicate check can't see it yet. We'll fix that in Step 3 with str.strip(), then re-run the dedupe. That's why the full pipeline below strips text before dropping duplicates.
Step 3: Strip Whitespace and Fix Text
Text data from websites often has leading or trailing spaces. That " A Light in the Attic " entry? It's a different string than "A Light in the Attic" because of the spaces, even though they're the same book.
Clean it with str.strip():
# Remove leading/trailing spaces from title column
df['title'] = df['title'].str.strip()
# Check the results
print(df['title'].unique())
You might also want to standardize text. For example, if you had a "status" column with values like "in stock", "In stock", "IN STOCK", you could make them all lowercase:
# Convert to lowercase (if you had a status column)
# df['status'] = df['status'].str.lower()
Or replace unwanted characters:
# Remove extra punctuation if needed
# df['title'] = df['title'].str.replace('"', '')
Step 4: Fix Data Types
Here's the big one. That price column looks like a number, but pandas sees it as text because of the £ symbol. You can't calculate an average price from text.
Use pd.to_numeric() to convert text to numbers. The errors='coerce' parameter turns anything that can't be converted into NaN (Not a Number), which we can handle separately.
# Convert price to numbers (remove £ and convert)
df['price'] = pd.to_numeric(df['price'].str.replace('£', ''), errors='coerce')
# Now check the data types
print(df.dtypes)
The price column is now float64—a numeric type. You can sum it, average it, sort it properly.
Dates are another common problem. Suppose you had a column with dates like "2026-08-22". Use pd.to_datetime():
# If you had a date column
# df['scrape_date'] = pd.to_datetime(df['scrape_date'])
Handling Missing Values
After conversion, some rows might have NaN values. That's where fillna() comes in:
# Replace NaN with a default value (0 for prices, "Unknown" for text)
# df['price'] = df['price'].fillna(0)
# df['rating'] = df['rating'].fillna("Unknown")
For our book data, if a price couldn't be converted, we might want to drop that row entirely:
# Drop rows where price is missing
df = df.dropna(subset=['price'])
Step 5: Filter Out Junk Rows
Sometimes your data includes rows that just don't make sense. Maybe a book has a price of zero, or an empty title.
Use boolean filtering to keep only the rows you want:
# Keep only books with positive prices
df = df[df['price'] > 0]
# Keep only rows where title is not empty
df = df[df['title'].notna()]
# Keep only rows where title contains actual text
df = df[df['title'].str.strip() != '']
You can combine multiple conditions with & (and) or | (or). Wrap each condition in parentheses:
# Keep books with price between £10 and £100
df = df[(df['price'] > 10) & (df['price'] < 100)]
Step 6: Save the Clean File
You've done the work. Now save the clean data to a new CSV file.
Important: Use index=False when saving. Without it, pandas writes row numbers as a separate column in your CSV. That's usually not what you want.
# Save to a new file - don't include the index column
df.to_csv("books_clean.csv", index=False)
print("Clean file saved: books_clean.csv")
Now open books_clean.csv. The duplicates are gone. The prices are numbers. The spaces are cleaned up. It's ready for analysis, reporting, or loading into Excel.
A Full Cleaning Pipeline
Here's the complete script that ties everything together. This is the exact pipeline you can run every time you scrape new data.
import pandas as pd
# Step 1: Load
df = pd.read_csv("books_raw.csv")
print(f"Loaded {len(df)} rows")
# Step 2: Strip whitespace FIRST — otherwise " A Light in the Attic "
# and "A Light in the Attic" look like different titles to pandas
df['title'] = df['title'].str.strip()
# Step 3: Drop duplicates (based on title)
df = df.drop_duplicates(subset=['title'])
print(f"After dedupe: {len(df)} rows")
# Step 4: Convert price to numeric
df['price'] = pd.to_numeric(df['price'].str.replace('£', ''), errors='coerce')
# Step 5: Handle missing values
# Drop rows with missing price or title
df = df.dropna(subset=['price', 'title'])
print(f"After dropping nulls: {len(df)} rows")
# Step 6: Filter out junk (price must be > 0)
df = df[df['price'] > 0]
# Step 7: Save
df.to_csv("books_clean.csv", index=False)
print("Cleaned file saved: books_clean.csv")
FAQ
What does data cleaning mean in pandas?
Data cleaning in pandas means using pandas functions to fix common problems in tabular data. This includes removing duplicate rows with drop_duplicates(), stripping extra spaces with str.strip(), converting text to numbers with pd.to_numeric(), handling missing values with fillna() or dropna(), and filtering out unwanted rows. It's the process of transforming raw, messy data into a consistent format that you can analyze or report on. In pandas, most cleaning operations are one-line methods that return a modified DataFrame.
How do I remove duplicates from a CSV in pandas?
Use df.drop_duplicates(). By default, this removes rows where every column is identical. If you want to remove duplicates based on specific columns, pass the subset parameter: df.drop_duplicates(subset=['title', 'author']) keeps only the first occurrence of each unique combination. Assign the result back to your DataFrame: df = df.drop_duplicates(). If you have a large dataset, you can also use keep='last' to keep the last occurrence instead of the first.
How do I convert string numbers to numbers in pandas?
Use pd.to_numeric(). If your strings have symbols like £ or $, strip them first with str.replace(). Example: df['price'] = pd.to_numeric(df['price'].str.replace('£', ''), errors='coerce'). This removes the pound sign and converts to float. The errors='coerce' parameter turns any value that can't be converted into NaN instead of throwing an error, which keeps your script running.
What does errors='coerce' do in pandas?
The errors='coerce' parameter tells pandas to turn problematic values into NaN instead of stopping your script with an error. For pd.to_numeric(), if a value like "N/A" or "unknown" appears, pandas converts it to NaN. This is useful because you can then handle those missing values with fillna() or dropna(). Without errors='coerce', pandas raises a ValueError and your script crashes. The other options are 'raise' (default, raises error) and 'ignore' (returns unmodified input). For real-world data, 'coerce' is almost always what you want.
Do I lose my original file if I make a mistake?
No. pandas operations like df.drop_duplicates() return a new DataFrame. They don't modify your original CSV file unless you explicitly save it. If you do df = df.drop_duplicates(), you're just changing the DataFrame in memory. The original books_raw.csv remains untouched until you run df.to_csv("books_raw.csv") and overwrite it. A safe practice is to always save cleaned data to a new file with a different name, like books_clean.csv, until you're confident your script works perfectly.
Next Steps
You've turned messy scraped data into a clean dataset. Here's what to do next:
- Automate the whole scrape-and-clean flow – Combine your scraper with this cleaning pipeline into one script. For more on scraping, revisit my scrape website data with Python guide.
- Export the clean data to Excel – Need an .xlsx file instead of CSV? Check out save scraped data to Excel to learn how to write formatted Excel spreadsheets from pandas.
- Scrape multiple pages – Your scraper might pull data from dozens of pages. Clean all that data at once with the pipeline above. See scrape multiple pages with Python for tips.
- Learn pandas from the ground up – If this felt fast, start with the foundational clean CSV files with pandas guide.
Open books_clean.csv in a spreadsheet or text editor. Look at those clean columns. You've taken raw, scrappy data and made it usable. That's the first step toward meaningful analysis or automated reporting. Keep this pipeline handy—you'll use it on almost every scraping project.
All code in this article was tested and runs successfully on Python 3.8.10 (Ubuntu 20.04) with pandas 2.0.3 — the full pipeline was verified against the sample CSV: strip-first dedupe removes the whitespace duplicate (dedupe-before-strip misses it), price conversion to numeric, null dropping, and clean CSV output all confirmed — verified August 2026.