Python Pandas Tutorial: A Complete Guide from Basics to Advanced

Series and DataFrames, reading files, selection, cleaning, groupby, merges, dates, statistics, Copy-on-Write and performance, then 10 small projects and interview questions. Every example was run on pandas 3.0.

Python Pandas Tutorial cover: a pandas groupby with named aggregation printing orders and revenue for North, South and West regions, with results of apply(axis=1) at 2.64 s, vectorized at 0.74 ms and the category dtype at 1.0 MB

Introduction

Most backend work ends up touching tabular data: a CSV export from a client, a report the finance team wants every Monday, a migration that has to reconcile two databases. Plain Python lists and dictionaries can do it, but the code gets long and slow quickly. Pandas is the library Python developers reach for at that point.

This guide starts from zero and goes all the way to production concerns. It assumes you know basic Python (lists, dictionaries, functions) and nothing about Pandas. Each chapter builds on the previous one and reuses the same two small datasets, so you can follow along in one notebook.

The two datasets used throughout

employees: six people, extending the table from the roadmap slides.

import pandas as pd

employees = pd.DataFrame(
    {
        "name": ["John", "Alex", "Sara", "Priya", "Omar", "Mei"],
        "age": [25, 30, 28, 35, 41, 29],
        "department": ["Engineering", "Data Science", "Product", "Engineering", "Sales", "Data Science"],
        "salary": [75000, 95000, 88000, 120000, 67000, 102000],
        "city": ["Kochi", "Bengaluru", "Kochi", "Chennai", "Bengaluru", "Kochi"],
    }
)
employees.to_csv("employees.csv", index=False)

orders: twelve e-commerce orders from January to March 2026, saved as orders.csv. Columns: order_id, order_date, customer_id, region (North, South, West), category (Electronics, Books, Home), quantity, unit_price and revenue (quantity times unit price).

    order_id order_date  customer_id region     category  quantity  unit_price  revenue
0       1001 2026-01-05            1  South  Electronics         1       24999    24999
1       1002 2026-01-18            2  North        Books         3         450     1350
2       1003 2026-01-29            1  South  Electronics         2        1999     3998
...
11      1012 2026-03-30            2  North        Books         2         650     1300

Chapter 1: Introduction to Pandas

What is Pandas?

Pandas is an open-source Python library for working with structured data: data arranged in rows and columns, like a spreadsheet or a database table. It gives you two main objects, the Series (one column) and the DataFrame (a whole table), plus hundreds of methods to load, clean, reshape, combine and summarise them.

A useful mental model: a DataFrame is a spreadsheet you control with code. Every click you would make in Excel (filter, sort, pivot, VLOOKUP) becomes one line of Python that you can rerun, test and review.

Why do we need it?

Suppose you need the average salary per department from a CSV. In plain Python you would open the file, parse each line, convert strings to numbers, build a dictionary of lists, then loop again to average. In Pandas:

pd.read_csv("employees.csv").groupby("department")["salary"].mean()
department
Data Science    98500.0
Engineering     97500.0
Product         88000.0
Sales           67000.0
Name: salary, dtype: float64

One line, and it handles type conversion, missing values and grouping for you. The heavy lifting runs in compiled code (NumPy, and increasingly Apache Arrow), so it is also far faster than a Python loop.

History and background

  • 2008: Wes McKinney started Pandas at AQR Capital Management to analyse financial data in Python.
  • 2009: released as open source. The name comes from "panel data", an econometrics term for multi-dimensional data.
  • 2020: version 1.0, a stable API after a decade of growth.
  • 2023: version 2.0 added optional PyArrow-backed data types.
  • 2026: version 3.0 made Copy-on-Write the only behaviour and introduced a dedicated string data type by default. Both changes appear throughout this guide.

What problems does Pandas solve?

  • Reading data from many formats (CSV, Excel, JSON, SQL, Parquet, HTML) into one common structure.
  • Cleaning messy real-world data: missing values, duplicates, wrong types, inconsistent text.
  • Selecting, filtering and transforming data without writing loops.
  • Summarising data by group, the equivalent of SQL's GROUP BY.
  • Joining datasets from different sources, the equivalent of SQL joins.
  • Working with dates and time series: resampling, rolling averages, time zones.
  • Writing the result back out as a report, a file or a database table.

Features and benefits

  • Labelled data. Rows and columns have names, so df["salary"] reads like English.
  • Automatic alignment. Operations match rows by label, not by position, which prevents a whole class of off-by-one bugs.
  • Missing data handling built into every method.
  • Vectorised operations that run in compiled code.
  • Rich I/O for more than a dozen file formats and SQL databases.
  • Time series tools that came from its finance origins.
  • Ecosystem. Matplotlib, Seaborn, scikit-learn, Jupyter and most data tools accept DataFrames directly.

Real-world applications

  • Backend development: bulk CSV imports, data migrations, admin report exports. My post on bulk CSV import and export pipelines covers the Django side of this.
  • Data analysis: sales, marketing and product dashboards.
  • Data engineering: ETL steps that clean data before it lands in a warehouse.
  • Finance: price series, returns, risk calculations.
  • Machine learning: feature preparation before training a model.
  • Testing and QA: comparing expected and actual datasets after a migration.

Pandas vs NumPy vs Excel vs SQL

PandasNumPyExcelSQL
Data shapeLabelled tables, mixed column typesN-dimensional arrays, one type per arraySheets of cellsTables in a database
Best atCleaning, reshaping, analysisFast numeric mathsQuick manual explorationStoring and querying large shared data
Size limitYour machine's RAMYour machine's RAM1,048,576 rows per sheetDisk; scales to billions of rows
RepeatableYes, it is codeYesManual steps are hard to repeatYes
LabelsRow and column labelsPositions onlyCell addressesColumn names
Missing dataNaN, NA, built-in handlingNaN for floats onlyBlank cellsNULL

These tools work together rather than compete. Pandas is built on NumPy. You often run a SQL query to fetch a filtered dataset, analyse it in Pandas, and export the result to Excel for a manager.

Chapter 2: Installation and Setup

Install with pip

Use a virtual environment so each project has its own package versions:

python -m venv .venv
source .venv/bin/activate            # Windows: .venv\Scripts\activate
pip install pandas                   # core library
pip install pyarrow openpyxl         # Parquet, faster CSV, Excel support
pip install matplotlib seaborn       # charts
pip install jupyterlab               # notebooks

pandas 3.0 needs Python 3.11 or newer. Optional features such as Excel or Parquet need extra packages; Pandas raises an ImportError naming the missing package when you call a method that needs it.

Install with Anaconda

Anaconda and Miniconda ship with Pandas and its scientific dependencies. If you use conda:

conda create -n analysis python=3.13 pandas pyarrow openpyxl matplotlib jupyterlab
conda activate analysis

Pick one installer per environment. Mixing pip and conda installs of the same package in one environment causes confusing version conflicts.

Import Pandas and check the version

import pandas as pd
import numpy as np

print(pd.__version__)
3.0.5

import pandas as pd imports the library under the short alias pd. The alias is a community convention: every tutorial, Stack Overflow answer and the official documentation uses it, so stick with it. NumPy is imported as np for the same reason.

For a full report of Pandas and every optional dependency, run pd.show_versions(). Paste its output when you report a bug.

Set up Jupyter

jupyter lab

Jupyter runs code in cells and shows each DataFrame as a formatted table under the cell. It is the most comfortable way to explore data. For production code (scheduled reports, ETL jobs) move the logic into normal .py files with tests.

Useful display options

pd.set_option("display.max_columns", 20)    # show up to 20 columns before truncating
pd.set_option("display.width", 120)         # wider console output
pd.set_option("display.precision", 2)       # 2 decimal places in printed floats

These only change how data is printed, never the data itself.

Chapter 3: Series and DataFrames

Everything in Pandas is built on two structures. Learn these well and the rest of the library makes sense.

What is a Series?

Definition: a Series is a one-dimensional labelled array. It holds values of one data type plus an index: a label for each value.

Purpose: a Series represents a single column of data, such as all salaries or all order dates. Think of it as a Python list with labels attached, or a dictionary whose values can be used in maths all at once.

Syntax:

pd.Series(data, index=None, dtype=None, name=None)

From a list

data = [10, 20, 30, 40]
s = pd.Series(data)
print(s)
0    10
1    20
2    30
3    40
dtype: int64

The left column is the index. Pandas created 0 to 3 automatically. dtype: int64 says every value is a 64-bit integer.

From a dictionary

salaries = pd.Series({"John": 75000, "Alex": 95000, "Sara": 88000}, name="salary")
print(salaries)
John    75000
Alex    95000
Sara    88000
Name: salary, dtype: int64

Dictionary keys become the index. The name becomes the column name if this Series later joins a DataFrame.

From a NumPy array, with a custom index

scores = pd.Series(np.array([82, 91, 77]), index=["math", "physics", "chemistry"])
print(scores.index)
print(scores.values)
Index(['math', 'physics', 'chemistry'], dtype='str')
[82 91 77]

Accessing elements

salaries["Alex"]                 # by label      -> 95000
salaries.iloc[0]                 # by position   -> 75000
salaries[salaries > 80000]       # by condition
Alex    95000
Sara    88000
Name: salary, dtype: int64

Series attributes and methods

print(salaries.dtype, salaries.size, salaries.name)
print(salaries.mean(), salaries.max(), salaries.idxmax())
print(salaries * 1.10)          # a 10% raise for everyone, no loop
int64 3 salary
86000.0 95000 Alex
John     82500.0
Alex    104500.0
Sara     96800.0
Name: salary, dtype: float64

salaries * 1.10 is a vectorised operation: Pandas applies it to every value in compiled code. This idea comes back in every chapter.

What is a DataFrame?

Definition: a DataFrame is a two-dimensional labelled table with rows and columns. Each column is a Series, and all columns share the same row index.

Purpose: it represents a whole dataset: a CSV file, an Excel sheet, a SQL query result. Different columns can hold different types (text, numbers, dates).

Syntax:

pd.DataFrame(data, index=None, columns=None, dtype=None)

From a dictionary of lists (most common)

data = {
    "Name": ["John", "Alex", "Sara"],
    "Age": [25, 30, 28],
}
df = pd.DataFrame(data)
print(df)
   Name  Age
0  John   25
1  Alex   30
2  Sara   28

Each key becomes a column name and each list becomes that column's values. All lists must have the same length.

From a list of dictionaries (typical API response)

rows = [
    {"name": "John", "age": 25},
    {"name": "Alex", "age": 30, "city": "Bengaluru"},
]
print(pd.DataFrame(rows))
   name  age       city
0  John   25        NaN
1  Alex   30  Bengaluru

Each dictionary becomes a row. Keys missing from a row become NaN ("not a number"), the standard missing-value marker.

From a list of lists, and from a NumPy array

pd.DataFrame([["John", 25], ["Alex", 30]], columns=["name", "age"])

rng = np.random.default_rng(seed=42)
pd.DataFrame(rng.integers(0, 100, size=(3, 4)), columns=["q1", "q2", "q3", "q4"])
   q1  q2  q3  q4
0   8  77  65  43
1  43  85   8  69
2  20   9  52  97

Rows, columns and labels

print(employees.shape)      # (rows, columns)
print(employees.columns)
print(employees.index)
print(employees.dtypes)
(6, 5)
Index(['name', 'age', 'department', 'salary', 'city'], dtype='str')
RangeIndex(start=0, stop=6, step=1)
name            str
age           int64
department      str
salary        int64
city            str
dtype: object
  • columns holds the column labels.
  • index holds the row labels. RangeIndex is the memory-efficient default 0, 1, 2 and so on.
  • dtypes shows each column's type. Text columns show str.

Series vs DataFrame

SeriesDataFrame
Dimensions1 (values + index)2 (rows and columns)
AnalogyOne spreadsheet columnA whole sheet
Data typesOne dtypeOne dtype per column
Get one from a DataFramedf["salary"]df[["salary"]] (double brackets)
Has.name.columns
type(employees["salary"])      # pandas.Series
type(employees[["salary"]])    # pandas.DataFrame

Single brackets return a Series. Double brackets pass a list of column names, so you get a DataFrame back even when the list has one name.

Chapter 4: Reading and Writing Data

Real data rarely starts in Python. Pandas has a pair of functions for each format: pd.read_*() returns a DataFrame, and df.to_*() writes one out.

FormatReadWriteExtra package
CSV / textpd.read_csv()df.to_csv()None (PyArrow optional for speed)
Excelpd.read_excel()df.to_excel()openpyxl
JSONpd.read_json()df.to_json()None
SQLpd.read_sql()df.to_sql()SQLAlchemy (or a sqlite3 connection)
Parquetpd.read_parquet()df.to_parquet()pyarrow
HTML tablespd.read_html()df.to_html()lxml

read_csv()

Definition: reads a delimited text file into a DataFrame. Purpose: CSV is the most common exchange format between systems, so this is the function you will call most.

df = pd.read_csv("employees.csv")
print(df.head(3))
   name  age    department  salary       city
0  John   25   Engineering   75000      Kochi
1  Alex   30  Data Science   95000  Bengaluru
2  Sara   28       Product   88000      Kochi

The first line becomes the column names and Pandas infers each column's type. Real files are rarely that clean. This European export uses semicolons, comma decimals and an NA marker:

order_id;order_date;amount;country
1001;2026-01-05;199,90;Deutschland
1002;2026-01-06;45,50;Österreich
1003;2026-01-06;NA;Schweiz
sales_eu = pd.read_csv(
    "sales_eu.csv",
    sep=";",                      # delimiter between fields
    decimal=",",                  # "199,90" means 199.90
    index_col="order_id",         # use this column as the row index
    parse_dates=["order_date"],   # convert to datetime while reading
    encoding="utf-8",             # file encoding
)
print(sales_eu)
print(sales_eu.dtypes)
         order_date  amount      country
order_id
1001     2026-01-05   199.9  Deutschland
1002     2026-01-06    45.5   Österreich
1003     2026-01-06     NaN      Schweiz
order_date    datetime64[us]
amount               float64
country                  str

The most useful parameters:

ParameterWhat it doesExample
sepField delimitersep=";", sep="\t"
headerWhich row holds column names; None if there is noneheader=None
namesColumn names to usenames=["name", "age"]
index_colColumn to use as the row indexindex_col="order_id"
usecolsRead only these columns (saves memory)usecols=["name", "salary"]
dtypeForce column typesdtype={"zip": "str"}
parse_datesParse these columns as datesparse_dates=["created"]
na_valuesExtra strings to treat as missingna_values=["-", "n/a"]
nrows, skiprowsRead part of a filenrows=1000
encodingText encodingencoding="latin-1"
chunksizeRead in pieces (Chapter 15)chunksize=100_000
engineParser; "pyarrow" is multi-threadedengine="pyarrow"

A file with no header row:

pd.read_csv("raw.csv", header=None, names=["name", "age", "department"])

File paths

A relative path such as "data/orders.csv" is resolved from the current working directory, which is wherever you started Python or Jupyter, not where your script lives. Build paths from the script's location to avoid surprises:

from pathlib import Path

DATA_DIR = Path(__file__).resolve().parent / "data"
orders = pd.read_csv(DATA_DIR / "orders.csv")

read_csv also accepts URLs, compressed files (.gz, .zip, detected from the extension) and file-like objects such as io.StringIO.

Common read errors

pd.read_csv("employes.csv")
FileNotFoundError: [Errno 2] No such file or directory: 'employes.csv'

Check the spelling and print Path.cwd() to see where Python is looking.

name,age
John,25
Alex,30,extra
pd.read_csv("bad.csv")
ParserError: Error tokenizing data. C error: Expected 2 fields in line 3, saw 3

A row has more fields than the header, usually an unquoted comma inside a value. Fix the export if you can. If you must load it, on_bad_lines="skip" drops those rows, but log how many you lost.

UnicodeDecodeError: 'utf-8' codec can't decode byte 0xe9 in position 13: invalid continuation byte

The file is not UTF-8. Files exported from older Windows tools are often latin-1 or cp1252: pd.read_csv("latin.csv", encoding="latin-1") read José and São Paulo correctly.

to_csv()

employees.to_csv("out.csv", index=False)
name,age,department,salary,city
John,25,Engineering,75000,Kochi

Best practice: pass index=False unless the index holds real data. Without it, Pandas writes the 0, 1, 2 index as an unnamed first column, and the next read_csv gives you a mystery Unnamed: 0 column.

read_excel() and to_excel()

Excel needs openpyxl for .xlsx files. Use ExcelWriter to write several sheets into one workbook:

with pd.ExcelWriter("report.xlsx") as writer:
    employees.to_excel(writer, sheet_name="Employees", index=False)
    employees.groupby("department")["salary"].mean().to_excel(writer, sheet_name="Avg salary")

avg = pd.read_excel("report.xlsx", sheet_name="Avg salary")
all_sheets = pd.read_excel("report.xlsx", sheet_name=None)   # dict of {sheet name: DataFrame}
print(avg)
print(list(all_sheets))
     department  salary
0  Data Science   98500
1   Engineering   97500
2       Product   88000
3         Sales   67000
['Employees', 'Avg salary']

sheet_name takes a name, a position (0 is the first sheet) or None for all sheets. Other useful options: skiprows for title rows above the table, and usecols="A:D" for a column range.

read_json() and to_json()

employees.head(2).to_json("employees.json", orient="records", indent=2)
pd.read_json("employees.json")
[
  {
    "name":"John",
    "age":25,
    "department":"Engineering",
    "salary":75000,
    "city":"Kochi"
  },
  ...
]

orient="records" writes a list of objects, the shape most REST APIs use. For nested JSON, pd.json_normalize() flattens inner objects into dotted columns:

orders_json = [
    {"id": 1, "customer": {"name": "John", "city": "Kochi"}, "total": 250},
    {"id": 2, "customer": {"name": "Sara", "city": "Chennai"}, "total": 410},
]
pd.json_normalize(orders_json)
   id  total customer.name customer.city
0   1    250          John         Kochi
1   2    410          Sara       Chennai

read_sql() and to_sql()

read_sql runs a query and returns the result as a DataFrame. It accepts a SQLAlchemy engine or connection for any database, or a plain sqlite3 connection for SQLite.

import sqlite3

conn = sqlite3.connect(":memory:")
employees.to_sql("employees", conn, index=False, if_exists="replace")

high_earners = pd.read_sql(
    "SELECT name, salary FROM employees WHERE salary > ? ORDER BY salary DESC",
    conn,
    params=(90000,),
)
print(high_earners)
    name  salary
0  Priya  120000
1    Mei  102000
2   Alex   95000

For PostgreSQL or MySQL, create an engine with SQLAlchemy: create_engine("postgresql+psycopg://user:pass@host/db"), and pass it instead of conn. Read credentials from environment variables, not from the code.

to_sql options worth knowing: if_exists ("fail", "replace", "append"), chunksize to insert in batches, and method="multi" to send several rows per INSERT. Filter in SQL rather than loading a whole table and filtering in Pandas; the database is better at it. My database optimization guide covers indexing for those queries.

read_parquet() and to_parquet()

Parquet is a compressed, column-oriented binary format. It stores the data types with the data, so dates stay dates and nothing is re-parsed.

employees.to_parquet("employees.parquet", index=False)
pd.read_parquet("employees.parquet", columns=["name", "salary"])   # read only two columns

On a 1,000,000-row orders file in my test:

ReadFile sizeTime (best of 5)
read_csv (default C engine)17.4 MB0.149 s
read_csv(engine="pyarrow")17.4 MB0.019 s
read_parquet6.2 MB0.015 s
read_parquet(columns=["revenue"])6.2 MB0.002 s

Use CSV to exchange data with people and other systems. Use Parquet for intermediate files in your own pipelines.

read_html()

read_html finds every <table> in an HTML page and returns a list of DataFrames:

from io import StringIO

html = """<table>
<tr><th>country</th><th>capital</th></tr>
<tr><td>India</td><td>New Delhi</td></tr>
<tr><td>Japan</td><td>Tokyo</td></tr>
</table>"""
tables = pd.read_html(StringIO(html))
print(tables[0])
  country    capital
0   India  New Delhi
1   Japan      Tokyo

Pass a URL, a file path, or literal HTML wrapped in StringIO. In pandas 3 a raw HTML string is treated as a file path and fails with FileNotFoundError. Check a site's terms before scraping it.

Chapter 5: Exploring and Understanding Data

When you receive an unfamiliar dataset, do not start analysing straight away. Spend two minutes answering: how big is it, what are the columns and types, what is missing, and what do typical values look like? These methods answer those questions.

head(), tail() and sample()

employees.head(2)                         # first 2 rows (default 5)
employees.tail(2)                         # last 2 rows
employees.sample(n=2, random_state=1)     # 2 random rows, repeatable
   name  age    department  salary       city
4  Omar   41         Sales   67000  Bengaluru
5   Mei   29  Data Science  102000      Kochi

head shows the format. sample is better at spotting problems, because files are often sorted and the first rows are the cleanest.

info()

employees.info()
<class 'pandas.DataFrame'>
RangeIndex: 6 entries, 0 to 5
Data columns (total 5 columns):
 #   Column      Non-Null Count  Dtype
---  ------      --------------  -----
 0   name        6 non-null      str
 1   age         6 non-null      int64
 2   department  6 non-null      str
 3   salary      6 non-null      int64
 4   city        6 non-null      str
dtypes: int64(2), str(3)
memory usage: 494.0 bytes

One call gives row count, column names, non-null counts (so you spot missing data) and types (so you spot numbers stored as text). Run it first on every new dataset.

describe()

employees.describe()
             age         salary
count   6.000000       6.000000
mean   31.333333   91166.666667
std     5.750362   19072.668053
min    25.000000   67000.000000
25%    28.250000   78250.000000
50%    29.500000   91500.000000
75%    33.750000  100250.000000
max    41.000000  120000.000000

Summary statistics for numeric columns. Look for impossible values: a negative age, a maximum far above the 75th percentile (an outlier or a typo). For text columns:

employees.describe(include="str")
        name   department   city
count      6            6      6
unique     6            4      3
top     John  Engineering  Kochi
freq       1            2      3

In pandas 3, include="object" still matches text columns but raises a deprecation warning. Use include="str".

shape, size, columns, index and dtypes

employees.shape       # (6, 5): rows, columns
employees.size        # 30: rows x columns
len(employees)        # 6: rows only
employees.columns     # column labels
employees.index       # row labels
employees.dtypes      # type of each column

These are attributes, not methods, so there are no parentheses.

memory_usage()

employees.memory_usage(deep=True)
Index         132
name           72
age            48
department    106
salary         48
city           88
dtype: int64

Bytes per column. deep=True measures the actual text content, not just pointers. Chapter 15 uses this to shrink large DataFrames.

value_counts(), unique() and nunique()

employees["department"].value_counts()
employees["department"].value_counts(normalize=True).round(2)   # proportions
employees["city"].unique()
employees["city"].nunique()
department
Engineering     2
Data Science    2
Product         1
Sales           1
Name: count, dtype: int64

<ArrowStringArray>
['Kochi', 'Bengaluru', 'Chennai']
Length: 3, dtype: str

3

value_counts is the fastest way to find inconsistent categories. If you see "Kochi", "kochi" and "Kochi " as three separate rows, you know the column needs cleaning (Chapter 10). Add dropna=False to count missing values too.

A five-minute exploration checklist

df = pd.read_csv("unknown.csv")
df.shape                       # how big?
df.info()                      # types and missing counts
df.sample(5, random_state=0)   # what do rows look like?
df.describe()                  # numeric ranges, outliers
df.isna().sum()                # missing values per column
df.duplicated().sum()          # fully duplicated rows
df.nunique()                   # IDs (all unique) vs categories (few unique)

Chapter 6: Selecting and Accessing Data

Selection is where most beginners get confused, because Pandas offers several ways to do it. The rule of thumb: use [] for columns, .loc for labels, .iloc for positions.

Columns

employees["name"]                  # one column -> Series
employees[["name", "salary"]]      # several columns -> DataFrame

employees.name (attribute access) also works, but fails for names with spaces or names that clash with methods such as count. Prefer brackets.

loc[]: select by label

Definition: .loc selects rows and columns by their labels. Syntax: df.loc[row_labels, column_labels].

employees.loc[0, "name"]                     # 'John'
employees.loc[1:3, ["name", "salary"]]       # rows labelled 1 to 3 INCLUSIVE
    name  salary
1   Alex   95000
2   Sara   88000
3  Priya  120000

Labels become more meaningful with a custom index:

by_name = employees.set_index("name")
by_name.loc["Sara"]                          # one row -> Series
by_name.loc["Alex":"Priya", "salary"]        # label slice
name
Alex      95000
Sara      88000
Priya    120000
Name: salary, dtype: int64

iloc[]: select by position

Definition: .iloc selects by integer position, counting from 0 like a Python list. Syntax: df.iloc[row_positions, column_positions].

employees.iloc[0, 0]                 # 'John': row 0, column 0
employees.iloc[0, 1]                 # 25: row 0, column 1 is "age"
employees.iloc[1:3, [0, 3]]          # rows 1 and 2 (end EXCLUDED), columns 0 and 3
   name  salary
1  Alex   95000
2  Sara   88000

at[] and iat[]: one value, fast

by_name.at["Mei", "salary"]     # 102000, by label
employees.iat[2, 3]             # 88000, by position

at and iat only fetch or set a single cell, and they skip the overhead of the general indexers. Use them inside rare unavoidable loops.

Boolean indexing

Definition: pass a Series of True/False values (a mask) and Pandas keeps the rows where it is True. This is how filtering works.

mask = employees["age"] > 25        # a boolean Series
employees[mask]
    name  age    department  salary       city
1   Alex   30  Data Science   95000  Bengaluru
2   Sara   28       Product   88000      Kochi
3  Priya   35   Engineering  120000    Chennai
4   Omar   41         Sales   67000  Bengaluru
5    Mei   29  Data Science  102000      Kochi

Multiple conditions

employees[(employees["department"] == "Engineering") & (employees["salary"] > 100000)]   # AND
employees[(employees["city"] == "Chennai") | (employees["age"] < 26)]                    # OR
employees[~(employees["city"] == "Kochi")]                                               # NOT
    name  age   department  salary     city
3  Priya   35  Engineering  120000  Chennai

Use &, | and ~, not Python's and, or and not, and wrap each condition in parentheses. Without them, operator precedence evaluates 25 & employees["salary"] first and you get:

ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

isin() and between()

employees[employees["city"].isin(["Chennai", "Bengaluru"])]        # value in a list
employees[employees["salary"].between(80000, 100000)]              # inclusive range

isin replaces a chain of == conditions joined with |. between includes both ends by default; change it with inclusive="left", "right" or "neither".

query()

min_salary = 90000
employees.query("department == 'Data Science' and salary > @min_salary")
   name  age    department  salary       city
1  Alex   30  Data Science   95000  Bengaluru
5   Mei   29  Data Science  102000      Kochi

query takes the condition as a string, which is easier to read for long filters. Reference Python variables with @ and wrap column names containing spaces in backticks. Never build the query string from user input.

filter()

Despite the name, DataFrame.filter selects columns or index labels by name, not rows by value:

employees.filter(items=["name", "city"])     # exact names
employees.filter(like="a", axis=1)           # names containing "a"
employees.filter(regex="^(name|sal)")        # names matching a regex

It is handy for wide datasets, for example selecting every column that starts with q1_.

Chapter 7: Adding, Updating and Removing Data

Add columns

emp = employees.copy()
emp["bonus"] = emp["salary"] * 0.10          # computed from another column
emp["is_senior"] = emp["age"] >= 30           # boolean column
print(emp.head(3))
   name  age    department  salary       city   bonus  is_senior
0  John   25   Engineering   75000      Kochi  7500.0      False
1  Alex   30  Data Science   95000  Bengaluru  9500.0       True
2  Sara   28       Product   88000      Kochi  8800.0      False

assign(): add columns in a chain

result = employees.assign(
    monthly=lambda d: (d["salary"] / 12).round(0),
    tax=lambda d: d["salary"] * 0.2,
)
   name  monthly      tax
0  John   6250.0  15000.0
1  Alex   7917.0  19000.0
2  Sara   7333.0  17600.0

assign returns a new DataFrame instead of changing the original, so it fits in method chains (df.query(...).assign(...).sort_values(...)). The lambda d: receives the DataFrame at that point in the chain.

insert(): add a column at a position

emp = employees.copy()
emp.insert(1, "emp_id", ["E1", "E2", "E3", "E4", "E5", "E6"])   # becomes column 1

insert changes the DataFrame in place and raises an error if the column already exists.

Add rows

new_hires = pd.DataFrame([
    {"name": "Ravi", "age": 27, "department": "Product", "salary": 81000, "city": "Chennai"},
])
emp = pd.concat([employees, new_hires], ignore_index=True)
   name  age    department  salary     city
5   Mei   29  Data Science  102000    Kochi
6  Ravi   27       Product   81000  Chennai

DataFrame.append() was removed in pandas 2.0; pd.concat is its replacement. Adding rows one at a time in a loop is slow, because each concat copies everything. Collect rows in a Python list and build the DataFrame once at the end. For a single quick row, emp.loc[len(emp)] = [...] also works on a default index.

Update values

emp = employees.copy()
emp.loc[emp["department"] == "Sales", "salary"] = 70000     # conditional update
emp.at[0, "city"] = "Thrissur"                               # one cell

Always update with one .loc[row_condition, column] call. Chapter 15 explains why emp[emp["department"] == "Sales"]["salary"] = 70000 silently does nothing.

rename()

employees.rename(columns={"name": "full_name", "salary": "annual_salary"})
employees.rename(columns=str.upper)                   # apply a function to every name
employees.rename(index={0: "first"})                  # rename row labels
['full_name', 'age', 'department', 'annual_salary', 'city']
['NAME', 'AGE', 'DEPARTMENT', 'SALARY', 'CITY']

A common clean-up after reading a spreadsheet: df.columns = df.columns.str.strip().str.lower().str.replace(" ", "_").

drop() and pop()

employees.drop(columns=["city", "age"])     # remove columns
employees.drop(index=[0, 1])                # remove rows by label

emp = employees.copy()
cities = emp.pop("city")                    # remove AND return the column

Use the explicit columns= and index= keywords rather than axis=1; the code says what it does.

drop_duplicates()

signups = pd.DataFrame({
    "email": ["a@x.com", "b@x.com", "a@x.com", "c@x.com"],
    "plan": ["free", "pro", "pro", "free"],
})
signups.duplicated()                                       # all False: no row is a full duplicate
signups.duplicated(subset=["email"])                       # row 2 repeats an email
signups.drop_duplicates(subset=["email"], keep="last")     # keep the latest signup
     email  plan
1  b@x.com   pro
2  a@x.com   pro
3  c@x.com  free

subset decides which columns define "the same record". keep chooses "first" (default), "last" or False (drop every copy).

set_index() and reset_index()

indexed = employees.set_index("name")           # "name" becomes the row labels
indexed.reset_index()                            # back to a column, with 0..n index

filtered = employees[employees["age"] > 28]
filtered.index.tolist()                          # [1, 3, 4, 5]: gaps after filtering
filtered.reset_index(drop=True).index.tolist()   # [0, 1, 2, 3]

drop=True throws the old index away instead of turning it into a column. Use it after filtering or concatenating when the old row numbers mean nothing.

Sorting rows and columns

employees.sort_values(by="age", ascending=False)
employees.sort_values(by=["department", "salary"], ascending=[True, False])
employees.sort_index(axis=1)                    # columns in alphabetical order
employees.nlargest(2, "salary")                  # top 2 without a full sort
    name  age    department  salary       city
5    Mei   29  Data Science  102000      Kochi
1   Alex   30  Data Science   95000  Bengaluru
3  Priya   35   Engineering  120000    Chennai
0   John   25   Engineering   75000      Kochi
2   Sara   28       Product   88000      Kochi
4   Omar   41         Sales   67000  Bengaluru

The second call sorts by department A to Z, then by salary high to low within each department. Missing values go last by default (na_position="first" changes that).

Chapter 8: Data Cleaning and Missing Values

Data cleaning means fixing or removing data that is missing, duplicated, wrongly typed or inconsistent, before you analyse it. Analysts commonly say it takes most of their time. A wrong total caused by a duplicate row is worse than no total at all.

Understanding missing values

Pandas marks missing data with NaN (from NumPy, for floats), NaT (for datetimes), None or pd.NA (for the newer nullable types). The isna family treats all of them as missing.

raw = pd.DataFrame({
    "name": ["John", "Alex", "Sara", None, "Mei"],
    "age": [25, np.nan, 28, 35, np.nan],
    "department": ["Engineering", "Data Science", None, "Engineering", "Data Science"],
    "salary": [75000, 95000, 88000, np.nan, 102000],
})
print(raw)
   name   age    department    salary
0  John  25.0   Engineering   75000.0
1  Alex   NaN  Data Science   95000.0
2  Sara  28.0           NaN   88000.0
3   NaN  35.0   Engineering       NaN
4   Mei   NaN  Data Science  102000.0

Notice age and salary became floats (25.0). Classic NumPy integers cannot hold NaN, so Pandas converts the column to float. The nullable Int64 type in Chapter 15 avoids this.

isna(), isnull(), notna() and notnull()

raw.isna().sum()                  # missing values per column
raw.isna().sum().sum()            # total missing cells: 5
raw[raw["age"].isna()]            # rows where age is missing
raw.notna().all(axis=1)           # True for rows with no missing values
name          1
age           2
department    1
salary        1
dtype: int64

isnull is an exact alias of isna, and notnull of notna. They exist for historical reasons; pick one pair and use it consistently.

fillna()

raw.fillna({
    "age": raw["age"].median(),     # numeric: median ignores extreme values
    "department": "Unknown",        # text: an explicit placeholder
    "salary": 0,
})
   name   age    department    salary
0  John  25.0   Engineering   75000.0
1  Alex  28.0  Data Science   95000.0
2  Sara  28.0       Unknown   88000.0
3   NaN  35.0   Engineering       0.0
4   Mei  28.0  Data Science  102000.0

Passing a dictionary fills each column differently, which is almost always what you want. Choose the fill value per column based on meaning: 0 is right for "number of returns" and wrong for "salary", where it would drag the average down.

For ordered data such as sensor readings, carry the last known value forward or the next one backward:

temps = pd.Series([31.0, np.nan, np.nan, 29.5, np.nan])
temps.ffill().tolist()     # [31.0, 31.0, 31.0, 29.5, 29.5]
temps.bfill().tolist()     # [31.0, 29.5, 29.5, 29.5, nan]

In pandas 3, fillna(method="ffill") raises TypeError: ... unexpected keyword argument 'method'. Use ffill() and bfill().

dropna()

raw.dropna()                       # drop rows with ANY missing value -> only John left
raw.dropna(subset=["name"])        # drop only rows missing a name
raw.dropna(thresh=3)               # keep rows with at least 3 non-missing values
raw.dropna(axis=1)                 # drop columns with any missing value

Plain dropna() kept 1 row out of 5 here. On real data it can silently remove most of your dataset. Use subset to drop only rows missing values you truly need, and compare len(df) before and after.

interpolate()

readings = pd.Series([10.0, np.nan, np.nan, 16.0, 18.0])
readings.interpolate().tolist()    # [10.0, 12.0, 14.0, 16.0, 18.0]

Fills gaps by drawing a straight line between known neighbours. It suits smoothly changing measurements (temperature, stock levels over time), not categories. For time-indexed data, interpolate(method="time") accounts for uneven gaps between timestamps.

Fixing wrong data types

A numeric column with one bad value is read as text. Arithmetic on it then fails or, worse, concatenates strings.

messy = pd.DataFrame({"price": ["1,200", "850", "n/a", "2,050"], "qty": ["3", "1", "2", "x"]})

messy["price"] = pd.to_numeric(messy["price"].str.replace(",", ""), errors="coerce")
messy["qty"] = pd.to_numeric(messy["qty"], errors="coerce").astype("Int64")
print(messy)
print(messy.dtypes)
    price   qty
0  1200.0     3
1   850.0     1
2     NaN     2
3  2050.0  <NA>
price    float64
qty        Int64

errors="coerce" turns unparseable values into missing values instead of raising. Count them afterwards (messy["price"].isna().sum()) so bad data does not vanish unnoticed. pd.to_datetime(..., errors="coerce") does the same for dates.

Inconsistent values

status = pd.Series(["Active", "active ", "ACTIVE", "inactive", "Inactve", "N/A"])
clean = (
    status.str.strip()
    .str.lower()
    .replace({"inactve": "inactive", "n/a": pd.NA})
)
clean.value_counts(dropna=False)
active      3
inactive    2
NaN         1
Name: count, dtype: int64

Normalise case and whitespace first, then map known typos with replace. Run value_counts() before and after to confirm every variant was caught.

Validating data

Turn your assumptions into checks that run every time the pipeline runs:

orders_check = pd.DataFrame({
    "order_id": [1, 2, 3, 3],
    "qty": [2, -1, 5, 1],
    "email": ["a@x.com", "bad", "c@x.com", "d@x.com"],
})
problems = {
    "duplicate_ids": int(orders_check["order_id"].duplicated().sum()),
    "negative_qty": int((orders_check["qty"] < 0).sum()),
    "bad_email": int((~orders_check["email"].str.contains(r"^[^@\s]+@[^@\s]+\.[a-z]+$")).sum()),
}
print(problems)
{'duplicate_ids': 1, 'negative_qty': 1, 'bad_email': 1}

In a production job, fail loudly (raise an exception or send an alert) when a count is above zero, rather than writing bad data downstream. Libraries such as Pandera and Great Expectations formalise these checks as schemas.

Chapter 9: Data Transformation and Manipulation

Transformation means deriving new values from existing ones: mapping codes to labels, bucketing numbers, capping outliers, ranking. Pandas offers a ladder of tools, from fast and restrictive to slow and flexible. Climb only as high as you need.

Vectorised operations: the default

A vectorised operation works on a whole column at once in compiled code. Arithmetic (+ - * /), comparisons, .str and .dt methods and most built-in methods are vectorised.

orders["revenue"] = orders["quantity"] * orders["unit_price"]

I timed four ways to compute price * qty on 1,000,000 rows:

ApproachTime
for _, row in df.iterrows() (measured on 100k rows, scaled)about 8.5 s
df.apply(lambda r: r["price"] * r["qty"], axis=1)2.64 s
Python zip loop over the two columns0.100 s
df["price"] * df["qty"]0.74 ms

The vectorised version was about 3,500 times faster than apply with axis=1. That gap is the single most important performance fact in Pandas.

map(): transform each value of a Series

Definition: Series.map replaces each value using a dictionary, a Series or a function.

codes = {"Engineering": "ENG", "Data Science": "DS", "Product": "PRD"}
employees["department"].map(codes).tolist()
['ENG', 'DS', 'PRD', 'ENG', nan, 'DS']

Values missing from the dictionary ("Sales") become NaN. That is useful for spotting unmapped codes, and a trap if you expected them to stay unchanged; use replace for that.

employees["salary"].map(lambda s: f"₹{s:,}").head(3).tolist()   # ['₹75,000', '₹95,000', '₹88,000']

apply(): run a function on values, rows or columns

def salary_band(salary):
    if salary >= 100000:
        return "high"
    if salary >= 85000:
        return "mid"
    return "entry"

employees["salary"].apply(salary_band).tolist()
['entry', 'mid', 'mid', 'high', 'entry', 'high']

On a DataFrame, apply passes each column (default) or each row (axis=1) to your function:

employees[["age", "salary"]].apply(lambda col: col.max() - col.min())   # range per column
employees.apply(lambda row: f"{row['name']} ({row['department']})", axis=1)
age          16
salary    53000
dtype: int64

The salary_band logic can be vectorised with np.select or pd.cut, which give the same result in compiled code:

np.select(
    [employees["salary"] >= 100000, employees["salary"] >= 85000],
    ["high", "mid"],
    default="entry",
)

pd.cut(employees["salary"], bins=[0, 85000, 100000, np.inf],
       labels=["entry", "mid", "high"], right=False)

DataFrame.map() (formerly applymap)

prices = pd.DataFrame({"a": [1.234, 2.5], "b": [3.14159, 4.0]})
prices.map(lambda x: f"{x:.2f}")
      a     b
0  1.23  3.14
1  2.50  4.00

DataFrame.map applies a function to every cell. It was called applymap until pandas 2.1; applymap was deprecated then and is removed in pandas 3.

map vs apply vs vectorised

ToolWorks onAcceptsSpeedUse when
Vectorised opsWhole columnsOperators, built-in methodsFastestAlways try first
Series.mapEach value of a SeriesDict, Series, functionFast with a dictLookups and code-to-label mapping
Series.applyEach value of a SeriesFunctionPython speedLogic with no vectorised equivalent
DataFrame.applyEach column or rowFunctionSlow with axis=1Per-column summaries; rows only as a last resort
DataFrame.mapEach cellFunctionPython speedCell-wise formatting

replace() and astype()

employees["city"].replace({"Bengaluru": "Bangalore"})     # unmatched values stay unchanged
pd.Series([0, -1, 5, -1]).replace(-1, np.nan)               # sentinel value to missing

pd.Series(["1", "2", "3"]).astype("int64").sum()            # 6
employees["department"].astype("category")                  # see Chapter 15

astype raises if any value cannot be converted. For dirty data, use pd.to_numeric(..., errors="coerce") instead.

where() and mask()

employees["salary"].where(employees["salary"] < 100000, 100000).tolist()
employees["salary"].mask(employees["salary"] < 80000, 80000).tolist()
[75000, 95000, 88000, 100000, 67000, 100000]
[80000, 95000, 88000, 120000, 80000, 102000]

where(cond, other) keeps values where the condition is true and replaces the rest. mask(cond, other) does the opposite: it replaces where the condition is true. Read mask as "hide these".

clip(), round() and abs()

changes = pd.Series([-12.456, 3.14159, 150.5, -0.5])
changes.clip(lower=-10, upper=100).tolist()   # [-10.0, 3.14159, 100.0, -0.5]
changes.round(1).tolist()                     # [-12.5, 3.1, 150.5, -0.5]
changes.abs().tolist()                        # [12.456, 3.14159, 150.5, 0.5]

clip caps outliers at a floor and ceiling, which is common before plotting or training a model.

rank()

emp = employees[["name", "salary"]].copy()
emp["rank"] = emp["salary"].rank(ascending=False).astype(int)

pd.Series([100, 90, 90, 80]).rank(ascending=False).tolist()                  # [1.0, 2.5, 2.5, 4.0]
pd.Series([100, 90, 90, 80]).rank(method="dense", ascending=False).tolist()  # [1.0, 2.0, 2.0, 3.0]

Ties get the average rank by default (2.5). method="min" gives both 2 like a sports table, and "dense" leaves no gaps after ties.

sort_values() and sort_index() were covered in Chapter 7, and rename() too.

Chapter 10: Working with Strings

Text columns expose vectorised string methods through the .str accessor. They mirror Python's str methods but run on a whole column and skip missing values instead of crashing.

customers = pd.DataFrame({
    "name": ["  john SMITH ", "alex o'neil", "SARA khan", None],
    "email": ["John.Smith@Gmail.COM ", "alex@company.io", "sara.khan@yahoo.com", "bad-email"],
    "phone": ["+91 98470-12345", "98470 55555", "(0484) 2345678", "n/a"],
    "product": ["iPhone 15 Pro - 256GB", "Galaxy S24 - 128GB", "Pixel 9 - 256GB", "iPhone 15 - 128GB"],
})

Case and whitespace

customers["name"].str.strip().str.title().tolist()
customers["email"].str.strip().str.lower().tolist()
customers["name"].str.upper().tolist()
['John Smith', "Alex O'Neil", 'Sara Khan', nan]
['john.smith@gmail.com', 'alex@company.io', 'sara.khan@yahoo.com', 'bad-email']
['  JOHN SMITH ', "ALEX O'NEIL", 'SARA KHAN', nan]

strip() removes whitespace from both ends; lstrip() and rstrip() remove it from one side. All three accept characters to remove: pd.Series(["--abc--"]).str.lstrip("-") gives "abc--". Missing values stay missing through the whole chain. Lowercase emails before comparing or deduplicating them.

replace()

customers["phone"].str.replace(r"\D", "", regex=True).tolist()           # keep digits only
customers["product"].str.replace(" - ", " | ", regex=False).tolist()     # literal text
['919847012345', '9847055555', '04842345678', '']
['iPhone 15 Pro | 256GB', 'Galaxy S24 | 128GB', 'Pixel 9 | 256GB', 'iPhone 15 | 128GB']

Always pass regex= explicitly. \D means "any non-digit", so the first line normalises phone numbers in every format to digits.

split()

parts = customers["product"].str.split(" - ", expand=True)
parts.columns = ["model", "storage"]
print(parts)
           model storage
0  iPhone 15 Pro   256GB
1     Galaxy S24   128GB
2        Pixel 9   256GB
3      iPhone 15   128GB

Without expand=True, each cell holds a list. With it, each piece becomes its own column. Use n=1 to split only on the first separator.

contains(), startswith(), endswith() and len()

customers["product"].str.contains("iphone", case=False).tolist()     # [True, False, False, True]
customers["product"].str.startswith("Pixel").tolist()                # [False, False, True, False]
customers["email"].str.strip().str.lower().str.endswith(".com").tolist()
customers["name"].str.len().tolist()                                 # [13.0, 11.0, 9.0, nan]
customers["name"].str.contains("john", case=False, na=False)         # missing -> False

These return boolean masks for filtering: customers[customers["product"].str.contains("iphone", case=False)]. contains treats the pattern as a regex by default; pass regex=False when searching for text containing ., + or (. Pass na=False so missing values do not break the filter.

extract(): pull structured data out of text

customers["product"].str.extract(r"(?P<storage_gb>\d+)GB")
customers["email"].str.strip().str.lower().str.extract(r"@(?P<domain>[\w.-]+)$")
  storage_gb          domain
0        256       gmail.com
1        128      company.io
2        256       yahoo.com
3        128             NaN

Each regex capture group (the part in parentheses) becomes a column, and ?P<name> names it. Rows with no match get NaN. extractall returns every match, not just the first.

match() and fullmatch()

pattern = r"^[\w.+-]+@[\w-]+\.[\w.]+$"
customers["email"].str.strip().str.match(pattern).tolist()      # [True, True, True, False]

match anchors at the start of the string only; fullmatch requires the whole string to match, so you can drop the ^ and $. contains searches anywhere. For validation, prefer fullmatch.

Slicing and joining

customers["name"].str.strip().str[:4].tolist()               # first 4 characters
customers["name"].str.strip().str.title().str.cat(sep=", ")  # 'John Smith, Alex O'Neil, Sara Khan'

Regular expressions quick reference

PatternMatches
\d / \DA digit / a non-digit
\w / \sLetter, digit or underscore / whitespace
+, *, ?One or more, zero or more, optional
^, $Start, end of string
[abc], [^abc]Any of, none of these characters
(...)Capture group (used by extract)

Write regex patterns as raw strings (r"\d+") so Python does not treat the backslashes as escape sequences.

Chapter 11: Grouping and Aggregation

What is GroupBy?

GroupBy answers "per something" questions: revenue per region, average salary per department, orders per customer. It follows a pattern called split-apply-combine:

employees                      split by department          apply mean()       combine
name   department   salary     Engineering: 75000, 120000 ->  97500     ->  Data Science  98500
John   Engineering   75000     Data Science: 95000, 102000 -> 98500         Engineering   97500
Alex   Data Science  95000     Product:      88000        ->  88000         Product       88000
...                            Sales:        67000        ->  67000         Sales         67000

It is the Pandas version of SQL's SELECT department, AVG(salary) FROM employees GROUP BY department.

groupby() with a single aggregation

employees.groupby("department")["salary"].mean()
employees.groupby("department")["salary"].sum().sort_values(ascending=False)
department
Data Science    197000
Engineering     195000
Product          88000
Sales            67000
Name: salary, dtype: int64

groupby("department") alone computes nothing; it returns a GroupBy object waiting for an aggregation. Select the column(s) you need before aggregating, so Pandas does not compute results for every column.

The built-in aggregations are all fast: sum, mean, median, min, max, count, size, std, var, first, last, nunique.

agg(): several statistics at once

orders.groupby("category")["revenue"].agg(["sum", "mean", "median", "min", "max", "count"])
                sum          mean   median   min    max  count
category
Books          6845   1711.250000   1672.5  1300   2200      4
Electronics  124993  24998.600000  24999.0  3998  54999      5
Home           8494   2831.333333   2598.0  2397   3499      3

Named aggregation: the form to use in real code

summary = orders.groupby("region").agg(
    orders=("order_id", "count"),
    units=("quantity", "sum"),
    revenue=("revenue", "sum"),
    avg_order=("revenue", "mean"),
)
print(summary.round(0))
        orders  units  revenue  avg_order
region
North        4      9    60046    15012.0
South        5     14    65190    13038.0
West         3      4    15096     5032.0

Each keyword becomes an output column: new_name=(source_column, function). You get flat, readable column names instead of a two-level header. aggregate is an alias of agg, and a dictionary also works: .agg({"revenue": "sum", "quantity": "max"}).

Grouping by multiple columns

orders.groupby(["region", "category"])["revenue"].sum()
region  category
North   Books           2650
        Electronics    54999
        Home            2397
South   Books           4195
        Electronics    60995
West    Electronics     8999
        Home            6097
Name: revenue, dtype: int64

The result has a two-level MultiIndex (Chapter 15). Pass as_index=False to get ordinary columns back, which is easier to export or merge:

orders.groupby(["region", "category"], as_index=False)["revenue"].sum()

count() vs size()

scores = pd.DataFrame({"team": ["A", "A", "B", "B", "B"], "score": [10, np.nan, 7, 8, np.nan]})
scores.groupby("team")["score"].count()    # A: 1, B: 2  (non-missing values)
scores.groupby("team").size()              # A: 2, B: 3  (all rows)

transform(): group results aligned to the original rows

agg returns one row per group. transform returns one value per original row, so you can compare each row with its group:

emp = employees.copy()
emp["dept_avg"] = emp.groupby("department")["salary"].transform("mean")
emp["vs_dept_avg"] = emp["salary"] - emp["dept_avg"]
    name    department  salary  dept_avg  vs_dept_avg
0   John   Engineering   75000   97500.0     -22500.0
1   Alex  Data Science   95000   98500.0      -3500.0
2   Sara       Product   88000   88000.0          0.0
3  Priya   Engineering  120000   97500.0      22500.0
4   Omar         Sales   67000   67000.0          0.0
5    Mei  Data Science  102000   98500.0       3500.0

This replaces the clumsy "aggregate, then merge back" pattern. Typical uses: percentage of group total, filling missing values with the group mean, z-scores within a group.

filter(): keep or drop whole groups

orders.groupby("customer_id").filter(lambda g: len(g) >= 3)   # customers with 3+ orders
    order_id  customer_id  revenue
0       1001            1    24999
1       1002            2     1350
2       1003            1     3998
5       1006            2    54999
7       1008            1     2200
11      1012            2     1300

The function receives each group as a DataFrame and returns True or False for the whole group. Do not confuse it with DataFrame.filter from Chapter 6.

Custom aggregations and top-N per group

def price_range(s):
    return s.max() - s.min()

orders.groupby("category")["unit_price"].agg(price_range)

# Largest order in each region
orders.sort_values("revenue", ascending=False).groupby("region").head(1)
   region  order_id  revenue
5   North      1006    54999
10  South      1011    31998
8    West      1009     8999

Custom functions run in Python per group, so they are slower than built-ins. Here agg(["max", "min"]) followed by a subtraction would be the vectorised version.

Chapter 12: Combining and Reshaping Data

concat(): stack DataFrames

q1 = pd.DataFrame({"month": ["Jan", "Feb"], "revenue": [120, 150]})
q2 = pd.DataFrame({"month": ["Apr", "May"], "revenue": [170, 160]})

pd.concat([q1, q2], ignore_index=True)           # rows on top of each other
pd.concat([q1, q2], keys=["Q1", "Q2"])           # remember which frame each row came from
  month  revenue
0   Jan      120
1   Feb      150
2   Apr      170
3   May      160

concat glues frames along an axis; it does not match rows on a key. With axis=1 it places frames side by side, aligned on the index. Use it for monthly files with the same columns.

merge(): join on key columns

Definition: merge combines two DataFrames by matching values in one or more key columns, exactly like a SQL join. The roadmap example:

df1 = pd.DataFrame({"id": [1, 2, 3], "name": ["Alice", "Bob", "Charlie"]})
df2 = pd.DataFrame({"id": [1, 2, 3], "dept": ["Data", "Eng", "Product"]})
pd.merge(df1, df2, on="id")
   id     name     dept
0   1    Alice     Data
1   2      Bob      Eng
2   3  Charlie  Product

Real data rarely matches perfectly. The how parameter decides what happens to rows without a partner:

customers = pd.DataFrame({"customer_id": [1, 2, 3, 4], "name": ["John", "Alex", "Sara", "Priya"]})
cust_orders = pd.DataFrame({"order_id": [501, 502, 503, 504],
                            "customer_id": [1, 1, 3, 9], "amount": [250, 120, 310, 90]})

customers.merge(cust_orders, on="customer_id", how="outer", indicator=True)
   customer_id   name  order_id  amount      _merge
0            1   John     501.0   250.0        both
1            1   John     502.0   120.0        both
2            2   Alex       NaN     NaN   left_only
3            3   Sara     503.0   310.0        both
4            4  Priya       NaN     NaN   left_only
5            9    NaN     504.0    90.0  right_only
howKeepsRows hereTypical use
"inner" (default)Only keys in both3 (John twice, Sara)Orders with a known customer
"left"All left rows5 (adds Alex, Priya with NaN)Enrich a main table; keep every customer
"right"All right rows4 (adds order 504 with no customer)Rarely; swap the frames and use left
"outer"Everything6Reconciling two systems
"cross"Every combinationrows x rowsGenerating all size and colour variants

indicator=True adds a _merge column saying where each row came from. It is the quickest way to find orphan records after a migration: filter on left_only and right_only.

sizes = pd.DataFrame({"size": ["S", "M"]})
colors = pd.DataFrame({"color": ["Red", "Blue"]})
sizes.merge(colors, how="cross")      # S-Red, S-Blue, M-Red, M-Blue

Different key names, overlapping columns and multiple keys

products = pd.DataFrame({"sku": ["A1", "B2"], "price": [100, 200]})
stock = pd.DataFrame({"product_code": ["A1", "B2"], "price": [95, 210]})
products.merge(stock, left_on="sku", right_on="product_code", suffixes=("_list", "_store"))

# Match on two columns at once
actual = orders.groupby(["region", "category"], as_index=False)["revenue"].sum()
targets = pd.DataFrame({"region": ["South", "North"], "category": ["Electronics", "Books"],
                        "target": [80000, 3000]})
actual.merge(targets, on=["region", "category"], how="inner")
  sku  price_list product_code  price_store
0  A1         100           A1           95
1  B2         200           B2          210

Protect yourself with validate

customers.merge(cust_orders, on="customer_id", validate="one_to_one")
MergeError: Merge keys are not unique in right dataset; not a one-to-one merge

validate ("one_to_one", "one_to_many", "many_to_one") raises if the keys are not as unique as you assumed. Without it, duplicate keys multiply rows silently: a 3-row frame merged with a 3-row frame produced 5 rows in my test because key 1 appeared twice on each side.

join(): merge on the index

salaries = employees.set_index("name")[["salary"]]
cities = employees.set_index("name")[["city"]]
salaries.join(cities)

join is a shortcut for merging on the index, and defaults to a left join. It can join several frames at once: a.join([b, c]).

pivot(): long to wide

long = pd.DataFrame({
    "month": ["Jan", "Jan", "Feb", "Feb"],
    "store": ["Kochi", "Chennai", "Kochi", "Chennai"],
    "sales": [100, 80, 120, 95],
})
wide = long.pivot(index="month", columns="store", values="sales")
store  Chennai  Kochi
month
Feb         95    120
Jan         80    100

pivot only reshapes. If two rows share the same index and column pair, it raises ValueError: Index contains duplicate entries, cannot reshape, because it does not know how to combine them.

pivot_table(): reshape and aggregate

orders.pivot_table(
    index="region", columns="category", values="revenue",
    aggfunc="sum", fill_value=0, margins=True,
)
category  Books  Electronics  Home     All
region
North      2650        54999  2397   60046
South      4195        60995     0   65190
West          0         8999  6097   15096
All        6845       124993  8494  140332

This is Excel's pivot table. aggfunc combines duplicates, fill_value replaces empty cells and margins=True adds the totals row and column.

melt(): wide to long

wide.reset_index().melt(id_vars="month", var_name="store", value_name="sales")
  month    store  sales
0   Feb  Chennai     95
1   Jan  Chennai     80
2   Feb    Kochi    120
3   Jan    Kochi    100

melt is the inverse of pivot. Spreadsheets are usually wide (one column per month), while groupby, plotting libraries and databases prefer long data (one row per observation).

stack() and unstack()

grouped = orders.groupby(["region", "category"])["revenue"].sum()
grouped.unstack(fill_value=0)       # inner index level -> columns
grouped.unstack().stack()           # columns -> inner index level

They move a level between the row index and the columns. unstack after a multi-column groupby is a fast way to build a matrix.

explode(): one row per list item

posts = pd.DataFrame({"post": ["Pandas", "Django"], "tags": [["python", "data"], ["python", "web", "orm"]]})
posts.explode("tags", ignore_index=True)
     post    tags
0  Pandas  python
1  Pandas    data
2  Django  python
3  Django     web
4  Django     orm

Common with JSON APIs and comma-separated fields: df["tags"].str.split(",").explode().

crosstab(): frequency tables

pd.crosstab(employees["department"], employees["city"])
city          Bengaluru  Chennai  Kochi
department
Data Science          1        0      1
Engineering           0        1      1
Product               0        0      1
Sales                 1        0      0

crosstab counts combinations by default. Add normalize="index" for row percentages, or values= and aggfunc= to aggregate another column.

Which one do I need?

GoalUse
Stack files with the same columnspd.concat
Add columns from another table by keymerge
Same, but the key is the indexjoin
Long to wide, no duplicatespivot
Long to wide with aggregationpivot_table
Wide to longmelt
Count combinationscrosstab

Chapter 13: Working with Dates and Time

to_datetime()

pd.to_datetime(pd.Series(["2026-01-05", "2026-02-14", "not a date"]), errors="coerce")
pd.to_datetime(pd.Series(["05/01/2026", "14/02/2026"]), format="%d/%m/%Y")
0   2026-01-05
1   2026-02-14
2          NaT
dtype: datetime64[us]

Dates stored as text cannot be sorted chronologically, filtered by range or grouped by month. Convert them first. NaT ("not a time") is the missing value for dates. Pass format whenever you know it: parsing is faster and there is no guessing.

Timestamp, Timedelta and Period

ts = pd.Timestamp("2026-03-15 14:30")
ts.day_name(), ts.quarter                            # ('Sunday', 1)
ts + pd.Timedelta(days=10, hours=3)                  # Timestamp('2026-03-25 17:30:00')
pd.Timestamp("2026-03-31") - pd.Timestamp("2026-01-01")   # Timedelta('89 days 00:00:00')

p = pd.Period("2026-03", freq="M")
p.start_time.date(), p.end_time.date()               # (2026-03-01, 2026-03-31)
  • Timestamp: a single point in time.
  • Timedelta: a duration, the difference between two timestamps.
  • Period: a whole span, such as "March 2026". Good for monthly reports.

date_range()

pd.date_range("2026-01-01", periods=4, freq="D")
pd.date_range("2026-01-01", "2026-06-30", freq="ME")     # month ends
pd.date_range("2026-01-05", periods=3, freq="W-MON")     # Mondays
DatetimeIndex(['2026-01-31', '2026-02-28', '2026-03-31', '2026-04-30', '2026-05-31', '2026-06-30'],
              dtype='datetime64[us]', freq='ME')

Useful for building a complete calendar to merge against, so days with zero sales still appear. Month-end is "ME" since pandas 2.2. In pandas 3 the old "M" raises ValueError: Invalid frequency: M in date_range and resample; the same applies to "Q" and "Y", now "QE" and "YE".

The .dt accessor

d = orders[["order_id", "order_date"]].head(4).copy()
d["year"] = d["order_date"].dt.year
d["month"] = d["order_date"].dt.month
d["day"] = d["order_date"].dt.day
d["weekday"] = d["order_date"].dt.day_name()
d["is_weekend"] = d["order_date"].dt.dayofweek >= 5     # Monday=0 ... Sunday=6
   order_id order_date  year  month  day   weekday  is_weekend
0      1001 2026-01-05  2026      1    5    Monday       False
1      1002 2026-01-18  2026      1   18    Sunday        True
2      1003 2026-01-29  2026      1   29  Thursday       False
3      1004 2026-02-03  2026      2    3   Tuesday       False

.dt does for dates what .str does for text. Other useful attributes: quarter, hour, is_month_end, days_in_month.

Filtering by date

orders[orders["order_date"].between("2026-03-01", "2026-03-15")]

by_date = orders.set_index("order_date").sort_index()
by_date.loc["2026-02"]           # every order in February 2026

With a sorted DatetimeIndex, partial strings such as "2026-02" select a whole month. Note that between("2026-03-01", "2026-03-15") stops at midnight on 15 March; with time components, use < "2026-03-16" as the upper bound.

Formatting dates

orders["order_date"].dt.strftime("%d %b %Y").head(3).tolist()   # ['05 Jan 2026', '18 Jan 2026', '29 Jan 2026']
orders["order_date"].dt.to_period("M")                          # Period('2026-01', 'M') ...

strftime returns text. Do it at the very end, for display or export, because text dates lose all date behaviour.

Resampling and time-based grouping

orders.set_index("order_date").resample("ME")["revenue"].sum()
order_date
2026-01-31    30347
2026-02-28    60493
2026-03-31    49492
Freq: ME, Name: revenue, dtype: int64

resample is groupby for time: it buckets rows into regular intervals ("D", "W-MON", "ME", "QE", "YE", "h"). Unlike groupby, it includes empty periods: weekly counts showed 0 for weeks with no orders. Pass on="order_date" to skip set_index.

To group by time and another column, use pd.Grouper:

orders.groupby([pd.Grouper(key="order_date", freq="ME"), "region"])["revenue"].sum()

Time zones

utc = pd.Series(pd.to_datetime(["2026-03-15 09:00", "2026-03-15 18:30"])).dt.tz_localize("UTC")
utc.dt.tz_convert("Asia/Kolkata")
0   2026-03-15 14:30:00+05:30
1   2026-03-16 00:00:00+05:30
dtype: datetime64[us, Asia/Kolkata]

tz_localize labels naive times with a zone. tz_convert converts already-aware times to another zone. Store and compute in UTC and convert only for display; note how the 18:30 UTC order lands on the next calendar day in India, which changes daily totals.

Chapter 14: Statistical and Analytical Operations

Descriptive statistics

rev = orders["revenue"]
print(rev.sum(), round(rev.mean(), 2), rev.median(), rev.min(), rev.max())
print(round(rev.std(), 2), round(rev.var()))
print(orders["category"].mode().tolist())
print(rev.quantile([0.25, 0.5, 0.9]))
140332 11694.33 3048.5 1300 54999
16966.97 287878142
['Electronics']
0.25     2148.75
0.50     3048.50
0.90    31298.10
MethodMeaningRead it as
mean()AveragePulled up by large values: 11,694 here
median()Middle valueThe typical order: 3,048.5. Far below the mean, so a few big orders dominate
mode()Most frequent value(s)Returns a Series, since there can be ties
std(), var()Spread around the meanUses sample formulas (ddof=1) by default, unlike NumPy
quantile(q)Value below which a share q of the data falls90% of orders are under 31,298

All of these skip missing values by default (skipna=True), and all work per column on a DataFrame. Pass numeric_only=True when a DataFrame also has text columns.

corr() and cov()

marketing = pd.DataFrame({
    "ad_spend": [1200, 1500, 1800, 2400, 3000, 3300],
    "visits":   [5100, 5900, 6800, 8900, 10800, 11600],
    "returns":  [32, 30, 35, 31, 29, 33],
})
marketing.corr().round(2)
          ad_spend  visits  returns
ad_spend      1.00    1.00    -0.14
visits        1.00    1.00    -0.16
returns      -0.14   -0.16     1.00

Correlation runs from -1 to 1 and measures how strongly two columns move together. Ad spend and visits move almost perfectly together; returns are unrelated. Correlation does not prove one causes the other. Covariance (cov()) measures the same relationship in the original units, which makes it harder to compare across columns.

cumsum(), cumprod(), diff() and pct_change()

monthly_rev = pd.Series([120000, 135000, 128000, 150000, 165000],
                        index=["Jan", "Feb", "Mar", "Apr", "May"], name="revenue")
report = monthly_rev.to_frame()
report["running_total"] = monthly_rev.cumsum()
report["change"] = monthly_rev.diff()
report["pct_change"] = (monthly_rev.pct_change() * 100).round(1)
     revenue  running_total   change  pct_change
Jan   120000         120000      NaN         NaN
Feb   135000         255000  15000.0        12.5
Mar   128000         383000  -7000.0        -5.2
Apr   150000         533000  22000.0        17.2
May   165000         698000  15000.0        10.0

Year-to-date totals, month-over-month change and growth rates in three lines. The first row is NaN because there is no previous month. cumprod compounds growth factors: pd.Series([1.10, 1.05, 0.98]).cumprod() gives [1.1, 1.155, 1.1319].

rolling(), expanding() and ewm()

daily = pd.Series([210, 190, 250, 300, 280, 260, 320, 340],
                  index=pd.date_range("2026-03-01", periods=8, freq="D"), name="orders")
w = daily.to_frame()
w["rolling_3d"] = daily.rolling(window=3).mean().round(1)
w["expanding_max"] = daily.expanding().max()
w["ewm_3"] = daily.ewm(span=3, adjust=False).mean().round(1)
            orders  rolling_3d  expanding_max  ewm_3
2026-03-01     210         NaN          210.0  210.0
2026-03-02     190         NaN          210.0  200.0
2026-03-03     250       216.7          250.0  225.0
2026-03-04     300       246.7          300.0  262.5
2026-03-05     280       276.7          300.0  271.2
2026-03-06     260       280.0          300.0  265.6
2026-03-07     320       286.7          320.0  292.8
2026-03-08     340       306.7          340.0  316.4
  • rolling(3): a moving window of the last 3 rows. Smooths noisy daily data. The first two rows are NaN until the window fills; min_periods=1 changes that.
  • expanding(): a window from the first row to the current one. "Best day so far".
  • ewm(span=3): exponentially weighted; recent values count more. Reacts faster than a simple rolling mean.

On a DatetimeIndex, windows can be time-based: daily.rolling("7D").sum() covers 7 calendar days even when some days are missing.

Chapter 15: Advanced Pandas Concepts

MultiIndex and hierarchical indexing

A MultiIndex has several levels of labels per row, like region then category. Groupby on several columns creates one.

mi = orders.groupby(["region", "category"])["revenue"].sum()

mi.loc["South"]                              # all categories in one region
mi.loc[("North", "Books")]                   # one exact cell: 2650
mi.xs("Electronics", level="category")       # one category across regions
mi.groupby(level="region").sum()             # aggregate an index level
region
North    54999
South    60995
West      8999
Name: revenue, dtype: int64

Build one directly with pd.MultiIndex.from_product([["2025", "2026"], ["Q1", "Q2"]], names=["year", "quarter"]). MultiIndexes are compact, but they make code harder to read and export. Many teams reset_index() straight after a groupby and keep data flat.

Advanced groupby and window functions

SQL window functions such as SUM() OVER (PARTITION BY ...) and ROW_NUMBER() map directly to groupby methods:

o = orders.copy()
region_total = o.groupby("region")["revenue"].transform("sum")
o["share_of_region"] = (o["revenue"] / region_total * 100).round(1)        # % of partition
o["order_no_for_customer"] = o.groupby("customer_id").cumcount() + 1        # ROW_NUMBER()
o["customer_running_total"] = o.groupby("customer_id")["revenue"].cumsum()  # running SUM()
o.groupby("region")["revenue"].rolling(2).mean()                            # rolling per group
   order_id  customer_id  revenue  order_no_for_customer  customer_running_total
0      1001            1    24999                      1                   24999
1      1002            2     1350                      1                    1350
2      1003            1     3998                      2                   28997
5      1006            2    54999                      2                   56349
7      1008            1     2200                      3                   31197

cumcount numbers rows within each group in their current order, so sort by date first when the order matters.

Categorical data

A categorical column stores each distinct value once and keeps small integer codes per row. For repetitive text such as city, status or country, the saving is large:

big["city"] = big["city"].astype("category")   # 1,000,000 rows, 5 distinct cities
str: 14.4 MB  category: 1.0 MB

Categories can also have an order, which makes sorting and comparisons follow business logic rather than the alphabet:

sizes = pd.Series(["M", "S", "L", "M"],
                  dtype=pd.CategoricalDtype(["S", "M", "L"], ordered=True))
sizes.sort_values().tolist()     # ['S', 'M', 'M', 'L']
(sizes > "S").tolist()           # [True, False, True, True]

Use categories for low-cardinality columns (few distinct values). For columns where almost every value is unique, such as names or emails, they use more memory.

Sparse data

clicks = pd.Series(np.zeros(1_000_000))
clicks.iloc[::1000] = 1                                     # 0.1% non-zero
sparse = clicks.astype(pd.SparseDtype("float64", 0.0))
dense: 8.0 MB  sparse: 0.012 MB  density: 0.001

Sparse types store only values that differ from a fill value. They suit one-hot encoded features and user-item matrices that are mostly zeros, and save nothing when most values differ.

Copy vs view, and Copy-on-Write

Before pandas 3, whether a selection returned a view (sharing memory with the original) or a copy depended on internal details. Changing a subset sometimes changed the original and sometimes did not, and Pandas warned with the infamous SettingWithCopyWarning.

pandas 3 makes Copy-on-Write (CoW) the only behaviour. The rule is now simple: every DataFrame or Series you get from an operation behaves as an independent copy. Internally Pandas still shares memory and copies only when one side is modified, so it is cheap.

employees = pd.read_csv("employees.csv")
seniors = employees[employees["age"] >= 30]
seniors["salary"] = seniors["salary"] * 1.05      # pandas 2: SettingWithCopyWarning

print(employees.loc[employees["age"] >= 30, "salary"].tolist())   # original unchanged
print(seniors["salary"].tolist())
[95000, 120000, 67000]
[99750.0, 126000.0, 70350.0]

No warning was raised in pandas 3.0.5, and the original was untouched. The same applies to a column you pull out: s = employees["salary"]; s.iloc[0] = 1 leaves employees unchanged.

The one pattern that now never works is chained assignment, assigning through two indexing steps:

emp[emp["name"] == "John"]["salary"] = 1      # does nothing
ChainedAssignmentError: A value is being set on a copy of a DataFrame or Series through chained assignment ...

It is a warning, not an exception, and John's salary stayed 75000. The first indexing step creates a new object, and the assignment modifies that temporary object. The fix is always one .loc call:

emp.loc[emp["name"] == "John", "salary"] = 80000     # works

Extension types: nullable integers, booleans and strings

x = pd.DataFrame({"qty": [3, None, 5], "active": [True, None, False], "name": ["a", None, "c"]})
print(x.dtypes)
print(x.convert_dtypes().dtypes)
qty       float64
active     object
name          str
dtype: object
qty         Int64
active    boolean
name       string
dtype: object

Classic NumPy types cannot represent missing integers or booleans, so qty became a float and active became object. Extension types fix that: Int64 (capital I), boolean and string store a separate missing marker, pd.NA. convert_dtypes() picks them for you. PyArrow-backed types such as "int64[pyarrow]" go further and share memory with Arrow-based tools like Polars and DuckDB.

Memory optimisation

nums["qty"] = pd.to_numeric(nums["qty"], downcast="unsigned")   # int64 -> uint8 (values 0..99)
nums["price"] = nums["price"].astype("float32")                 # float64 -> float32
{'qty': dtype('uint8'), 'price': dtype('float32')}
16.0 MB -> 5.0 MB

Combined steps for a large DataFrame: read only needed columns (usecols), downcast numbers, convert repetitive text to category, and drop intermediate DataFrames you no longer need. Check float32 precision (about 7 significant digits) before using it for money; keep currency as float64, integers in the smallest unit (paise, cents), or Decimal outside Pandas.

Chunk-based processing

When a file is too large for memory, read it in pieces and combine partial results:

totals = pd.Series(dtype="int64")
rows = 0
for chunk in pd.read_csv("big_orders.csv", chunksize=200_000, usecols=["region", "revenue"]):
    rows += len(chunk)
    totals = totals.add(chunk.groupby("region")["revenue"].sum(), fill_value=0)

print(rows)
print(totals.astype("int64"))
1000000
region
North    846900919
South    850450446
West     851453726
dtype: int64

I checked the result against a full in-memory groupby and the totals matched exactly. This works for anything you can combine piece by piece (sums, counts, min, max). Medians and distinct counts cannot be combined that way; for those, use Parquet with column selection, or a tool from Chapter 18.

Faster filtering and queries

  • Filter as early as possible, ideally in SQL or with usecols and Parquet filters, so less data reaches Pandas.
  • Select columns before groupby: df.groupby("k")["v"].sum(), not df.groupby("k").sum()["v"].
  • Set and sort an index when you look up by key repeatedly: df.set_index("sku").sort_index() turns many lookups into binary searches.
  • Use isin for membership tests instead of chains of ==.
  • query() and eval() can use the numexpr package for large arithmetic expressions; measure before assuming a speed-up.

Chapter 16: Pandas Visualization

Every DataFrame and Series has a .plot() method that draws with Matplotlib. It is the fastest way to look at your data while exploring.

import matplotlib.pyplot as plt

monthly = orders.resample("ME", on="order_date")["revenue"].sum()

monthly.plot(kind="line", marker="o", title="Monthly revenue")
orders.groupby("category")["revenue"].sum().sort_values().plot(kind="barh")
employees["salary"].plot(kind="hist", bins=5)
orders.plot(kind="box", column="unit_price", by="category")
marketing.plot(kind="scatter", x="ad_spend", y="visits")
orders.pivot_table(index="order_date", columns="region", values="revenue",
                   aggfunc="sum", fill_value=0).resample("ME").sum().plot(kind="area", stacked=True)
orders.groupby("region")["revenue"].sum().plot(kind="pie", autopct="%1.0f%%", ylabel="")
plt.show()
Six charts drawn with pandas plot from the sample data: a line chart of monthly revenue rising from 30k in January to 60k in February then 49k in March; a horizontal bar chart where Electronics revenue far exceeds Home and Books; a salary histogram from 70k to 120k; box plots of unit price by category on a log scale; a scatter plot showing visits rising with ad spend; and a stacked area chart of revenue by North, South and West regions.
Six chart types from the sample datasets, each one line of Pandas
Chartkind=Use it to show
Line"line"A trend over time
Bar"bar", "barh"Comparing categories; horizontal for long labels
Histogram"hist"The distribution of one numeric column
Box"box"Spread, median and outliers, compared across groups
Scatter"scatter"The relationship between two numeric columns
Area"area"How parts add up to a total over time
Pie"pie"Shares of a whole, only with a few slices; a bar chart is usually easier to read

Matplotlib integration

.plot() returns a Matplotlib Axes, so you can customise anything Matplotlib can:

fig, ax = plt.subplots(figsize=(8, 4))
monthly.plot(ax=ax, marker="o")
ax.set_title("Monthly revenue")
ax.set_ylabel("Revenue (INR)")
ax.axhline(monthly.mean(), linestyle="--", color="grey", label="average")
ax.legend()
fig.tight_layout()
fig.savefig("monthly_revenue.png", dpi=150)

Pass ax= to place several Pandas plots in one figure, as in the grid above.

Seaborn integration

Seaborn builds statistical charts directly from long-format DataFrames:

import seaborn as sns

sns.set_theme(style="whitegrid")
sns.barplot(data=employees, x="department", y="salary", estimator="mean", errorbar=None)
sns.boxplot(data=orders, x="category", y="unit_price")

Use Pandas plotting for quick looks, Seaborn for grouped and statistical charts, and Plotly or a BI tool when people need interactive dashboards.

Chapter 17: Real-World Pandas Projects

Ten short projects that combine the earlier chapters. Each uses the sample datasets or a small inline one, so you can run them as written.

Project 1: Employee data analysis

Problem: HR wants headcount, average salary and average age per department, and the top earner in each. Input: employees.

report = (
    employees.groupby("department")
    .agg(headcount=("name", "count"), avg_salary=("salary", "mean"), avg_age=("age", "mean"))
    .round(1)
    .sort_values("avg_salary", ascending=False)
)

dept_max = employees.groupby("department")["salary"].transform("max")
top_earners = employees.loc[employees["salary"] == dept_max, ["name", "department", "salary"]]
              headcount  avg_salary  avg_age
department
Data Science          2     98500.0     29.5
Engineering           2     97500.0     30.0
Product               1     88000.0     28.0
Sales                 1     67000.0     41.0

    name    department  salary
2   Sara       Product   88000
3  Priya   Engineering  120000
4   Omar         Sales   67000
5    Mei  Data Science  102000

How it works: named aggregation builds the summary; transform("max") puts each department's maximum next to every employee so a simple comparison finds the top earners. Business use: salary benchmarking and pay-equity reviews.

Project 2: E-commerce sales analysis

Problem: which categories drive revenue, who are the top customers, and what is the average order value? Input: orders.

cat = orders.groupby("category").agg(
    orders=("order_id", "count"), units=("quantity", "sum"), revenue=("revenue", "sum"),
)
cat["share_pct"] = (cat["revenue"] / cat["revenue"].sum() * 100).round(1)

top_customers = orders.groupby("customer_id")["revenue"].sum().nlargest(3)
aov = orders["revenue"].sum() / orders["order_id"].nunique()
             orders  units  revenue  share_pct
category
Electronics       5      7   124993       89.1
Home              3      6     8494        6.1
Books             4     14     6845        4.9

customer_id
2    57649
4    33993
1    31197

aov: 11694.33

Insight: Books sells the most units (14) but brings under 5% of revenue. Business use: deciding where to spend marketing budget and which customers to target for loyalty programmes.

Project 3: Customer data cleaning

Problem: a CRM export has duplicates, messy names, mixed-case emails, phone numbers in several formats and dates in two formats. Input:

   customer_id          name                 email            phone signup_date
0          101    john smith  John.Smith@Gmail.com  +91 98470-12345  2026-01-05
1          102  ALEX O'NEIL       alex@company.io       98470 55555  05/02/2026
2          103     sara khan        SARA@yahoo.com       9847011111  2026-02-10
3          103     sara khan        SARA@yahoo.com       9847011111  2026-02-10
4          104           NaN                priya@      98470 22222  2026-03-01
5          105      mei  lin   mei.lin@outlook.com              NaN   not known
def clean_customers(df: pd.DataFrame) -> tuple[pd.DataFrame, pd.DataFrame]:
    df = df.drop_duplicates(subset="customer_id").copy()
    df["name"] = df["name"].str.strip().str.replace(r"\s+", " ", regex=True).str.title()
    df["email"] = df["email"].str.strip().str.lower()
    df["phone"] = df["phone"].str.replace(r"\D", "", regex=True).str[-10:]

    iso = pd.to_datetime(df["signup_date"], format="%Y-%m-%d", errors="coerce")
    indian = pd.to_datetime(df["signup_date"], format="%d/%m/%Y", errors="coerce")
    df["signup_date"] = iso.fillna(indian)

    valid_email = df["email"].str.fullmatch(r"[\w.+-]+@[\w-]+\.[\w.]+", na=False)
    is_bad = ~valid_email | df["name"].isna()
    return df[~is_bad], df[is_bad]

good, rejected = clean_customers(raw_customers)
   customer_id         name                 email       phone signup_date
0          101   John Smith  john.smith@gmail.com  9847012345  2026-01-05
1          102  Alex O'Neil       alex@company.io  9847055555  2026-02-05
2          103    Sara Khan        sara@yahoo.com  9847011111  2026-02-10
5          105      Mei Lin   mei.lin@outlook.com         NaN         NaT

rejected:
   customer_id name   email
4          104  NaN  priya@

How it works: duplicates go first so later steps do less work. \s+ collapses double spaces; str[-10:] keeps the 10-digit mobile number after removing the country code. Each date format is parsed explicitly. Invalid rows go to a separate rejected frame instead of disappearing. Business use: CRM imports, email campaign lists, deduplicating customers before a migration.

Project 4: Monthly revenue report

monthly = orders.resample("ME", on="order_date").agg(
    orders=("order_id", "count"), revenue=("revenue", "sum"),
)
monthly.index = monthly.index.strftime("%b %Y")
monthly["mom_growth_pct"] = (monthly["revenue"].pct_change() * 100).round(1)
monthly["cumulative"] = monthly["revenue"].cumsum()
            orders  revenue  mom_growth_pct  cumulative
order_date
Jan 2026         3    30347             NaN       30347
Feb 2026         3    60493            99.3       90840
Mar 2026         6    49492           -18.2      140332

Insight: March had twice as many orders as February but 18% less revenue, because February included one 54,999 order. Order count and revenue can move in opposite directions, so report both. Business use: the monthly management report.

Project 5: Product inventory analysis

inventory = pd.DataFrame({
    "sku": ["KB-01", "MS-02", "HD-03", "MN-04", "CB-05"],
    "product": ["Keyboard", "Mouse", "Headset", "Monitor", "USB-C Cable"],
    "stock": [12, 3, 0, 25, 140],
    "reorder_level": [10, 15, 5, 8, 50],
    "unit_cost": [1500, 600, 2200, 11000, 250],
    "units_sold_30d": [40, 55, 18, 6, 90],
})
inventory["stock_value"] = inventory["stock"] * inventory["unit_cost"]
inventory["days_of_cover"] = (inventory["stock"] / (inventory["units_sold_30d"] / 30)).round(1)
inventory["status"] = np.select(
    [inventory["stock"] == 0, inventory["stock"] <= inventory["reorder_level"]],
    ["out of stock", "reorder"],
    default="ok",
)
     sku      product  stock  stock_value  days_of_cover        status
0  KB-01     Keyboard     12        18000            9.0            ok
1  MS-02        Mouse      3         1800            1.6       reorder
2  HD-03      Headset      0            0            0.0  out of stock
3  MN-04      Monitor     25       275000          125.0            ok
4  CB-05  USB-C Cable    140        35000           46.7            ok

Insight: the keyboard is "ok" by its reorder level but has only 9 days of cover at the current sales rate, while monitors tie up 275,000 in stock for 125 days. Business use: purchase planning and freeing up working capital.

Project 6: Student performance analysis

students = pd.DataFrame({
    "student": ["Anu", "Ben", "Chitra", "Dev", "Esha", "Farhan"],
    "class": ["10A", "10A", "10A", "10B", "10B", "10B"],
    "math": [78, 92, 45, 88, 67, 95],
    "science": [82, 89, 52, 79, 71, 91],
    "english": [91, 76, 60, 85, 74, 88],
})
subjects = ["math", "science", "english"]
students["average"] = students[subjects].mean(axis=1).round(1)
students["grade"] = pd.cut(students["average"], bins=[0, 60, 75, 90, 100],
                           labels=["D", "C", "B", "A"], right=False)
students["class_rank"] = students.groupby("class")["average"].rank(ascending=False, method="min").astype(int)

needs_support = students.loc[(students[subjects] < 50).any(axis=1), "student"].tolist()   # ['Chitra']
  student class  math  science  english  average grade  class_rank
0     Anu   10A    78       82       91     83.7     B           2
1     Ben   10A    92       89       76     85.7     B           1
2  Chitra   10A    45       52       60     52.3     D           3
3     Dev   10B    88       79       85     84.0     B           2
4    Esha   10B    67       71       74     70.7     C           3
5  Farhan   10B    95       91       88     91.3     A           1

mean(axis=1) averages across columns for each row. pd.cut converts scores to grade bands, and a grouped rank ranks within each class. Business use: report cards, and identifying students who need support early.

Project 7: CSV data processing

Problem: a system drops one CSV per day into a folder. Combine them, remove orders that appear in more than one file, and produce city totals.

from pathlib import Path

frames = []
for path in sorted(Path("daily_exports").glob("orders_*.csv")):
    df = pd.read_csv(path)
    df["source_file"] = path.name           # keep lineage for debugging
    frames.append(df)

combined = pd.concat(frames, ignore_index=True).drop_duplicates(subset="order_id")
combined.groupby("city", as_index=False)["amount"].sum().to_csv("city_totals.csv", index=False)
   order_id     city  amount            source_file
0         1    Kochi    1200  orders_2026-03-01.csv
1         2  Chennai     800  orders_2026-03-01.csv
2         3    Kochi     450  orders_2026-03-02.csv
3         4    Delhi    2300  orders_2026-03-02.csv
4         5  Chennai     990  orders_2026-03-03.csv

city_totals.csv:
city,amount
Chennai,1790
Delhi,2300
Kochi,1650

Order 2 appeared in two files and was counted once. Build one list and concatenate once; concatenating inside the loop copies the growing frame every time. Business use: consolidating exports from POS systems, partners or log files.

Project 8: Excel report generation

summary = orders.pivot_table(index="region", columns="category", values="revenue",
                             aggfunc="sum", fill_value=0, margins=True, margins_name="Total")

with pd.ExcelWriter("sales_report.xlsx", engine="openpyxl") as writer:
    summary.to_excel(writer, sheet_name="Summary")
    orders.to_excel(writer, sheet_name="Orders", index=False)

    ws = writer.sheets["Orders"]                # the openpyxl worksheet
    ws.freeze_panes = "A2"                      # keep the header visible
    for col in ws.columns:                      # rough auto-width
        ws.column_dimensions[col[0].column_letter].width = max(len(str(c.value)) for c in col) + 2
['Summary', 'Orders']
        Books  Electronics  Home   Total
region
North    2650        54999  2397   60046
South    4195        60995     0   65190
West        0         8999  6097   15096
Total    6845       124993  8494  140332

Pandas writes the data; writer.sheets exposes the underlying openpyxl worksheet for formatting. Business use: the weekly report finance asks for, generated by a scheduled job instead of by hand.

Project 9: Database data analysis

import sqlite3

conn = sqlite3.connect("shop.db")    # or a SQLAlchemy engine for PostgreSQL/MySQL
customers = pd.DataFrame({"customer_id": [1, 2, 3, 4, 5, 6],
                          "name": ["John", "Alex", "Sara", "Priya", "Omar", "Mei"],
                          "city": ["Kochi", "Bengaluru", "Kochi", "Chennai", "Bengaluru", "Kochi"]})
customers.to_sql("customers", conn, index=False, if_exists="replace")
orders.to_sql("orders", conn, index=False, if_exists="replace")

query = """
    SELECT c.city, o.category, o.revenue, o.order_date
    FROM orders AS o
    JOIN customers AS c ON c.customer_id = o.customer_id
    WHERE o.order_date >= :start
"""
df = pd.read_sql(query, conn, params={"start": "2026-02-01"}, parse_dates=["order_date"])

city_report = df.pivot_table(index="city", columns="category", values="revenue",
                             aggfunc="sum", fill_value=0)
city_report.reset_index().to_sql("city_category_revenue", conn, index=False, if_exists="replace")
category   Books  Electronics  Home
city
Bengaluru   1300        54999  2598
Chennai     1995        31998     0
Kochi       2200         8999  5896

The database does the join and the date filter, where indexes help; Pandas does the reshaping, which is awkward in SQL. The result is written back as a reporting table. Business use: reporting from a production replica without adding load to the main application.

Project 10: Business KPI reporting

o = orders.copy()
o["month"] = o["order_date"].dt.to_period("M")
first_month = o.groupby("customer_id")["month"].transform("min")
o["customer_type"] = np.where(o["month"] == first_month, "new", "returning")

kpis = o.groupby("month").agg(
    revenue=("revenue", "sum"),
    orders=("order_id", "nunique"),
    customers=("customer_id", "nunique"),
)
kpis["aov"] = (kpis["revenue"] / kpis["orders"]).round(0)
kpis["new_customers"] = o[o["customer_type"] == "new"].groupby("month")["customer_id"].nunique()
kpis["revenue_growth_pct"] = (kpis["revenue"].pct_change() * 100).round(1)
kpis["target_hit"] = kpis["revenue"] >= pd.Series([40000, 50000, 60000], index=kpis.index)
         revenue  orders  customers      aov  new_customers  revenue_growth_pct  target_hit
month
2026-01    30347       3          2  10116.0              2                 NaN       False
2026-02    60493       3          3  20164.0              2                99.3        True
2026-03    49492       6          6   8249.0              2               -18.2       False

How it works: transform("min") finds each customer's first month, which classifies every order as new or returning. Assigning kpis["new_customers"] aligns on the month index automatically. Business use: the KPI table behind a dashboard or a board update.

Chapter 18: Performance and Best Practices

The rules that matter most

  1. Prefer vectorised operations. Column arithmetic ran in 0.74 ms where apply(axis=1) took 2.64 s (Chapter 9).
  2. Avoid loops over rows. iterrows() was the slowest option measured. If a loop is truly unavoidable, itertuples() is much faster than iterrows(), and a loop over NumPy arrays (df["a"].to_numpy()) faster still.
  3. Use appropriate data types. Read IDs as strings, dates as datetimes, and numbers as numbers. Wrong types are slower and cause bugs.
  4. Optimise memory. Downcast numbers and load only the columns you need. Memory use went from 16.0 MB to 5.0 MB in Chapter 15.
  5. Use categoricals for repetitive text. 14.4 MB to 1.0 MB for a city column.
  6. Avoid unnecessary copies. With Copy-on-Write, extra .copy() calls are no longer needed for safety. Do not keep five intermediate versions of a large DataFrame alive; reuse names or chain methods.
  7. Read large files in chunks when they do not fit in memory, or convert them to Parquet once.
  8. Filter early. Push filters into SQL, usecols or Parquet reads before data reaches Pandas.
  9. Do not grow DataFrames in a loop. Collect rows in a list and build the frame once.
  10. Benchmark before optimising. Guesses about performance are often wrong.

Benchmarking

# In Jupyter
%timeit df["price"] * df["qty"]

# In a script
import time
start = time.perf_counter()
result = df.groupby("region")["revenue"].sum()
print(f"{time.perf_counter() - start:.4f}s")

# Memory
df.info(memory_usage="deep")

Measure on realistic data sizes. An approach that wins on 100 rows can lose on 10 million.

Readable code is a best practice too

report = (
    pd.read_csv("orders.csv", parse_dates=["order_date"])
    .query("region != 'West'")
    .assign(month=lambda d: d["order_date"].dt.to_period("M"))
    .groupby(["month", "category"], as_index=False)["revenue"].sum()
    .sort_values(["month", "revenue"], ascending=[True, False])
)

Method chaining reads top to bottom like a recipe, avoids half-finished intermediate variables and works naturally with Copy-on-Write. Break the chain when a step needs a comment or a check.

Testing Pandas code

import pandas.testing as pdt

def test_monthly_revenue():
    result = monthly_revenue(sample_orders)
    expected = pd.DataFrame({"month": ["2026-01"], "revenue": [30347]})
    pdt.assert_frame_equal(result, expected)

assert_frame_equal compares values, dtypes, index and column names, and prints a clear diff when they differ. Keep transformations in plain functions that take and return DataFrames, so they are easy to test with pytest. My guide on Python testing and code quality covers the rest.

When Pandas is not the right tool

Pandas works in memory on one machine. As a rough guide, it needs several times the file size in RAM while working. Its limits:

  • Datasets larger than available memory.
  • Many common operations use only one CPU core.
  • Heavy row-by-row logic that cannot be vectorised.
  • Transactional, multi-user data. That belongs in a database.
ToolWhat it isConsider it when
PolarsA DataFrame library written in Rust, multi-threaded, with a lazy query optimiserSingle-machine data that is slow or tight in Pandas; new pipelines
DuckDBAn in-process analytical SQL database that queries Pandas frames, CSV and Parquet directlyYou prefer SQL, or need joins and aggregations on larger-than-memory files
DaskParallel, chunked DataFrames with a Pandas-like APIScaling existing Pandas code across cores or a small cluster
PySparkThe Python API for Apache Spark, a distributed engineTerabyte-scale data on a cluster, often in a data platform such as Databricks

Pandas remains the best choice for most datasets that fit in memory, thanks to its API coverage and ecosystem. Learn Pandas first; the concepts transfer directly to the others.

Chapter 19: Common Errors and Troubleshooting

ErrorTypical causeFix
KeyError: 'Salary'Column name typo, wrong case or hidden whitespacePrint df.columns.tolist(); normalise with df.columns.str.strip().str.lower()
KeyError: 10 from .loc[10]Label does not exist, often after filteringUse .iloc for positions or reset_index(drop=True)
IndexError: single positional indexer is out-of-bounds.iloc position beyond the last rowCheck len(df); handle empty results
ValueError: Length of values (3) does not match length of index (6)Assigning a list of the wrong length as a columnAssign a Series (aligned by index) or a list of the right length
ValueError: The truth value of a Series is ambiguousand/or or if on a Series; missing parenthesesUse &, |, ~ with parentheses; .any()/.all() in if
ValueError: invalid literal for int() with base 10: 'abc'astype(int) on dirty textpd.to_numeric(s, errors="coerce"), then inspect the NaNs
ChainedAssignmentError (pandas 3) / SettingWithCopyWarning (pandas 2)df[cond]["col"] = valuedf.loc[cond, "col"] = value
ParserError: Expected 2 fields in line 3, saw 3Wrong delimiter, or unquoted commas inside valuesSet sep; fix the export's quoting; on_bad_lines="skip" as a last resort
FileNotFoundErrorRelative path resolved from a different working directoryBuild paths with Path(__file__).parent
UnicodeDecodeErrorFile is not UTF-8encoding="latin-1" or "cp1252"; "utf-8-sig" for Excel CSVs with a BOM
ValueError: You are trying to merge on int64 and str columnsKey types differ between framesConvert both keys to the same type before merging
MergeError: Merge keys are not uniquevalidate= caught duplicate keysDeduplicate or aggregate the side that should be unique
ValueError: Index contains duplicate entries, cannot reshapepivot with duplicate index/column pairsUse pivot_table with an aggfunc

Silent problems (no error, wrong answer)

These are worse than exceptions, because nothing tells you something went wrong.

  • Numbers stored as text. pd.Series(["10", "20", "30"]).sum() returns '102030', string concatenation. Check dtypes after every read.
  • Row explosion after a merge. Duplicate keys on both sides multiply rows. Compare len() before and after, and use validate=.
  • Missing values skipped in maths. pd.Series([1, None, 3]).mean() is 2.0, not 1.33, and the sum of an all-missing column is 0. Decide deliberately how missing values should count.
  • Comparing with NaN. == np.nan is always False. Use isna().
  • Ambiguous dates parsed in the wrong order (Chapter 13).
  • Integer columns turning into floats when one value is missing; IDs then print as 101.0. Use Int64.

Performance problems

If a script is slow, check in this order: a Python loop or apply(axis=1) over rows; growing a DataFrame inside a loop; reading the same file repeatedly; text columns that should be category; and loading columns or rows that a filter later throws away.

Chapter 20: Pandas Interview Preparation

Basic questions

1. What is Pandas and what are its main data structures?

An open-source Python library for structured data. Its main structures are the Series (one-dimensional, labelled) and the DataFrame (two-dimensional, labelled rows and columns, each column a Series).

2. How do you read a CSV and inspect it?

df = pd.read_csv("file.csv"), then df.shape, df.info(), df.head() and df.describe().

3. How do you select rows where a column meets a condition?

Boolean indexing: df[df["age"] > 30]; for several conditions, df[(cond1) & (cond2)]; or df.query("age > 30").

4. How do you find and handle missing values?

df.isna().sum() to find them; fillna to replace (per-column values, ffill, interpolation) or dropna(subset=...) to remove. The right choice depends on what the column means.

5. What is the index?

The row labels. It identifies rows, drives .loc lookups and aligns data in arithmetic, joins and assignments.

Intermediate questions

6. Explain split-apply-combine.

GroupBy splits rows into groups by key, applies a function to each group (aggregate, transform or filter) and combines the results into one object.

7. What is the difference between agg and transform?

agg returns one row per group. transform returns a result the same length as the input, aligned to the original rows, so you can add a group statistic as a new column.

8. How do you remove duplicates while keeping the latest record?

df.sort_values("updated_at").drop_duplicates(subset="customer_id", keep="last").

9. How do you convert a column to datetime and get monthly totals?

df["date"] = pd.to_datetime(df["date"], format=...), then df.resample("ME", on="date")["amount"].sum().

10. How do you reshape wide data to long?

df.melt(id_vars=[...], var_name=..., value_name=...). The reverse is pivot or pivot_table.

Advanced questions

11. What is Copy-on-Write and how did pandas 3 change things?

Every derived DataFrame or Series behaves as an independent copy; data is physically copied only when one side is modified. It is the only mode in pandas 3. SettingWithCopyWarning is gone, and chained assignment (df[a][b] = x) never updates the original. Use a single .loc.

12. How would you process a 20 GB CSV on a laptop with 16 GB RAM?

Read only needed columns with explicit dtypes, process with chunksize and combine partial aggregates, or convert once to Parquet and read selected columns. If the logic needs the whole dataset (joins, medians), use DuckDB or Polars' lazy mode, or Dask.

13. Why is apply(axis=1) slow, and what do you use instead?

It calls a Python function once per row and builds a Series for each row. Replace it with vectorised column operations, np.where/np.select for conditions, pd.cut for bins, and map with a dictionary for lookups.

14. How do you reduce a DataFrame's memory?

Measure with memory_usage(deep=True), then downcast numbers, convert low-cardinality text to category, use the str or Arrow-backed types instead of object, and drop unused columns.

15. How do you replicate a SQL window function?

groupby().transform() for SUM() OVER (PARTITION BY ...), groupby().cumcount() for ROW_NUMBER(), groupby().rank() for RANK(), groupby().shift() for LAG(), and groupby().rolling() for moving windows.

Scenario-based questions

16. After merging orders with customers, your revenue total went up. Why?

The customer table has duplicate keys, so some orders matched several customer rows and were counted more than once. Check customers["customer_id"].duplicated().sum(), deduplicate, and add validate="many_to_one" to the merge.

17. A column of amounts sums to a huge string. What happened?

It was read as text, probably because of a currency symbol, thousands separator or a stray value. Clean the text and convert with pd.to_numeric(..., errors="coerce"), then investigate the rows that became NaN.

18. A daily report shows no row for days with zero sales. How do you fix it?

Use resample("D"), which includes empty periods, or reindex against pd.date_range(start, end, freq="D") with fill_value=0.

Practical coding questions

19. Top 2 earners per department

(employees.sort_values("salary", ascending=False)
          .groupby("department")
          .head(2))

20. Percentage of each order within its region's revenue

orders["pct_of_region"] = (
    orders["revenue"] / orders.groupby("region")["revenue"].transform("sum") * 100
).round(1)

21. Customers who ordered in January but not in March

month = orders["order_date"].dt.month
jan = set(orders.loc[month == 1, "customer_id"])
mar = set(orders.loc[month == 3, "customer_id"])
sorted(jan - mar)          # [] here: customers 1 and 2 both ordered again in March

22. Second-highest salary

employees["salary"].drop_duplicates().nlargest(2).iloc[-1]    # 102000

Frequently asked differences

PairDifference
Series vs DataFrameOne labelled column vs a table of columns sharing an index
loc vs ilocLabels (slice end included) vs integer positions (slice end excluded)
merge vs join vs concatJoin on columns (default inner) vs join on the index (default left) vs stack along an axis with no key matching
map vs applySeries.map maps each value with a dict, Series or function; apply runs a function on values, or on rows/columns of a DataFrame
isna vs isnullNo difference; isnull is an alias
dropna vs fillnaRemove rows/columns with missing values vs replace missing values
pivot vs pivot_tableReshape only, fails on duplicates vs reshape and aggregate duplicates, with optional totals
count vs sizeNon-missing values vs all rows, including missing
sort_values vs sort_indexSort by column values vs sort by index labels

Frequently Asked Questions

Is Pandas hard to learn?

The basics take a few days if you know Python: reading files, selecting, filtering, grouping. Fluency comes from using it on real, messy data. Start with the roadmap concepts, then move to groupby, merge and dates.

Do I need NumPy before learning Pandas?

No. Knowing that operations work on whole arrays at once (vectorisation) helps, and you will pick up the few NumPy functions you need, such as np.where and np.nan, along the way.

Should I learn Pandas or Polars in 2026?

Learn Pandas first. Most tutorials, libraries and existing codebases use it, and job descriptions ask for it. Polars is worth adding when performance becomes a problem; the concepts carry over directly.

How much data can Pandas handle?

As much as fits comfortably in memory, usually a few million to tens of millions of rows on a laptop. Efficient dtypes, column selection and chunking stretch that further.

Is Pandas used in backend development?

Yes: bulk imports and exports, data migrations, reports, reconciliation jobs and admin analytics. Run heavy Pandas work in background workers such as Celery rather than inside web requests.

What changed in pandas 3.0?

Copy-on-Write is always on, text columns default to the new str dtype, and deprecated APIs were removed, including DataFrame.applymap, fillna(method=...) and the "M", "Q" and "Y" frequency aliases. The official migration guide lists every change.

Conclusion

Pandas turns tabular data work into short, repeatable code. The core is small: Series and DataFrames, reading and inspecting data, selecting with loc and iloc, cleaning missing and messy values, groupby for summaries and merge for combining sources. Dates, reshaping, window functions and memory tuning build on that foundation.

Three habits matter more than any single method: vectorise instead of looping, check dtypes and row counts after every read and merge, and on pandas 3 update data with one .loc call. Pick a real CSV from your own work, run the five-minute exploration checklist from Chapter 5, and build one of the projects above on it.

Related reading

Further Learning Resources