All lessons

Module 1: Getting started · Lesson 1.5 · about 20 min

Reading CSV and Excel files

Load data with read_csv and read_excel, and use the options that fix the usual problems: separators, header rows, column names, dates, text codes that look like numbers, and missing-value markers.

Key points, meanings and formulas on one page: choose "Save as PDF" in the print box.

Module 1 · about 20 minutes · uses Lesson 1.4. You’ll be able to read CSV and Excel files and fix the common reading problems with options instead of manual edits.

read_csv turns a text file into a DataFrame, and a few options (dates, separators, missing markers, column types) decide whether it arrives clean.

In everyday terms: unpacking a delivery: you tell the person unpacking which boxes hold dates, which are fragile (IDs that must stay text), and what “n/a” scribbled on a box means.

1. read_csv

Pythonorders.csv

That’s most of reading files. The rest of this lesson is the options for files that aren’t so tidy.

2. The options you’ll need

Problem Option
Not commas (;, tabs) sep=';', sep='\t'
Junk lines above the header skiprows=3
No header row header=None, names=['a', 'b']
Only some columns usecols=['date', 'qty']
Dates as text parse_dates=['date']
Codes with leading zeros dtype={'postcode': str}
Missing written as - or n/a na_values=['-', 'n/a']
Thousands with commas (1,250) thousands=','
Accents garbled encoding='latin-1' (or 'cp1252')

Try a few on a small “awkward” file. io.StringIO lets us pretend a piece of text is a file:

Python

Remove the options one at a time and run again to see what each fixes.

3. Excel files

sales = pd.read_excel('sales.xlsx')                       # first sheet
sales = pd.read_excel('sales.xlsx', sheet_name='2026')    # a named sheet
all_sheets = pd.read_excel('sales.xlsx', sheet_name=None) # every sheet, as a dict of DataFrames

read_excel takes the same skiprows, usecols, dtype and na_values options. On your own computer it needs the openpyxl package (pip install openpyxl). The in-page Python here doesn’t include it, so the course datasets are CSV.

Writing files is the mirror image: df.to_csv('out.csv', index=False) and df.to_excel('out.xlsx', index=False). index=False stops pandas writing the 0, 1, 2 row labels as an extra column.

parse_dates=['date']
Read these columns as real dates, not text.
dtype={'zip': str}
Keep codes as text so leading zeros survive.
na_values=['n/a', '-']
Extra markers to treat as missing.
sep=';'
The separator if it is not a comma.
Physical meaning
A CSV is just text with commas. pandas guesses each column's type from that text; the options are how you correct the guesses before the mistakes spread into every later step.

“If it loaded without an error, it loaded correctly.” A postal code 01234 can arrive as the number 1234, dates as text, and “n/a” as a word. Always run info() and look at a few rows right after reading.

Watch it happen, one step at a time:

The file. order_id,date,store_id,qty then lines of text.
Plain read. pd.read_csv('orders.csv'): date comes in as text (object).
Tell it about dates. parse_dates=['date']: date becomes datetime64.
Check. orders.info(): 9,717 rows, the right types, no surprise missing values.
Ready. Now date filters, months and resampling all work.

4. Your turn

Your turnorders.csv
Your turnproducts.csv
How it works

Principle: a CSV file is just text. read_csv splits each line at the separator, takes the first line as column names, and guesses each column’s type from its values. Most reading problems are wrong guesses, and each option corrects one.

Why fix on read, not later: reading 01234 as a number loses the zero for good; reading dates as text makes every later step awkward. Options on read keep the raw file untouched and the fix repeatable.

5. Practice

  1. A file has two title lines before the header. Which option?
  2. Missing values appear as .. Which option?
  3. Why add index=False when saving?
Answers
  1. skiprows=2.
  2. na_values=['.'].
  3. Otherwise the row labels (0, 1, 2, …) are saved as an extra unnamed column.

Try it on your own data: the CSV profiler tool shows what’s in any CSV (types, missing values, odd entries) before you write the read_csv line.

New words: CSV, separator, header, parse dates, encoding, read_excel.

  • read_csv reads text files; read_excel reads .xlsx (needs openpyxl).
  • Use parse_dates for dates and dtype=str for codes with leading zeros.
  • na_values adds extra missing-value markers.
  • Check info() and head() right after reading.

Check yourself

1A file uses semicolons between values. Which option reads it?
2Postal codes like 01234 lose their leading zero. How do you keep it?
3What does parse_dates=["date"] do?

Sources

Your ticks stay in this browser. Save them as a file on the course home.

Updated 2026-11-22. All datasets are made up. Python runs in your browser (the first run downloads it once, about 20 MB); nothing you type is sent anywhere.