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
Download the complete course kit: datasets, all examples, solutions, and capstone.
Return to the full Data Analysis with Python and pandas curriculum.
