File size: 12,880 Bytes
677a9c5 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 | """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
|