Python and Pandas for Data Analysis Questions

Direct answer

Build one boolean mask per condition, amount > 100, status.isin([...]), created_at.between(...), and combine them with & for AND / | for OR, wrapping every individual comparison in parentheses. Parentheses are required because Python's & and | bind tighter than comparison operators like > and ==, so without them the expression groups incorrectly and pandas raises rather than silently misevaluating.

Approach

import pandas as pd

orders = pd.DataFrame({
    'order_id': [1, 2, 3, 4],
    'user_id': [10, 11, 12, 13],
    'amount': [50, 150, 200, 90],
    'status': ['complete', 'shipped', 'pending', 'complete'],
    'created_at': ['2024-01-15', '2024-02-20', '2024-02-25', '2024-04-01'],
})
orders['created_at'] = pd.to_datetime(orders['created_at'])

mask_amount = orders['amount'] > 100
mask_status = orders['status'].isin(['complete', 'shipped'])
mask_date = orders['created_at'].between('2024-01-01', '2024-03-31')

result = orders[mask_amount & mask_status & mask_date]

# Equivalent with .query()
result_q = orders.query(
    "amount > 100 and status in ['complete', 'shipped'] "
    "and created_at >= '2024-01-01' and created_at <= '2024-03-31'"
)
# result.equals(result_q) -> True

Output (only order_id 2 satisfies all three conditions: amount 150 > 100, status "shipped", created_at 2024-02-20 in range):

   order_id  user_id  amount   status created_at
1         2       11     150  shipped 2024-02-20

Key points

• Use & / | for elementwise boolean-Series logic, never the Python keywords and / or, which only work on single scalar truth values and raise on a Series.
• Wrap each comparison, (orders['amount'] > 100), in parentheses before combining with &/|; the operator-precedence trap is the single most common bug in hand-written boolean masks.
• .query() reads more like SQL and lets you write and/or/in directly as keywords inside the string, since the expression is parsed and evaluated separately from normal Python operator precedence.

Complexity

Each comparison, isin, or between call is a single vectorized O(n) pass over the column. Combining k masks with & is O(k*n) total. .query() compiles the expression once and evaluates it in a comparable O(n) pass (and can use numexpr under the hood for large frames to reduce the number of intermediate boolean arrays materialized). Memory: each intermediate boolean mask is O(n) at 1 byte per element, and the final result is O(m) for the m matching rows.

Edge cases

• NaN in a compared column: any comparison against NaN (not-a-number) evaluates to False, so rows with missing amount are silently excluded, never raised, which matches how NaN comparisons work generally.
• Missing parentheses: orders['amount'] > 100 & orders['status'] == 'complete' raises a TypeError at the &, because & binds to 100 and orders['status'] before the comparisons resolve; it fails loudly rather than returning a wrong-but-silent mask.
• Timezone-aware vs timezone-naive values mixed in created_at: comparing them raises a TypeError, so normalize timezone handling before filtering.
• Duplicate index labels in orders: boolean masking is positional in effect (aligned by index, but each row is independently True/False), so duplicates don't break the filter itself, though a later .loc lookup by label on the result could return more rows than expected.

Trade-offs and pitfalls

For very large frames, .query() can be more memory-efficient because it can avoid materializing every intermediate boolean mask (numexpr evaluates the whole expression in a more fused fashion), which matters if you are chaining many conditions. For readability with column names that are valid Python identifiers, .query() also tends to be easier to review at a glance than a long &-chained boolean expression. Prefer .loc[mask] over df[mask] when you also need to select specific columns in the same step, since df[mask][cols] = ... reintroduces exactly the chained-indexing risk that plain boolean filtering for reading avoids.

Direct answer

Pull the three fields with separate, narrowly-scoped .str.extract() calls (one regex per field) rather than one mega-regex with several optional named groups. A single combined pattern with .*? lazy gaps between optional groups is fragile: it is easy to write one that compiles and "runs" but silently returns empty or missing values for cases it was meant to catch. Coerce the ETA with pd.to_datetime(..., errors='coerce') so malformed dates become NaN (not-a-number, pandas' missing-value marker) instead of raising, and leave order_id / reason as NaN when the note does not contain them.

Approach

1. Write one small, targeted regex per field instead of one large regex trying to capture everything positionally.
2. Require the order id to contain at least one digit (via a lookahead) so the word "order" appearing without an id (e.g. "No order info") does not falsely match on the next word.
3. Run each regex with Series.str.extract, which is vectorized (implemented in pandas' compiled extension code, not a Python-level loop).
4. Normalize the two accepted ETA separators (- and /) to one, then parse with an explicit format= and errors='coerce'.

Worked example

import re
import pandas as pd

df = pd.DataFrame({
    "customer_notes": [
        "Order 12345: delayed due to weather; ETA 2024-05-10",
        "ETA:2024/06/01; order#98765 - late",
        "No order info, customer canceled",
        None,
        "Order: ABC123; ETA unknown",
    ]
})

order_re = re.compile(r"(?i)\border[:#]?\s*(?P<order_id>(?=[A-Za-z0-9-]*\d)[A-Za-z0-9-]+)")
reason_re = re.compile(r"(?i)\b(?P<reason>delayed(?: due to [^;.,]+)?|late|cancell?ed|returned|damaged)\b")
eta_re = re.compile(r"(?i)ETA[:\s]*(?P<eta_raw>\d{4}[-/]\d{2}[-/]\d{2})")

notes = df["customer_notes"]
order_id = notes.str.extract(order_re)["order_id"]
reason = notes.str.extract(reason_re)["reason"]
eta_raw = notes.str.extract(eta_re)["eta_raw"]
eta = pd.to_datetime(eta_raw.str.replace("/", "-", regex=False), format="%Y-%m-%d", errors="coerce")

out = df.assign(order_id=order_id, reason=reason, eta=eta)
print(out[["order_id", "reason", "eta"]])

Output (verified against pandas 3.0.3):

  order_id                  reason        eta
0    12345  delayed due to weather 2024-05-10
1    98765                    late 2024-06-01
2      NaN                canceled        NaT
3      NaN                     NaN        NaT
4   ABC123                     NaN        NaT

Row 2 has no order id (the note only says "No order info"), row 3 is a fully missing note (None), and row 4 has an unparseable ETA ("unknown"): all three land as NaN or NaT (not-a-time, pandas' missing marker for datetime data) rather than raising or silently mis-extracting.

Key points

• Compile each regex once (re.compile) and reuse it; .str.extract accepts a compiled pattern directly.
• The digit lookahead (?=[A-Za-z0-9-]*\d) on the order id is what keeps "No order info" from false-matching "info" as an order id. Without it, any word after "order" would be captured.
• Keep the three extractions independent. Interleaving them into one pattern with optional groups and lazy wildcards is exactly the failure mode above: it looks compact, but each optional group's success depends on how the lazy .*? between them consumed the string, which is not something you can verify by eye.

Complexity

Each .str.extract call is a single vectorized pass over the Series: O(n) in the number of rows, with per-row cost dominated by the regex engine rather than by pandas. Memory: .str.extract returns a new DataFrame per call, so three calls materialize three intermediate frames of about n rows each, and .assign copies that data into the final frame. For very wide free-text extraction (many fields), several small targeted patterns concatenated column-wise are usually cheaper than one very wide regex, because a single catastrophic-backtracking pattern can turn what looks like O(n) into effectively O(n times k) with a large hidden constant k.

Edge cases

• Missing note (None/NaN): .str.extract propagates a missing input to missing output without raising; eta_raw for that row is NaN, so pd.to_datetime on it stays NaT.
• No match at all (row 2): every group for that row is NaN; do not assume at least one field always fills in.
• Ambiguous separators: the pattern matches both - and / for the ETA; if the underlying data mixes MM/DD/YYYY and DD/MM/YYYY for the slash form, that is a genuinely unresolvable ambiguity from the text alone and should be flagged, not silently guessed at.

Performance tips for millions of rows

• Prefer Series.str methods (.str.extract, .str.contains) over .apply() with a Python-level regex loop, since the former runs in pandas' vectorized layer.
• Pre-compile every regex and pass the compiled object in, not a fresh string each call.
• Keep each pattern narrow and anchored; avoid nested quantifiers that can backtrack catastrophically on adversarial or unexpectedly long text.
• For genuinely large volumes, read and process in chunks (pd.read_csv(chunksize=...)) or move to an out-of-core engine (Dask, Polars) once a single machine's memory becomes the bottleneck.
• Benchmark on a representative sample before committing: several small, simple extracts are frequently faster in aggregate than one large regex trying to do everything in one pass, and they are far easier to debug when a field comes back wrong.

Direct answer

Split the logic into a fit step and a transform step: fit computes and stores a median per group (only for groups with enough non-missing observations) plus one overall median as the fallback, and transform looks up each row's group median, falls back to the overall median when the group is unseen, too small, or was entirely missing, and fills only the rows where the target is actually NaN (not-a-number, pandas' missing-value marker). Storing the fitted medians as plain attributes means the exact same transform logic can be reapplied to new data without recomputing anything from the original training set.

Structured elaboration

• Fit: group by the region column, compute the median of the target column per group (pandas' median() already ignores NaN within each group), and separately count non-null observations per group.
• Reliability threshold: a group's stored median is only trusted if it had at least min_group_size non-missing observations at fit time. Groups below that threshold, or groups that never appeared at fit time at all, fall back to the single overall median. This directly matches "too few observations, or was never seen at all" from the question, both routes land on the same fallback.
• Transform: map each row's group to its stored median (or NaN if the group wasn't kept), fill the gaps in that mapped Series with the global median, then use it only to fill rows where the target is actually missing, leaving already-present values untouched.
• Packaging this as a small class with fit/transform methods (the scikit-learn estimator convention) is what makes "fit once, reapply consistently" concrete: the fitted medians live on the object, not recomputed inline, so new data goes through transform alone and gets exactly the same group-to-median mapping the training data used.

Worked example

import pandas as pd

class GroupMedianImputer:
    def __init__(self, group_col, target_col, min_group_size=3):
        self.group_col = group_col
        self.target_col = target_col
        self.min_group_size = min_group_size

    def fit(self, X):
        non_null = X.dropna(subset=[self.target_col])
        counts = non_null.groupby(self.group_col)[self.target_col].size()
        medians = non_null.groupby(self.group_col)[self.target_col].median()
        # only trust a group's median if it had enough observed values
        reliable = medians[counts >= self.min_group_size]
        self.group_medians_ = reliable.to_dict()
        self.global_median_ = X[self.target_col].median()
        return self

    def transform(self, X):
        X_out = X.copy()
        fill_values = X_out[self.group_col].map(self.group_medians_).fillna(self.global_median_)
        mask = X_out[self.target_col].isna()
        X_out.loc[mask, self.target_col] = fill_values[mask].values
        return X_out

# fit on historical data with three regions: two well-observed, one tiny, one all-missing
train_df = pd.DataFrame({
    'region': ['east', 'east', 'east', 'west', 'west', 'west', 'tiny', 'north'],
    'income': [50000, 52000, 48000, 60000, 62000, 64000, 70000, float('nan')],
})
imputer = GroupMedianImputer('region', 'income', min_group_size=3)
imputer.fit(train_df)

# reapply the same fitted logic to new data, including an unseen region
incoming_df = pd.DataFrame({
    'region': ['east', 'west', 'tiny', 'south', 'north'],
    'income': [float('nan')] * 5,
})
new_df = imputer.transform(incoming_df)

Verified: imputer.group_medians_ keeps only east (50000.0) and west (62000.0), the two regions with at least min_group_size=3 non-missing observations; tiny (1 observation) and north (all-missing) are correctly excluded from group_medians_. Running transform on incoming_df fills east and west with their own group medians, and correctly falls back to the overall median (60000.0) for tiny, the never-seen south, and the all-missing north, confirming all three fallback triggers named in the question, too few observations, never seen, and entirely missing, resolve to the same value.

Trade-offs and pitfalls

• Complexity: fit is O(n) for the groupby plus counting pass; transform is O(n) for the map and fill. Memory is O(g) for the stored medians, where g is the number of distinct groups, independent of how large future batches are.
• Edge cases: a group with observations but they're all above/below normal ranges still gets a legitimate median, that's expected, not a bug; the target column being entirely NaN at fit time makes the global median itself NaN, so decide explicitly whether that should raise rather than silently leaving rows unfilled; non-numeric or badly-typed group keys (e.g. a mix of int and str region codes) will map inconsistently, so normalize the group key's dtype before fitting.
• A common mistake is computing the fallback as "global median of the fit set" but then never re-checking it against new data: if the new data's distribution shifts meaningfully from what was fit, the stored medians go stale silently, since nothing here re-fits automatically. This is a reasonable trade for reproducibility (the same input always imputes the same way), but it does mean this needs periodic re-fitting in production, the same way any other fitted preprocessing step does.
• min_group_size is itself a judgment call: too low and a two-observation "median" is really just picking one noisy value; too high and legitimately small-but-real segments always fall back to the (potentially very different) global median. There's no universally correct default; pick it based on how much you trust a small sample's median in this specific dataset.

Direct answer

Prefer pivot_table when you want a one-call pivot that also aggregates duplicate (index, column) pairs; prefer groupby + unstack when you want explicit control over the aggregation step or need to chain other transformations before reshaping. pivot_table is a convenience layer built on the same groupby machinery underneath, so their performance is close; the real difference is ergonomics and what happens when your keys are not unique.

Structured elaboration

pd.pivot (no _table) is the plain reshape, no aggregation at all: give it duplicate keys and it raises rather than silently picking one. pivot_table groups the duplicate rows and reduces them with aggfunc before reshaping, which is exactly why it never errors on the duplicate (store, date, product) rows described here.

Worked example

import pandas as pd

df = pd.DataFrame({
    'store': ['S1', 'S1', 'S1', 'S2', 'S2'],
    'date': ['2026-01-01', '2026-01-01', '2026-01-02', '2026-01-01', '2026-01-01'],
    'product': ['A', 'A', 'B', 'A', 'A'],
    'sales': [10, 5, 7, 3, 4],
})

sales_matrix = pd.pivot_table(
    df,
    values='sales',
    index='store',
    columns='product',
    aggfunc='sum',
    fill_value=0
)
print(sales_matrix)

Output (verified by running this exact code, and confirmed identical to df.groupby(['store','product'])['sales'].sum().unstack(fill_value=0)):

product   A  B
store
S1       15  7
S2        7  0

Store S1 has two rows for ('S1', 'A') (sales 10 and 5); pivot_table sums them to 15 rather than raising or silently dropping one. The (S2, B) combination never occurs in the source data, so fill_value=0 fills it in rather than leaving NaN.

How pivot_table handles duplicates, precisely

For every (index, column) combination that appears more than once, pivot_table collects all matching rows' values and reduces them with aggfunc before placing a single cell in the output. This is exactly groupby([index_cols, columns_cols])[values].agg(aggfunc) followed by .unstack(); pivot_table is not doing anything groupby cannot do, it is packaging that same operation into one call with a friendlier signature (aggfunc, fill_value, margins) at the cost of being slightly less flexible if you need to do something groupby's chain does not directly expose.

Trade-offs and pitfalls

• Performance is close to identical since pivot_table is a groupby under the hood; do not choose between them for speed, choose for readability and whether you need margins/fill_value for free.
• groupby + unstack gives you more control mid-pipeline: you can filter, apply multiple aggregations with .agg({...}), or reset the index before unstacking, which is awkward to bolt onto pivot_table's single call.
• pivot_table defaults aggfunc to 'mean', which silently changes your result if you forget to pass aggfunc='sum' explicitly, a common source of "my totals look too small" bugs.
• Both can leave you with a MultiIndex on the columns if you pivot on more than one values/columns combination; flatten it explicitly (e.g. columns.map('_'.join)) before handing the result to code that expects flat column names.

Direct answer

Copy-on-Write (CoW) is now mandatory in pandas 3.0, it cannot be turned off. Under CoW, every pandas object behaves as if it holds independent data: reading through a result that used to be called a "view" is free, but the moment either the original or the derived object is written to, pandas makes a physical copy at that instant so the two can never silently diverge into each other. The old question "is this a view or a copy?" is no longer something you can predict from the syntax you used, and you no longer need to predict it, because a write can never leak across objects either way.

Structured elaboration

Before pandas 2.0, some operations (plain-column selection, some slicing) returned a "view" backed by the same underlying NumPy array as the source. Whether mutating that view also mutated the source depended on internal implementation details the documentation itself called unreliable, SettingWithCopyWarning existed precisely because pandas could not always tell you which case you were in. That warning was heuristic: it could fire on code that was actually fine, and stay silent on code that silently corrupted data.

Under mandatory CoW, SettingWithCopyWarning does not exist anymore. This was verified directly: pandas.errors.SettingWithCopyWarning raises AttributeError, it is not deprecated-but-present, it is gone, because the ambiguous partial-mutation scenario it warned about can no longer happen.

The two-statement form now silently succeeds on a copy

import pandas as pd

df = pd.DataFrame({'a': [1, 2, 3, 4, 5], 'b': [10, 20, 30, 40, 50]})
subset = df[df['a'] > 2]
subset['b'] = 999

Verified by running this exact code: no warning, no error, subset['b'] is 999 for the matching rows, and df is completely unchanged. This is the single biggest behavior change from the pre-3.0 mental model. What used to be "sometimes this mutates df, sometimes it doesn't, watch for the warning" is now deterministic: it never mutates df. subset is an independent object the instant it is assigned to a variable, so writing to it can only ever affect subset.

Only the single-expression chained form warns, and it is only a warning

import pandas as pd

df = pd.DataFrame({'a': [1, 2, 3, 4, 5], 'b': [10, 20, 30, 40, 50]})
df[df['a'] > 2]['b'] = 999

Verified by running this exact code: it raises no exception, df is unchanged, but a ChainedAssignmentError is emitted. Its class hierarchy (ChainedAssignmentError.__mro__) confirms it is a subclass of the built-in Warning, not a hard exception, so by default it prints to stderr and execution continues, exactly like any other uncaught Python warning. Write "warns via ChainedAssignmentError," not "raises ChainedAssignmentError," since the latter implies the program stops, and by default it does not.

Pandas can only catch this pattern in the single-expression form because that is the only shape where its internals can observe, within one call, that the object receiving the write was produced moments earlier by an indexing operation with no other references. Once the intermediate result is stored in a variable across two statements, as in the block above, subset is just an ordinary DataFrame by the time you write to it, so no warning path fires at all. That means the coverage of ChainedAssignmentError is narrower than most people assume: the two-statement form, which is the shape most engineers actually write, and the one most "avoid SettingWithCopyWarning" tutorials describe, is exactly the one pandas 3.0 cannot warn about.

Worked example

import pandas as pd

df = pd.DataFrame({'a': [1, 2, 3, 4, 5], 'b': [10, 20, 30, 40, 50]})

# two-statement chained assignment: silently succeeds on a copy
subset = df[df['a'] > 2]
subset['b'] = 999
print(df)          # unchanged
print(subset)       # 'b' is 999 for rows where a > 2

# single-expression chained assignment: warns via ChainedAssignmentError, does not halt
df[df['a'] > 2]['b'] = 999
print(df)          # still unchanged

# the correct pattern: a single .loc assignment
df.loc[df['a'] > 2, 'b'] = 999
print(df)          # 'b' is 999 for rows where a > 2, this is the only reliable pattern

Running this exact code against pandas 3.0.3: the two-statement block leaves df's b column as [10, 20, 30, 40, 50] (unchanged) and subset's b column as [999, 999, 999] for the three matching rows. The single-expression line prints a ChainedAssignmentError warning to stderr and also leaves df unchanged. Only the final .loc[...] = ... line mutates df, giving b = [10, 20, 999, 999, 999].

Trade-offs and pitfalls

• Do not treat "no warning" as a correctness signal anymore, since the two-statement chained form now produces neither a warning nor a mutation. Correctness depends entirely on habitually writing through a single .loc/.iloc assignment, df.loc[mask, 'col'] = value, which is the only pattern guaranteed to mutate df in both the pre-3.0 and 3.0 worlds. Treat it as the default even outside interview settings.
• Study material that says "chained assignment sometimes works, watch for SettingWithCopyWarning" is wrong on both halves for pandas 3.0: the warning does not exist, and "sometimes works" is not a real behavior either, the two-statement form is now unconditionally a no-op on the original.
• Because CoW defers the physical copy until the first write, code that reads heavily but writes rarely is cheaper than eager-copy pandas used to be, you get view-like sharing on the read path for free. But once you do write, that write pays a full copy of the affected block at that moment, so a loop that repeatedly mutates the same derived frame can pay a copy cost on every iteration if pandas cannot prove there is only one reference to the underlying data. The fix is the same discipline as always: do the transformation as one vectorized .loc assignment, or build the result with .assign() or pd.concat rather than mutating iteratively.
• To avoid unnecessary copies and large temporaries on genuinely large data: stop calling .copy() defensively "just in case," CoW already protects you from cross-object mutation, so a defensive copy only doubles memory for no safety benefit; prefer a single .loc[...] = ... write over any chained form so you pay exactly one copy, not an unpredictable number; drop columns you do not need before doing a wide operation rather than after; and check df.memory_usage(deep=True) and downcast dtypes (categorical for low-cardinality strings, smaller integer or float widths), since dtype choice affects the size of any eventual copy far more than CoW's own bookkeeping does.