Getting Data In: Files and Formats

pd.read_csv('data.csv') works about eighty per cent of the time. This chapter is about the other twenty, and about the failures that do not raise an error but quietly corrupt your data.

CSV Is Not a Format

A CSV file is a convention, not a standard. There is no header saying what the encoding is, what separates the fields, or what a missing value looks like. Every reader has to guess, and pandas guesses well enough that you may not notice when it guesses wrong.

Here is a load that states its assumptions instead of leaving them to chance:

import pandas as pd

df = pd.read_csv(
    'orders.csv',
    dtype={'customer_id': 'string', 'postcode': 'string'},
    parse_dates=['created_at'],
    na_values=['', 'NA', 'N/A', 'null', 'NULL', '-', 'unknown', '#N/A'],
    thousands=',',
    encoding='utf-8',
)

Each of those arguments prevents a specific, common disaster.

The identifier disaster

This is the most expensive default in pandas and it costs people days.

# customer_id column contains: 0012345
df = pd.read_csv('customers.csv')
df.customer_id[0]        # 12345   ← the leading zeros are gone forever

Pandas saw digits and made an integer. That identifier will now never match anything again, and nothing warned you.

Anything that is an identifier rather than a quantity must be loaded as text. Customer IDs, postcodes, phone numbers, account numbers, product SKUs, national insurance numbers. The test is simple: would you ever add two of them together? If not, it is text.

dtype={'customer_id': 'string', 'postcode': 'string', 'phone': 'string'}

Encoding

An encoding is the mapping from bytes to characters. Get it wrong and you get either an exception or, worse, silently mangled text.

# UnicodeDecodeError: 'utf-8' codec can't decode byte 0xa3

That byte, 0xa3, is a pound sign in Windows-1252. Two things to try:

pd.read_csv(path, encoding='utf-8')          # try first, it is the standard
pd.read_csv(path, encoding='latin-1')        # never fails, may produce nonsense
pd.read_csv(path, encoding='cp1252')         # what Excel on Windows writes

latin-1 deserves a warning: it maps every possible byte to some character, so it never raises. That makes it a useful diagnostic and a dangerous default. If it “works” but your text has é where é should be, the file was really UTF-8.

To find out rather than guess:

with open(path, 'rb') as f:
    print(f.read(200))          # look at the raw bytes

Delimiters and quoting

pd.read_csv(path, sep=';')          # common in Europe, where , is a decimal point
pd.read_csv(path, sep='\t')         # tab separated
pd.read_csv(path, sep=None, engine='python')   # let pandas sniff it

Fields containing the delimiter must be quoted. "Delhi, India",33.8 is two fields, not three, and pandas handles this correctly. What it cannot handle is a file where quoting is inconsistent, which happens when a file was assembled by string concatenation somewhere upstream.

Rows that are not data

Real files have preamble, footers, and blank lines:

pd.read_csv(path,
    skiprows=3,            # a title block above the header
    skipfooter=2,          # a totals row at the bottom (needs engine='python')
    engine='python',
    comment='#',           # ignore lines starting with #
)

If the header is not on the first line, header=3 names the row that holds the column names. If there is no header at all, header=None, names=[...].

Always Look Before You Load

Two minutes here saves hours later.

# What does it actually look like?
with open('orders.csv') as f:
    for _ in range(5):
        print(repr(f.readline()))

repr is doing important work. It shows you the invisible characters: trailing spaces, \r\n line endings, tabs pretending to be spaces. A plain print hides exactly the things that break parsing.

Then read a sample before committing to the whole file:

sample = pd.read_csv('huge.csv', nrows=1000)
sample.dtypes
sample.head()

Work out your dtype and parse_dates arguments on the sample, then apply them to the full read.

The Fifteen-Minute Check

Once loaded, before forming any opinion:

df.shape                                       # how much is there
df.dtypes                                      # what did pandas decide
df.isna().mean().sort_values(ascending=False)  # what is missing
df.describe(include='all').T                   # ranges, impossible values
df.duplicated().sum()                          # exact duplicate rows

for c in df.select_dtypes(['object', 'string']):
    print(f"\n{c}: {df[c].nunique()} unique")
    print(df[c].value_counts().head(10))

That last loop is the one that earns its keep. It is how you find that your country column holds UK, U.K., United Kingdom, england and Uk , which your model will treat as five unrelated categories.

Stop Using CSV After the First Read

CSV has no types. Every read re-guesses, every write throws the types away, and the parsing is slow.

Parquet fixes all three. It is columnar, compressed, and stores the schema.

df.to_parquet('orders.parquet')
df = pd.read_parquet('orders.parquet')     # dtypes preserved exactly

	CSV	Parquet
Types preserved	No	Yes
File size	Baseline	Often 5–10× smaller
Read speed	Baseline	Often 10× faster
Read one column only	No	Yes
Human readable	Yes	No
Opens in Excel	Yes	No

The workflow that follows: read the CSV once with all your arguments correct, save as Parquet, and read Parquet thereafter. Keep CSV for handing data to people, not for your own pipeline.

# Only the columns you need — Parquet does not read the rest from disk
df = pd.read_parquet('orders.parquet', columns=['order_id', 'revenue'])

Excel

You will not escape it.

sheets = pd.read_excel('report.xlsx', sheet_name=None)   # None = all sheets
sheets.keys()

df = pd.read_excel('report.xlsx', sheet_name='Q3', skiprows=2,
                   usecols='B:H', dtype={'code': 'string'})

Excel-specific hazards: merged cells become a value in the first cell and NaN in the rest; numbers stored as text stay text; and dates may arrive as integers counting days from 1900.

Writing multiple sheets:

with pd.ExcelWriter('output.xlsx') as writer:
    summary.to_excel(writer, sheet_name='Summary', index=False)
    detail.to_excel(writer, sheet_name='Detail', index=False)

index=False matters. Without it you get an unnamed first column containing row numbers, which everyone downstream then has to remove.

JSON and Nested Data

import json

with open('response.json') as f:
    data = json.load(f)

df = pd.json_normalize(data)                    # flatten one level
df = pd.json_normalize(data, sep='__')          # nested keys become a__b
df = pd.json_normalize(data, record_path='items', meta=['order_id'])

That last form is the useful one. When each record contains a list of sub-records, record_path explodes them into rows and meta carries the parent fields down.

flowchart LR
    J["{'order_id': 1,<br/>&nbsp;'items': [{'sku':'A'},<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;{'sku':'B'}]}"]
    J -->|"record_path='items'<br/>meta=['order_id']"| T["order_id | sku<br/>1 | A<br/>1 | B"]
    style T fill:#eef1fc,stroke:#2141c8

For newline-delimited JSON, common in log files:

df = pd.read_json('events.jsonl', lines=True)

Files Larger Than Memory

Three approaches, in order of effort.

Read fewer columns.

df = pd.read_csv(path, usecols=['id', 'date', 'revenue'])

Often enough on its own. Most wide tables have a handful of columns you actually need.

Shrink the types.

df = pd.read_csv(path, dtype={'tier': 'category', 'count': 'int32'})

A category column with few distinct values can use a tenth of the memory of the equivalent text.

Process in chunks.

totals = []
for chunk in pd.read_csv(path, chunksize=100_000):
    totals.append(chunk.groupby('region').revenue.sum())

result = pd.concat(totals).groupby(level=0).sum()

This works whenever your operation can be computed piecewise. Sums, counts and group totals can. A median cannot, which is a genuine limitation.

Past that point, stop fighting pandas. DuckDB will run SQL directly against a Parquet file larger than memory, and Polars handles larger-than-memory frames natively. Both are covered in the performance chapter.

Writing Output People Can Use

df.to_csv('out.csv', index=False)                  # almost always index=False
df.to_csv('out.csv', index=False, float_format='%.2f')

Two habits worth adopting. Always index=False unless the index carries meaning, and round your floats. Handing someone a CSV where revenue reads 1234.5678999999998 invites questions you do not want.

What Carries Forward

Identifiers load as text. If you would never add two of them together, set dtype='string'.

Look at the raw bytes before you parse. Five lines with repr reveals encoding and line-ending problems immediately.

Convert to Parquet after the first read. Types preserved, far smaller, far faster.

index=False when writing, and round the floats.

Next: getting data from databases and APIs, where the data is not sitting in a file at all.