--- title: "How to read a TSV file in Python (faster than pandas)" date: "2026-06-08" author: "Al Brown" category: "Engineering" excerpt: "Read a tab-separated 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 a TSV file in Python (faster than pandas) To read a TSV 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_csv("events.tsv", sep="\t") ``` `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 a TSV file](/resources/engineering/query-a-tsv-file) to do this with clickhouse local. ## Read a TSV into a DataFrame `pd.read_csv` works the way you expect. Pass `sep="\t"` for a tab-separated file and point it at a path: ```python import chdb.datastore as pd df = pd.read_csv("data/events.tsv", sep="\t") print(df) print(df.dtypes) ``` ```text event_id country event_type revenue product 0 1 GB purchase 49.99 Widget, Deluxe 1 2 AU view 0.00 Gadget 2 3 GB purchase 19.50 Widget 3 4 IN purchase 5.00 Bolt 4 5 AU purchase 99.00 Gizmo 5 6 IN view 0.00 Bolt event_id int64 country str event_type str revenue float64 product str dtype: object ``` Look at the dtypes. chDB inferred them from the data: `event_id` is an integer, `revenue` is a float. You did not declare a single type or pass a `dtype` map. Notice `Widget, Deluxe` in the product column. The comma inside it is just text. In a TSV the delimiter is a tab, so commas inside fields need no quoting and never split a column. That is why some teams ship TSV instead of CSV. 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: ```python import chdb.datastore as pd df = pd.read_csv("data/events.tsv", sep="\t") revenue = df[df["event_type"] == "purchase"].groupby("country")["revenue"].sum() print(revenue) ``` ```text country AU 99.00 GB 69.49 IN 5.00 Name: revenue, 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. 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 headerless TSV needs column names from you, exactly as in pandas. Pass `names` and `sep="\t"`, and chDB still infers each column's type: ```python df = pd.read_csv( "data/events_noheader.tsv", sep="\t", names=["event_id", "country", "event_type", "revenue", "product"], ) print(df.head(3)) ``` ```text event_id country event_type revenue product 0 1 GB purchase 49.99 Widget, Deluxe 1 2 AU view 0.00 Gadget 2 3 GB purchase 19.50 Widget ``` 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: ```python df = pd.read_csv("data/events.tsv", sep="\t") 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_csv`? On a file big enough that scanning is the actual cost, yes. TSV 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 (~85 MB) TSV, 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.484s import chdb.datastore as pd: 0.056s speedup: 8.6x ``` About 8.6x 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 TSV (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 TSV files plus `run.ipynb` and a `run.py` mirror containing the exact code above: [github.com/ClickHouse/examples/tree/main/local-analytics/chdb-tsv](https://github.com/ClickHouse/examples/tree/main/local-analytics/chdb-tsv) Working with other formats? [Read a CSV file in Python](/resources/engineering/read-csv-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. If you keep re-reading the same TSV, [convert it to Parquet](/resources/engineering/convert-tsv-to-parquet) once and every later read gets faster. The same code scales from a TSV 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.