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.
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.
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
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:
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:
order_id,date,store_id,qty then lines of text.pd.read_csv('orders.csv'): date comes in as text (object).parse_dates=['date']: date becomes datetime64.orders.info(): 9,717 rows, the right types, no surprise missing values.4. Your turn
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
- A file has two title lines before the header. Which option?
- Missing values appear as
.. Which option? - Why add
index=Falsewhen saving?
Answers
skiprows=2.na_values=['.'].- 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
Sources
- pandas documentation, User Guide: IO tools (text, CSV) https://pandas.pydata.org/docs/user_guide/io.html
- McKinney, W. (2022), Python for Data Analysis, 3rd edition, O'Reilly (open access) https://wesmckinney.com/book/
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.