{ "cells": [ { "cell_type": "markdown", "id": "42a5b888", "metadata": {}, "source": [ "# Exporting to Arrow\n", "\n", "Reading a table into NumPy is the right default, but NumPy has no type for\n", "three things an H5Col table can hold:\n", "\n", "- a value that is genuinely absent, rather than a particular number standing in\n", " for absence;\n", "- a categorical column as what it really is, a small set of labels plus one code\n", " per row, instead of a full label repeated for every row;\n", "- a list column, whose rows hold different numbers of values, with its own\n", " missing values at every level of nesting.\n", "\n", "Apache Arrow has all three. `to_arrow()` is the export that keeps them, and it\n", "is also the doorway to pandas, Polars, DuckDB and Parquet.\n", "\n", "This notebook needs the optional dependency: `pip install h5col[arrow]`." ] }, { "cell_type": "code", "execution_count": 1, "id": "3a17514c", "metadata": { "execution": { "iopub.execute_input": "2026-08-10T17:27:27.253163Z", "iopub.status.busy": "2026-08-10T17:27:27.252904Z", "iopub.status.idle": "2026-08-10T17:27:27.675993Z", "shell.execute_reply": "2026-08-10T17:27:27.675544Z" } }, "outputs": [], "source": [ "import tempfile\n", "from pathlib import Path\n", "\n", "import h5py\n", "import pyarrow as pa\n", "import pyarrow.parquet as pq\n", "\n", "from h5col import (\n", " ColumnSpec,\n", " FixedString,\n", " LeafValuesSpec,\n", " ListColumnSpec,\n", " Table,\n", " field,\n", ")" ] }, { "cell_type": "markdown", "id": "bb775934", "metadata": {}, "source": [ "## A table with something of everything\n", "\n", "Weather observations again: a string identifier, a temperature that is\n", "sometimes missing, a categorical station kind, a flag, and a variable number of\n", "raw samples per row." ] }, { "cell_type": "code", "execution_count": 2, "id": "6f2688b9", "metadata": { "execution": { "iopub.execute_input": "2026-08-10T17:27:27.677396Z", "iopub.status.busy": "2026-08-10T17:27:27.677281Z", "iopub.status.idle": "2026-08-10T17:27:27.702795Z", "shell.execute_reply": "2026-08-10T17:27:27.702470Z" } }, "outputs": [ { "data": { "text/plain": [ "" ] }, "execution_count": 2, "metadata": {}, "output_type": "execute_result" } ], "source": [ "tmp = Path(tempfile.mkdtemp())\n", "store = h5py.File(tmp / \"observations.h5\", \"w\")\n", "\n", "obs = Table.create(\n", " store.create_group(\"obs\"),\n", " [\n", " ColumnSpec(name=\"station\", dtype=FixedString(nbytes=8), description=\"ICAO id\"),\n", " ColumnSpec(\n", " name=\"t_air\",\n", " dtype=\"float32\",\n", " fill_value=-999.0,\n", " units=\"degC\",\n", " valid_min=-80.0,\n", " valid_max=60.0,\n", " ),\n", " ColumnSpec(name=\"kind\", categories=[\"manned\", \"automatic\"]),\n", " ColumnSpec(name=\"checked\", dtype=\"bool\"),\n", " ListColumnSpec(\n", " name=\"samples\",\n", " values=LeafValuesSpec(dtype=\"float64\"),\n", " nullable=True,\n", " units=\"degC\",\n", " ),\n", " ],\n", " title=\"Surface observations\",\n", ")\n", "\n", "obs.append(\n", " {\n", " \"station\": [\"KBOS\", \"KJFK\", \"KLGA\", \"KDCA\"],\n", " \"t_air\": [21.5, None, 23.1, 19.8],\n", " \"kind\": [\"manned\", \"automatic\", None, \"automatic\"],\n", " \"checked\": [True, True, False, True],\n", " \"samples\": [[21.4, 21.6], None, [23.0, 23.2, 23.1], []],\n", " }\n", ")\n", "obs" ] }, { "cell_type": "markdown", "id": "75a2fff5", "metadata": {}, "source": [ "## The export\n", "\n", "One call. Note what each column became." ] }, { "cell_type": "code", "execution_count": 3, "id": "d3a59f54", "metadata": { "execution": { "iopub.execute_input": "2026-08-10T17:27:27.703932Z", "iopub.status.busy": "2026-08-10T17:27:27.703875Z", "iopub.status.idle": "2026-08-10T17:27:28.171157Z", "shell.execute_reply": "2026-08-10T17:27:28.170805Z" } }, "outputs": [ { "data": { "text/plain": [ "station: large_string\n", " -- field metadata --\n", " h5col.description: 'ICAO id'\n", "t_air: float\n", " -- field metadata --\n", " h5col.units: 'degC'\n", " h5col.valid_min: '-80.0'\n", " h5col.valid_max: '60.0'\n", "kind: dictionary\n", "checked: bool\n", "samples: large_list\n", " child 0, item: double\n", " -- field metadata --\n", " h5col.units: 'degC'" ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "arrow_table = obs.to_arrow()\n", "arrow_table.schema" ] }, { "cell_type": "markdown", "id": "01aa595e", "metadata": {}, "source": [ "`station` is a string, `kind` is a *dictionary* of two labels with one code per\n", "row, and `samples` is a list of doubles. Nothing was flattened or expanded on\n", "the way out." ] }, { "cell_type": "code", "execution_count": 4, "id": "00f22564", "metadata": { "execution": { "iopub.execute_input": "2026-08-10T17:27:28.172265Z", "iopub.status.busy": "2026-08-10T17:27:28.172162Z", "iopub.status.idle": "2026-08-10T17:27:28.175937Z", "shell.execute_reply": "2026-08-10T17:27:28.175652Z" } }, "outputs": [ { "data": { "text/plain": [ "[{'station': 'KBOS',\n", " 't_air': 21.5,\n", " 'kind': 'manned',\n", " 'checked': True,\n", " 'samples': [21.4, 21.6]},\n", " {'station': 'KJFK',\n", " 't_air': None,\n", " 'kind': 'automatic',\n", " 'checked': True,\n", " 'samples': None},\n", " {'station': 'KLGA',\n", " 't_air': 23.100000381469727,\n", " 'kind': None,\n", " 'checked': False,\n", " 'samples': [23.0, 23.2, 23.1]},\n", " {'station': 'KDCA',\n", " 't_air': 19.799999237060547,\n", " 'kind': 'automatic',\n", " 'checked': True,\n", " 'samples': []}]" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "arrow_table.to_pylist()" ] }, { "cell_type": "markdown", "id": "093c2564", "metadata": {}, "source": [ "## Missing values are real nulls\n", "\n", "This is the part that NumPy cannot do. In the file, a missing `t_air` is stored\n", "as `-999`. Read into NumPy without a mask, that number is indistinguishable\n", "from a measurement:" ] }, { "cell_type": "code", "execution_count": 5, "id": "bfdddfc4", "metadata": { "execution": { "iopub.execute_input": "2026-08-10T17:27:28.176874Z", "iopub.status.busy": "2026-08-10T17:27:28.176811Z", "iopub.status.idle": "2026-08-10T17:27:28.179490Z", "shell.execute_reply": "2026-08-10T17:27:28.179272Z" } }, "outputs": [ { "data": { "text/plain": [ "[21.5, -999.0, 23.100000381469727, 19.799999237060547]" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "obs[\"t_air\"].read(masked=False).tolist()" ] }, { "cell_type": "markdown", "id": "eb390a95", "metadata": {}, "source": [ "In Arrow it is `null`, and the count is exact:" ] }, { "cell_type": "code", "execution_count": 6, "id": "9bd6cead", "metadata": { "execution": { "iopub.execute_input": "2026-08-10T17:27:28.180603Z", "iopub.status.busy": "2026-08-10T17:27:28.180549Z", "iopub.status.idle": "2026-08-10T17:27:28.182453Z", "shell.execute_reply": "2026-08-10T17:27:28.182168Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "[21.5, None, 23.100000381469727, 19.799999237060547]\n", "nulls per column: {'station': 0, 't_air': 1, 'kind': 1, 'checked': 0, 'samples': 1}\n" ] } ], "source": [ "print(arrow_table[\"t_air\"].to_pylist())\n", "print(\"nulls per column:\",\n", " {n: arrow_table[n].null_count for n in arrow_table.column_names})" ] }, { "cell_type": "markdown", "id": "e534aead", "metadata": {}, "source": [ "The `samples` column shows the distinction a list column cares about: row 1 is\n", "`null`, meaning no value at all, while row 3 is `[]`, a value that happens to be\n", "an empty list. Both survive the export." ] }, { "cell_type": "code", "execution_count": 7, "id": "caf7a306", "metadata": { "execution": { "iopub.execute_input": "2026-08-10T17:27:28.183383Z", "iopub.status.busy": "2026-08-10T17:27:28.183327Z", "iopub.status.idle": "2026-08-10T17:27:28.184990Z", "shell.execute_reply": "2026-08-10T17:27:28.184687Z" } }, "outputs": [ { "data": { "text/plain": [ "[[21.4, 21.6], None, [23.0, 23.2, 23.1], []]" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "arrow_table[\"samples\"].to_pylist()" ] }, { "cell_type": "markdown", "id": "0bdb831a", "metadata": {}, "source": [ "## Column attributes travel with the data\n", "\n", "`units`, `description` and the valid range are stored as HDF5 attributes on\n", "each column. They come across as Arrow field metadata, under names beginning\n", "`h5col.`, so a table exported this way does not lose the information that made\n", "it interpretable." ] }, { "cell_type": "code", "execution_count": 8, "id": "acd0fa11", "metadata": { "execution": { "iopub.execute_input": "2026-08-10T17:27:28.185873Z", "iopub.status.busy": "2026-08-10T17:27:28.185819Z", "iopub.status.idle": "2026-08-10T17:27:28.187501Z", "shell.execute_reply": "2026-08-10T17:27:28.187195Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "t_air {'h5col.units': 'degC', 'h5col.valid_min': '-80.0', 'h5col.valid_max': '60.0'}\n", "station {'h5col.description': 'ICAO id'}\n", "samples {'h5col.units': 'degC'}\n" ] } ], "source": [ "for name in (\"t_air\", \"station\", \"samples\"):\n", " meta = arrow_table.schema.field(name).metadata or {}\n", " print(name, {k.decode(): v.decode() for k, v in meta.items()})" ] }, { "cell_type": "markdown", "id": "1ea697ea", "metadata": {}, "source": [ "## Onward to pandas" ] }, { "cell_type": "code", "execution_count": 9, "id": "7d812552", "metadata": { "execution": { "iopub.execute_input": "2026-08-10T17:27:28.188346Z", "iopub.status.busy": "2026-08-10T17:27:28.188282Z", "iopub.status.idle": "2026-08-10T17:27:28.226030Z", "shell.execute_reply": "2026-08-10T17:27:28.225744Z" } }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
stationt_airkindcheckedsamples
0KBOS21.500000mannedTrue[21.4, 21.6]
1KJFKNaNautomaticTrueNone
2KLGA23.100000NaNFalse[23.0, 23.2, 23.1]
3KDCA19.799999automaticTrue[]
\n", "
" ], "text/plain": [ " station t_air kind checked samples\n", "0 KBOS 21.500000 manned True [21.4, 21.6]\n", "1 KJFK NaN automatic True None\n", "2 KLGA 23.100000 NaN False [23.0, 23.2, 23.1]\n", "3 KDCA 19.799999 automatic True []" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "arrow_table.to_pandas()" ] }, { "cell_type": "markdown", "id": "729fbba4", "metadata": {}, "source": [ "Missing values arrive as `NaN`/`None` rather than `-999`, so an average is the\n", "average of the measurements that exist:" ] }, { "cell_type": "code", "execution_count": 10, "id": "1d51ce10", "metadata": { "execution": { "iopub.execute_input": "2026-08-10T17:27:28.226949Z", "iopub.status.busy": "2026-08-10T17:27:28.226894Z", "iopub.status.idle": "2026-08-10T17:27:28.230334Z", "shell.execute_reply": "2026-08-10T17:27:28.230002Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "mean t_air from Arrow : 21.466665\n", "mean of the stored numbers : -233.65001\n" ] } ], "source": [ "df = arrow_table.to_pandas()\n", "print(\"mean t_air from Arrow :\", df[\"t_air\"].mean())\n", "print(\"mean of the stored numbers :\", obs[\"t_air\"].read(masked=False).mean())" ] }, { "cell_type": "markdown", "id": "c2da0bc8", "metadata": {}, "source": [ "## Onward to Parquet\n", "\n", "The metadata survives the round trip, which is what makes it worth carrying." ] }, { "cell_type": "code", "execution_count": 11, "id": "44551cb5", "metadata": { "execution": { "iopub.execute_input": "2026-08-10T17:27:28.231188Z", "iopub.status.busy": "2026-08-10T17:27:28.231124Z", "iopub.status.idle": "2026-08-10T17:27:28.312806Z", "shell.execute_reply": "2026-08-10T17:27:28.312414Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "{b'h5col.units': b'degC', b'h5col.valid_min': b'-80.0', b'h5col.valid_max': b'60.0'}\n", "values identical: True\n" ] } ], "source": [ "pq.write_table(arrow_table, tmp / \"observations.parquet\")\n", "back = pq.read_table(tmp / \"observations.parquet\")\n", "\n", "print(back.schema.field(\"t_air\").metadata)\n", "print(\"values identical:\", back.to_pylist() == arrow_table.to_pylist())" ] }, { "cell_type": "markdown", "id": "8659aee7", "metadata": {}, "source": [ "## Exporting only the rows you want\n", "\n", "`to_arrow()` accepts a query the same way `read()` does." ] }, { "cell_type": "code", "execution_count": 12, "id": "571fc65c", "metadata": { "execution": { "iopub.execute_input": "2026-08-10T17:27:28.313794Z", "iopub.status.busy": "2026-08-10T17:27:28.313725Z", "iopub.status.idle": "2026-08-10T17:27:28.320492Z", "shell.execute_reply": "2026-08-10T17:27:28.320151Z" } }, "outputs": [ { "data": { "text/plain": [ "[{'station': 'KBOS', 't_air': 21.5},\n", " {'station': 'KLGA', 't_air': 23.100000381469727}]" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "obs.to_arrow([\"station\", \"t_air\"], where=field(\"t_air\") > 20.0).to_pylist()" ] }, { "cell_type": "markdown", "id": "c9efc2cf", "metadata": {}, "source": [ "## A note on speed\n", "\n", "For list columns the export is not only more faithful but considerably quicker.\n", "H5Col stores a list column as an offsets array plus a values buffer, which is\n", "almost exactly how Arrow lays one out, so most of the export hands the same\n", "blocks of memory across rather than rebuilding them. Reading the same column\n", "into Python lists has to construct every row as an object.\n", "\n", "On a column of 200,000 rows the difference is roughly twenty to thirty times,\n", "and it widens with nesting." ] }, { "cell_type": "code", "execution_count": 13, "id": "c1f4580b", "metadata": { "execution": { "iopub.execute_input": "2026-08-10T17:27:28.321436Z", "iopub.status.busy": "2026-08-10T17:27:28.321381Z", "iopub.status.idle": "2026-08-10T17:27:28.327275Z", "shell.execute_reply": "2026-08-10T17:27:28.327016Z" } }, "outputs": [], "source": [ "store.close()" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.14.6" } }, "nbformat": 4, "nbformat_minor": 5 }