Skip to content

Tutorial 3: Write your own script to reshape a NAHPU Data Package

Tutorial 2 converts a NAHPU Data Package with a ready-made script. When a museum, a lab, or an analysis needs a different layout, write your own. This tutorial builds one from scratch, step by step, in Python or R.

The example output is a simple field catalog, one row per specimen:

field_idspeciessexcountrylocalitycollection_dateparts
HH618Crocidura sp.MaleIndonesiaDesa Insil; Mt. Ambang; Danau Aliyah2016-02-17Skin; Skull; Skeleton; Tissue; Intestine; Embryo
HH650Maxomys musschenbroekiiMaleIndonesiaDesa Insil; Mt. Ambang; Danau Aliyah2016-02-19Tissue

The same steps produce any other layout: change which tables you join and which columns you write.

You need the lsumz-mammal-specify-frictionless repository and uv, pip, or R, installed as in Tutorial 2.

The finished scripts are in the repository as python/examples/field_catalog.py and R/examples/field_catalog.R. Both write the same file, results/ambang-shrews-field-catalog.csv, from the bundled example package. Create your own file next to them, such as python/examples/my_catalog.py or R/examples/my_catalog.R, and follow along.

This tutorial uses one more Python package than Tutorial 2: pandas, for joining and reshaping tables.

From the python/ folder, add the examples dependency group:

Terminal window
cd python
uv sync --group examples

Check that pandas is available:

Terminal window
uv run --group examples python -c "import pandas; print(pandas.__version__)"

A NAHPU Data Package stores each NAHPU database table as a CSV file under tables/, described in datapackage.json. The tables keep NAHPU's own column names, and they link to each other through ID columns. These are the links a specimen-based script usually follows:

FromColumnTo
specimenspeciesIDtaxonomy.id
specimenuuidmammalAttribute.specimenUuid (one row per specimen)
specimenuuidspecimenPart.specimenUuid (one row per part)
specimencatalogerIDpersonnel.uuid
specimencollEventIDcollEvent.id
specimencoordinateIDcoordinate.id
collEventsiteIDsite.id
sitegeographyIdgeography.id

In datapackage.json, resource names are lowercase: the mammalAttribute table is the mammalattribute resource, and collEvent is collevent. Bird, herpetofauna, and other attribute tables follow the same pattern.

Coded values, such as sex, are stored as numbers. The package explains them in the sqlite-enum-mappings resource (mappings/sqlite_enums.csv).

Extract the archive, open datapackage.json, and list its resources.

from pathlib import Path
import zipfile
import pandas as pd
from frictionless import Package
ROOT = Path(__file__).resolve().parents[2]
ARCHIVE = ROOT / "data" / "example" / "ambang-shrews.nahpu-dp.zip"
EXTRACTED = ROOT / "data" / "extracted" / "ambang-shrews"
with zipfile.ZipFile(ARCHIVE) as archive:
archive.extractall(EXTRACTED)
package = Package(EXTRACTED / "datapackage.json")
print(package.resource_names)

For a .tar.gz package, use tarfile.open(ARCHIVE).extractall(EXTRACTED).

To see a table's columns, primary key, and links, read its schema:

schema = package.get_resource("specimen").schema
print(schema.field_names)
print(schema.foreign_keys)

NAHPU leaves empty tables out of the package. A project with no specimen parts has no specimenpart resource at all, so read tables through a small helper that returns an empty table instead of failing.

def read_table(name: str) -> pd.DataFrame:
if not package.has_resource(name):
return pd.DataFrame()
rows = package.get_resource(name).read_rows()
return pd.DataFrame([row.to_dict() for row in rows])
specimens = read_table("specimen")
taxonomy = read_table("taxonomy")
attributes = read_table("mammalattribute")
personnel = read_table("personnel")
events = read_table("collevent")
sites = read_table("site")
geography = read_table("geography")
parts = read_table("specimenpart")
enums = read_table("sqlite-enum-mappings")

Frictionless reads each column with the type from the schema: field numbers arrive as integers, measurements as numbers, and empty cells as missing values.

4. Keep the specimens you need and join their details

Section titled “4. Keep the specimens you need and join their details”

Filter on taxonGroup first, then follow the links from step 1. Left joins keep every specimen even when a related record is missing.

specimens = specimens[specimens["taxonGroup"].isin(["Mammals", "General Mammals", "Bats"])]
catalog = (
specimens.merge(taxonomy, left_on="speciesID", right_on="id", how="left")
.merge(attributes, left_on="uuid", right_on="specimenUuid", how="left")
.merge(
personnel[["uuid", "initial"]].rename(columns={"uuid": "catalogerID"}),
on="catalogerID",
how="left",
)
.merge(
events[["id", "siteID"]].rename(columns={"id": "collEventID"}),
on="collEventID",
how="left",
)
.merge(
sites[["id", "geographyId"]].rename(columns={"id": "siteID"}),
on="siteID",
how="left",
)
.merge(
geography.rename(columns={"id": "geographyId"}),
on="geographyId",
how="left",
)
)

Select only the columns you need from tables that share column names, such as id or remark, so the joined table stays readable.

Look up the code's label in sqlite-enum-mappings by table and column. The same number can mean different things in different tables, so always filter on both.

sex = enums[(enums["table"] == "mammalAttribute") & (enums["column"] == "sex")]
sex_labels = dict(zip(sex["sqlite_index"], sex["display_name"]))
catalog["sex_label"] = catalog["sex"].map(sex_labels)

Reproductive and age fields work the same way. User-defined lists, such as part types and treatments, are stored as text and need no decoding; the vocabularies/ resources list the values the project allowed.

A specimen has many parts. Choose one of three shapes:

  • One cell, used here: join the part types with ; .
  • Numbered columns, such as PrepType1, PrepType2: number the parts per specimen and pivot them wide. Tutorial 2's converter does this; see R/nahpu_to_specify.R, which uses pivot_wider().
  • One row per part: join specimens onto specimenpart instead of the other way round.
part_list = (
parts.sort_values("id")
.groupby("specimenUuid")["type"]
.agg("; ".join)
.rename("parts")
)
catalog = catalog.merge(part_list, left_on="uuid", right_index=True, how="left")

Sorting by id keeps the parts in the order they were entered in NAHPU.

Build the output columns, sort the rows, and write a CSV.

OUTPUT = ROOT / "results" / "ambang-shrews-field-catalog.csv"
catalog = catalog.sort_values(["initial", "fieldNumber"])
species = catalog["genus"] + " " + catalog["specificEpithet"].fillna("sp.")
output = pd.DataFrame(
{
"field_id": catalog["initial"] + catalog["fieldNumber"].astype("Int64").astype("string"),
"species": species,
"sex": catalog["sex_label"],
"country": catalog["country"],
"locality": catalog["locality"],
"collection_date": catalog["collectionDate"],
"parts": catalog["parts"],
}
)
OUTPUT.parent.mkdir(parents=True, exist_ok=True)
output.to_csv(OUTPUT, index=False)

astype("Int64") keeps field numbers whole even when some are missing, so 618 does not become 618.0.

Sort on the numeric fieldNumber, not the text field_id, so HH1000 comes after HH999.

Run the script:

From the python/ folder:

Terminal window
uv run --group examples python examples/my_catalog.py

Open results/ambang-shrews-field-catalog.csv. It should list the five example specimens shown at the top of this page.

Before you trust a script with real data:

  • Validate the package. The Python framework installs a command-line tool. With uv, from the python/ folder:

    Terminal window
    uv run frictionless validate ../data/extracted/ambang-shrews/datapackage.json

    With pip, from the repository folder with .venv activated:

    Terminal window
    frictionless validate data/extracted/ambang-shrews/datapackage.json
  • Compare row counts. The output should have as many rows as the specimens you meant to keep.

  • Open a few rows next to the same records in NAHPU, including a specimen with several parts and one with missing values.

  • Keep a small expected file, as the repository does in data/example/expected-specify.csv, and compare against it whenever you change the script.

  • Other taxa: swap mammalattribute for birdattribute, herpattribute, or another attribute table, and change the taxonGroup filter.
  • Coordinates: join coordinate through specimen.coordinateID. When it is empty, NAHPU uses the event site's coordinate if the site has exactly one; see how python/nahpu_to_specify.py does this.
  • Dates: NAHPU stores dates as yyyy-mm-dd. Reformat them with pd.to_datetime(...).dt.strftime(...) in Python or lubridate::ymd() and format() in R.
  • Everything else: nahpu-project.json holds the full project, including values with no table column, for cases the CSV tables do not cover.