Advanced Pandas

.loc uses label-based indexing — you specify row and column labels. .iloc uses integer-position-based indexing — you specify zero-based integer positions. With .loc, the end of a slice is inclusive; with .iloc it is exclusive (like Python slicing). A critical gotcha: if the index is an integer RangeIndex, .loc and .iloc may look identical but behave differently when the index is non-contiguous. Always prefer explicit .loc or .iloc over chained [] indexing to avoid SettingWithCopyWarning and ambiguous behavior.

df.loc[0:5, 'revenue']   # labels 0 through 5 inclusive
df.iloc[0:5, 2]          # rows 0-4, 3rd column (exclusive end)

Chained indexing occurs when you chain two or more [] operations: df['col'][mask] = value. The problem is that the first [] may return either a view or a copy of the underlying data depending on internal pandas mechanics. If a copy is returned, the assignment silently fails to modify the original DataFrame, producing a SettingWithCopyWarning. The fix is to always use a single .loc or .iloc call: df.loc[mask, 'col'] = value. This is guaranteed to modify the original DataFrame in place. In pandas 2.0+, Copy-on-Write semantics make this even more important to understand.

# Dangerous - may not modify df
df['revenue'][df['region']=='West'] = 0
# Safe
df.loc[df['region']=='West', 'revenue'] = 0

Copy-on-Write, introduced as default in pandas 3.0 (opt-in from 2.0 with pd.options.mode.copy_on_write = True), ensures that any DataFrame or Series derived from another is always a copy logically, and actual copying is deferred until a write occurs. This eliminates the view-vs-copy ambiguity that caused SettingWithCopyWarning. Under CoW, chained indexing never silently fails — it raises an error or creates an independent copy. CoW also enables lazy copying for memory efficiency: slices share memory until mutated. Code relying on in-place mutation of slices must be rewritten to use .loc-based assignment on the original DataFrame.

import pandas as pd
pd.options.mode.copy_on_write = True  # pandas 2.0
# Now chained indexing won't silently modify df

GroupBy implements the split-apply-combine paradigm: split the DataFrame into groups based on one or more keys, apply a function to each group independently, then combine the results into a new DataFrame. The apply function can be an aggregation (reduces each group to a scalar), a transformation (returns a same-shape group), or a filter (drops groups not meeting a condition). Custom aggregations via agg({'col': ['mean', 'std']}) or named aggregations (agg(avg=('col','mean'))) are preferred over apply for performance because they use optimized C-level paths. apply with a Python lambda is flexible but slow for large DataFrames.

df.groupby('region').agg(
    avg_revenue=('revenue', 'mean'),
    total=('revenue', 'sum'),
    count=('id', 'count')
)

agg reduces each group to one or more scalar values, changing the shape of the output (fewer rows). transform applies a function to each group but returns a Series with the same index as the original DataFrame — one value per original row — enabling broadcast of group-level statistics back to individual rows without a merge. A classic use case: compute z-scores within groups with transform('mean') and transform('std'), or add a column with the group total alongside individual rows. transform must return a scalar or a same-length array per group. apply is more flexible but does not guarantee these constraints.

df['group_mean'] = df.groupby('region')['revenue'].transform('mean')
df['z_score'] = (df['revenue'] - df['group_mean']) / df.groupby('region')['revenue'].transform('std')

df['col'].rolling(window=n) creates a rolling window object. Methods like .mean(), .sum(), .std(), .apply(func) compute statistics over the last n observations. Key parameters: min_periods (minimum non-NaN observations required), center (center the window rather than trailing), and win_type (window weighting function such as gaussian). For time-based windows, pass a timedelta string to window (e.g., '7D') when the index is a DatetimeIndex. expanding() creates a cumulative window from the first row to the current. ewm() computes exponentially weighted statistics with decay parameter alpha or span.

df['7d_avg'] = df['revenue'].rolling(window=7, min_periods=1).mean()
df['cumsum'] = df['revenue'].expanding().sum()
df['ema'] = df['revenue'].ewm(span=7).mean()

A MultiIndex (hierarchical index) has multiple levels of row or column labels, enabling representation of higher-dimensional data in a 2D structure. Created via pd.MultiIndex.from_tuples(), groupby with multiple keys, or pivot operations. Access data with .loc[(level0_val, level1_val)] or .xs(key, level='level_name'). stack() converts innermost column level to row index; unstack() does the opposite. swaplevel() and sortlevel() reorder index levels. Groupby on a MultiIndex uses level= parameter. MultiIndex operations are powerful for panel data, time-series cross-sections, and multi-dimensional aggregations, but complexity grows quickly — flatten with reset_index() when possible.

df = df.groupby(['region','product'])['revenue'].sum().unstack()
df.loc['West']          # all products in West
df.xs('Widget', level='product', axis=1)

Pandas defaults often waste memory: int64 for small integers, float64 for all floats, object for all strings. Optimizations: use int8/int16/int32 for bounded integers, float32 for ML features where full precision is unnecessary, and category dtype for low-cardinality string columns (encoding as integers internally). pd.read_csv() accepts dtype= and parse_dates= to avoid post-load conversion. Sparse arrays handle data that is mostly zero or NaN. The memory_usage(deep=True) method reveals true memory consumption. For very large datasets, chunked reading with chunksize= parameter processes files incrementally, and PyArrow-backed nullable dtypes (pd.ArrowDtype) reduce overhead further in pandas 2.0+.

df = pd.read_csv('data.csv', dtype={
    'region': 'category',
    'count': 'int32',
    'score': 'float32'
})

pd.merge() provides full SQL-JOIN-like functionality: join on any column(s) using on=, left_on=, right_on=, or using the index via left_index=, right_index=. Supports inner, left, right, and outer join types. It is the most flexible merging operation. df.join() is a convenience wrapper that by default joins on the index, making it concise for index-based merges. It passes through to pd.merge internally. pd.concat() stacks DataFrames along rows or columns without matching keys. Key pitfall with merge: many-to-many joins create a Cartesian product of matching rows, which can silently explode row counts. Always verify output shape after merges.

result = pd.merge(orders, customers,
                  left_on='customer_id',
                  right_on='id',
                  how='left',
                  validate='many_to_one')

pd.eval() and df.query() parse and evaluate expression strings using the numexpr engine, which operates on NumPy arrays in chunks without creating large intermediate Python objects. For DataFrames with millions of rows and arithmetic expressions, this can be 2-10x faster than equivalent Python code and use less memory. df.query('revenue > 1000 and region == "West"') is more readable than boolean indexing for complex conditions. Limitations: not all Python operators are supported, function calls are limited, and for small DataFrames the overhead is not worth it. Local variable references use @ prefix inside query strings.

# Faster for large DataFrames
result = df.query('revenue > @threshold and region == "West"')
df.eval('profit = revenue - cost', inplace=True)

The performance hierarchy from fastest to slowest: vectorized NumPy/pandas operations > df.apply() with built-in reductions > apply() with Python lambdas > iterrows() (never use for large DataFrames). Vectorized operations use SIMD and avoid Python interpreter overhead. For row-wise operations, prefer apply(func, axis=1) over iterrows() — it is still Python-level but avoids index reconstruction overhead per row. For truly custom logic, consider Cython, Numba JIT (@numba.njit), or converting to NumPy arrays and operating there. pipe() enables method chaining with custom transformation functions for clean pipeline composition.

# Slow
for idx, row in df.iterrows(): ...
# Better
df.apply(func, axis=1)
# Best
np.where(df['col'] > 0, df['a'], df['b'])

category dtype stores a column as an integer code array plus a lookup array of unique categories, similar to a dictionary encoding. For low-cardinality string columns (e.g., country, status, region with < 1000 unique values), this can reduce memory by 90% compared to object dtype. Groupby and value_counts() on categorical columns are also faster because operations work on integer codes. Ordered categories enable comparison operators (<, >) and sorted displays. Pitfall: performing string operations on category dtype (like str.contains) is slower because each operation must go through the category map. Use category for join keys, groupby keys, and label columns.

df['status'] = df['status'].astype('category')
df['priority'] = pd.Categorical(df['priority'],
    categories=['low','medium','high'], ordered=True)
df[df['priority'] > 'low']

pipe(func, *args, **kwargs) passes the DataFrame as the first argument to func, enabling method chaining with custom functions that don't exist as DataFrame methods. This keeps the entire transformation pipeline as a single readable chain rather than intermediate variable assignments. It is inspired by the Unix pipe and R's magrittr %>% operator. Useful for: applying custom preprocessing functions, integrating scikit-learn transformers in a pandas-native way, and building readable ETL pipelines. The function receives and must return a DataFrame (or Series). pipe() makes code more testable because each step is a pure function.

def remove_outliers(df, col, z=3):
    mu, sigma = df[col].mean(), df[col].std()
    return df[df[col].between(mu - z*sigma, mu + z*sigma)]

result = (df
  .pipe(remove_outliers, 'revenue')
  .pipe(remove_outliers, 'cost')
  .groupby('region')['revenue'].mean())

df.resample(rule) groups time series data by a fixed frequency specified as a DateOffset alias (e.g., 'D' for daily, 'W' for weekly, 'ME' for month-end, 'QS' for quarter-start). After resampling, apply aggregation: .sum(), .mean(), .ohlc() for OHLC candlestick data, or .agg(). The closed and label parameters control whether the left or right bin edge is included and which edge labels the bin. The origin parameter aligns bin boundaries. Upsampling (to higher frequency) requires interpolation: .interpolate(), .ffill(), or .bfill(). The index must be a DatetimeIndex for resample to work.

# Downsampling to weekly mean
daily_df.resample('W').mean()
# Monthly OHLC
price_series.resample('ME').ohlc()

pd.cut(x, bins) divides data into equal-width bins defined by explicit bin boundaries or a number of equal-width intervals. pd.qcut(x, q) divides data into quantile-based bins so each bin contains approximately equal numbers of observations. Use pd.cut() when the bin boundaries are meaningful (e.g., age groups 0-18, 18-65, 65+) or domain-defined. Use pd.qcut() for rank-based grouping where equal population per bin is desired (e.g., revenue quartiles). Both return Categorical Series. The labels parameter assigns custom names. include_lowest=True in pd.cut() ensures the minimum value is included in the first bin.

df['age_group'] = pd.cut(df['age'], bins=[0,18,65,120], labels=['young','adult','senior'])
df['revenue_quartile'] = pd.qcut(df['revenue'], q=4, labels=['Q1','Q2','Q3','Q4'])

Key strategies: dropna() removes rows/columns with NaN (thresh= parameter requires minimum non-NaN count). fillna(value) fills with a scalar, dict (per-column), or forward/backfill strategy (method='ffill'/'bfill'). interpolate() fills by linear or polynomial interpolation — best for time series. isna().sum() diagnoses missing counts per column. For large DataFrames, avoid chained fillna calls; compute the fill dictionary once and apply in one pass. When loading data, na_values= in read_csv() maps custom missing value sentinels. Imputation logic for ML pipelines should be fit only on training data (using sklearn SimpleImputer or IterativeImputer) to prevent data leakage.

df.fillna({
    'revenue': 0,
    'region': 'Unknown',
    'score': df['score'].median()
}, inplace=True)
df['price'].interpolate(method='linear', inplace=True)

melt() converts wide-format data (one row per subject, many columns for measurements) to long format (one row per subject-measurement pair). It unpivots specified value columns into key-value rows, controlled by id_vars (identifier columns) and value_vars (columns to unpivot). pivot_table() aggregates and converts long format to wide format, similar to a spreadsheet pivot: rows become the index, unique values of one column become column headers, and a specified aggregation (default mean) fills cells. pivot() is the non-aggregating version requiring unique row-column combinations. melt() is used to normalize data for tidy analysis; pivot_table() is used for cross-tabulation summaries.

# Wide to long
long = df.melt(id_vars=['id'], value_vars=['q1','q2','q3'],
               var_name='quarter', value_name='revenue')
# Long to wide
wide = long.pivot_table(index='id', columns='quarter',
                        values='revenue', aggfunc='sum')

Pandas stores timezone-naive datetimes as plain integers (nanoseconds since epoch). Timezone-aware datetimes store the UTC offset metadata alongside the values. dt.tz_localize(tz) attaches a timezone to a naive datetime (assigning interpretation), while dt.tz_convert(tz) converts an already-localized datetime to a different timezone (shifting the values). Mixing naive and aware datetimes raises TypeError. When reading from databases or APIs, always normalize to UTC immediately (tz_convert('UTC')), and convert to the user's local timezone only at display time. pd.Timestamp and pd.DatetimeTZDtype back these operations. pytz and dateutil both work as timezone providers.

df['ts'] = pd.to_datetime(df['ts_str'])
df['ts_utc'] = df['ts'].dt.tz_localize('UTC')
df['ts_east'] = df['ts_utc'].dt.tz_convert('US/Eastern')

df.style returns a Styler object that applies CSS formatting to DataFrame display in Jupyter notebooks. Methods include .background_gradient() for heatmap-style coloring, .highlight_max()/.highlight_min() to flag extremes, .bar() to add in-cell bar charts, .format('{:.2%}') for custom number formatting, and .apply()/.applymap() for conditional formatting with custom logic. Styler is purely presentational — it does not modify the underlying data. It is invaluable for exploratory data analysis, model performance tables, and quick reporting dashboards in notebooks. Styling can be exported to Excel with to_excel() preserving formats via openpyxl.

df.style\
  .background_gradient(cmap='RdYlGn', subset='accuracy')\
  .format({'accuracy': '{:.1%}', 'latency_ms': '{:.0f}'})\
  .highlight_max(subset='f1', color='lightgreen')

pandas 2.0+ supports ArrowDtype backed by PyArrow arrays, enabling zero-copy data sharing with Arrow-native systems (Polars, DuckDB, Spark, Parquet). Use dtype_backend='pyarrow' in read_parquet(), read_csv(), etc. ArrowDtype handles nullability natively (unlike NumPy which uses float NaN for integer nulls). Converting to/from Arrow: pa.Table.from_pandas(df) and pa_table.to_pandas(). Zero-copy means no memory allocation for conversion — both pandas and the Arrow consumer point to the same memory buffer. This is critical for large-scale data pipelines where copy overhead between systems was a bottleneck.

import pyarrow as pa
df = pd.read_parquet('data.parquet', dtype_backend='pyarrow')
arrow_table = pa.Table.from_pandas(df)  # zero-copy
df2 = arrow_table.to_pandas(zero_copy_only=True)

Series.map() applies a function or dictionary element-wise to a Series — useful for value substitution and simple element transformations. In pandas 2.1+, DataFrame.applymap() was renamed to DataFrame.map() for consistency. DataFrame.apply() applies a function along an axis (rows or columns), receiving each column (axis=0) or row (axis=1) as a Series. apply() is far more flexible but slower than vectorized operations. applymap()/DataFrame.map() applies a function element-wise to every cell in a DataFrame. For performance, always prefer vectorized operations over apply, and apply over iterrows for row-wise work.

# Series.map for substitution
df['status'] = df['code'].map({1:'active', 0:'inactive'})
# DataFrame.map for element-wise formatting
df.map(lambda x: round(x,2) if isinstance(x, float) else x)

pd.concat() with verify_integrity=True raises a ValueError if the resulting index contains duplicates, making it a safety check when concatenating DataFrames that should produce unique indexes. Without it, duplicate indexes silently pass through, causing subtle bugs in subsequent .loc lookups (which return multiple rows for a single label) and groupby operations. This is especially important when concatenating batch-processed results or combining outputs from parallel workers that each generate a RangeIndex. Alternatively, ignore_index=True resets the index to a fresh sequential RangeIndex after concatenation, avoiding duplicates altogether.

result = pd.concat([df1, df2, df3],
                   verify_integrity=True,
                   ignore_index=True)

df.duplicated() returns a boolean Series where True marks duplicate rows (by default, all columns, keeping first occurrence). Parameters: subset= for specific columns, keep= ('first','last',False — mark all duplicates). df.drop_duplicates(subset=, keep=) removes them. For uniqueness constraints on key columns, duplicated(subset='id').any() validates data integrity. After a merge, unexpected row count increases often indicate unexpected duplicates in join keys — always validate='one_to_one' or 'many_to_one' in pd.merge() to catch this. value_counts() on suspected key columns quickly identifies cardinality violations.

# Find duplicates
dupes = df[df.duplicated(subset='user_id', keep=False)]
# Remove keeping last
df_clean = df.drop_duplicates(subset='user_id', keep='last')
print(f'Removed {len(df)-len(df_clean)} duplicates')

Extension arrays are a pandas API for custom array types stored in DataFrame columns, decoupled from NumPy's type system. They were introduced because NumPy lacks nullable integer, boolean, and string types (NaN forces float promotion for integer columns). Built-in extension arrays include IntegerArray (pd.Int64Dtype()), BooleanArray, StringDtype, DatetimeTZDtype, CategoricalDtype, and ArrowDtype. Third-party libraries (GeoPandas, CyberPandas) use the extension array API for domain-specific types. Nullable integer dtype pd.Int64Dtype() allows integers with NaN without promoting to float64, fixing a long-standing pain point in data engineering workflows.

df['count'] = df['count'].astype(pd.Int64Dtype())  # nullable int
df['flag'] = df['flag'].astype(pd.BooleanDtype())  # nullable bool
df['name'] = df['name'].astype(pd.StringDtype())   # StringDtype

Parquet's columnar format allows reading only the needed columns without deserializing the rest, making column pruning free. pd.read_parquet(path, columns=['col1','col2']) reads only specified columns. Row filtering at read time (predicate pushdown) requires the pyarrow or fastparquet engine: the filters parameter accepts a list-of-lists of predicates that are pushed into the Parquet metadata, skipping irrelevant row groups. This is dramatically faster than reading the full file and then filtering in pandas. For very large datasets, consider DuckDB's direct Parquet querying (SELECT ... FROM 'file.parquet') which natively supports both column pruning and predicate pushdown.

df = pd.read_parquet(
    'large_dataset.parquet',
    columns=['user_id', 'revenue', 'region'],
    filters=[('region', '==', 'West'), ('revenue', '>', 1000)]
)

A systematic profiling workflow: (1) df.shape and df.dtypes — understand dimensions and types. (2) df.describe(include='all') — distribution statistics for numeric and categorical columns. (3) df.isna().sum() / df.isna().mean() — missing value rates per column. (4) df.nunique() — cardinality per column. (5) df.duplicated().sum() — duplicate row count. (6) For numeric columns, check skewness with df.skew() and outliers via IQR or z-scores. Libraries like ydata-profiling (formerly pandas-profiling) automate this into a comprehensive HTML report. This baseline assessment informs preprocessing decisions before modeling.

summary = pd.DataFrame({
    'missing_pct': df.isna().mean(),
    'unique': df.nunique(),
    'dtype': df.dtypes
})
print(summary.sort_values('missing_pct', ascending=False))

When grouping on a column of category dtype, groupby by default creates groups for all categories in the category definition, even if no rows belong to that category (observed=False). This can produce rows with NaN or zero counts for categories not present in the data — useful for ensuring all categories appear in reports. Setting observed=True restricts groups to only categories actually present in the data, avoiding phantom groups. In pandas 2.0, the default changed and a FutureWarning was raised; pandas 2.2 made observed=True the default. Always explicitly set observed= to avoid behavior changes across pandas versions.

df['region'] = df['region'].astype('category')
# Only groups with data
df.groupby('region', observed=True)['revenue'].sum()

df.at[row_label, col_label] and df.iat[row_pos, col_pos] provide fast single-cell access — they are optimized for scalar lookup and assignment, bypassing the overhead of general .loc/.iloc machinery. They are substantially faster when you need to read or write a single value in a tight loop (though loops should generally be avoided). at uses labels; iat uses integer positions. They are equivalent to df.loc[r,c] and df.iloc[r,c] semantically but faster for scalar access. They do not support slicing or boolean indexing. For vectorized operations, standard .loc is preferred; for unavoidable single-cell writes in loops, prefer .at or .iat.

# Fast single cell access
value = df.at[idx, 'revenue']
df.at[idx, 'status'] = 'processed'

pd.arrays.SparseArray and the SparseDtype store only non-default (non-fill) values in memory, making them efficient for data that is mostly zeros or NaN — common in recommendation system matrices, one-hot encoded features, and NLP document-term matrices. A DataFrame column can be declared as sparse via df['col'].astype(pd.SparseDtype(float, fill_value=0)). df.sparse.to_coo() converts a DataFrame to a scipy.sparse.coo_matrix for use with scikit-learn estimators that accept sparse input. Sparse arrays consume memory proportional to non-zero density, offering dramatic savings for high-sparsity data (99%+ zeros).

df['tfidf'] = df['tfidf'].astype(pd.SparseDtype(float, fill_value=0.0))
coo = df.sparse.to_coo()  # scipy sparse matrix for sklearn

pd.merge(..., validate=) enforces cardinality constraints and raises MergeError if violated: 'one_to_one' — both DataFrames have unique keys; 'one_to_many' — left keys are unique, right may repeat; 'many_to_one' — right keys are unique, left may repeat; 'many_to_many' — no constraint (default). Using validate is a defensive programming practice that prevents silent data quality bugs where unexpected duplicates in join keys create a Cartesian explosion of rows, inflating metrics and corrupting analyses. Always set validate in production ETL code where data assumptions about key uniqueness must be enforced.

orders_with_customers = pd.merge(
    orders, customers,
    left_on='customer_id', right_on='id',
    how='left',
    validate='many_to_one'
)

assign() returns a new DataFrame with additional or modified columns, enabling method chaining without intermediate variables or inplace mutation. Multiple columns can be added in one call, and later assignments in the same call can reference columns created earlier (in Python 3.7+ with ordered dicts). This functional style makes transformation pipelines readable and testable, as each step produces a new immutable DataFrame. Particularly valuable when combined with pipe() for complex multi-step transformations. Unlike direct assignment (df['new_col'] = ...), assign() works correctly under Copy-on-Write semantics and in method chains.

result = (df
  .assign(margin = lambda x: (x['revenue'] - x['cost']) / x['revenue'],
          margin_pct = lambda x: x['margin'] * 100)
  .query('margin > 0.2')
  .groupby('region')['margin_pct'].mean()
)

Both convert wide to long format. melt() is for DataFrames where column names represent values (e.g., year columns 2020, 2021, 2022 become a 'year' column). It is flexible in specifying which columns to unpivot via value_vars. stack() operates on MultiIndex column DataFrames, moving the innermost column level to become the innermost row index level, producing a MultiIndex Series or DataFrame. stack() is typically used after pivot_table() or unstack() to restore the shape. melt() is generally more suitable for tidy data conversion of regular DataFrames; stack()/unstack() is more natural for hierarchical index manipulation.

# stack: column level -> row index level
long = df.stack(level='metric')
# melt: named columns -> value_vars rows
long = df.melt(id_vars=['id','region'], var_name='metric', value_name='value')

df['col'].quantile(q) computes a single quantile where q is between 0 and 1. For multiple quantiles at once, pass a list: df['col'].quantile([0.25, 0.5, 0.75]). df.describe() computes the 25th, 50th, and 75th percentiles by default; custom percentiles via describe(percentiles=[.1,.9]). For groupwise percentiles, use groupby.quantile(): df.groupby('region')['revenue'].quantile(0.9). The method parameter controls interpolation (linear, midpoint, nearest). For windowed percentile calculations, use a rolling().apply(np.percentile) — though this is slow; the bottleneck is Python-level apply on each window.

# Group-wise 90th percentile
p90 = df.groupby('region')['revenue'].quantile(0.9)
# Multiple quantiles at once
df['revenue'].quantile([0.1, 0.25, 0.5, 0.75, 0.9])

inplace=True modifies the DataFrame in place and returns None, while reassignment (df = df.method()) creates a new object and rebinds the variable. With Copy-on-Write in pandas 2.0+, inplace=True may not always be more memory-efficient since CoW may still create a copy internally. The pandas core team discourages inplace=True because it prevents method chaining, makes code harder to parallelize, and can cause subtle bugs (functions receiving a DataFrame via argument won't see the mutation). The guidance as of pandas 2.0 is to prefer reassignment and method chaining over inplace modifications for cleaner, more predictable code.

# Avoid
df.drop(columns=['tmp'], inplace=True)
# Prefer
df = df.drop(columns=['tmp'])

Calling pd.concat() inside a loop (df = pd.concat([df, new_df])) is O(n^2) in total rows because each call copies all existing data into a new buffer. The correct pattern is to collect all DataFrames in a list first, then call pd.concat(list_of_dfs) once. This is O(n) in total rows because pandas pre-allocates the output buffer knowing total size. An equivalent anti-pattern is df.append() in a loop (deprecated in pandas 2.0). For very large concatenations, writing intermediate results to Parquet and reading once is even more memory-efficient. The list-then-concat pattern is the most common performance fix for data collection pipelines.

# Slow O(n^2)
result = pd.DataFrame()
for chunk in chunks:
    result = pd.concat([result, chunk])

# Fast O(n)
all_chunks = []
for chunk in chunks:
    all_chunks.append(chunk)
result = pd.concat(all_chunks, ignore_index=True)

np.nan is a floating-point value (IEEE 754 NaN), so using it as a missing value in integer columns forces promotion to float64. pd.NA is pandas' native NA scalar introduced with extension arrays, designed to propagate through operations correctly for any dtype including integer, boolean, and string. pd.NA follows three-valued logic for booleans (pd.NA | True == True, pd.NA & False == False, pd.NA == pd.NA is pd.NA). np.nan comparisons always return False (np.nan == np.nan is False). Use pd.isna() to detect either type. Nullable integer columns (Int64Dtype) use pd.NA; float columns still use np.nan.

import pandas as pd, numpy as np
print(np.nan == np.nan)   # False
print(pd.NA == pd.NA)     # <NA> (not True or False)
print(pd.isna(pd.NA))     # True

The accessor API (registered via @pd.api.extensions.register_dataframe_accessor('namespace')) allows attaching custom methods to DataFrame, Series, or Index objects under a namespace, enabling domain-specific DataFrame APIs. The accessor class receives the underlying object in __init__, where validation logic can reject unsupported data. For example, a geo accessor for lat/lon DataFrames, or a finance accessor for OHLCV data. Accessors appear as df.namespace.method(). This pattern is used by libraries like GeoPandas (df.geo), Pandas-Flavor, and PyJanitor (df.janitor). It extends pandas without subclassing, following the composition over inheritance principle.

@pd.api.extensions.register_dataframe_accessor('finance')
class FinanceAccessor:
    def __init__(self, df):
        if 'close' not in df.columns:
            raise AttributeError('Missing close column')
        self._df = df
    def returns(self):
        return self._df['close'].pct_change()

df.finance.returns()

Boolean indexing (df[df['col'] > 0]) creates a temporary boolean array and applies it as a mask — for large DataFrames this allocates an intermediate array of length N. df.query('col > 0') with numexpr backend avoids this by operating directly on the memory in chunks, using SIMD instructions and reducing peak memory. The performance advantage appears at large scale (millions of rows) and complex expressions. query() is also more readable for compound conditions. For small DataFrames (<100K rows), boolean indexing is often faster due to query parsing overhead. The @ operator in query strings references local Python variables.

threshold = 1000
# Boolean indexing
df[df['revenue'] > threshold]
# query() with local variable
df.query('revenue > @threshold and region != "Unknown"')

object dtype stores Python string objects as pointers, meaning each element is a separate Python heap allocation. For a DataFrame with 1M string rows, this means 1M Python objects with header overhead (~50 bytes each), high memory usage, slow iteration, and no possibility of C-level string operations. StringDtype (backed by Python or Arrow strings) is more memory-efficient, enables nullable string semantics, and enables faster bulk operations. Category dtype is even better for low-cardinality strings. When reading from CSV, pandas defaults to object for strings; always explicitly cast to StringDtype or category. Arrow-backed strings (pd.StringDtype(storage='pyarrow')) are most efficient.

df['name'] = df['name'].astype(pd.StringDtype())
# Or Arrow-backed for maximum efficiency
df['name'] = df['name'].astype(pd.StringDtype(storage='pyarrow'))

pd.crosstab(index, columns) computes a frequency table of how often combinations of values co-occur, similar to pivot_table with aggfunc='count'. normalize= parameter computes proportions: 'index' normalizes per row, 'columns' per column, 'all' over the entire table. margins=True adds row and column totals. A values= parameter with aggfunc= enables aggregation beyond counts (making it equivalent to pivot_table). It is especially useful for chi-squared independence testing preprocessing, confusion matrix display, and exploring categorical variable relationships. The output is a DataFrame with MultiIndex columns when multiple column variables are passed.

ct = pd.crosstab(df['region'], df['product'],
                 margins=True,
                 normalize='index')

sort_values(by=) sorts the DataFrame by the values in one or more columns (ascending/descending per column via ascending= list). sort_index() sorts by the index labels. Both have a stable sort by default (kind='mergesort' for sort_values), preserving original order for equal keys. sort_index() is O(n log n) on the label values. After a groupby, merge, or concat, the index may be unsorted, causing .loc slices to return unexpected results — always sort_index() if you rely on label-based slicing. inplace=False returns a new sorted DataFrame; the original is unchanged.

# Sort by multiple columns
df.sort_values(['region', 'revenue'], ascending=[True, False])
# Sort index after concat
df.sort_index(inplace=True)

df.sample(n=100, random_state=42) draws n rows without replacement, with random_state seeding NumPy's RNG for reproducibility. frac=0.1 draws 10% of rows. replace=True enables sampling with replacement (bootstrapping). weights= provides row-level sampling probabilities proportional to a column of weights. For stratified sampling (ensuring proportional representation of each group), groupby and apply sample per group, then concat. random_state accepts an integer seed or np.random.Generator for fine-grained control. Reproducible sampling is essential for train/test splits and auditable experiments.

# Stratified sample
stratified = df.groupby('label', group_keys=False).apply(
    lambda x: x.sample(frac=0.2, random_state=42)
)

pd.option_context() is a context manager that temporarily changes pandas display or computation options within a with block, reverting them on exit. This avoids permanent option changes that affect the global state. Common uses: pd.option_context('display.max_rows', 100, 'display.float_format', '{:.2f}'.format) to inspect more rows in a notebook; 'mode.chained_assignment', None to suppress warnings during exploratory scripts; 'compute.use_bottleneck', False for debugging. Unlike pd.set_option() which persists globally, option_context is safe and idempotent. For production code, avoid suppressing warnings — fix the underlying cause instead.

with pd.option_context('display.max_rows', 200,
                        'display.float_format', '{:.4f}'.format):
    print(df.describe())

Before pandas 2.0, integer columns with missing values were automatically upcast to float64 (using NaN as sentinel), losing integer type information and changing semantics. Pandas 2.0+ with nullable integer types (Int8, Int16, Int32, Int64 — capital I) and pd.NA support integers with missing values without promotion. Operations on nullable integers propagate pd.NA correctly rather than producing NaN. Integer downcast behavior changed: pd.read_csv() no longer automatically downcasts integers, and explicit dtype specification is required. This breaking change in 2.0 requires updating code that relied on NaN-in-integer columns being float, especially arithmetic comparisons and type checks.

# Nullable integer column
df['count'] = pd.array([1, 2, None, 4], dtype=pd.Int32Dtype())
print(df['count'].dtype)  # Int32
print(df['count'].isna()) # [False, False, True, False]

The Series.str accessor provides vectorized string methods that apply element-wise without explicit Python loops: str.contains(), str.startswith(), str.split(), str.replace(), str.extract() (regex groups into columns), str.get_dummies() (one-hot encode delimited values). These are faster than apply(lambda x: x.method()) because they avoid Python function call overhead per element when backed by C extensions. str.contains() supports regex and na= parameter for NA handling. str.extractall() extracts all regex matches per element into a MultiIndex DataFrame. str accessor works on both object and StringDtype columns.

# Extract email domain
df['domain'] = df['email'].str.extract(r'@([\w.]+)')
# Multi-value get_dummies
df['tags'].str.get_dummies(sep=',')

pd.Grouper(key='datetime_col', freq='ME') creates a time-frequency grouper that can be passed to groupby alongside regular column groupers, enabling mixed time and categorical grouping. This is more flexible than resample(), which only supports single-column time indexing. pd.Grouper supports all DateOffset frequencies, offset aliases, and the closed/label parameters. Key use: groupby(['region', pd.Grouper(key='date', freq='W')]) computes weekly statistics per region in one step. origin='start_day', offset= parameters fine-tune bin alignment. pd.Grouper also works for non-time categorical grouping with level= for MultiIndex.

df.groupby(['region', pd.Grouper(key='date', freq='ME')]).agg(
    total_revenue=('revenue', 'sum'),
    order_count=('id', 'count')
)

melt() is general-purpose: it unpivots specified columns into key-value rows with full control over id_vars and value_vars. pd.wide_to_long(df, stubnames, i, j) is specialized for DataFrames with columns named by a stub followed by a suffix (e.g., sales_2020, sales_2021, sales_2022 — stub 'sales', suffix is the year). It automatically identifies these columns by pattern and reshapes them. wide_to_long handles multiple stub groups simultaneously, making it very compact for time-indexed wide tables. It is less flexible than melt() but handles common 'stub+suffix' naming conventions cleanly without manual column specification.

# Columns: id, sales_2021, sales_2022, cost_2021, cost_2022
long = pd.wide_to_long(df, stubnames=['sales','cost'],
                        i='id', j='year', suffix=r'\d+')

Schema drift — unexpected column additions, removals, or type changes — breaks rigid pipelines. Defensive patterns: use df.reindex(columns=expected_cols, fill_value=0) to ensure expected columns exist (missing ones filled, extra dropped). Validate dtypes with df.dtypes.to_dict() against an expected schema dict and raise ValueError on mismatch. Use pandas-compatible schema validators like pandera (pa.DataFrameSchema) which raise descriptive errors on constraint violations. Log df.shape and df.dtypes at each pipeline stage. For production ML feature pipelines, define a schema contract (expected columns, types, ranges) and test it in CI. This prevents silent data corruption reaching models.

import pandera as pa
schema = pa.DataFrameSchema({
    'revenue': pa.Column(float, pa.Check.ge(0)),
    'region': pa.Column(str, pa.Check.isin(['North','South','West','East']))
})
validated_df = schema.validate(df)

IntervalIndex stores intervals as an index type, enabling fast label-based lookups by value: df.loc[3.7] on an IntervalIndex returns the row whose interval contains 3.7. It is created via pd.IntervalIndex.from_breaks() or as the result of pd.cut(). It supports closed='left', 'right', or 'both' endpoint conventions. IntervalIndex enables efficient range-based groupby and nearest-bin lookups without linear scans. It is particularly useful for building lookup tables from binned histograms, rate tables (e.g., tax brackets), and feature engineering where category membership is defined by numeric ranges.

breaks = [0, 18, 65, 120]
idx = pd.IntervalIndex.from_breaks(breaks, closed='left')
df = pd.DataFrame({'rate': [0.1, 0.25, 0.35]}, index=idx)
print(df.loc[30])  # interval [18, 65) row

The standard numpy bool dtype maps missing values to False (NaN coerces to False), silently corrupting truth values. BooleanDtype (pd.BooleanDtype()) is a nullable boolean extension type that supports pd.NA as a distinct missing value, following three-valued logic: pd.NA | True == True, pd.NA & False == False, pd.NA == pd.NA is pd.NA. It prevents false-False confusion from NaN coercion. BooleanDtype columns can be created via astype(pd.BooleanDtype()) and are the output of comparison operations on nullable integer columns. Recommended whenever boolean columns may have missing values, which is common in real-world data quality scenarios.

df['is_active'] = df['is_active'].astype(pd.BooleanDtype())
print(df['is_active'].isna().sum())  # correctly counts missing booleans