Read CSV and JSON Without Losing Identifiers

Module 2: Reading and Understanding Tables · Lecture 5 of 32

Ingestion decisions shape every later result. Read identifiers as text, preserve the original export, and test a round trip so that an innocent save does not create an extra index column or change missing-value semantics.

What you will learn

Control CSV types and missing-value interpretation.
Read JSON records and distinguish text IDs from numbers.
Verify a file round trip and explain when Parquet is useful.

Understand the problem

CSV has values but little schema

A CSV header supplies column names, not reliable types, units, timezone rules, or key constraints. A customer ID such as 0007 must remain text even though it contains digits. Otherwise inference can strip leading zeros and break joins. Read raw source fields as strings, then parse and validate them explicitly in a separate cleaning step.

Know your missing-value vocabulary

read_csv recognizes several default missing markers. A legitimate code such as NA can therefore be mistaken for missing if you do not configure the parser. Decide whether an empty field, a literal NULL, and a business label such as Not applicable mean the same thing. Use keep_default_na and na_values when the source contract requires more control. Do not globally replace every unfamiliar token with zero.

JSON and columnar formats solve different problems

A JSON records array can represent nested objects and null values, but nesting often requires normalization before tabular analysis. Use StringIO when passing JSON text to read_json. Parquet stores column-oriented typed data and often compresses analytic tables effectively; pandas needs an appropriate engine such as pyarrow to write it. The core kit intentionally requires only CSV and JSON support so learners can run every lecture without that optional dependency.

Make ingestion inspectable

Keep the unmodified raw export and record its hash. Write transformed outputs under a new path with index=False unless the index is deliberately part of the interchange schema. Check row count, column names, and key fields after reloading. An output file existing is not evidence that its content survived the round trip.

Run the worked example

Download and extract the course kit linked below. Open a terminal in the extracted folder, complete the setup in Lecture 2, and run python run_course.py --lesson 5 --examples-only. Each script starts from the shared dataset; it does not depend on a notebook cell from another lecture. All retail records are fictional and amounts are integer paise unless a chart explicitly says INR.

from pathlib import Path
import numpy as np
import pandas as pd
from course_data import load_orders, load_raw, load_customers, clean_orders, ROOT, OUT

from io import StringIO
sample = chr(10).join(['customer_id,region', '0007,South', '0008,NA'])
typed = pd.read_csv(StringIO(sample), dtype='string', keep_default_na=False)
assert typed.loc[0, 'customer_id'] == '0007'
assert typed.loc[1, 'region'] == 'NA'
records = '[{"customer_id":"0007","units":2},{"customer_id":"0008","units":null}]'
json_table = pd.read_json(StringIO(records), dtype={'customer_id': 'string', 'units': 'Int64'})
target = OUT / 'ingestion-roundtrip.csv'
typed.to_csv(target, index=False)
restored = pd.read_csv(target, dtype='string', keep_default_na=False)
pd.testing.assert_frame_equal(typed, restored)
print(restored.to_string(index=False))
print('Missing JSON unit counts:', int(json_table['units'].isna().sum()))

Verified output

customer_id region
       0007  South
       0008     NA
Missing JSON unit counts: 1

Walk through the result

The ID 0007 remains a four-character string across the CSV round trip. The literal region code NA survives because default missing-marker inference is disabled for this particular example. That policy is not used blindly for the whole retail export, where empty customer IDs genuinely represent unknown identities. JSON null becomes a missing nullable integer. The frame comparison checks structure and values rather than simply looking at the first displayed row.

Common mistakes and diagnosis

Do not use a float for an identifier, even when Excel exported it that way. Check encoding explicitly if names become garbled; utf-8 and utf-8-sig are common but not interchangeable for every source. A parser error may indicate a quoted comma or newline rather than a bad row that should be discarded. Avoid on_bad_lines='skip' until you have an audit trail for rejected records.

Practice assignment

Read the raw orders CSV as strings, save only order_id and customer_id to a new file, and reload it. Verify that the number of rows is unchanged and that no Unnamed: 0 column appears. Why is this not yet the clean orders table?

Worked solution and explanation

Add the following code below the worked example, or run the matching file in the solutions folder. Compare the result with the business definition before trusting a passing assertion.

raw = load_raw()
keys = raw[['order_id', 'customer_id']]
path = OUT / 'raw-order-keys.csv'
keys.to_csv(path, index=False)
again = pd.read_csv(path, dtype='string')
assert len(again) == len(raw)
assert list(again.columns) == ['order_id', 'customer_id']
pd.testing.assert_frame_equal(keys, again)

Check your understanding

What does index=False protect against, and what does it not protect against?

Answer guide

It prevents pandas from adding the row index as an unintended CSV column. It does not validate the grain, remove duplicates, guarantee types on reload, preserve every missing-value convention, or prove a source is complete. A file round trip still needs explicit parser settings and comparisons of the fields that matter.

Reference and next step

pandas input and output tools

Download the complete course kit: datasets, all examples, solutions, and capstone.

Return to the full Data Analysis with Python and pandas curriculum.

Course index · Previous · Next