PandasAdvancedPandas
Data Loading and Inspection
2 topics
Read CSV/Excel/Parquet with production-friendly options
Load real-world files efficiently by setting dtypes, parsing dates, and handling bad rows.
import pandas as pd
# Sales export with mixed quality rows
sales = pd.read_csv(
"sales_2025_q1.csv",
dtype={"store_id": "string", "sku": "string"},
parse_dates=["order_date"],
na_values=["", "NA", "null"],
on_bad_lines="skip" # skip malformed rows instead of failing entire job
)
# Finance team workbook
targets = pd.read_excel("targets.xlsx", sheet_name="Q1", dtype={"store_id": "string"})
# Large event log in Parquet
events = pd.read_parquet("events.parquet")
print(sales.shape, targets.shape, events.shape)
print(sales.dtypes)Use Parquet for faster IO and smaller storage.
Set dtypes during read to reduce memory and avoid type guessing bugs.
Quick profiling and sanity checks
Validate schema and data quality before analysis to catch issues early.
import pandas as pd
orders = pd.read_csv("orders.csv", parse_dates=["created_at"])
print(orders.head(3))
print(orders.info())
print(orders.describe(include="all"))
# Missingness overview
missing_pct = orders.isna().mean().sort_values(ascending=False)
print(missing_pct.head(10))
# Duplicates check for business key
dup_count = orders.duplicated(subset=["order_id"]).sum()
print(f"Duplicate order_id rows: {dup_count}")Run these checks in every pipeline step.
Keep a list of expected columns and assert against it.
Load, Inspect, and Save Data
3 topics
Read CSV/Excel with explicit schema
Use explicit dtypes and date parsing to avoid silent type errors and speed up ingestion for production datasets.
import pandas as pd
sales = pd.read_csv(
"sales_2025.csv",
dtype={
"order_id": "string",
"customer_id": "string",
"store_id": "string",
"quantity": "int64",
"unit_price": "float64"
},
parse_dates=["order_date"],
na_values=["", "NA", "null"]
)
print(sales.head())
print(sales.dtypes)Set dtypes during read to prevent mixed-type columns.
Use parse_dates early so downstream filters/grouping work correctly.
Fast dataset profiling
Quickly inspect shape, nulls, uniqueness, and descriptive stats to understand data quality before transformations.
print("Rows, Cols:", sales.shape)
print("\nColumns:")
print(sales.columns.tolist())
print("\nMissing values:")
print(sales.isna().sum().sort_values(ascending=False).head(10))
print("\nNumeric summary:")
print(sales.describe())
print("\nCategorical cardinality:")
print(sales.select_dtypes(include="object").nunique().sort_values(ascending=False))Run profiling once after load and once after cleaning.
High cardinality columns often need indexing or category encoding.
Write outputs for analytics and reporting
Export cleaned datasets to common formats for BI tools, data warehouses, or handoff to stakeholders.
# Export clean table
sales.to_csv("sales_clean.csv", index=False)
# Export parquet for better compression + faster reads
sales.to_parquet("sales_clean.parquet", index=False)
# Export report slices to Excel
with pd.ExcelWriter("sales_reports.xlsx", engine="xlsxwriter") as writer:
sales[sales["order_date"].dt.month == 1].to_excel(writer, sheet_name="January", index=False)
sales[sales["order_date"].dt.month == 2].to_excel(writer, sheet_name="February", index=False)Prefer Parquet for large analytical datasets.
Use Excel export for business users; keep index=False unless index is meaningful.
Foundations and Mental Model
2 topics
What Pandas is (and how it differs from PyTorch)
Pandas is a data handling library for structured/tabular data. Use it to prepare datasets (CSV, Parquet, SQL tables), engineer features, and summarize data. PyTorch is a machine learning framework used after data prep to train neural networks on tensors.
import pandas as pd
import torch
# Pandas: table-like data
df = pd.DataFrame({"age": [25, 31], "income": [50000, 72000]})
print(df)
# PyTorch: tensor math for learning
x = torch.tensor([[25.0, 50000.0], [31.0, 72000.0]])
print(x.shape)Think: Pandas = data tables, PyTorch = model training.
Most ML pipelines start with Pandas, then convert to NumPy/PyTorch tensors.
Core objects: Series and DataFrame
Series is a single labeled column, DataFrame is a labeled 2D table. Both support vectorized operations for fast transformations.
import pandas as pd
s = pd.Series([10, 20, 30], name="score")
df = pd.DataFrame({
"name": ["Ana", "Ben", "Cara"],
"score": [88, 92, 79]
})
print(s.mean())
print(df["score"].max())Use DataFrame for almost all real-world tasks; Series is useful for column-level work.
Column operations are vectorized—prefer them over Python loops.
Cleaning and Transformation
2 topics
Handle missing values and data types safely
Standardize nulls, fill defaults by business rule, and convert columns to analysis-ready types.
import pandas as pd
customers = pd.read_csv("customers.csv")
# Normalize text placeholders to real NA
customers["email"] = customers["email"].replace(["N/A", "unknown", "-"], pd.NA)
# Fill missing loyalty tier with default
customers["tier"] = customers["tier"].fillna("standard")
# Convert signup date and numeric spend
customers["signup_date"] = pd.to_datetime(customers["signup_date"], errors="coerce")
customers["lifetime_value"] = pd.to_numeric(customers["lifetime_value"], errors="coerce")
# Optional: memory optimization for low-cardinality categories
customers["tier"] = customers["tier"].astype("category")
print(customers.dtypes)Use errors='coerce' to avoid crashes and then inspect failed conversions.
Category dtype can significantly reduce memory usage.
Filter, create columns, and chain operations
Build readable transformation pipelines for reporting and feature engineering.
import pandas as pd
orders = pd.read_csv("orders.csv", parse_dates=["order_date"])
result = (
orders
.query("status == 'completed' and amount > 0")
.assign(
order_month=lambda d: d["order_date"].dt.to_period("M").astype(str),
net_amount=lambda d: d["amount"] - d["discount"].fillna(0),
is_weekend=lambda d: d["order_date"].dt.dayofweek >= 5
)
[["order_id", "customer_id", "order_month", "net_amount", "is_weekend"]]
)
print(result.head())Method chaining improves readability and reduces temporary variables.
Prefer vectorized expressions over row-wise loops for performance.
Data Loading and Inspection
2 topics
Read common formats (CSV, Parquet, Excel, SQL)
Pandas supports multiple file/database formats. Choose Parquet for analytics pipelines and CSV for interoperability.
import pandas as pd
# CSV
df_csv = pd.read_csv("customers.csv")
# Parquet (fast + compressed)
df_parquet = pd.read_parquet("events.parquet")
# Excel
df_xlsx = pd.read_excel("report.xlsx", sheet_name="Sheet1")
# SQL
# from sqlalchemy import create_engine
# engine = create_engine("postgresql+psycopg2://user:pass@host/db")
# df_sql = pd.read_sql("SELECT * FROM orders", engine)Pass dtype= and parse_dates= in read_csv for consistent schemas.
Use chunksize= for very large files.
Quick dataset profiling
Immediately inspect shape, types, nulls, and distribution to catch data issues before transformations.
import pandas as pd
df = pd.read_csv("customers.csv")
print(df.shape)
print(df.head(3))
print(df.info())
print(df.isna().sum())
print(df.describe(include="all"))Run info() and isna().sum() on every new dataset.
Use include='all' in describe() for categorical overview.
Clean and Prepare Data
3 topics
Handle missing values strategically
Choose fill/drop methods by business meaning: preserve key rows, impute defaults for operational fields, and avoid blind fills.
# Drop rows missing critical identifiers
sales = sales.dropna(subset=["order_id", "customer_id"])
# Fill operational defaults
sales["discount_pct"] = sales["discount_pct"].fillna(0)
# Fill category with explicit label
sales["payment_method"] = sales["payment_method"].fillna("unknown")
# Forward-fill inventory values by store/date
sales = sales.sort_values(["store_id", "order_date"])
sales["stock_level"] = sales.groupby("store_id")["stock_level"].ffill()Treat IDs, facts, and dimensions differently when filling nulls.
Use groupby + ffill for time-series operational data.
Standardize text and categories
Normalize string formats to reduce duplicate categories caused by case, spacing, or punctuation differences.
sales["city"] = (
sales["city"]
.str.strip()
.str.lower()
.str.replace(r"\s+", " ", regex=True)
)
# Map raw status values to canonical labels
status_map = {
"delivered": "delivered",
"Delivered": "delivered",
"in transit": "in_transit",
"In-Transit": "in_transit",
"cancelled": "cancelled"
}
sales["shipment_status"] = sales["shipment_status"].map(status_map).fillna("other")Normalize text before joins/grouping to prevent false splits.
Keep an 'other' bucket for unmapped values and audit it later.
Remove duplicates with business rules
Deduplicate based on the true business key and keep the most recent record when events are replayed.
# Suppose order events may be duplicated from retries
sales = sales.sort_values("updated_at")
sales_latest = sales.drop_duplicates(subset=["order_id"], keep="last")
# Verify impact
print("Before:", len(sales), "After:", len(sales_latest))Sort by trusted recency column before drop_duplicates.
Always log before/after row counts in pipelines.
Cleaning and Transformation
3 topics
Handle missing values and duplicates
Clean nulls and duplicate rows/keys before analysis or ML. Strategy depends on business logic: drop, fill, or impute.
import pandas as pd
df = pd.DataFrame({
"city": ["NY", None, "SF", "SF"],
"sales": [100, 200, None, None]
})
# Fill nulls
_df = df.copy()
_df["city"] = _df["city"].fillna("Unknown")
_df["sales"] = _df["sales"].fillna(_df["sales"].median())
# Drop duplicates
_df = _df.drop_duplicates()
print(_df)Use median for skewed numeric columns; mean for symmetric distributions.
If duplicates are expected, dedupe using a subset of key columns.
Filter, select, and create columns
Use boolean masks and assign() to transform data safely and readably.
import pandas as pd
df = pd.DataFrame({
"product": ["A", "B", "C"],
"price": [10, 25, 40],
"qty": [2, 1, 3]
})
high_value = df[df["price"] > 20]
result = df.assign(revenue=df["price"] * df["qty"])
print(high_value)
print(result)Prefer .loc[row_filter, col_list] for explicit indexing.
Use assign() in pipelines for cleaner chained transformations.
GroupBy, aggregation, and pivoting
Summarize business metrics by dimensions (region, category, date) using groupby and pivot tables.
import pandas as pd
df = pd.DataFrame({
"region": ["US", "US", "EU", "EU"],
"category": ["A", "B", "A", "B"],
"sales": [100, 120, 90, 110]
})
agg = df.groupby("region", as_index=False).agg(total_sales=("sales", "sum"))
pivot = df.pivot_table(index="region", columns="category", values="sales", aggfunc="sum")
print(agg)
print(pivot)Use named aggregations for clear output column names.
Set as_index=False when you want SQL-like grouped output.
Joins, GroupBy, and Aggregation
3 topics
Merge datasets with integrity checks
Combine transactional and reference data while validating relationship assumptions.
import pandas as pd
orders = pd.read_csv("orders.csv")
products = pd.read_csv("products.csv")
# Validate many-to-one relationship: many orders per one product
merged = orders.merge(
products[["sku", "category", "unit_cost"]],
on="sku",
how="left",
validate="many_to_one",
indicator=True
)
# Investigate unmatched SKUs
unmatched = merged[merged["_merge"] == "left_only"]["sku"].drop_duplicates()
print("Unmatched SKUs:", unmatched.tolist())
merged = merged.drop(columns="_merge")Use validate= to detect accidental duplicate keys.
indicator=True helps debug missing matches quickly.
Business KPIs with groupby and named aggregations
Calculate multi-metric summaries cleanly for dashboards.
import pandas as pd
sales = pd.read_csv("sales.csv", parse_dates=["order_date"])
kpi = (
sales
.assign(month=lambda d: d["order_date"].dt.to_period("M").astype(str))
.groupby(["region", "month"], as_index=False)
.agg(
orders=("order_id", "nunique"),
revenue=("amount", "sum"),
avg_order_value=("amount", "mean"),
refund_rate=("is_refunded", "mean")
)
.sort_values(["region", "month"])
)
print(kpi.head())Named aggregations produce clear column names.
Boolean mean is a quick way to compute rates.
Pivot tables for reporting
Create manager-friendly summary tables by dimension and metric.
import pandas as pd
sales = pd.read_csv("sales.csv", parse_dates=["order_date"])
sales["month"] = sales["order_date"].dt.to_period("M").astype(str)
report = pd.pivot_table(
sales,
index="month",
columns="channel",
values="amount",
aggfunc="sum",
fill_value=0,
margins=True,
margins_name="Total"
)
print(report)pivot_table handles duplicates; pivot does not.
Use fill_value for cleaner downstream exports.
Filter, Transform, and Engineer Features
3 topics
Advanced filtering with boolean masks and query
Combine multiple conditions for operational use cases like high-value delayed orders in specific regions.
high_risk = sales[
(sales["order_value"] > 1000) &
(sales["shipment_status"] == "in_transit") &
(sales["region"].isin(["west", "north"]))
]
# Equivalent with query for readability
high_risk_q = sales.query(
"order_value > 1000 and shipment_status == 'in_transit' and region in ['west', 'north']"
)
print(high_risk_q[["order_id", "order_value", "region"]].head())Use parentheses with & and | in boolean masks.
query() can be cleaner for analyst-facing notebooks.
Create derived metrics and date features
Build reusable analytical features such as revenue, AOV buckets, month keys, and weekday patterns.
sales["revenue"] = sales["quantity"] * sales["unit_price"] * (1 - sales["discount_pct"])
sales["order_month"] = sales["order_date"].dt.to_period("M").astype(str)
sales["order_weekday"] = sales["order_date"].dt.day_name()
sales["aov_bucket"] = pd.cut(
sales["order_value"],
bins=[0, 50, 200, 500, 10_000],
labels=["low", "mid", "high", "vip"],
right=False
)
print(sales[["order_id", "revenue", "order_month", "aov_bucket"]].head())Use vectorized operations instead of row loops for performance.
Store month as period/string for easier reporting pivots.
Apply row-wise logic only when necessary
Prefer vectorized logic; use apply for complex rules that cannot be expressed cleanly with masks.
def risk_flag(row):
if row["payment_method"] == "cash" and row["order_value"] > 300:
return "review"
if row["shipment_status"] == "in_transit" and row["days_since_order"] > 7:
return "delayed"
return "ok"
sales["risk_flag"] = sales.apply(risk_flag, axis=1)
# Better (vectorized) alternative for simple rules
sales["is_large_order"] = sales["order_value"].ge(500)apply(axis=1) is slower on large datasets; benchmark before production.
Use np.select for multi-branch vectorized conditions.
Joins, Time Series, and Performance
2 topics
Merge and join tables safely
Combine datasets with merge() using one-to-one, one-to-many, or many-to-many relationships; validate joins to avoid silent data blowups.
import pandas as pd
customers = pd.DataFrame({"customer_id": [1, 2], "name": ["Ana", "Ben"]})
orders = pd.DataFrame({"order_id": [101, 102, 103], "customer_id": [1, 1, 2], "amount": [50, 75, 20]})
merged = customers.merge(orders, on="customer_id", how="left", validate="one_to_many")
print(merged)Always check row counts before/after merge.
Use validate= to enforce expected relationship cardinality.
Time series basics
Convert to datetime, index by time, resample, and compute rolling metrics for trends and forecasting features.
import pandas as pd
df = pd.DataFrame({
"date": ["2026-01-01", "2026-01-02", "2026-01-03"],
"sales": [100, 120, 90]
})
df["date"] = pd.to_datetime(df["date"])
df = df.set_index("date")
daily = df.resample("D").sum()
daily["rolling_avg_2d"] = daily["sales"].rolling(2).mean()
print(daily)Use UTC for storage and convert to local timezone only for display.
Sort datetime index before rolling/resampling.
Aggregate, Join, and Analyze Time Series
3 topics
GroupBy for KPI dashboards
Compute common business KPIs like revenue, orders, unique customers, and average basket size by segment.
kpi = (
sales.groupby(["order_month", "region"], as_index=False)
.agg(
orders=("order_id", "nunique"),
customers=("customer_id", "nunique"),
total_revenue=("revenue", "sum"),
avg_order_value=("order_value", "mean")
)
)
print(kpi.sort_values(["order_month", "region"]).head(10))Use named aggregations for readable output columns.
nunique on order/customer IDs is common in executive reporting.
Merge fact and dimension tables safely
Join transactional data with lookup tables (customers, products, stores) while validating join integrity.
customers = pd.read_csv("customers.csv", dtype={"customer_id": "string"})
sales_enriched = sales.merge(
customers[["customer_id", "segment", "signup_date"]],
on="customer_id",
how="left",
validate="many_to_one"
)
missing_segment = sales_enriched["segment"].isna().mean()
print(f"Missing customer segment rate: {missing_segment:.2%}")Use validate= to catch accidental many-to-many joins.
After merge, always check unmatched key rates.
Resample time series for trend analysis
Convert event-level data into daily/weekly summaries and calculate rolling metrics for trend monitoring.
daily = (
sales.set_index("order_date")
.resample("D")
.agg(daily_revenue=("revenue", "sum"), daily_orders=("order_id", "nunique"))
.fillna(0)
)
daily["rev_7d_ma"] = daily["daily_revenue"].rolling(7, min_periods=1).mean()
daily["wow_growth"] = daily["daily_revenue"].pct_change(7)
print(daily.tail())Set datetime index before resample.
Use rolling averages to smooth noisy daily data.
Time Series and Window Analysis
2 topics
Resample daily events to weekly/monthly trends
Aggregate timestamped data for trend analysis and operational monitoring.
import pandas as pd
traffic = pd.read_csv("web_traffic.csv", parse_dates=["timestamp"])
weekly = (
traffic
.set_index("timestamp")
.resample("W")
.agg(visits=("visits", "sum"), conversions=("conversions", "sum"))
)
weekly["conversion_rate"] = weekly["conversions"] / weekly["visits"]
print(weekly.tail())Set datetime index before resampling.
Pick frequencies aligned with business reporting cadence.
Rolling metrics and period-over-period comparisons
Compute smoothed metrics and growth rates for performance tracking.
import pandas as pd
revenue = pd.read_csv("daily_revenue.csv", parse_dates=["date"]).sort_values("date")
revenue["rev_7d_avg"] = revenue["revenue"].rolling(window=7, min_periods=1).mean()
revenue["wow_growth"] = revenue["revenue"].pct_change(periods=7)
print(revenue[["date", "revenue", "rev_7d_avg", "wow_growth"]].tail(10))Use min_periods to avoid too many initial NaNs.
pct_change with periods=7 is useful for weekly seasonality.
Performance and Export
2 topics
Scale processing with chunking and selective columns
Process large files without exhausting memory.
import pandas as pd
file_path = "transactions_big.csv"
chunksize = 200_000
usecols = ["customer_id", "amount", "status"]
totals = {}
for chunk in pd.read_csv(file_path, chunksize=chunksize, usecols=usecols):
chunk = chunk[chunk["status"] == "paid"]
agg = chunk.groupby("customer_id")["amount"].sum()
for cid, val in agg.items():
totals[cid] = totals.get(cid, 0) + val
top_customers = sorted(totals.items(), key=lambda x: x[1], reverse=True)[:10]
print(top_customers)Read only required columns with usecols.
Chunking is great for one-pass aggregations.
Export clean outputs for BI and downstream systems
Write reliable outputs in CSV and Parquet with reproducible options.
import pandas as pd
report = pd.DataFrame({
"region": ["North", "South"],
"revenue": [125000.50, 98000.75],
"month": ["2025-01", "2025-01"]
})
# CSV for business users
report.to_csv("monthly_revenue_report.csv", index=False, encoding="utf-8")
# Parquet for analytics pipelines
report.to_parquet("monthly_revenue_report.parquet", index=False)
print("Export complete")CSV is human-friendly; Parquet is pipeline-friendly.
Keep index=False unless index has semantic meaning.
Pandas to PyTorch Pipeline
2 topics
Prepare features in Pandas, then convert to tensors
A common workflow: clean and encode data in Pandas, split features/labels, then convert to torch tensors for training.
import pandas as pd
import torch
# Example tabular dataset
df = pd.DataFrame({
"age": [25, 31, 22, 40],
"income": [50000, 72000, 42000, 99000],
"bought": [0, 1, 0, 1]
})
X = df[["age", "income"]].to_numpy(dtype="float32")
y = df["bought"].to_numpy(dtype="float32")
X_t = torch.tensor(X)
y_t = torch.tensor(y).unsqueeze(1)
print(X_t.shape, y_t.shape)Use float32 for model inputs unless you need higher precision.
Normalize/standardize numeric columns before training.
Tiny PyTorch network after Pandas preprocessing
After Pandas feature prep, define a neural network with torch.nn and train using an optimizer like Adam.
import torch
import torch.nn as nn
import torch.optim as optim
model = nn.Sequential(
nn.Linear(2, 4),
nn.ReLU(),
nn.Linear(4, 1),
nn.Sigmoid()
)
criterion = nn.BCELoss()
optimizer = optim.Adam(model.parameters(), lr=1e-3)
# Assume X_t, y_t already prepared from Pandas
# for _ in range(200):
# preds = model(X_t)
# loss = criterion(preds, y_t)
# optimizer.zero_grad()
# loss.backward() # autograd computes gradients
# optimizer.step()Autograd handles gradient computation after loss.backward().
Start simple; add complexity only when baseline underperforms.
Related Cheat Sheets
More hands-on references connected to this topic.
PandasA practical cheat sheet for loading, cleaning, transforming, analyzing, and exporting tabular data with Pandas, plus how it fits with PyTorch workflows.
shared topics
FastAPIFastAPI routes, request models, dependency injection, authentication, and async patterns.
shared topics
PythonPython syntax, data structures, comprehensions, OOP, error handling, and standard library essentials.
shared topics
PyTorchEssential PyTorch patterns for tensor ops, autograd, model building, training loops, and deployment.
shared topics
Related Articles
Background reading and deeper explanations for this sheet.
Generative AI Roadmap: A Practical, Beginner-Friendly Guide to Learning and Building Real Projects
Generative AI can feel overwhelming, but you don’t need a PhD to get started and create useful applications. This roadmap breaks down what to learn, when to learn it, and how to apply each skill in real-world projects—from prompt engineering and APIs to RAG, evaluation, and production deployment.
keyword overlap
He Missed Quota by 11%. The Problem Wasn't His Selling.
Marcus missed quota by 11% and knew exactly why — only 12 of his 40 weekly hours went to actual selling. This is the story of how his team used AI to claw back the other 28, and what changed when they did.
keyword overlap
Intro to FastAPI: Build a Production-Ready Python API with Real Examples
FastAPI is one of the fastest ways to build modern APIs in Python without sacrificing code quality or developer experience. In this practical, beginner-friendly guide, you’ll learn how to create endpoints, validate data, handle errors, connect a database, secure routes, and prepare your FastAPI app for real-world deployment.
keyword overlap
Python Stack vs Heap Memory: A Simple, Practical Guide for Developers
Confused about where Python stores function calls, variables, and real objects? This guide breaks down stack vs heap memory in plain English, with clear examples showing local references on the stack and actual objects on the heap. You’ll learn how this impacts performance, debugging, and writing cleaner Python code.
keyword overlap