Skip to main content

The Same 540K Rows Can Be 3.5MB or 102.7MB: How to Choose Between CSV, Excel, JSON and Parquet

· 16 min read
Inyoung Lee
Datapopcorn CEO / AI automation educator

I saved the same 540,000 rows of online store transactions and got 3.5MB as Parquet, 45.0MB as CSV and 102.7MB as JSON Lines. Reading them back took 0.1 seconds for Parquet, 0.24 seconds for CSV, 1.3 seconds for JSON and around 20 seconds for the original Excel file. The bigger difference is types. With CSV and JSON, the moment I read the data back, dates turned into strings and customer ID 17850 turned into 17850.0, while Parquet kept them as they were. On the other hand, with a small 151-row dataset, CSV was smaller than Parquet. You don't choose a format by file size alone. You choose it by who reads it, how often it's read, and whether the types need to survive.

This post uses the same UCI Online Retail II data as the total sales post and the pandas types post, and practices JSON with PokeAPI.

What the four formats are like​

FormatShapeType infoEasy for people to openTypical use
CSVTable, row-based textNoneEasySharing with people, when it needs to open in any tool
ExcelTable + formatting and formulasPartialEasiestWhen people view and edit it directly
JSONText that can be nestedRoughly strings and numbersPossibleAPI responses, data where each item has a different number of children
ParquetTable, column-based binaryPreservedHardIntermediate saves during analysis, data read repeatedly, large data

Parquet stores data by column, not by row. Similar values from the same column sit next to each other, so it compresses well, and you can read just the columns you need. It also records each column's type inside the file. All three show up clearly in the measurements below.

Stuck at the save step: Parquet rejects mixed types​

As in the previous post, I read the data with explicit types for the invoice, product and customer ID columns, then saved it in three formats. CSV and JSON saved without a problem, but Parquet threw an error.

Excel read: 22.6s, 541,910 rows
Parquet save failed: ArrowTypeError - ("Expected bytes, got a 'int' object", 'Conversion failed for column Description with type
Python types of Description values: {'str': 540455, 'float': 1454, 'int': 1} | numeric examples: [20713]
Saved after casting Description to string

The product description (Description) column held 540,455 strings and 1,454 empty values (NaN, which is a float in Python), plus exactly one number, 20713. It's a value Excel read as a number. CSV silently saves this kind of mix as text, while Parquet requires "one type per column," so it refuses to save.

It looks like an annoying error, but it actually caught, at the save step, the "silently wrong" problem from the previous post. Once I set the description column to string, it saved.

File size and read time​

Original xlsx (2 sheets): 43.5 MB
CSV size: 45.0 MB
JSON Lines size: 102.7 MB
Parquet size: 3.5 MB
CSV read: 0.242s (best of 3)
JSON Lines read: 1.295s (best of 3)
Parquet read: 0.099s (best of 3)
Parquet (2 columns only) read: 0.003s (best of 3)

Each read time is the fastest of three runs after one warm-up read. In this script I timed Excel only once: 22.6 seconds in the run above, and between 18 and 23 seconds when I reran the same script several times.

FormatSizeRead time
Excel (original, 2 sheets)43.5 MBAbout 18-23s (one sheet)
CSV45.0 MB0.242s
JSON Lines102.7 MB1.295s
Parquet3.5 MB0.099s
Parquet, only the 2 needed columns(same file)0.003s

Parquet is one-thirteenth the size of CSV. This data has 540,000 rows with 38 country names and the same product descriptions repeated over and over, so column-based compression works especially well. JSON Lines writes the column names again on every row, so it's more than twice the size of CSV.

What stands out in the read times is that for data this size, CSV is plenty fast too. Parquet's real strength is in the last line. If you only need the quantity and price columns, it pulls just those columns off the disk, so it's done in 0.003 seconds. The more columns a real-world table has (hundreds, say), the bigger this gap gets. For reference, the original xlsx is already a compressed format, so its size is similar to the CSV, but it took 70 to 90 times longer to read than CSV.

Do the types survive a round trip?​

I read the saved files back without any options and compared the types.

[CSV] InvoiceDate=object, Customer ID=float64, Invoice=object, StockCode=object, Customer ID sample=np.float64(17850.0), values equal (Customer ID)=False
[JSON Lines] InvoiceDate=object, Customer ID=float64, Invoice=object, StockCode=object, Customer ID sample=np.float64(17850.0), values equal (Customer ID)=False
[Parquet] InvoiceDate=datetime64[ns], Customer ID=string, Invoice=object, StockCode=object, Customer ID sample='17850', values equal (Customer ID)=True
ColumnOriginal typeRead back from CSVRead back from JSON LinesRead back from Parquet
InvoiceDateDateStringStringDate
Customer IDString '17850'Float 17850.0Float 17850.0String '17850'

CSV and JSON can't carry information like "this column is a date" or "this column is a string ID." When reading them back, pandas guesses from the values alone: a customer ID that looks like a number becomes a number, and because empty values are mixed in, it guesses float. The type problem you barely fixed at read time comes back after a single save. Dates become strings, so you have to convert them again before any monthly aggregation.

If you have to use CSV, you need to specify dtype and parse_dates every time you read it. If you're saving intermediate results during analysis, Parquet does this for you.

Split data that grows every day: partitioning​

If you dump data that piles up daily, like orders, into a single file, you have to read the whole thing even when you only want "this month." So you save it in folders split by something like date. This is called partitioning. If you name the folders in the form column=value, most tools read the name and restore it as a column.

df["ym"] = df["InvoiceDate"].dt.strftime("%Y-%m")
df.to_parquet("by_month", partition_cols=["ym"], index=False)          # one folder per month
nov = pd.read_parquet("by_month", filters=[("ym", "==", "2011-11")])   # read only the November folder
Partition folders: 13 total, ym=2010-12 … ym=2011-12
Read 2011-11 only: 84,711 rows, 0.02s, ym dtype=category

It created 13 folders, from ym=2010-12 to ym=2011-12, and read only the 84,711 rows for November 2011 in 0.02 seconds. With partitioning, if a particular month was collected incorrectly, you can delete just that folder and rewrite it, which also helps automation where running the same job several times should give the same result.

There are two things to watch out for. First, the ym you read back is category, not string. Partition columns are restored from the folder names, not from inside the files. So here too, you need to check types after reading. Second, splitting too finely actually makes things slower. If a day's data is only a few KB and you split by day, all the time goes into opening and closing thousands of small files. In this post the data is a few thousand rows per day, so I split by month for the experiment. Whether daily or monthly is better depends on your actual query patterns and file sizes.

JSON: data that doesn't fit neatly into a table​

CSV, Excel and Parquet are all tables with fixed rows and columns. But the data APIs return often isn't a table. If you pull the 151 first-generation Pokémon from PokeAPI, each Pokémon can have one type or two.

import requests
import pandas as pd

rows = []
for i in range(1, 152):
    p = requests.get(f"https://pokeapi.co/api/v2/pokemon/{i}", timeout=30).json()
    rows.append({
        "dex_no": f"{p['id']:04d}",                              # four-digit string like 0025
        "name": p["name"],
        "weight": p["weight"],                                  # unit: 0.1kg
        "types": [t["type"]["name"] for t in p["types"]],       # keep the list as is
        **{s["stat"]["name"]: s["base_stat"] for s in p["stats"]},
    })
df = pd.DataFrame(rows)
Rows: 151 | 2 types: 67 | 1 type: 84
Charizard: ['fire', 'flying'] | Pikachu: ['electric']
Rows after explode: 218
Total weight: originally 6,938.7kg → after explode 10,650.7kg

67 of the 151 Pokémon have two types. This is where a common mistake comes in. If you use explode to spread the list into rows for a per-type analysis, 151 rows become 218, and Pokémon with two types appear twice. Sum the weights in that state and 6,938.7kg inflates to 10,650.7kg. It's the same problem as total sales getting inflated when you sum without checking what one row represents. The moment you explode, the meaning of one row changes from "one Pokémon" to "one type of one Pokémon." Do per-type analysis on the exploded table, and per-Pokémon totals on the original table.

Nested dictionaries in JSON, like {"stats": {"hp": 35, "attack": 55}}, can be flattened into stats.hp and stats.attack columns with pd.json_normalize(). The part that needs a decision is a list like types above, in other words a one-to-many relationship.

With small data, CSV is smaller​

I saved the same 151 Pokémon in three formats and read them back.

gen1.csv 7.7 KB
gen1.json 23.0 KB
gen1.parquet 10.7 KB
Reread from CSV | Pikachu dex_no: np.int64(25) | Bulbasaur types: "['grass', 'poison']"
Reread from Parquet | Pikachu dex_no: '0025' | Bulbasaur types: ndarray ['grass', 'poison']

At 151 rows, CSV (7.7KB) is smaller than Parquet (10.7KB). Parquet attaches schema and statistics to every file, so with small data that fixed cost outweighs the rest. The benefit of compression only shows when the data is large, like the 540,000 rows earlier. If someone says "Parquet is always smaller," AI or human, they said it without actually measuring.

Even so, Parquet is still the only one that gives the types back intact when read with default options (with CSV, you can prevent this by specifying dtype when reading). Read it back from CSV and Pikachu's Pokédex number 0025 becomes the number 25, and Bulbasaur's list of types becomes a single string, "['grass', 'poison']". From Parquet, '0025' and ['grass', 'poison'] come back as they were (strictly speaking, as an array, not a Python list).

When to use what​

SituationFormat
Sending results to people when you don't know what program they'll useCSV
When people open and edit it directly, or you need colors and formulasExcel
Receiving API responses, or when each item has a different number of childrenJSON
Saving intermediate results during analysis and reading them repeatedly, or when the data is largeParquet
Data that piles up every dayParquet + date partitions

When you ask AI to do the conversion​

Format conversion is a good job to hand to AI. Just ask it to also verify that the converted result matches the original.

Convert this CSV to Parquet. After converting, compare the file size and read time of the two files, and when reading it back, check that the row count, column types, dates and code values with leading zeros match the original.

When you get the result, check three things: Is the row count the same? Are the column types right, with dates as dates and IDs as strings? In sample values, are leading zeros and date formats intact? Summary statistics alone make it easy to miss that 0025 became 25, so compare a few rows by eye.

Questions you might get in an interview​

Explain the difference between CSV and Parquet, and when you'd use Parquet. CSV is row-based text, so it opens anywhere, but it has no type information and you read the whole thing every time. Parquet is column-based binary, so you read only the columns you need, it compresses well and it preserves types. Use Parquet for intermediate saves during analysis and for large data, and CSV for handing things to people. With the 540,000 rows in this post, it was 45.0MB vs. 3.5MB, and 0.003 seconds to read just two columns.

What are Parquet's downsides? People can't open it directly, it's hard to edit a single row, and with small data it's actually bigger than CSV. At 151 rows, CSV was 7.7KB and Parquet was 10.7KB.

What do you watch out for when turning nested JSON into a table? Dictionaries can be flattened with json_normalize, but if you spread a list (one-to-many) into rows with explode, the meaning of a row changes and totals get double-counted. First decide what one row represents, and if needed, split the one-to-many information into a separate table.

What's the difference between partitioning and sharding? Partitioning splits data into sections within one storage location by something like date, so you read only the part you need. Sharding splits data across multiple servers by a key such as a hash of the user ID, to spread the load. Analysts run into partitioning often; for sharding, knowing the concept is enough.

Full code​

Run pip install pandas openpyxl pyarrow requests first. The first script compares formats on the online store data; run it in the same folder as online_retail_II.xlsx.

import os, time, shutil
import pandas as pd
import pyarrow  # load it up front so import time doesn't leak into the first read time

XLSX = os.environ.get("RETAIL_XLSX", "online_retail_II.xlsx")
OUT = "formats"
shutil.rmtree(OUT, ignore_errors=True); os.makedirs(OUT)

def timed(fn):
    t = time.perf_counter(); r = fn(); return r, time.perf_counter() - t

# The original, read with explicit types
df, t_xlsx = timed(lambda: pd.read_excel(
    XLSX, sheet_name="Year 2010-2011",
    dtype={"Invoice": str, "StockCode": str, "Customer ID": "Int64"}))
df["Customer ID"] = df["Customer ID"].astype("string")  # IDs as strings
print(f"Excel read: {t_xlsx:.1f}s, {len(df):,} rows")

# 1) Save in three formats
paths = {"CSV": f"{OUT}/retail.csv", "JSON Lines": f"{OUT}/retail.jsonl", "Parquet": f"{OUT}/retail.parquet"}
df.to_csv(paths["CSV"], index=False)
df.to_json(paths["JSON Lines"], orient="records", lines=True, date_format="iso", force_ascii=False)
try:
    df.to_parquet(paths["Parquet"], index=False)
except Exception as e:
    print("Parquet save failed:", type(e).__name__, "-", str(e)[:90])
    odd = df["Description"].map(type).value_counts().to_dict()
    print("Python types of Description values:", {t.__name__: n for t, n in odd.items()},
          "| numeric examples:", df.loc[df["Description"].map(lambda v: isinstance(v, (int, float)) and pd.notna(v)), "Description"].head(3).tolist())
    df["Description"] = df["Description"].astype("string")
    df.to_parquet(paths["Parquet"], index=False)
    print("Saved after casting Description to string")
xlsx_mb = os.path.getsize(XLSX) / 1024**2
print(f"Original xlsx (2 sheets): {xlsx_mb:.1f} MB")
for k, p in paths.items():
    print(f"{k} size: {os.path.getsize(p) / 1024**2:.1f} MB")

# 2) Time to read back
readers = {
    "CSV": lambda: pd.read_csv(paths["CSV"]),
    "JSON Lines": lambda: pd.read_json(paths["JSON Lines"], lines=True),
    "Parquet": lambda: pd.read_parquet(paths["Parquet"]),
    "Parquet (2 columns only)": lambda: pd.read_parquet(paths["Parquet"], columns=["Quantity", "Price"]),
}
back = {}
for k, fn in readers.items():
    fn()  # one warm-up run
    runs = [timed(fn) for _ in range(3)]
    back[k] = runs[0][0]
    print(f"{k} read: {min(sec for _, sec in runs):.3f}s (best of 3)")

# 3) Do the types survive the round trip?
for k in ["CSV", "JSON Lines", "Parquet"]:
    b = back[k]
    print(f"[{k}] InvoiceDate={b['InvoiceDate'].dtype}, Customer ID={b['Customer ID'].dtype}, "
          f"Invoice={b['Invoice'].dtype}, StockCode={b['StockCode'].dtype}, "
          f"Customer ID sample={b['Customer ID'].dropna().iloc[0]!r}, "
          f"values equal (Customer ID)={b['Customer ID'].astype('string').fillna('').equals(df['Customer ID'].fillna(''))}")

# 4) Monthly partitions
df["ym"] = df["InvoiceDate"].dt.strftime("%Y-%m")
df.to_parquet(f"{OUT}/by_month", partition_cols=["ym"], index=False)
folders = sorted(os.listdir(f"{OUT}/by_month"))
print("Partition folders:", len(folders), "total,", folders[0], "…", folders[-1])
nov, sec = timed(lambda: pd.read_parquet(f"{OUT}/by_month", filters=[("ym", "==", "2011-11")]))
print(f"Read 2011-11 only: {len(nov):,} rows, {sec:.2f}s, ym dtype={nov['ym'].dtype}")

The second is the PokeAPI script. It sends one request per Pokémon, 151 requests in total.

import os
import requests
import pandas as pd

rows = []
for i in range(1, 152):
    p = requests.get(f"https://pokeapi.co/api/v2/pokemon/{i}", timeout=30).json()
    rows.append({
        "dex_no": f"{p['id']:04d}",
        "name": p["name"],
        "weight": p["weight"],  # unit: 0.1kg
        "types": [t["type"]["name"] for t in p["types"]],
        **{s["stat"]["name"]: s["base_stat"] for s in p["stats"]},
    })
df = pd.DataFrame(rows)
print("Rows:", len(df), "| 2 types:", (df["types"].str.len() == 2).sum(), "| 1 type:", (df["types"].str.len() == 1).sum())
print("Charizard:", df.loc[df["name"] == "charizard", "types"].iloc[0], "| Pikachu:", df.loc[df["name"] == "pikachu", "types"].iloc[0])

# Spreading the one-to-many relationship into rows
long = df.explode("types")
print("Rows after explode:", len(long))
print(f"Total weight: originally {df['weight'].sum() / 10:,.1f}kg → after explode {long['weight'].sum() / 10:,.1f}kg")

# Save, then read back
os.makedirs("poke", exist_ok=True)
df.to_csv("poke/gen1.csv", index=False)
df.to_json("poke/gen1.json", orient="records", force_ascii=False)
df.to_parquet("poke/gen1.parquet", index=False)
for f in ["gen1.csv", "gen1.json", "gen1.parquet"]:
    print(f, f"{os.path.getsize('poke/' + f) / 1024:.1f} KB")
c = pd.read_csv("poke/gen1.csv")
q = pd.read_parquet("poke/gen1.parquet")
print("Reread from CSV | Pikachu dex_no:", repr(c.loc[c["name"] == "pikachu", "dex_no"].iloc[0]), "| Bulbasaur types:", repr(c.loc[c["name"] == "bulbasaur", "types"].iloc[0]))
b = q.loc[q["name"] == "bulbasaur", "types"].iloc[0]
print("Reread from Parquet | Pikachu dex_no:", repr(q.loc[q["name"] == "pikachu", "dex_no"].iloc[0]), "| Bulbasaur types:", type(b).__name__, list(b))

References​

File sizes and times were measured on macOS with Python 3.9.6, pandas 2.3.3 and pyarrow 21.0.0. Times vary by environment, so read them as relative differences between formats.