calcofi4py ¶

CalCOFI Python helpers — the thin sibling of calcofi4r. Two data stores, a few verbs, no credentials in code.

— the version these examples were run against; calcofi4py.__version__ tells you yours.

pip install "calcofi4py[viz] @ git+https://github.com/CalCOFI/calcofi4py"

[viz] adds pandas + plotly, which every example below uses (the CTD helpers return DataFrames; the maps and profiles are plotly). Without it you get release access only (DuckDB). The extras go inside the quotes with the git URL — a bare pip install 'calcofi4py[viz]' only works after the package is already installed.

Updating and pinning. The package is not on PyPI, so pip install 'calcofi4py[viz]' on an existing install just reports Requirement already satisfied and changes nothing. Re-run the git URL — with --upgrade — to move to the latest main, or pin a release tag:

pip install --upgrade "calcofi4py[viz] @ git+https://github.com/CalCOFI/calcofi4py"           # latest main
pip install --upgrade "calcofi4py[viz] @ git+https://github.com/CalCOFI/calcofi4py@v0.3.6"    # a release tag
python -c "import calcofi4py as cc; print(cc.__version__)"                                    # confirm

In RStudio's Python console (rstudio.calcofi.io) the interpreter is embedded once per R session: after an upgrade do Session → Restart R, or import keeps returning the old module.

The public database releases (no account needed)¶

Immutable, versioned Parquet on a public bucket; DuckDB reads only what a query touches, straight over HTTPS.

import calcofi4py as cc
cc.__version__                             # '0.9.2' — confirm before copying the examples below

con = cc.cc_get_db()                       # latest release, every table as a view
con.sql("SHOW TABLES")
df = con.sql("""
  SELECT date_trunc('year', s.datetime) AS year, count(*) AS casts   -- sample.datetime is UTC
  FROM sample s WHERE s.dataset_key = 'calcofi_ctd-cast'
  GROUP BY 1 ORDER BY 1
""").df()

cc.cc_query("SELECT count(*) FROM obs").fetchone()      # one-shot
cc.cc_get_db("v2026.08.14")                             # pin a version (reproducible)
cc.cc_get_db(supplemental=True)                         # + obs_ctd_full (216M rows), obs_mets_full
cc.cc_list_versions()[:3]

# quality flags: obs.measurement_qual is each dataset's OWN code set (bottle/CTD 8 = suspect,
# 9 = missing/bad; DIC WOCE 3/4/9). A flagged value is still a row — filter it:
con.sql(f"SELECT * FROM obs o WHERE o.measurement_type = 'oxygen_ml_l' AND {cc.qual_ok_sql('o')}")

To see what's in each table first: the Schema explorer. More: Data Access.

The CTD team's PostgreSQL working database (account required)¶

Private, multi-user, reached over SSH — see Server Access for the account, the ~/.ssh/config alias calcofi, and the ~/.pgpass file (your password lives there and in no script, ever).

import calcofi4py as cc
cc.__version__                             # '0.9.2' — cc_withdraw_flags() needs >= 0.3.5

con = cc.cc_pg_connect(tunnel=True)        # opens `ssh -N calcofi` for you; ~/.pgpass auth
con.execute("SELECT count(*) FROM ctd.cast WHERE is_best_stage").fetchone()

casts = cc.cc_ctd_casts(con, "2304SH")                                  # one row per cast (best stage)
scans = cc.cc_ctd_scans(con, "2304SH", cast_id="2304_001d",
                        columns=["temp1", "temp2"])                     # this cast: 40 scans, 3–42 m, 1 m bins

# propose a QC flag — look first, write second, and you can still undo
where, args = "study = %s AND cast_id = %s AND depth = %s", ("2304SH", "2304_001d", 20)

hit = con.execute(f"SELECT scan_id, depth, temp1, temp2 FROM ctd.v_scan_best WHERE {where}", args).fetchall()
assert len(hit) == 1, hit             # [] = your WHERE matches nothing, and the INSERT below would flag nothing, silently

(flag_id,) = con.execute(f"""
  INSERT INTO ctd.flag (scan_id, variable, qual_code, reason)
  SELECT scan_id, 'temp1', 3, 'README example (withdrawn right after)'
  FROM ctd.v_scan_best WHERE {where}
  RETURNING flag_id
""", args).fetchone()
con.commit()                          # con.rollback() instead discards it before anyone sees it

cc.cc_flags(con, "2304SH", status="proposed")     # the ledger: who proposed what, and its fate

# undo: the ledger is append-only, so undo = withdraw your own proposal (the audit trail keeps it);
# curators accept/reject everything else in pgAdmin or SQL
cc.cc_withdraw_flags(con, [flag_id], note="README example")
cc.cc_pg_tunnel_close()

Both at once — release Parquet ⋈ PostgreSQL in one DuckDB query¶

The two stores answer different questions. The public release is what everyone sees: immutable, versioned Parquet with every CalCOFI dataset integrated on shared keys. The PostgreSQL database is the CTD team's working state: the raw cast archive and the flag ledger, changing daily as people propose and review. They share cruise_key (YYYY-MM-NODC, e.g. 2026-07-3322), so a question that spans them — which cruises are we still flagging, and what does the public release already publish for those cruises? — is one join away.

DuckDB is the bridge. cc_get_db() registers the release tables as views over the Parquet (called by their bare names: sample, cruise, …); cc_pg_attach() then ATTACHes PostgreSQL as a second catalog named pg (DuckDB's postgres extension talks libpq through your SSH tunnel and reads ~/.pgpass; read-only by default), so its tables are pg.<schema>.<table>. From there it is ordinary SQL — DuckDB plans across both sources and hands back one DataFrame.

import calcofi4py as cc

cc.cc_pg_tunnel()                                 # SSH tunnel up (cc_pg_connect(tunnel=True) does the same)
con = cc.cc_get_db(tables=["sample", "cruise"])   # DuckDB with the release tables as views
cc.cc_pg_attach(con)                              # + PostgreSQL attached as catalog `pg` (pg.ctd.*, pg.work.*)
con.sql("SHOW ALL TABLES")                        # both catalogs, side by side

qc_vs_release = con.sql("""
  WITH qc AS (                                    -- PostgreSQL side: the team's working state
    SELECT fi.cruise_key, fi.study,
           count(*) FILTER (WHERE f.status = 'proposed') AS flags_proposed,
           count(*) FILTER (WHERE f.status = 'accepted') AS flags_accepted
    FROM pg.ctd.flag f
    JOIN pg.ctd.file fi USING (file_id)           -- a flag sits on a scan/file; the file knows its cruise
    GROUP BY 1, 2)
  SELECT qc.study, qc.cruise_key, qc.flags_proposed, qc.flags_accepted,
         count(s.sample_key) AS casts_in_release  -- release side: what the public sees today
  FROM qc
  LEFT JOIN sample s                              -- `sample` = the release view (no prefix)
         ON s.cruise_key = qc.cruise_key AND s.dataset_key = 'calcofi_ctd-cast'
  GROUP BY ALL
  ORDER BY qc.cruise_key DESC
""").df()
#  study   cruise_key  flags_proposed  flags_accepted  casts_in_release
# 2607SH 2026-07-3322             574               0               122    <- being cleaned; already public
# 2304SH 2023-04-3322               0               0               226    <- its 3 flags were withdrawn

cc.cc_pg_tunnel_close()

Writes go the other way too: cc_pg_attach(con, read_only=False) lets a CREATE TABLE pg.work.my_subset AS SELECT … FROM sample WHERE … land release rows in your PostgreSQL schema for the team to work on.

Walkthrough¶

CTD QA/QC, end to end — the helpers working together on one cruise × one variable, read-only: casts → map → QC rules → triage → ledger → clean 1 m bins → a cross-store query, ending with the session info that makes the page a reproducible record. Pre-rendered by someone with an account (scripts/render_articles.sh); the site build holds no credentials.

Conventions inherited from calcofi4r (do not drift)¶

Secrets only in ~/.pgpass / %APPDATA%\postgresql\pgpass.conf; never a password argument in examples.
PGHOST / PGPORT / PGUSER override every default; on the CalCOFI server the host defaults to postgis (no tunnel), elsewhere localhost.
Release access follows catalog.json exactly: partitioned tables are s3-glob views with hive_partitioning, supplemental tables opt-in.

Dev¶

pip install -e ".[dev]"
pytest -q                       # pure-logic + (if online) release tests, including this README
CALCOFI_PG_TEST=1 pytest -q     # + live PostgreSQL tests (tunnel + ~/.pgpass)

The examples above are tests. tests/test_readme.py runs every python block of this README: the release blocks against the promoted release on every push (.github/workflows/test.yml), the PostgreSQL blocks with CALCOFI_PG_TEST=1. The CalCOFI database release pipeline (CalCOFI/workflows test_release.qmd) runs the same test against each new release before promoting it, pointed there by CALCOFI_RELEASE_VERSION (and CALCOFI_RELEASE_PREFIX on a staging run), so a column the release renames fails the release, not the reader. A block that must not run under a test starts with # readme: skip — none does today.