--- title: "How to read an NDJSON file in Python (faster than pandas)" date: "2026-06-08" author: "Al Brown" category: "Engineering" excerpt: "Read an NDJSON file into a DataFrame with chDB, a drop-in replacement for pandas. Change one import line and your existing pandas code runs on ClickHouse's engine, so it stays fast as files grow." --- # How to read an NDJSON file in Python (faster than pandas) To read an NDJSON file in Python, use [chDB](/resources/engineering/what-is-chdb) as a drop-in replacement for pandas. Change one import line and the code you already write keeps working: ```python import chdb.datastore as pd df = pd.read_json("data/events.ndjson", lines=True) ``` `df` supports the pandas API you already use (indexing, filtering, `groupby`, `merge`), but the work runs on ClickHouse's engine instead of pandas, so it stays fast as files grow. There is no server to start and no separate load step. ```bash pip install chdb ``` Prefer the command line? See [how to query an NDJSON file](/resources/engineering/query-ndjson-file) to do this with clickhouse local. ## Read an NDJSON file into a DataFrame `pd.read_json` with `lines=True` reads one JSON object per line and infers each column's type: ```python import chdb.datastore as pd df = pd.read_json("data/events.ndjson", lines=True) print(df) print(df.dtypes) ``` ```text event_id country event_type revenue user tags 0 1 GB purchase 49.99 [101, gold] [web, promo] 1 2 AU view 0.00 [102, silver] [app] 2 3 GB purchase 19.50 [103, gold] [web] 3 4 IN purchase 5.00 [104, bronze] [app, promo, ref] 4 5 AU purchase 99.00 [105, gold] [] event_id int64 country str event_type str revenue float64 user object tags object dtype: object ``` Look at the dtypes. chDB inferred them from the data: `event_id` is an integer and `revenue` is a float. You did not declare a single type. Nested columns (`user`, `tags`) come back as `object` — Python lists or dicts, exactly as they appeared in the file. `df` is not the whole file loaded into memory. It is a lazy, ClickHouse-backed object that records what you asked for and runs it on ClickHouse's engine when you materialize a result, whether you print it, take `len()`, or call `.to_pandas()`. That is why it stays fast on large files: it only computes the rows and columns you actually use. ## Filter and aggregate the way you already do The pandas you write does not change. Filter with a boolean mask, group, and aggregate: ```python import chdb.datastore as pd df = pd.read_json("data/events.ndjson", lines=True) purchases = df[df["event_type"] == "purchase"].groupby("country")["revenue"].sum().sort_values(ascending=False) print(purchases) ``` ```text country AU 99.00 GB 69.49 IN 5.00 Name: revenue, dtype: float64 ``` Same syntax as pandas, same result. chDB compiles the whole chain into one optimized query and only scans the columns the aggregation touches, rather than materializing the full file first. ## Access nested fields NDJSON rows often carry nested objects and arrays. chDB reads them into `object` columns. To extract a sub-field, materialize the column with `.to_pandas()` and use `.apply()`: ```python df = pd.read_json("data/events.ndjson", lines=True) user_tiers = df[["event_id", "revenue"]].copy().to_pandas() user_tiers["tier"] = df["user"].to_pandas().apply(lambda u: u["tier"] if isinstance(u, dict) else u[1]) gold = user_tiers[user_tiers["tier"] == "gold"] print(gold) ``` ```text event_id revenue tier 0 1 49.99 gold 2 3 19.50 gold 4 5 99.00 gold ``` The pattern is: do the filtering and aggregation on the chDB object (where it is fast), then `.to_pandas()` the relevant column when you need to reach into nested data. ## Hand off to real pandas when you need it When a library downstream needs an actual pandas DataFrame (scikit-learn, a plotting call, anything that mutates in place), call `.to_pandas()` to materialize one: ```python df = pd.read_json("data/events.ndjson", lines=True) pdf = df.to_pandas() # a real pandas.DataFrame, in memory ``` The usual pattern is to do the heavy filtering and aggregation on the chDB object, where it is fast, then `.to_pandas()` the small result and continue in ordinary pandas. ## Is it faster than `pandas.read_json`? On a file big enough that scanning is the actual cost, yes. NDJSON is a text format, and chDB parses and aggregates in compiled, multi-threaded ClickHouse code rather than the Python layer. The filter and aggregation above on a 2M-row NDJSON file, best-of-3 with a warm page cache on an Apple M4 Pro (14 cores, 24 GB RAM, macOS); chDB 4.1.8, Python 3.14; best-of-3, warm: ```text import pandas as pd: 0.174s import chdb.datastore as pd: 0.031s speedup: 5.5x ``` About 5.5x here, from the same code with one import changed. The back-to-back ratio is the robust claim; absolute times shift with cache state and concurrent load. The small case can cut the other way: on a tiny NDJSON file (a few thousand rows) plain pandas is as fast or faster, because chDB pays a fixed per-query startup cost that dominates when there is barely any data. Reach for chDB when the file is large, or when you want one import to cover both. ## Works in Jupyter Printing a chDB object renders it as a table in a notebook, and `.to_pandas()` feeds straight into `.plot()`, joins, or any pandas code. The companion folder ships a `run.ipynb` you can open and run cell by cell. ## Run it yourself The complete, runnable example is here, with `generate.sh` to create the sample NDJSON files plus `run.ipynb` and a `run.py` mirror containing the exact code above: [github.com/ClickHouse/examples/tree/main/local-analytics/chdb-ndjson](https://github.com/ClickHouse/examples/tree/main/local-analytics/chdb-ndjson) Working with other formats? [Read a JSONL file in Python](/resources/engineering/read-jsonl-file-python) and [read a JSON file in Python](/resources/engineering/read-json-file-python) use the same drop-in `import chdb.datastore as pd` pattern; only the reader changes. For deeply nested documents, see [flatten nested JSON in Python](/resources/engineering/flatten-nested-json-python). The same code scales from an NDJSON file on your laptop to a server to [ClickHouse Cloud](https://clickhouse.com/cloud) with no rewrite, so there is nothing to re-learn when the data outgrows one machine.