Module 2: Reading and Understanding Tables · Lecture 7 of 32
A failed conversion is evidence about the source, not an invitation to erase it. Parse a deliberately messy numeric column, preserve unknown values, and distinguish independent transformations from mutations of the original table.
What you will learn
- Parse numeric strings while retaining invalid-row evidence.
- Use nullable integers without treating unknown as zero.
- Predict how derived columns behave under pandas 3 Copy-on-Write.
Understand the problem
Parsing and validation are separate
pd.to_numeric can turn numeric-looking text into numbers. With errors=’coerce’, unparseable tokens become missing. That makes detection convenient, but the conversion alone does not decide whether the row should be rejected, corrected, or retained as unknown. Keep the original text alongside the parsed value when investigating a production export.
Integer domains need more than a cast
The retail schema stores whole units and whole paise. A source value of 2.5 can be numeric yet invalid for units. The shared cleaner first checks that the numeric remainder modulo one is zero, then converts accepted values to nullable Int64. It rejects the row rather than truncating 2.5 to 2. In another domain, fractional quantities may be valid; the rule belongs in the schema.
Missingness propagates
Adding an unknown nullable integer to a known value normally produces unknown, which is often exactly the honest result. Equality with a missing sentinel is not the right missingness test; use isna or notna. When a condition contains an unknown boolean, explicitly decide whether that row belongs to your selected population instead of letting a default hide the decision.
One-step mutation
With pandas 3 Copy-on-Write, selecting a column into a separate Series does not create a supported way to mutate the parent through that Series. If you mean to update a DataFrame, assign through that DataFrame with loc. If you mean to create a new result, use assign or another transformation and retain the original. This distinction improves reasoning about reusable pipeline functions.
Run the worked example
Download and extract the course kit linked below. Open a terminal in the extracted folder, complete the setup in Lecture 2, and run python run_course.py --lesson 7 --examples-only. Each script starts from the shared dataset; it does not depend on a notebook cell from another lecture. All retail records are fictional and amounts are integer paise unless a chart explicitly says INR.
from pathlib import Path
import numpy as np
import pandas as pd
from course_data import load_orders, load_raw, load_customers, clean_orders, ROOT, OUT
source = pd.Series(['2', 'unknown', '0', '2.5', None], dtype='string')
numeric = pd.to_numeric(source, errors='coerce')
whole = numeric.where(numeric.mod(1).eq(0)).astype('Int64')
review = pd.DataFrame({'source': source, 'units': whole})
review['valid_units'] = review['units'].gt(0).fillna(False)
base = pd.DataFrame({'units': [1, 2, 3]})
derived = base['units']
derived.iloc[0] = 99
base.loc[base['units'].eq(1), 'units'] = 5
print(review.to_string(index=False))
print('Parent values:', base['units'].tolist())
assert whole.isna().sum() == 3
assert base['units'].tolist() == [5, 2, 3]
Verified output
source units valid_units
2 2 True
unknown <NA> False
0 0 False
2.5 <NA> False
<NA> <NA> False
Parent values: [5, 2, 3]
Walk through the result
The string unknown, the fractional count 2.5, and the absent field all become missing in the integer domain, but their original source tokens remain available. Zero parses correctly and then fails the positive-quantity rule, demonstrating why parsing is not validation. The derived Series is modified independently; the parent changes only when loc targets base explicitly. This example assumes the pandas 3.x environment supplied with the kit.
Common mistakes and diagnosis
Avoid astype(int) on unvalidated source strings: the first bad token raises and hides the rest of the issue population. Avoid astype(float) for IDs and large integer money fields. Filling every missing value with zero merges “not observed” and “observed zero.” Do not suppress chained-assignment warnings on an older environment as a substitute for fixing ownership; rewrite the assignment clearly.
Practice assignment
Create a rejected-values table containing every row whose parsed units is missing or not positive. Count invalid source tokens separately from valid numeric zeros. Preserve the original text so a source-system owner can investigate.
Worked solution and explanation
Add the following code below the worked example, or run the matching file in the solutions folder. Compare the result with the business definition before trusting a passing assertion.
rejected = review.loc[~review['valid_units'], ['source', 'units']].copy()
invalid_or_absent = int(review['units'].isna().sum())
nonpositive = int(review['units'].le(0).fillna(False).sum())
assert len(rejected) == invalid_or_absent + nonpositive
assert invalid_or_absent == 3 and nonpositive == 1
Check your understanding
Why is a zero-unit order different from an unparseable-unit order?
Answer guide
Zero is a known numeric value that violates the positive-unit business rule. An unparseable token supplies no accepted numeric quantity at all. Both are rejected in this course, but they may require different source fixes. Keeping separate reasons helps distinguish a business-process issue from an export or typing defect.
Reference and next step
Download the complete course kit: datasets, all examples, solutions, and capstone.
Return to the full Data Analysis with Python and pandas curriculum.
