Python Foundation for Data Engineers

Chapter 16 — Reading & Writing Real File Formats

Lesson 16.1 — CSV & Excel with Pandas: read_csv and read_excel

You've been building DataFrames by hand all chapter, straight from Python dictionaries and lists. Real work almost never starts that way — it starts with a file, sitting somewhere, that someone else created. This chapter is about getting real files in and out of pandas properly, starting with the two most common ones you'll meet in a typical business setting.

You've already read CSV files with Python's built-in csv module, back in Week 1, Chapter 8. Pandas makes it dramatically shorter:

python
import pandas as pd

orders = pd.DataFrame({
    "order_id": [10432, 10433, 10434],
    "customer": ["Priya Shah", "Raj Kumar", "Amit Verma"],
    "total": [149.50, 89.99, 24.00],
})
orders.to_csv("orders.csv", index=False)

That index=False is worth noting deliberately — without it, pandas would save the row labels, 0, 1, 2, as their own extra column in the file, which is almost never what you actually want. Now, reading it back:

python
df = pd.read_csv("orders.csv")
print(df)
print(df.dtypes)
Output / Note

order_id customer total 0 10432 Priya Shah 149.50 1 10433 Raj Kumar 89.99 2 10434 Amit Verma 24.00 order_id int64 customer object total float64

One line, pd.read_csv(), and you've got a fully loaded DataFrame with types already sensibly guessed — compare that to Week 1, Chapter 8, where you had to loop through rows and convert types by hand. Pandas did the row count, the delimiter handling, and a first pass at type detection, all in that single line.

Excel files work almost identically, though they need one extra library installed first:

bash
pip install openpyxl
python
orders.to_excel("orders.xlsx", index=False, sheet_name="Orders")

df_excel = pd.read_excel("orders.xlsx", sheet_name="Orders")
print(df_excel)
Output / Note

order_id customer total 0 10432 Priya Shah 149.50 1 10433 Raj Kumar 89.99 2 10434 Amit Verma 24.00

Notice sheet_name — Excel files can hold multiple sheets, so you tell pandas which one you actually want. If you leave it out, pandas defaults to the first sheet, which is fine sometimes and a quiet source of confusion other times, when a file has more than one sheet and you didn't realize it.

A genuinely important habit, worth building now: real CSV and Excel files are messier than the ones you build by hand. Always run .shape, .dtypes, and .isna().sum() — every tool from this chapter and the last — on any file the moment you load it, before trusting a single thing about it.

Try It Yourself: Save the orders DataFrame from this lesson to a CSV file called test_orders.csv, without the index column. Read it back in, and confirm its .shape matches the original.

Lesson 16.2 — JSON with Pandas: read_json and Nested Data

You met JSON properly back in Week 1, Chapter 8 — the format that preserves real types, and handles nested data naturally. Pandas can read and write it directly, though nested JSON needs a little more care than flat CSV data does.

Flat JSON works almost exactly like CSV:

python
import pandas as pd

orders = pd.DataFrame({
    "order_id": [10432, 10433],
    "customer": ["Priya Shah", "Raj Kumar"],
    "total": [149.50, 89.99],
})
orders.to_json("orders.json", orient="records", indent=2)

df = pd.read_json("orders.json")
print(df)
Output / Note

order_id customer total 0 10432 Priya Shah 149.50 1 10433 Raj Kumar 89.99

That orient="records" argument tells pandas to save it as a list of individual record objects — genuinely the most common, most widely compatible JSON shape, and the one you'll want by default.

Real JSON, especially from APIs — which you'll work with directly starting next week — is often nested, with a field containing its own sub-fields, not just plain values. Here's where a direct pd.read_json() starts to struggle, and a different tool takes over: pd.json_normalize().

python
import pandas as pd

raw_data = [
    {
        "order_id": 10432,
        "customer": {"name": "Priya Shah", "is_priority": True},
        "total": 149.50,
    },
    {
        "order_id": 10433,
        "customer": {"name": "Raj Kumar", "is_priority": False},
        "total": 89.99,
    },
]

df = pd.json_normalize(raw_data)
print(df)
Output / Note

order_id total customer.name customer.is_priority 0 10432 149.50 Priya Shah True 1 10433 89.99 Raj Kumar False

Notice what happened to the nested customer field — pd.json_normalize() automatically flattened it into two separate, properly named columns, customer.name and customer.is_priority, using a dot to show where each one came from. This is genuinely one of the most useful tools you'll reach for once you start pulling real API responses into pandas, because APIs almost always nest related data this way, and a flat table is what you actually need to filter, group, and analyze it with everything else you've learned this week.

Try It Yourself: Build a small list of dictionaries representing pipeline runs, where each one has a nested stats field containing rows_loaded and errors. Use pd.json_normalize() to flatten it into a proper DataFrame, and print the resulting column names.

Lesson 16.3 — Parquet: The Format Real Pipelines Use, and Why

CSV and Excel are what you'll meet most often from business users and manual exports. But once you're inside a real, working data pipeline — reading and writing data between automated steps — the format changes, and for good reason. Let's meet Parquet.

First, install the library pandas needs to work with it:

bash
pip install pyarrow
python
import pandas as pd

orders = pd.DataFrame({
    "order_id": range(1, 10001),
    "customer": ["Customer " + str(i) for i in range(1, 10001)],
    "total": [round(20 + i * 0.37, 2) for i in range(1, 10001)],
})

orders.to_csv("orders_large.csv", index=False)
orders.to_parquet("orders_large.parquet", index=False)

We just saved the same 10,000-row dataset two different ways. Let's compare them honestly:

python
import os

csv_size = os.path.getsize("orders_large.csv")
parquet_size = os.path.getsize("orders_large.parquet")

print(f"CSV size: {csv_size:,} bytes")
print(f"Parquet size: {parquet_size:,} bytes")
Output / Note

CSV size: 254,893 bytes Parquet size: 87,412 bytes

Your exact numbers will vary, but the pattern holds consistently: Parquet files are typically dramatically smaller than the equivalent CSV — often a third to a fifth of the size, sometimes considerably more on larger, more repetitive datasets. That's because Parquet is a columnar format — it stores all the values of one column together, tightly packed by type, instead of storing each row as a flat line of comma-separated text the way CSV does. Similar values sitting next to each other compress dramatically better.

There's a second, arguably bigger advantage, one that matters even more in real pipeline work: Parquet remembers types.

python
df_csv = pd.read_csv("orders_large.csv")
df_parquet = pd.read_parquet("orders_large.parquet")

print(df_csv.dtypes)
print(df_parquet.dtypes)

Reading from CSV, pandas has to guess every column's type, fresh, every single time the file is loaded — exactly the same guessing game from Chapter 14, where a genuinely numeric-looking column could still hide unexpected text. Reading from Parquet, the types are stored directly in the file itself — no guessing, no surprises, no silent misinterpretation on the next read.

For genuinely large datasets — many millions of rows, more than comfortably fits in memory — you'll eventually meet Polars, a newer, Rust-built alternative to pandas that uses a very similar DataFrame style but runs considerably faster on big Parquet files. It's worth knowing the name exists, and that it's gaining real adoption in the field — but it's outside the scope of this course, since pandas remains the standard starting point, and everything you're learning here transfers directly once you're ready to explore it.

The practical takeaway for this lesson: once data is moving between stages of a real pipeline — rather than being handed to a human to open — reach for Parquet over CSV. You'll get a smaller file, a faster read, and types you can actually trust.

Try It Yourself: Take the sales_clean DataFrame you built in Chapter 14's hands-on exercise (or recreate a small version of it), and save it as both a CSV and a Parquet file. Read the Parquet version back in, and confirm the order_date column came back as a real datetime64 type, without you having to call pd.to_datetime() again.

Lesson 16.4 — Hands-On: Convert a CSV to Parquet and Compare File Size

Let's put this whole chapter to work on a task that's genuinely common in real pipeline work: taking a file as it arrives — usually CSV — and converting it into the format the rest of the pipeline actually wants to work with.

First, let's create a moderately messy CSV, close to what a real daily export might look like:

python
import pandas as pd
import numpy as np

np.random.seed(42)
row_count = 50_000

daily_export = pd.DataFrame({
    "order_id": range(1, row_count + 1),
    "customer": [f"Customer {i}" for i in range(1, row_count + 1)],
    "total": np.round(np.random.uniform(10, 500, row_count), 2),
    "order_date": pd.date_range("2026-01-01", periods=row_count, freq="min").astype(str),
})

daily_export.to_csv("daily_export.csv", index=False)

Your task:

  1. Read the CSV in.
  2. Convert order_date to a proper datetime column, exactly as you learned in Chapter 14.
  3. Save the result as Parquet.
  4. Compare the file sizes of the original CSV and the new Parquet file.
  5. Confirm the Parquet version correctly preserves the datetime type on reload.

Here's the shape to build from:

python
import pandas as pd
import os

df = pd.read_csv("daily_export.csv")
df["order_date"] = pd.to_datetime(df["order_date"])

df.to_parquet("daily_export.parquet", index=False)

csv_size = os.path.getsize("daily_export.csv")
parquet_size = os.path.getsize("daily_export.parquet")
savings = (1 - parquet_size / csv_size) * 100

print(f"CSV size: {csv_size:,} bytes")
print(f"Parquet size: {parquet_size:,} bytes")
print(f"Space saved: {savings:.1f}%")

reloaded = pd.read_parquet("daily_export.parquet")
print(reloaded.dtypes)
Output / Note

CSV size: 2,847,213 bytes Parquet size: 612,847 bytes Space saved: 78.5% order_id int64 customer object total float64 order_date datetime64[ns]

Your exact numbers will differ slightly, but expect genuinely dramatic savings, and notice order_date reloaded as a proper datetime64 automatically — no pd.to_datetime() needed on the way back in, unlike the CSV version, which would hand you back plain text every single time.

This is a small, complete, realistic version of a step that happens in real pipelines constantly: a file lands as CSV because that's what a source system exports, gets cleaned and properly typed once, and is saved as Parquet for every downstream step from there on — smaller, faster, and honest about its own types, exactly the qualities this whole chapter has been building toward.

Try It Yourself: Repeat this comparison with row_count = 500_000 instead of 50,000. Watch how the percentage space savings changes — or doesn't — as the file gets larger, and note how long each format takes to read back in, using the time module from Week 2, Chapter 11.