Cleaning crawled data with pandas: normalization, deduplication and data types

A crawler that finishes without errors does not mean the data is usable, and pandas is the first tool you reach for to fix it. The same product can show up three times under three different URLs; the price can read 19.990.000₫ in one row, 19.990.000 VND in another and Contact us in a third; the update date can be 16/07/2022 or 2022-07-16; and names can carry stray whitespace or decomposed accented characters that look identical to the eye. This article uses pandas 1.4.3 on Python 3.10 to turn a dirty crawl CSV into a clean table: normalize text, cast to the right types, deduplicate on a sensible key and cut memory with the category type. Every snippet was actually run, and the output is pasted verbatim.

This article continues the data collection part of the series: Crawling data with Python: requests, BeautifulSoup and robots.txt, A polite and compliant crawler: rate limits, caching, ETags and sitemaps and Scrapy from A to Z: spiders, item pipelines and middleware, which produce the kind of raw file that pandas cleans up here.

Why crawled data is always dirty before it reaches pandas

Web pages are written for human eyes, not for analysis code. Every store formats prices, dates and stock levels its own way, and even within one store, old and new page templates coexist. These problems repeat in almost every e-commerce crawl project:

The right workflow follows a fixed order: read everything as raw strings, normalize each column, cast types, build a key, and only then deduplicate. Deduplicating data that has not been normalized always misses rows.

Set up the pandas environment and sample data

Create a dedicated venv for this article and pin the versions so the results are reproducible. The approach is covered in Python environments for data and AI projects: venv, Poetry and version management.

python3.10 -m venv .venv
.venv/bin/pip install pandas==1.4.3 numpy==1.23.1

The crawl_raw.csv file used to try out pandas simulates a crawl of a hypothetical electronics store (domain shop.example, not a real site): 9 rows containing the problems from the table above, including one name in NFD form. Create this file (header line included) to rerun every example. The product name on the seventh data row (Müller 20W Charger) is stored in NFD form, so if you retype it by hand, copy it exactly. Column names are short Latin-alphabet abbreviations: ten is the name, gia the price, ton_kho the stock, ngay_cap_nhat the update date, danh_muc the category and danh_gia the rating.

url,ten,gia,ton_kho,ngay_cap_nhat,danh_muc,danh_gia
https://shop.example/p/ip-13,  iPhone 13 128GB Smartphone ,"19.990.000₫",In stock 25,16/07/2022,Phones,4.5
https://shop.example/p/ip-13?utm_source=fb,iPhone 13 128GB Smartphone,"19.990.000 VND",25,2022-07-16,phones,4.5
https://shop.example/p/ss-a53,Samsung  Galaxy A53,"8.490.000VND",Out of stock,15/07/2022,Phones,4.2
https://shop.example/p/lg-ult,Laptop LG Gram 14,"28.900.000 ₫",3,14/07/2022,Laptop,
https://shop.example/p/lg-ult/,LAPTOP LG GRAM 14,"28,900,000",3,14/07/2022,LAPTOP,N/A
https://shop.example/p/tai-nghe-1,Bluetooth Headphones,Contact us,120,13/07/2022,Accessories,3.9
https://shop.example/p/sac-20w,Müller 20W Charger,"350.000₫",In stock 0,,Accessories,4.0
https://shop.example/p/ip-13,iPhone 13 128GB Smartphone,"19.990.000₫",25,16/07/2022,Phones,4.5
https://shop.example/p/op-lung,Silicone Phone Case,"90.000VND",1.200,12/07/2022,Accessories,"4,1"

The first step is to read every column with dtype=str and keep_default_na=False. If you let pandas read_csv infer types on its own, the text N/A quietly becomes NaN. Reading raw keeps every type-conversion decision in your own code, where you can see and test it.

import re
from urllib.parse import urlsplit

import pandas as pd

pd.set_option("display.width", 140)
pd.set_option("display.max_columns", 20)

raw = pd.read_csv("crawl_raw.csv", dtype=str, keep_default_na=False)
print("=== 1. Raw data ===")
print(raw.drop(columns="url"))
print(raw.dtypes.to_string())

df = raw.copy()

The raw table (the url column is dropped for brevity):

=== 1. Raw data ===
                             ten             gia       ton_kho  ngay_cap_nhat     danh_muc  danh_gia
0    iPhone 13 128GB Smartphone      19.990.000₫   In stock 25     16/07/2022       Phones       4.5
1     iPhone 13 128GB Smartphone  19.990.000 VND            25     2022-07-16       phones       4.5
2            Samsung  Galaxy A53    8.490.000VND  Out of stock     15/07/2022       Phones       4.2
3              Laptop LG Gram 14    28.900.000 ₫             3     14/07/2022       Laptop          
4              LAPTOP LG GRAM 14      28,900,000             3     14/07/2022       LAPTOP       N/A
5           Bluetooth Headphones      Contact us           120     13/07/2022  Accessories       3.9
6       Müller 20W Fast Charger        350.000₫    In stock 0                 Accessories       4.0
7     iPhone 13 128GB Smartphone     19.990.000₫            25     16/07/2022       Phones       4.5
8            Silicone Phone Case       90.000VND         1.200     12/07/2022  Accessories       4,1
url              object
ten              object
gia              object
ton_kho          object
ngay_cap_nhat    object
danh_muc         object
danh_gia         object

In pandas, every column is object. From here we transform each column with pandas functions.

Normalize text: Unicode NFC, whitespace and letter case

Accented text can be encoded in two ways: the precomposed form (NFC), where ü is a single character, and the decomposed form (NFD), where u plus a combining diaeresis is two characters. Vietnamese, German and French names hit this all the time. The Unicode standard calls the two forms canonically equivalent: they render identically, but string comparison treats them as different. Unicode Standard Annex #15 describes these normalization forms and notes that the W3C recommends NFC for web content. Data crawled from many sources often mixes both.

pandas has Series.str.normalize(form) to normalize a whole column in one call; under the hood it calls the native unicodedata.normalize function from the Python standard library, the one you use for a single string. We wrap NFC and whitespace collapsing in one vectorized function and apply it to the text columns:

# --- 2. Normalize text ---
def norm_text(s: pd.Series) -> pd.Series:
    return (
        s.str.normalize("NFC")                         # bring accents to a single form
         .str.replace(r"\s+", " ", regex=True)         # collapse whitespace
         .str.strip()
    )

nfd_rows = (raw["ten"] != raw["ten"].str.normalize("NFC")).sum()
print("\nNames not in NFC form:", int(nfd_rows))
df["ten"] = norm_text(df["ten"])
df["danh_muc"] = norm_text(df["danh_muc"]).str.lower().str.capitalize()

Running this step on the sample, the comparison finds exactly one name in decomposed form:

Names not in NFC form: 1

We normalize letter case only in the category column, because categories are classification labels. For product names we keep the original capitalization for display and use casefold() only when building a comparison key. The rule: columns for display keep their nice form, columns for matching are converted to a canonical form.

Cast numbers with Int64 and pd.NA

Price is the main problem. Three formats appear in the sample: 19.990.000₫, 28,900,000 and Contact us. A simple and robust approach is to strip every non-digit character and convert to an integer; a cell with no digits left (like Contact us) becomes a missing value. The Vietnamese dong has no decimal part, so this works here; for currencies with fractional units you must not apply \D mechanically.

# --- 3. Price: string -> nullable integer (Int64) ---
def parse_price(s: str):
    digits = re.sub(r"\D", "", s)              # drop ., ₫, VND, spaces
    return int(digits) if digits else pd.NA

df["gia"] = pd.array([parse_price(x) for x in df["gia"]], dtype="Int64")

The notable part is the Int64 type (capital I), an integer type that allows missing values. According to the pandas nullable integer documentation, NaN is a float, so an integer column with a missing cell usually gets pushed to float64; the Int64 type uses pd.NA as its missing marker and stays an integer. Float64 represents integers exactly only up to 2⁵³, as the example in the common mistakes section below shows.

The stock column has three text forms (In stock 25, Out of stock and 1.200 with a dot as thousands separator), and the rating column has a decimal comma (4,1), empty cells and N/A. The pandas function pd.to_numeric(..., errors="coerce") turns anything it cannot parse into a missing value instead of crashing the whole pipeline:

# --- 4. Stock, rating ---
df["ton_kho"] = (
    df["ton_kho"].str.replace("In stock", "", regex=False).str.replace(".", "", regex=False)
    .str.strip().replace({"Out of stock": "0"})
)
df["ton_kho"] = pd.to_numeric(df["ton_kho"], errors="coerce").astype("Int64")
df["danh_gia"] = pd.to_numeric(
    df["danh_gia"].str.replace(",", ".", regex=False), errors="coerce"
)

Be careful with errors="coerce": it hides errors, so after converting, count the newly created missing cells.

Normalize dates in multiple formats

The date column holds two formats: 16/07/2022 and 2022-07-16. Do not call pd.to_datetime with no arguments on the whole column. For an ambiguous string like 03/04/2022, pandas defaults to month first, day second (US style). The safe way is to declare an explicit format for each known pattern, set errors="coerce" so non-matching rows become NaT, and then merge the results with fillna:

# --- 5. Dates: two formats in one column ---
d1 = pd.to_datetime(df["ngay_cap_nhat"], format="%d/%m/%Y", errors="coerce")
d2 = pd.to_datetime(df["ngay_cap_nhat"], format="%Y-%m-%d", errors="coerce")
df["ngay_cap_nhat"] = d1.fillna(d2)

The Müller 20W Charger row has an empty date cell, so it matches neither format and comes out as NaT; that is genuinely missing data, not a parsing error. The check result is printed at the end of the article: missing date: 1 and the row NaT row: ['Müller 20W Charger'].

Deduplicate on a normalized key

The DataFrame.drop_duplicates method takes two important parameters: subset (which columns to compare) and keep ('first', 'last' or False). Calling pandas on all columns is nearly useless for crawl data, because two records of the same product rarely match cell for cell: the crawl time, a URL with tracking parameters or the stock status all differ. We need an identity key that we compute ourselves.

For products, the natural key is the normalized URL: lowercase the domain, drop the query string (?utm_source=fb) and the trailing /. The standard library function urlsplit splits URLs correctly and is safer than cutting strings by hand. Note that dropping the whole query is only valid when the parameters do not determine the page content; for paginated pages like ?page=2 you must keep those parameters.

When there are several duplicate records, which one to keep is a business decision. Here we keep the record with the latest ngay_cap_nhat: sort in descending order by date, drop_duplicates(..., keep="first"), then restore the original order with sort_index. The kind="stable" parameter guarantees that when two records share a date, the one that appears first is kept, so results are reproducible between runs.

# --- 6. Deduplication key: canonical URL ---
def canon_url(u: str) -> str:
    p = urlsplit(u)
    return f"{p.scheme}://{p.netloc.lower()}{p.path.rstrip('/')}"   # drop query and trailing /

df["url_chuan"] = df["url"].map(canon_url)
print("\n=== 2. Duplicate records by canonical URL ===")
print(df.duplicated("url_chuan", keep=False).sum(), "rows in duplicate groups")

before = len(df)
df = (
    df.sort_values("ngay_cap_nhat", ascending=False, kind="stable")
      .drop_duplicates("url_chuan", keep="first")
      .sort_index()
      .reset_index(drop=True)
)
print(f"{before} -> {len(df)} rows after deduplication")
print("Still duplicated by name (case-insensitive):", int(df["ten"].str.casefold().duplicated().sum()))

Deduplication result:

=== 2. Duplicate records by canonical URL ===
5 rows in duplicate groups
9 -> 6 rows after deduplication
Still duplicated by name (case-insensitive): 0

Nine rows become six: the five rows in duplicate groups (three copies of the iPhone 13, two of the Laptop LG Gram 14) collapse into two. The last line cross-checks with a second key, the name: zero means the URL key did not miss any case.

The category type and measuring memory

A column with few distinct values repeated many times, such as category or brand, should be converted to the pandas categorical type. Instead of storing every string, pandas stores a table of unique values plus an array of small integer codes. The pandas pitfall to remember is that you must normalize letter case before converting, otherwise Phones and phones become two separate groups.

# --- 7. Final data types ---
df["danh_muc"] = df["danh_muc"].astype("category")
df = df.drop(columns="url").rename(columns={"url_chuan": "url"})
print("\n=== 3. After cleaning ===")
print(df)
print(df.dtypes.to_string())
print("\nMissing price:", int(df["gia"].isna().sum()), "| missing date:", int(df["ngay_cap_nhat"].isna().sum()))
print("NaT row:", df.loc[df["ngay_cap_nhat"].isna(), "ten"].tolist())
print("\nCategories:", df["danh_muc"].cat.categories.tolist())
print("\nMean price by category:")
print(df.groupby("danh_muc", observed=True)["gia"].mean().round(0).to_string())

# --- 8. Memory: object vs category/Int64 ---
big_raw = pd.concat([raw] * 20000, ignore_index=True)
big = pd.DataFrame({
    "danh_muc": norm_text(big_raw["danh_muc"]).str.lower(),
    "gia": pd.array([parse_price(x) for x in big_raw["gia"]], dtype="Int64"),
})
m_obj = big.memory_usage(deep=True).sum()
big["danh_muc"] = big["danh_muc"].astype("category")
m_cat = big.memory_usage(deep=True).sum()
print(f"\nMemory for {len(big):,} rows: object {m_obj/1e6:.1f} MB -> category {m_cat/1e6:.1f} MB")

The full output of the clean table section:

=== 3. After cleaning ===
                          ten       gia  ton_kho  ngay_cap_nhat     danh_muc  danh_gia                                url
0  iPhone 13 128GB Smartphone  19990000       25     2022-07-16       Phones       4.5       https://shop.example/p/ip-13
1          Samsung Galaxy A53   8490000        0     2022-07-15       Phones       4.2      https://shop.example/p/ss-a53
2           Laptop LG Gram 14  28900000        3     2022-07-14       Laptop       NaN      https://shop.example/p/lg-ult
3        Bluetooth Headphones      <NA>      120     2022-07-13  Accessories       3.9  https://shop.example/p/tai-nghe-1
4    Müller 20W Fast Charger    350000        0            NaT  Accessories       4.0     https://shop.example/p/sac-20w
5         Silicone Phone Case     90000     1200     2022-07-12  Accessories       4.1     https://shop.example/p/op-lung
ten                      object
gia                       Int64
ton_kho                   Int64
ngay_cap_nhat    datetime64[ns]
danh_muc               category
danh_gia                float64
url                      object

Missing price: 1 | missing date: 1
NaT row: ['Müller 20W Charger']

Categories: ['Accessories', 'Laptop', 'Phones']

Mean price by category:
danh_muc
Accessories      220000.0
Laptop         28900000.0
Phones         14240000.0

Memory for 180,000 rows: object 13.3 MB -> category 1.8 MB

The gia column is Int64, ngay_cap_nhat is datetime64[ns] and danh_muc is category. The Bluetooth Headphones product has a price of <NA> and the Müller 20W Charger has a date of NaT; both are genuinely missing data, not parsing errors. Measured on an Apple M4 (16 GB), repeating the sample 20,000 times into 180,000 rows, memory dropped from 13.3 MB to 1.8 MB when the category column went from object to category. The figure depends on the number of distinct values: a column that is nearly unique on every row, like product name, gains nothing.

When not to clean data with pandas

• Data larger than RAM: pandas loads the whole table into memory, so it is a poor fit for very large sets. For tens of millions of rows, read in chunksize pieces, or clean directly in the database and in the Scrapy pipeline.
• Per-record cleaning during the crawl: pandas works in batches, so if each item only needs type casting and required-field checks, an item pipeline or Pydantic is simpler, and bad records are blocked early.
• Traceability needs: keep the raw copy immutable and write the cleaned data to a new file. Do not overwrite the original, so you can rerun from the start when the rules change.

Common mistakes when cleaning data with pandas

The six pitfalls below are the reason the clean table above was built in exactly that order. Each is a standalone code block with real output (Python 3.10.22, pandas 1.4.3); you only need import pandas as pd and import unicodedata at the top of the file.

Pitfall 1: deduplicating before normalizing

raw = pd.DataFrame({"ten": ["Laptop LG Gram 14", "LAPTOP LG GRAM  14", " laptop lg gram 14"]})
print("drop_duplicates on raw data keeps:", len(raw.drop_duplicates()), "rows")
key = raw["ten"].str.casefold().str.split().str.join(" ")
print("After normalizing the key:", len(raw.drop_duplicates().loc[~key.duplicated()]), "row(s)")

drop_duplicates on raw data keeps: 3 rows
After normalizing the key: 1 row(s)

The three names differ only in letter case and whitespace, so pandas drop_duplicates treats them as three separate records; after normalizing the key only one is left.

Pitfall 2: NFC is not NFD

a = "Müller"                                # NFC
b = unicodedata.normalize("NFD", a)         # NFD
print(a == b, len(a), len(b), a == unicodedata.normalize("NFC", b))

False 6 7 True

"Müller" in precomposed form has 6 characters and in decomposed form has 7, so == returns False; converted to NFC, they are equal.

Pitfall 3: integer column turned into float

print(pd.Series([19990000, None]).dtype, pd.Series([19990000, None], dtype="Int64").dtype)
big = 9007199254740993                      # 2**53 + 1
print(int(pd.Series([big, None])[0]), int(pd.Series([big, None], dtype="Int64")[0]))

float64 Int64
9007199254740992 9007199254740993

One missing cell pushes the whole column to float64, and pandas rounds the integer 9007199254740993 (2⁵³ + 1) down to ...992, while Int64 keeps it intact. Product codes or phone numbers stored as numbers are very prone to this bug.

Pitfall 4: ambiguous dates

s = pd.Series(["03/04/2022"])
print(pd.to_datetime(s).iloc[0].date(), pd.to_datetime(s, dayfirst=True).iloc[0].date())
print(pd.to_datetime(pd.Series(["16/07/2022"]), errors="coerce").iloc[0])
print(pd.to_datetime(pd.Series(["16/07/2022"]), format="%d/%m/%Y").iloc[0].date())

UserWarning: Parsing '16/07/2022' in DD/MM/YYYY format. Provide format or specify infer_datetime_format=True for consistent parsing.
2022-03-04 2022-04-03
2022-07-16 00:00:00
2022-07-16

By default pandas reads 03/04/2022 as March 4, and as April 3 with dayfirst=True. The string 16/07/2022 comes out right only because pandas guesses and emits a UserWarning as shown (the warning line goes to stderr, before the result lines); do not rely on that guess, and when the format is known, always declare format.

Pitfall 5: to_numeric either raises or silently returns NaN

try:
    pd.to_numeric(pd.Series(["25", "Contact us"]))
except ValueError as e:
    print("ValueError:", e)
print(pd.to_numeric(pd.Series(["25", "Contact us"]), errors="coerce").tolist())

ValueError: Unable to parse string "Contact us" at position 1
[25.0, nan]

Without errors, Contact us raises a ValueError immediately; with errors="coerce" it becomes NaN, but you have to count the newly missing cells yourself.

Pitfall 6: converting to category too early

c = pd.Series(["Phones", "phones", "PHONES"]).astype("category")
print(len(c.cat.categories), "different groups because not normalized")

3 different groups because not normalized

Three spellings of one category become three groups; normalize letter case before calling astype("category").

Checklist for cleaning crawled data

• Read raw with pandas using dtype=str and keep_default_na=False, and keep the original immutable.
• Normalize text: NFC, collapse whitespace, normalize letter case for label columns.
• In pandas, use Int64 for integers that may be missing; to_numeric(errors="coerce") plus a count of newly missing cells.
• Dates: declare an explicit format for each known pattern.
• Deduplicate on a key you compute (canonical URL) and choose which record to keep by a clear rule.
• Convert low-cardinality label columns to the pandas category type after normalizing them.
• Print dtypes, row counts before and after and the number of missing cells as a minimum check; turn them into tests when the pipeline runs on a schedule.

In short, cleaning data with pandas is not a string of loose commands but an ordered process in which each step has a check attached. A reminder as well: crawl only where it is allowed and avoid storing personal data you do not need, as stressed in the article on the compliant crawler.

Next article

The pandas table is now clean and has the right numeric types; the next step is computing over millions of values without writing Python loops. Continue with NumPy for machine learning: vectorization and broadcasting.