Exporting to Arrow#

Reading a table into NumPy is the right default, but NumPy has no type for three things an H5Col table can hold:

  • a value that is genuinely absent, rather than a particular number standing in for absence;

  • a categorical column as what it really is, a small set of labels plus one code per row, instead of a full label repeated for every row;

  • a list column, whose rows hold different numbers of values, with its own missing values at every level of nesting.

Apache Arrow has all three. to_arrow() is the export that keeps them, and it is also the doorway to pandas, Polars, DuckDB and Parquet.

This notebook needs the optional dependency: pip install h5col[arrow].

import tempfile
from pathlib import Path

import h5py
import pyarrow as pa
import pyarrow.parquet as pq

from h5col import (
    ColumnSpec,
    FixedString,
    LeafValuesSpec,
    ListColumnSpec,
    Table,
    field,
)

A table with something of everything#

Weather observations again: a string identifier, a temperature that is sometimes missing, a categorical station kind, a flag, and a variable number of raw samples per row.

tmp = Path(tempfile.mkdtemp())
store = h5py.File(tmp / "observations.h5", "w")

obs = Table.create(
    store.create_group("obs"),
    [
        ColumnSpec(name="station", dtype=FixedString(nbytes=8), description="ICAO id"),
        ColumnSpec(
            name="t_air",
            dtype="float32",
            fill_value=-999.0,
            units="degC",
            valid_min=-80.0,
            valid_max=60.0,
        ),
        ColumnSpec(name="kind", categories=["manned", "automatic"]),
        ColumnSpec(name="checked", dtype="bool"),
        ListColumnSpec(
            name="samples",
            values=LeafValuesSpec(dtype="float64"),
            nullable=True,
            units="degC",
        ),
    ],
    title="Surface observations",
)

obs.append(
    {
        "station": ["KBOS", "KJFK", "KLGA", "KDCA"],
        "t_air": [21.5, None, 23.1, 19.8],
        "kind": ["manned", "automatic", None, "automatic"],
        "checked": [True, True, False, True],
        "samples": [[21.4, 21.6], None, [23.0, 23.2, 23.1], []],
    }
)
obs

<h5col.Table '/obs' nrows=4>

The export#

One call. Note what each column became.

arrow_table = obs.to_arrow()
arrow_table.schema

station: large_string
  -- field metadata --
  h5col.description: 'ICAO id'
t_air: float
  -- field metadata --
  h5col.units: 'degC'
  h5col.valid_min: '-80.0'
  h5col.valid_max: '60.0'
kind: dictionary<values=string, indices=int8, ordered=0>
checked: bool
samples: large_list<item: double>
  child 0, item: double
  -- field metadata --
  h5col.units: 'degC'

station is a string, kind is a dictionary of two labels with one code per row, and samples is a list of doubles. Nothing was flattened or expanded on the way out.

arrow_table.to_pylist()

[{'station': 'KBOS',
  't_air': 21.5,
  'kind': 'manned',
  'checked': True,
  'samples': [21.4, 21.6]},
 {'station': 'KJFK',
  't_air': None,
  'kind': 'automatic',
  'checked': True,
  'samples': None},
 {'station': 'KLGA',
  't_air': 23.100000381469727,
  'kind': None,
  'checked': False,
  'samples': [23.0, 23.2, 23.1]},
 {'station': 'KDCA',
  't_air': 19.799999237060547,
  'kind': 'automatic',
  'checked': True,
  'samples': []}]

Missing values are real nulls#

This is the part that NumPy cannot do. In the file, a missing t_air is stored as -999. Read into NumPy without a mask, that number is indistinguishable from a measurement:

obs["t_air"].read(masked=False).tolist()

[21.5, -999.0, 23.100000381469727, 19.799999237060547]

In Arrow it is null, and the count is exact:

print(arrow_table["t_air"].to_pylist())
print("nulls per column:",
      {n: arrow_table[n].null_count for n in arrow_table.column_names})

[21.5, None, 23.100000381469727, 19.799999237060547]
nulls per column: {'station': 0, 't_air': 1, 'kind': 1, 'checked': 0, 'samples': 1}

The samples column shows the distinction a list column cares about: row 1 is null, meaning no value at all, while row 3 is [], a value that happens to be an empty list. Both survive the export.

arrow_table["samples"].to_pylist()

[[21.4, 21.6], None, [23.0, 23.2, 23.1], []]

Column attributes travel with the data#

units, description and the valid range are stored as HDF5 attributes on each column. They come across as Arrow field metadata, under names beginning h5col., so a table exported this way does not lose the information that made it interpretable.

for name in ("t_air", "station", "samples"):
    meta = arrow_table.schema.field(name).metadata or {}
    print(name, {k.decode(): v.decode() for k, v in meta.items()})

t_air {'h5col.units': 'degC', 'h5col.valid_min': '-80.0', 'h5col.valid_max': '60.0'}
station {'h5col.description': 'ICAO id'}
samples {'h5col.units': 'degC'}

Onward to pandas#

arrow_table.to_pandas()
station t_air kind checked samples
0 KBOS 21.500000 manned True [21.4, 21.6]
1 KJFK NaN automatic True None
2 KLGA 23.100000 NaN False [23.0, 23.2, 23.1]
3 KDCA 19.799999 automatic True []

Missing values arrive as NaN/None rather than -999, so an average is the average of the measurements that exist:

df = arrow_table.to_pandas()
print("mean t_air from Arrow      :", df["t_air"].mean())
print("mean of the stored numbers :", obs["t_air"].read(masked=False).mean())

mean t_air from Arrow      : 21.466665
mean of the stored numbers : -233.65001

Onward to Parquet#

The metadata survives the round trip, which is what makes it worth carrying.

pq.write_table(arrow_table, tmp / "observations.parquet")
back = pq.read_table(tmp / "observations.parquet")

print(back.schema.field("t_air").metadata)
print("values identical:", back.to_pylist() == arrow_table.to_pylist())

{b'h5col.units': b'degC', b'h5col.valid_min': b'-80.0', b'h5col.valid_max': b'60.0'}
values identical: True

Exporting only the rows you want#

to_arrow() accepts a query the same way read() does.

obs.to_arrow(["station", "t_air"], where=field("t_air") > 20.0).to_pylist()

[{'station': 'KBOS', 't_air': 21.5},
 {'station': 'KLGA', 't_air': 23.100000381469727}]

A note on speed#

For list columns the export is not only more faithful but considerably quicker. H5Col stores a list column as an offsets array plus a values buffer, which is almost exactly how Arrow lays one out, so most of the export hands the same blocks of memory across rather than rebuilding them. Reading the same column into Python lists has to construct every row as an object.

On a column of 200,000 rows the difference is roughly twenty to thirty times, and it widens with nesting.

store.close()