Skip to content
Tutorials Center
My learning

Topics

Lesson 5 of 64 ยท Getting Started

Reading CSV Data

Learn pandas read_csv: separators, headers, names, usecols, index_col, dtype, parse_dates, na_values, nrows, skiprows and reading big files in chunks.

Most real data starts life as a CSV file: a text file where each line is a row and commas separate the columns. pd.read_csv() turns such a file into a DataFrame, and it is probably the pandas function you will call more than any other. It has dozens of options, but about a dozen of them solve nearly every real-world problem.

In this lesson the "file" is always a string wrapped in io.StringIO, which behaves like an open text file. That is exactly how it works in the browser, where there are no real files. On your own computer you would pass a path instead: pd.read_csv("sales.csv").

flowchart LR
    A["CSV text<br/>(file, string, URL)"] --> B["read_csv<br/>split, infer types, parse"]
    B --> C["DataFrame"]
    O["options:<br/>sep, header, usecols,<br/>dtype, parse_dates, na_values"] --> B

read_csv turns text into a typed table; its options control how the text is interpreted.

The basic call

By default, read_csv treats the first line as the header, splits on commas and guesses a type for every column:

Python read_csv from a string

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

pandas found integers, floats, booleans and text without being told. That guessing is convenient, but it is also where most bugs come from, so the options below let you take control.

Note: read_csv also accepts file paths, URLs (https://...) and compressed files (.csv.gz, .zip). Those are read-only ideas here: the browser sandbox has no files and no network. The options you learn below work identically for all sources.

Separators, headers and column names

Not every file uses commas. European exports often use semicolons, and logs or database dumps may use tabs. Use sep= (or delimiter=). Some files have no header row; use header=None and supply names= yourself:

Python sep, header and names

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

If you also want to replace an existing header, pass both header=0 and names=[...]. Without header=0, the old header line would be read as data.

Choosing columns and the index

Real files often have more columns than you need. usecols= loads only the ones you name, which saves memory and time. index_col= turns a column into the row index while reading:

Python usecols and index_col

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Controlling data types

Type guessing has traps. An ID such as 00123 is read as the number 123, losing its leading zeros. A column with one stray word becomes text. Fix this with dtype=, which maps column names to types:

Python dtype keeps leading zeros

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

As a rule of thumb, identifiers (zip codes, phone numbers, product codes) are labels, not quantities, so read them as strings.

Dates

CSV has no date type, so a date column arrives as text. Ask for parsing with parse_dates=. Check the result with dtypes; you should see datetime64:

Python parse_dates

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Dates in unusual formats are better converted afterwards with pd.to_datetime(..., format=...), which is covered in Dates and Times.

Missing values

By default, pandas treats empty cells and strings like NA, N/A, NaN and null as missing. Real files invent their own markers: -, ?, missing, -999. Tell pandas about them with na_values=:

Python na_values

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The sentinel value -999 is dangerous because it looks like a real number. Converting it to a missing value at read time stops it from silently skewing averages.

Skipping rows and limiting rows

Some files start with a title or comments before the header. skiprows= skips lines at the top (or a list of line numbers), and comment="#" ignores lines starting with a character. nrows= reads only the first n rows, which is great for peeking at a huge file:

Python skiprows, comment and nrows

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Counting is by physical line, so the order matters: the first call skips the report line and ignores the comment line, while the second skips both lines by number.

Reading big files in chunks

A file that does not fit in memory can be read piece by piece. With chunksize=, read_csv returns an iterator of DataFrames. You process each piece and combine only the small results:

Python Chunked reading

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Only 250 rows are in memory at a time, yet the final average covers all 1,000. The same idea scales to files with hundreds of millions of rows. Memory Usage and Smaller Dtypes and When Data Gets Big continue the topic.

Cheat sheet of options

Problem Option
Not comma separated sep=";", sep="\t"
No header row header=None, names=[...]
Need only some columns usecols=[...]
A column should be the index index_col="id"
Numbers read wrongly dtype={"zip": "str"}
Dates stay text parse_dates=["day"]
Strange missing markers na_values=[...]
Junk at the top skiprows=n, comment="#"
Just a peek nrows=n
Too big for memory chunksize=n

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. A column of zip codes like 02134 loses its leading zero after read_csv. What is the best fix?

    pandas guessed an integer type. Reading identifiers as strings preserves every character, including leading zeros.
  2. 2. Which option reads only the columns "id" and "score" from a wide file?

    usecols loads only the named columns. names renames columns and nrows limits rows, not columns.
  3. 3. What does chunksize=1000 make read_csv return?

    With chunksize, read_csv returns an iterator, and each iteration gives the next DataFrame of up to that many rows, so memory stays small.
  4. 4. Your file uses -999 to mean "no reading". How do you make it NaN while reading?

    na_values lists extra strings that should be treated as missing values, so the sentinel never gets into your calculations.
  5. 5. What is the first thing read_csv does with the first line of the file by default?

    header="infer" is the default, which means the first line becomes the column names unless you pass header=None.

Summary

  • pd.read_csv(source) returns a DataFrame; the source can be a path, URL or file-like object such as io.StringIO.
  • Control parsing with sep, header, names, usecols and index_col.
  • Prevent bad type guesses with dtype=; read identifiers as strings.
  • Use parse_dates= for date columns and na_values= for custom missing markers.
  • skiprows, comment and nrows handle junk at the top and quick peeks.
  • chunksize= reads huge files piece by piece.

Next: Writing and Formatting Data, the other direction: from DataFrame back to text.