Python and Pandas for Data Analysis Questions

Direct answer

Set a DatetimeIndex (not a (user, timestamp) MultiIndex) for the rolling call itself, grouped by the user column, then join the result back onto a frame keyed by the full (user, timestamp) MultiIndex. Rolling with a calendar window like '7D' requires a DatetimeIndex; putting the datetime inside a MultiIndex and calling .groupby(level=0).rolling('7D') on the whole frame raises ValueError: window must be an integer 0 or greater, because pandas cannot resolve a calendar window against a MultiIndex level directly.

Approach

1. Sort by (user, timestamp). This is a hard requirement: rolling windows trust row order.
2. For the rolling computation only, index the frame by timestamp alone (a plain DatetimeIndex) and group by the user column.
3. Call .rolling('7D', min_periods=1) on the single value column and aggregate mean/std/min/max in one .agg([...]) call, avoiding four separate rolling passes.
4. Join the result back onto a frame indexed by the full (user, timestamp) MultiIndex, which matches the grouped rolling result's own (user, timestamp) index exactly, so timestamps that repeat across different users cannot fan out into duplicate rows during the join.

Worked example

import pandas as pd
pd.set_option("display.width", 200)
pd.set_option("display.max_columns", None)

df = pd.DataFrame({
    "user": ["a", "a", "a", "b", "b"],
    "timestamp": ["2024-01-01", "2024-01-03", "2024-01-06", "2024-01-01", "2024-01-02"],
    "value": [10.0, 20.0, 30.0, 5.0, 15.0],
})
df["timestamp"] = pd.to_datetime(df["timestamp"])
df = df.sort_values(["user", "timestamp"])

# time-based rolling needs a DatetimeIndex, so index by timestamp only for
# this call (grouped by the 'user' column, not by an index level)
by_time = df.set_index("timestamp")
rolling_obj = by_time.groupby("user")["value"].rolling("7D", closed="both", min_periods=1)
agg = rolling_obj.agg(["mean", "std", "min", "max"]).rename(columns={
    "mean": "value_7d_mean", "std": "value_7d_std", "min": "value_7d_min", "max": "value_7d_max",
})

# agg's index is already (user, timestamp), matching df's own MultiIndex, so
# no droplevel is needed and the join cannot duplicate rows even though
# 2024-01-01 repeats across both users
result = df.set_index(["user", "timestamp"]).join(agg).reset_index()
print(result)

Output (verified against pandas 3.0.3):

  user  timestamp  value  value_7d_mean  value_7d_std  value_7d_min  value_7d_max
0    a 2024-01-01   10.0           10.0           NaN          10.0          10.0
1    a 2024-01-03   20.0           15.0      7.071068          10.0          20.0
2    a 2024-01-06   30.0           20.0     10.000000          10.0          30.0
3    b 2024-01-01    5.0            5.0           NaN           5.0           5.0
4    b 2024-01-02   15.0           10.0      7.071068           5.0          15.0

User a's 01-06 row averages all three of their observations (all fall within a 7-day trailing window of 01-06): mean 20.0, std 10.0 over [10, 20, 30]. User b's rows never touch user a's despite both having a 2024-01-01 row, because the join matches on the full (user, timestamp) pair.

Indexing requirements this approach depends on

• A calendar-based .rolling('7D') call requires the object it is called on to have a DatetimeIndex (directly, or accessed through groupby(...)[col].rolling(...) where the frame being grouped has one). A MultiIndex containing a datetime level is not sufficient by itself.
• Data must be sorted by (user, timestamp) before rolling; an out-of-order timestamp within a user silently produces a wrong window rather than raising.
• Because the grouped rolling result's index is (user, timestamp), whatever you join it back onto must use that exact same MultiIndex (not a de-duplicated or level-dropped one) to avoid an accidental many-to-many join when timestamps repeat across groups.

Complexity

O(n log n) for the initial sort, then O(n) for the grouped rolling pass, since each user's window work is bounded by that user's own row count. Memory: the single .agg(['mean','std','min','max']) call computes all four statistics from one pass over each window rather than four separate rolling objects, avoiding four redundant intermediate Series; the final join adds one more full-size copy for the merged result (pandas 3.0's Copy-on-Write means the original df is never mutated by any of this).

Edge cases

• Duplicate timestamps within the same user: both rows fall in each other's windows correctly; std on a window of size 1 is NaN (there is no variance with a single observation), which is expected, not a bug.
• Duplicate timestamps across different users (as in the example, both users have a 2024-01-01 row): this is exactly the case that breaks a naive droplevel-then-join approach into a row-multiplying join; keeping the full MultiIndex on both sides avoids it.
• A user with only one observation: the rolling stats for that single row are just that row's own value for mean/min/max and NaN for std.

Trade-offs and pitfalls

• Grouping and rolling on a MultiIndex level directly (df.set_index(['user','timestamp']).groupby(level=0).rolling('7D')) looks like the natural extension of the single-index pattern but raises ValueError in current pandas; the DatetimeIndex has to be the object's only index for the calendar-window logic to resolve, so keep the datetime as the sole index during the rolling call and reintroduce the grouping key as a join afterward rather than as an index level.

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: A MultiIndex, a hierarchical index whose single axis carries more than one level of labels (here, user_id and date together instead of one flat key), is worth setting up when the key is naturally hierarchical and you will repeatedly do fast, label-based access along one or both levels: point lookups on the full tuple, slicing all rows for a user_id, or groupby(level=...) aggregation. If you mostly do single-key point lookups and interface with tools that expect a flat schema, a single composite key column is simpler and just as fast in practice.

Structured elaboration

import pandas as pd
import numpy as np

df = pd.DataFrame({
    "user_id": np.repeat([101, 102, 103], 3),
    "date": pd.to_datetime(["2023-01-01", "2023-01-02", "2023-01-03"] * 3),
    "value": np.random.RandomState(0).randn(9),
})

# building a sorted MultiIndex is what makes lookups fast
df = df.set_index(["user_id", "date"]).sort_index()

# efficient tuple lookup
key = (102, pd.Timestamp("2023-01-02"))
row = df.loc[key]
# equivalent, explicit cross-section form:
row_xs = df.xs((102, "2023-01-02"))

Verified (pandas 3.0.3, seed 0): both df.loc[key] and df.xs(...) return the identical single-row Series for (102, '2023-01-02'), since the index was sorted with sort_index() after set_index.

Worked example, what "efficient" means concretely: once df.index.is_monotonic_increasing is True, .loc[key] resolves the outer level with a binary search (O(log n)) rather than a linear scan of every row, the same mechanism a database B-tree index gives you. Skipping sort_index() after set_index() does not break correctness, it just falls back to a slower scan and triggers a PerformanceWarning on partial-label access.

MultiIndex vs. a single composite key column

Recommendation: default to a MultiIndex when the hierarchy is used repeatedly for in-memory slicing or aggregation and the frame stays inside pandas. Reach for a flat composite key (or a surrogate integer id) when you're handing the data to something outside pandas, need simple joins with systems that don't understand hierarchical indexes, or the team's familiarity with .xs/level= is low enough that the ergonomics cost outweighs the performance gain.

Trade-offs and pitfalls: the most common mistake is choosing MultiIndex purely to "look up two things at once" without ever using level-wise slicing or groupby(level=...), in that case it adds API friction with no real benefit over a flat index plus an ordinary two-column filter. The second common mistake is building the index and never calling sort_index(), which silently degrades every partial lookup to a linear scan while still returning correct results, so the regression is easy to miss in code review.

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

Use pd.merge_asof with direction='backward' to attach each trade to the most recent price
at or before its own timestamp, passing by='symbol' to restrict matches to the same symbol.
The one hard requirement is that both DataFrames are sorted by the on timestamp column
itself, not by symbol first: merge_asof performs a single sorted-order scan, and by only
restricts which rows are eligible matches within that scan, it does not re-sort per group.

Structured elaboration

• Sort by the on key only. Sorting by ['symbol', 'timestamp'] groups rows by symbol
  first, which breaks global timestamp order the moment two symbols' events interleave in
  time, and merge_asof raises ValueError: left keys must be sorted (or silently returns
  wrong matches on older pandas that did not check). Sort by 'timestamp' alone; by='symbol'
  handles the per-symbol restriction internally.
• direction controls which side of "at or before" you want: 'backward' (the default)
  finds the last price at or before the trade; 'forward' finds the next one at or after;
  'nearest' finds whichever is closer in time.
• by= restricts matches to the same group, so an AAPL trade can never match an MSFT
  price, without you having to loop over symbols yourself.

Worked example

import pandas as pd

prices = pd.DataFrame({
    "timestamp": pd.to_datetime(["2025-01-01 09:30:00", "2025-01-01 09:31:00", "2025-01-01 09:30:00"]),
    "symbol": ["AAPL", "AAPL", "MSFT"],
    "price": [150.0, 150.5, 250.0],
})

trades = pd.DataFrame({
    "timestamp": pd.to_datetime(["2025-01-01 09:30:30", "2025-01-01 09:31:10", "2025-01-01 09:29:00"]),
    "symbol": ["AAPL", "AAPL", "MSFT"],
    "quantity": [10, 5, 20],
})

# sort by the 'on' key alone; do NOT sort by symbol first (see pitfalls)
prices = prices.sort_values("timestamp")
trades = trades.sort_values("timestamp")

merged = pd.merge_asof(
    trades,
    prices,
    on="timestamp",
    by="symbol",
    direction="backward",
    suffixes=("", "_price"),
)
print(merged)
#             timestamp symbol  quantity  price
# 0 2025-01-01 09:29:00   MSFT        20    NaN
# 1 2025-01-01 09:30:30   AAPL        10  150.0
# 2 2025-01-01 09:31:10   AAPL         5  150.5

The MSFT trade at 09:29 gets not-a-number (NaN) for price: its only MSFT price observation
is at 09:30, which is AFTER the trade, so direction='backward' correctly finds nothing.

Key points

• by='symbol' restricts matches without a manual per-symbol loop.
• direction and tolerance (a max allowed time gap) both shape which price counts as "close
  enough"; use tolerance if a match hours old should be treated as no match at all.

Complexity

O(n log n) for the sort, which dominates; the merge_asof scan itself is O(n + m) given
sorted input (n trades, m prices).

Edge cases

A trade earlier than any price observation for its symbol produces NaN in the price columns
(decide: drop, forward-fill from a seed price, or treat as invalid upstream); a symbol present
in one side but not the other never matches, by design; and duplicate timestamps within a
symbol on the price side, where direction='backward' takes the last one in sort order, so add
an explicit tiebreaker column if that ordering needs to be deterministic beyond timestamp.

Trade-offs and pitfalls

The pitfall worth naming explicitly, because it is easy to get backwards: sorting by
['symbol', 'timestamp'] looks like the natural thing to do when you are about to pass
by='symbol', but it is wrong. merge_asof needs the on column globally sorted across the
whole DataFrame; grouping by symbol first breaks that the moment two symbols' timestamps
interleave (an MSFT trade at 09:29 sorted after an AAPL trade at 09:31 under a
symbol-then-timestamp sort). Sort by timestamp alone and let by do the grouping.

• If you want only exact-timestamp matches instead of "most recent at or before", use a plain
  inner merge on both timestamp and symbol instead of merge_asof.
• Consider whether an unmatched trade (NaN price) should be dropped, flagged, or filled from
  a known opening price, since silently keeping NaN in downstream arithmetic will propagate
  it.