CSV Options That Matter
Overview
Separators, headers and the arguments you reach for on a real file.
What You Will Learn in This Lesson
By the end of this lesson, you will be able to:
- Read a file whose fields are not separated by commas.
- Load a file that has no header row, and name the columns yourself.
- Skip junk at the top of a file.
- Recognise a wrong separator from the shape alone.
Why This Matters
read_csv makes three assumptions about every file: comma separated, first line is the header, types inferred. Real exports break each of them, and none of the breakages raises — you get a table, it is just the wrong one. Four arguments cover almost every real file you will meet.
Step 1: The file is not always comma separated
sep tells read_csv what divides the fields. A tab-separated export read with the default gives one column holding the whole line.
Naming the separator
Tabs are "\t", semicolons are common in exports from locales where the comma is a decimal point. Pipe-separated files turn up too.
import pandas as pd
from io import StringIO
tabbed = "name\tscore\nAda\t92\n"
print(pd.read_csv(StringIO(tabbed), sep="\t"))What it looks like when you get it wrong
The load succeeds. The shape is the tell: one column where you expected several, and a column name that is the entire header line.
import pandas as pd
from io import StringIO
tabbed = "name\tscore\nAda\t92\n"
wrong = pd.read_csv(StringIO(tabbed))
print(wrong.shape)
print(list(wrong.columns))This is why df.shape is the first of the four checks. It catches this before any number you compute from the table can mislead you.
Mini Practice #1: Same data, two separators
Try It YourselfRead this semicolon file twice — once with the default and once correctly — and print both shapes.
Step 2: Headers, and files without one
header=None stops pandas taking the first row of data as your column names, and names= supplies your own.
A file with no header
Without header=None, your first row of real data silently becomes the column names and you are quietly one row short.
import pandas as pd
from io import StringIO
raw = "Ada,92\nGrace,88\n"
print(pd.read_csv(StringIO(raw), header=None, names=["name", "score"]))
print(len(pd.read_csv(StringIO(raw)))) # one row lostJunk above the header
Exports from reporting tools often carry a title and a blank line before the real header. skiprows steps over them.
import pandas as pd
from io import StringIO
messy = "Quarterly report\n\nname,score\nAda,92\n"
print(pd.read_csv(StringIO(messy), skiprows=2))Mini Practice #2: Supply the names
Try It YourselfThis file has no header. Load it with your own column names and confirm you have two rows, not one.
Step 3: When to Use It
Files that are not plain comma-separated
Tabs, semicolons, no header, or a few junk lines at the top: sep, header, names and skiprows.
pd.read_csv(path, sep="\t")Files from other countries or tools
Different separators and decimal marks are common in exports; say what the file uses.
When Not to Use It
Do not keep adding options until it stops raising. Check the resulting shape and column names — a load that runs with the wrong options is still wrong.
End-of-Lesson Exercises
Exercise 1: Count the rows in a tab-separated file
A tab-separated file scores.tsv is supplied with name and score columns. Write row_count(path) that reads it properly and returns the number of rows.
Without sep="\t" the whole line lands in one column and you get one column and three rows — the row count would look right and the table would be wrong. len(df) or df.shape[0] counts the rows.
Exercise 2: A file with no header
A file raw.csv holds names and scores with no header row. Write headerless(path) that reads it, naming the columns name and score, and returns the number of rows. Losing a row to the header is the mistake being checked.
Supplying names= tells pandas there is no header to read. Reading it with neither argument takes your first row of data as the column names, and the count comes back one short.