From a dataset page to a data frame¶

A question we hear: "I asked the API for bottle data from 1996 to 2023 and it stops in May 2021. Is the rest lost?" It is not lost, and the answer is a short walk: the dataset page says which dataset holds which years, the Explorer shows them, and calcofi4py pulls them into a DataFrame. This article takes that walk with the two datasets that meet at that seam — the hydrographic bottle database (calcofi_bottle) and the CTD casts (calcofi_ctd-cast) — and ends with the same eight variables, wide, that the question asked for.

Everything here runs against the public release: no account, no credentials, no path typed by hand (the package resolves every table through the release catalog).

In [1]:

Copied!





import pandas as pd
import calcofi4py as cc

print("calcofi4py", cc.__version__)        # the version every output below was produced with

pd.options.display.max_rows = 12
pd.options.display.width = 140

VERSION = cc.cc_resolve_version("latest")  # pin this string in your own code for a reproducible pull
rel = cc.cc_get_db(VERSION, tables=["sample", "obs_env", "sample_measurement", "measurement_type"])
rel.execute("SET enable_progress_bar = false")   # no Jupyter progress-bar widgets in a static page
print("release", VERSION)
import pandas as pd
import calcofi4py as cc

print("calcofi4py", cc.__version__)        # the version every output below was produced with

pd.options.display.max_rows = 12
pd.options.display.width = 140

VERSION = cc.cc_resolve_version("latest")  # pin this string in your own code for a reproducible pull
rel = cc.cc_get_db(VERSION, tables=["sample", "obs_env", "sample_measurement", "measurement_type"])
rel.execute("SET enable_progress_bar = false")   # no Jupyter progress-bar widgets in a static page
print("release", VERSION)

calcofi4py 0.9.3

release v2026.10.01

1. Read the dataset page¶

Every dataset has a page at https://calcofi.io/datasets/{dataset_key}/, built from the release's own dataset record (datasets.json). Its header answers what years, how many variables; its Access section has three blocks worth knowing:

Explore — the apps that open on this dataset (the CalCOFI Explorer first);
Get the data — the release's Parquet objects, the CF netCDF files and the ERDDAP datasets, each a link;
Code — ready-to-paste R and Python (cc_get_db(), then cite) and plain DuckDB SQL against the object URL.

The same record is one call away, so the years below are read from the release, not typed:

In [2]:

Copied!





pages = pd.DataFrame([
    {"dataset_key": d["dataset_key"], "name": d["dataset_name_short"],
     "years": f'{d["coverage"]["year_min"]}–{d["coverage"]["year_max"]}',
     "variables": d["coverage"]["n_variables"], "sampling events": d["coverage"]["n_roots"],
     "page": d["links"]["page"]}
    for d in cc.cc_datasets(VERSION)
    if d["dataset_key"] in ("calcofi_bottle", "calcofi_ctd-cast", "calcofi_ctd-derived")])
pages
pages = pd.DataFrame([
    {"dataset_key": d["dataset_key"], "name": d["dataset_name_short"],
     "years": f'{d["coverage"]["year_min"]}–{d["coverage"]["year_max"]}',
     "variables": d["coverage"]["n_variables"], "sampling events": d["coverage"]["n_roots"],
     "page": d["links"]["page"]}
    for d in cc.cc_datasets(VERSION)
    if d["dataset_key"] in ("calcofi_bottle", "calcofi_ctd-cast", "calcofi_ctd-derived")])
pages

Out[2]:

	dataset_key	name	years	variables	sampling events	page
0	calcofi_bottle	Hydrographic Bottle	1949–2021	26	35644	https://calcofi.io/datasets/calcofi_bottle/
1	calcofi_ctd-cast	CTD Cast Files	1993–2026	44	9674	https://calcofi.io/datasets/calcofi_ctd-cast/
2	calcofi_ctd-derived	CTD Derived Products	1993–2025	2	9275	https://calcofi.io/datasets/calcofi_ctd-derived/

And the same seam straight from the sample table — the first and last sampling event of each:

In [3]:

Copied!





rel.sql("""
  SELECT dataset_key, count(*) AS sampling_events,
         min(datetime)::DATE AS first, max(datetime)::DATE AS last
  FROM sample
  WHERE dataset_key IN ('calcofi_bottle', 'calcofi_ctd-cast')
  GROUP BY 1 ORDER BY 1
""").df()
rel.sql("""
  SELECT dataset_key, count(*) AS sampling_events,
         min(datetime)::DATE AS first, max(datetime)::DATE AS last
  FROM sample
  WHERE dataset_key IN ('calcofi_bottle', 'calcofi_ctd-cast')
  GROUP BY 1 ORDER BY 1
""").df()

Out[3]:

	dataset_key	sampling_events	first	last
0	calcofi_bottle	931015	1949-02-28	2021-05-13
1	calcofi_ctd-cast	19330	1993-08-11	2026-07-13

So a query on calcofi_bottle ends in 2021 by construction: that is where the bottle database, as the provider publishes it, ends. The CTD casts carry on, and — this is the part that is easy to miss — they carry the bottle samples drawn on those casts too, as measurement types prefixed btl_ (plus salinity_btl, oxygen_btl_ml_l, oxygen_btl_umol_kg). On the CTD Cast Files page they sit in the variable list next to the sensor series.

2. The same question in the Explorer¶

The CalCOFI Explorer answers it without code. Pick a variable (Temperature pools the bottle and CTD series, which are comparable), set the years, and the map, the years strip and the depth strip redraw; the Datasets pills under Refine show which dataset each value comes from, so the bottle database stopping in 2021 and the CTD casts running on are visible at a glance. For example, temperature, CTD casts only, 1996–2026.

Under Share: Download data (zip) hands over the observations, the exact SQL, the citations and reproduce.py; Copy code › Python copies a script that runs the same query with DuckDB; Copy link is the whole view as a URL. The code it copies reads the same release objects as the package does below.

3. The same pull with calcofi4py¶

CTD cast profiles, 1996 → present¶

Which CTD series answers "temperature", "salinity", "oxygen"? The release says so itself: measurement_type.variable groups the series that measure the same thing across datasets.

In [4]:

Copied!





rel.sql("""
  SELECT variable, measurement_type, dataset, units, description
  FROM measurement_type WHERE variable IS NOT NULL ORDER BY variable, dataset
""").df()
rel.sql("""
  SELECT variable, measurement_type, dataset, units, description
  FROM measurement_type WHERE variable IS NOT NULL ORDER BY variable, dataset
""").df()

Out[4]:

	variable	measurement_type	dataset	units	description
0	oxygen_ml_l	oxygen_ml_l	bottle	ml/L	Dissolved oxygen (QC'd)
1	oxygen_ml_l	oxygen_ml_l_ave_sta_corr	ctd-cast	ml/L	DO average station-corrected
2	oxygen_umol_kg	oxygen_umol_kg	bottle	umol/kg	Dissolved oxygen (QC'd)
3	oxygen_umol_kg	oxygen_umol_kg_ave_sta_corr	ctd-cast	umol/kg	DO average station-corrected
4	salinity	salinity	bottle	PSS-78	Salinity - Practical Salinity Scale 1978 (QC'd)
5	salinity	salinity_ave_corr	ctd-cast	PSU	Average salinity corrected
6	sigma_theta	sigma_theta	bottle	kg/m3	Potential density (Sigma Theta)
7	sigma_theta	sigma_theta_1	ctd-cast	kg/m3	Potential density sensor 1
8	temperature	temperature	bottle	degC	Water temperature (QC'd)
9	temperature	temperature_ave	ctd-cast	degC	Average temperature

The CTD headline series are the sensor averages, with salinity and oxygen station-corrected against the bottles (the provider's own guidance: station-corrected data are the best). Chlorophyll and nitrate on a cast come two ways: a sensor estimate corrected to the bottles (est_*_sta_corr, fluorometer and ISUS), and the bottle value itself (btl_*).

Two filters belong on every pull. Quality flags: measurement_qual is the provider's own code, and qual_ok_sql() is the predicate that drops what the provider flagged questionable or bad (CTD codes 8 and 9). Data stage: every CTD cast carries sample.data_stage — final (fully processed), preliminary_with_bottle (the bottle merge has run, so corrected salinity and oxygen and the btl_* values exist) or preliminary_without_bottle (sensor data only: no corrected salinity or oxygen and no bottle values yet), and a cruise moves up the ladder as the provider reprocesses it.

In [5]:

Copied!





CTD = ["temperature_ave", "salinity_ave_corr", "oxygen_ml_l_ave_sta_corr",
       "est_chlorophyll_a_sta_corr", "btl_chlorophyll_a",
       "est_nitrate_sta_corr", "btl_nitrate"]
ctd_in = ", ".join(f"'{t}'" for t in CTD)

flags = rel.sql(f"""
  SELECT measurement_type, count(*) AS rows,
         count(*) FILTER (WHERE NOT {cc.qual_ok_sql('o')}) AS flagged_dropped
  FROM obs_env o
  WHERE o.dataset_key = 'calcofi_ctd-cast' AND o.measurement_type IN ({ctd_in})
    AND o.datetime >= '1996-04-01'
  GROUP BY 1 ORDER BY 1
""").df()
flags
CTD = ["temperature_ave", "salinity_ave_corr", "oxygen_ml_l_ave_sta_corr",
       "est_chlorophyll_a_sta_corr", "btl_chlorophyll_a",
       "est_nitrate_sta_corr", "btl_nitrate"]
ctd_in = ", ".join(f"'{t}'" for t in CTD)

flags = rel.sql(f"""
  SELECT measurement_type, count(*) AS rows,
         count(*) FILTER (WHERE NOT {cc.qual_ok_sql('o')}) AS flagged_dropped
  FROM obs_env o
  WHERE o.dataset_key = 'calcofi_ctd-cast' AND o.measurement_type IN ({ctd_in})
    AND o.datetime >= '1996-04-01'
  GROUP BY 1 ORDER BY 1
""").df()
flags

Out[5]:

	measurement_type	rows	flagged_dropped
0	btl_chlorophyll_a	104126	0
1	btl_nitrate	130118	0
2	est_chlorophyll_a_sta_corr	322040	55
3	est_nitrate_sta_corr	291146	419
4	oxygen_ml_l_ave_sta_corr	600055	0
5	salinity_ave_corr	619829	0
6	temperature_ave	640553	0

How many casts per year carry each series, by data stage — the table to read before trusting a trend at the recent end:

In [6]:

Copied!





casts = rel.sql(f"""
  SELECT year(s.datetime) AS year, s.data_stage, o.measurement_type,
         count(DISTINCT o.sample_key) AS casts
  FROM obs_env o JOIN sample s ON s.sample_key = o.sample_key
  WHERE o.dataset_key = 'calcofi_ctd-cast' AND o.measurement_type IN ({ctd_in})
    AND s.datetime >= '1996-04-01' AND {cc.qual_ok_sql('o')}
  GROUP BY ALL
""").df()
by_year = (casts.pivot_table(index=["year", "data_stage"], columns="measurement_type",
                             values="casts", fill_value=0)
                .reindex(columns=CTD).astype(int).reset_index())
by_year.columns.name = None
by_year.query("year >= 2018")
casts = rel.sql(f"""
  SELECT year(s.datetime) AS year, s.data_stage, o.measurement_type,
         count(DISTINCT o.sample_key) AS casts
  FROM obs_env o JOIN sample s ON s.sample_key = o.sample_key
  WHERE o.dataset_key = 'calcofi_ctd-cast' AND o.measurement_type IN ({ctd_in})
    AND s.datetime >= '1996-04-01' AND {cc.qual_ok_sql('o')}
  GROUP BY ALL
""").df()
by_year = (casts.pivot_table(index=["year", "data_stage"], columns="measurement_type",
                             values="casts", fill_value=0)
                .reindex(columns=CTD).astype(int).reset_index())
by_year.columns.name = None
by_year.query("year >= 2018")

Out[6]:

	year	data_stage	temperature_ave	salinity_ave_corr	oxygen_ml_l_ave_sta_corr	est_chlorophyll_a_sta_corr	btl_chlorophyll_a	est_nitrate_sta_corr	btl_nitrate
22	2018	final	294	294	294	294	294	255	271
23	2019	final	241	241	239	241	241	211	241
24	2020	final	250	250	247	230	230	160	229
25	2021	final	122	122	104	119	121	0	118
26	2021	preliminary_with_bottle	146	146	146	146	146	118	145
27	2022	preliminary_with_bottle	227	227	220	224	225	0	95
28	2023	preliminary_with_bottle	323	323	320	321	322	0	26
29	2024	preliminary_with_bottle	270	270	265	269	270	0	223
30	2025	preliminary_with_bottle	231	231	215	230	230	104	231
31	2025	preliminary_without_bottle	100	0	0	0	0	0	0
32	2026	preliminary_without_bottle	299	0	0	0	0	0	0

The sensor series run to the last cruise; the bottle-derived ones stop where the bottle merge has not yet happened. btl_nitrate thins in 2022–2023 because the provider's preliminary CTD + bottle files for most of those cruises carry bottle depths, salinity, oxygen and chlorophyll but no nutrients yet.

The bottle database to 2021, and the bottle values on the casts after it¶

The bottle database and the btl_* values on the CTD casts come from the same bottles for the cruises they share, but they are different products: the bottle database is the provider's reconciled archive with its own quality codes, the btl_* values are what the CTD + bottle files carry, with no quality codes at all. Whether they agree value for value is a question we have put to the provider. So they are shown side by side with a source column, never merged — a merge would silently double-count the years they overlap.

In [7]:

Copied!





seam = rel.sql(f"""
  SELECT 'bottle database' AS source, o.measurement_type, o.root_id, o.cruise_key, o.site_key,
         o.datetime, o.depth_min_m AS depth_m, o.value
  FROM obs_env o
  WHERE o.dataset_key = 'calcofi_bottle' AND o.measurement_type = 'nitrate'
    AND o.datetime >= '2015-01-01' AND {cc.qual_ok_sql('o')}
  UNION ALL
  SELECT 'CTD cast btl_*' AS source, o.measurement_type, o.root_id, o.cruise_key, o.site_key,
         o.datetime, o.depth_min_m AS depth_m, o.value
  FROM obs_env o
  WHERE o.dataset_key = 'calcofi_ctd-cast' AND o.measurement_type = 'btl_nitrate'
    AND o.datetime >= '2015-01-01' AND {cc.qual_ok_sql('o')}
""").df()
side = (seam.assign(year=seam.datetime.dt.year)
            .groupby(["year", "source"]).root_id.nunique()     # root_id = the cast, in both datasets
            .unstack("source", fill_value=0))
side.columns.name = "casts with nitrate"
side
seam = rel.sql(f"""
  SELECT 'bottle database' AS source, o.measurement_type, o.root_id, o.cruise_key, o.site_key,
         o.datetime, o.depth_min_m AS depth_m, o.value
  FROM obs_env o
  WHERE o.dataset_key = 'calcofi_bottle' AND o.measurement_type = 'nitrate'
    AND o.datetime >= '2015-01-01' AND {cc.qual_ok_sql('o')}
  UNION ALL
  SELECT 'CTD cast btl_*' AS source, o.measurement_type, o.root_id, o.cruise_key, o.site_key,
         o.datetime, o.depth_min_m AS depth_m, o.value
  FROM obs_env o
  WHERE o.dataset_key = 'calcofi_ctd-cast' AND o.measurement_type = 'btl_nitrate'
    AND o.datetime >= '2015-01-01' AND {cc.qual_ok_sql('o')}
""").df()
side = (seam.assign(year=seam.datetime.dt.year)
            .groupby(["year", "source"]).root_id.nunique()     # root_id = the cast, in both datasets
            .unstack("source", fill_value=0))
side.columns.name = "casts with nitrate"
side

Out[7]:

casts with nitrate	CTD cast btl_*	bottle database
year
2015	288	289
2016	301	298
2017	314	310
2018	271	268
2019	241	230
2020	229	222
2021	263	117
2022	95	0
2023	26	0
2024	223	0
2025	231	0

Where both exist they count nearly the same casts; after May 2021 only the CTD casts carry nitrate. For a series that crosses the seam, pick one source per period and keep the source column in your table, so a reader can see where it changes.

4. Per-cast products: mixed-layer depth and chlorophyll¶

Some quantities have one value per cast, not one per depth: the mixed-layer depth (three definitions) and the chlorophyll maximum and depth-integrated chlorophyll. They are the calcofi_ctd-derived dataset, in the sample_measurement table, keyed to the CTD cast they were computed from.

In [8]:

Copied!





derived = rel.sql("""
  SELECT s.sample_key, s.cruise_key, s.site_key, s.datetime, s.data_stage,
         m.measurement_type, m.measurement_value
  FROM sample_measurement m JOIN sample s ON s.sample_key = m.sample_key
  WHERE m.dataset_key = 'calcofi_ctd-derived' AND s.datetime >= '1996-04-01'
""").df()
per_cast = (derived.pivot_table(index=["sample_key", "cruise_key", "site_key", "datetime", "data_stage"],
                                columns="measurement_type", values="measurement_value")
                   .reset_index())
per_cast.columns.name = None
print(f"{len(per_cast):,} casts, {per_cast.datetime.min():%Y-%m-%d} → {per_cast.datetime.max():%Y-%m-%d}")
rel.sql("""
  SELECT measurement_type, units, description FROM measurement_type
  WHERE measurement_type IN (SELECT DISTINCT measurement_type FROM sample_measurement
                             WHERE dataset_key = 'calcofi_ctd-derived')
  ORDER BY 1
""").df()
derived = rel.sql("""
  SELECT s.sample_key, s.cruise_key, s.site_key, s.datetime, s.data_stage,
         m.measurement_type, m.measurement_value
  FROM sample_measurement m JOIN sample s ON s.sample_key = m.sample_key
  WHERE m.dataset_key = 'calcofi_ctd-derived' AND s.datetime >= '1996-04-01'
""").df()
per_cast = (derived.pivot_table(index=["sample_key", "cruise_key", "site_key", "datetime", "data_stage"],
                                columns="measurement_type", values="measurement_value")
                   .reset_index())
per_cast.columns.name = None
print(f"{len(per_cast):,} casts, {per_cast.datetime.min():%Y-%m-%d} → {per_cast.datetime.max():%Y-%m-%d}")
rel.sql("""
  SELECT measurement_type, units, description FROM measurement_type
  WHERE measurement_type IN (SELECT DISTINCT measurement_type FROM sample_measurement
                             WHERE dataset_key = 'calcofi_ctd-derived')
  ORDER BY 1
""").df()

8,985 casts, 1996-04-15 → 2026-07-13

Out[8]:

	measurement_type	units	description
0	chl_integrated	mg/m2	Chlorophyll-a integrated from the surface to 2...
1	chl_integrated_depth	m	Depth reached by chl_integrated (200 m unless ...
2	chl_max	ug/L	Chlorophyll-a at its maximum (5 m running median)
3	chl_max_depth	m	Depth of the chlorophyll-a maximum (5 m runnin...
4	mld_sigma_theta_003	m	Mixed-layer depth, sigma-theta increase of 0.0...
5	mld_sigma_theta_0125	m	Mixed-layer depth, sigma-theta increase of 0.1...
6	mld_temperature_02	m	Mixed-layer depth, temperature departure of 0....

In [9]:

Copied!

per_cast.sort_values("datetime").tail(5)
per_cast.sort_values("datetime").tail(5)

Out[9]:

	sample_key	cruise_key	site_key	datetime	data_stage	chl_integrated	chl_integrated_depth	chl_max	chl_max_depth	mld_sigma_theta_003	mld_sigma_theta_0125	mld_temperature_02
7893	calcofi_ctd-cast:cast:2607_068d	2026-07-3322	076.7 070.0	2026-07-13 00:37:02	preliminary_without_bottle	NaN	NaN	NaN	NaN	NaN	NaN	12.071284
7894	calcofi_ctd-cast:cast:2607_069d	2026-07-3322	076.7 060.0	2026-07-13 06:15:28	preliminary_without_bottle	NaN	NaN	NaN	NaN	NaN	NaN	12.628455
7895	calcofi_ctd-cast:cast:2607_070d	2026-07-3322	076.7 055.0	2026-07-13 09:48:13	preliminary_without_bottle	NaN	NaN	NaN	NaN	NaN	NaN	10.368121
7896	calcofi_ctd-cast:cast:2607_071d	2026-07-3322	076.7 051.0	2026-07-13 12:58:57	preliminary_without_bottle	NaN	NaN	NaN	NaN	NaN	NaN	14.340243
7897	calcofi_ctd-cast:cast:2607_072d	2026-07-3322	076.7 049.0	2026-07-13 15:03:20	preliminary_without_bottle	NaN	NaN	NaN	NaN	NaN	NaN	14.496187

The most recent casts show the data stages again: a preliminary_without_bottle cast has no corrected salinity (so no density) and no bottle-corrected chlorophyll, so only the temperature-based mixed-layer depth. (The years on the dataset page count the per-depth series of calcofi_ctd-derived; the per-cast table above can run later.)

5. The original shape: eight variables, wide, with `order_occ`¶

The question asked for depth_r, temperature, salinity, bottom_depth, chlorophyll_a, nitrate, nitrite, oxygen_ml_l from the bottle database, pivoted wide with order_occ. On the CTD casts the same eight are: depth is the row's own depth_min_m, bottom depth is the cast's seafloor_depth_m (on sample, beside order_occ), and the other six map to the series below. Sensor series are on a 10 m grid (plus inflections and every bottle depth); btl_* values sit at the bottle depths, so their columns are empty on the rows between bottles. Swap the dictionary to change the variables.

In [10]:

Copied!





EIGHT = {                                   # her name -> the CTD-cast series
    "temperature":   "temperature_ave",
    "salinity":      "salinity_ave_corr",
    "oxygen_ml_l":   "oxygen_ml_l_ave_sta_corr",
    "chlorophyll_a": "btl_chlorophyll_a",
    "nitrate":       "btl_nitrate",
    "nitrite":       "btl_nitrite",
}

def ctd_wide(columns=EIGHT, start="1996-04-01", end=None, con=rel):
    """CTD-cast profiles, one row per cast x depth, one column per variable, flags applied."""
    pick = ", ".join(f"max(o.value) FILTER (WHERE o.measurement_type = '{t}') AS {name}"
                     for name, t in columns.items())
    types = ", ".join(f"'{t}'" for t in columns.values())
    until = f"AND s.datetime < '{end}'" if end else ""
    return con.sql(f"""
      SELECT s.sample_key, s.cruise_key, s.site_key, s.order_occ, s.datetime,
             s.latitude, s.longitude, s.data_stage,
             o.depth_min_m AS depth_r, s.seafloor_depth_m AS bottom_depth, {pick}
      FROM obs_env o JOIN sample s ON s.sample_key = o.sample_key
      WHERE o.dataset_key = 'calcofi_ctd-cast' AND o.measurement_type IN ({types})
        AND s.datetime >= '{start}' {until} AND {cc.qual_ok_sql('o')}
      GROUP BY ALL ORDER BY s.datetime, s.sample_key, depth_r
    """).df()

wide = ctd_wide()
print(f"{wide.shape[0]:,} rows x {wide.shape[1]} columns; {wide.sample_key.nunique():,} casts, "
      f"{wide.datetime.min():%Y-%m-%d} → {wide.datetime.max():%Y-%m-%d}")
wide.dropna(subset=["nitrate"]).tail(5)
EIGHT = {                                   # her name -> the CTD-cast series
    "temperature":   "temperature_ave",
    "salinity":      "salinity_ave_corr",
    "oxygen_ml_l":   "oxygen_ml_l_ave_sta_corr",
    "chlorophyll_a": "btl_chlorophyll_a",
    "nitrate":       "btl_nitrate",
    "nitrite":       "btl_nitrite",
}

def ctd_wide(columns=EIGHT, start="1996-04-01", end=None, con=rel):
    """CTD-cast profiles, one row per cast x depth, one column per variable, flags applied."""
    pick = ", ".join(f"max(o.value) FILTER (WHERE o.measurement_type = '{t}') AS {name}"
                     for name, t in columns.items())
    types = ", ".join(f"'{t}'" for t in columns.values())
    until = f"AND s.datetime < '{end}'" if end else ""
    return con.sql(f"""
      SELECT s.sample_key, s.cruise_key, s.site_key, s.order_occ, s.datetime,
             s.latitude, s.longitude, s.data_stage,
             o.depth_min_m AS depth_r, s.seafloor_depth_m AS bottom_depth, {pick}
      FROM obs_env o JOIN sample s ON s.sample_key = o.sample_key
      WHERE o.dataset_key = 'calcofi_ctd-cast' AND o.measurement_type IN ({types})
        AND s.datetime >= '{start}' {until} AND {cc.qual_ok_sql('o')}
      GROUP BY ALL ORDER BY s.datetime, s.sample_key, depth_r
    """).df()

wide = ctd_wide()
print(f"{wide.shape[0]:,} rows x {wide.shape[1]} columns; {wide.sample_key.nunique():,} casts, "
      f"{wide.datetime.min():%Y-%m-%d} → {wide.datetime.max():%Y-%m-%d}")
wide.dropna(subset=["nitrate"]).tail(5)

630,779 rows x 16 columns; 9,020 casts, 1996-04-15 → 2026-07-13

Out[10]:

	sample_key	cruise_key	site_key	order_occ	datetime	latitude	longitude	data_stage	depth_r	bottom_depth	temperature	salinity	oxygen_ml_l	chlorophyll_a	nitrate	nitrite
610067	calcofi_ctd-cast:cast:2504_116d	2025-04-3322	060.0 053.0	116	2025-04-18 22:48:32	37.84821	-123.1012	preliminary_with_bottle	9.0	87.0	10.34980	33.59885	5.73735	2.419	20.50	0.082
610070	calcofi_ctd-cast:cast:2504_116d	2025-04-3322	060.0 053.0	116	2025-04-18 22:48:32	37.84821	-123.1012	preliminary_with_bottle	17.0	87.0	9.82320	33.63905	5.12650	2.228	22.80	0.093
610073	calcofi_ctd-cast:cast:2504_116d	2025-04-3322	060.0 053.0	116	2025-04-18 22:48:32	37.84821	-123.1012	preliminary_with_bottle	30.0	87.0	9.36715	33.70320	3.82220	0.593	25.19	0.092
610076	calcofi_ctd-cast:cast:2504_116d	2025-04-3322	060.0 053.0	116	2025-04-18 22:48:32	37.84821	-123.1012	preliminary_with_bottle	50.0	87.0	8.93990	33.85405	2.33885	0.061	28.78	0.041
610080	calcofi_ctd-cast:cast:2504_116d	2025-04-3322	060.0 053.0	116	2025-04-18 22:48:32	37.84821	-123.1012	preliminary_with_bottle	76.0	87.0	8.70880	33.97870	1.69605	0.040	30.25	0.071

wide.to_csv("calcofi_ctd_casts_1996-present.csv", index=False) writes it out. To keep only fully processed casts, filter data_stage == "final"; to see what a cruise is still waiting for, group by data_stage.

6. In ten lines¶

The same pull, minimal — CTD temperature, salinity, oxygen, chlorophyll and nitrate, 1996 → present, flags applied, long then wide:

In [11]:

Copied!





import calcofi4py as cc
con = cc.cc_get_db(tables=["obs_env", "sample"])            # latest public release, no credentials
con.execute("SET enable_progress_bar = false")              # in Jupyter only: no progress widget
df = con.sql(f"""
  SELECT s.cruise_key, s.order_occ, s.datetime, s.latitude, s.longitude, s.data_stage,
         o.depth_min_m AS depth_m, o.measurement_type, o.value
  FROM obs_env o JOIN sample s ON s.sample_key = o.sample_key
  WHERE o.dataset_key = 'calcofi_ctd-cast' AND s.datetime >= '1996-04-01' AND {cc.qual_ok_sql('o')}
    AND o.measurement_type IN ('temperature_ave', 'salinity_ave_corr', 'oxygen_ml_l_ave_sta_corr',
                               'est_chlorophyll_a_sta_corr', 'btl_chlorophyll_a', 'btl_nitrate')""").df()
wide10 = df.pivot_table(index=["cruise_key", "order_occ", "datetime", "latitude", "longitude",
                               "data_stage", "depth_m"], columns="measurement_type", values="value").reset_index()
print(f"{len(df):,} values -> {wide10.shape[0]:,} rows x {wide10.shape[1]} columns")
import calcofi4py as cc
con = cc.cc_get_db(tables=["obs_env", "sample"])            # latest public release, no credentials
con.execute("SET enable_progress_bar = false")              # in Jupyter only: no progress widget
df = con.sql(f"""
  SELECT s.cruise_key, s.order_occ, s.datetime, s.latitude, s.longitude, s.data_stage,
         o.depth_min_m AS depth_m, o.measurement_type, o.value
  FROM obs_env o JOIN sample s ON s.sample_key = o.sample_key
  WHERE o.dataset_key = 'calcofi_ctd-cast' AND s.datetime >= '1996-04-01' AND {cc.qual_ok_sql('o')}
    AND o.measurement_type IN ('temperature_ave', 'salinity_ave_corr', 'oxygen_ml_l_ave_sta_corr',
                               'est_chlorophyll_a_sta_corr', 'btl_chlorophyll_a', 'btl_nitrate')""").df()
wide10 = df.pivot_table(index=["cruise_key", "order_occ", "datetime", "latitude", "longitude",
                               "data_stage", "depth_m"], columns="measurement_type", values="value").reset_index()
print(f"{len(df):,} values -> {wide10.shape[0]:,} rows x {wide10.shape[1]} columns")

2,416,666 values -> 630,791 rows x 13 columns

7. How to cite¶

Cite the datasets you used and the release they came from — cc_cite() formats both (text, BibTeX or CSL-JSON) from the release's own records:

In [12]:

Copied!

for entry in cc.cc_cite(["calcofi_ctd-cast", "calcofi_bottle", "calcofi_ctd-derived"], version=VERSION):
    print(entry, end="\n\n")
for entry in cc.cc_cite(["calcofi_ctd-cast", "calcofi_bottle", "calcofi_ctd-derived"], version=VERSION):
    print(entry, end="\n\n")

CalCOFI (2026). CalCOFI Integrated Database, release v2026.10.01 [Data set]. Scripps Institution of Oceanography, NOAA Fisheries, and California Department of Fish and Wildlife. https://doi.org/10.5281/zenodo.23085119
Page: https://calcofi.io/datasets/release/

CalCOFI. (2023). CalCOFI CTD Cast Files. CalCOFI.org.
Page: https://calcofi.io/datasets/calcofi_ctd-cast/

CalCOFI. (2023). CalCOFI Bottle Database 194903-202105. CalCOFI.org.
Page: https://calcofi.io/datasets/calcofi_bottle/

CalCOFI. (2026). CalCOFI CTD Derived Hydrographic Products (from the CalCOFI CTD Cast Files). CalCOFI.io. https://calcofi.io/workflows/ingest_calcofi_ctd-derived.html
Page: https://calcofi.io/datasets/calcofi_ctd-derived/

Session info¶

In [13]:

Copied!

print(cc.cc_session_info(extra={"release": VERSION}))
print(cc.cc_session_info(extra={"release": VERSION}))

rendered_utc    2026-10-02 11:38:45 UTC
python          3.14.7 (CPython)
platform        macOS-15.7.1-arm64-arm-64bit-Mach-O
executable      /Users/bbest/Github/CalCOFI/calcofi4py/.venv/bin/python3.14

packages
  calcofi4py    0.9.2
  duckdb        1.5.5
  psycopg       3.3.4
  pandas        3.0.5
  plotly        6.9.0
  numpy         2.5.2
  jupyter_core  5.9.1
  ipykernel     7.3.0

environment
  release       v2026.10.01