9  Pandas

What This Chapter Covers

Pandas (Wes McKinney, 2010) is the library you will spend the most time in as an analyst. By the end of this chapter you will be able to:

• Create Series (1-D labelled) and DataFrame (2-D labelled) objects from scratch, from a dictionary, or from a file.
• Inspect a DataFrame with head, tail, info, describe, shape and dtypes.
• Select rows and columns using bracket notation, .loc (label-based) and .iloc (position-based).
• Filter with Boolean conditions and combine them using &, | and ~.
• Handle missing data with dropna, fillna and isna.
• Create new columns from existing ones using vectorised expressions and apply.
• Group and aggregate with groupby (split-apply-combine).
• Combine DataFrames with concat and SQL-style merge.
• Read and write CSV and Excel files.

---

9.1 A Map of Pandas

Pandas revolves around two data structures — the Series and the DataFrame — and a set of verbs that operate on them. The diagram below sorts the most common operations by purpose.

flowchart TB
    A["Pandas DataFrame / Series"] --> B["I/O<br/>read_csv, read_excel<br/>to_csv, to_excel"]
    A --> C["Inspect<br/>head, tail, info, describe"]
    A --> D["Select<br/>[], .loc, .iloc"]
    A --> E["Filter<br/>boolean masks"]
    A --> F["Clean<br/>dropna, fillna, astype"]
    A --> G["Transform<br/>new columns, apply, map"]
    A --> H["Aggregate<br/>groupby, agg, pivot_table"]
    A --> I["Combine<br/>concat, merge, join"]

    %% Style
    classDef dark fill:#2e4057,color:#ffffff,stroke:#ff9933,stroke-width:3px,rx:10px,ry:10px;
    class A,B,C,D,E,F,G,H,I dark;

---

Pandas is an open-source, BSD-licensed library providing high-performance, easy-to-use data structures and data analysis tools for the Python programming language. It’s built on top of NumPy, another library offering support for multi-dimensional arrays, and integrates well with other libraries in the Python Data Science stack like Matplotlib for plotting, SciPy for scientific computing, and scikit-learn for machine learning.

9.2 Core Features

• Data Structures: Pandas introduces two primary data structures: DataFrame and Series. A DataFrame is a two-dimensional, size-mutable, potentially heterogeneous tabular data structure with labeled axes (rows and columns). A Series, on the other hand, is a one-dimensional array with axis labels.

• Handling of Data: Pandas excels in the handling of missing data, data alignment, and merging, reshaping, selecting, as well as data slicing and indexing.

• File Import Export: It provides extensive capabilities to read and write data with a wide variety of formats, including CSV, Excel, SQL databases, JSON, HTML, and more.

• Time Series: Pandas offers comprehensive support for working with time series data, including date range generation, frequency conversion, moving window statistics, and more.

9.3 Installation

Pandas comes pre-installed in this book’s pyodide runtime, so there is no need to run pip install pandas here. In your own environment, install it with pip install pandas (or conda install pandas inside Anaconda).

9.3.1 Basic Usage

Importing Pandas is typically done using the pd alias, alongside NumPy:

Python

Try here

Creating Data Structures

A Series is a one-dimensional labelled array and a DataFrame is a two-dimensional labelled table. The example below builds both from in-memory data.

Python

Try here

Viewing Data

head() and tail() are the two fastest ways to look at a DataFrame, while index, columns and to_numpy() expose its underlying structure. For a one-line summary, info() shows dtypes and memory, and describe() prints numeric summary statistics.

Python

Try here

Python

Try here

Data Selection

You can select a single column, a positional slice of rows, a specific row by label with .loc, or a specific row by integer position with .iloc.

Rule of thumb: .loc uses labels (both endpoints inclusive); .iloc uses integer positions (end exclusive, like a Python slice). When in doubt, be explicit — avoid relying on df[...] for row selection beyond quick peeks.

Python

Try here

Selecting and Dropping Columns

Besides .loc and .iloc, two patterns come up constantly when preparing data for analysis: picking a subset of columns with a list of names, and dropping columns you no longer need.

Python

Try here

Boolean Filtering

Most real analytics questions are of the form “show me rows where X”. Pandas answers those with Boolean masks — the same idea as NumPy, lifted to DataFrames.

• df[df["Revenue"] > 100] keeps rows meeting the condition.
• Combine conditions with & (and), | (or), ~ (not). Always parenthesise each condition.
• .isin([...]) tests for membership in a list.
• .between(lo, hi) tests for inclusive range membership.

Python

Try here

Sorting

sort_values() sorts by the values in one or more columns; sort_index() sorts by the row index. Pass ascending=False to reverse, and a list to break ties across multiple columns.

Python

Try here

Random Sampling

sample() draws a random subset of rows — useful for spot-checking a large dataset, or for splitting data into smaller pieces before further analysis. Pass random_state for a reproducible sample, and either n (a fixed count) or frac (a fraction of all rows).

Python

Try here

Adding and Transforming Columns

New columns are created by assignment — the right-hand side is any vectorised expression or an apply() call. Pandas lines up values by index automatically.

Python

Try here

Recoding and Binning Values

Two patterns turn a numeric column into categories that are easier to report and group by: pd.cut() slices a continuous variable into labelled bins, and pd.get_dummies() converts a categorical column into a set of 0/1 indicator columns (sometimes called dummy coding or one-hot encoding) — the standard way to prepare categorical data for many statistical and machine-learning tools.

Python

Try here

Missing Data

Pandas uses np.nan to mark missing values. Most default computations ignore them, but you can remove or replace them with dropna() and fillna().

Python

Try here

Operations

Pandas DataFrames offer column-wise summary statistics and row-wise or column-wise function application via apply().

Python

Try here

Grouping

groupby() splits a DataFrame by the values of one or more columns, applies an aggregation to each group, then combines the results back together — the canonical split-apply-combine pattern.

Single aggregations (sum, mean, count) are one-liners. For multiple aggregations on different columns, use .agg({...}).

Python

Try here

Python

Try here

Pivot Tables and Cross-Tabulation

pivot_table() reshapes a DataFrame into a spreadsheet-style summary, with one set of values forming the rows, another forming the columns, and an aggregation filling the cells. pd.crosstab() is the special case for counting how often combinations of two categorical columns occur together — handy for a quick frequency table before deeper analysis. Calling .reset_index() afterwards turns a grouped index back into ordinary columns, which is often the tidier shape for further use.

Python

Try here

Merging

Pandas offers two main ways to combine DataFrames: concat() stacks them along an axis, and merge() joins them on one or more key columns, SQL-style.

Python

Try here

Python

Try here

Choosing a Join Type

how=	Returns
inner	Only rows with a match in both DataFrames (the default)
left	All rows from the left DataFrame, plus any matches from the right
right	All rows from the right DataFrame, plus any matches from the left
outer	All rows from both, with NaN filled in where a match is missing

Use how="left" for the common “enrich my main table with a lookup” pattern.

Importing and Exporting Data: CSV, Excel, and URLs

Pandas reads and writes several file formats, and treats a local file and a web address the same way – pass the path or link to the matching read_* function and pandas downloads and parses it in one call. The table below covers the formats you will meet most often in business analytics work.

Source	Read	Write
Local CSV	pd.read_csv("filename.csv")	df.to_csv("my_dataframe.csv", index=False)
Local Excel	pd.read_excel("filename.xlsx", sheet_name="Sheet1")	df.to_excel("my_dataframe.xlsx", sheet_name="Sheet1", index=False)
A URL (CSV)	pd.read_csv("https://raw.githubusercontent.com/datasciencedojo/datasets/master/titanic.csv")	— (write to a URL requires a separate upload step, not a pandas call)

A few practical notes:

• index=False on a write avoids saving pandas’ row index as an extra, unlabelled column – usually what you want unless the index itself carries meaning.
• pd.read_excel() needs a specific sheet name (or an index, sheet_name=0 for the first sheet) when the workbook has more than one tab.
• Reading from a URL works exactly like reading a local file, provided the link points directly at the raw file and the server allows the request – no separate download step is needed.

Python

Try here

Reading from a URL needs a live network connection and a server that allows the request, which is the one exception to every other example in this chapter running entirely offline. Excel workbooks are trickier still in an in-browser runtime, since they depend on a local file or an extra package the runtime may not have. To keep that part self-contained, here is the same CSV round-trip shown again with an in-memory string via io.StringIO, which needs neither a network call nor a file on disk:

Python

Try here

---

Basic Plotting with pandas and Matplotlib

Pandas’ .plot() method is a thin, convenient wrapper around Matplotlib — it is the fastest way to get a first look at a column’s distribution or a comparison across categories, straight from a DataFrame. Common kinds are "bar", "hist", "box", and "scatter"; each reveals a different aspect of the data, so it is worth trying more than one on the same column.

Python

Try here

---

9.4 Common Pitfalls with Pandas

• SettingWithCopyWarning → Assigning to a slice (e.g. df[df.A > 0]["B"] = 1) may silently fail. Use .loc on both sides: df.loc[df.A > 0, "B"] = 1.
• Forgetting parentheses in boolean filters → df[df.A > 0 & df.B < 5] raises an error because & binds tighter than >. Write df[(df.A > 0) & (df.B < 5)].
• and/or vs &/| → and and or work on scalar booleans. For element-wise logic on a Series, use & and |.
• .loc is inclusive on both ends → df.loc[0:3] returns four rows (0, 1, 2, 3), unlike Python slicing. .iloc[0:3] returns three rows.
• Chained indexing → df["A"]["x"] sometimes works and sometimes doesn’t. Prefer df.loc["x", "A"].
• Silent NaN in aggregations → sum() skips NaN by default. If NaN means “unknown, can’t compute”, pass skipna=False to force NaN in the result.
• Mutating while iterating → Don’t modify a DataFrame inside a for loop over its rows. Build a new column with a vectorised expression or apply() instead.
• Dtype surprises after reading CSV → A column with one blank cell may arrive as object or float64 rather than int64. Check df.dtypes and cast with .astype(int) if needed.

Pandas is a foundational tool for data analysis in Python, offering comprehensive functions and methods for efficient data manipulation and analysis. Its robust features for handling complex data operations make it indispensable for analysts and data scientists.

---

Summary

Concept	Description
Foundations
Pandas	A high-performance data analysis library built on NumPy, centred on labelled tabular data structures
Series	A one-dimensional labelled array that behaves like a column of a spreadsheet with an index
DataFrame	A two-dimensional, size-mutable, heterogeneous tabular structure with row and column labels
Labelled Axes	Rows and columns carry names, so code can refer to data by label rather than by integer position
Built on NumPy	Pandas operations ultimately run on NumPy arrays, inheriting vectorised speed for numerical work
Inspecting a DataFrame
head() and tail()	df.head(n) returns the first n rows and df.tail(n) returns the last n, for quick inspection
info() and describe()	df.info() prints dtypes and memory; df.describe() prints mean, std, min, max and quartiles for numeric columns
.index, .columns, .to_numpy()	df.index and df.columns expose the axis labels, and df.to_numpy() returns the underlying NumPy array
Selecting and Filtering
Selection with []	df['A'] selects a column as a Series; df[0:3] slices rows by position using bracket notation
.loc vs .iloc	df.loc[label] selects by row or column labels and is inclusive; df.iloc[position] selects by integer position and is exclusive
Selecting and Dropping Columns	Select a subset of columns with a list of names, such as df[['A','B']], or remove columns no longer needed with df.drop(columns=[...])
Boolean Filtering	Filter rows by passing a Boolean Series to [] — combine with &, |, ~ and always parenthesise each condition
.isin() and .between()	.isin([...]) tests membership in a list; .between(lo, hi) tests inclusive range membership
sort_values()	Sort by one or more columns with ascending flags, the most common way to produce top-N or bottom-N views
Transforming and Cleaning
Random Sampling	df.sample(n=...) or df.sample(frac=...) draws a random subset of rows, with random_state for reproducibility
Creating New Columns	Assign to df['new'] with any vectorised expression; Pandas aligns values by the row index automatically
Recoding and Binning Values	pd.cut() slices a continuous column into labelled bins, and pd.get_dummies() dummy-codes a categorical column into 0/1 indicator columns
Missing Data np.nan	Pandas represents missing values with np.nan and excludes them from most default computations
dropna() and fillna()	Use dropna() to remove rows with missing values and fillna(value) to substitute a replacement value
Summary Statistics	Built-in reductions such as df.mean(), df.sum() and df.describe() summarise numeric columns
apply()	Apply an arbitrary function column-wise or row-wise with df.apply(func) for custom transformations
Grouping and Aggregating
groupby()	df.groupby(col).agg(...) splits the DataFrame into groups, applies an aggregation and recombines the results
.agg() with Multiple Aggregations	Pass a dict or named tuples to .agg() to compute several different aggregations across several columns in one call
Pivot Tables and Cross-Tabulation	df.pivot_table() builds a spreadsheet-style summary across two dimensions, and pd.crosstab() counts combinations of two categorical columns; reset_index() turns a grouped index back into columns
Combining DataFrames
concat()	pd.concat stacks DataFrames along an axis, typically for appending new rows below existing ones
merge()	pd.merge performs SQL-style joins on one or more key columns, connecting two DataFrames by matching values
Join Types inner, left, right, outer	how='inner' keeps only matched rows; 'left' keeps all rows on the left; 'right' on the right; 'outer' keeps all rows from both
File I/O
read_csv() and to_csv()	pd.read_csv(path) and df.to_csv(path) load and save comma-separated files with a single call; path can be a local file or a direct URL
read_excel() and to_excel()	pd.read_excel(path, sheet_name) and df.to_excel(path, sheet_name) do the same for Excel workbooks
Basic Visualization
Basic Plotting with pandas	The .plot() method wraps Matplotlib directly on a DataFrame or Series, offering quick bar, histogram, box and scatter views of the data
Gotchas
Common Pitfalls	Beware SettingWithCopyWarning, missing parentheses in filters, and .loc being inclusive at both ends

McKinney, W. (2010). Data Structures for Statistical Computing in Python. Proceedings of the 9th Python in Science Conference, 56–61. https://doi.org/10.25080/Majora-92bf1922-00a