Data Analysis with Python (pandas, polars, DuckDB) Module 3 – Loading data: CSV, Excel, JSON, Parquet and databases
Parquet, Arrow and choosing a file format
Why Parquet beats CSV for analysis, measured on 300,000 rows: write and read it in pandas, polars and DuckDB, use Arrow, and pick a format.
What you will learn
- Explain row-oriented versus columnar storage and why Parquet is smaller and faster to scan
- Read and write Parquet with pandas, polars and DuckDB and pick a compression codec
- Use Apache Arrow to move tables between pandas, polars and DuckDB
- Choose CSV, Excel, JSON or Parquet for a given hand-off
Before you start
What you’ll learn · Before you start
What you will learn
- Explain row-oriented versus columnar storage and why Parquet is smaller and faster to scan
- Read and write Parquet with pandas, polars and DuckDB and pick a compression codec
- Use Apache Arrow to move tables between pandas, polars and DuckDB
- Choose CSV, Excel, JSON or Parquet for a given hand-off
Before you start
On this page
Parquet is a file format that stores a table column by column instead of row by row. That one decision explains why it is the usual choice for analysis: each column is compressed on its own and compresses well, a reader can load only the columns a question needs, the file records every column’s type so dates stay dates, and statistics in the file let readers skip whole blocks of rows. CSV is still the right file for people and spreadsheets, and Excel for people who will edit the numbers.
Apache Arrow is the matching format in memory: a standard columnar layout that pandas, polars and DuckDB all understand, so a table can move between them without being rewritten as text. This lesson measures what the choice means on one table of 300,000 orders, written to every format and read back by three libraries.
Rows or columns
A CSV file stores whole rows, one after another. To add up the prices, a reader still has to read every byte of every line, split it into fields and convert the text to numbers. A Parquet file cuts the rows into row groups and stores each column of a row group together, as a compressed column chunk. Its footer holds the schema (the names and types of the columns) and the minimum and maximum value of every chunk.
Row-oriented CSV and columnar Parquet
Text description of the diagram
The upper box is a CSV file: each line holds one whole order, with its id, city, channel, time, quantity and price, and the lines follow one another. To get only the prices, a reader still has to read and split every line.
The lower box is a Parquet file holding the same data. The rows are cut into row groups (rows 1 to 50,000 in row group 0, and so on). Inside a row group, each column is stored together as a column chunk: all the order ids, then all the cities, then all the times, each chunk compressed on its own. The file ends with a footer that records the schema (column names and types) and the minimum and maximum of each column chunk.
Because of that layout, a reader can load only the columns it needs, skip a row group whose minimum and maximum show it cannot match a filter, and restore every column's type from the footer.
One table, seven files
Every example on this page uses the same seeded table of 300,000 orders, with eight columns: integers, a category, text, timestamps, decimal prices, nullable integers and booleans. The helper that makes it:
"""A seeded table of 300,000 orders with eight columns of different types, imported by the examples."""
import numpy as np
import pandas as pd
CITIES = np.array(["Lagos", "Leeds", "Lima", "Osaka", "Perth", "Pune", "Quito", "Riga"])
def make_orders(n=300_000, seed=11):
rng = np.random.default_rng(seed)
return pd.DataFrame({
"order_id": np.arange(1, n + 1),
"city": pd.Categorical(CITIES[rng.integers(0, len(CITIES), n)]),
"channel": np.array(["app", "web", "shop"])[rng.integers(0, 3, n)],
"placed_at": pd.Timestamp("2026-01-01") + pd.to_timedelta(rng.integers(0, 86_400 * 181, n), unit="s"),
"qty": rng.integers(1, 6, n),
"price": rng.integers(500, 50_000, n) / 100,
"coupon": pd.array(np.where(rng.random(n) < 0.8, pd.NA, rng.integers(1, 99, n)), dtype="Int64"),
"is_gift": rng.random(n) < 0.1,
})Written once as CSV, as Parquet with four compression settings, as Feather and as JSON Lines:
import os
from make_orders import make_orders
orders = make_orders()
orders.to_csv("orders.csv", index=False)
for codec in ["snappy", "zstd", "gzip", None]:
orders.to_parquet(f"orders_{codec or 'none'}.parquet", compression=codec)
orders.to_feather("orders.feather")
orders.to_json("orders.jsonl", orient="records", lines=True, date_format="iso")
csv_mb = os.path.getsize("orders.csv") / 1e6
print(f"{'file':<22}{'MB':>7}{'of CSV':>9}")
for name in ["orders.csv", "orders_none.parquet", "orders_snappy.parquet", "orders_zstd.parquet", "orders_gzip.parquet", "orders.feather", "orders.jsonl"]:
mb = os.path.getsize(name) / 1e6
print(f"{name:<22}{mb:>7.2f}{mb / csv_mb:>9.0%}")Output
file MB of CSV orders.csv 15.77 100% orders_none.parquet 6.78 43% orders_snappy.parquet 5.23 33% orders_zstd.parquet 3.93 25% orders_gzip.parquet 3.88 25% orders.feather 7.95 50% orders.jsonl 42.23 268%
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 file_sizes.py
Even without compression, Parquet needs 43 % of the CSV’s size, because numbers are stored as binary values rather than text and repeated values such as city names are dictionary-encoded: stored once per chunk and referred to by small codes. Snappy, the default codec, brings it to a third, and zstd or gzip to a quarter. Feather, Arrow’s own file format, is fast to write and read but larger, because it stays close to the layout in memory. JSON Lines repeats every field name in every record and is the largest of all.
How much the codec costs depends on the codec. This run timed writing and reading 50,000 of the orders with each:
import time
import pandas as pd
from make_orders import make_orders
orders = make_orders(n=50_000)
def best_of_three(work):
best = float("inf")
for _ in range(3):
start = time.perf_counter()
work()
best = min(best, time.perf_counter() - start)
return best * 1000
print(f"{'codec':<8}{'write ms':>10}{'read ms':>10}")
for codec in ["snappy", "zstd", "gzip"]:
path = f"orders_{codec}.parquet"
write = best_of_three(lambda: orders.to_parquet(path, compression=codec))
read = best_of_three(lambda: pd.read_parquet(path))
print(f"{codec:<8}{write:>10.1f}{read:>10.1f}")Output
codec write ms read ms snappy 52.6 4.2 zstd 22.2 4.8 gzip 1119.2 8.6
This output changes from run to run: the times differ on every run and on every computer
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 codec_times.py
Snappy and zstd were both quick, while gzip took far longer to write for almost no saving over zstd. A sensible default: snappy when you are unsure, zstd for files you keep or send, gzip only for a reader that needs it.
Types survive the trip
Text has no types. When pandas reads a CSV it has to guess them again, and some guesses cannot be right:
import pandas as pd
from make_orders import make_orders
orders = make_orders(n=1_000)
orders.to_csv("orders.csv", index=False)
orders.to_parquet("orders.parquet")
dtypes = pd.DataFrame({
"written": orders.dtypes.astype(str),
"from CSV": pd.read_csv("orders.csv").dtypes.astype(str),
"from Parquet": pd.read_parquet("orders.parquet").dtypes.astype(str),
})
print(dtypes, end="\n\n")
print("Parquet round trip identical:", pd.read_parquet("orders.parquet").equals(orders))
print("CSV round trip identical: ", pd.read_csv("orders.csv").equals(orders))Output
written from CSV from Parquet order_id int64 int64 int64 city category str category channel str str str placed_at datetime64[us] str datetime64[us] qty int64 int64 int64 price float64 float64 float64 coupon Int64 float64 Int64 is_gift bool bool bool Parquet round trip identical: True CSV round trip identical: False
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 types_survive.py
The CSV round trip turned the category into plain text, the timestamps into text, and the nullable integers (most
orders have no coupon) into float64. Every downstream script would need the dtype and parse_dates options of the
CSV lesson to repair that. The Parquet round trip gave back a table identical to the one written, because the types
are stored in the file.
Reading less: columns and row groups
Columnar storage pays off when a question needs only part of the table. pd.read_parquet(path, columns=[...]) reads
only the named column chunks, while read_csv(usecols=...) must still read and split every line:
import time
import pandas as pd
from make_orders import make_orders
orders = make_orders()
orders.to_csv("orders.csv", index=False)
orders.to_parquet("orders.parquet")
orders.to_feather("orders.feather")
def best_of_three(read):
best = float("inf")
for _ in range(3):
start = time.perf_counter()
read()
best = min(best, time.perf_counter() - start)
return best
runs = {
"CSV, all 8 columns": lambda: pd.read_csv("orders.csv"),
"CSV, 2 columns": lambda: pd.read_csv("orders.csv", usecols=["city", "price"]),
"Parquet, all 8 columns": lambda: pd.read_parquet("orders.parquet"),
"Parquet, 2 columns": lambda: pd.read_parquet("orders.parquet", columns=["city", "price"]),
"Feather, all 8 columns": lambda: pd.read_feather("orders.feather"),
}
for label, read in runs.items():
print(f"{label:<24}{best_of_three(read) * 1000:>8.1f} ms")Output
CSV, all 8 columns 299.3 ms CSV, 2 columns 93.1 ms Parquet, all 8 columns 10.3 ms Parquet, 2 columns 5.3 ms Feather, all 8 columns 9.7 ms
This output changes from run to run: the times differ on every run and on every computer
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 read_times.py
Parquet was many times faster than CSV for the whole table, and faster still for two columns. The footer’s statistics also let a reader skip row groups. Written in time order, in row groups of 50,000 rows, each group covers about a month, so a filter for March can only match two of the six groups:
import pandas as pd
import pyarrow.parquet as pq
from make_orders import make_orders
# Sorted by time and written in row groups of 50,000 rows, so each group covers a narrow span of dates.
orders = make_orders().sort_values("placed_at", ignore_index=True)
orders.to_parquet("orders.parquet", row_group_size=50_000)
meta = pq.ParquetFile("orders.parquet").metadata
column = meta.schema.names.index("placed_at")
march = (pd.Timestamp("2026-03-01"), pd.Timestamp("2026-04-01"))
print(f"{meta.num_rows:,} rows in {meta.num_row_groups} row groups")
for g in range(meta.num_row_groups):
stats = meta.row_group(g).column(column).statistics
low, high = pd.Timestamp(stats.min), pd.Timestamp(stats.max)
overlaps = low < march[1] and high >= march[0]
print(f"group {g}: {low:%Y-%m-%d} to {high:%Y-%m-%d} {'read' if overlaps else 'skipped'} for March")
in_march = pd.read_parquet("orders.parquet", filters=[("placed_at", ">=", march[0]), ("placed_at", "<", march[1])])
print(f"\nrows placed in March: {len(in_march):,}")Output
300,000 rows in 6 row groups group 0: 2026-01-01 to 2026-01-31 skipped for March group 1: 2026-01-31 to 2026-03-02 read for March group 2: 2026-03-02 to 2026-04-01 read for March group 3: 2026-04-01 to 2026-05-01 skipped for March group 4: 2026-05-01 to 2026-05-31 skipped for March group 5: 2026-05-31 to 2026-06-30 skipped for March rows placed in March: 51,250
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 row_groups.py
filters=[("placed_at", ">=", ...), ("placed_at", "<", ...)] passes the condition to the Parquet reader, which uses
the same statistics to skip the groups that cannot match, and then keeps only the matching rows of the others. The
skipping works only when the data is clustered: on unsorted data, every group spans the whole half-year and every
group must be read. Sorting by the column people filter on most, usually a date, costs a little once, when the file is
written, and every later read benefits.
A mixed column will not write
Parquet stores one type per column. A column with Python objects of different types, such as product codes read from a spreadsheet that are partly numbers and partly text, cannot be written as it is:
import pandas as pd
# A product-code column read from a spreadsheet: numbers and text mixed in one object column.
for values in (["A-17", 1001, None, 2.5], [1001, "A-17", None, 2.5]):
frame = pd.DataFrame({"code": pd.Series(values, dtype="object")})
try:
frame.to_parquet("codes.parquet")
except Exception as err: # pyarrow raises ArrowTypeError or ArrowInvalid
print(f"{type(err).__name__}: {err.args[0]}")
# Make the column text on purpose. In pandas 3, astype("str") keeps the missing value missing.
fixed = frame.assign(code=frame["code"].astype("str"))
fixed.to_parquet("codes.parquet")
back = pd.read_parquet("codes.parquet")
print(back["code"].dtype, back["code"].tolist())Output
ArrowTypeError: Expected bytes, got a 'int' object ArrowInvalid: Could not convert 'A-17' with type str: tried to convert to int64 str ['1001', 'A-17', nan, '2.5']
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 mixed_types.py
The error depends on which type comes first: pyarrow takes the first value’s type for the column and fails at the
first value that does not fit. The fix is a decision about what the column is. Product codes are text, so
astype("str") converts every value to text. In pandas 3 that conversion keeps missing values missing; in pandas 2,
astype(str) wrote the word nan instead, according to pandas’ migration guide.
One file, three readers
A Parquet file is not tied to pandas. polars and DuckDB read the same file, with the same types, and agree on every number:
import duckdb
import pandas as pd
import polars as pl
from make_orders import make_orders
make_orders().to_parquet("orders.parquet")
df = pd.read_parquet("orders.parquet")
print("pandas:", len(df), round((df["qty"] * df["price"]).sum(), 2))
lf = pl.read_parquet("orders.parquet")
print("polars:", lf.height, round(lf.select((pl.col("qty") * pl.col("price")).sum()).item(), 2))
rows, revenue = duckdb.sql("SELECT count(*), round(sum(qty * price), 2) FROM 'orders.parquet'").fetchone()
print("DuckDB:", rows, revenue)
print(lf.schema)Output
pandas: 300000 227247085.1
polars: 300000 227247085.1
DuckDB: 300000 227247085.1
Schema({'order_id': Int64, 'city': Categorical, 'channel': String, 'placed_at': Datetime(time_unit='us', time_zone=None), 'qty': Int64, 'price': Float64, 'coupon': Int64, 'is_gift': Boolean})
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 three_readers.py
DuckDB queries the file directly with SQL, without loading it into a DataFrame first, and reads only the two columns the query uses. polars reports the same eight columns with its own type names, the category included.
Parquet, Avro & Arrow File Viewer Open a Parquet, Arrow or Avro file in the browser to see its rows, schema, row groups and statistics.Arrow: the shared format in memory
Parquet is for files; Arrow describes how a column sits in memory. pandas 3 stores its str columns in Arrow arrays
when PyArrow is installed, polars is built on the Arrow layout, and DuckDB reads and returns Arrow tables. Because they
share the layout, a table can move between them through Arrow:
import duckdb
import pandas as pd
import polars as pl
import pyarrow as pa
from make_orders import make_orders
orders = make_orders(n=1_000)
arrow_orders = pa.Table.from_pandas(orders, preserve_index=False) # pandas to Arrow
print(arrow_orders.schema.field("city"), "|", arrow_orders.schema.field("coupon"))
frame = pl.from_arrow(arrow_orders) # Arrow to polars
print("polars:", frame.shape)
# DuckDB queries the Arrow table through its Python variable name.
print("DuckDB:", duckdb.sql("SELECT channel, count(*) AS n FROM arrow_orders GROUP BY channel ORDER BY channel").fetchall())
# pandas 3 builds a DataFrame from anything that offers Arrow data (the PyCapsule interface).
back = pd.DataFrame.from_arrow(frame)
print("pandas from polars:", back.shape, back["coupon"].dtype)Output
pyarrow.Field<city: dictionary<values=large_string, indices=int8, ordered=0>> | pyarrow.Field<coupon: int64>
polars: (1000, 8)
DuckDB: [('app', 345), ('shop', 321), ('web', 334)]
pandas from polars: (1000, 8) float64
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 arrow_handoff.py
pa.Table.from_pandasturns the DataFrame into an Arrow table; the category becomes a dictionary-encoded column (each city stored once, with small integer codes).pl.from_arrowand a DuckDB query on the Arrow table work on it directly; DuckDB finds the table by its Python variable name.pd.DataFrame.from_arrow, new in pandas 3, accepts any object that offers its data through the Arrow PyCapsule interface, such as a polars DataFrame or a DuckDB result. The run shows one trap: the nullable integer column came back asfloat64. Checkdtypesafter every hand-off, as after every file read.
Choosing a format for a hand-off
| Criterion | CSV | Excel |
|---|---|---|
| Reader | Anyone, any tool | People who edit numbers |
| Keeps types | No | Partly |
| Size here | 15.8 MB | Not measured |
| When to choose | Sharing with anyone | Reports people adjust |
| Criterion | JSON | Parquet | Feather |
|---|---|---|---|
| Reader | Web services | Analysis tools | Your next step |
| Keeps types | Partly | Yes | Yes |
| Size here | 42.2 MB | 3.9 to 6.8 MB | 8.0 MB |
| Nested data | Yes | Yes | Yes |
| When to choose | APIs | Storing, sharing | Short hand-offs |
“Partly” means that some types survive and others do not. An Excel cell holds a number, text, a boolean or a date, so categories come back as text and integer columns with gaps as floats; JSON has numbers, text, booleans and nesting, but no dates.
Your turn: save a table safely
Write save_parquet_safely(df, path). It must write any DataFrame to Parquet so that reading the file back gives the
same table, except that columns mixing numbers and text become text, with their missing values still missing. It
must not change the caller’s DataFrame.
import pandas as pd
def save_parquet_safely(df, path):
"""Write df to Parquet so that reading it back gives the same table, with mixed columns as text."""
df.to_parquet(path) # fails on a column that mixes numbers and textimport pandas as pd
from save_safely import save_parquet_safely
stock = pd.DataFrame({
"code": pd.Series([1001, "A-17", None, "007"], dtype="object"),
"sold_on": pd.to_datetime(["2026-04-01", "2026-04-02", None, "2026-04-03"]),
"qty": pd.array([5, None, 2, 1], dtype="Int64"),
"note": pd.Series(["", None, "gift", "repeat"], dtype="object"),
"price": [18.5, 22.0, 9.99, 120.0],
})
before = stock.copy()
expected = stock.assign(code=stock["code"].astype("str"), note=stock["note"].astype("str"))
def check(name, test):
try:
ok = bool(test())
except Exception: # the write failed, for example
ok = False
print("PASS" if ok else "FAIL", name)
return ok
def saved():
save_parquet_safely(stock, "stock.parquet")
return pd.read_parquet("stock.parquet")
back = None
try:
back = saved()
except Exception as err:
print(f"save_parquet_safely raised {type(err).__name__}")
results = [
check("the file is written and read back", lambda: back is not None),
check("the same columns in the same order", lambda: list(back.columns) == list(stock.columns)),
check("mixed columns become text, missing values stay missing", lambda: back["code"].dtype == "str" and back["code"].isna().sum() == 1),
check("dates are unchanged", lambda: back["sold_on"].equals(stock["sold_on"])),
check("nullable integers stay Int64", lambda: back["qty"].dtype == "Int64" and back["qty"].isna().sum() == 1),
check("the whole table matches", lambda: pd.testing.assert_frame_equal(back, expected) is None),
check("the caller's DataFrame is not changed", lambda: stock.equals(before)),
]
print(f"{sum(results)} of {len(results)} checks pass")Output
save_parquet_safely raised ArrowInvalid FAIL the file is written and read back FAIL the same columns in the same order FAIL mixed columns become text, missing values stay missing FAIL dates are unchanged FAIL nullable integers stay Int64 FAIL the whole table matches PASS the caller's DataFrame is not changed 1 of 7 checks pass
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 check_save.py
Place both files next to each other and change nothing but save_safely.py; once the function is right,
python check_save.py passes all seven of its checks. Hints: find the columns whose dtype is object, convert a copy rather than the original, and
leave every other column alone, so the dates and the nullable integers keep their types.
Check yourself
5 questions about this lesson. Every answer and why it is right is on the page, behind “Show the answer”. Your score stays in this browser.
Interview questions
Fresher · Why is Parquet usually smaller and faster to query than CSV?
CSV stores each row as text, so numbers take as many bytes as they have digits, every value must be parsed again on every read, and a reader must scan whole lines even to get one column. Parquet stores data by column: each column chunk holds values of one type in binary form and is compressed on its own, which works well because a column’s values are alike. A query that needs two columns reads only those chunks, and the minimum and maximum kept for each chunk let a reader skip row groups that cannot match a filter. The schema is stored in the file, so types survive without guessing. In this lesson’s run, 300,000 orders took 15.8 MB as CSV and 5.2 MB as snappy-compressed Parquet, which pandas read many times faster.
Key takeaways
- Parquet stores columns together, compressed, with the schema and min/max statistics in the footer; CSV stores rows of text.
- On 300,000 orders, Parquet took 25 to 43 % of the CSV’s size, depending on the codec; snappy is the default, zstd is smaller at similar speed, and gzip was far slower to write.
- Types survive Parquet; a CSV round trip lost the category, the timestamps and the nullable integers.
columns=[...]reads only some column chunks, andfiltersskips row groups, if the data was sorted by the filter column before writing.- A column that mixes numbers and text will not write; convert it with
astype("str"), which keeps missing values missing in pandas 3. - pandas, polars and DuckDB read the same file and agree; DuckDB queries it with SQL directly.
- Arrow is the shared format in memory:
pa.Table.from_pandas,pl.from_arrowandpd.DataFrame.from_arrowmove tables between libraries, but check the dtypes after each move. - Hand people CSV or Excel, programs JSON, analysis Parquet, and your own next step Feather.
References
- Parquet file format documentation (The Apache Software Foundation)
- pandas.DataFrame.to_parquet (pandas API reference) (pandas development team (NumFOCUS))
- pandas.read_parquet (pandas API reference) (pandas development team (NumFOCUS))
- pandas.DataFrame.from_arrow (pandas API reference) (pandas development team (NumFOCUS))
- What's new in 3.0.0 (pandas) (pandas development team (NumFOCUS))
- PyArrow functionality (pandas user guide) (pandas development team (NumFOCUS))
- Migration guide for the new string data type (pandas 3.0) (pandas development team (NumFOCUS))
- Apache Arrow Python bindings (PyArrow) (The Apache Software Foundation)
- Arrow Columnar Format (The Apache Software Foundation)
- The Arrow PyCapsule Interface (The Apache Software Foundation)
- Feather File Format (PyArrow) (The Apache Software Foundation)
- Reading and Writing Parquet Files (DuckDB documentation) (DuckDB Foundation)
- Parquet (Polars user guide) (Polars)
Related tools
Report a problem with this lesson
Kept only in this browser. Your Learn progress