Saturday, November 21, 2026

Python Pandas: A Complete Guide

Python Pandas: A Complete Guide

A practical, end-to-end reference for working with data in pandas, from loading a file to producing a clean, analysis-ready result.

Python Pandas: A Complete Guide


1. Setup and Core Concepts

Install and import:

pip install pandas numpy openpyxl pyarrow
import pandas as pd
import numpy as np

Pandas has two main data structures:

  • Series: a one-dimensional labeled array (a single column).
  • DataFrame: a two-dimensional table of rows and columns, where each column is a Series.

Every DataFrame has an index (row labels) and columns (column labels). Most pandas operations are vectorized: they act on whole columns at once, which is far faster than Python loops.

2. Creating Data

# From a dictionary
df = pd.DataFrame({
    "name": ["Asha", "Ravi", "Meera", "John"],
    "age": [28, 35, 42, 31],
    "city": ["Pune", "Delhi", "Pune", "Mumbai"],
    "salary": [55000, 72000, 91000, 64000],
})

# From a list of dicts
df = pd.DataFrame([{"a": 1, "b": 2}, {"a": 3, "b": 4}])

# From a NumPy array
df = pd.DataFrame(np.random.randn(5, 3), columns=["x", "y", "z"])

# A Series
s = pd.Series([10, 20, 30], index=["a", "b", "c"], name="score")

3. Reading and Writing Files

df = pd.read_csv("data.csv")
df = pd.read_excel("data.xlsx", sheet_name="Sheet1")
df = pd.read_json("data.json")
df = pd.read_parquet("data.parquet")
df = pd.read_sql("SELECT * FROM users", connection)

Useful read_csv options:

df = pd.read_csv(
    "data.csv",
    usecols=["id", "date", "amount"],   # load only needed columns
    dtype={"id": "int32"},              # control types
    parse_dates=["date"],               # parse dates on load
    na_values=["NA", "?", ""],          # extra missing-value markers
    nrows=1000,                         # read only the first 1000 rows
    sep=";", encoding="utf-8",
)

For very large files, read in chunks:

for chunk in pd.read_csv("big.csv", chunksize=100_000):
    process(chunk)

Writing:

df.to_csv("out.csv", index=False)
df.to_excel("out.xlsx", index=False)
df.to_parquet("out.parquet")   # compact and fast; preserves dtypes

4. Inspecting Your Data

df.head()          # first 5 rows
df.tail(3)         # last 3 rows
df.shape           # (rows, columns)
df.columns         # column names
df.dtypes          # data types
df.info()          # types, non-null counts, memory
df.describe()      # summary statistics for numeric columns
df.describe(include="object")  # summary for text columns
df["city"].value_counts()      # frequency of each value
df["city"].nunique()           # number of unique values
df.sample(5)       # random rows

5. Selecting Data

Columns

df["age"]               # a Series
df[["name", "age"]]     # a DataFrame with two columns

Rows and cells with loc and iloc

  • loc selects by label.
  • iloc selects by integer position.
df.loc[0]                         # row with index label 0
df.loc[0:2, ["name", "age"]]      # labels 0 to 2 (inclusive!), two columns
df.iloc[0]                        # first row
df.iloc[0:3, 0:2]                 # first 3 rows, first 2 columns (end exclusive)
df.at[0, "name"]                  # fast single-cell access by label

Note that loc slices include the end label, while iloc slices exclude the end position.

Boolean filtering

df[df["age"] > 30]
df[(df["age"] > 30) & (df["city"] == "Pune")]      # AND
df[(df["city"] == "Pune") | (df["city"] == "Delhi")]  # OR
df[df["city"].isin(["Pune", "Delhi"])]
df[~df["city"].isin(["Pune"])]                     # NOT
df[df["name"].str.startswith("A")]
df[df["age"].between(25, 35)]
df.query("age > 30 and city == 'Pune'")            # readable alternative

Always wrap each condition in parentheses and use &, |, ~ instead of and, or, not.

6. Modifying Data

# Add or change columns
df["bonus"] = df["salary"] * 0.1
df["senior"] = df["age"] >= 35
df["band"] = np.where(df["salary"] > 70000, "high", "standard")

# Several columns at once, chainable
df = df.assign(
    total=lambda d: d["salary"] + d["bonus"],
    age_next_year=lambda d: d["age"] + 1,
)

# Multiple conditions
df["grade"] = np.select(
    [df["salary"] >= 90000, df["salary"] >= 60000],
    ["A", "B"],
    default="C",
)

# Rename, drop, reorder
df = df.rename(columns={"salary": "annual_salary"})
df = df.drop(columns=["bonus"])
df = df[["name", "city", "age"]]

# Update values conditionally
df.loc[df["city"] == "Delhi", "city"] = "New Delhi"

# Replace values
df["city"] = df["city"].replace({"Pune": "PNQ", "Mumbai": "BOM"})

# Sorting
df = df.sort_values("salary", ascending=False)
df = df.sort_values(["city", "age"], ascending=[True, False])
df = df.reset_index(drop=True)

7. Handling Missing Data

Missing values appear as NaN (or NaT for dates, pd.NA for nullable types).

df.isna().sum()              # missing count per column
df.isna().mean() * 100       # percentage missing

df.dropna()                          # drop rows with any missing value
df.dropna(subset=["age"])            # only if "age" is missing
df.dropna(thresh=3)                  # keep rows with at least 3 non-null values

df["age"] = df["age"].fillna(df["age"].median())
df["city"] = df["city"].fillna("Unknown")
df["price"] = df["price"].ffill()    # forward-fill (useful for time series)
df["price"] = df["price"].interpolate()

Choose a strategy deliberately: dropping loses data, filling with the mean or median can distort distributions, and forward-fill assumes ordering is meaningful.

8. Data Types and Cleaning

df["age"] = df["age"].astype("int32")
df["amount"] = pd.to_numeric(df["amount"], errors="coerce")  # bad values become NaN
df["date"] = pd.to_datetime(df["date"], errors="coerce")
df["city"] = df["city"].astype("category")  # saves memory for repeated text

Duplicates

df.duplicated().sum()
df = df.drop_duplicates()
df = df.drop_duplicates(subset=["email"], keep="last")

String operations

The .str accessor applies string methods to a whole column:

df["name"] = df["name"].str.strip().str.title()
df["email"] = df["email"].str.lower()
df["has_gmail"] = df["email"].str.contains("gmail", na=False)
df[["first", "last"]] = df["full_name"].str.split(" ", n=1, expand=True)
df["phone"] = df["phone"].str.replace(r"\D", "", regex=True)
df["code"] = df["text"].str.extract(r"(\d{4})")

Applying custom functions

df["age_group"] = df["age"].apply(lambda x: "young" if x < 30 else "adult")
df["label"] = df.apply(lambda row: f"{row['name']} ({row['city']})", axis=1)
df["city_code"] = df["city"].map({"Pune": 1, "Delhi": 2})

apply with axis=1 is slow on large data. Prefer vectorized operations, np.where, or np.select when possible.

Outliers

q1, q3 = df["salary"].quantile([0.25, 0.75])
iqr = q3 - q1
mask = df["salary"].between(q1 - 1.5 * iqr, q3 + 1.5 * iqr)
df_clean = df[mask]

9. Aggregation and GroupBy

GroupBy follows the split, apply, combine pattern.

df.groupby("city")["salary"].mean()
df.groupby("city")["salary"].agg(["mean", "min", "max", "count"])

# Named aggregation (clean output column names)
summary = df.groupby("city").agg(
    avg_salary=("salary", "mean"),
    headcount=("name", "count"),
    oldest=("age", "max"),
).reset_index()

# Multiple keys
df.groupby(["city", "department"])["salary"].sum()

# Transform: result aligned with the original rows
df["city_avg"] = df.groupby("city")["salary"].transform("mean")
df["pct_of_city"] = df["salary"] / df.groupby("city")["salary"].transform("sum")

# Filter groups
df.groupby("city").filter(lambda g: len(g) >= 3)

# Top N per group
df.sort_values("salary", ascending=False).groupby("city").head(2)

# Rank within group
df["rank"] = df.groupby("city")["salary"].rank(ascending=False, method="dense")

Pivot tables and crosstabs

pd.pivot_table(
    df, index="city", columns="department",
    values="salary", aggfunc="mean", fill_value=0, margins=True,
)

pd.crosstab(df["city"], df["department"])

10. Combining DataFrames

Merge (SQL-style joins)

merged = pd.merge(orders, customers, on="customer_id", how="left")

# Different key names
pd.merge(orders, customers, left_on="cust_id", right_on="id", how="inner")

# Validate relationships and diagnose mismatches
pd.merge(a, b, on="id", how="outer", indicator=True, validate="one_to_many")

Join types: inner (matches only), left (all left rows), right (all right rows), outer (everything).

Concatenate

pd.concat([df1, df2], ignore_index=True)   # stack rows
pd.concat([df1, df2], axis=1)              # side by side, aligned on index

Join on index

df1.join(df2, how="left", rsuffix="_other")

11. Reshaping Data

# Wide to long
long = pd.melt(
    df, id_vars=["name"], value_vars=["q1", "q2", "q3"],
    var_name="quarter", value_name="sales",
)

# Long to wide
wide = long.pivot(index="name", columns="quarter", values="sales").reset_index()

# Stack and unstack move column levels to and from the index
df.set_index(["city", "year"])["sales"].unstack("year")

# Explode list-like cells into rows
df.explode("tags")

# One-hot encoding
pd.get_dummies(df, columns=["city"], drop_first=True)

12. Time Series

df["date"] = pd.to_datetime(df["date"])

# Date parts via the .dt accessor
df["year"] = df["date"].dt.year
df["month"] = df["date"].dt.month
df["weekday"] = df["date"].dt.day_name()
df["quarter"] = df["date"].dt.quarter

# Use the date as the index for time-based operations
ts = df.set_index("date").sort_index()
ts.loc["2026-01"]                 # all rows in January 2026
ts.loc["2026-01-01":"2026-03-31"] # date range slice

# Resample: change frequency
ts["sales"].resample("M").sum()    # monthly totals (use "ME" in newer pandas)
ts["sales"].resample("W").mean()   # weekly averages

# Rolling and expanding windows
ts["ma7"] = ts["sales"].rolling(window=7).mean()
ts["cumulative"] = ts["sales"].expanding().sum()

# Shift and percentage change
ts["prev_day"] = ts["sales"].shift(1)
ts["growth"] = ts["sales"].pct_change()

# Date arithmetic
df["days_since"] = (pd.Timestamp.today() - df["date"]).dt.days
pd.date_range("2026-01-01", periods=12, freq="MS")

Frequency aliases change between pandas versions (for example "M" became "ME" for month-end in pandas 2.2), so check your version's documentation if you see a deprecation warning.

13. Window and Cumulative Functions

df["cumsum"] = df["sales"].cumsum()
df["cummax"] = df["sales"].cummax()
df["diff"] = df["sales"].diff()

# Per-group running total
df["running"] = df.groupby("store")["sales"].cumsum()

# Per-group rolling average
df["roll3"] = df.groupby("store")["sales"].transform(lambda s: s.rolling(3).mean())

14. MultiIndex (Hierarchical Index)

mi = df.set_index(["city", "department"]).sort_index()
mi.loc["Pune"]
mi.loc[("Pune", "Sales")]
mi.xs("Sales", level="department")
mi.reset_index()

MultiIndex is powerful for grouped output, but many workflows are simpler if you keep data "flat" and call reset_index() after aggregations.

15. Performance and Memory

  • Vectorize: avoid for loops and iterrows(). Use column arithmetic, np.where, .str, and .dt.
  • Downcast types: use int32, float32, or category where the range allows.
  • Load less: use usecols, nrows, and chunksize in read_csv.
  • Use Parquet instead of CSV for large, repeated workloads.
  • Check memory: df.memory_usage(deep=True).sum() / 1e6 gives megabytes.
  • Avoid chained indexing like df[df.a > 1]["b"] = 0; use df.loc[df.a > 1, "b"] = 0.
# Memory-reduction example
df["city"] = df["city"].astype("category")
df["age"] = pd.to_numeric(df["age"], downcast="integer")

For datasets that outgrow memory, consider Polars, Dask, or DuckDB, which keep a similar workflow with different scaling characteristics.

16. Visualization

Pandas has built-in plotting through Matplotlib:

import matplotlib.pyplot as plt

df["salary"].plot(kind="hist", bins=20)
df.groupby("city")["salary"].mean().plot(kind="bar")
ts["sales"].plot(figsize=(10, 4), title="Daily sales")
df.plot(kind="scatter", x="age", y="salary")
plt.tight_layout()
plt.show()

For more polished charts, use Seaborn or Plotly on top of DataFrames.

17. Common Pitfalls

  1. SettingWithCopyWarning: happens when you modify a slice that might be a copy. Use .loc for assignment, or call .copy() after filtering.
  2. Index misalignment: arithmetic between Series aligns on index labels, not position. Reset indexes if that surprises you.
  3. inplace=True: it rarely saves memory and breaks method chaining. Prefer reassignment: df = df.dropna().
  4. Mixed dtypes after reading CSVs: pass dtype= explicitly or clean with pd.to_numeric(..., errors="coerce").
  5. Forgetting reset_index() after groupby, which leaves group keys in the index.
  6. Many-to-many merges silently multiplying rows. Use validate= and check len() before and after.
  7. Using == with NaN: NaN == NaN is False. Use .isna().
  8. Row-by-row loops with iterrows() or apply(axis=1) on large data.

18. A Complete Worked Example

A typical cleaning-to-insight pipeline using method chaining:

import pandas as pd
import numpy as np

raw = pd.read_csv("sales.csv", parse_dates=["order_date"])

clean = (
    raw
    .rename(columns=str.lower)
    .drop_duplicates(subset="order_id")
    .assign(
        region=lambda d: d["region"].str.strip().str.title(),
        amount=lambda d: pd.to_numeric(d["amount"], errors="coerce"),
    )
    .dropna(subset=["amount", "order_date"])
    .query("amount > 0")
    .assign(month=lambda d: d["order_date"].dt.to_period("M"))
)

monthly = (
    clean
    .groupby(["month", "region"])
    .agg(revenue=("amount", "sum"), orders=("order_id", "nunique"))
    .reset_index()
)

monthly["avg_order"] = monthly["revenue"] / monthly["orders"]
monthly["growth"] = monthly.groupby("region")["revenue"].pct_change()

top_regions = (
    monthly.groupby("region")["revenue"].sum()
    .sort_values(ascending=False)
    .head(5)
)

monthly.to_csv("monthly_summary.csv", index=False)

19. Quick Reference Cheat Sheet

Task Code
Read CSV pd.read_csv("f.csv")
First rows df.head()
Summary df.info(), df.describe()
Select columns df[["a", "b"]]
Filter rows df[df["a"] > 5] or df.query("a > 5")
By label / position df.loc[...] / df.iloc[...]
New column df["c"] = df["a"] + df["b"]
Missing values df.isna().sum(), fillna, dropna
Change type df["a"].astype("int")
Sort df.sort_values("a")
Group and aggregate df.groupby("a")["b"].agg("mean")
Pivot pd.pivot_table(df, index=..., columns=..., values=...)
Join pd.merge(a, b, on="k", how="left")
Stack rows pd.concat([a, b])
Dates pd.to_datetime, .dt.year, .resample("M")
Save df.to_csv("out.csv", index=False)

20. Where to Go Next

  • Official docs: the pandas User Guide and API reference at pandas.pydata.org.
  • Practice on real datasets from Kaggle or government open-data portals.
  • Learn NumPy basics, Matplotlib or Seaborn for charts, and scikit-learn for modeling.
  • When data grows beyond memory, explore Polars, DuckDB, or Dask.

A good learning loop is: load a messy dataset, inspect it, clean it, ask three questions of it with groupby, and plot the answers. Repeating that on different datasets builds fluency faster than reading alone.


Display Only Empty Rows in Pandas

Rows where every value is empty:

df[df.isna().all(axis=1)]

Rows with at least one empty value:

df[df.isna().any(axis=1)]

Rows where a specific column is empty:

df[df["email"].isna()]

Empty strings or whitespace: CSV files often hold "" or " " rather than real NaN, and isna() misses those. Convert them first:

df2 = df.replace(r"^\s*$", np.nan, regex=True)
df2[df2.isna().all(axis=1)]

Alternatively, load the file with pd.read_csv("data.csv", na_values=["", " "]) so blanks become NaN from the start.

Counting them:

df.isna().all(axis=1).sum()    # fully empty rows
df.isna().any(axis=1).sum()    # rows with any gap

If you want to remove the empty rows rather than view them, use df.dropna(how="all").

To find empty columns instead of rows, use axis=0.

No comments:

Post a Comment