Pandas Cheatsheet
Creating DataFrames
import pandas as pd
import numpy as np
pd.DataFrame({"a": [1, 2, 3], "b": [4, 5, 6]})
pd.DataFrame([[1, 2], [3, 4]], columns=["a", "b"])
pd.Series([1, 2, 3], name="values")
pd.Series({"a": 1, "b": 2})
pd.read_csv("file.csv")
pd.read_csv("file.csv", usecols=["a", "b"], dtype={"a": "int32"}, parse_dates=["date"])
pd.read_parquet("file.parquet")
pd.read_excel("file.xlsx", sheet_name="Sheet1")
pd.read_json("file.json")
pd.read_sql("SELECT * FROM table", con=engine)
df.to_csv("out.csv", index=False)
df.to_parquet("out.parquet")
df.to_excel("out.xlsx", index=False)
df.to_sql("table_name", con=engine, if_exists="replace")
Inspecting Data
df.head(); df.tail(); df.sample(5)
df.shape
df.info()
df.describe() # summary stats for numeric columns
df.describe(include="all") # include categorical too
df.dtypes
df.columns; df.index
df.isnull().sum() # nulls per column
df.nunique() # unique values per column
df.memory_usage(deep=True)
df.value_counts() # for a Series
df["col"].unique()
Selecting Data
df["col"] # single column -> Series
df[["a", "b"]] # multiple columns -> DataFrame
df.loc[0] # row by label
df.loc[0:3] # rows by label range (inclusive)
df.iloc[0] # row by position
df.iloc[0:3] # rows by position (exclusive end)
df.loc[0:3, ["a", "b"]] # rows + columns by label
df.iloc[0:3, 0:2] # rows + columns by position
df.at[0, "a"] # fast scalar access by label
df.iat[0, 0] # fast scalar access by position
df.loc[df["a"] > 5] # boolean filter
df.loc[(df["a"] > 5) & (df["b"] < 10)] # combine with & | ~ and parentheses
df.query("a > 5 and b < 10") # query syntax alternative
df[df["col"].isin(["x", "y"])]
df[df["col"].str.contains("pattern", na=False)]
df[df["col"].between(1, 10)]
Modifying Data
df["c"] = df["a"] + df["b"]
df["c"] = df["a"].apply(lambda x: x * 2)
df["c"] = np.where(df["a"] > 5, "high", "low")
df.assign(c=lambda d: d["a"] + d["b"]) # returns new df, chainable
df.rename(columns={"a": "alpha"})
df.rename(columns=str.upper)
df.drop(columns=["a"])
df.drop(index=[0, 1])
df.drop_duplicates()
df.drop_duplicates(subset=["a"], keep="first")
df["a"] = df["a"].astype("int32")
df["date"] = pd.to_datetime(df["date"])
df["num"] = pd.to_numeric(df["num"], errors="coerce") # bad values -> NaN
df.set_index("id")
df.reset_index(drop=True)
df.sort_values("a")
df.sort_values(["a", "b"], ascending=[True, False])
df.sort_index()
Missing Data
df.isnull(); df.notnull()
df.isnull().sum()
df.dropna() # drop rows with any null
df.dropna(subset=["a"]) # drop rows null in specific columns
df.dropna(how="all") # drop only fully-null rows
df.fillna(0)
df.fillna({"a": 0, "b": "unknown"}) # per-column fill
df["a"].fillna(df["a"].mean())
df.ffill(); df.bfill() # forward/backward fill
df.interpolate() # interpolate missing numeric values
GroupBy & Aggregation
df.groupby("category")["value"].sum()
df.groupby("category")["value"].agg(["sum", "mean", "count"])
df.groupby("category").agg(
total=("value", "sum"),
avg=("value", "mean"),
n=("value", "count"),
)
df.groupby(["cat1", "cat2"])["value"].sum() # multi-level group
df.groupby("category").apply(lambda g: g.nlargest(3, "value"))
df.groupby("category").filter(lambda g: len(g) > 5) # keep groups matching condition
df.groupby("category")["value"].transform("mean") # broadcast agg back to rows
df.groupby("category").size() # row count per group
df.groupby("category").first(); .last(); .nth(0)
# Window / rolling
df["rolling_mean"] = df["value"].rolling(window=7).mean()
df["expanding_sum"] = df["value"].expanding().sum()
df["ewm"] = df["value"].ewm(span=7).mean()
Merging & Joining
pd.merge(df1, df2, on="key", how="inner") # inner, left, right, outer, cross
pd.merge(df1, df2, left_on="id1", right_on="id2")
pd.merge(df1, df2, on="key", suffixes=("_left", "_right"))
pd.merge(df1, df2, on="key", indicator=True) # adds "_merge" column showing source
df1.join(df2, how="left") # join on index
pd.concat([df1, df2]) # stack vertically (union rows)
pd.concat([df1, df2], axis=1) # stack horizontally (union cols)
pd.concat([df1, df2], ignore_index=True) # reset index after concat
Reshaping
df.pivot(index="date", columns="category", values="value")
df.pivot_table(index="date", columns="category", values="value", aggfunc="sum", fill_value=0)
df.melt(id_vars=["id"], value_vars=["a", "b"], var_name="metric", value_name="value")
df.stack() # columns -> rows (MultiIndex)
df.unstack() # rows -> columns
df.transpose() # swap rows/columns
pd.crosstab(df["a"], df["b"]) # frequency cross-tabulation
String Operations (.str accessor)
df["col"].str.lower(); .str.upper(); .str.strip()
df["col"].str.contains("pattern", case=False, na=False)
df["col"].str.replace("old", "new", regex=False)
df["col"].str.split(",")
df["col"].str.split(",", expand=True) # split into separate columns
df["col"].str.extract(r"(\d+)")
df["col"].str.len()
df["col"].str.startswith("prefix")
df["col"].str.cat(sep=", ")
df["col"].str.zfill(5)
Datetime Operations (.dt accessor)
df["date"] = pd.to_datetime(df["date"])
df["date"].dt.year; .dt.month; .dt.day
df["date"].dt.day_name()
df["date"].dt.dayofweek
df["date"].dt.quarter
df["date"].dt.is_month_end
df["date"] + pd.Timedelta(days=7)
df["date"].dt.to_period("M")
df.set_index("date").resample("D").sum() # daily resample
df.set_index("date").resample("M").mean() # monthly resample
df.set_index("date").resample("W").agg({"a": "sum", "b": "mean"})
pd.date_range("2026-01-01", "2026-12-31", freq="D")
pd.date_range("2026-01-01", periods=12, freq="MS")
Categorical Data
df["cat"] = df["cat"].astype("category") # saves memory, speeds up groupby
df["cat"].cat.categories
df["cat"] = df["cat"].cat.set_categories(["low", "med", "high"], ordered=True)
df["cat"].cat.codes # integer codes
pd.cut(df["value"], bins=[0, 10, 50, 100], labels=["low", "med", "high"])
pd.qcut(df["value"], q=4) # quantile-based binning
Applying Functions
df["col"].apply(func) # element-wise on a Series
df.apply(func, axis=1) # row-wise across a DataFrame (slow — vectorize if possible)
df.apply(func, axis=0) # column-wise
df.applymap(func) # element-wise on entire DataFrame (deprecated, use .map)
df["col"].map({"a": 1, "b": 2}) # value mapping via dict
df["col"].map(func)
df.pipe(custom_func, arg1=1) # chain a custom function into a pipeline
Performance Tips
# Vectorize instead of .apply()/loops where possible
df["c"] = df["a"] + df["b"] # fast
df["c"] = df.apply(lambda r: r["a"] + r["b"], axis=1) # slow — avoid
# Use categorical dtype for low-cardinality string columns
df["status"] = df["status"].astype("category")
# Use vectorized string/datetime accessors, not .apply(lambda x: x.method())
# Read only needed columns
pd.read_csv("file.csv", usecols=["a", "b"])
# Use chunked reading for huge CSVs
for chunk in pd.read_csv("huge.csv", chunksize=100_000):
process(chunk)
# Downcast numeric types to save memory
df["a"] = pd.to_numeric(df["a"], downcast="integer")
# Use .loc for assignment to avoid SettingWithCopyWarning
df.loc[df["a"] > 5, "flag"] = "high" # correct
# df[df["a"] > 5]["flag"] = "high" # WRONG — chained indexing, may silently fail
# Use eval/query for large numeric expressions (uses numexpr under the hood)
df.eval("c = a + b", inplace=True)
df.query("a > 5 and b < 10")
Common Gotchas
- Chained indexing (
df[cond]["col"] = x) may raiseSettingWithCopyWarningand silently not modifydf— use.loc[cond, "col"] = xinstead. df.copy()— modifying a slice without.copy()may or may not affect the original; be explicit.- Integer columns silently become
float64if they containNaN— use nullableInt64(capital I) dtype to keep them as integers with nulls. ==on floating point columns is fragile — usenp.isclose()for comparisons.groupbydropsNaNgroup keys by default — passdropna=Falseto keep them.- Merge can silently produce duplicate rows if keys aren’t unique on one side — check with
validate="one_to_many"etc. onpd.merge. ```
Advanced Pandas Optimization & Workflows
# 1. Custom Aggregations and Window Optimization with Numba JIT
# Compiles the sliding/rolling function directly into raw assembly
df["rolling_numba"] = df["value"].rolling(window=10).mean(engine="numba")
# 2. Memory Minimization with Category & Sparse Dtypes
# Reduces memory footprint for columns with massive duplication
df["sparse_col"] = pd.arrays.SparseArray(df["some_mostly_null_column"])
# 3. Method Chaining Pattern (Clean, Modern, Robust Pipelines)
pipeline_df = (
pd.read_csv("large_dataset.csv")
.query("status == 'active'")
.assign(total_cost=lambda d: d["quantity"] * d["unit_price"])
.groupby("category")
.agg(avg_cost=("total_cost", "mean"))
.reset_index()
)