pandas · Chapter 4 of 22
Reading real files
By the end of this chapter you can read files that are not tidy CSVs: tell read_csv about separators, title lines, missing headers, thousands commas and its own words for "no value"; read only some columns or rows; read a big file in pieces; read an Excel workbook sheet by sheet; flatten nested JSON with json_normalize; and write a table back out with to_csv, to_parquet and the other writers. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Tables from zero
- 5 tasks
- pandas
- In the all labs plan
The first 2 chapters of pandas are free. This one comes with the all labs plan.
Why you need this
Kargha's three channels each send their orders their own way. The marketplace sends an Excel workbook whose first sheet is a summary and whose orders sheet starts with two banner lines. The Varanasi store sends JSON, one record per bill, with the bill's items nested inside it. The courier and the bank send text files with their own separators and title lines. Harpreet opens each one by hand every month and copies it into her spreadsheet.
Chapter 1's pd.read_csv(path) assumes a tidy file: commas, the column names on the first line, every value where it should be. Give it the marketplace's workbook and it cannot read it at all; give it the bank's statement and it guesses wrong without a word. Here is a payout statement from Kargha's bank, written to a file so you can try it:
import pandas as pd
statement = """Kargha current account, weaver payouts, September 2026
W01;05-09-2026;1,24,500;UTR99120001
W07;05-09-2026;-;
W12;05-09-2026;38,250;UTR99120003
"""
with open("payouts.csv", "w", encoding="utf-8") as f:
f.write(statement)
print(pd.read_csv("payouts.csv").head(3))
Kargha current account weaver payouts September 2026
0 W01;05-09-2026;1 24 500;UTR99120001
1 W07;05-09-2026;-; NaN NaN
2 W12;05-09-2026;38 250;UTR99120003 NaN
The title became the column names, the semicolons were not separators, and ₹1,24,500 was cut in two at its commas. No error. A table like this looks like data and is nonsense. This chapter is about telling pandas what the file really is, and looking before trusting it.
What you build: 5 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
courier.py: the courier's invoice
Write read_invoice(path) in courier.py. path is a text file from the courier: two title lines, then a line of column names, awb|booked_on|city|weight_g|charge_rs, then one line for each parcel with | between the values, like AWB66120031|2026-09-01|Pune|1,250|95. Weights and charges over 999 are written with a comma (1,250), and a parcel that was not weighed has the words not weighed where its weight should be.
Return a DataFrame with the columns awb, booked_on, city, weight_g and charge_rs, in that order, one row for each parcel in the file's order, labelled 0, 1, 2 and so on. awb, booked_on and city are text, weight_g is decimal numbers (a missing value where the parcel was not weighed) and charge_rs is whole numbers.
The Check writes two invoices of its own, of 6 parcels (two not weighed) and of 3 parcels (a charge of 1,050), and reads each.
- Task 2
marketplace.py: the marketplace's orders sheet
Write read_orders(path) in marketplace.py. path is an Excel workbook like /workspace/data/orders_marketplace.xlsx, with two sheets, Summary first and then Orders. The Orders sheet has two banner lines, then the header, then one row for each order.
Return a DataFrame of the Orders sheet with the columns Order Id, Order Date, SKU, Qty, Selling Price, Ship City, Status and Commission, in that order and with exactly those names, one row for each order in the sheet's order, labelled 0, 1, 2 and so on: 6042 rows for orders_marketplace.xlsx. Qty is whole numbers and Commission decimal numbers; Order Date and Selling Price stay text, like 01-10-2025 and ₹1,299.00 (chapters 10 and 18 turn them into numbers and dates).
The Check reads /workspace/data/orders_marketplace.xlsx and a small workbook of its own made the same way, with 3 orders.
- Task 3
store_items.py: the store's bills, one row per item
Write store_items(path) in store_items.py. path is a JSON file like /workspace/data/orders_store.json: a list of bills, each an object with bill, time, staff and payment, and items, a list of objects with sku, qty and price.
Return a DataFrame with one row for each item of each bill, in the file's order, labelled 0, 1, 2 and so on, with the columns sku, qty, price, bill, time, staff and payment, in that order: each item's own fields, then its bill's. For orders_store.json that is 3749 rows.
The Check reads /workspace/data/orders_store.json and a small file of its own of 3 bills: one with 2 items, one with no items (it gives no rows) and one with 1 item.
- Task 4
count_status.py: a big file, piece by piece
Write count_status(path, status, chunksize) in count_status.py. path is a CSV file of website order lines like /workspace/data/orders_site.csv, with a status column. Read it in pieces of chunksize rows, never all at once: the Check looks for read_csv's argument that does it.
Return how many lines of the file have that status (delivered, returned, cancelled or shipped), as one whole number (an int), and 0 when no line has it.
The Check tries orders_site.csv in pieces of 5000 and 4000 for three statuses, a file of its own of 7 lines in pieces of 3 (so the last piece has 1 line), and a file of 1 line in pieces of 10.
- Task 5 · interview question
Interview: round_trip.py, the extra Unnamed: 0
Harpreet saves a table to CSV at night and reads it back in the morning. Her round_trip(table, path) should give back the same table, but it comes back with an extra first column called Unnamed: 0, holding 0, 1, 2 and so on. Fix it so it writes table to a CSV file at path whose first line is the column names, separated by commas, and nothing else, and returns the table read back from that file: the same columns in the same order, the same values, labelled 0, 1, 2 and so on. round_trip(weavers, path) is a DataFrame of 39 rows and the 5 columns of weavers.csv.
The Check gives tables labelled 0, 1, 2 (the catalogue, the weavers and a small table of 2 rows) and a path to write, and reads the file's first line too.
Devika will ask: where did Unnamed: 0 come from, and how do you keep it out?
What you will learn
- read_csv's arguments: sep, skiprows, header, names, thousands, na_values, usecols, nrows, dtype, index_col, parse_dates.
- chunksize: a big file as an iterator of smaller DataFrames.
- read_excel: sheet_name, the first sheet trap, banner lines, every sheet at once.
- read_json and pd.json_normalize: a list of records, and nested records flattened to one row each.
- Writing: to_csv, to_excel, to_json and to_parquet.
More in Tables from zero
Build "Reading real files" yourself
Kargha, a Varanasi handloom brand, sells through a website, a marketplace and a store. You make one true picture of its sales. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.
The first 2 chapters of pandas are free. This one comes with the all labs plan.