Pandas Deep Dive

Reading Data

Everything starts with getting data into a DataFrame. Pandas can read almost any format.

CSV files

import pandas as pd

# Basic read
df = pd.read_csv("sales.csv")

# Common options
df = pd.read_csv(
    "sales.csv",
    sep=",",              # delimiter (use "\t" for TSV)
    header=0,             # row number for column names
    index_col="id",       # use a column as the index
    usecols=["id", "product", "revenue"],  # only load these columns
    dtype={"id": str},    # force column types
    parse_dates=["date"], # auto-parse date columns
    na_values=["N/A", ""], # treat these as NaN
    nrows=1000            # only read first 1000 rows (great for testing)
)

Excel files

# Reads the first sheet by default
df = pd.read_excel("report.xlsx")

# Read a specific sheet
df = pd.read_excel("report.xlsx", sheet_name="Q1 Sales")

# Read all sheets into a dict of DataFrames
all_sheets = pd.read_excel("report.xlsx", sheet_name=None)

SQL databases

import sqlite3

conn = sqlite3.connect("company.db")

# Read an entire table
df = pd.read_sql("SELECT * FROM employees", conn)

# Read with a query (filter at the database level for speed)
df = pd.read_sql(
    "SELECT name, salary FROM employees WHERE department = 'Engineering'",
    conn
)

conn.close()

Selecting Data

There are four main ways to grab data from a DataFrame. Each serves a different purpose.

Single column

# Returns a Series (1D)
df["name"]

# Dot notation (works only for simple column names without spaces)
df.name

Multiple columns

# Returns a DataFrame (2D) — note the double brackets
df[["name", "salary", "department"]]

loc — label-based selection

# Row by label, column by name
df.loc[0, "name"]               # single value
df.loc[0:4, "name":"salary"]    # slice (inclusive on both ends!)
df.loc[df["salary"] > 80000]    # filter rows by condition

iloc — integer position-based selection

# Row by position, column by position
df.iloc[0, 1]             # first row, second column
df.iloc[0:5, 0:3]         # first 5 rows, first 3 columns (exclusive end)
df.iloc[-1]               # last row

Filtering Rows

Filtering is how you ask "show me only the rows where..." It is the Pandas equivalent of SQL's WHERE clause.

Boolean indexing

# Single condition
high_earners = df[df["salary"] > 100000]

# Multiple conditions — use & (and), | (or), ~ (not)
# MUST wrap each condition in parentheses
senior_engineers = df[
    (df["department"] == "Engineering") &
    (df["years_exp"] >= 5)
]

# Negate: everyone NOT in Engineering
non_eng = df[~(df["department"] == "Engineering")]

query() — the readable alternative

# Same result, cleaner syntax
senior_engineers = df.query("department == 'Engineering' and years_exp >= 5")

# You can reference variables with @
min_salary = 80000
df.query("salary >= @min_salary")

isin() — match against a list

# Like SQL's IN clause
target_depts = ["Engineering", "Product", "Design"]
df[df["department"].isin(target_depts)]

Sorting

# Sort by one column (ascending by default)
df.sort_values("salary")

# Descending
df.sort_values("salary", ascending=False)

# Sort by multiple columns
df.sort_values(["department", "salary"], ascending=[True, False])

# Sort by index
df.sort_index()

GroupBy & Aggregation

GroupBy is the Pandas version of SQL's GROUP BY. The pattern is always: split (group rows) → apply (calculate) → combine (merge results).

Basic groupby + agg

# Average salary per department
df.groupby("department")["salary"].mean()

# Multiple aggregations
df.groupby("department")["salary"].agg(["mean", "median", "min", "max", "count"])

Multiple columns, different aggs

# Different function per column
df.groupby("department").agg(
    avg_salary=("salary", "mean"),
    total_headcount=("id", "count"),
    max_experience=("years_exp", "max")
)

transform vs apply

# transform: returns same-size Series (great for adding a column)
df["dept_avg"] = df.groupby("department")["salary"].transform("mean")

# Now each row has its department's average next to its actual salary
df["above_avg"] = df["salary"] > df["dept_avg"]

# apply: flexible, returns any shape (slower)
def top_earner(group):
    return group.nlargest(1, "salary")

df.groupby("department").apply(top_earner)

Merging & Joining

Combining DataFrames is one of the most common Pandas operations. If you know SQL JOINs, you already understand the concept.

merge — the SQL-style way

# INNER JOIN (default) — only matching rows
result = pd.merge(employees, departments, left_on="dept_id", right_on="id")

# LEFT JOIN — all employees, even without a department
result = pd.merge(employees, departments, left_on="dept_id", right_on="id", how="left")

# RIGHT JOIN
result = pd.merge(employees, departments, left_on="dept_id", right_on="id", how="right")

# FULL OUTER JOIN
result = pd.merge(employees, departments, left_on="dept_id", right_on="id", how="outer")

# JOIN on same-name column
result = pd.merge(orders, customers, on="customer_id")

join — index-based shortcut

# When the join key is the index
employees.set_index("dept_id").join(departments.set_index("id"), how="left")

concat — stacking DataFrames

# Stack vertically (like UNION ALL)
all_data = pd.concat([q1_sales, q2_sales, q3_sales, q4_sales])

# Stack horizontally (side by side)
combined = pd.concat([df1, df2], axis=1)

Handling Missing Data

Real-world data is messy. Pandas represents missing values as NaN (Not a Number). Here is your toolkit for dealing with them.

Detecting

# Check for nulls
df.isna()                  # Boolean DataFrame
df.isna().sum()            # Count nulls per column
df.isna().sum().sum()      # Total nulls in entire DataFrame

# Percentage of nulls
(df.isna().sum() / len(df) * 100).round(1)

Filling

# Fill with a constant
df["salary"].fillna(0)

# Fill with the column mean
df["salary"].fillna(df["salary"].mean())

# Forward fill (propagate last valid value)
df["price"].fillna(method="ffill")

# Interpolate (linear by default — great for time series)
df["temperature"].interpolate()

Dropping

# Drop rows with ANY null
df.dropna()

# Drop rows where a specific column is null
df.dropna(subset=["email"])

# Drop columns that are mostly null (> 50% missing)
threshold = len(df) * 0.5
df.dropna(axis=1, thresh=threshold)

Reshaping Data

Sometimes your data is in the wrong shape for your analysis. Pivot makes it wider; melt makes it longer.

pivot_table — long to wide

# Rows: department, Columns: year, Values: average salary
pivot = df.pivot_table(
    values="salary",
    index="department",
    columns="year",
    aggfunc="mean"
)
# Result: departments as rows, years as columns, cells = avg salary

melt — wide to long

# Turn columns into rows (opposite of pivot)
# Useful when each column is a year or category
long_df = pd.melt(
    wide_df,
    id_vars=["department"],       # keep these as-is
    value_vars=["2023", "2024"],  # unpivot these columns
    var_name="year",
    value_name="revenue"
)

stack / unstack

# stack: columns → rows (adds a level to the index)
stacked = df.stack()

# unstack: rows → columns (removes a level from the index)
unstacked = df.unstack()

Method Chaining

Instead of saving intermediate variables, chain operations together. This reads top-to-bottom, like a recipe.

result = (
    df
    .query("department == 'Engineering'")
    .assign(
        salary_k=lambda x: x["salary"] / 1000,
        tenure=lambda x: 2026 - x["hire_year"]
    )
    .groupby("level")
    .agg(
        avg_salary_k=("salary_k", "mean"),
        headcount=("id", "count")
    )
    .sort_values("avg_salary_k", ascending=False)
    .reset_index()
)

SQL-to-Pandas Cheat Sheet

If you know SQL, you know most of Pandas. This table maps the 15+ most common SQL operations to their Pandas equivalents.

Performance Tips

What to do

# GOOD: vectorized (fast)
df["bonus"] = df["salary"] * 0.1

# BAD: iterrows (slow — loops in Python)
for idx, row in df.iterrows():
    df.at[idx, "bonus"] = row["salary"] * 0.1

More performance tips

• Read only what you need: Use usecols and nrows in read_csv().
• Downcast types: df["id"] = df["id"].astype("int32") cuts memory in half vs int64.
• Use categoricals: df["status"] = df["status"].astype("category") for columns with few unique values.
• Filter before groupby: Reduce rows first, then aggregate — just like SQL.
• Avoid apply when possible: Use built-in methods (str, dt accessors) instead of .apply(lambda).