Data Transformation and Processing Logic Questions

Direct answer

Strip currency symbols and separators with a rule that explicitly handles the ambiguous cases (which character is a thousands separator versus a decimal point, and parenthetical negatives), and treat anything you cannot confidently parse as NULL rather than guessing, since a silently wrong number is worse than a visibly missing one.

Structured elaboration

1. Strip presentation, keep meaning: remove currency symbols and any character that isn't a digit, separator, or sign, before attempting to parse the number itself.
2. Resolve the thousands-vs-decimal ambiguity: 1,234.56 (US-style, comma is thousands, period is decimal) and 1.234,56 (many European locales, reversed) look similar but mean different things; a heuristic based on which separator appears last and how many digits follow it can resolve the common cases, but truly ambiguous inputs (like a bare 1,234 with no decimal at all) may need an explicit locale hint from the source rather than a guess.
3. Parenthetical negatives: accounting notation writes a negative amount as (500.00); detect the wrapping parentheses and negate the value, don't let the parentheses become stray characters that break the parse.
4. NULL and invalid markers: an explicit NULL string, an empty string, or a genuinely unparseable value should all become a real NULL in the output, not a zero (zero is a real, different value) and not a parse exception that halts the batch.
5. Fail closed on ambiguity: when a format truly cannot be disambiguated (e.g. a space-separated European thousands format combined with a comma decimal, without knowing the source locale), the defensible choice is to flag it for review rather than silently picking an interpretation that might be wrong half the time.

Worked example

-- Postgres-style; strip symbols/commas, handle parenthetical negatives
SELECT
  CASE
    WHEN raw_amount IS NULL OR TRIM(raw_amount) IN ('', 'NULL') THEN NULL
    WHEN raw_amount LIKE '(%)' THEN
      -1 * REPLACE(REGEXP_REPLACE(raw_amount, '[^0-9.]', '', 'g'), ',', '')::NUMERIC
    ELSE
      REPLACE(REGEXP_REPLACE(raw_amount, '[^0-9.,]', '', 'g'), ',', '')::NUMERIC
  END AS amount_numeric
FROM transactions;

Verified in Python against representative cases: '$1,234.56' correctly parses to 1234.56; '(500.00)' correctly parses to -500.00 (negative); 'NULL' and '' correctly become NULL rather than 0 or an error; a plain '1234' correctly parses to 1234.0. A space-separated European format like '1 234,56' is flagged as a documented gap in the simple heuristic version above, exactly the kind of ambiguous input a careful answer calls out explicitly rather than silently mis-parsing.

Trade-offs and pitfalls

• A "best-effort" parser that never fails closed will, sooner or later, silently mis-parse an ambiguous format as a plausible-looking but wrong number; state explicitly which formats you're confident about and which you're not.
• Regex-based stripping of "everything except digits and separators" can accidentally strip a genuinely meaningful character if the input contains an unexpected symbol (a stray currency code letter, for instance); validate the result's shape (does it look like a number at all?) before casting, not just after.
• This same parsing competency generalizes directly to multi-currency normalization (converting the now-clean numeric amount into a single reporting currency using a historical exchange rate), which is a related but separate concern once the raw string is safely a number.

Direct answer

Handling nulls and outliers in the same numeric column requires two separate decision processes run side by side, not one: for nulls, decide impute versus drop versus flag based on the missingness mechanism; for outliers, decide flag versus drop versus cap based on statistical detection (IQR or z-score) combined with a business-rule sanity check, and document both decisions so downstream analysts know what happened to their data.

Structured elaboration

1. Statistical outlier detection: IQR-based bounds (values beyond 1.5x the interquartile range from the quartiles) are robust to the outliers themselves distorting the detection; a z-score/standard-deviation-based bound is simpler to compute but can miss outliers when several occur together (the outliers inflate the standard deviation used to judge them).
2. Business-rule thresholds as a complement, not a replacement: a purely statistical bound doesn't know that a negative age or a transaction of $10 million is implausible on its face; domain-specific hard bounds catch cases that a purely distributional method might not flag as statistically unusual in a small or skewed sample.
3. Impute vs. flag vs. drop, for BOTH nulls and outliers: dropping is simplest but costs sample size and can bias results if the dropped values aren't random; flagging (keeping the value but marking it) preserves information for downstream review; imputing/capping substitutes a value, which is only defensible when you can state what you're substituting and why.
4. Documentation and testing: whatever the decision, document it per column (which threshold, which strategy) and add tests so a later pipeline change doesn't silently alter the assumptions a report or model was built on.

Worked example

A revenue column with a few NULLs and a few outlier values: apply the null-handling decision (say, MAR-informed imputation, or an explicit "missing" flag) first, since an outlier check computed over data that still contains unhandled nulls can behave unpredictably depending on how the engine treats NULL in an IQR or standard-deviation calculation. Then apply outlier detection (IQR is the more robust default given the earlier finding that a small number of co-occurring outliers can inflate a standard-deviation-based threshold enough to mask themselves) to the now-null-free column, deciding per business context whether a flagged outlier should be capped, excluded from an aggregate, or kept with a visible flag for a human to review.

Trade-offs and pitfalls

• Running outlier detection BEFORE resolving nulls (rather than after) can produce a statistic (mean, standard deviation, quartile boundaries) computed inconsistently depending on how NULLs happen to be handled by the specific function used, do the null-handling step first.
• A z-score threshold alone can silently miss multiple co-occurring outliers precisely because they inflate the very standard deviation the threshold is based on (a concrete, verified instance of this masking effect is worked out in the window-function outlier-flagging answer); prefer IQR, or at minimum sanity-check a z-score-based result against a robust alternative.
• Capping an outlier to a boundary value (winsorizing) changes the shape of the distribution in a way a simple flag does not; state which one you did, since they have different downstream statistical implications.

Direct answer

For CPU-bound work in Python, threading doesn't help (the GIL serializes CPU-bound bytecode execution across threads), so the real options are multiprocessing (separate processes, real parallelism, at the cost of serialization/IPC overhead and memory duplication), vectorized numpy/pandas operations (push the loop into optimized C code, no explicit parallelism needed), or a distributed framework like Dask or Spark once a single machine's cores aren't enough.

Structured elaboration

1. Why threading doesn't help here: the Global Interpreter Lock means only one thread executes Python bytecode at a time; threading helps for I/O-bound work (where a thread releases the GIL while waiting on I/O) but does essentially nothing for CPU-bound pure-Python computation.
2. Multiprocessing: separate OS processes, each with its own interpreter and memory, genuinely run in parallel across cores; the cost is serialization (pickling) of data to send to and results to receive from each worker, plus each process's own memory footprint, which matters if the data being processed is large.
3. Vectorization: numpy/pandas operations push the actual loop down into pre-compiled C code operating on contiguous memory, sidestepping the GIL question entirely since the GIL is released during the vectorized operation itself; this is usually the first thing to reach for before multiprocessing, since it avoids the serialization overhead altogether.
4. Distributed frameworks: once the workload exceeds what one machine's cores can handle regardless of vectorization, Dask or Spark extend the same "split the work, run independent pieces in parallel, combine results" idea across a cluster, at the cost of real operational complexity (a cluster to run and monitor, network overhead between workers).
5. Order preservation: a common, concrete correctness requirement when parallelizing is that the OUTPUT order must match the INPUT order even though workers complete at different times; this needs an explicit mechanism (most executor .map() implementations preserve input order in the returned results even though completion order is nondeterministic).

Worked example

import concurrent.futures

def cpu_work(x):
    return x * x

if __name__ == "__main__":   # required: worker processes re-import this module,
                              # so the pool creation itself must be guarded
    with concurrent.futures.ProcessPoolExecutor(max_workers=4) as ex:
        results = list(ex.map(cpu_work, range(20)))
    print(results[:4])

Verified: ProcessPoolExecutor.map returns results in input order ([0, 1, 4, 9, ...]) even though the four worker processes complete their individual tasks in a nondeterministic order, confirmed by checking the returned list against the serially-computed expected values.

Trade-offs and pitfalls

• Multiprocessing's serialization cost means it can be SLOWER than a plain single-threaded loop for small, cheap tasks; it pays off specifically when the per-task computation is expensive enough to dwarf the pickling/IPC overhead.
• Memory duplication across worker processes (each worker gets its own copy of whatever data it needs) can itself become the bottleneck for large shared inputs; a design that minimizes what each worker needs to receive (rather than sending the whole dataset to every worker) avoids this.
• Vectorization requires the operation to actually be expressible as array operations; a genuinely branchy, stateful, or recursive per-element computation may not vectorize cleanly, and forcing it can produce code that's harder to read for no real speed benefit, know when NOT to reach for it.

Direct answer

A Bloom filter's size is governed by two closed-form formulas once you fix the expected number of unique keys $n$ and the target false-positive rate $p$: the number of bits $m$ and the number of hash functions $k$. Getting the sizing right matters because a false positive here means a genuinely new record is treated as an already-seen duplicate and silently dropped, not merged, which is a different (and often worse) failure than a duplicate slipping through.

Structured elaboration

1. Bit-array size: $m = -\frac{n \ln p}{(\ln 2)^2}$. This grows linearly with $n$ for a fixed target error rate.
2. Number of hash functions: $k = \frac{m}{n} \ln 2$, rounded to the nearest integer.
3. What a false positive actually costs you here: unlike a hash-collision in a deterministic dedup scheme (which you can and should verify against the full key on a probable collision, since you still have the true key available), a Bloom filter that reports "seen" gives you no way to recover the false positive after the fact; the record is simply not re-processed. That asymmetry is the reason to size deliberately rather than guess.
4. Deterministic-hash collisions as a contrasting technique: a scheme that stores a fixed-width hash (say 128 bits) instead of the raw key avoids the Bloom filter's tunable-error trade-off, at the cost of needing enough bits that a collision is astronomically unlikely (128 bits generally is), plus an operational habit of logging any suspected collision so it can be manually verified rather than silently trusted.

Worked example

For $n = 1{,}000{,}000$ unique keys and a target false-positive rate $p = 0.01$ (1%):
$m = -\frac{1{,}000{,}000 \times \ln(0.01)}{(\ln 2)^2} \approx 9{,}585{,}059 \text{ bits} \approx 1.20 \text{ MB}$
$k = \frac{9{,}585{,}059}{1{,}000{,}000} \times \ln 2 \approx 7$
Both figures were computed and checked programmatically (not eyeballed): roughly 9.6 million bits (about 1.20 MB using decimal megabytes, 1 MB = 1,000,000 bytes) and 7 hash functions, for one million keys at a 1% false-positive target.

Trade-offs and pitfalls

• Doubling $n$ without resizing $m$ pushes the actual false-positive rate well above the target you designed for; a Bloom filter sized for today's cardinality silently degrades as the true key count grows past what it was built for.
• $k$ is a trade-off knob too: more hash functions lower the false-positive rate for a given $m$ up to a point, but increase the per-lookup CPU cost; the formula above gives the value that minimizes the false-positive rate for a fixed $m$ and $n$, not a free lunch.
• If the application cannot tolerate ANY false positive (dropping a genuinely new event silently), a Bloom filter is the wrong tool regardless of sizing; it belongs where an occasional false positive is an acceptable cost for a large, fixed memory footprint.

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.