CompTIA Data+ DA0-002 · Free study guide

Objective 2.3 — Transform and cleanse data

Transformation changes representation so data can be compared, combined, and analyzed. Cleansing corrects or manages defects discovered during exploration. Both can improve usefulness, but both can also destroy meaning when applied mechanically. Preserve the raw source, record the rule, and make the output reproducible.

String manipulation and regular expressions

Common string operations include trimming leading or trailing whitespace, normalizing case, replacing characters, splitting a field, extracting a substring, and concatenating values. These operations can standardize North and north before comparison, or separate a controlled compound code into documented components.

A regular expression, or RegEx, describes a text pattern. It can identify values that resemble an email address, extract a code from a longer field, or replace a known formatting pattern. A RegEx match proves that text fits the pattern, not that the value is real. 555-555-5555 may match a phone pattern and still be inaccurate.

Parsing and conversion

Parsing interprets one representation according to a grammar or layout. A process might parse a JSON object, a delimited address, or a timestamp containing an offset. Conversion changes a value from one type or unit to another, such as text to date, kilograms to pounds, or integer cents to decimal dollars.

Dates require an explicit format. 03/04/2026 is ambiguous without a locale convention. Time-zone conversion should preserve the instant and, when business questions need it, the original local context. Failed conversions should be quarantined or reported rather than silently replaced with null.

Validate units before arithmetic. Combining meters and feet in one column produces plausible numbers with incompatible meanings.

Binning and clustering

Binning places numeric values into defined ranges, such as ages 18–24, 25–34, and 35–44. It can simplify reporting or apply a business rule. Boundaries must be explicit so a value on the edge enters exactly one bin. Binning loses detail, so retain the original continuous value when possible.

Clustering groups observations according to similarity in their features rather than according to prewritten range boundaries. It can discover segments, but the resulting groups require interpretation and validation. Clustering and binning both create groups; binning follows fixed rules, while clustering derives groups from the data and method.

Scaling and standardization

Scaling changes numeric magnitude so variables become comparable. Min-max scaling commonly maps values to a range such as 0 through 1. It is useful when a distance-based method would otherwise be dominated by a field measured in large units.

Standardization commonly subtracts the mean and divides by the standard deviation, producing values centered near zero with unit standard deviation. It expresses observations in standard-deviation units. The term “standardize” can also mean making categories or formats consistent, so use scenario context.

Scaling does not make bad data accurate, remove skew, or prove variables deserve equal analytical weight. Save the fitted transformation parameters and apply the same rule to future data.

Augmentation and exploding

Augmentation adds useful information from another source or a derived process. Adding census population to regional sales enables per-capita measures. The new field needs a reliable join key, compatible time period, and documented source; otherwise enrichment can create false precision.

Exploding converts one row containing a collection into several rows, usually one per array element. If one order record contains an array of three products, exploding can create three order-product rows. This changes grain. Order-level revenue copied to every exploded row would triple totals unless it is allocated or kept in a separate order table.

The reverse family of operations may collect rows into arrays or pivot values into columns. Always state the grain before and after reshaping.

Merging and appending

Merging joins data sets horizontally by matching keys and adding columns. Adding customer region to orders by customer ID is a merge. Check key uniqueness before merging; a many-to-many match can multiply rows unexpectedly.

Appending stacks rows from compatible data sets. Combining January and February extracts with the same schema is an append. Align names, types, units, and category meanings first. Adding a source or period column can preserve lineage.

Imputation and deletion

Imputation replaces missing values using a chosen rule. Options include a constant, mean, median, mode, group-specific statistic, forward value, or model estimate. The rule depends on why data is missing and how the result will be used. Median is less sensitive to extreme values than mean, but neither recreates the unknown truth.

Imputation can reduce apparent variability and bias relationships. Add a missingness indicator when the fact that a value was missing may itself matter, and document which values were filled.

Deletion can remove invalid fields, duplicate records, or observations with unusable missing data. It is defensible when the rule is explicit and the removed portion does not create unacceptable bias. Deleting every incomplete row can disproportionately remove one population and make a tidy data set less representative.

Derived variables and calculated fields

A derived variable is computed from other fields, such as age from date of birth, margin from revenue and cost, or duration from start and end timestamps. A calculated field may be evaluated in a transformation, database view, or BI model.

Define formula, units, rounding, null behavior, and effective dates. Age changes over time, so storing it without its calculation date creates stale data. Ratios need a rule for zero denominators. Reusing one certified calculation prevents dashboards from implementing conflicting versions.

Scenario: preparing subscription activity

A subscription feed contains mixed-case emails with spaces, dates in two locale formats, prices in dollars and euros, an array of add-on products, missing income, and monthly files that need customer-region attributes.

The process preserves raw inputs, trims and case-normalizes email under an approved rule, parses dates with source-specific formats, and converts currency using a documented rate and date. Exploding add-ons creates a new order-add-on grain without copying full subscription revenue into every row. Monthly files are standardized and appended; region is merged after checking customer-key uniqueness. Income imputation is chosen only after examining missingness and distribution, with an indicator retained. Derived monthly recurring revenue uses one documented formula. Row counts, unmatched keys, conversion failures, and totals are reconciled at every stage.

Exam traps

• Treating a RegEx format match as proof that the value exists or is accurate.
• Parsing an ambiguous date without a locale or source rule.
• Binning when the analysis still needs continuous detail.
• Confusing fixed bins with data-derived clusters.
• Scaling before correcting units or invalid values.
• Exploding an array without recognizing the change in grain.
• Merging on non-unique keys and overlooking row multiplication.
• Appending columns instead of rows, or merging files that should be appended.
• Imputing every missing value with the mean regardless of skew or cause.
• Deleting incomplete rows without checking resulting bias.
• Publishing a derived metric without formula, unit, or null rules.

Readiness checklist

• I can choose trimming, case normalization, splitting, replacement, concatenation, or RegEx for a text problem.
• I can distinguish parsing from type or unit conversion.
• I can explain the difference between binning and clustering.
• I can choose and distinguish min-max scaling and statistical standardization.
• I can describe augmentation and the grain change caused by exploding.
• I can distinguish merging from appending and test their row-count effects.
• I can compare imputation choices and defend deletion only after checking bias.
• I can define a derived variable with formula, units, dates, rounding, and null behavior.
• I can preserve raw data and make every transformation reproducible and auditable.