Reading CSV and JSON data

Intermediate

Introduction

Most data reaches you as files: spreadsheets exported as CSV, records from web services in JSON, logs from servers. Before any analysis, you have to read the file into Python values. Both formats look simple, and both have traps that catch programs written by hand. Python's standard library has a module for each that handles the traps for you.

How it works

A CSV file has one row per line, with fields separated by commas, and usually a header row with the column names:

name,age,city
"Lee, Jr.",29,Chattogram

Splitting each line with split(",") breaks as soon as a field contains a comma: "Lee, Jr." would become two fields. CSV handles this with quoting: a field in double quotes may contain commas, and a double quote inside it is written twice. The csv module understands these rules. csv.reader(lines) gives each row as a list of strings, and csv.DictReader(file) gives each row as a dictionary keyed by the header, which makes code easier to read: row["age"] rather than row[1].

Whatever the reader, every value is text. The program must convert columns: int(row["age"]), float(row["price"]), row["active"] == "true", and decide what an empty field means, usually a missing value, None.

To read a real file you write open("people.csv", newline="", encoding="utf-8"); in this course's examples, io.StringIO(text) turns a string into something that behaves like an open file, so the examples need no files.

JSON holds nested data: objects (which become Python dictionaries), arrays (lists), strings, numbers, true and false (True and False) and null (None). json.loads(text) parses a JSON string into Python values, and json.dumps(value) turns Python values back into JSON text. Unlike CSV, JSON keeps numbers as numbers and can hold lists inside records, which is why web services use it.

In pandas, pd.read_csv("people.csv") and pd.read_json(...) do the reading and convert the types automatically; it is still worth checking that they guessed correctly.

Example

# Reading data: CSV and JSON with Python's standard library.
import csv
import io
import json

text = """name,age,city
Asha,34,Dhaka
"Lee, Jr.",29,Chattogram
Mina,41,Sylhet
"""
# A real program would use open("people.csv"); io.StringIO makes a file from text.
rows = list(csv.DictReader(io.StringIO(text)))
for row in rows:
    row["age"] = int(row["age"])  # every CSV value starts as text
print(rows[1])
print("average age:", round(sum(row["age"] for row in rows) / len(rows), 1))

record = json.loads('{"name": "Asha", "skills": ["python", "sql"], "active": true}')
print(record["skills"][1], record["active"])
print(json.dumps(rows[0]))
Open in compiler

Line by line

  1. text holds a small CSV file; the second person's name contains a comma, so it is quoted.
  2. csv.DictReader reads it into dictionaries; the quoted name stays one field.
  3. The loop converts each age from text to an integer, so the average can be computed.
  4. json.loads parses a record with a list and a boolean; json.dumps writes the first row back out as JSON.

Expected result

{'name': 'Lee, Jr.', 'age': 29, 'city': 'Chattogram'}
average age: 34.7
sql True
{"name": "Asha", "age": 34, "city": "Dhaka"}

In the real world

Government open-data portals, banks and spreadsheets export CSV; web APIs, configuration files and many databases speak JSON. Data pipelines spend a large share of their code on reading formats correctly: different separators (semicolons in many European countries), different encodings, dates in different styles. Getting this step right prevents silent errors that appear much later.

Common mistakes

  • Splitting CSV lines on commas by hand, which breaks on quoted fields.
  • Forgetting to convert types, so numbers are compared and added as text.
  • Treating an empty field as zero instead of as a missing value.
  • Opening CSV files without newline="", which can produce blank rows on Windows.

Best practices

  • Use csv.DictReader and refer to columns by name.
  • Convert each column in one place, right after reading, and report values that do not convert.
  • Check the number of rows read against what you expect.

Summary

CSV and JSON are the commonest data formats. Read CSV with csv.reader or csv.DictReader, which handle quoted fields, and convert every value from text yourself, treating empty fields as missing. Read and write JSON with json.loads and json.dumps, which map objects, arrays and literals to dictionaries, lists and Python values.

Quick check

1. Why is line.split(',') unreliable for CSV?
2. What type is every value read by csv.DictReader?
3. What does JSON null become in Python?
4. What does json.dumps do?

Key takeaways

  • Use the csv module, not split(","), so quoted fields with commas are read correctly.
  • Every CSV value is text: convert numbers, booleans and empty fields yourself.
  • json.loads turns JSON text into dictionaries and lists, and json.dumps does the reverse.

Practice problems for this lesson

My notes

Write down what you want to remember from this lesson. Notes are saved on this device as you type, and appear on your dashboard.