Alignment is
the whole library.

The inclusive .loc endpoint looks like an inconsistency and is not. With labels you often have no idea what the next label is, so df.loc['2024-01-01':'2024-01-31'] has to include the 31st. Under a half-open rule you would have to name the following day, which for irregular or timestamped indexes you may not know exists. Positional slicing has no such problem, so .iloc keeps Python's normal rule.

The problem, as it existed for a decade

df[df.price > 100]['flag'] = True       # chained: two separate operations

Python evaluates that left to right as two calls. First df.__getitem__(mask) produces a new object. Then __setitem__('flag', True) runs on that object, whatever it turned out to be.

Whether the intermediate was a view onto df or a fresh copy depended on internals: how the columns were grouped into blocks by dtype, whether the selection covered a whole block, what the mask happened to select. A view meant the write reached df. A copy meant the write went into a temporary that was discarded on the next line, so the assignment silently did nothing. Same code, and the outcome depended on the shape of your data.

SettingWithCopyWarning existed because pandas could not tell you which one had happened. It was a warning that said "this might not have worked", which is an unsatisfying thing for a library to have to say.

What pandas 3.0 did about it

pandas 3.0, released in January 2026, made Copy-on-Write the only mode. The rule is now a single sentence with no exceptions:

Three consequences follow, and they are all improvements:

• Chained assignment never works, rather than working unpredictably. It fails the same way every time, which means you find it immediately instead of in production.
• SettingWithCopyWarning is gone. It existed only to flag ambiguity, and there is no ambiguity left. Defensive .copy() calls added to silence it are now dead weight.
• It is usually faster. "As if a copy" is not "always a copy". pandas tracks references internally and defers the actual copy until someone writes. Reads share memory as before, and the copy happens only on the write that would have been ambiguous. That also removes a great deal of defensive copying pandas used to do just in case, which is why 3.0 tends to use less memory rather than more.

The correct form, which has not changed

# right: one operation, both axes named, no intermediate object
df.loc[df.price > 100, 'flag'] = True

# wrong under any version: two operations
df[df.price > 100]['flag'] = True

Why the single call works: df.loc[rows, cols] = value is one __setitem__ on df itself. There is no intermediate, so there is nothing to be a copy of. pandas knows the target and the selection at once and writes into the original directly.

agg collapses. transform broadcasts the group result back to every member row.

df.groupby('symbol')['px'].agg('mean')         # one row per symbol
df.groupby('symbol')['px'].transform('mean')   # one row per original row

# which is why transform is what you want for a group-relative feature:
g = df.groupby('symbol')['px']
df['demeaned'] = df['px'] - g.transform('mean')
df['zscore']   = (df['px'] - g.transform('mean')) / g.transform('std')

Assigning a transform result back is safe precisely because it carries the original index, so pandas aligns it row for row. Try the same with an agg result and you get NaN everywhere, because you are aligning a symbol-indexed Series against a row-indexed frame and almost nothing matches.

The aggregation forms worth knowing cold

# one function, several columns
df.groupby('symbol').agg({'px': 'mean', 'qty': 'sum'})

# named aggregation: flat column names, no MultiIndex to unpick
df.groupby('symbol').agg(
    avg_px=('px', 'mean'),
    total_qty=('qty', 'sum'),
    n_trades=('px', 'size'),
)

# several functions on one column
df.groupby('symbol')['px'].agg(['mean', 'std', 'count'])

Named aggregation is the form to reach for by default. It produces ordinary flat columns instead of a two-level column index, which saves you a droplevel or a rename on every downstream use.

The same computation, four ways

# 1. vectorised: one C loop over contiguous memory
df['notional'] = df['px'] * df['qty']

# 2. apply on a column: a Python call per element, no per-row container
df['notional'] = df['px'].apply(lambda p: p * 2)

# 3. apply across rows: a Series is built for every row
df['notional'] = df.apply(lambda r: r['px'] * r['qty'], axis=1)

# 4. iterrows: a Series per row, and you rebuild the column by hand
out = []
for idx, row in df.iterrows():
    out.append(row['px'] * row['qty'])
df['notional'] = out

Treat those as orders of magnitude rather than measurements. The point is the shape of the ladder and the reason for each step, not the numbers.

Why iterrows is pathological, specifically

Two separate problems, and the second is worse than the first.

One: it constructs a Series per row. A DataFrame is stored by column, so its memory holds one contiguous array per column. A row is not a thing that exists in memory. To hand you one, pandas has to reach into every column, pull out the element at that position, and assemble a brand new Series object with its own index built from the column names. That is an allocation and a full index construction for every row, and it is thrown away immediately afterwards.

Two: it destroys your dtypes. A Series holds one dtype. A row spanning an int column, a float column and a string column cannot be one dtype, so pandas upcasts everything to object. Your int64 becomes a boxed Python int and your float64 becomes a boxed float. So iterrows is slow and lossy: values can change type mid-loop, integers can silently become floats, and comparisons can behave differently from the same comparison on the column.

The escape ladder when a loop feels necessary

1. Can it be arithmetic on whole columns? Usually yes, including conditional logic via np.where or np.select.
2. Is it a group calculation? Then it is groupby plus agg or transform, not a loop over groups.
3. Does it depend on the previous row? Look for cumsum, cumprod, shift, diff, ffill, rolling, expanding. Most sequential logic is one of these in disguise.
4. Is it genuinely path dependent, like a state machine or an order book replay? Then drop to NumPy arrays with df['x'].to_numpy() and loop over those. You keep the loop and lose the pandas overhead, which is usually a 10 to 50 times improvement on its own. If it is still too slow, that loop is now in a shape numba or Cython can compile.

Two independent questions

Resampling puts each timestamp into a time bin. Two settings control the details, and they are genuinely independent of each other:

• closed asks: which end of the interval is inclusive? closed='left' means a tick exactly at 09:30:00 belongs to the bin starting at 09:30. closed='right' means it belongs to the previous bin.
• label asks: what timestamp do we put on the resulting row? label='left' stamps it with the bin's start, label='right' with its end.

The defaults are closed='left' and label='left' for most frequencies, which means a bar stamped 09:30 covers 09:30:00 up to but not including 09:31:00. The exceptions are the calendar-ish frequencies such as ME, W, QE and YE, which default to right-closed and right-labelled because a monthly period is naturally named by its final day.

bars = ticks['price'].resample('1min', label='left', closed='left').ohlc()
vol  = ticks['size'].resample('1min', label='left', closed='left').sum()
bars['volume'] = vol

.ohlc() gives you first, max, min and last per bin in one call, which is exactly a candlestick. Note that on a bin with no ticks, open, high, low and close are all NaN while a summed volume is 0, so an empty bar is detectable and you should decide deliberately whether to forward fill it or drop it.

Resample bin inspector

Nine ticks on a timeline, resampled into bins. Toggle closed and label and watch the boundary ticks change bin and the row stamps move. The two edge ticks that sit exactly on a boundary are the ones to watch.

s.rolling(20).mean()                    # first 19 values are NaN
s.rolling(20, min_periods=1).mean()     # no NaN, but early values use fewer points
s.rolling(20, min_periods=5).mean()     # NaN until 5 observations, then compute
s.rolling('5min').mean()                # time-based window, needs a DatetimeIndex

The default is min_periods=window, which is why a 20-period mean starts with 19 NaNs. That default is the honest one: a "20-day average" computed from 3 points is not a 20-day average, and silently returning one would let an unreliable early value flow into everything downstream.

Setting min_periods=1 removes the NaNs at the cost of the first few values being noisier than the rest, computed from fewer observations. Sometimes that is what you want, at a series start where you would otherwise throw away real data. Often it is a quiet way of pretending you have more history than you do. Setting it to something in between, say half the window, is usually the honest compromise.

Count-based against time-based windows

rolling(20) takes the last 20 rows. rolling('5min') takes every row within the last 5 minutes of wall clock time. On irregularly spaced data these are completely different, and the time-based version is almost always what you meant. Twenty ticks might span a second during the open and an hour at lunchtime, so a count-based window silently changes its time horizon with market activity.

s.shift(1)         # move values DOWN one row: each row now holds the previous value
s.shift(-1)        # move values UP one row: each row now holds the NEXT value
s.diff()          # s - s.shift(1)
s.pct_change()    # s.diff() / s.shift(1)

s.shift(freq='1D')   # shifts the INDEX by a duration, not the values by rows

Get the sign right by reading it as: a positive shift brings the past forward. After shift(1), row i holds what row i−1 held, so the row can see its own history. A negative shift brings the future backward, which is only ever correct for constructing a target variable, never for a feature.

The correct shape of a signal

# WRONG: today's return decides today's position
df['signal']   = (df['ret'] > 0).astype(int)
df['pnl']      = df['signal'] * df['ret']        # perfect. and impossible.

# RIGHT: yesterday's information decides today's position
df['signal']   = (df['ret'] > 0).astype(int).shift(1)
df['pnl']      = df['signal'] * df['ret']

The wrong version produces a beautiful upward-sloping equity curve, a Sharpe ratio in the double digits, and no money. It is the single most common error in backtesting because nothing about it looks wrong: no exception, no warning, and a result so good that it is tempting to believe.

The tell is the result itself. A backtest with a Sharpe over about 3 on daily data is far more likely to have lookahead than to have alpha. Treat an implausibly good result as a bug report rather than a discovery, and go looking for the shift you forgot.

Lookahead bias detector

A random price series and a trivially simple signal: go long when the return was positive. Slide the shift and watch a strategy that cannot lose become a strategy that does nothing, which is the honest answer.

The problem it solves

You have trades at irregular times and quotes at different irregular times. For each trade you want the quote that was standing at that instant. An ordinary merge on timestamp finds almost nothing, because a trade at 09:30:01.234567 has no quote at exactly that microsecond. What you want is not equality but "the most recent quote at or before this trade".

pd.merge_asof(
    trades.sort_values('time'),
    quotes.sort_values('time'),
    on='time',
    by='symbol',               # match within symbol first, exactly
    direction='backward',     # the default: last quote at or before the trade
    tolerance=pd.Timedelta('2s'),   # beyond this, leave it NaN rather than lie
)

merge_asof aligner

Quotes on the top track, trades on the bottom. Drag the slider to move the selected trade in time and watch which quote it matches. Switch direction to see why forward and nearest are lookahead, and tighten the tolerance to see matches drop to NaN rather than go stale.

Two operations that sound alike and are opposites

naive = pd.Timestamp('2024-03-10 09:30')          # a wall clock reading. no zone.

aware = naive.tz_localize('America/New_York')     # ATTACH a zone. the clock reading is unchanged.
utc   = aware.tz_convert('UTC')                    # TRANSLATE. the instant is unchanged, the reading moves.

• tz_localize takes a naive timestamp and asserts which zone that wall clock reading was taken in. The digits stay the same, the meaning becomes definite.
• tz_convert takes an aware timestamp and expresses the same physical instant in another zone. The digits change, the instant does not.

Calling tz_convert on a naive timestamp raises, because there is nothing to convert from. Calling tz_localize on an aware one raises for the same reason in reverse. If you see either error, you have the wrong function.

Why localize is the risky one

Converting is pure arithmetic and cannot fail. Localizing can, because wall clock time is not a bijection with real time on the two days a year that DST changes.

• The spring gap. Clocks jump from 02:00 to 03:00, so 02:30 never happened. Localizing it raises NonExistentTimeError unless you pass nonexistent='shift_forward' or similar.
• The autumn overlap. Clocks fall back, so 01:30 happens twice, an hour apart. Localizing it raises AmbiguousTimeError unless you say which one with ambiguous='infer' or an explicit boolean array.

The reason this works: UTC has no DST, so every day is exactly 24 hours and durations are simple subtraction. In a DST-observing zone, the difference between two wall clock readings is not necessarily the elapsed time between them, which quietly breaks any duration, any rolling window and any resample that straddles a transition.

# filter
df[df['px'] > 100]
df.loc[(df.px > 100) & (df.symbol == 'AAPL'), ['time', 'px']]
df.query('px > 100 and symbol == "AAPL"')      # readable for long conditions
df[df.symbol.isin(['AAPL', 'MSFT'])]              # membership, not a chain of ==
df[df.symbol.str.startswith('A')]                 # .str gives vectorised string ops

# group and aggregate
df.groupby('symbol').agg(px=('px', 'mean'), n=('px', 'size'))
df.groupby(['symbol', 'side'])['qty'].sum().unstack(fill_value=0)
df.pivot_table(index='date', columns='symbol', values='px', aggfunc='last')

# merge
trades.merge(ref, on='symbol', how='left', validate='many_to_one')

# missing data
df.isna().sum()                # the first thing to run on new data
df.dropna(subset=['px'])       # drop rows missing a specific column
df['px'].ffill(limit=5)       # carry forward, but not indefinitely
df['px'].fillna(df.px.median())
df['px'].interpolate(method='time')   # respects irregular spacing