How to read a CSV file in Python (faster than pandas)

To read a CSV file in Python, use chDB as a drop-in replacement for pandas. Change one import line and the code you already write keeps working:

1import chdb.datastore as pd
2df = pd.read_csv("orders.csv")

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.

1pip install chdb

Prefer the command line? See how to run SQL on a CSV file to do this with clickhouse local.

Read a CSV into a DataFrame #

pd.read_csv works the way you expect. Point it at a path and print the result:

1import chdb.datastore as pd
2
3df = pd.read_csv("orders.csv")
4print(df)
5print(df.dtypes)

1   order_id country product  amount order_date
20         0      GB    book   50.98 2026-01-01
31         1      AU     pen   30.73 2026-01-02
42         2      IN     mug   81.54 2026-01-03
53         3      US    lamp   28.92 2026-01-04
64         4      GB    book    4.15 2026-01-05
75         5      AU     pen   63.26 2026-01-06
86         6      IN     mug   23.42 2026-01-07
97         7      US    lamp   47.28 2026-01-08
10order_id              int64
11country                 str
12product                 str
13amount              float64
14order_date    datetime64[s]
15dtype: object

Look at the dtypes. chDB inferred them from the data: order_id is an integer, amount is a float, and order_date came back as a real datetime, not a string. You did not declare a single type or pass a dtype map.

One thing is different from pandas under the hood: 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:

1import chdb.datastore as pd
2
3df = pd.read_csv("orders.csv")
4revenue = df[df["product"] == "book"].groupby("country")["amount"].sum()
5print(revenue)

1country
2GB    55.13
3Name: amount, dtype: float64

Same syntax as pandas, same result. The difference is that chDB compiles the whole chain into one optimized query and only scans the columns the aggregation touches, rather than materializing the full file first. Joins, computed columns with assign, the .str and .dt accessors, and merge across files all work the same way.

No header? Name the columns #

A CSV has no built-in schema, so a headerless file needs column names from you, exactly as in pandas. Pass names and chDB still infers each column's type:

1df = pd.read_csv(
2    "orders_noheader.csv",
3    names=["order_id", "country", "product", "amount", "order_date"],
4)
5print(df.head(3))

1   order_id country product  amount  order_date
20         0      GB    book   50.98  2026-01-01
31         1      AU     pen   30.73  2026-01-02
42         2      IN     mug   81.54  2026-01-03

Leave names off a headerless file and the columns come back as c1, c2, c3 and so on. Pass header=None if you want that positional naming on purpose.

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:

1df = pd.read_csv("orders.csv")
2pdf = 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_csv`? #

On a file big enough that scanning is the actual cost, yes. CSV is a text format, so most of the work is parsing bytes into typed values, and chDB does that in compiled, multi-threaded ClickHouse code rather than the Python layer. The aggregation above on a 3M-row (~110 MB) CSV, best-of-3 with a warm page cache on an Apple M4 Pro (14 cores, 24 GB RAM, macOS):

1import pandas as pd:            0.561s
2import chdb.datastore as pd:    0.105s
3speedup:                        5.3x

About 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 gap widens as files grow, because plain pandas parses and holds the whole file in memory before it can group anything, while chDB streams and aggregates in one pass.

The small case can cut the other way: on a tiny CSV (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 CSV files plus run.ipynb and a run.py mirror containing the exact code above:

github.com/ClickHouse/examples/tree/main/local-analytics/chdb-csv

Working with other formats? Read a JSON file in Python and read a Parquet file in Python use the same drop-in import chdb.datastore as pd pattern; only the reader changes. If you keep re-reading the same CSV, convert it to Parquet once and every later read gets faster.

The same code scales from a CSV on your laptop to a server to ClickHouse Cloud with no rewrite, so there is nothing to re-learn when the data outgrows one machine.