Data Transformation and Processing Logic Questions

Direct answer

The right way to handle a column's missing values depends first on WHY the data is missing, not just on how much is missing; understanding whether the missingness is completely random (MCAR), related to observed data (MAR), or related to the missing value itself (MNAR) should drive the choice between dropping, imputing, or explicitly flagging missingness as its own category.

Structured elaboration

1. MCAR (missing completely at random): the fact that a value is missing has nothing to do with any variable, observed or not. Dropping rows is relatively safe here since it doesn't systematically bias the remaining data, though it still costs sample size.
2. MAR (missing at random, conditional on observed data): missingness correlates with something you CAN observe (e.g. income is more often missing for self-employed respondents, and you know who's self-employed). Imputation conditioned on the observed correlate (model-based, or at least stratified by the correlate) is more defensible than a single global mean/median.
3. MNAR (missing not at random): the missingness itself depends on the unobserved value (e.g. very high earners are more likely to decline to state income). This is the hardest case, naive imputation can systematically bias results, and an explicit "missing" indicator/category, or a domain-informed model of the missingness mechanism itself, is often more honest than pretending you can fill it in accurately.
4. When to add an indicator instead of imputing a value at all: if the fact that a value is missing is itself informative (which is common under MAR/MNAR), an explicit missing-indicator feature can carry real signal that a filled-in value would erase.
5. Communicating the choice: document which strategy was used per column and why, since a stakeholder consuming a dashboard or a model built on imputed data needs to know how much of what they're looking at is real versus filled-in, especially for columns with high missingness rates.

Worked example

A churn dataset with missing values in income, last_login, and plan_type, where the missingness patterns differ:

• plan_type missing only for very old accounts predating a schema migration: this looks close to MCAR (the migration is unrelated to any account's actual behavior), dropping or imputing with the overall mode is comparatively low-risk.
• last_login missing specifically for accounts that never logged in after signup: this is closer to MNAR-adjacent (the missingness IS the signal, "never logged in"), so an explicit "never logged in" category, not any numeric imputation, is the appropriate handling.
• income missing more often among certain segments who chose not to disclose it: closer to MAR/MNAR, a global mean/median fill would understate the real variation and should be flagged to stakeholders as a known limitation, or handled with a segment-conditioned estimate instead.

Trade-offs and pitfalls

• Defaulting to "just impute with the mean/median" for every column regardless of the missingness mechanism is the most common mistake; it's the easy answer but not always the correct one.
• An explicit missing-indicator column can leak information in a modeling context if the indicator itself was constructed using information not actually available at prediction time; treat it with the same point-in-time discipline as any other feature.
• Communicating "this metric is based on N% imputed values" honestly changes how much a stakeholder should trust a headline number, saying so is uncomfortable but is exactly the trust-preserving move a senior analyst makes.

Direct answer

Check for all three representations of "no value" together, NULL, an empty string, and a whitespace-only string, since they are easy to conflate but behave differently in comparisons, and normalize them to a single canonical form (NULL) so every downstream query only has to check one thing.

Structured elaboration

1. Why check all three together: WHERE email IS NULL alone misses an empty string; WHERE email = '' alone misses NULL and whitespace-only values; TRIM(email) = '' catches whitespace-only strings but you still need the IS NULL check alongside it since TRIM(NULL) is NULL, not an empty string, and NULL = '' is NULL (neither true nor false), not true.
2. Normalizing rather than just detecting: once identified, updating all three cases to a single NULL representation means every later query in the pipeline only has one case to handle, rather than every downstream author needing to remember all three checks independently.
3. Dialect caveats: some databases (notably Oracle) treat an empty string as equivalent to NULL by default, which changes which of the checks above are even necessary there; state the dialect you're targeting.

Worked example

SELECT id FROM users2 WHERE email IS NULL OR TRIM(email) = '';

Against rows with email values NULL, '', ' ', and 'a@x.com', this correctly returns ids for the first three (verified), and leaves the fourth alone; a query checking only IS NULL or only = '' would each miss two of the three "no real value" cases.

Trade-offs and pitfalls

• Normalizing to NULL is a one-way, destructive operation for the original raw representation; if you might later need to distinguish "was explicitly empty" from "was never set" for audit purposes, capture that distinction before normalizing, not after.
• TRIM behavior around non-breaking spaces, tabs, and other whitespace-like Unicode characters is not always what you expect across every SQL dialect; a strict "empty after trimming standard ASCII whitespace" check can still miss a value that's visually empty but contains e.g. a non-breaking space.
• Running this normalization as a one-time UPDATE and never repeating it means any new NULL-adjacent representation a future data source introduces (a new placeholder string like "N/A") slips through unnoticed; pair this with an ongoing validation check, not a one-time cleanup.

Direct answer

Extract the nested scalar fields with a JSON path expression into named columns, and explode (unnest) any array field into one output row per array element, producing a flat table with the scalar fields repeated across each element's row; this single pattern is reported as the most common first task in real data-engineering work.

Structured elaboration

1. Scalar nested fields: a JSON path expression ($.user.id, $.product.id) pulls a nested scalar directly into a named top-level column.
2. Array fields need UNNEST, not just extraction: a nested array can't become a single column value in a flat table without losing information (or awkwardly encoding a list into one cell); UNNEST turns each array element into its own row, with the surrounding scalar fields repeated on every one of those rows.
3. The flatten-timing trade-off: doing this extraction at ingestion time produces a fixed, fast-to-query schema but requires a migration whenever the nested structure changes; parsing the JSON at query time is more flexible (no rewrite needed when new nested fields appear) but costs more per query and doesn't benefit as much from columnar storage/compression.
4. A deeper worked case: computing something like daily active users by country and daily revenue by category directly from a deeply nested payload (a user object with attributes, a products array with prices) requires flattening BOTH the scalar user attributes and exploding the products array in the same query, then aggregating on the now-flat result.

Worked example

SELECT event_id,
  json_extract_string(raw_json, '$.user.id') AS user_id,
  json_extract_string(raw_json, '$.product.id') AS product_id,
  UNNEST(actions) AS action  -- one row per element of the actions array
FROM raw_events;

Verified against an event whose actions array has 2 elements (view, click): this correctly produces 2 output rows for that one event, with user_id and product_id repeated identically on both, and the action field distinct per row, exactly the explode semantics the question requires.

Trade-offs and pitfalls

• An array field that is EMPTY (zero elements) disappears entirely from the output with a plain UNNEST (an inner-join-like behavior); if downstream code expects to see the parent record at all even when its array is empty, use the "outer" variant of unnest (explode_outer in Spark, UNNEST with an explicit outer-join equivalent elsewhere) so the record survives with a NULL element instead of vanishing.
• Flattening at ingestion time locks in a schema that a later nested-field addition will require a migration to accommodate; flattening at query time avoids that migration but repeats the parsing cost on every single query, including ones that never touch the new field.
• A deeply nested payload with several arrays at different levels can produce a combinatorial explosion of rows if multiple arrays are exploded in the same query without care (a cross-product of array elements rather than the intended pairing); be deliberate about which arrays actually need to be exploded together versus separately.

Direct answer

Build one reusable, configurable imputation function rather than ad-hoc per-column code: numeric columns get a chosen strategy (commonly the median, which is robust to outliers), categorical columns get their own strategy (commonly the mode or an explicit placeholder), the function must not mutate its input, must preserve dtypes, and should optionally flag which values were imputed so downstream consumers can tell real data from filled-in data.

Structured elaboration

1. Per-column-type strategy, configurable: accept a strategy name per column type (numeric_strategy='median', categorical_strategy='mode') rather than hardcoding one approach, since different columns can legitimately need different handling.
2. Preserve dtype and avoid in-place mutation: work on a copy of the input, and cast the filled column back to its original dtype if the fill operation changed it (a common gotcha: filling an integer column's NaNs with a float median silently upcasts the whole column to float).
3. The <column>_imputed flag: adding a boolean companion column for each imputed column lets a downstream model or analyst distinguish "this was really 42" from "this was imputed to 42," which matters for both model training (it can be a useful feature itself) and for auditability.
4. Edge cases: an entirely-missing column has no median/mode to compute, decide explicitly what happens (skip it, fill with a fixed placeholder, or raise a clear error rather than a cryptic one); an empty input DataFrame should return an empty result without raising.
5. Scaling beyond memory: for a dataset too large to fit in memory, the median specifically can't be computed with one pass (unlike mean, which is just sum/count), it needs either a full-dataset pass to sort or estimate quantiles, or a chunked/approximate quantile estimate; this is worth calling out since "just apply the same function per chunk" doesn't work for median the way it does for mean.

Worked example

import pandas as pd

def impute_dataframe(df, numeric_strategy='median', categorical_strategy='mode',
                      text_fill='unknown', add_flags=True):
    out = df.copy()
    meta = {}
    for col in out.columns:
        if out[col].isna().sum() == 0:
            continue
        if add_flags:
            out[f'{col}_imputed'] = out[col].isna()
        if pd.api.types.is_numeric_dtype(out[col]):
            fill = out[col].median()
        elif pd.api.types.is_object_dtype(out[col]) or isinstance(out[col].dtype, pd.CategoricalDtype):
            mode = out[col].mode()
            fill = mode.iloc[0] if not mode.empty else text_fill
        else:
            fill = None
        out[col] = out[col].fillna(fill)
        meta[col] = {'strategy': numeric_strategy if pd.api.types.is_numeric_dtype(df[col]) else categorical_strategy, 'fill_value': fill}
    return out, meta

Handles an empty DataFrame (no columns have any NaNs to process, returns unchanged) and a column that is entirely NaN (its own median/mode over all-NaN input is itself NaN or empty; the fallback text_fill/placeholder path is what keeps this from raising, which is exactly the edge case a real interview probes).

Trade-offs and pitfalls

• Median is more robust than mean to the very outliers that made you decide to impute in the first place; using mean here quietly lets a few extreme values distort every imputed value.
• Adding a _imputed flag column doubles the column count for every imputed field; for very wide tables this is a real storage/complexity cost, worth it specifically when the imputed-vs-real distinction matters downstream (it usually does for modeling, less often for a quick ad-hoc report).
• Fitting the imputation strategy (the median/mode value itself) needs to happen on TRAINING data only if this feeds a model, computing it fresh on validation/test data (or on live production data) leaks information and produces an inconsistent, non-reproducible pipeline.

Direct answer

Normalize confidently-parseable phone numbers into a single canonical format (E.164-style, a leading + and country code), extract any extension separately rather than losing it or corrupting the main number, and explicitly return "cannot confidently normalize" for genuinely ambiguous inputs instead of guessing a country code.

Structured elaboration

1. Strip formatting, isolate digits: remove punctuation, spaces, and parentheses to get the raw digit sequence, then separately detect and extract an extension (x123, ext. 123) if present, before trying to interpret the remaining digits as a phone number.
2. Confident cases: a number that already starts with + is assumed to include an explicit country code; a 10-digit number (in a context where you know the default country, e.g. US) can be confidently prefixed with that default country code; an 11-digit number starting with a 1 similarly maps cleanly to a US/Canada E.164 number.
3. Ambiguous cases, fail closed: a number like 001-44-20-1234 mixes an exit code convention with a country code in a way that isn't safely disambiguated without knowing the sender's own dialing convention; rather than guess, return "cannot confidently normalize" and route it to a review queue.
4. Privacy consideration: phone numbers are personal data; whatever normalization and storage approach you use should follow the same access-control and retention discipline as other PII, not be treated as a harmless free-text field just because it's short.

Worked example

def canonicalize_phone(raw, default_country='1'):
    digits = re.sub(r'\D', '', raw)
    ext = None
    m = re.search(r'(?:x|ext\.?)\s*(\d+)$', raw, re.IGNORECASE)
    if m:
        ext = m.group(1)
        digits = re.sub(r'\D', '', raw[:m.start()])
    if raw.strip().startswith('+'):
        return '+' + digits, ext
    if len(digits) == 10:
        return '+' + default_country + digits, ext
    if len(digits) == 11 and digits.startswith('1'):
        return '+' + digits, ext
    return None, ext  # cannot confidently normalize

Verified: '+1 (555) 123-4567' and a bare '5551234567' (with a known US default) both correctly normalize to +15551234567; '5551234 x123' correctly extracts extension 123 while returning None for the main number (a 7-digit number is too ambiguous to confidently assign a country code to); '001-44-20-1234' correctly returns None rather than a confidently-wrong guess.

Trade-offs and pitfalls

• Assuming a default country for any bare 10-digit number is only safe when you actually know the data source's country context; applying a US default to a dataset that actually mixes multiple countries' numbers will silently mis-normalize a real fraction of them.
• A library purpose-built for phone number parsing (handling the full space of international dialing conventions correctly) is generally a better production choice than a hand-rolled regex approach like the one above, which is presented here as an illustration of the fail-closed PRINCIPLE, not a claim that this exact regex handles every real-world case.
• Storing a normalized phone number without also retaining (or being able to reconstruct) the original raw input can make it hard to audit or re-normalize later if the canonicalization rule itself needs to change.