Skip to content
Bamboo Weekly

pandas read_csv

Load a CSV file — from your disk, or straight from a URL — into a data frame.

What does the first line of a Pandas project usually look like? It is pd.read_csv, and in nearly every Bamboo Weekly solution that line does not name a file at all. It names a URL:

import pandas as pd

url = ('https://earthquake.usgs.gov/fdsnws/event/1/query.csv'
       '?starttime=2024-01-01&endtime=2024-12-31&minmagnitude=6')

pd.read_csv(url)

Pandas fetches the URL for you. No requests, no download step, no temporary file to clean up — which is why the examples on this page run anywhere, and why so many exercises here start at a live government API.

That is the easy part. The hard part is that read_csv takes more than forty keyword arguments, and getting a handful of them right at load time saves you an afternoon of repair work afterward. Dates left as strings, an identifier column guessed as an integer, forty columns read when you needed four — each is cheap to prevent and painful to undo.

Official documentation: pandas.read_csv

The arguments that earn their keep

pd.read_csv(source,
            usecols=['a', 'b'],       # read only these columns
            dtype={'zip': str},       # stop Pandas from guessing
            parse_dates=['when'],     # real datetimes, not strings
            date_format='%d/%m/%Y',   # ... for when the format is ambiguous
            na_values=['-', 'n/a'],   # what counts as missing
            skiprows=3,               # step over a title block
            header=0,                 # which row holds the column names
            names=['a', 'b'],         # or supply the names yourself
            index_col='id',           # which column becomes the index
            sep='\t',                 # not everything is comma-separated
            thousands=',',            # so "1,234" arrives as a number
            encoding='Latin-1',       # when the file is not UTF-8
            nrows=1000)               # peek at something enormous

I go through the most important of these in The six most important read_csv arguments in Pandas, and cover getting the index right on the way in with Load your data with the right index in Pandas.

A worked example, on real data

The USGS publishes every earthquake it records as CSV, through a public API. No key, no signup — just a URL with a date range and a minimum magnitude. Bamboo Weekly #3 used this same source, and reached for parse_dates for exactly the reason below.

Let's find the five largest earthquakes of 2024:

(
    pd.read_csv(url,
                usecols=['time', 'place', 'mag', 'depth'],
                parse_dates=['time'])
    .sort_values('mag', ascending=False)
    .head(5)
)

                               time    depth  mag                                     place
98 2024-01-01 07:10:09.476000+00:00   10.000  7.5     2024 Noto Peninsula, Japan Earthquake
43 2024-07-19 01:50:48.571000+00:00  127.291  7.4  41 km ESE of San Pedro de Atacama, Chile
73 2024-04-02 23:58:12.173000+00:00   40.000  7.4           15 km S of Hualien City, Taiwan
2  2024-12-17 01:47:25.741000+00:00   54.372  7.3           24 km WNW of Port-Vila, Vanuatu
49 2024-06-28 05:36:36.902000+00:00   24.000  7.2               10 km WSW of Atiquipa, Peru

Two arguments did real work there. usecols meant the eighteen columns we did not want were never read. parse_dates meant that time arrived as a datetime, so it can be sorted, filtered by range, and grouped by month — none of which works on a string that merely looks like a date. And the chain reads cleanly because sort_values hands back a data frame; only inplace=True returns None, which is one of several reasons the core developers ask us not to use it.

usecols: memory you never allocate

Here is a US zip code database, 42,522 rows and sixteen columns, which Bamboo Weekly #42 used to map plant hardiness zones:

zip_url = 'http://uszipcodelist.com/zip_code_database.csv'

everything = pd.read_csv(zip_url)
everything.memory_usage(deep=True).sum() / 1024**2

7.9

Nearly 8 MB, and I wanted three of those columns. Ask for three:

needed = pd.read_csv(zip_url, usecols=['zip', 'primary_city', 'state'])
needed.memory_usage(deep=True).sum() / 1024**2

1.6

Be precise about what happened there: usecols did not free 6 MB, because those 6 MB were never allocated. The columns were skipped during parsing, whereas dropping them afterward would have cost you the full read first. On a file that fits comfortably in memory this is a nicety; on one that does not, it is the difference between working and not. (Note the deep=True; without it you get a number that ignores the contents of string columns — see memory_usage.)

nrows is the same instinct applied to rows. Meeting a large file for the first time, pd.read_csv(url, nrows=100) shows you the columns and the shape of the values in a fraction of a second.

dtype: where the leading zero goes to die

Look closely at the zip codes we just read:

needed.head(3)

   zip primary_city state
0  501   Holtsville    NY
1  544   Holtsville    NY
2  601     Adjuntas    PR

Holtsville, New York is 00501, not 501. The file even says so — the raw line reads "00501","UNIQUE","Holtsville",…, quoted and everything. Quoting does not save you. A column of digits looks numeric to Pandas, so it becomes an int64, and the leading zeros are gone. Tell it otherwise:

pd.read_csv(zip_url, usecols=['zip', 'primary_city', 'state'],
            dtype={'zip': str}).head(3)

     zip primary_city state
0  00501   Holtsville    NY
1  00544   Holtsville    NY
2  00601     Adjuntas    PR

This is the single most common real-world CSV bug I see, and zip codes are only the friendliest example; FIPS county codes, phone numbers, account numbers and ISBNs all have the same shape. If a column is a label rather than a quantity, pass dtype=str for it, even when — especially when — it is made entirely of digits. You will never sum these values, and an integer zip code will not join against a text one.

dtype earns its keep in the other direction too. A column with few distinct values is a good candidate for 'category', and dtype_backend='pyarrow' gives you Arrow-backed columns throughout:

pd.read_csv(zip_url, usecols=['zip', 'primary_city', 'state'],
            dtype_backend='pyarrow').dtypes

zip              int64[pyarrow]
primary_city    string[pyarrow]
state           string[pyarrow]
dtype: object

Note that dtype_backend chooses how columns are stored, not who does the parsing — that is engine=, and engine='pyarrow' is a genuinely faster reader on large files. The two combine, and appear together throughout recent solutions.

Dates: parse_dates, and date_format when that is not enough

parse_dates takes a list of columns and hands them to the datetime parser. When the format is unambiguous, that is all you need. When it is not, here is what happens:

from io import StringIO

csv = "when,value\n01/02/2024,1\n15/03/2024,2\n"

pd.read_csv(StringIO(csv), parse_dates=['when']).dtypes

when       str
value    int64
dtype: object

No exception. No warning at all — I checked, with warnings forced on. The when column is simply still text, and you find out three steps later when .dt.month raises an AttributeError. Say what you mean instead:

pd.read_csv(StringIO(csv), parse_dates=['when'],
            date_format='%d/%m/%Y').dtypes

when     datetime64[us]
value             int64
dtype: object

Nobody remembers whether the month is %m or %M, and I do not either. I keep strfti.me open when I am writing one of these — it shows you the result as you type, which beats scrolling a table of directives. Do check your work, though: a date_format that does not match is exactly as silent as no date_format at all. Bamboo Weekly #65 walks through this failure on real data, landing on date_format='%m/%d/%Y %I:%M:%S %p'. For a column that is already loaded, see to_datetime.

When the file fights back

Real files are not always polite, and a handful of arguments handle most of the rudeness.

na_values and its companion keep_default_na decide what counts as missing, and the defaults have teeth. Bamboo Weekly #35 grouped countries by continent and got five back instead of six, because North America is abbreviated NA, which read_csv treats as missing. So is Namibia's country code:

csv = "country,continent\nCanada,NA\nBrazil,SA\nNamibia,NA\n"

pd.read_csv(StringIO(csv))

   country continent
0   Canada       NaN
1   Brazil        SA
2  Namibia       NaN

keep_default_na=False turns that off, after which you can name the strings you do want treated as missing with na_values.

skiprows, header and names deal with files that put something other than column names on line one. NASA's global temperature record opens with a title line, so a naive read produces a single column named after the title:

gistemp = 'https://data.giss.nasa.gov/gistemp/tabledata_v4/GLB.Ts+dSST.csv'

pd.read_csv(gistemp, nrows=2).columns.tolist()

['Land-Ocean: Global Means']

Step over it, declare what missing looks like, and set the index in the same call:

(
    pd.read_csv(gistemp, skiprows=1, na_values=['***'], index_col='Year')
    [['Jan', 'Jul', 'J-D']]
    .tail(3)
)

       Jan   Jul   J-D
Year
2024  1.25  1.20  1.28
2025  1.38  1.02  1.19
2026  1.08  1.23   NaN

sep handles files that are comma-separated in name only — tabs, semicolons, pipes. And storage_options handles the ones that refuse to talk to you at all. The Bureau of Labor Statistics, source of the JOLTS and inflation data behind several Bamboo Weekly issues, returns HTTP 403 to Pandas' default user agent and wants a contact address in the header:

url = 'https://download.bls.gov/pub/time.series/jt/jt.state'

pd.read_csv(url, sep='\t')

HTTPError: HTTP Error 403: Forbidden

(
    pd.read_csv(url, sep='\t',
                storage_options={'User-Agent': 'Pandas (you@example.com)'},
                usecols=['state_code', 'state_text'])
    .head(4)
)

  state_code state_text
0         00   Total US
1         01    Alabama
2         02     Alaska
3         04    Arizona

Wikipedia behaves the same way, where a plain storage_options={'User-Agent': 'Mozilla/5.0'} is enough.

Two more by name: thousands=',' turns "1,234,567" into a number rather than a string, and encoding='Latin-1' rescues the older government files that predate universal UTF-8.

Five mistakes people make

Letting Pandas guess the dtype of an identifier column. Zip codes, FIPS codes, phone numbers and account IDs are labels made of digits. Pandas reads them as integers and 00501 becomes 501, silently and permanently. Pass dtype={'zip': str}.

Reading the whole file and then throwing most of it away. usecols skips columns at parse time; df.drop(columns=…) does not. If you need four columns out of forty, say so on the way in.

Assuming parse_dates worked. It fails quietly, leaving you a column of strings and no error message. Check df.dtypes after any read where dates matter — it costs one line.

Forgetting thousands. One comma inside "1,234" makes the entire column text. Your sums fail, your plots come out as a bar chart of every distinct string, and nothing anywhere says why.

Reaching for low_memory on stale advice. The internet is full of confident and contradictory claims here, so I checked. On Pandas 3.0.5 the argument still exists, still defaults to True, and still does something: under the default C engine, a column whose type changes partway through the file comes back as object, with DtypeWarning: Columns (0: a) have mixed types. Specify dtype option on import or set low_memory=False. Passing low_memory=False does fix that, at the cost of buffering the whole file — but note which remedy the warning names first. dtype= states your intent rather than hoping inference lands well. And with the pyarrow reader the argument is not merely useless; it raises ValueError: The 'low_memory' option is not supported with the 'pyarrow' engine. The name was always backwards: low_memory=False uses more memory, not less.

Where it shows up in Bamboo Weekly

read_csv appears in 98 Bamboo Weekly posts, across 97 issues — more than any other method on this site. These four are free to read, and each leans on a different argument.

Bamboo Weekly #3 is the earthquake data above, and the cleanest illustration of why parse_dates belongs in the read rather than in a follow-up step.

Bamboo Weekly #42 joins two USDA plant hardiness releases against a zip code database. Every read in it carries dtype={'zipcode': str} alongside usecols and index_col — without which the join would match nothing at all.

Bamboo Weekly #65 is the date_format post: it starts with parse_dates=['Date'], shows what Pandas does when it cannot infer a format, and fixes it with an explicit one.

Bamboo Weekly #35 is the NA-is-North-America story, and worth reading as a correction rather than a solution — a reader spotted the missing continent, and the updated post carries the keep_default_na=False fix. It also uses positional usecols=[0, 22] with skiprows=3 and names=, which is how you read one column out of a file whose headers you would rather not trust.

Practice it

Work through a read_csv exercise, with instant feedback and no signup required: practice.lernerpython.com/bamboo-weekly/read-csv/

Go deeper

Bamboo Weekly is the practice. If you want the structured version — full courses with downloadable Jupyter notebooks, plus live Pandas office hours when you get stuck — that is LernerPython+Data. A paid Bamboo Weekly subscription is included with it.

If your data is in a spreadsheet, see read_excel, which shares usecols, header, skiprows, nrows and na_values with this function and adds sheet_name. If it is in an HTML table, see read_html. And once it is loaded, memory_usage tells you what it cost you.

See it on real data

Below are the 100 Bamboo Weekly exercises that use read_csv on real-world data — try each one, then study the worked solution.

Part of the Pandas Methods Index. See also practice by skill.