Excel, JSON and Parquet
The finance team sends a spreadsheet with one sheet per month. The website's order system hands you JSON. The data team stores last year's history as Parquet. Three formats, one habit: each has a pd.read_… function, and each has one thing you must tell it.
The rule
Every format has a
read_and ato_; learn the one question each asks.
Excel (.xlsx): the question is which sheet?
march = pd.read_excel("sales_2024.xlsx", sheet_name="March")
every = pd.read_excel("sales_2024.xlsx", sheet_name=None) # dict of all sheetsWith no sheet_name, you get only the first sheet. With None, you get a dictionary: sheet name to DataFrame. Reading Excel needs an extra package, usually openpyxl.
JSON: the question is what shape is it? Flat records map straight onto rows:
# [{"branch": "Pune", "copies": 3}, {"branch": "Delhi", "copies": 5}]
orders = pd.read_json("orders.json")Nested JSON, where a value is itself an object, needs flattening:
records = [{"id": 1, "shop": {"city": "Pune", "floor": 2}}]
pd.json_normalize(records)
# columns: id, shop.city, shop.floorjson_normalize turns each nested key into a column joined with a dot.
Parquet: the question is which columns do I need? Parquet is a compressed, column-by-column binary format built for analysis. It keeps each column's dtype, so dates stay dates and integers stay integers with no guessing.
history = pd.read_parquet("sales_2023.parquet", columns=["branch", "copies"])
sales.to_parquet("sales.parquet")Because it is stored column by column, loading two columns out of fifty reads only those two, which is much faster than CSV.
The shortcut
Match the file to its one question: Excel → sheet, JSON → shape, Parquet → columns. Then run the usual check: shape, head(), dtypes.
Name the question
It is Excel, so: which sheet? The workbook has sheets January, February, March.
The trap is reading only the first sheet without noticing. The code runs, the numbers look plausible, and eleven months are missing. Check the sheet names before trusting a total.
Drills
Answer fast — trust the rule.
What does pd.read_excel("book.xlsx") load when the file has four sheets?
What columns does pd.json_normalize([{"id": 7, "shop": {"city": "Delhi"}}]) produce?
Which is a real advantage of Parquet over CSV for analysis?
What type does pd.read_excel("book.xlsx", sheet_name=None) return?
Remember
- Every format has a
read_and ato_; learn the one question each asks. - Excel → which sheet; JSON → what shape; Parquet → which columns.
- Default Excel reads the first sheet only: check sheet names before trusting totals.