skip to content

Readers and Writers

The read_* and to_* functions move frames in and out of CSV, Parquet, SQL, Excel and JSON. Interviewers ask about dtype control on read, chunking and why Parquet beats CSV.

on this pageshow

explore

questions

12

In pandas, why does a DataFrame saved with df.to_csv('out.csv') come back from pd.read_csv with an extra 'Unnamed: 0' column?

level: juniorimportance: must knowfreq 70%

answer

  1. what the writer adds by default
  2. row labels become a field
  3. an empty header cell
  4. the index keyword on the writer side

basics

~10 s

DataFrame.to_csv writes the row index as the first column by default (index=True), under an empty header when the index has no name. read_csv names that empty header 'Unnamed: 0' and builds a fresh RangeIndex.

solid answer

~30 s

`DataFrame.to_csv` defaults to `index=True`, so the row labels are written as the first field of every line, and an unnamed index gets an empty header cell: the file starts `,session_id,clicks`. `read_csv` has no way to know that column was an index, so it names the empty header `Unnamed: 0` and gives the frame a new `RangeIndex`. The fix depends on whether the index carries information. For a default `RangeIndex`, write with `df.to_csv(path, index=False)`. For a meaningful index such as a user id, keep it, name it (`df.index.name = 'user_id'` or `index_label=`), and read it back with `pd.read_csv(path, index_col=0)` or `index_col='user_id'`.

code

python · 16 lines
python
import io

import pandas as pd

df = pd.DataFrame({"session_id": ["s1", "s2"], "clicks": [3, 5]})

buf = io.StringIO()
df.to_csv(buf)  # index=True by default
print(buf.getvalue().splitlines()[0])  # ,session_id,clicks
buf.seek(0)
print(pd.read_csv(buf).columns.tolist())  # ['Unnamed: 0', 'session_id', 'clicks']

clean = io.StringIO()
df.to_csv(clean, index=False)
clean.seek(0)
print(pd.read_csv(clean).columns.tolist())  # ['session_id', 'clicks']

go deeper

for a junior

Recall that to_csv writes the index by default and that index=False on the writer is the everyday fix for a meaningless RangeIndex.

for a middle

Explain the blank header cell, why read_csv names it Unnamed: 0, and when to keep the index and restore it with index_col instead of dropping it.

for a senior

Set the convention for shared files: name meaningful indexes, write the rest with index=False, and let compression='infer' handle .gz on both ends.

for a principal

Treat every CSV hand-off as schema-less: decide per dataset which side owns the index choice so no consumer has to guess at Unnamed columns.

## What `to_csv` writes by default A pandas **DataFrame** has two kinds of labels: the **columns** (names across the top) and the **index** (row labels down the side). When you create a frame without saying otherwise, the index is a `RangeIndex` of 0, 1, 2 and so on. It is not data you entered, but it is part of the object. `DataFrame.to_csv` treats the index as worth saving. Its signature declares `index: bool = True`, so every line of the file starts with that row's label. The header line needs a name for that first field, and pandas uses the index's name. When the index has no name (the usual case) the header cell is left **empty**, so a two-column frame is written as: ``` ,session_id,clicks 0,s1,3 1,s2,5 ``` ## What `read_csv` does with that column `pd.read_csv` sees three header cells, one of them blank. It does not know the blank one used to be an index, so it treats it as an ordinary column. The reader's docstring states the naming rule: empty headers are named `"Unnamed: {i}"`, where `i` is the column position. The result is a frame with columns `Unnamed: 0`, `session_id` and `clicks`, plus a brand-new `RangeIndex` of its own. Write and read that frame a second time and you get `Unnamed: 0` and `Unnamed: 0.1`, because duplicate headers are suffixed with `.1`, `.2` and so on. Nothing is broken in either function. The writer kept information, and the reader had no metadata telling it what that information was. A CSV file carries no schema, so the choice has to be made in code on one side or the other. ## Choosing the fix | Situation | Writer | Reader | |---|---|---| | Index is a meaningless `RangeIndex` | `df.to_csv(path, index=False)` | `pd.read_csv(path)` | | Index holds real keys (user ids, dates) | `df.to_csv(path)` after naming the index | `pd.read_csv(path, index_col='user_id')` | | File already written with a blank index header | nothing to change now | `pd.read_csv(path, index_col=0)` | | `MultiIndex` with two levels | `df.to_csv(path)` | `pd.read_csv(path, index_col=[0, 1])` | Some points behind the table: - **`index=False` is the usual answer** for a frame whose index came from the constructor or from `reset_index`. It also makes the file easier for any other tool to read. - **Name a meaningful index** with `df.index.name = 'user_id'` or pass `index_label='user_id'` to `to_csv`; the header then says what the column is, and `index_col` can refer to it by name. - **`index_col=False` does the opposite of what it sounds like.** It forces `read_csv` *not* to use the first column as the index, which the docstring suggests for malformed files with a delimiter at the end of every line. - **`read_csv` has no `index` keyword**, so `pd.read_csv(path, index=False)` fails with a `TypeError` instead of fixing anything. - **Dropping `Unnamed: 0` after every read** works, but it hides a writer decision that one argument would have made explicit. ## Other writer defaults worth knowing `to_csv` has a few more defaults that surprise people on a round trip: - `compression='infer'`: a path ending in `.gz`, `.bz2`, `.zip`, `.xz`, `.zst` or a `.tar` variant is compressed with that method. `read_csv` also defaults to `compression='infer'`, so `pd.read_csv('clicks.csv.gz')` decompresses without being told. A dict such as `{'method': 'gzip', 'compresslevel': 1}` tunes the codec. - `na_rep=''`: missing values are written as empty fields, which the reader's default missing markers turn back into missing values. - `date_format=None`: datetimes are written in their default text form unless you pass a format string. - `path_or_buf=None`: with no path at all, `to_csv` returns the CSV text as a string, which is handy in tests. - `mode='w'` truncates; `mode='a'` appends, and you then want `header=False` so the header is not repeated in the middle of the file. ## A round-trip checklist 1. Decide whether the index is data. If it is not, write with `index=False`. 2. If it is, give it a name before writing so the header is not blank. 3. On the read side, pass `index_col` with that name (or position) to restore it. 4. Let the `.gz` extension pick compression on both sides instead of passing the codec twice. 5. Read the file back once in a test and compare `df.columns` with what you wrote.

  • The index holds user ids you need to keep. How do you make the round trip clean?
    Name the index before writing, with `df.index.name = 'user_id'` or `to_csv(path, index_label='user_id')`, so the header cell is no longer blank. Then read with `pd.read_csv(path, index_col='user_id')`, which makes that column the index again instead of a data column. For a file already written with a blank header, `index_col=0` restores it by position.
  • How do you write the same frame gzip-compressed, and does read_csv need to be told?
    `df.to_csv('clicks.csv.gz', index=False)` is enough: `compression` defaults to `'infer'`, which picks gzip from the `.gz` extension. `pd.read_csv('clicks.csv.gz')` also infers it. Pass `compression='gzip'` or a dict like `{'method': 'gzip', 'compresslevel': 1}` when the name gives no hint or you want to tune the codec.

  • read_csv invents the Unnamed: 0 column on its own
  • Passing index=False to read_csv removes the extra column
  • to_csv leaves the index out unless index=True is passed
  • index_col=False tells read_csv to use the first column as the index
  • Dropping Unnamed: 0 after every read is the proper fix
open as a page

In pandas 3.0, how do read_csv's dtype and parse_dates keep zip codes like 02134 intact and timestamps typed, and what dtypes result?

level: middleimportance: must knowfreq 75%

basics

~10 s

Pass dtype={'zip': 'str'} so the parser never turns zip codes into numbers, and parse_dates=['ts'] for timestamps. In pandas 3.0 the zip column gets the str dtype and ISO timestamps become datetime64[us].

open as a page

When pandas DataFrame.to_sql loads a nightly result into an existing database table, what do if_exists and index default to, and what goes wrong?

level: middleimportance: must knowfreq 60%

basics

~10 s

if_exists defaults to 'fail', so an existing table raises ValueError; index defaults to True, so the DataFrame index is written as an extra column. Use 'append', or in pandas 3.0 'delete_rows', with index=False.

open as a page

In pandas read_csv, how do na_values and keep_default_na decide which strings become NaN, and when must you turn the defaults off?

level: middleimportance: should knowfreq 50%

basics

~10 s

read_csv treats a built-in list of strings ('', 'NA', 'NULL', 'null', 'None', 'nan' and others) as missing, and na_values adds to that list. Set keep_default_na=False when real values such as 'NA' must survive.

open as a page

In pandas, how do you read every sheet of an Excel workbook, what does read_excel return, and how do you write several sheets back?

level: middleimportance: should knowfreq 50%

basics

~10 s

pd.read_excel(path, sheet_name=None) returns a dict of DataFrames keyed by sheet name; the default sheet_name=0 reads only the first sheet. To write several sheets, open one pd.ExcelWriter and call to_excel(writer, sheet_name=...) per frame.

open as a page

In pandas, how do you load a newline-delimited JSON event log with nested user objects and item arrays into flat DataFrames?

level: middleimportance: should knowfreq 55%

basics

~10 s

Read it with pd.read_json(path, lines=True), which keeps nested objects as dicts and arrays as lists, then flatten with pd.json_normalize: nested keys become dotted columns, and record_path with meta gives one row per array element.

open as a page

In pandas, how do read_csv's chunksize, iterator=True and low_memory differ, and which of them bounds memory on a 20 GB clickstream file?

level: seniorimportance: should knowfreq 55%

basics

~10 s

chunksize and iterator=True make read_csv return a TextFileReader that yields DataFrames piece by piece, which bounds memory. low_memory=True only parses in internal pieces and still returns one full DataFrame.

open as a page

In pandas, read_csv on a clickstream file fails with 'Expected 6 fields in line 48213, saw 7': what do the on_bad_lines options do, and which would you choose?

level: seniorimportance: should knowfreq 45%

basics

~10 s

on_bad_lines decides what happens to lines with too many fields: 'error' (default) raises ParserError, 'warn' drops each with a ParserWarning, 'skip' drops silently, and a callable works only with engine='python' or 'pyarrow'.

open as a page

A nightly pandas job writes Parquet with to_parquet(partition_cols=['run_date']): how do you read one week of three columns with read_parquet?

level: seniorimportance: should knowfreq 45%

basics

~10 s

Call read_parquet on the dataset directory with columns= naming the three columns and filters=[('run_date', 'in', week)]: unread columns are never decoded, other days' directories are skipped, and the pyarrow engine also drops non-matching rows.

open as a page

A pandas to_sql load of two million rows through a SQLAlchemy engine is slow or hits packet limits: what do chunksize, method='multi' and an ADBC connection change?

level: seniorimportance: should knowfreq 40%

basics

~20 s

chunksize sets rows per batch (default: all at once); method='multi' packs each chunk into one multi-row INSERT, so chunksize must keep rows times columns under the database's parameter limit. An ADBC connection bulk-ingests through Arrow and rejects chunksize and method.

open as a page

A lead must standardise how a team's pandas jobs hand DataFrames to each other on disk: which writer do you choose, and what does that choice cost?

level: principalimportance: should knowfreq 35%

basics

~20 s

Standardise on to_parquet with the pyarrow engine and index=False for handoffs: it round-trips most pandas dtypes and supports column and filter pushdown. The cost is pinning PyArrow, enforcing string column names and no Python-object columns, and keeping CSV and Excel as edge exports.

open as a page

In pandas read_csv, what do engine='pyarrow' and dtype_backend='pyarrow' each change, and what stops working when you switch to the pyarrow engine?

level: seniorimportance: nice to knowfreq 35%

basics

~20 s

engine picks the parser that reads the text; dtype_backend picks the dtypes of the resulting columns. They are independent. The pyarrow engine is multithreaded but rejects chunksize, iterator, nrows, comment, thousands, converters and several other options with ValueError.

open as a page