You are viewing a public preview
Sign in to save your progress, do assignments, join discussions and get feedback.
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.
By the end of this lesson you can:
pd.to_numeric(..., errors="coerce") and explain what coerce does.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 lecture uses six simulated transactions whose quantity and date arrived as text:
| ID | quantity | transaction_date | Problem |
|---|---|---|---|
| T01 | 2 | valid 2025 date | none |
| T02 | three | valid 2025 date | word instead of number |
| T03 | -1 | 31/02/2025 | negative quantity and impossible date |
| T04 | (missing) | valid 2025 date | quantity missing |
| T05 | 4 | a real date in 2026 | outside the reporting year |
| T06 | 1 | valid 2025 date | none |
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.
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.
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.
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.
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.
03/04/2025 could be March or April; always pass format=.-1 and a 2026 date both convert; business rules must still be checked.00125 lose their leading zeros; keep them as text.errors="coerce" turns unreadable values into missing values so problems can be reviewed together.NaT.Flashcards · Converting Numbers and Dates
9 wordsConverts values to numbers; with `errors="coerce"` unreadable values become missing instead of raising an error.
An option that replaces values that cannot be converted with a missing value.
A value that was present in the original column but became missing after conversion.
Converts text to dates; an explicit `format` avoids guessing the meaning of day and month.
Not a Time: the pandas missing-value marker for datetime values.
A symbol grouping digits in large numbers, which must be removed before conversion if confirmed.
A rule that the remainder after dividing by 1 is zero, rejecting fractions.
Boolean columns, one per problem type, explaining why a record is held for review.
A pandas integer dtype that allows missing values, used after quantities are validated as whole.