Python and Pandas for Data Analysis Questions

Direct answer

Load the file with read_csv, then explicitly convert event_time with pd.to_datetime(..., errors='coerce') as a separate step rather than relying on read_csv's own parse_dates argument to handle bad rows. That distinction matters: parse_dates has no error-tolerant mode, if even one value in the column fails to parse, pandas silently leaves the entire column as plain strings instead of converting the good rows and marking the bad one, so any downstream code that assumes it's working with real datetimes breaks without warning.

Structured elaboration

1. Load, keeping user_id as a string (preserves any leading zeros) and doing datetime conversion as its own explicit step.
2. Inspect with head() and info(), which reports non-null counts and dtypes per column, this is exactly where a silently-unparsed date column would show up as object/string instead of datetime64.
3. Select and filter: subset to the three requested columns, then filter on value > 0 and on the event falling within calendar year 2021.
4. Handle malformed dates explicitly by coercing to NaT (pandas' null value for datetimes) rather than raising, and by capturing which rows were malformed for follow-up rather than silently discarding them.

Worked example

import pandas as pd

# 1) load, convert event_time explicitly (not via read_csv's parse_dates)
df = pd.read_csv("data.csv", dtype={"user_id": str})
df["event_time"] = pd.to_datetime(df["event_time"], errors="coerce")

# 2) first 5 rows and a concise summary
print(df.head(5))
df.info()

# 3) select columns, filter value > 0 and year == 2021
df["value"] = pd.to_numeric(df["value"], errors="coerce")
sub = df[["user_id", "event_time", "value"]].copy()
mask = (sub["value"] > 0) & (sub["event_time"].dt.year == 2021)
result = sub.loc[mask]

# 4) malformed dates become NaT; capture them rather than silently dropping
malformed = df[df["event_time"].isna()]
if not malformed.empty:
    malformed.to_csv("malformed_event_time_rows.csv", index=False)

Verified against a 6-row fixture where one row's event_time is the literal string "not-a-date": with the explicit pd.to_datetime(..., errors='coerce') step, that row correctly becomes NaT while every other row parses to a real Timestamp, df.info() correctly reports event_time as datetime64[us] with 5 of 6 non-null, and the year/value filter correctly excludes both the malformed row and the out-of-range/non-positive rows, leaving exactly the one row that is genuinely 2021 and positive.

Trade-offs and pitfalls

• The reproducibility bug worth calling out explicitly: passing parse_dates=["event_time"] directly to read_csv on this exact fixture does not produce NaT for the bad row, it silently leaves the whole event_time column as plain strings, dtype object/str, with no error and no warning. This was reproduced directly: pd.read_csv("data.csv", parse_dates=["event_time"]) on a file containing one malformed date returns a column that still reports dtype: str, and any later comparison like sub['event_time'] >= pd.to_datetime('2021-01-01') then raises TypeError: Invalid comparison between dtype=str and Timestamp instead of filtering correctly. Confirmed separately that parse_dates does correctly produce datetime64 when every value in the column happens to be valid, so the failure is specific to the presence of malformed rows, exactly the scenario the question is asking about. The safe pattern is to load the column as-is and convert it explicitly with pd.to_datetime(..., errors='coerce') afterward, which degrades gracefully to NaT per-row instead of abandoning the whole column.
• Filtering with .dt.year == 2021 is simpler and less error-prone than constructing explicit start/end Timestamp boundaries, and it naturally excludes NaT rows (a NaT comparison is always False), so malformed rows are automatically excluded from the year filter without extra code.
• Complexity: everything here is a single linear pass, O(n) time for the load, the datetime/numeric coercion, and the boolean-mask filter, and O(n) memory for holding the loaded frame (plus a second, smaller O(k) copy for the k malformed rows written out separately). Nothing here requires more than one pass over the data or a full in-memory copy beyond the loaded frame itself.
• Edge cases: an entirely empty file, or one where event_time is missing from the header, should fail fast with a clear error rather than proceeding silently; a value column containing non-numeric strings is coerced to NaN (not-a-number, pandas' missing-value marker) by pd.to_numeric(..., errors='coerce') and then naturally excluded by value > 0 (since a NaN comparison is also False), which is convenient but worth flagging explicitly to whoever owns data quality, since bad values disappear from the result set without a visible error.
• Persisting the malformed rows to a side file, rather than just dropping them, is what makes this auditable: silently dropping bad rows hides a data-quality problem; logging them (or writing them somewhere reviewable) surfaces it.

Direct answer: A pandas MultiIndex (hierarchical index) lets a single axis (rows or columns) carry more than one level of labels, so a (country, city) pair identifies a row instead of needing two separate lookup columns. It gives you fast partial selection on the outer level(s), natural groupby-by-level aggregation, and a compact representation that avoids repeating country on every city row.

Structured elaboration

Setup used for every example below (build this fixture exactly as shown before running any block):

import pandas as pd

tuples = [('US', 'NY'), ('US', 'SF'), ('CA', 'Toronto'), ('CA', 'Vancouver')]
idx = pd.MultiIndex.from_tuples(tuples, names=['country', 'city'])
df = pd.DataFrame(
    {'year': [2020, 2020, 2020, 2020],
     'population': [8_336_000, 884_000, 2_930_000, 631_000]},
    index=idx,
)

1. Select every row for one country (partial indexing on the outer level):

print(df.loc['US'])
# equivalently, explicit about which level you're selecting on:
print(df.xs('US', level='country'))

Both drop the country level and return a frame indexed only by city.

2. Reorder and sort the levels:

df_swapped = df.swaplevel('country', 'city').sort_index()
print(df_swapped)

swaplevel only changes level order; it does not sort. Skipping sort_index after a level swap leaves the index non-monotonic, which degrades or breaks partial-label slicing (see Edge cases).

3. Turn the MultiIndex back into plain columns:

df_flat = df.reset_index()
print(df_flat)
df_flat['country_city'] = df_flat['country'] + '_' + df_flat['city']

reset_index() moves every level into a regular column and replaces the index with a default RangeIndex.

4. A related but different operation, unstack, pivots one index level into columns instead of dropping it into rows. It is the tool when you want a country x city matrix rather than flat rows:

print(df['population'].unstack('city'))

This produces one row per country and one column per city, with NaN (not-a-number, pandas' missing-value marker) where a country has no data for that city (verified: CA has NaN under NY/SF, US has NaN under Toronto/Vancouver).

Worked example (verified output, pandas 3.0.3, REPL transcript shown for readability, not meant to be pasted as one script):

>>> df.loc['US']
      year  population
city
NY    2020     8336000
SF    2020      884000

>>> df.swaplevel('country', 'city').sort_index()
                   year  population
city      country
NY        US       2020     8336000
SF        US       2020      884000
Toronto   CA       2020     2930000
Vancouver CA       2020      631000

>>> df.reset_index()
  country       city  year  population
0      US         NY  2020     8336000
1      US         SF  2020      884000
2      CA    Toronto  2020     2930000
3      CA  Vancouver  2020      631000

Complexity: Building the MultiIndex from tuples is O(n log n) (it deduplicates each level into codes and sorts internally for the factorization). Once the index is monotonic ("lexsorted"), .loc['US'] is a binary search on the outer level, O(log n) to find the block plus O(k) to return the k matching rows. Internally a MultiIndex stores each level as small integer codes referencing a deduplicated array of labels, so it is more memory-efficient than a flat index built from concatenated "country_city" strings, especially when labels repeat heavily. reset_index() and swaplevel() both copy the underlying data (O(n) space), they do not mutate in place.

Edge cases:

• Non-lexsorted index: if the levels aren't sorted, partial-label indexing still works but pandas emits PerformanceWarning: indexing past lexsort depth may impact performance (verified) because it falls back to a linear scan instead of binary search. Always sort_index() after swaplevel or after building an index from unsorted tuples.
• Duplicate index entries: df.loc[('US', 'NY')] when that tuple appears twice returns both rows, not an error, silently changing your code's assumption of "one row per key" if you didn't expect duplicates.
• Missing key: df.loc['MX'] on a country not present raises KeyError, so wrap lookups you don't control (e.g. user input) in a try/except or check 'MX' in df.index.get_level_values('country') first.
• All-NaN or empty frame: reset_index() and unstack() both work fine on an empty MultiIndex frame, they simply return an empty frame or an all-NaN matrix respectively; no special-casing is required.

Trade-offs and pitfalls: when MultiIndex helps vs. a flat index

A common mistake is reaching for MultiIndex purely to avoid repeating a column value, when a flat index with an ordinary groupby on that column would be just as fast and far more familiar to teammates and downstream tools.

Direct answer

A genuine left join can never drop rows from the original left DataFrame: it can only fail to
find a match, which produces not-a-number (NaN) on the right-side columns, not a missing row.
So if the row count actually shrank, either the merge is not really a left join (check the
how= argument and that you assigned the result rather than something upstream), or duplicate
keys somewhere caused a downstream drop_duplicates()/aggregation to shrink it after the fact.
The first move either way is merge(..., indicator=True) to see, per row, which side it came
from.

Structured elaboration

Work through this checklist in order; each step is a detection snippet followed by its fix.

1) Confirm counts and get an indicator breakdown

print("orders:", len(orders), "customers:", len(customers))
m = orders.merge(customers, how="left", on="customer_id", indicator=True)
print(m['_merge'].value_counts())
# len(m) should equal len(orders). If it does not, the merge itself is not really a plain
# left join; if it does but many rows are 'left_only', those orders found no match, which
# is a data problem, not a merge bug.

If the key dtypes are incompatible in a hard way (numeric on one side, string on the other),
this call raises ValueError: You are trying to merge on int64 and str columns before you even
see _merge counts, and that error IS the diagnosis: go straight to step 4. Softer mismatches
(matching-looking values, whitespace, invisible characters) do not raise; they merge silently
and show up only as unexpected 'left_only' rows, which is what the rest of this checklist is
for.

2) Rule out an accidental inner join or an overwritten variable

m_inner = orders.merge(customers, how="inner", on="customer_id")
print(len(m_inner), len(orders))

Fix: make sure how="left" is what is actually being called, and that the result is assigned
to a new variable rather than silently reused for something else downstream.

3) Duplicate keys on either side

orders_dup = orders['customer_id'].duplicated(keep=False)
customers_dup = customers['customer_id'].duplicated(keep=False)
print(orders[orders_dup].shape, customers[customers_dup].shape)

Fix: decide the intended semantics. If customers should be a unique reference table, dedupe
it with a business rule (customers.sort_values('updated_at').drop_duplicates('customer_id', keep='last')); if orders legitimately repeats a customer, that is expected and not a bug.

4) Dtype mismatch between key columns

print(orders['customer_id'].dtype, customers['customer_id'].dtype)

A merge on 1 (int) versus '1' (str) matches nothing, silently, because the two values
compare unequal even though they print the same. Fix by casting both sides to the same type:

customers['customer_id'] = customers['customer_id'].astype(orders['customer_id'].dtype)

5) Whitespace and case differences

orders['bad_ws'] = orders['customer_id'].astype(str).str.contains(r'^\s+|\s+$')
print(orders['bad_ws'].sum())

Fix:

for df, col in [(orders, 'customer_id'), (customers, 'customer_id')]:
    df[col] = df[col].astype(str).str.strip().str.lower()

6) Invisible, non-printing characters (non-breaking space, zero-width space)
These characters look identical when printed but are not equal in Python. The standard
library's re module handles this fine; no third-party regex package is needed.

import re

def has_invisible(s):
    return bool(re.search(r'[ ​‌‍]', s))

orders['has_invis'] = orders['customer_id'].astype(str).apply(has_invisible)
print(orders['has_invis'].sum())

Fix (strip the known characters, then Unicode-normalize both sides consistently):

import unicodedata

invis = [' ', '​', '‌', '‍', '']
for ch in invis:
    orders['customer_id'] = orders['customer_id'].str.replace(ch, '', regex=False)
    customers['customer_id'] = customers['customer_id'].str.replace(ch, '', regex=False)

orders['customer_id'] = orders['customer_id'].apply(lambda s: unicodedata.normalize('NFKC', s))
customers['customer_id'] = customers['customer_id'].apply(lambda s: unicodedata.normalize('NFKC', s))

7) Nulls in the key column

print(orders['customer_id'].isna().sum(), customers['customer_id'].isna().sum())

A left merge produces no match for a null key (nulls never equal each other in a join); decide
whether that row should be kept as unmatched, mapped to a sentinel value, or dropped upstream.

8) Confirmed via the validate parameter

orders.merge(customers, how='left', left_on='customer_id', right_on='customer_id', validate='m:1')
# raises pandas.errors.MergeError if customers turns out to have duplicate keys

9) Final reconciliation

m = orders.merge(customers, how='left', on='customer_id', indicator=True)
missing = m[m['_merge'] == 'left_only']
print(missing[['customer_id']].drop_duplicates().head())

Complexity

Each detection step is a single O(n) column scan (dtype check, null check, whitespace/regex
check) or an O(n log n) sort-based step (duplicated(), drop_duplicates()); running the
full checklist end to end is linear in the number of columns scanned, dominated by whichever
step sorts.

Edge cases

Keys that are numerically equal but different dtype (1 vs '1') compare unequal in a merge
even though they print the same; leading/trailing whitespace or invisible Unicode characters
that look identical when printed; null keys, which never match anything, including another
null; and duplicate keys in a table you assumed was a unique reference table.

Trade-offs and pitfalls

• Eyeballing printed values will not catch whitespace or invisible-character mismatches; you
  have to check programmatically.
• Not verifying key dtypes before merging is one of the most common causes here, because pandas
  silently returns zero matches rather than raising an error on a dtype mismatch.
• Skipping the indicator=True check first wastes time chasing deeper causes when the real
  issue (rows that never matched, versus rows that were genuinely dropped) is visible
  immediately.

Direct answer: Reduce the number of comparisons with cheap blocking (exact-match on a coarse key like zip code) so you never compare every record against every other record, then compute an approximate string-similarity score on the fields that actually vary (name, address) within each block, and use two thresholds instead of one: an auto-accept threshold for high-confidence matches and a lower "send to human review" band for everything else. Validate on a labeled sample before trusting any threshold in production.

Structured elaboration: approach

1. Block on a cheap exact key that correlates with true matches (zip code, or city + zip) to cut comparisons from O(n * m) to the sum of O(n_b * m_b) over much smaller blocks.
2. Score each candidate pair within a block using a token-based fuzzy string metric (handles reordering, abbreviations, and minor typos better than edit distance alone), on both name and address, combined into one weighted score.
3. Threshold in two tiers: auto-link above a high threshold, route a middle band to human review, and treat everything below the review floor as a non-match.
4. Validate on a labeled sample before trusting the thresholds, and keep auditing a random slice of auto-matches after launch.

Worked example (verified, pandas 3.0.3, rapidfuzz 3.14.5):

import pandas as pd
from rapidfuzz import fuzz

left_df = pd.DataFrame({
    'customer_id': [1, 2, 3],
    'name': ['Jon Smith', 'Maria Garcia', 'Robert Lee'],
    'address': ['123 Main St Apt 4', '55 Oak Ave', '9 Elm Rd'],
    'zipcode': ['94110', '10001', '73301'],
})
right_df = pd.DataFrame({
    'customer_id': [101, 102, 103],
    'name': ['Jonathan Smith', 'Maria J Garcia', 'Rob Lee'],
    'address': ['123 Main Street, Apt 4', '55 Oak Avenue', '10 Elm Rd'],
    'zipcode': ['94110', '10001', '73301'],
})

def normalize_zip(z):
    return str(z).strip()[:5] if pd.notna(z) else None

left_df['zip5'] = left_df['zipcode'].apply(normalize_zip)
right_df['zip5'] = right_df['zipcode'].apply(normalize_zip)

left_blocks = left_df.dropna(subset=['zip5'])
right_blocks = right_df.dropna(subset=['zip5'])

candidates = left_blocks.merge(right_blocks, on='zip5', suffixes=('_L', '_R'))

def combined_score(row, w_name=0.6, w_addr=0.4):
    name_score = fuzz.token_set_ratio(row['name_L'], row['name_R'])   # 0-100
    addr_score = fuzz.token_set_ratio(row['address_L'], row['address_R'])
    return w_name * name_score + w_addr * addr_score

candidates['score'] = candidates.apply(combined_score, axis=1)

AUTO_MATCH = 90
REVIEW_LOW = 70
matches_auto = candidates[candidates['score'] >= AUTO_MATCH]
matches_review = candidates[(candidates['score'] >= REVIEW_LOW) & (candidates['score'] < AUTO_MATCH)]

On this fixture the measured scores were 83.09, 94.78, and 83.70, giving 1 auto-match and 2 review-band matches at the thresholds above, no false positives and no missed true matches on this sample.

Complexity: without blocking, comparing every left row to every right row is O(N x M). Blocking on zip code reduces that to the sum over blocks of O(n_b x m_b), which is close to O(N + M) when blocks are small relative to the full datasets. Each pairwise score itself is O(k) in string length. The dominant cost at scale is usually the number of candidate pairs a block produces, not the scoring function, so a bad blocking key (one that's too coarse, producing huge blocks) can defeat the whole strategy even with a fast scorer.

Edge cases and how to guard against false matches

• Missing or malformed zip codes: records that fail the blocking key never get compared at all, and are silently dropped from the candidate set. Add a fallback block (city + first 3 digits of phone, or a phonetic key like Soundex on the last name) for rows where the primary block key is null, and track how many records never entered any block.
• Multiple candidate matches for one record: detect many-to-many matches explicitly rather than silently keeping only the highest score, they usually indicate either a genuinely ambiguous case or a data quality problem worth surfacing.
• International or inconsistently formatted addresses: general token-similarity scoring degrades quickly across address formats from different countries, a dedicated address-standardization step before scoring (parsing into number/street/unit/city/postal components) improves both blocking and scoring accuracy.
• Precision/recall trade-off: raising the auto-match threshold reduces false positives (bad merges) but pushes more true matches into the review queue or below it entirely; choose the operating point from a labeled validation sample and the real cost of a false merge versus a missed one, not a default like 90.

Trade-offs and pitfalls: validating before you trust it

• Never ship auto-linking on an unlabeled sample. Hand-label a few hundred candidate pairs across the score range, plot precision against threshold, and pick the auto-accept cutoff from that curve rather than a round number.
• Log every decision (score, which fields drove it, timestamp, source) so a bad auto-merge can be audited and reversed, treat linkage as a reversible operation, not a one-way write.
• Periodically re-sample already-auto-matched pairs for human spot-checking, upstream data quality drifts (new abbreviation conventions, a new source system) can silently degrade precision after launch even if nothing in your code changed.
• Weighting name higher than address (or vice versa) is a modeling choice, not a fact, validate it against your actual labeled data instead of assuming one field is inherently more reliable.

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.