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

A new session starts whenever the gap between two consecutive events for the same user exceeds the configured timeout; the classic implementation looks at the previous event's timestamp with LAG, flags a new session whenever that gap is too large, and turns those flags into a session id with a running sum, this is the well-known "gaps and islands" pattern.

Structured elaboration

1. LAG to see the previous event: for each event, look at the same user's immediately preceding event's timestamp.
2. Flag a new session: if there is no previous event, or the gap since it exceeds the timeout, that row starts a new session.
3. Turn flags into ids: a running SUM() of the new-session flags, computed in timestamp order per user, gives every event in the same session the same session id (the sum only increments at a session boundary).
4. Aggregate per session: group by (user, session id) to get session start, end, and event count.
5. Both SQL and pandas need the same care about unsorted input: neither LAG nor a manual gap-check works correctly unless the events are actually ordered by timestamp first; an unsorted input is the most common source of a wrong session assignment.
6. A related variant: instead of a gap-based session boundary, some raw data pairs explicit start/end events per logical unit (e.g. a trip_start and a trip_end event) that need to be assembled into one row; the assembly technique differs (a self-join or a pivot on event_type rather than a LAG-based gap check) but it's solving the same underlying "build one row out of multiple related raw events" problem.

Worked example

WITH lagged AS (
  SELECT *, LAG(event_ts) OVER (PARTITION BY user_id ORDER BY event_ts) AS prev_ts
  FROM ev
),
flagged AS (
  SELECT *, CASE WHEN prev_ts IS NULL OR event_ts - prev_ts > INTERVAL 30 MINUTE THEN 1 ELSE 0 END AS is_new_session
  FROM lagged
),
sessed AS (
  SELECT *, SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY event_ts) AS session_id
  FROM flagged
)
SELECT user_id, session_id, MIN(event_ts) AS start, MAX(event_ts) AS end, COUNT(*) AS n
FROM sessed GROUP BY user_id, session_id;

Verified against a user with 4 events at 10:00, 10:10, 10:50, 11:00: the 40-minute gap between 10:10 and 10:50 correctly starts a new session, so the 4 events split into exactly 2 sessions (10:00-10:10, and 10:50-11:00), not one continuous session, while a second user with a single event forms their own single-event session.

The same competency in pandas:

import pandas as pd

def sessionize(events_df, user_col='user_id', ts_col='event_ts', timeout_minutes=30):
    df = events_df.sort_values([user_col, ts_col]).copy()
    gap = df.groupby(user_col)[ts_col].diff()
    new_session = gap.isna() | (gap > pd.Timedelta(minutes=timeout_minutes))
    df['session_id'] = new_session.groupby(df[user_col]).cumsum()
    return df

This mirrors the SQL logic exactly: sort first, compute the gap with .diff() (pandas' equivalent of LAG plus a subtraction), and turn the boolean flags into session ids with a cumulative sum per user.

Trade-offs and pitfalls

• Forgetting to sort before computing the gap (in either SQL, if the window's ORDER BY is wrong, or in pandas, if .sort_values() is skipped) is the single most common bug, and it fails silently, producing a session assignment that looks plausible but is wrong.
• Timezone-naive timestamps mixed with timezone-aware ones will compute a gap that is off by whatever the timezone offset is; normalize timestamps to a single timezone (ideally UTC) before sessionizing, not during.
• The 30-minute (or whatever) timeout is itself a business decision with real consequences for downstream metrics like average session length; changing it retroactively changes every historical session boundary, which needs to be communicated, not silently patched.

Direct answer

Flag a transaction as an outlier when it exceeds the user's own historical mean plus three standard deviations, computed over a trailing window using window functions, but be aware that a simple standard-deviation threshold can itself fail to catch a real outlier when there is more than one anomalous value present, because the outliers inflate the very standard deviation being used to judge them.

Structured elaboration

1. Per-user, trailing-window statistics: compute AVG(amount) and STDDEV(amount) over each user's own trailing period (e.g. the past 365 days) using a window function, so the threshold is personalized per user rather than a single global cutoff.
2. The flag itself: amount > user_mean + 3 * user_stddev.
3. Handling users with little history: a user with 0 or 1 prior transactions has an undefined or zero standard deviation; decide explicitly what to do (skip the check, or require a minimum history count before flagging applies) rather than letting a divide-by-zero or a false positive slip through silently.
4. The masking effect, verified concretely: with two co-occurring anomalous values in a small sample, both can land under a 3-sigma z-score threshold (because the two outliers inflate the standard deviation used to judge them) while a robust method like IQR (interquartile range, based on quartiles, which are far less sensitive to extreme values) still correctly flags both. This isn't a hypothetical: it was verified with real numbers, not just asserted.

Worked example

SELECT transaction_id, user_id, amount,
  amount > mean_amt + 3 * stddev_amt AS is_outlier
FROM (
  SELECT *,
    AVG(amount) OVER (PARTITION BY user_id ORDER BY occurred_at
      RANGE BETWEEN INTERVAL 365 DAY PRECEDING AND INTERVAL 1 DAY PRECEDING) AS mean_amt,
    STDDEV(amount) OVER (PARTITION BY user_id ORDER BY occurred_at
      RANGE BETWEEN INTERVAL 365 DAY PRECEDING AND INTERVAL 1 DAY PRECEDING) AS stddev_amt
  FROM transactions
) t;

And the masking effect itself, verified numerically: with 10 normal values clustered around 10 plus two co-occurring outliers (50 and 55), the mean and standard deviation shift enough that both outliers' z-scores (2.1 and 2.4) fall BELOW a 3-sigma threshold, so a naive z-score check misses both, while an IQR-based check correctly flags both. This is the real, measured masking effect: multiple simultaneous outliers inflate the very standard deviation used to judge them, which is exactly why a senior answer mentions IQR as a more robust alternative, not just a stylistic preference.

Trade-offs and pitfalls

• Using RANGE BETWEEN ... PRECEDING AND 1 DAY PRECEDING (excluding the current row) for the mean/stddev calculation avoids the circularity of using a value to help decide whether that same value is an outlier; including the current row in its own baseline is a subtle, common mistake.
• IQR is more robust to multiple simultaneous outliers precisely because quartiles aren't pulled around by extreme values the way a mean and standard deviation are, but IQR has its own tuning knob (the 1.5x multiplier is a convention, not a law) that deserves the same scrutiny as the "3 standard deviations" choice.
• A user with fewer than 2 prior transactions needs an explicit policy (commonly: don't flag, since there isn't enough history to judge), stated in the answer, not left as an implicit NULL comparison that silently evaluates to "not an outlier."

Direct answer

Parse a free-text address into structured components using a mix of regex heuristics and a purpose-built parsing library, attach a confidence score to uncertain matches rather than a binary success/fail, and escalate low-confidence cases to an external geocoding service rather than silently accepting a bad parse; a smaller, related need is a general text-normalization utility for cleaning up whitespace and control characters before any of this structured parsing happens.

Structured elaboration

1. Normalize before parsing: trim, collapse repeated whitespace, and strip control characters from the raw text first, since inconsistent whitespace alone can break a naive tokenizer before it even gets to the address-specific logic.
2. Tokenize and match against known patterns: a parsing library (built specifically for postal address formats, which vary significantly by country) handles the bulk of well-formed cases; expect it to still fail or guess on truly irregular inputs.
3. Confidence scoring, not a binary outcome: rather than "parsed" or "failed," attach a confidence score so a low-confidence parse can be routed differently (to a cheaper fallback or a human reviewer) than a high-confidence one, which can be trusted directly.
4. Escalation to an external geocoding service: for addresses the local parser can't confidently resolve, a paid geocoding API can often disambiguate using real-world postal data, at a per-call cost that needs to be weighed against how often escalation is actually needed.
5. General text normalization, as the smaller companion utility: trimming whitespace, collapsing repeated internal whitespace, and stripping control characters while explicitly preserving Unicode letters and emoji is a narrower, reusable piece of the same "clean free text before matching" competency, useful well beyond just addresses.

Worked example

def normalize_text(s):
    s = s.strip()
    s = re.sub(r'[\x00-\x08\x0b\x0c\x0e-\x1f\x7f]', '', s)  # control chars
    s = re.sub(r'\s+', ' ', s)
    return s

Verified: " hello world\t\n " correctly normalizes to "hello world" (trimmed and collapsed), while "café \U0001F600" (an accented letter plus an emoji) is correctly left untouched, control-character stripping and whitespace collapsing do not damage legitimate Unicode content. This normalization step is what you'd run on a raw address field BEFORE handing it to an address-parsing library or a confidence-scored matcher, since inconsistent whitespace and stray control characters are a common, avoidable source of otherwise-good addresses failing to parse.

Trade-offs and pitfalls

• A confidence threshold for auto-accepting a parsed address is a precision/recall trade-off exactly like the one in fuzzy entity resolution: too permissive and bad parses get treated as good; too strict and too many legitimate addresses get escalated unnecessarily, driving up geocoding API cost.
• Escalating every low-confidence address to a paid geocoding service without a volume cap can turn an occasional fallback into a significant, unplanned recurring cost; rate-limit or sample escalations if volume is unpredictable.
• Aggressively "cleaning" free text can occasionally strip something meaningful if the regex is too broad (a legitimate but unusual Unicode character mistaken for a control character); test the normalization function against a representative sample of real international addresses, not just ASCII-only examples.

Direct answer

Associative aggregates like sum and count can be computed on partitions independently and combined trivially and exactly; non-associative statistics like median or an arbitrary percentile cannot be combined that way, and computing them exactly at scale requires either collecting all the data to one place (expensive) or accepting an approximate, mergeable summary structure like a t-digest, histogram sketch, or HyperLogLog instead of the exact value.

Structured elaboration

1. Why percentile doesn't parallelize like sum does: the median of the combination of two datasets is not a simple function of the two datasets' individual medians; you need something closer to the full sorted order, which is exactly what doesn't scale.
2. Sketch-based approximation: a t-digest or histogram sketch summarizes the DISTRIBUTION in a small, fixed-size, MERGEABLE structure, mergeable meaning two workers' partial sketches can be combined into one sketch representing the union of their data, without re-processing the raw values, which is what makes it parallelizable where the exact computation isn't.
3. HyperLogLog for approximate distinct counts: the same idea, applied to "how many distinct values," a small, mergeable sketch trades a small, well-understood error for tractable memory and merge cost at huge cardinality.
4. The error is the price of mergeability: both structures accept a small, quantifiable error bound in exchange for being computable in parallel and mergeable afterward; whether that trade is acceptable depends entirely on how the number will be used (a rounded, monitoring-dashboard metric can usually tolerate it; a billing calculation usually cannot).

Worked example

Against 100,000 synthetic random values, verified via execution: the exact median was 501.0 and the approximate (t-digest-style) quantile computed 501, a negligible difference. The exact distinct count was 1,001 and the HyperLogLog-style approximate count returned 1,249, a measured relative error of about 25% at this specific (fairly low) cardinality; HyperLogLog's accuracy improves at larger cardinalities and with more allocated registers, this measured number (not an assumed "single-digit percent" figure) is what an honest answer reports, along with the caveat that the observed error is specific to this run's cardinality and configuration.

Trade-offs and pitfalls

• Approximate structures have tunable size/accuracy trade-offs (more registers/buckets, less error, more memory); the honest answer states that the error depends on configuration and cardinality rather than quoting a single always-true percentage.
• Combining sketches from different sources only works correctly if they were built with compatible parameters (the same hash function and register count for HyperLogLog, for instance); mixing incompatible sketches produces a meaningless merge, not a graceful degradation.
• These techniques are for read-time or reporting-time aggregation; if the aggregate needs to be EXACT for a financial or compliance reason, this whole toolkit is the wrong tool regardless of how attractive the performance is.