pandas: Cleaning, Merging & Performance

Missing Data

# Detect missing values
df.isnull()                         # boolean DataFrame
df.isnull().sum()                   # count per column
df.isnull().sum() / len(df) * 100  # % missing per column

# Drop missing
df.dropna()                         # drop rows with ANY null
df.dropna(subset=['name', 'email']) # drop rows with null in specific columns
df.dropna(how='all')                # drop rows where ALL values are null
df.dropna(thresh=3)                 # keep rows with at least 3 non-null values
df.dropna(axis=1)                   # drop columns with any null

# Fill missing values
df.fillna(0)
df.fillna({'salary': df['salary'].median(), 'dept': 'Unknown'})
df['salary'].fillna(df['salary'].mean(), inplace=True)
df.ffill()                          # forward fill (propagate last valid)
df.bfill()                          # backward fill
df['salary'].interpolate(method='linear')

# Replace specific values
df.replace(-999, np.nan)
df.replace({'dept': {'Eng': 'Engineering', 'Des': 'Design'}})

Merging & Joining

# merge — like SQL JOIN
pd.merge(df_left, df_right, on='id', how='inner')   # inner, left, right, outer
pd.merge(df_left, df_right, left_on='user_id', right_on='id')  # different key names
pd.merge(df_left, df_right, on=['dept', 'level'])   # multiple keys
pd.merge(df_left, df_right, on='id', how='left', suffixes=('_left', '_right'))  # duplicate column names

# concat — stack DataFrames
pd.concat([df1, df2])               # stack rows (union)
pd.concat([df1, df2], ignore_index=True)  # reset index
pd.concat([df1, df2], axis=1)       # stack columns side by side

# join — index-based merge
df1.join(df2, how='left')
df1.join(df2, on='dept_id')        # join on a column to other's index

# Update values from another DataFrame
df1.update(df2)                     # overwrite non-null values from df2

# merge_asof — time-series merge (nearest key)
pd.merge_asof(events, prices, on='timestamp', direction='backward')

Reshaping

# melt — wide to long (unpivot)
df_long = pd.melt(df,
    id_vars=['name', 'dept'],          # columns to keep
    value_vars=['q1', 'q2', 'q3', 'q4'],
    var_name='quarter',
    value_name='revenue',
)

# pivot — long to wide
df_wide = df_long.pivot(index='name', columns='quarter', values='revenue')
df_wide = df_long.pivot_table(index='name', columns='quarter', values='revenue', aggfunc='sum')

# stack / unstack (multi-index)
df.stack()      # columns → index level
df.unstack()    # innermost index level → columns

# explode — one element per row from list column
df_exploded = df.explode('tags')

Performance Tips

• Use categorical dtype for low-cardinality string columns: df["dept"] = df["dept"].astype("category") — saves 5-10x memory.

• Downcast numeric types: pd.to_numeric(df["age"], downcast="integer") — int64 → int8 saves 8x.

• Avoid loops: use vectorized operations, .str, .dt, .apply with caution. Loops kill performance.

• Read only needed columns: pd.read_csv(usecols=[...]) — avoids loading unused data.

• Use read_parquet over read_csv for repeated analysis — 10-100x faster loading.

• eval() and query(): use pandas.eval("a + b") for large arithmetic — avoids creating intermediate arrays.

• inplace=True rarely helps performance and makes code harder to chain. Return new DataFrames instead.

• polars: if pandas is too slow, polars is a Rust-based DataFrame library that is 10-100x faster for most operations.