Lesson 21

Reading and Writing Files

Load a CSV into a DataFrame, control the separator, header, and index, and write your result back out as CSV, text, or JSON.

Intermediate25 min

Reading and Writing Files hero Module 24 Lesson 21

Most analysis starts with a file. pd.read_csv(path) gives you a DataFrame and df.to_csv(path) writes one back. The arguments in between are what turn a messy file into a clean table.

The round trip from a CSV file to a cleaned file

Write a file, then read it back

index=False matters more than it looks. Without it the row labels become a first column in the file, and reading it back gives you a stray Unnamed: 0 column.

python

import pandas as pd

learners = pd.DataFrame({
    "name": ["Avery", "Jordan", "Casey", "Riley", "Morgan", "Quinn"],
    "track": ["Python", "NumPy", "Python", "pandas", "NumPy", "pandas"],
    "city": ["Northgate", "Eastport", "Northgate", "Westfield", "Eastport", "Westfield"],
    "hours": [42, 31, 55, 24, 47, 38],
    "score": [88, 74, 91, 65, 82, 79],
})
learners.to_csv("learners.csv", index=False)

loaded = pd.read_csv("learners.csv")
print(loaded.head(3))
print(loaded.shape)
print(loaded.dtypes)

Output:

text

     name   track       city  hours  score
0   Avery  Python  Northgate     42     88
1  Jordan   NumPy   Eastport     31     74
2   Casey  Python  Northgate     55     91
(6, 5)
name     object
track    object
city     object
hours     int64
score     int64
dtype: object

Read from text you already have

read_csv accepts any file-like object, so wrapping a string in io.StringIO lets you practise without a file on disk. Everything you learn here works the same on a real path.

python

import io

import pandas as pd

raw = '''item,shelf,price,count
desk lamp,A1,24.5,2
notebook,B2,3.2,10
backpack,A3,41.0,1
'''
stock = pd.read_csv(io.StringIO(raw))
print(stock)
print(stock["price"].sum())

Output:

text

        item shelf  price  count
0  desk lamp    A1   24.5      2
1   notebook    B2    3.2     10
2   backpack    A3   41.0      1
68.7

Separators, headers, and the index

sep sets the separator for a tab or semicolon file. header=None says the first line is data, and then names supplies the column names. index_col promotes a column to the row index while reading.

python

import io

import pandas as pd

tabbed = "desk lamp\tA1\t24.5\nnotebook\tB2\t3.2\n"
stock = pd.read_csv(
    io.StringIO(tabbed), sep="\t", header=None, names=["item", "shelf", "price"]
)
print(stock)

keyed = pd.read_csv(io.StringIO(tabbed), sep="\t", header=None,
                    names=["item", "shelf", "price"], index_col="item")
print(keyed)

Output:

text

        item shelf  price
0  desk lamp    A1   24.5
1   notebook    B2    3.2
          shelf  price
item                  
desk lamp    A1   24.5
notebook     B2    3.2

Big files, small pieces

usecols reads only the columns you need and nrows reads only the first few rows, which is how you peek at a large file cheaply. na_values adds your own spellings of missing to the list pandas already knows, which includes an empty field, NA, and n/a.

python

import io

import pandas as pd

raw = '''item,shelf,price,count
desk lamp,A1,24.5,2
notebook,B2,--,10
backpack,A3,41.0,1
mug,B1,8.75,6
'''
print(pd.read_csv(io.StringIO(raw), usecols=["item", "price"], nrows=2))

plain = pd.read_csv(io.StringIO(raw))
print(plain["price"].dtype)

stock = pd.read_csv(io.StringIO(raw), na_values=["--"])
print(stock["price"].isna().sum())
print(stock["price"].dtype)

Output:

text

        item price
0  desk lamp  24.5
1   notebook    --
object
1
float64

Without na_values the dashes are ordinary text, so the whole price column stays object. With it, the dash becomes NaN and the column reads as numbers.

Writing other shapes

to_csv with sep="\t" writes a tab-separated file. to_json writes records for a web service, and to_excel writes a spreadsheet once the openpyxl package is installed. to_string is for printing, not for saving.

python

import pandas as pd

learners = pd.DataFrame({
    "name": ["Avery", "Jordan", "Casey", "Riley", "Morgan", "Quinn"],
    "track": ["Python", "NumPy", "Python", "pandas", "NumPy", "pandas"],
    "city": ["Northgate", "Eastport", "Northgate", "Westfield", "Eastport", "Westfield"],
    "hours": [42, 31, 55, 24, 47, 38],
    "score": [88, 74, 91, 65, 82, 79],
})
learners.head(2).to_csv("sample.tsv", index=False, sep="\t")
print(open("sample.tsv").read())
print(learners.head(2)[["name", "score"]].to_json(orient="records"))

Output:

text

name	track	city	hours	score
Avery	Python	Northgate	42	88
Jordan	NumPy	Eastport	31	74

[{"name":"Avery","score":88},{"name":"Jordan","score":74}]

This page runs in your browser, so a file you write lives in a temporary in-memory folder and disappears when you reload.

Key takeaways

pd.read_csv reads a path or any file-like object
Always pass index=False to to_csv unless the labels are real data
sep, header, names, and index_col shape an awkward file into a good table
usecols and nrows make a first look at a large file cheap
Next: Exploring a Dataset
Previous: Combining DataFrames

Loading playground…

Check your understanding

Reading and Writing Files Quiz

Which function reads a CSV file?

1 / 10

← Combining DataFrames

Exploring a Dataset →