Unit 10: Data cleanup - Subjective Questions

Data cleanup is the process of detecting and correcting inaccurate, incomplete, inconsistent, duplicated, or improperly formatted data.

It is important because it:

• improves the accuracy of analysis;
• prevents invalid values from causing program errors;
• standardizes values collected from different sources;
• removes duplicate and irrelevant records;
• makes matching, sorting, and aggregation more reliable;
• increases confidence in reports and decisions produced from the data.

A typical cleanup workflow involves investigation, matching, formatting, validation, and verification of the cleaned result.

Investigation identifies the structure and quality problems in a dataset. A programmer can:

• inspect its dimensions using df.shape;
• examine column names and data types using df.info() and df.dtypes;
• preview records with df.head() and df.tail();
• obtain summary statistics using df.describe(include="all");
• count missing values with df.isna().sum();
• inspect distinct values using df["column"].unique() or value_counts();
• detect duplicates with df.duplicated();
• test numeric ranges and business rules.

The findings should be documented so that cleanup operations are deliberate, reproducible, and verifiable.

Missing values can be identified in pandas with isna() or isnull(). For example, df.isna().sum() gives the number of missing entries in each column.

Possible treatments include:

• Deletion: use dropna() when incomplete rows are few and nonessential.
• Constant replacement: use fillna("Unknown") for missing categories.
• Statistical imputation: replace numeric values with a mean, median, or mode.
• Forward or backward filling: use ffill() or bfill() for ordered data.
• Source correction: recover values from the original source when possible.

The method must reflect the meaning of the data. For example, replacing a missing salary with zero would incorrectly imply that the person earned nothing.

• A syntactic error violates the expected representation or format. Examples include 2026/35/80 as a date, letters in a numeric field, or an email address without @.
• A semantic error is correctly formatted but logically incorrect. Examples include an age of 250, an end date earlier than a start date, or a product code assigned to the wrong category.

Syntactic errors can often be detected through parsing, data-type conversion, regular expressions, and format validation. Semantic errors require range checks, relationships between fields, reference data, and domain-specific business rules.

In pandas, df.duplicated() identifies duplicate rows, while df.drop_duplicates() removes them. A subset of identifying columns may be used:

df.duplicated(subset=["name", "date_of_birth"], keep=False)

Important precautions include:

• distinguish true duplicates from different entities with similar details;
• normalize spaces, capitalization, dates, and punctuation before comparison;
• select reliable identifying fields;
• decide which record to retain using keep="first", keep="last", or a quality rule;
• preserve an audit copy before deletion;
• compare row counts and inspect removed records afterward.

Duplicate removal should be based on evidence rather than blindly deleting identical-looking rows.

Data profiling is the systematic examination of data to understand its structure, content, quality, and relationships.

A useful profile includes:

• row and column counts;
• column names and inferred data types;
• minimum, maximum, mean, median, and quartiles for numeric data;
• frequency distributions for categorical data;
• counts and percentages of missing values;
• uniqueness and duplicate rates;
• minimum and maximum string lengths;
• common date, phone, and identifier patterns;
• invalid values and outliers;
• relationships or dependencies between columns.

Profiling establishes a quality baseline and helps determine which cleanup rules are required.

Exact matching declares values equal only when their normalized forms are identical. It is fast, predictable, and suitable for stable identifiers such as customer IDs, ISBNs, or verified email addresses.

Fuzzy matching measures similarity rather than requiring equality. It can recognize spelling variations such as Jon Smith and John Smith. Techniques include edit distance, token similarity, and phonetic matching.

Exact matching should be preferred when dependable keys exist. Fuzzy matching is useful for names, addresses, and manually entered text, but it can create false matches. Therefore, fuzzy matching requires a carefully selected threshold, supporting fields, and often human review.

A robust matching procedure can use the following stages:

1. Profile both datasets to identify useful fields and quality problems.
2. Standardize values by trimming spaces, normalizing case, parsing dates, and standardizing phone numbers and addresses.
3. Create blocking keys, such as postcode plus surname initial, to reduce the number of candidate pairs.
4. Compare multiple fields using exact matching for stable values and fuzzy similarity for names or addresses.
5. Assign weights according to field reliability; for example, date of birth may receive more weight than street spelling.
6. Calculate a combined score and define thresholds for matches, nonmatches, and manual review.
7. Resolve conflicts where one record matches multiple candidates.
8. Validate a sample against known outcomes and record the matching decisions.

Using several fields reduces the risk of linking unrelated customers who happen to share a name.

Edit distance, commonly Levenshtein distance, is the minimum number of single-character insertions, deletions, and substitutions required to transform one string into another.

For strings and , a normalized similarity can be expressed as:

where is the edit distance. A score near indicates high similarity.

For example, kitten and sitting have an edit distance of . Edit distance helps match typographical variations in names or addresses. However, strings should first be normalized, and the similarity threshold should be tested because short strings can produce misleading results.

Record linkage is the process of determining which records in one or more datasets refer to the same real-world entity.

• Blocking: groups records by a simple key, such as postcode, so that every record is not compared with every other record.
• Candidate generation: produces plausible record pairs from within compatible blocks.
• Scoring: measures agreement across fields such as name, date of birth, address, and phone number.
• Threshold selection: classifies pairs according to their scores. A high threshold may indicate an automatic match, a low threshold a nonmatch, and intermediate scores may require review.

These stages improve efficiency while balancing false positives and false negatives.

A false positive occurs when two records are incorrectly classified as belonging to the same entity. It can merge information from different people or products. A false negative occurs when two records belonging to the same entity are not matched, leaving duplicates or fragmented information.

They can be reduced by:

• normalizing fields before comparison;
• using multiple independent matching attributes;
• assigning greater weight to reliable fields;
• selecting thresholds with labelled validation data;
• sending uncertain cases for manual review;
• measuring precision and recall;
• avoiding automatic matching based only on a common name;
• periodically reviewing matching rules as data changes.

The preferred balance depends on whether an incorrect link or a missed link has the more serious consequence.

String standardization converts equivalent text into a consistent representation. Common operations include:

• removing leading and trailing whitespace with str.strip();
• replacing repeated internal whitespace;
• converting text to lowercase with str.lower() or using Unicode-aware casefold();
• normalizing Unicode characters;
• standardizing punctuation and abbreviations;
• removing nonessential symbols;
• correcting known spelling variants through a mapping table;
• separating or combining components consistently.

For example, " Main St. ", "MAIN STREET", and "main st" may be mapped to a shared canonical form. The original value should normally be retained because aggressive normalization can remove meaningful distinctions.

A regular expression describes a text pattern and can be used with Python's re module or pandas string methods.

Applications include:

• finding values that fail an expected pattern;
• extracting components such as area codes;
• removing unwanted punctuation;
• replacing repeated spaces;
• validating identifiers;
• identifying values suitable for matching.

For example, str.fullmatch(r"[A-Z]{2}\d{4}") can test whether an identifier contains two uppercase letters followed by four digits. str.replace(r"\s+", " ", regex=True) reduces repeated whitespace to one space.

A pattern match verifies textual structure, not real-world validity. Additional rules are needed to confirm that a value actually exists or is semantically correct.

Date cleanup should proceed as follows:

1. Inspect samples and count the formats in use.
2. Parse values with pd.to_datetime(), supplying a known format whenever possible.
3. Use errors="coerce" to convert unparseable values to NaT for investigation.
4. Resolve ambiguous forms such as 03/04/2026 by applying source-specific day-first or month-first rules.
5. Standardize valid dates, for example with dt.strftime("%Y-%m-%d").
6. Validate semantic constraints, such as allowed ranges and start dates preceding end dates.
7. Retain raw values or an error report for auditing.

A consistent ISO-style representation improves sorting, matching, and data exchange.

Numeric cleanup may require removing currency symbols, thousands separators, spaces, or textual units before conversion. For example, str.replace() can normalize raw strings, followed by pd.to_numeric(..., errors="coerce").

Important considerations include:

• determine whether commas represent thousands or decimal separators;
• preserve negative amounts shown with signs or parentheses;
• distinguish missing values from genuine zeros;
• validate permitted ranges and units;
• avoid binary floating-point for exact financial calculations by using decimal.Decimal or integer minor units;
• store numbers as numeric values and apply display formatting only when presenting them.

This separation prevents formatted strings such as "$1,250.00" from interfering with arithmetic or sorting.

• Series.map() is useful for translating individual values through a dictionary or function. Unmapped dictionary values commonly become missing.
• replace() substitutes selected values and can preserve values not mentioned in a mapping.
• apply() runs a custom function on each value or along a DataFrame axis. It is flexible but may be slower than vectorized operations.
• Vectorized string methods such as str.strip(), str.lower(), and str.replace() operate efficiently and clearly on entire text columns.

Vectorized methods should generally be preferred for standard transformations. Mapping tables are appropriate for known categories, while apply() should be reserved for logic that cannot be expressed cleanly using built-in vectorized operations.

A reproducible pipeline can contain these stages:

1. Load safely: preserve source files and explicitly configure delimiters, encodings, and expected types.
2. Profile: inspect schema, missingness, distributions, duplicates, and invalid patterns.
3. Normalize schema: standardize column names and select appropriate data types.
4. Clean values: trim strings, normalize categories, parse dates, convert numbers, and handle missing entries.
5. Validate: apply ranges, required-field rules, allowed-value sets, and cross-column constraints.
6. Match: normalize identifiers, generate candidates, calculate similarities, and classify links.
7. Deduplicate or merge: choose surviving values using documented precedence and quality rules.
8. Format output: apply consistent dates, identifiers, units, and column order.
9. Verify: rerun profiling, compare counts and totals, and inspect exceptions.
10. Record lineage: save logs, rejected records, rule versions, and summary metrics.

The pipeline should be deterministic and idempotent so that rerunning it produces the same cleaned result.

An outlier is an observation that differs substantially from the general distribution of a variable. It may indicate an input error, an unusual but genuine event, or a separate population.

Investigation methods include:

• checking minimum and maximum values;
• plotting histograms, scatter plots, or box plots;
• calculating standardized scores;
• using the interquartile range rule, where possible outliers fall below or above ;
• comparing the value with related columns and the original source.

Treatment may involve correcting a source error, converting units, capping values, transforming the variable, analyzing the record separately, or retaining it unchanged. Deletion is justified only when evidence shows that the observation is invalid or unsuitable for the intended analysis.

Storage formats should preserve meaning and support computation, while presentation formats should improve readability.

• Dates should be stored as date or datetime values, not as decorative strings such as "10th September 2026".
• Numbers should remain numeric so that they can be sorted and calculated; currency symbols and thousands separators should be added only for display.
• Identifiers may need string storage to preserve leading zeros, as in "00125".

Mixing display formatting with storage can produce incorrect sorting, failed calculations, and lost information. Python programs should clean and type values first, then use methods such as strftime() or format specifications only when exporting or displaying results.

Post-cleanup validation should include:

• comparing input and output row counts;
• recounting missing, duplicate, and invalid values;
• checking data types, ranges, category sets, and uniqueness constraints;
• confirming cross-field rules and referential integrity;
• reconciling important totals, such as quantities or monetary amounts;
• evaluating matching accuracy with reviewed samples and metrics such as precision and recall;
• testing formatting requirements in exported files;
• inspecting rejected and manually reviewed records;
• rerunning the pipeline to check idempotence.

Documentation should record the source, cleanup rules, mappings, thresholds, code version, execution time, changed-record counts, exceptions, and responsible reviewer. This audit trail makes the result explainable and reproducible.