Python Delete Datasets

Table.delete() deletes the datasets that match a filter. One call can delete one dataset, many datasets or all datasets, in one commit.

Example files and table for this page

The examples use small NetCDF files of CTD profiles, see Getting started. Run this block first. It writes the files and makes the table profiles.lance from the six files in profiles/.

from pathlib import Path

import numpy as np
import pandas as pd
import xarray as xr


def write_profile(path, station, cruise, lat, lon, levels, time, temp_type="float64", extra=None):
    """Write one CTD profile as a NetCDF file."""
    depth = np.arange(levels, dtype="float64") * 10.0
    ds = xr.Dataset(
        {
            "TEMP": ("depth", (15.0 - depth / 100.0).astype(temp_type), {"units": "degree_Celsius"}),
            "PSAL": ("depth", 35.0 + depth / 1000.0, {"units": "psu"}),
            "LATITUDE": ((), lat),
            "LONGITUDE": ((), lon),
            "TIME": ((), pd.Timestamp(time)),
        },
        coords={"depth": ("depth", depth, {"units": "m"})},
        attrs={"station": station, "cruise": cruise},
    )
    if extra:
        ds["DOXY"] = ("depth", np.full(levels, 250.0), {"units": "umol/kg"})
    Path(path).parent.mkdir(exist_ok=True)
    ds.to_netcdf(path)


# Six profiles of two cruises.
write_profile("profiles/A1.nc", "A1", "cruise-1", 52.5, 4.0, 50, "2026-06-01")
write_profile("profiles/A2.nc", "A2", "cruise-1", 53.0, 4.5, 80, "2026-06-02")
write_profile("profiles/A3.nc", "A3", "cruise-1", 60.0, 2.0, 120, "2026-06-03")
write_profile("profiles/B1.nc", "B1", "cruise-2", 54.0, 3.0, 60, "2026-07-01")
write_profile("profiles/B2.nc", "B2", "cruise-2", 55.5, 3.5, 70, "2026-07-02")
write_profile("profiles/B3.nc", "B3", "cruise-2", 57.0, 5.0, 90, "2026-07-03")
# Three new profiles of a third cruise.
write_profile("new/C1.nc", "C1", "cruise-3", 58.0, 6.0, 40, "2026-08-01")
write_profile("new/C2.nc", "C2", "cruise-3", 58.5, 6.5, 45, "2026-08-02")
write_profile("new/C3.nc", "C3", "cruise-3", 59.0, 7.0, 50, "2026-08-03")
# Corrected profiles, with other depth levels.
write_profile("corrected/A1.nc", "A1", "cruise-1", 52.5, 4.0, 55, "2026-06-01")
write_profile("corrected/A2.nc", "A2", "cruise-1", 53.0, 4.5, 90, "2026-06-02")
write_profile("corrected/B1.nc", "B1", "cruise-2", 54.0, 3.0, 65, "2026-07-01")
write_profile("corrected/B2.nc", "B2", "cruise-2", 55.5, 3.5, 75, "2026-07-02")
# Files that differ from the others.
write_profile("other/int_temp.nc", "D1", "cruise-4", 50.0, 1.0, 10, "2026-09-01", temp_type="int32")
write_profile("other/with_doxy.nc", "E1", "cruise-5", 51.0, 2.0, 20, "2026-10-01", extra=True)
write_profile("other/float32_temp.nc", "F1", "cruise-6", 52.0, 2.0, 10, "2026-11-01", temp_type="float32")

import lance_nd


def stations(table):
    """The station of each dataset in the table, in table order."""
    return [batch.to_xarray().attrs["station"] for batch in table.scan(project=[])]


with lance_nd.create("profiles.lance", mode="overwrite", stats=True) as writer:
    with xr.open_dataset("profiles/A1.nc") as ds:
        writer.write(ds)
    with xr.open_dataset("profiles/A2.nc") as ds:
        writer.write(ds)
    with xr.open_dataset("profiles/A3.nc") as ds:
        writer.write(ds)
    with xr.open_dataset("profiles/B1.nc") as ds:
        writer.write(ds)
    with xr.open_dataset("profiles/B2.nc") as ds:
        writer.write(ds)
    with xr.open_dataset("profiles/B3.nc") as ds:
        writer.write(ds)
table = lance_nd.open("profiles.lance")

Delete one dataset

deleted = table.delete("__global_attrs__.station = 'A2'")
print(deleted, table.version, table.count_rows())
print(stations(table))

1 2 5
['A1', 'A3', 'B1', 'B2', 'B3']

• delete() returns the number of deleted datasets.
• delete() commits a new version at once. table changes to the new version.
• The filter is the same Lance SQL as the filter of a scan, see Read datasets.

Delete many datasets

One filter can match many datasets. They are all deleted in one commit.

A list of values with IN:

deleted = table.delete("__global_attrs__.station IN ('A1', 'B2')")
print(deleted, stations(table))

2 ['A3', 'B1', 'B3']

All datasets of a group, here a cruise:

table.restore(1)
deleted = table.delete("__global_attrs__.cruise = 'cruise-2'")
print(deleted, stations(table))

3 ['A1', 'A2', 'A3']

table.restore(1) brings back the six datasets of version 1, see Versions. The next examples start with it too.

A pattern with LIKE. % matches any text:

table.restore(1)
print(table.delete("__global_attrs__.station LIKE 'B%'"), stations(table))

3 ['A1', 'A2', 'A3']

Two conditions with AND, OR and NOT:

table.restore(1)
f = "__global_attrs__.cruise = 'cruise-1' AND NOT __global_attrs__.station = 'A2'"
print(table.delete(f), stations(table))

2 ['A2', 'B1', 'B2', 'B3']

By value, here all profiles north of 55°N:

table.restore(1)
print(table.delete("LATITUDE.values[1] > 55"), stations(table))

3 ['A1', 'A2', 'B1']

By time:

table.restore(1)
print(table.delete("TIME.values[1] < TIMESTAMP '2026-07-01'"), stations(table))

3 ['B1', 'B2', 'B3']

By size, here all profiles with more than 80 depth levels:

table.restore(1)
print(table.delete("TEMP.dim_sizes[1] > 80"), stations(table))

2 ['A1', 'A2', 'B1', 'B2']

All datasets:

table.restore(1)
print(table.delete("true"), table.count_rows())

6 0

The table stays, with its variables and its earlier versions.

Delete a list from Python

Build an IN filter from a Python list. Double each single quote in the values.

def quoted(values):
    return ", ".join("'" + str(v).replace("'", "''") + "'" for v in values)


table.restore(1)
remove = ["A1", "A3", "B2"]
print(table.delete(f"__global_attrs__.station IN ({quoted(remove)})"), stations(table))

3 ['A2', 'B1', 'B3']

A filter with IN and many values is one commit, so it is much faster than one delete() for each value.

Check before you delete

A scan with the same filter shows what a delete will remove:

table.restore(1)
f = "__global_attrs__.cruise = 'cruise-1'"
print([b.to_xarray().attrs["station"] for b in table.scan(project=[], filter=f)])
print(table.delete(f))

['A1', 'A2', 'A3']
3

What a delete does

• The filter must match exactly. A delete does not use the stats, because prune keeps datasets that do not match.
• A filter that matches nothing deletes nothing, but it still makes a new version.
• A delete only marks the rows as deleted. The files on disk keep their size. Compact rewrites the files without the deleted rows, and cleanup frees the disk space.
• The deleted datasets stay in the earlier versions. You can read them with checkout() and get them back with restore(), see Versions.
• To change a dataset, delete it and add the new dataset, see Replace datasets.