Convert TSV to Parquet

A great deal of scientific and analytical output is tab-separated by convention rather than by choice: expression matrices, variant call summaries, annotation tables, instrument logs, search and ad platform exports. The tab was picked because the descriptive columns are full of commas and semicolons, and it has served as a transport format for thirty years.

It is a bad format to compute against repeatedly. Every read parses every byte, every tool re-derives the column types and derives them slightly differently, and a table that will be filtered a hundred times pays the parsing cost a hundred times. Converting once to a typed columnar file moves that work to the front, which is the entire argument for doing it.

A Parquet column holds one type for all of its values. The writer here reads the whole column before deciding, and the rule is deliberately unforgiving: if every non-empty value is a boolean the column is BOOLEAN, if they are all whole numbers inside the 32-bit range it is INT32, if they are all numbers it is DOUBLE, and if a single value is none of those the entire column is a string.

Analysis tables are full of values that are none of those. `NA` is the R convention and appears in nearly every table R touched. A bare `.` is the VCF and GTF convention for a missing field. `-`, `n/a`, `NULL`, `#N/A` and `--` all appear depending on which program wrote the file. Any one of them in any row makes a column of ten million numbers into a column of ten million strings, and that is why a column you know is numeric arrives as text.

The tempting alternative is to type the column from the first row, or from the first thousand, and write anything that disagrees as null. Most tools do exactly that. It produces a file that is valid, loads without a warning, and has silently deleted every row where the marker appeared.

Nobody discovers that until a count comes back short, and by then the file has usually been joined to three others. A string column is loud: it shows up the moment you look at the schema, it can be cast in one expression, and the cast will tell you precisely which values could not be converted. Refusing to guess costs you a cast and saves you a class of error that is very hard to find afterwards. That trade is made deliberately and it is the single most consequential decision in this conversion.

It takes one command. `cut -f7 table.tsv | sort -u | head -50` prints the distinct values of the seventh column, and the marker will be obvious among them. Doing it on every column of a wide table is a loop; doing it on the three columns you actually filter on is usually enough.

The fix is in the source, not in the converter: replace the marker with a genuinely empty field. Nulls are excluded from the type inference, so a column of numbers with real gaps comes through as a numeric column with nulls in it, which is what a query engine expects and what an average or a range filter can work with. `awk` doing a field-level substitution over one column is a few seconds of work and changes the schema of the resulting file.

Wide tables are where the columnar layout earns most. An expression matrix with a row per gene and a column per sample, or a feature table with a column per measurement, is stored one column after another rather than one row after another — so a query naming four columns reads four columns’ worth of bytes and never touches the rest.

Against a tab-separated file the same query has to read every byte of every row to find the fields it wants, because the only way to locate the seventeenth field of a line is to count seventeen tabs. That difference does not diminish as the table grows; it widens. It is also why the footer matters on a wide table more than on a narrow one: listing two thousand column names and types without reading any data is the difference between a schema you can inspect and a header row you have to `head -1 | tr` apart.

The writer emits INT32 only where every value in the column fits inside the 32-bit range. Beyond that the column becomes DOUBLE rather than INT64, which surprises people who know Parquet has a 64-bit integer type.

The reason is honesty about what arrived. Values pass through JavaScript numbers on the way in, which carry 53 bits of integer precision, so a genomic coordinate or an event identifier beyond that point has already been rounded before the writer sees it. Declaring it INT64 would promise an exactness the value no longer has, and an identifier that comes back off by one looks exactly like an identifier. If a column holds large identifiers rather than quantities, keep it as text in the source — a non-numeric prefix is enough — and it will arrive as a string column, intact.

The file footer holds the column names as the header row wrote them, the type of each, and the row count, and the data itself is written in row groups of between one thousand and one hundred thousand rows with per-column statistics attached. Each column is compressed on its own with Snappy, and a column whose values repeat is stored once as a dictionary with small integer references into it.

For an analysis table that last part is where the size goes. A chromosome column, a sample identifier, a strand, a category — each of them has a handful of distinct values repeated across millions of rows, and each collapses to almost nothing. A column of continuous measurements does not, and it is the one that will dominate the resulting file. Knowing which of your columns are which is a good predictor of the size you will get.

DuckDB reads the file in place: a FROM clause naming the path is enough, no import step and no table definition. `DESCRIBE SELECT * FROM "table.parquet"` prints the inferred schema, which is the first thing to look at and the fastest way to find a column that came through as text.

pandas reads it in one call and Spark treats it as a native table format. In all three, the correct move after a surprise is to query the offending values rather than to cast blindly — a cast will null out whatever does not fit, which is precisely the failure the writer refused to commit on your behalf. The file starts and ends with the four bytes PAR1, which is how each of them recognises it before reading the footer.

Both halves run on this page: the tab-separated parser is plain JavaScript and the Parquet writer is a library loaded on demand. Nothing is uploaded, which matters for an unpublished result set, a table under embargo, or measurements that are not yours to distribute.

The whole table is held in memory while it is transposed into columns, so tens of megabytes converts without ceremony and hundreds is where a browser tab begins to strain. A multi-gigabyte matrix belongs in DuckDB, which will read the tab-separated file from disk and write Parquet in a single statement without holding it. Saying so is more useful than letting a very large conversion fail two thirds of the way through.

Keep it if a person or a script has to read it as text. Parquet is binary, is not editable, and the registry records its support as patchy — a collaborator without the right tooling cannot open it at all, and "install DuckDB first" is a poor reply to somebody who wanted to look at a table.

Keep it too if the file is a published artefact. Tab-separated tables are what journals, repositories and public datasets accept, and they will still be readable with no dependency in twenty years. Convert to Parquet for the working copy — the one you filter, join and aggregate repeatedly — and keep the original as the thing of record.