5  Loading Data with pandas

5.1 Learning Objectives

By the end of this chapter, you will be able to:

  • Load CSV and Excel files into a pandas DataFrame using pd.read_csv() and pd.read_excel().
  • Control how pandas interprets a file using header, skipfooter, na_values, sep, dtype, and encoding.
  • Run the post-load checklist — shape, headers, types, missingness, distributions, duplicates, plausibility, and a quick visual scan — on every new dataset.
  • Diagnose FileNotFoundError, DtypeWarning, and other loading errors, and choose an appropriate fix.
  • Navigate pandas’ documentation using help(), the ? operator, and the online API reference.

5.2 Why Loading Is Harder Than It Looks

Every analysis begins with loading data — getting information from a file on disk into a pandas DataFrame where you can inspect and manipulate it. This chapter covers the mechanics of loading CSV and Excel files using pd.read_csv() and pd.read_excel(), the parameters that control how pandas interprets the file, and the post-load inspection routine that catches problems before they propagate into your analysis.

Loading data is where most silent errors originate. A column that looks numeric but was loaded as text, a header row mistakenly treated as data, a delimiter that splits values in the wrong places — these problems are invisible unless you actively look for them. The discipline of inspecting data immediately after loading is one of the most valuable habits you will develop in this course.

This chapter builds on the terminal and project-organization skills from Chapter 1. If a pd.read_csv call fails with FileNotFoundError, review that chapter — the fix is almost always a path or working-directory issue.

5.3 Setup

You will use three libraries in this chapter: pandas for the DataFrame, numpy for numerical arrays, and matplotlib for the quick visualization at the end.

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt

If you see ModuleNotFoundError: No module named 'pandas' (or numpy, or matplotlib), the package is not installed in the Python environment your notebook is using. Install it with:

conda install pandas numpy matplotlib

Or, if you are not using conda:

pip install pandas numpy matplotlib

After installing, restart the Jupyter kernel (Kernel → Restart) and rerun the import cell. If the error persists, the notebook’s kernel is probably pointing at a different environment than the one you installed into — see Chapter 1 for how to check sys.executable and align the two. Full reference: Chapter 2.

5.4 How to Read pandas Documentation

Before you run your first load, take a minute to learn how to read the documentation. You will come back to the docs constantly — not because you are a beginner, but because experienced data scientists do the same. Nobody memorizes every parameter of pd.read_csv (there are more than 30).

There are three ways to access documentation from inside a notebook:

  1. The ? operator (IPython / Jupyter only): type pd.read_csv? in a cell and run it. A help panel appears with the function’s signature and docstring.
  2. The help() function (works anywhere): help(pd.read_csv) prints the same information as text.
  3. The online docs: pandas.pydata.org/docs/reference/api/pandas.read_csv.html shows the same content formatted for the web, plus examples and cross-references.

A function signature looks like this:

pd.read_csv(filepath_or_buffer, *, sep=NoDefault.no_default, header='infer',
            names=None, index_col=None, usecols=None, dtype=None,
            engine=None, ..., encoding=None, skipfooter=0, ...)

The first argument (filepath_or_buffer) is required — you must pass it. Everything after the * is a keyword argument — it has a default value and you can leave it out unless you need to change it. The documentation tells you what each parameter does, what types it accepts, and what the default behavior is.

TipDocs are a reference, not a tutorial

Pandas documentation is terse by design. It describes behavior, not motivation. When you are learning a new function, skim the signature and the first paragraph, then jump to the examples at the bottom — that is usually the fastest way to understand what the function does. Come back to the parameter list when you hit a specific problem.

5.5 A First Load

The Colorado Department of Revenue publishes monthly cannabis sales by county. The data is distributed as a CSV file originally exported from Excel, which means it has some quirks we need to handle. Let’s start with a naive load and see what happens.

TipBefore you run pd.read_csv: where is the file?

pd.read_csv('colorado_cannabis_sales_by_county.csv') looks for the file in your notebook’s current working directory. If you get a FileNotFoundError, the file is not there — the file name is mistyped, the file lives in a subdirectory (data/raw/colorado_cannabis_sales_by_county.csv), or your notebook is running in a different folder than you expected. Run import os; print(os.getcwd()) to confirm where you are. See Chapter 2 for a deeper walkthrough of FileNotFoundError.

df = pd.read_csv('colorado_cannabis_sales_by_county.csv')

Before doing anything else, look at the data.

df.shape
df.head()
df.tail(10)

Something is wrong. The real column headers appear to be on row 2, not row 0. The first two rows contain a title block from the original Excel file. And the bottom of the file likely contains footnotes or totals that are not data rows.

This is a common pattern with government data — the files are formatted for human reading, not for machine ingestion. Your job is to tell pandas how to read the file correctly.

5.6 Public Interest Context: Enclosure by Design

The cannabis sales file is technically public — it is published by a Colorado state agency and freely downloadable. But its formatting serves a different audience than you. The two-row title block, the eight-row footnote section, and the currency-formatted numbers are there because the file was designed for humans reading it in Excel, not for analysts loading it into pandas. The NR values in the sales columns are a separate form of enclosure: the Colorado Department of Revenue suppresses sales figures for counties with fewer than three active licensees, so the counties with the least cannabis activity — often smaller, rural counties — are also the ones with the least data.

The work you do with header=, skipfooter=, and na_values= is not just a technical step. It is the labor of making nominally “open” data actually usable — labor that is unevenly distributed across the people who nominally have access. When you encounter a file like this, it is worth asking: who can realistically use this dataset as published, and who cannot?

NoteConnection to Chapter 4

This section connects to the concept of enclosure introduced in Chapter 4. Public data formatted for Excel rather than pandas is a form of enclosure by interface — access is technically possible, but practically gated by who has the tooling and skill to clean it.

5.7 The Post-Load Checklist

Before you touch any data, run through this checklist. It takes less than a minute and catches the majority of loading problems.

  • Shape: df.shape — does the row and column count match expectations?
  • Headers: df.columns.tolist() — are column names unique, non-empty, and meaningful?
  • Types: df.dtypes — are numbers numeric, dates datetime, and text object?
  • Missingness: df.isna().sum() — how many missing values per column?
  • Distributions: df['col'].value_counts() — do categorical values look right?
  • Duplicates: df.duplicated(subset=[key_cols]).sum() — is there a candidate key?
  • Plausibility: df.describe() — any impossible ranges, negative counts, future dates?
  • Quick look: df.head(), df.tail(), df.sample(5) — do rows look right?

You will apply this checklist in every loading chapter in this book, in both Python and SQL. Memorize it.

5.8 Fixing the Load with read_csv Parameters

The pd.read_csv() function has dozens of parameters that control how the file is interpreted. For most real-world files, you will need to specify at least a few of them. Here are the most important ones.

5.8.1 header: where the column names are

If the header row is not the first row of the file, specify its zero-indexed position:

df = pd.read_csv(
    'colorado_cannabis_sales_by_county.csv',
    header=2
)

df.head()

5.8.2 skipfooter: removing trailing rows

Government data files often have footnotes, totals, or metadata appended to the bottom. The skipfooter parameter removes a specified number of rows from the end. Note that skipfooter requires setting engine='python' because the default C parser does not support it.

df = pd.read_csv(
    'colorado_cannabis_sales_by_county.csv',
    header=2,
    skipfooter=8,
    engine='python'
)

df.tail()

5.8.3 na_values: declaring custom missing value codes

Many datasets use codes like NR (Not Reported), -999, ., or empty strings to represent missing values. The na_values parameter tells pandas to treat these as NaN:

df = pd.read_csv(
    'colorado_cannabis_sales_by_county.csv',
    header=2,
    skipfooter=8,
    engine='python',
    na_values=['NR']
)

df.tail()

5.8.4 sep: specifying the delimiter

CSV stands for “comma-separated values,” but many files use other delimiters — tabs (\t), pipes (|), or semicolons (;). If your file uses something other than a comma, specify it with sep:

# Example: pipe-delimited file
# df = pd.read_csv('voter_data.txt', sep='|')

5.8.5 dtype: controlling column types

By default, pandas guesses each column’s type. You can override this with a dictionary mapping column names to types. This is especially useful for columns that look numeric but should remain strings — like zip codes or ID numbers that have leading zeros:

# Example: preserve leading zeros in zip codes
# df = pd.read_csv('data.csv', dtype={'zip_code': str, 'voter_id': str})

5.9 Building Up Iteratively

Notice the pattern above: you load the file, check the result, add a parameter to fix a problem, and reload. This iterative approach — get something to work, then move on to the next issue — is how experienced analysts work with messy files.

In a finished notebook, you would consolidate everything into a single cell. But when you are first working with a new dataset, spread the steps across multiple cells so you can see the effect of each parameter.

5.10 What the Parameters Fixed — and What Remains

The read_csv parameters handle structural problems at the boundary between file and DataFrame: they put the header in the right place, skip rows that are not data, and mark sentinel values as missing. Run the checklist again to see what they accomplished:

NoteFrom loading to cleaning

The column names, currency formatting, and statewide aggregate rows still need attention. Those are post-load cleaning operations — renaming columns, stripping currency symbols, filtering rows — covered in Chapter 6, which begins by completing this dataset before moving to more complex cleaning scenarios.

5.11 Running the Post-Load Checklist

print('Shape:', df.shape)
print('\nDtypes:')
print(df.dtypes)
print('\nMissing values:')
print(df.isna().sum())
for col in ['Month', 'Year', 'County']:
    print(df[col].value_counts(), '\n\n')
for col in ['Medical', 'Retail']:
    print(df[col].describe(), '\n\n')

5.12 A Quick Preview: What You Can Do with Clean Data

With the data loaded and cleaned, you can immediately begin exploring. Here is a pivot table and a simple visualization — both of which we will cover in depth in later chapters:

pivot_df = pd.pivot_table(
    data=df,
    index='County',
    columns='Year',
    values='Retail',
    aggfunc='sum'
)

pivot_df.head()
pivot_df.loc['Las Animas'].plot(title='Las Animas County Retail Cannabis Sales by Year')

5.13 The read_csv Parameters You Should Know

Parameter Purpose Example
header Which row contains column names header=2
skiprows Rows to skip at the top skiprows=3
skipfooter Rows to skip at the bottom skipfooter=8 (requires engine='python')
sep Column delimiter sep='|', sep='\t'
na_values Custom missing value codes na_values=['NR', '-999', '.']
dtype Force column types dtype={'zip': str}
encoding Character encoding encoding='latin-1'
usecols Load only specific columns usecols=['County', 'Year', 'Sales']

5.14 Common Issues in This Chapter

WarningFileNotFoundError: [Errno 2] No such file or directory

What it means: Pandas looked for the file at the path you gave and did not find it.

How to fix it: Run import os; print(os.getcwd()) to see where your notebook is looking. Confirm the file name spelling and extension. Use a relative path like '../data/raw/filename.csv' if the file lives in a sibling directory. See Chapter 2.

WarningDtypeWarning: Columns (N) have mixed types

What it means: Pandas found both numeric and non-numeric values in the same column and fell back to object (string) type. Your later arithmetic will silently fail.

How to fix it: Inspect the column with df['col'].value_counts(). If the mixing comes from a stray header row or footer, set skiprows or skipfooter. If the column has sentinel codes like NR or -999, pass them via na_values. If you want to force the type, use dtype={'col': str} (or float, int, etc.).

WarningUnicodeDecodeError: 'utf-8' codec can't decode byte

What it means: The file uses a non-UTF-8 encoding (common in files exported from older Windows software or government systems).

How to fix it: Try encoding='latin-1' or encoding='cp1252' in your pd.read_csv call. If you are on the command line, file my_data.csv (macOS/Linux) reports the encoding. Re-save the file as UTF-8 if you will use it repeatedly.

WarningNumbers import as strings because of $ or ,

What it means: A column like $1,234,567 is stored as text. df['Retail'] + 100 will concatenate a string rather than add a number.

How to fix it: Strip the currency/comma characters, then convert: df['Retail'] = pd.to_numeric(df['Retail'].str.replace(r'[$,]', '', regex=True), errors='coerce'). See Chapter 6 for the full pattern.

5.15 Key Takeaways

  • pd.read_csv and pd.read_excel have dozens of parameters; you rarely need all of them, but knowing header, skipfooter, na_values, sep, dtype, and encoding will cover most real-world files.
  • Run the post-load checklist on every new dataset: shape, headers, types, missingness, distributions, duplicates, plausibility, head/tail/sample.
  • Load → inspect → adjust → reload. This iterative pattern is how experienced analysts work with messy files.
  • Loading errors are the easiest class of errors to fix because the diagnostic message is usually precise. When in doubt, read the error, check the docs, and consult Chapter 2.
  • Documentation is a reference, not a tutorial. Get comfortable with help(), ?, and the online API page — they will save you hours over the course.

5.16 Further Reading

5.17 Exercises

Load the Boulder County voter data file (boco_voter_data.txt), which uses a pipe (|) delimiter. Run the post-load checklist. How many rows and columns does it have? What are the column dtypes? Are there any columns that should be loaded as strings but were guessed as numeric?

df = pd.read_csv('boco_voter_data.txt', sep='|')
print('Shape:', df.shape)
print('\nDtypes:')
print(df.dtypes)
print('\nFirst few rows:')
df.head()

Columns like RES_ZIP_CODE, VOTER_ID, and PRECINCT may contain leading zeros and should be loaded as strings using the dtype parameter.

Load the Colorado State Demography Office population file (sya-county.csv). You may see a DtypeWarning about mixed types. Investigate which column triggered the warning by checking .dtypes and inspecting the column’s unique values. What non-numeric value is present, and what should you do about it?

sya_df = pd.read_csv('sya-county.csv', header=1)
print(sya_df.dtypes)
print(sya_df.head())

The DtypeWarning typically indicates that a column contains both numbers and text (such as a header row mixed into the data, or a metadata row). Investigate with .value_counts() and address by specifying dtype on import or by filtering out the problematic rows.

Choose one of the course datasets and write a single cell that loads it cleanly — specifying all necessary read_csv parameters, renaming columns to clean names, converting types as needed, and removing any non-data rows. End the cell by printing the shape and a sample of rows.