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.
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
locselects by label.ilocselects 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
forloops anditerrows(). Use column arithmetic,np.where,.str, and.dt. - Downcast types: use
int32,float32, orcategorywhere the range allows. - Load less: use
usecols,nrows, andchunksizeinread_csv. - Use Parquet instead of CSV for large, repeated workloads.
- Check memory:
df.memory_usage(deep=True).sum() / 1e6gives megabytes. - Avoid chained indexing like
df[df.a > 1]["b"] = 0; usedf.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
- SettingWithCopyWarning: happens when you modify a slice that might be a copy. Use
.locfor assignment, or call.copy()after filtering. - Index misalignment: arithmetic between Series aligns on index labels, not position. Reset indexes if that surprises you.
inplace=True: it rarely saves memory and breaks method chaining. Prefer reassignment:df = df.dropna().- Mixed dtypes after reading CSVs: pass
dtype=explicitly or clean withpd.to_numeric(..., errors="coerce"). - Forgetting
reset_index()aftergroupby, which leaves group keys in the index. - Many-to-many merges silently multiplying rows. Use
validate=and checklen()before and after. - Using
==with NaN:NaN == NaNisFalse. Use.isna(). - Row-by-row loops with
iterrows()orapply(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