Information Exploration

Author

Brian C. Keegan, Ph.D.

Published

January 1, 2026

Preface

This book teaches you how to use information to identify interesting real-world problems and generate insight. You will learn to find, collect, assemble, and organize data to inspire new questions, make predictions, generate deliverables, and work toward solutions. Along the way, you will develop the practical skills of exploratory data analysis using Python and SQL — two tools that appear in virtually every data-oriented career.

If the word “exploratory” feels a little abstract, that is intentional. Real analysis is rarely linear. You load data, discover it is messier than you expected, go back to clean it, realize you need a different angle, and iterate. This book teaches that rhythm — not only the individual techniques, but the habits of mind that turn a pile of rows into a defensible conclusion.

Who This Book Is For

This book is written for undergraduate students who have completed one introductory course in Python. You should be comfortable with variables, loops, conditionals, functions, and lists. If you can write a for loop that iterates over a list and appends results to another list, you have enough Python background to begin.

No prior experience with pandas, SQL, databases, or the command line is required. The book introduces each of these tools from scratch, with careful attention to the concepts that trip up newcomers.

What You Will Learn

By the time you finish this book, you will be able to:

Load tabular data from CSV, Excel, and SQLite sources into a working environment — and recognize the silent failures (wrong headers, misparsed types, hidden footnotes) that happen along the way.
Clean messy real-world data using type coercion, string methods, missing-value handling, and deduplication, and document every decision you make in a Known Issues Log.
Derive new information from existing datasets with grouped aggregation, pivot tables, and multi-table joins, auditing each operation to confirm nothing silently broke.
Translate the same analytical moves between pandas and SQL, so you can choose the right tool for the job regardless of where the data lives.
Characterize distributions, build visualizations with matplotlib and seaborn, and run simple hypothesis tests to answer real questions about public data.
Read Python error messages and tracebacks fluently, turning “walls of red text” into actionable diagnostic information.

You do not need to memorize every function or argument you encounter. Learning to navigate documentation is one of the most valuable skills this book will help you build — and you will come back to the docs for the rest of your career.

The Load–Clean–Extend–Reshape Framework

Data analysis is often taught as a grab bag of techniques. This book takes a different approach: it organizes the entire curriculum around a four-stage workflow that applies regardless of the tool you are using.

Loading is the process of getting data from its source — a CSV file, an Excel spreadsheet, a database — into your working environment. This stage seems simple, but it is where many invisible problems originate: wrong delimiters, misidentified headers, encoding errors, and columns silently cast to the wrong type.

Cleaning addresses the problems that loading reveals. You will learn to identify and fix data type mismatches, standardize inconsistent categories, handle missing values, detect duplicates, and document every decision you make along the way.

Extending means deriving new information from what you already have. This includes computing new columns, building grouped summaries and aggregation tables, and combining information from multiple sources. The goal is to transform raw observations into metrics that can support analysis.

Reshaping is the process of changing the structure of your data — pivoting between wide and long formats, merging tables on shared keys, and auditing the results to confirm nothing was lost or duplicated in the process.

These four stages are not strictly sequential. Real analysis involves cycling back through them as you discover new issues. But the framework gives you a reliable starting point and a vocabulary for describing where you are in any analysis workflow.

How This Book Is Organized

The book is divided into five parts. Part I builds the conceptual and technical foundation. Parts II and III each apply the Load–Clean–Extend–Reshape framework with a different tabular tool. Part IV extends the toolkit to non-tabular data (spatial, network, text, time series, and web-structured formats). Part V applies the results to analysis.

Part I: Foundations covers the paratechnical skills that most courses skip but that every analyst needs — navigating the terminal, organizing project files, writing documentation, managing Python environments, and reading error messages — as well as the conceptual and ethical vocabulary for working with public data. Four chapters: workspace setup, documentation and errors, research-question framing, and public-interest data science. These short chapters are designed to be referenced throughout the rest of the book.

Part II: Python introduces the pandas library for tabular data analysis. Across four chapters, you will learn to load CSV and Excel files with read_csv and read_excel, clean data using string methods, type coercion, and dictionary-based recoding, extend datasets with derived columns, groupby aggregation, and pivot_table, and reshape data using melt, merge, and concat. The datasets in this section include Colorado cannabis sales data, Boulder County voter registration records, Lahman Baseball statistics, and Colorado population estimates from the State Demography Office.

Part III: SQL teaches you to perform the same operations using Structured Query Language and the SQLite database engine. You will set up a database from the command line, design schemas, load cleaned CSVs, write diagnostic queries, use GROUP BY, subqueries, and Common Table Expressions (CTEs), and join multiple tables while auditing join quality. The SQL chapters use Colorado public health datasets — CDC mortality data, Colorado Bureau of Investigation crime statistics, and population estimates — culminating in a multi-table join that produces an analysis-ready dataset.

Part IV: Non-Tabular Data extends the book’s toolkit to data that does not arrive as rows and columns. Five chapters introduce spatial data with GeoPandas, network data with NetworkX, unstructured text with NLTK, time series with statsmodels and Prophet, and structured web data (JSON, XML, HTML) with requests and Beautiful Soup.

Part V: Analysis organizes the final stage of the book as five complementary modes of exploration. You will characterize distributions, outliers, and categorical patterns through descriptive exploration; use trace and digital-ethnographic methods to read data as the residue of particular institutions and practices in qualitative exploration; compare groups and variables with t-tests, one-way ANOVA, chi-square, and correlation in comparative exploration (via scipy.stats); build a visual vocabulary with matplotlib and seaborn in visual exploration; and fit linear models with statsmodels to quantify and interpret relationships across multiple simultaneous variables in modeling exploration. Part V uses the same datasets from earlier chapters so that the analytical questions grow naturally from the data preparation work you have already done.

Two Appendices document the datasets used throughout the book (sources, structures, known quirks) and disclose the author’s use of AI assistance during drafting.

Recurring Themes

Several principles recur throughout the book, regardless of whether you are working in pandas or SQL.

Inspect before you act. Every chapter begins by looking at the data before transforming it. The post-load checklist — shape, headers, types, missingness, distributions, duplicates, plausibility, and a quick visual scan — appears in every loading chapter and becomes second nature.

Validate after every transformation. Cleaning and reshaping operations can silently drop rows, create duplicates, or introduce null values. The book teaches you to check row counts, compare totals, and cross-verify results after every step.

Make invisible problems visible. Many data errors are silent — a join that drops rows because of trailing whitespace in a key column, a column that looks numeric but contains a hidden sentinel value, a merge that inflates row counts because of an unexpected many-to-many relationship. A central goal of this book is to train your eye to catch these problems before they corrupt your analysis.

Document your decisions. Every cleaning choice involves a judgment call. The book introduces the Known Issues Log and Data Dictionary as lightweight documentation tools that make your workflow reproducible and your assumptions transparent.

Conventions Used in This Book

Code blocks contain Python or SQL that you can run in a Jupyter notebook or SQLite shell. When a code example includes output, it appears directly below the code block. Exercises appear throughout each chapter with solutions hidden in collapsible blocks — try the exercise before revealing the answer.

Terminal commands are shown in bash code blocks. On Windows, equivalent commands are noted where they differ.

Accompanying Materials

All datasets used in this book are available in the accompanying GitHub repository. Each chapter assumes you have downloaded the relevant data files to your working directory. Instructions for setting up your environment appear in 1 Setting Up Your Workspace.

A Note on Difficulty

Some chapters will feel harder than others, and that is not a reflection of your ability. Data work is a craft that rewards patience — you will write code that does not work, read error messages that look like nonsense, and spend an hour tracking down a single stray character. This is completely normal. Every experienced data analyst goes through the same cycle, and the difference between a beginner and a professional is not that professionals do not get stuck. It is that they have learned which moves to try first when they do.

If something in this book feels confusing, slow down rather than push through. Re-read the section. Run the code one line at a time. Print the intermediate results. The concepts in this book will click with practice — often on the third or fourth real project, not the first. Stay with it.

Acknowledgments

This book grew out of the INFO 3401: Information Exploration course at the University of Colorado Boulder. It reflects the contributions of many students whose questions, mistakes, and feedback shaped the material into its current form.

# Preface {.unnumbered} This book teaches you how to use information to identify interesting real-world problems and generate insight. You will learn to find, collect, assemble, and organize data to inspire new questions, make predictions, generate deliverables, and work toward solutions. Along the way, you will develop the practical skills of exploratory data analysis using [Python](https://www.python.org/doc/) and [SQL](https://www.sqlite.org/lang.html) — two tools that appear in virtually every data-oriented career. If the word "exploratory" feels a little abstract, that is intentional. Real analysis is rarely linear. You load data, discover it is messier than you expected, go back to clean it, realize you need a different angle, and iterate. This book teaches that rhythm — not only the individual techniques, but the habits of mind that turn a pile of rows into a defensible conclusion. ## Who This Book Is For This book is written for undergraduate students who have completed one introductory course in Python. You should be comfortable with variables, loops, conditionals, functions, and lists. If you can write a `for` loop that iterates over a list and appends results to another list, you have enough Python background to begin. No prior experience with [pandas](https://pandas.pydata.org/docs/), SQL, databases, or the command line is required. The book introduces each of these tools from scratch, with careful attention to the concepts that trip up newcomers. ## What You Will Learn By the time you finish this book, you will be able to: - Load tabular data from CSV, Excel, and SQLite sources into a working environment — and recognize the silent failures (wrong headers, misparsed types, hidden footnotes) that happen along the way. - Clean messy real-world data using type coercion, string methods, missing-value handling, and deduplication, and document every decision you make in a Known Issues Log. - Derive new information from existing datasets with grouped aggregation, pivot tables, and multi-table joins, auditing each operation to confirm nothing silently broke. - Translate the same analytical moves between [pandas](https://pandas.pydata.org/docs/) and SQL, so you can choose the right tool for the job regardless of where the data lives. - Characterize distributions, build visualizations with [matplotlib](https://matplotlib.org/stable/) and [seaborn](https://seaborn.pydata.org/), and run simple hypothesis tests to answer real questions about public data. - Read Python error messages and tracebacks fluently, turning "walls of red text" into actionable diagnostic information. You do not need to memorize every function or argument you encounter. Learning to navigate documentation is one of the most valuable skills this book will help you build — and you will come back to the docs for the rest of your career. ## The Load–Clean–Extend–Reshape Framework Data analysis is often taught as a grab bag of techniques. This book takes a different approach: it organizes the entire curriculum around a four-stage workflow that applies regardless of the tool you are using. **Loading** is the process of getting data from its source — a CSV file, an Excel spreadsheet, a database — into your working environment. This stage seems simple, but it is where many invisible problems originate: wrong delimiters, misidentified headers, encoding errors, and columns silently cast to the wrong type. **Cleaning** addresses the problems that loading reveals. You will learn to identify and fix data type mismatches, standardize inconsistent categories, handle missing values, detect duplicates, and document every decision you make along the way. **Extending** means deriving new information from what you already have. This includes computing new columns, building grouped summaries and aggregation tables, and combining information from multiple sources. The goal is to transform raw observations into metrics that can support analysis. **Reshaping** is the process of changing the structure of your data — pivoting between wide and long formats, merging tables on shared keys, and auditing the results to confirm nothing was lost or duplicated in the process. These four stages are not strictly sequential. Real analysis involves cycling back through them as you discover new issues. But the framework gives you a reliable starting point and a vocabulary for describing where you are in any analysis workflow. ## How This Book Is Organized The book is divided into five parts. Part I builds the conceptual and technical foundation. Parts II and III each apply the Load–Clean–Extend–Reshape framework with a different tabular tool. Part IV extends the toolkit to non-tabular data (spatial, network, text, time series, and web-structured formats). Part V applies the results to analysis. **Part I: Foundations** covers the paratechnical skills that most courses skip but that every analyst needs — navigating the terminal, organizing project files, writing documentation, managing Python environments, and reading error messages — as well as the conceptual and ethical vocabulary for working with public data. Four chapters: workspace setup, documentation and errors, research-question framing, and public-interest data science. These short chapters are designed to be referenced throughout the rest of the book. **Part II: Python** introduces the [pandas](https://pandas.pydata.org/docs/) library for tabular data analysis. Across four chapters, you will learn to load CSV and Excel files with `read_csv` and `read_excel`, clean data using string methods, type coercion, and dictionary-based recoding, extend datasets with derived columns, `groupby` aggregation, and `pivot_table`, and reshape data using `melt`, `merge`, and `concat`. The datasets in this section include Colorado cannabis sales data, Boulder County voter registration records, Lahman Baseball statistics, and Colorado population estimates from the State Demography Office. **Part III: SQL** teaches you to perform the same operations using Structured Query Language and the [SQLite](https://www.sqlite.org/docs.html) database engine. You will set up a database from the command line, design schemas, load cleaned CSVs, write diagnostic queries, use `GROUP BY`, subqueries, and Common Table Expressions (CTEs), and join multiple tables while auditing join quality. The SQL chapters use Colorado public health datasets — CDC mortality data, Colorado Bureau of Investigation crime statistics, and population estimates — culminating in a multi-table join that produces an analysis-ready dataset. **Part IV: Non-Tabular Data** extends the book's toolkit to data that does not arrive as rows and columns. Five chapters introduce spatial data with [GeoPandas](https://geopandas.org/), network data with [NetworkX](https://networkx.org/), unstructured text with [NLTK](https://www.nltk.org/), time series with [statsmodels](https://www.statsmodels.org/) and [Prophet](https://facebook.github.io/prophet/), and structured web data (JSON, XML, HTML) with [requests](https://requests.readthedocs.io/) and [Beautiful Soup](https://www.crummy.com/software/BeautifulSoup/bs4/doc/). **Part V: Analysis** organizes the final stage of the book as five complementary modes of **exploration**. You will characterize distributions, outliers, and categorical patterns through **descriptive exploration**; use trace and digital-ethnographic methods to read data as the residue of particular institutions and practices in **qualitative exploration**; compare groups and variables with t-tests, one-way ANOVA, chi-square, and correlation in **comparative exploration** (via [scipy.stats](https://docs.scipy.org/doc/scipy/reference/stats.html)); build a visual vocabulary with [matplotlib](https://matplotlib.org/stable/) and [seaborn](https://seaborn.pydata.org/) in **visual exploration**; and fit linear models with [statsmodels](https://www.statsmodels.org/) to quantify and interpret relationships across multiple simultaneous variables in **modeling exploration**. Part V uses the same datasets from earlier chapters so that the analytical questions grow naturally from the data preparation work you have already done. Two **Appendices** document the datasets used throughout the book (sources, structures, known quirks) and disclose the author's use of AI assistance during drafting. ## Recurring Themes Several principles recur throughout the book, regardless of whether you are working in pandas or SQL. **Inspect before you act.** Every chapter begins by looking at the data before transforming it. The post-load checklist — shape, headers, types, missingness, distributions, duplicates, plausibility, and a quick visual scan — appears in every loading chapter and becomes second nature. **Validate after every transformation.** Cleaning and reshaping operations can silently drop rows, create duplicates, or introduce null values. The book teaches you to check row counts, compare totals, and cross-verify results after every step. **Make invisible problems visible.** Many data errors are silent — a join that drops rows because of trailing whitespace in a key column, a column that looks numeric but contains a hidden sentinel value, a merge that inflates row counts because of an unexpected many-to-many relationship. A central goal of this book is to train your eye to catch these problems before they corrupt your analysis. **Document your decisions.** Every cleaning choice involves a judgment call. The book introduces the Known Issues Log and Data Dictionary as lightweight documentation tools that make your workflow reproducible and your assumptions transparent. ## Conventions Used in This Book Code blocks contain Python or SQL that you can run in a Jupyter notebook or SQLite shell. When a code example includes output, it appears directly below the code block. Exercises appear throughout each chapter with solutions hidden in collapsible blocks — try the exercise before revealing the answer. Terminal commands are shown in `bash` code blocks. On Windows, equivalent commands are noted where they differ. ## Accompanying Materials All datasets used in this book are available in the accompanying GitHub repository. Each chapter assumes you have downloaded the relevant data files to your working directory. Instructions for setting up your environment appear in @sec-setup. ## A Note on Difficulty Some chapters will feel harder than others, and that is not a reflection of your ability. Data work is a craft that rewards patience — you will write code that does not work, read error messages that look like nonsense, and spend an hour tracking down a single stray character. This is completely normal. Every experienced data analyst goes through the same cycle, and the difference between a beginner and a professional is not that professionals do not get stuck. It is that they have learned which moves to try first when they do. If something in this book feels confusing, slow down rather than push through. Re-read the section. Run the code one line at a time. Print the intermediate results. The concepts in this book will click with practice — often on the third or fourth real project, not the first. Stay with it. ## Further Reading ::: {.callout-tip collapse="true"} ## 📚 General resources that complement this book - **Python documentation** — the [official Python tutorial](https://docs.python.org/3/tutorial/) is an excellent reference when you need to refresh syntax or a standard-library module. - **pandas user guide** — [pandas.pydata.org/docs/user_guide/](https://pandas.pydata.org/docs/user_guide/) is longer than you want it to be but worth bookmarking. The "10 minutes to pandas" page is a useful quick reference. - **SQLite documentation** — [sqlite.org/docs.html](https://www.sqlite.org/docs.html) includes the full SQL syntax reference. [SQLZoo](https://sqlzoo.net/) and [Mode's SQL tutorial](https://mode.com/sql-tutorial/) are both free, interactive, and beginner-friendly. - **Real Python** — [realpython.com](https://realpython.com/) publishes high-quality, beginner-friendly tutorials on almost every topic in this book. - **"Python for Data Analysis"** by Wes McKinney (the creator of pandas) — freely available at [wesmckinney.com/book/](https://wesmckinney.com/book/). A natural next read after this book. ::: ## Acknowledgments This book grew out of the INFO 3401: Information Exploration course at the University of Colorado Boulder. It reflects the contributions of many students whose questions, mistakes, and feedback shaped the material into its current form.