Đại học Kinh tế Quốc dân – National Economics UniversityMICRO4ASIA
🏆 Arena
Sign inSign up
MICRO4ASIAThe six micro-credentials

Courses are delivered on the Limio learning platform. Limio

← Data Cleaning and Preprocessing with Python

You are viewing a public preview

Sign in to save your progress, do assignments, join discussions and get feedback.

Sign in
Module 2 — Clean the Data

Period 9 · Converting Numbers and Dates

Print / Save as PDF
Lecture video — Period 9: Converting Numbers and Dates

Converting text to numbers and dates, checking the results against business rules, and separating records that need review.

Lesson overview

Now we will convert numeric fields and dates in a small sales table. Our goal is to produce values that we can calculate with and check. A successful conversion is only the first step. For example, a negative quantity can be a valid number but still break the rules of a sales dataset. We will keep the original data, identify the reason for each problem, and separate records that need review. By the end, you should be able to explain why each record is accepted or held for checking.

Study notes

By the end of this lesson you can:

  • Convert text to numbers with pd.to_numeric(..., errors="coerce") and explain what coerce does.
  • Separate a new conversion failure from a value that was already missing.
  • Parse dates with an explicit format and detect impossible dates.
  • Check converted values against business rules (positive whole quantities, dates in 2025).
  • Build issue flags, split records into clean and review tables, and verify the split.

Key idea: Conversion and validation answer different questions. A value can convert successfully and still break a business rule, so convert with explicit rules, find failures, then validate meaning.

The example and the business rules

The lecture uses six simulated transactions whose quantity and date arrived as text:

IDquantitytransaction_dateProblem
T012valid 2025 datenone
T02threevalid 2025 dateword instead of number
T03-131/02/2025negative quantity and impossible date
T04(missing)valid 2025 datequantity missing
T054a real date in 2026outside the reporting year
T061valid 2025 datenone

The rules are explicit assumptions: quantities must be positive whole numbers, and dates must fall in 2025. In another dataset, negative quantities might be returns. Keep transaction_id as text.

Converting numbers

pd.to_numeric(df["quantity"], errors="coerce") converts what it can. three becomes missing; the original missing value stays missing; -1 converts fine, because it is a valid number. Values may appear as floats when the column also contains missing values. The default errors="raise" would stop at the first invalid value; coerce lets us collect all problems and review them together.

Do not call every missing output a conversion error. A conversion failure is a value that was present before and missing after conversion:

failed = df["quantity"].notna() & df["quantity_num"].isna()

This finds T02 only. T04 was already missing and needs a different action, such as requesting the value. (This assumes blank strings and agreed markers were already turned into real missing values, as in Period 7.)

Before converting, check how the source writes numbers. In the course data, unit_price uses a comma as a thousands separator, so str.replace(",", "") is justified there. Elsewhere a comma may be a decimal mark, so never strip punctuation blindly. Also, an identifier with digits is not a measurement: converting 00125 to a number removes its leading zeros.

Converting dates

The agreed format is day/month/year, so use format="%d/%m/%Y". Then 03/04/2025 means 3 April 2025. Do not let the parser guess. With errors="coerce", the impossible 31/02/2025 becomes NaT (the missing datetime marker), and you detect the failure the same way as for numbers. A 2026 date converts successfully, so a separate reporting-period check is still needed. For data from several systems, use known source groups and explicit formats.

Business validation, flags and outputs

Quantity must be present, finite, positive and whole: q.notna() & np.isfinite(q) & (q > 0) & (q % 1 == 0). Dates must lie between 1 January and 31 December 2025, including both ends. A row is valid only when both checks pass. So T03 fails even though -1 converted, and T05 fails even though its date is real.

A useful review table explains what went wrong, with one Boolean column per issue. Apply quantity rules only when a number is present, so a missing quantity is not also called "negative". issues.any(axis=1) marks rows with at least one issue. Across the example there are 5 flags but 4 invalid records, because T03 has two flags; record both numbers in the processing log.

Worked example

import numpy as np
import pandas as pd

raw = pd.DataFrame({
    "transaction_id": ["T01", "T02", "T03", "T04", "T05", "T06"],
    "quantity": ["2", "three", "-1", None, "4", "1"],
    "transaction_date": ["14/01/2025", "03/04/2025", "31/02/2025",
                         "20/05/2025", "15/01/2026", "30/12/2025"],
})
df = raw.copy()
df["quantity_num"] = pd.to_numeric(df["quantity"], errors="coerce")
df["date"] = pd.to_datetime(df["transaction_date"], format="%d/%m/%Y", errors="coerce")

q = df["quantity_num"]
issues = pd.DataFrame({
    "quantity_missing": df["quantity"].isna(),
    "quantity_not_number": df["quantity"].notna() & q.isna(),
    "quantity_not_positive_whole": q.notna() & ~((q > 0) & (q % 1 == 0)),
    "date_invalid": df["transaction_date"].notna() & df["date"].isna(),
    "date_outside_2025": df["date"].notna()
        & ~df["date"].between(pd.Timestamp("2025-01-01"), pd.Timestamp("2025-12-31")),
})
bad = issues.any(axis=1)
clean = df[~bad].copy()
review = df[bad].join(issues[bad])

assert len(clean) + len(review) == len(df)
assert clean["transaction_id"].tolist() == ["T01", "T06"]
clean["quantity_num"] = clean["quantity_num"].astype("Int64")
print(int(issues.values.sum()), len(review), len(clean))      # 5 4 2

Expected output: 5 4 2 (five issue flags, four rows in review, two accepted). The dates other than T03 and T05 are illustrative. Final check from the lecture: a new row with quantity 2.5 and a valid May 2025 date converts successfully but fails the whole-number rule, so it goes to review.

Common mistakes

  • Treating every NaN after conversion as a failure. Compare with the original: only present-then-missing values failed to convert.
  • Letting pandas guess date formats. 03/04/2025 could be March or April; always pass format=.
  • Assuming converted means valid. -1 and a 2026 date both convert; business rules must still be checked.
  • Converting identifiers to numbers. IDs such as 00125 lose their leading zeros; keep them as text.

Summary

  • errors="coerce" turns unreadable values into missing values so problems can be reviewed together.
  • A conversion failure = present before AND missing after conversion.
  • Parse dates with an explicit agreed format; impossible dates become NaT.
  • Validate meaning separately: positive whole quantities, dates within the reporting year.
  • Use one Boolean flag per issue; flags can outnumber invalid rows.
  • Split into clean and review tables, assert that no row is lost, and log the counts.

Flashcards · Converting Numbers and Dates

9 words
WordMeaningExample
  • pd.to_numeric()

    Converts values to numbers; with `errors="coerce"` unreadable values become missing instead of raising an error.

    `pd.to_numeric(df["quantity"], errors="coerce")`

  • errors="coerce"

    An option that replaces values that cannot be converted with a missing value.

    `"three"` becomes NaN.

  • Conversion failure

    A value that was present in the original column but became missing after conversion.

    `df["quantity"].notna() & df["quantity_num"].isna()`

  • pd.to_datetime()

    Converts text to dates; an explicit `format` avoids guessing the meaning of day and month.

    `pd.to_datetime(s, format="%d/%m/%Y", errors="coerce")`

  • NaT

    Not a Time: the pandas missing-value marker for datetime values.

    `31/02/2025` becomes NaT.

  • Thousands separator

    A symbol grouping digits in large numbers, which must be removed before conversion if confirmed.

    `"142,000".replace(",", "")` gives `"142000"`.

  • Whole-number check

    A rule that the remainder after dividing by 1 is zero, rejecting fractions.

    `(q > 0) & (q % 1 == 0)` rejects 2.5.

  • Issue flags

    Boolean columns, one per problem type, explaining why a record is held for review.

    `issues.any(axis=1)` marks rows with at least one issue.

  • Int64 (nullable)

    A pandas integer dtype that allows missing values, used after quantities are validated as whole.

    `clean["quantity_num"].astype("Int64")`