Working with Files: CSV, JSON & Excel
with open() pattern and file paths, reading and writing CSV files with Python's built-in csv module, a first look at pandas.read_csv, working with JSON via the json module, and how to open Excel files in Python.
Why File I/O Matters
Before you can analyze data, you have to get it out of whatever file it's sitting in. Three formats cover the overwhelming majority of real-world datasets you'll encounter: CSV (tables, spreadsheet exports), JSON (nested data, API responses), and Excel (business spreadsheets). This lesson covers all three.
The with open() Pattern & File Paths
Python's built-in open() function opens a file. Wrapping it in a with block guarantees the file gets closed automatically — even if an error happens partway through — which is why it's the standard pattern for file work in Python.
# Reading a plain text file with open("notes.txt", "r") as f: content = f.read() print(content) # The file is automatically closed here, even if read() had raised an error # Writing to a text file ("w" overwrites; "a" appends) with open("output.txt", "w") as f: f.write("First line\n") f.write("Second line\n")
"data.csv" is a relative path — Python looks for it in the current working directory (usually wherever you launched your script or notebook from). An absolute path like "C:/Users/you/data.csv" works no matter where the script runs. When a file "can't be found," a wrong relative path is almost always the cause.Reading & Writing CSV with the csv Module
A CSV ("comma-separated values") file is just plain text where each line is a row and commas separate the columns. Python's built-in csv module handles the fiddly parts — like values that contain commas themselves — correctly.
Reading a CSV file
import csv with open("players.csv", "r", newline="") as f: reader = csv.reader(f) header = next(reader) # the first row — column names print("Columns:", header) for row in reader: print(row) # each row is a list of strings
Reading a CSV as dictionaries — DictReader
csv.DictReader uses the header row as keys automatically, which makes each row far easier to work with than a plain list.
import csv with open("players.csv", "r", newline="") as f: reader = csv.DictReader(f) for row in reader: print(f"{row['name']} scored {row['goals']} goals") # Note: every value from DictReader is a string — convert numbers yourself goals = int(row["goals"])
Writing a CSV file
import csv rows = [ {"name": "Amara", "goals": 14}, {"name": "Ben", "goals": 9}, ] with open("output.csv", "w", newline="") as f: writer = csv.DictWriter(f, fieldnames=["name", "goals"]) writer.writeheader() writer.writerows(rows)
csv module, pass newline="" to open(). Without it, Windows can insert extra blank lines between rows because of how it handles line endings.A Preview of pandas.read_csv
The csv module is great for understanding what's happening under the hood, but in practice, most data scientists reach for Pandas to load CSV files — it's a single line, handles type conversion automatically, and returns a table you can immediately explore. You'll learn Pandas properly in Section 2; here's a preview of just how much shorter the same task becomes.
import pandas as pd df = pd.read_csv("players.csv") # one line — loads the whole file into a DataFrame print(df.head()) # preview the first 5 rows print(df["goals"].sum()) # goals column is already numeric — no manual int() conversion df.to_csv("output.csv", index=False) # writing is just as short
csv module is part of the standard library — no installation required — and it's the right choice for streaming through a huge file row by row without loading everything into memory at once. Knowing both gives you options.Working with JSON via the json Module
JSON (JavaScript Object Notation) represents nested data using objects (like Python dicts) and arrays (like Python lists). It's the standard format for web APIs, configuration files, and any data that isn't naturally a flat table.
Reading a JSON file
import json with open("team.json", "r") as f: data = json.load(f) # parses the file into Python dicts/lists print(data["team_name"]) for player in data["players"]: print(f"{player['name']}: {player['goals']} goals")
Given a file like this, data becomes an ordinary nested Python dict — exactly the structure you reviewed in Lesson 3:
{
"team_name": "Falcons",
"players": [
{"name": "Amara", "goals": 14},
{"name": "Ben", "goals": 9}
]
}
Writing a JSON file
import json data = {"team_name": "Falcons", "players": [{"name": "Amara", "goals": 14}]} with open("output.json", "w") as f: json.dump(data, f, indent=2) # indent=2 makes the file human-readable # json.dumps() (with an "s") converts to a string instead of writing to a file — # useful for printing or sending data over a network text = json.dumps(data) print(text)
json.load(f) and json.dump(data, f) work directly with an open file. json.loads(text) and json.dumps(data) — with an "s" — work with a plain Python string instead. Mixing them up is a very common beginner error.A Brief Look at Excel Files
Excel files (.xlsx) are common in business settings — sales reports, exported dashboards, shared spreadsheets. Python doesn't read them with the standard library; you need a third-party package. The two most common are openpyxl (works directly with Excel files) and Pandas (which uses openpyxl under the hood for a much simpler interface).
# Requires: pip install openpyxl (pandas uses it behind the scenes for .xlsx) import pandas as pd df = pd.read_excel("sales_report.xlsx", sheet_name="Q1") print(df.head()) # Writing an Excel file is just as short df.to_excel("summary.xlsx", index=False)
pd.read_excel() raises an import error about a missing engine, run pip install openpyxl in your environment and try again. Unlike CSV and JSON, Excel support isn't built into Python or into a bare Pandas install.Choosing the Right Tool
pd.read_csv() the fastest way in.json.load()..xlsx file with multiple sheets, pd.read_excel() is the most direct route in.csv.reader avoids loading the whole thing at once.Lesson Summary
Let's recap everything you learned in this lesson:
with open(...) as f: so they close automatically, even on error.csv.reader and csv.DictReader read CSV rows as lists or dicts; csv.DictWriter writes them back out.pd.read_csv() loads a whole CSV into a Pandas DataFrame in one line, with automatic type conversion.json.load()/json.dump() work with files; json.loads()/json.dumps() work with strings.pd.read_excel() reads Excel files, using openpyxl as its engine (pip install openpyxl if missing).with open("file.txt") as f: instead of calling open() directly?csv.DictReader use as the keys for each row's dictionary?json.load() and json.loads()?pd.read_excel() on an .xlsx file?Practice moving data between formats — a task you'll do constantly in real projects.
Write a script called
convert.py that: (1) creates a small CSV file called students.csv with columns name and score for 3 students using csv.DictWriter, (2) reads that CSV back with csv.DictReader, converting each score to an int, and (3) writes the resulting list of dicts out to students.json using json.dump() with indent=2.
Rules: Use
with open() for every file operation. Print the final list of dicts before writing the JSON file, so you can confirm the scores are real integers, not strings.
💡 Show hints if you're stuck
- Write the CSV first with
csv.DictWriter(f, fieldnames=["name","score"]) - When reading back, build a new list:
[{"name": row["name"], "score": int(row["score"])} for row in reader] - Write with
json.dump(students, f, indent=2)