Quick summary
Summarize this blog with AI
A reliable data-cleaning workflow states which values are invalid, preserves raw evidence, and makes every replacement visible. This lesson cleans one small score dataset with NumPy and pandas. It uses explicit assignment rather than chained inplace mutation, so the intended parent DataFrame is updated under Copy-on-Write.
Tested with Python 3.12.14, NumPy 2.3.5, pandas 2.2.3 with Copy-on-Write enabled, and pandas 3.0.6. Run the code blocks in order. These are fictional records with teaching policies, not universal rules for imputing real measurements.
Define the cleaning contract and retain the source
For this fixture, an ID is required, scores must be finite and between 0 and 100, and whitespace around names is removed. Explicit object dtype preserves the raw text and None cells before conversion. A missing name becomes Unknown. A duplicate ID retains the first received record after trimming its ID; later records are set aside for review. In a real system, an event time or trusted source rank may be a better precedence rule.
import numpy as np
import pandas as pd
raw = pd.DataFrame({
'id': [' a ', 'b', 'b', 'c', 'd'],
'name': [' Ada ', 'Ben', 'Ben duplicate', None, ' Dee '],
'score': ['80', 'bad', '90', None, 'inf'],
}, dtype=object)
clean = raw.copy(deep=True)
clean['id'] = clean['id'].astype('string').str.strip()
if clean['id'].isna().any() or clean['id'].eq('').any():
raise ValueError('every record needs a nonempty ID')
duplicate_mask = clean.duplicated(subset=['id'], keep='first')
duplicate_review = clean.loc[duplicate_mask].copy()
clean = clean.loc[~duplicate_mask].copy()
clean['name'] = clean['name'].astype('string').str.strip().fillna('Unknown')
clean['name'] = clean['name'].replace('', 'Unknown')
print(clean['id'].tolist())
print(duplicate_review['id'].tolist())
print(clean['name'].tolist())
Expected output:
['a', 'b', 'c', 'd']
['b']
['Ada', 'Ben', 'Unknown', 'Dee']
The valid later score of 90 for ID b is not silently substituted for its first record. That is an intentional consequence of the documented precedence rule. Keep duplicate_review so the decision can be revisited. A deep DataFrame copy does not recursively clone arbitrary mutable Python objects stored inside object columns; this fixture contains only scalars.
Parse numbers and classify missing versus invalid values
errors='coerce' maps unparseable input to a missing numeric value. Without a separate status field, that would erase the difference between missing source data and malformed input. NumPy's isfinite also excludes infinity, which is numeric but unsuitable under this score contract.
clean['score_raw'] = clean['score']
score_text = clean['score_raw'].astype('string').str.strip()
source_missing = clean['score_raw'].isna() | score_text.eq('').fillna(False)
parsed = pd.to_numeric(score_text, errors='coerce').astype('float64')
valid = parsed.notna() & np.isfinite(parsed) & parsed.between(0, 100)
clean['score_status'] = 'valid'
clean.loc[source_missing, 'score_status'] = 'missing'
clean.loc[~source_missing & ~valid, 'score_status'] = 'invalid'
clean['score_clean'] = parsed.where(valid)
print(clean['score_status'].tolist())
print(clean['score_clean'].fillna('missing').tolist())
Expected output:
['valid', 'invalid', 'missing', 'invalid']
[80.0, 'missing', 'missing', 'missing']
Both malformed 'bad' and infinite 'inf' are invalid. The original None is missing. The raw column remains available for review. These rules avoid treating a silent parse failure as an ordinary measured zero.
Impute explicitly and keep an imputation flag
For demonstration, fill unusable scores with the median of valid retained scores. There is only one valid retained score, so the median is 80. This shows the mechanism; it does not justify median imputation for a real study. With no valid scores, fail instead of inventing a replacement.
fill_value = clean['score_clean'].median()
if pd.isna(fill_value):
raise ValueError('cannot impute without at least one valid score')
clean['score_imputed'] = clean['score_clean'].isna()
clean['score_clean'] = clean['score_clean'].fillna(fill_value)
print(clean['score_clean'].tolist())
print(clean['score_imputed'].tolist())
print(raw['score'].tolist())
Expected output:
[80.0, 80.0, 80.0, 80.0]
[False, True, True, True]
['80', 'bad', '90', None, 'inf']
For predictive modeling, estimate an imputation value using training data only and apply that fitted value to later data. Recomputing a median on test data leaks information about the evaluation set. Preserve both the status and imputation flag so downstream analysis can distinguish observed and substituted values.
Assign to the intended DataFrame under Copy-on-Write
Use df['score'] = df['score'].fillna(value) to replace a column, and df.loc[mask, 'score'] = value for selected rows. A chained call such as df['score'].fillna(value, inplace=True) does not update the parent DataFrame under Copy-on-Write. Likewise, editing a filtered intermediate object does not express an update to its parent.
example = pd.DataFrame({'score': [10.0, None]})
example['score'] = example['score'].fillna(0)
example.loc[example['score'].eq(0), 'score'] = 5
print(example['score'].tolist())
Expected output:
[10.0, 5.0]
Copy-on-Write is the default behavior in pandas 3.0. The pandas 2.2 test for this article enabled pd.options.mode.copy_on_write = True explicitly. The assignment style above also avoids relying on ambiguous view-versus-copy behavior in older code.
Handle an all-equal column before dividing
After imputation, this fixture is constant. Min-max scaling would divide by zero if its range were used without a check. This teaching policy maps a constant feature to zero; removing it or retaining its original units may be a better analytical decision.
low = clean['score_clean'].min()
high = clean['score_clean'].max()
if high == low:
clean['score_scaled'] = 0.0
else:
clean['score_scaled'] = (clean['score_clean'] - low) / (high - low)
print(clean['score_scaled'].tolist())
assert clean['id'].is_unique
assert clean['score_clean'].notna().all()
assert clean['score_clean'].between(0, 100).all()
assert np.isfinite(clean['score_clean']).all()
assert int(clean['score_imputed'].sum()) == 3
assert len(clean) + len(duplicate_review) == len(raw)
Expected output:
[0.0, 0.0, 0.0, 0.0]
Review the effect, not just whether code ran
The fixture retains four of five rows, sends one duplicate for review, and imputes three of four retained scores. That 75% imputation rate is a reason to investigate source quality before drawing conclusions. The assertions verify this contract, while the raw values and status fields preserve the evidence needed to change it.
Do not automatically delete every statistical outlier or normalize every variable. Those are analytical choices based on the data's meaning. Add tests for blank IDs, duplicate precedence, all-invalid scores, values at 0 and 100, constant columns, and an empty input before packaging this workflow into a reusable function.
Continue with pandas window calculations once the input contract is stable. Primary references: Copy-on-Write, numeric conversion, and NumPy isfinite.
