efe-T's picture
Model C v1.0.0: Colab-verified SAME detector threshold pipeline
677a9c5 verified
Raw History Blame Contribute Delete
12.9 kB
"""Pinned PSI acquisition and conservative, traceable workbook preprocessing."""
import hashlib
import json
import re
from pathlib import Path
import numpy as np
import openpyxl
import pandas as pd
import requests
from .inference import FEATURES
WORKBOOK = "PSI-101_Analyzed Data_SAME, SAME-R_Results.xlsx"
WORKBOOK_SHA = "6d3a539ccf425b1fa8edc19cc75d828f57d650c7ce734d4e7aa686fea9593218"
SOURCES = [
("PSI-101", WORKBOOK, WORKBOOK_SHA),
("PSI-102", "PSI-102_Experimental table_SAME.csv", "268d1e121d2f211b124407dd32b9475617bd269ede2c2d1749441da3e949a2af"),
("PSI-101", "PSI-101_Experimental table_SAME-R.csv", "87164b7dc9168a4273c159f7a9f23699afbd06ad97b77974377e79b9bf8a3bef"),
("PSI-102", "PSI-102_Reports_SAME_Final Science Report.pdf", "af91c96d22d93bbefbc0f2c5e1cc06e24813f532e5379b3fec9f121cccb22193"),
("PSI-101", "PSI-101_Reports_SAME-R_Final Report.pdf", "65381e2715eb7379113dd1b9c550baaa5fa5a247dba00419123143aa98ef80d6"),
]
MATERIALS = ["Kapton", "Lampwick", "Pyrell", "Silicone", "Teflon"]
MAP = {"N": "airflow_cm_s", "O": "resistance_ratio", "P": "heating_s", "Q": "aging_s", "R": "mixing_s"}
def sha(path):
return hashlib.sha256(Path(path).read_bytes()).hexdigest()
def acquire(root="."):
root = Path(root)
raw = root / "data/raw"
raw.mkdir(parents=True, exist_ok=True)
session = requests.Session()
sources = []
for study in ["PSI-102", "PSI-101"]:
url = f"https://psi.nasa.gov/geode-py/ws/repo/investigations/{study}"
dest = raw / f"{study}-metadata.json"
if not dest.exists():
response = session.get(url, timeout=120)
response.raise_for_status()
dest.write_bytes(response.content)
sources.append({"study": study, "url": url, "path": str(dest.relative_to(root)), "sha256": sha(dest), "kind": "source_metadata"})
for study, filename, checksum in SOURCES:
dest = raw / filename
url = f"https://psi.nasa.gov/geode-py/ws/studies/{study}/download"
if not dest.exists():
response = session.get(url, params={"file": filename, "version": 4, "redirect": "false"}, timeout=180)
response.raise_for_status()
signed_url = response.text.strip().strip('"')
if not signed_url.startswith("https://"):
raise RuntimeError("Unexpected PSI file resolver response")
# Never persist signed URL credentials in the manifest or logs.
payload = session.get(signed_url, timeout=300)
payload.raise_for_status()
temporary = dest.with_suffix(dest.suffix + ".partial")
temporary.write_bytes(payload.content)
if sha(temporary) != checksum:
temporary.unlink()
raise ValueError(f"Pinned PSI checksum mismatch: {filename}")
temporary.replace(dest)
if sha(dest) != checksum:
raise ValueError(f"Pinned PSI checksum mismatch: {filename}")
sources.append({"study": study, "url": url, "parameters": {"file": filename, "version": 4, "redirect": False}, "study_version": 4, "file_version": 1 if filename == WORKBOOK else "see_metadata", "path": str(dest.relative_to(root)), "bytes": dest.stat().st_size, "sha256": sha(dest), "kind": "source_reported"})
(raw / "source_manifest.json").write_text(json.dumps(sources, indent=2))
return raw / WORKBOOK
def number(value):
if isinstance(value, bool):
return None, "missing"
try:
parsed = float(value)
if np.isfinite(parsed):
return parsed, "reported_numeric"
except (TypeError, ValueError):
pass
return None, "missing_or_source_error"
def measurement(value, *, below_limit=False, saturated=False):
parsed, status = number(value)
if parsed is None:
return None, status
if below_limit:
return None, "below_detection_limit"
if saturated:
return None, "saturated"
return parsed, status
def preprocess(path, output="data/processed"):
wb = openpyxl.load_workbook(path, data_only=True, read_only=True)
sheet = wb["Combined Rslts Copy this sheet"]
records = []
for row_number, cells in enumerate(sheet.iter_rows(values_only=True), 1):
def cell(col):
return cells[openpyxl.utils.column_index_from_string(col) - 1] if openpyxl.utils.column_index_from_string(col) <= len(cells) else None
campaign = {1: "SAME", 2: "SAME-R"}.get(cell("A"))
if campaign is None or cell("C") not in MATERIALS:
continue
test = str(cell("G")).strip()
match = re.match(r"\s*(\d+)", test)
if not match:
continue
run = match[1]
sample = str(cell("D") or "").strip()
# SAME has missing sample IDs: carousel + position is the conservative reuse unit.
if not sample:
sample = f"carousel={cell('B')};position={cell('F')};material={cell('C')}"
signal, status = number(cell("AW"))
record = {"source_row": row_number, "source_sheet": sheet.title, "campaign": campaign, "material": cell("C"), "test_id": f"{campaign}:{test}", "source_test_id": test, "source_local_test_id": str(cell("E")), "run_id": f"{campaign}:{run}", "sample_id": f"{campaign}:{sample}", "description": str(cell("H") or ""), "fan_problem": str(cell("L") or ""), "flat_profiles": str(cell("M") or ""), "iss_scatter_v": signal, "iss_scatter_status": status, "iss_saturation_status": "not_individually_resolved", "iss_detection_limit_status": "not_documented_for_this_reduced_channel", "signal_kind": "measured", "label_provenance": "derived", "label_rule": "ISS scatter >=2.0 V; Meyer et al. 2018 Figure 9", "alarm_level_reached": int(signal >= 2.0) if signal is not None else None}
for col, name in MAP.items():
record[name], record[name + "_status"] = number(cell(col))
record[name + "_source_cell"] = f"{col}{row_number}"
record["net_aging_s"], record["net_aging_s_status"] = number(cell("U"))
for col, name in {"BL": "m0_source", "BM": "m1_mm_cm3", "BR": "source_amd_um", "BS": "source_dam_um"}.items():
record[name], record[name + "_status"] = number(cell(col))
if record[name] is not None and record[name] <= 0:
record[name] = None
record[name + "_status"] = "invalid_nonpositive_source_formula"
record["derived_amd_um"] = 1000 * record["m1_mm_cm3"] / record["m0_source"] if record["m0_source"] and record["m1_mm_cm3"] else None
record["amd_derivation"] = "1000*M1(mm/cm3)/M0(1/cm3); moment ratio; no lognormal assumption"
records.append(record)
wb.close()
# Connected components prevent leakage from reused samples AND aged/reburned runs.
parents = list(range(len(records)))
def find(i):
while parents[i] != i:
parents[i] = parents[parents[i]]
i = parents[i]
return i
seen = {}
for i, record in enumerate(records):
for key in [record["run_id"], record["sample_id"]]:
if key in seen:
parents[find(i)] = find(seen[key])
seen[key] = i
for i, r in enumerate(records):
r["group_id"] = f"group-{find(i):03d}"
r["aging_index"] = sum(1 for q in records[:i] if q["run_id"] == r["run_id"])
reasons = []
if re.search(r"\d+\s*a", r["source_test_id"], re.I):
reasons.append("secondary_aging_reading")
if any(r[k] is None for k in FEATURES[1:]):
reasons.append("missing_control")
if r["heating_s"] is None or r["heating_s"] <= 0:
reasons.append("followup_or_missing_heating_configuration")
if r["iss_scatter_v"] is None or r["iss_scatter_v"] < 0:
reasons.append("missing_or_invalid_signal")
if r["fan_problem"].lower() in ["y", "yes"] or r["flat_profiles"].lower() in ["y", "yes"]:
reasons.append("source_quality_flag")
r["training_eligible"] = not reasons
r["exclusion_reasons"] = ";".join(reasons)
df = pd.DataFrame(records)
df["duplicate_condition"] = df.duplicated(FEATURES, keep=False)
df["near_duplicate_condition"] = df.duplicated(["material", "airflow_cm_s", "resistance_ratio", "heating_s"], keep=False)
out = Path(output)
out.mkdir(parents=True, exist_ok=True)
df.to_csv(out / "observations.csv", index=False)
df[df.training_eligible].to_csv(out / "training.csv", index=False)
df.select_dtypes(include="number").corr().to_csv(out / "correlations.csv")
dictionary = [{"name": k, "unit": {"airflow_cm_s": "cm/s", "resistance_ratio": "1"}.get(k, "s"), "role": "controlled", "source": f"{sheet.title}!{col}", "conversion": "identity", "provenance": "source_reported"} for col, k in MAP.items()]
dictionary.extend([{"name": "iss_scatter_v", "unit": "V", "role": "measured", "source": f"{sheet.title}!AW", "provenance": "source_reported", "limitations": "High-end saturation not numerically de-censored; preserved as reported for threshold classification only; exact calibrated LOD unavailable."}, {"name": "alarm_level_reached", "unit": "boolean", "role": "derived_label", "provenance": "derived", "rule": "iss_scatter_v >= 2.0", "source": "Meyer et al. 2018 Figure 9", "assumptions": "Summary signal crossing threshold represents recorded level, not time-to-alarm."}])
details = {
"material": ("C", "category", "controlled", "source_reported", "Tested smoke material only"),
"campaign": ("A", "category", "identifier", "derived", "1 maps SAME;2 maps SAME-R; ground excluded"),
"source_local_test_id": ("E", "identifier", "identifier", "source_reported", "Local material/test notation"),
"source_test_id": ("G", "identifier", "identifier", "source_reported", "Global SAME number with secondary suffix a"),
"sample_id": ("D or B+F+C", "identifier", "identifier", "derived", "Campaign prefix; carousel/position/material fallback if D blank"),
"run_id": ("G", "identifier", "identifier", "derived", "Numeric prefix; campaign-qualified"),
"net_aging_s": ("U", "s", "source_derived", "source_reported", "Source timing reduction; not a programmed input"),
"m0_source": ("BL", "particles/cm3", "source_reduced_measurement", "source_reported", "Source calibrated CPC; size-dependent correction already applied"),
"m1_mm_cm3": ("BM", "mm/cm3", "source_reduced_measurement", "source_reported", "Author ion chamber reduction; no recalibration"),
"source_amd_um": ("BR", "um", "source_derived", "source_reported", "1000*M1/M0; no lognormal assumption"),
"source_dam_um": ("BS", "um", "source_derived", "source_reported", "Source mass-equivalent diameter; density/spherical-equivalent interpretation; no lognormal needed"),
"derived_amd_um": ("BL+BM", "um", "derived", "derived", "1000*M1/M0; no lognormal assumption"),
"group_id": ("D+G or B+F+C", "identifier", "derived", "derived", "Connected run/sample graph over all observations; conservative ambiguous51a link"),
"aging_index": ("row order within G numeric prefix", "index", "derived", "derived", "Sequential reading index, not independent experiment"),
}
known = {item["name"] for item in dictionary}
for name in df.columns:
if name in known:
continue
source, unit, role, provenance, assumptions = details.get(name, ("processing/source workbook", "1" if pd.api.types.is_numeric_dtype(df[name]) else "text", "metadata", "derived", "Status, identifier or deterministic processing metadata; see preprocessing code"))
dictionary.append({"name": name, "unit": unit, "role": role, "provenance": provenance, "source": f"{sheet.title}!{source}", "assumptions": assumptions})
for item in dictionary:
item["type"] = str(df[item["name"]].dtype)
item["missing_markers"] = ["blank", "NA", "n/a", "?", "??", "Excel error"]
item.setdefault("assumptions", "No conversion or additional physical model")
if item["role"] in ["measured", "source_reduced_measurement"]:
item["instrument_limits"] = "Per-row LOD/saturation cannot be reconstructed from the summary. Do not use generic instrument limits after source dilution/calibration. Only AW2V threshold used for learning."
(out / "data_dictionary.json").write_text(json.dumps(dictionary, indent=2))
(out / "harmonization.json").write_text(json.dumps({"campaigns": ["SAME", "SAME-R"], "source": "author-combined workbook", "controls": MAP, "units": "identity; no recalibration", "target": "AW volts; common ISS sensor alarm reference", "not_harmonized": ["STS AX requires AY baseline and device-use flags", "MPASS not common", "ground experiments excluded", "DBP calibration excluded", "moment concentrations retain author calibration; never model inputs"]}, indent=2))
return df