1197 lines
53 KiB
Python
1197 lines
53 KiB
Python
#!/usr/bin/env python3
|
|
"""Data-quality audit of the Schwarz PAX BLE counter export.
|
|
|
|
Reads the raw CSV read-only, computes schema/coverage/cadence/correlation/
|
|
zero-inflation/battery/anomaly statistics, writes CSV+JSON tables and PNG
|
|
figures under paper/research/. No raw data is modified.
|
|
|
|
Run:
|
|
uv run --with pandas --with matplotlib python paper/research/scripts/01_data_quality.py
|
|
"""
|
|
from __future__ import annotations
|
|
|
|
import json
|
|
from pathlib import Path
|
|
|
|
import matplotlib
|
|
|
|
matplotlib.use("Agg")
|
|
import matplotlib.dates as mdates
|
|
import matplotlib.pyplot as plt
|
|
import numpy as np
|
|
import pandas as pd
|
|
from zoneinfo import ZoneInfo
|
|
|
|
ROOT = Path("/home/noel/Documents/Hackathon_2026")
|
|
RAW = ROOT / "data" / "raw" / "schwarz-pax.csv"
|
|
OUT = ROOT / "paper" / "research"
|
|
TAB = OUT / "outputs"
|
|
FIG = OUT / "figures"
|
|
for d in (TAB, FIG, OUT / "scripts"):
|
|
d.mkdir(parents=True, exist_ok=True)
|
|
|
|
TZ = ZoneInfo("Europe/Berlin")
|
|
SLOT = "15min"
|
|
|
|
# Panel window: full days after all eight core locations are live.
|
|
PANEL_START = pd.Timestamp("2026-06-18", tz="UTC")
|
|
PANEL_END = pd.Timestamp("2026-11-08", tz="UTC") # exclusive
|
|
PANEL_DAYS = int((PANEL_END - PANEL_START).days)
|
|
PANEL_SLOTS_TOTAL = int((PANEL_END - PANEL_START) / pd.Timedelta(SLOT))
|
|
|
|
# Two device IDs carry the same location name (hardware replacement).
|
|
LOC_MAP = {"863f75b0": "density-01", "f8c19730": "density-01"}
|
|
DEVICE_LABEL = {
|
|
"863f75b0": "density-01 (old)",
|
|
"f8c19730": "density-01 (new)",
|
|
}
|
|
|
|
plt.rcParams.update(
|
|
{
|
|
"figure.dpi": 150,
|
|
"savefig.dpi": 180,
|
|
"font.size": 8,
|
|
"axes.titlesize": 9,
|
|
"axes.labelsize": 8,
|
|
"axes.grid": True,
|
|
"grid.alpha": 0.25,
|
|
"axes.spines.top": False,
|
|
"axes.spines.right": False,
|
|
}
|
|
)
|
|
|
|
|
|
def pct(x: float, nd: int = 1) -> float:
|
|
return round(float(x) * 100, nd)
|
|
|
|
|
|
def md_table(df: pd.DataFrame, floatfmt: str = "{:.2f}") -> str:
|
|
"""Small markdown table writer that does not need tabulate."""
|
|
df = df.copy()
|
|
cols = list(df.columns)
|
|
lines = ["| " + " | ".join(map(str, cols)) + " |",
|
|
"|" + "|".join(["---"] * len(cols)) + "|"]
|
|
for _, row in df.iterrows():
|
|
vals = []
|
|
for c in cols:
|
|
v = row[c]
|
|
if isinstance(v, (float, np.floating)):
|
|
vals.append("" if pd.isna(v) else floatfmt.format(v))
|
|
else:
|
|
vals.append(str(v))
|
|
lines.append("| " + " | ".join(vals) + " |")
|
|
return "\n".join(lines)
|
|
|
|
|
|
def main() -> None:
|
|
raw = pd.read_csv(RAW, parse_dates=["time"])
|
|
raw["time"] = pd.to_datetime(raw["time"], utc=True)
|
|
raw = raw.sort_values(["sensor_id", "time"]).reset_index(drop=True)
|
|
|
|
dens = raw[raw["type"] == "densityData"].copy()
|
|
batt = raw[raw["type"] == "batteryData"].copy()
|
|
|
|
# ---------------------------------------------------------------- schema
|
|
schema = pd.DataFrame(
|
|
{
|
|
"column": raw.columns,
|
|
"dtype": [str(t) for t in raw.dtypes],
|
|
"non_null": [int(raw[c].notna().sum()) for c in raw.columns],
|
|
"null": [int(raw[c].isna().sum()) for c in raw.columns],
|
|
"example": [raw[c].iloc[0] for c in raw.columns],
|
|
}
|
|
)
|
|
schema.to_csv(TAB / "schema.csv", index=False)
|
|
|
|
inventory = (
|
|
raw.groupby(["sensor_id", "name", "longitude", "latitude"], dropna=False)
|
|
.agg(rows=("value", "size"), first=("time", "min"), last=("time", "max"))
|
|
.reset_index()
|
|
)
|
|
inventory["readings_density"] = inventory["sensor_id"].map(
|
|
dens.groupby("sensor_id").size()
|
|
)
|
|
inventory["readings_battery"] = inventory["sensor_id"].map(
|
|
batt.groupby("sensor_id").size()
|
|
)
|
|
inventory["location"] = inventory["sensor_id"].map(LOC_MAP).fillna(inventory["name"])
|
|
inventory["role"] = np.where(
|
|
inventory["readings_density"] >= 1000, "core", "episodic"
|
|
)
|
|
inventory = inventory.sort_values(["role", "location", "first"]).reset_index(drop=True)
|
|
inventory.to_csv(TAB / "sensor_inventory.csv", index=False)
|
|
|
|
core_ids = inventory.loc[inventory.role == "core", "sensor_id"].tolist()
|
|
episodic_ids = inventory.loc[inventory.role == "episodic", "sensor_id"].tolist()
|
|
core = dens[dens.sensor_id.isin(core_ids)].copy()
|
|
core["slot"] = core["time"].dt.floor(SLOT)
|
|
core["local"] = core["time"].dt.tz_convert(TZ)
|
|
|
|
# -------------------------------------------------------------- cadence
|
|
cadence_rows = []
|
|
interval_rows = []
|
|
for sid, g in core.groupby("sensor_id"):
|
|
g = g.sort_values("time")
|
|
dt = g["time"].diff().dt.total_seconds().dropna()
|
|
dt = dt[dt > 0]
|
|
near = dt[dt < 3600]
|
|
cadence_rows.append(
|
|
{
|
|
"sensor_id": sid,
|
|
"name": g["name"].iloc[0],
|
|
"n": len(g),
|
|
"median_s": near.median(),
|
|
"p05_s": near.quantile(0.05),
|
|
"p95_s": near.quantile(0.95),
|
|
"median_min": near.median() / 60,
|
|
"p05_min": near.quantile(0.05) / 60,
|
|
"p25_min": near.quantile(0.25) / 60,
|
|
"p75_min": near.quantile(0.75) / 60,
|
|
"p95_min": near.quantile(0.95) / 60,
|
|
"share_14_17min": ((near >= 840) & (near <= 1020)).mean(),
|
|
"share_28_34min": ((near >= 1680) & (near <= 2040)).mean(),
|
|
"max_gap_h": dt.max() / 3600,
|
|
}
|
|
)
|
|
for v in dt:
|
|
interval_rows.append((sid, v / 60))
|
|
cadence = pd.DataFrame(cadence_rows)
|
|
cadence.to_csv(TAB / "cadence_by_sensor.csv", index=False)
|
|
intervals = pd.DataFrame(interval_rows, columns=["sensor_id", "minutes"])
|
|
|
|
bins = [0, 5, 10, 14, 17, 20, 28, 34, 40, 60, 360, 1440, np.inf]
|
|
labels = ["<5m", "5-10m", "10-14m", "14-17m", "17-20m", "20-28m",
|
|
"28-34m", "34-40m", "40-60m", "1-6h", "6-24h", ">24h"]
|
|
intervals["bin"] = pd.cut(intervals["minutes"], bins=bins, labels=labels, right=False)
|
|
cadence_bins = (
|
|
intervals.groupby(["sensor_id", "bin"], observed=False).size().unstack(fill_value=0)
|
|
)
|
|
cadence_bins.to_csv(TAB / "cadence_bins.csv")
|
|
cadence_bin_share = cadence_bins.div(cadence_bins.sum(axis=1), axis=0)
|
|
|
|
# ------------------------------------------------- coverage / basic stats
|
|
rows = []
|
|
for sid, g in core.groupby("sensor_id"):
|
|
g = g.sort_values("time")
|
|
n = len(g)
|
|
span_s = (g["time"].iloc[-1] - g["time"].iloc[0]).total_seconds()
|
|
first_slot = g["time"].iloc[0].floor(SLOT)
|
|
last_slot = g["time"].iloc[-1].floor(SLOT)
|
|
expected_active = int((last_slot - first_slot) / pd.Timedelta(SLOT)) + 1
|
|
observed_active = g["slot"].nunique()
|
|
panel = g[(g.slot >= PANEL_START) & (g.slot < PANEL_END)]
|
|
panel_slots_total = int((PANEL_END - PANEL_START) / pd.Timedelta(SLOT))
|
|
dt = g["time"].diff().dt.total_seconds().dropna()
|
|
dt = dt[dt > 0]
|
|
zero = g["value"] == 0
|
|
runs = (zero != zero.shift()).cumsum()
|
|
zero_runs = zero.groupby(runs).sum()
|
|
panel = g[(g.slot >= PANEL_START) & (g.slot < PANEL_END)]
|
|
panel_slots_total = int((PANEL_END - PANEL_START) / pd.Timedelta(SLOT))
|
|
panel_hours = (PANEL_END - PANEL_START).total_seconds() / 3600
|
|
panel_gaps = panel["time"].diff().dt.total_seconds().dropna() / 3600
|
|
panel_gaps = panel_gaps[panel_gaps > 0]
|
|
median_min = dt[dt < 3600].median() / 60
|
|
cadence_ceiling = 100 * 15 / median_min
|
|
rows.append(
|
|
{
|
|
"sensor_id": sid,
|
|
"name": g["name"].iloc[0],
|
|
"role": "core",
|
|
"n": n,
|
|
"first": g["time"].iloc[0].strftime("%Y-%m-%d %H:%M"),
|
|
"last": g["time"].iloc[-1].strftime("%Y-%m-%d %H:%M"),
|
|
"span_days": span_s / 86400,
|
|
"readings_per_day": n / (span_s / 86400),
|
|
"expected_slots_active": expected_active,
|
|
"observed_slots_active": observed_active,
|
|
"coverage_active_pct": 100 * observed_active / expected_active,
|
|
"panel_slots": len(panel),
|
|
"panel_readings_per_day": len(panel) / 143,
|
|
"coverage_panel_pct": 100 * len(panel) / panel_slots_total,
|
|
"median_cadence_min": median_min,
|
|
"cadence_ceiling_pct": cadence_ceiling,
|
|
"coverage_vs_ceiling_pct": 100 * (len(panel) / panel_slots_total) / (cadence_ceiling / 100),
|
|
"time_in_gaps_gt1h_pct": 100 * panel_gaps[panel_gaps > 1].sum() / panel_hours,
|
|
"time_in_gaps_gt6h_pct": 100 * panel_gaps[panel_gaps > 6].sum() / panel_hours,
|
|
"panel_max_gap_h": float(panel_gaps.max()) if len(panel_gaps) else np.nan,
|
|
"panel_gaps_gt_1h": int((panel_gaps > 1).sum()),
|
|
"panel_gaps_gt_1d": int((panel_gaps > 24).sum()),
|
|
"zero_frac": zero.mean(),
|
|
"lt1_frac": (g["value"] < 1).mean(),
|
|
"lt5_frac": (g["value"] < 5).mean(),
|
|
"mean": g["value"].mean(),
|
|
"median": g["value"].median(),
|
|
"p95": g["value"].quantile(0.95),
|
|
"p99": g["value"].quantile(0.99),
|
|
"max": g["value"].max(),
|
|
"min": g["value"].min(),
|
|
"max_gap_h": dt.max() / 3600,
|
|
"gaps_gt_1h": int((dt > 3600).sum()),
|
|
"gaps_gt_6h": int((dt > 6 * 3600).sum()),
|
|
"gaps_gt_1d": int((dt > 86400).sum()),
|
|
"gaps_gt_7d": int((dt > 7 * 86400).sum()),
|
|
"max_zero_run": int(zero_runs.max()) if len(zero_runs) else 0,
|
|
"zero_runs_ge5": int((zero_runs >= 5).sum()),
|
|
}
|
|
)
|
|
for sid in episodic_ids:
|
|
g = dens[dens.sensor_id == sid].sort_values("time")
|
|
dt = g["time"].diff().dt.total_seconds().dropna()
|
|
rows.append(
|
|
{
|
|
"sensor_id": sid,
|
|
"name": g["name"].iloc[0],
|
|
"role": "episodic",
|
|
"n": len(g),
|
|
"first": g["time"].iloc[0].strftime("%Y-%m-%d %H:%M"),
|
|
"last": g["time"].iloc[-1].strftime("%Y-%m-%d %H:%M"),
|
|
"span_days": (g.time.iloc[-1] - g.time.iloc[0]).total_seconds() / 86400,
|
|
"readings_per_day": np.nan,
|
|
"coverage_active_pct": np.nan,
|
|
"coverage_panel_pct": np.nan,
|
|
"zero_frac": (g.value == 0).mean(),
|
|
"lt1_frac": (g.value < 1).mean(),
|
|
"lt5_frac": (g.value < 5).mean(),
|
|
"mean": g.value.mean(),
|
|
"median": g.value.median(),
|
|
"p95": g.value.quantile(0.95),
|
|
"p99": g.value.quantile(0.99),
|
|
"max": g.value.max(),
|
|
"min": g.value.min(),
|
|
"max_gap_h": (dt.max() / 3600) if len(dt) else np.nan,
|
|
"gaps_gt_1h": int((dt > 3600).sum()) if len(dt) else 0,
|
|
"gaps_gt_6h": int((dt > 6 * 3600).sum()) if len(dt) else 0,
|
|
"gaps_gt_1d": int((dt > 86400).sum()) if len(dt) else 0,
|
|
"gaps_gt_7d": int((dt > 7 * 86400).sum()) if len(dt) else 0,
|
|
}
|
|
)
|
|
coverage = pd.DataFrame(rows)
|
|
coverage.to_csv(TAB / "coverage_metrics.csv", index=False)
|
|
|
|
# ----------------------------------------------------------- duplicates
|
|
dup = {
|
|
"rows_total": int(len(raw)),
|
|
"exact_duplicate_rows": int(raw.duplicated().sum()),
|
|
"duplicate_time_sensor_type": int(raw.duplicated(["time", "sensor_id", "type"]).sum()),
|
|
"duplicate_time_sensor": int(raw.duplicated(["time", "sensor_id"]).sum()),
|
|
"duplicate_sensor_name": int(inventory["name"].duplicated().sum()),
|
|
"density_negative": int((dens["value"] < 0).sum()),
|
|
"density_nan": int(dens["value"].isna().sum()),
|
|
"battery_negative": int((batt["value"] < 0).sum()),
|
|
"battery_over_100": int((batt["value"] > 100).sum()),
|
|
"battery_nan": int(batt["value"].isna().sum()),
|
|
"intervals_lt_60s_core": int(
|
|
(core.groupby("sensor_id")["time"].diff().dt.total_seconds() < 60).sum()
|
|
),
|
|
}
|
|
|
|
# -------------------------------------------------------- battery merge
|
|
bsmall = batt[["time", "sensor_id", "value"]].rename(
|
|
columns={"time": "battery_time", "value": "battery"}
|
|
).sort_values("battery_time")
|
|
dsmall = dens[["time", "sensor_id", "value", "name"]].sort_values("time")
|
|
paired = pd.merge_asof(
|
|
dsmall,
|
|
bsmall,
|
|
left_on="time",
|
|
right_on="battery_time",
|
|
by="sensor_id",
|
|
direction="nearest",
|
|
tolerance=pd.Timedelta("60s"),
|
|
)
|
|
paired["offset_s"] = (paired["battery_time"] - paired["time"]).dt.total_seconds()
|
|
paired["slot"] = paired["time"].dt.floor(SLOT)
|
|
paired["local"] = paired["time"].dt.tz_convert(TZ)
|
|
|
|
battery_rows = []
|
|
for sid, g in paired.groupby("sensor_id"):
|
|
g = g.sort_values("time")
|
|
if g["battery"].isna().all():
|
|
continue
|
|
same = (g["battery"].diff() == 0)
|
|
run_id = (same != same.shift()).cumsum()
|
|
runs = g.groupby(run_id).size()
|
|
battery_rows.append(
|
|
{
|
|
"sensor_id": sid,
|
|
"name": g["name"].iloc[0],
|
|
"n": len(g),
|
|
"battery_n": int(g["battery"].notna().sum()),
|
|
"battery_missing": int(g["battery"].isna().sum()),
|
|
"first": g["battery"].iloc[0],
|
|
"last": g["battery"].iloc[-1],
|
|
"min": g["battery"].min(),
|
|
"p05": g["battery"].quantile(0.05),
|
|
"median": g["battery"].median(),
|
|
"mean": g["battery"].mean(),
|
|
"max": g["battery"].max(),
|
|
"share_lt10": (g["battery"] < 10).mean(),
|
|
"share_lt20": (g["battery"] < 20).mean(),
|
|
"share_zero": (g["battery"] == 0).mean(),
|
|
"stale_share": same.mean(),
|
|
"max_stale_run": int(runs.max()),
|
|
"max_stale_run_days": float(runs.max() * 15 / 60 / 24),
|
|
"n_resets_gt10": int((g["battery"].diff() > 10).sum()),
|
|
"max_drop": float(g["battery"].diff().min()),
|
|
"corr_density": g[["battery", "value"]].corr().iloc[0, 1],
|
|
}
|
|
)
|
|
battery = pd.DataFrame(battery_rows)
|
|
battery.to_csv(TAB / "battery_summary.csv", index=False)
|
|
|
|
# battery vs daily coverage/readings (core period)
|
|
daily_batt_cov = []
|
|
for sid, g in paired[paired.sensor_id.isin(core_ids)].groupby("sensor_id"):
|
|
g = g[(g.time >= PANEL_START) & (g.time < PANEL_END)].copy()
|
|
if g.empty:
|
|
continue
|
|
g["day"] = g["time"].dt.floor("1D")
|
|
daily = g.groupby("day").agg(
|
|
battery=("battery", "median"),
|
|
readings=("battery", "size"),
|
|
density=("value", "mean"),
|
|
)
|
|
daily_batt_cov.append(
|
|
{
|
|
"sensor_id": sid,
|
|
"days": len(daily),
|
|
"corr_battery_readings": daily[["battery", "readings"]].corr().iloc[0, 1],
|
|
"corr_battery_density": daily[["battery", "density"]].corr().iloc[0, 1],
|
|
"median_readings": daily["readings"].median(),
|
|
"median_battery": daily["battery"].median(),
|
|
}
|
|
)
|
|
battery_daily = pd.DataFrame(daily_batt_cov)
|
|
battery_daily.to_csv(TAB / "battery_vs_coverage.csv", index=False)
|
|
|
|
battery_panel = paired[
|
|
paired.sensor_id.isin(core_ids)
|
|
& (paired.time >= PANEL_START)
|
|
& (paired.time < PANEL_END)
|
|
]
|
|
panel_device_ids = [
|
|
s for s in core_ids
|
|
if len(dens[(dens.sensor_id == s) & (dens.time >= PANEL_START)]) > 0
|
|
]
|
|
battery_struct = {
|
|
"dens_rows": int(len(dens)),
|
|
"battery_rows": int(len(batt)),
|
|
"density_rows_with_battery_60s": int(paired.battery.notna().sum()),
|
|
"density_rows_without_battery_60s": int(paired.battery.isna().sum()),
|
|
"core_panel_density_rows": int(len(battery_panel)),
|
|
"core_panel_active_devices": int(len(panel_device_ids)),
|
|
"core_panel_expected_rows": int(len(panel_device_ids) * PANEL_SLOTS_TOTAL),
|
|
"core_panel_battery_coverage_pct": 100 * len(battery_panel) / (len(panel_device_ids) * PANEL_SLOTS_TOTAL),
|
|
}
|
|
|
|
# ------------------------------------------------------ location matrix
|
|
core = core.copy()
|
|
core["location"] = core["sensor_id"].map(LOC_MAP).fillna(core["name"])
|
|
loc_slot = (
|
|
core.groupby(["location", "slot"], as_index=False)
|
|
.agg(value=("value", "mean"), n=("value", "size"))
|
|
)
|
|
mat_all = loc_slot.pivot(index="slot", columns="location", values="value").sort_index()
|
|
locs = list(mat_all.columns)
|
|
panel_mat = mat_all[(mat_all.index >= PANEL_START) & (mat_all.index < PANEL_END)]
|
|
panel_slots_total = int((PANEL_END - PANEL_START) / pd.Timedelta(SLOT))
|
|
panel_mat = panel_mat.reindex(
|
|
pd.date_range(PANEL_START, PANEL_END, freq=SLOT, inclusive="left")
|
|
)
|
|
panel_mat.to_csv(TAB / "panel_15min_matrix.csv")
|
|
|
|
n_report = panel_mat.notna().sum(axis=1)
|
|
report_hist = n_report.value_counts().sort_index()
|
|
report_hist.to_csv(TAB / "locations_per_slot.csv", header=["slots"])
|
|
all8 = panel_mat.dropna()
|
|
all8_slots = len(all8)
|
|
any_slots = int((n_report > 0).sum())
|
|
loc_coverage = panel_mat.notna().mean()
|
|
all8_independence = float(loc_coverage.prod() * panel_slots_total)
|
|
monthly_coverage = (panel_mat.notna().resample("MS").mean() * 100).round(2)
|
|
monthly_coverage.to_csv(TAB / "monthly_coverage.csv")
|
|
|
|
# monthly diagnostics
|
|
month_rows = []
|
|
for per, g in panel_mat.groupby(panel_mat.index.to_period("M")):
|
|
c = g.notna().sum(axis=1)
|
|
cv_m = (g.std(axis=1) / g.mean(axis=1)).where((c >= 2) & (g.mean(axis=1) > 0))
|
|
r_m = g.corr().where(~np.eye(len(locs), dtype=bool)).stack().mean()
|
|
month_rows.append(
|
|
{
|
|
"month": str(per),
|
|
"slots": len(g),
|
|
"all8_slots": int(g.dropna().shape[0]),
|
|
"all8_pct": 100 * g.dropna().shape[0] / len(g),
|
|
"any_pct": 100 * (c > 0).mean(),
|
|
"mean_pairwise_r": r_m,
|
|
"median_cv": cv_m.median(),
|
|
}
|
|
)
|
|
monthly_diag = pd.DataFrame(month_rows)
|
|
monthly_diag.to_csv(TAB / "monthly_diagnostics.csv", index=False)
|
|
|
|
# aggregation sensitivity: how complete is the 8-location panel at coarser bins?
|
|
agg_rows = []
|
|
for freq, minutes in [("15min", 15), ("30min", 30), ("60min", 60), ("120min", 120)]:
|
|
tmp = core.copy()
|
|
tmp["bin"] = tmp["time"].dt.floor(freq)
|
|
b = tmp.pivot_table(index="bin", columns="location", values="value", aggfunc="mean")
|
|
b = b[(b.index >= PANEL_START) & (b.index < PANEL_END)]
|
|
expected = int((PANEL_END - PANEL_START) / pd.Timedelta(minutes=minutes))
|
|
cc = b.notna().sum(axis=1)
|
|
cm = (b.std(axis=1) / b.mean(axis=1)).where((cc >= 2) & (b.mean(axis=1) > 0))
|
|
rr = b.corr().where(~np.eye(len(locs), dtype=bool)).stack().mean() if b.notna().sum().min() > 2 else np.nan
|
|
agg_rows.append(
|
|
{
|
|
"bin_minutes": minutes,
|
|
"expected_bins": expected,
|
|
"bins_with_any": int((cc > 0).sum()),
|
|
"all8_bins": int(b.dropna().shape[0]),
|
|
"all8_pct": 100 * b.dropna().shape[0] / expected,
|
|
"mean_pairwise_r": rr,
|
|
"median_cv": cm.median(),
|
|
}
|
|
)
|
|
agg_sens = pd.DataFrame(agg_rows)
|
|
agg_sens.to_csv(TAB / "aggregation_sensitivity.csv", index=False)
|
|
|
|
# August outage vs battery: strong cross-device confound
|
|
aug_mask = (panel_mat.index.year == 2026) & (panel_mat.index.month == 8)
|
|
aug_cov = (panel_mat[aug_mask].notna().mean() * 100).rename("aug_coverage_pct")
|
|
aug_b = paired[
|
|
(paired.time >= pd.Timestamp("2026-08-01", tz="UTC"))
|
|
& (paired.time < pd.Timestamp("2026-09-01", tz="UTC"))
|
|
& (paired.sensor_id.isin(core_ids))
|
|
].copy()
|
|
aug_b["location"] = aug_b["sensor_id"].map(LOC_MAP).fillna(aug_b["name"])
|
|
aug_batt = aug_b.groupby("location")["battery"].agg(
|
|
aug_battery_median="median",
|
|
aug_battery_p10=lambda s: s.quantile(0.10),
|
|
aug_battery_p90=lambda s: s.quantile(0.90),
|
|
)
|
|
aug_tab = pd.concat([aug_cov, aug_batt], axis=1).dropna()
|
|
aug_tab.to_csv(TAB / "august_battery_coverage.csv")
|
|
aug_r = float(aug_tab["aug_coverage_pct"].corr(aug_tab["aug_battery_median"]))
|
|
|
|
# missingness is not MCAR: compare density on high- vs low-coverage days
|
|
panel_local = panel_mat.copy()
|
|
panel_local.index = panel_local.index.tz_convert(TZ)
|
|
dmean = panel_local.resample("1D").mean()
|
|
dcount = panel_local.resample("1D").count()
|
|
bias_rows = []
|
|
for c in locs:
|
|
hi = dmean.loc[dcount[c] >= 80, c].median()
|
|
lo = dmean.loc[dcount[c] < 50, c].median()
|
|
bias_rows.append(
|
|
{
|
|
"location": c,
|
|
"median_density_high_cov_days": hi,
|
|
"median_density_low_cov_days": lo,
|
|
"ratio_low_to_high": lo / hi if hi else np.nan,
|
|
"days_below_50pct": int((dcount[c] < 50).sum()),
|
|
}
|
|
)
|
|
coverage_bias = pd.DataFrame(bias_rows)
|
|
coverage_bias.to_csv(TAB / "coverage_bias.csv", index=False)
|
|
miss_hour = (panel_local.isna().groupby(panel_local.index.hour).mean() * 100).round(2)
|
|
miss_hour.to_csv(TAB / "missingness_by_hour.csv")
|
|
|
|
# pairwise correlation (pairwise complete observations)
|
|
def pairwise_corr(mat: pd.DataFrame, method: str = "pearson") -> tuple[pd.DataFrame, pd.DataFrame]:
|
|
cols = list(mat.columns)
|
|
r = pd.DataFrame(np.nan, index=cols, columns=cols, dtype=float)
|
|
nn = pd.DataFrame(0, index=cols, columns=cols, dtype=int)
|
|
for a in cols:
|
|
for b in cols:
|
|
sub = mat[[a, b]].dropna()
|
|
nn.loc[a, b] = len(sub)
|
|
if len(sub) >= 3:
|
|
x = sub.iloc[:, 0]
|
|
y = sub.iloc[:, 1]
|
|
if method == "spearman":
|
|
r.loc[a, b] = x.rank().corr(y.rank())
|
|
else:
|
|
r.loc[a, b] = x.corr(y)
|
|
return r, nn
|
|
|
|
pear, pear_n = pairwise_corr(panel_mat, "pearson")
|
|
spear, _ = pairwise_corr(panel_mat, "spearman")
|
|
logp, _ = pairwise_corr(np.log1p(panel_mat), "pearson")
|
|
pear.to_csv(TAB / "correlation_pearson.csv")
|
|
spear.to_csv(TAB / "correlation_spearman.csv")
|
|
logp.to_csv(TAB / "correlation_log1p.csv")
|
|
pear_n.to_csv(TAB / "correlation_pair_n.csv")
|
|
|
|
# detrended correlation: remove location-specific hour-of-week mean
|
|
det = panel_mat.copy()
|
|
how = pd.Series(det.index.tz_convert(TZ).hour * 7 + det.index.tz_convert(TZ).day_of_week, index=det.index)
|
|
for c in det.columns:
|
|
det[c] = det[c] - det[c].groupby(how).transform("mean")
|
|
det_corr, _ = pairwise_corr(det, "pearson")
|
|
det_corr.to_csv(TAB / "correlation_detrended.csv")
|
|
|
|
# daily-level robustness: collapses autocorrelation to ~143 observations
|
|
daily_for_corr = panel_mat.resample("1D").mean()
|
|
daily_cov_for_corr = panel_mat.resample("1D").count()
|
|
daily_for_corr = daily_for_corr.where(daily_cov_for_corr.min(axis=1) >= 0.8 * 96)
|
|
daily_corr, daily_n = pairwise_corr(daily_for_corr, "pearson")
|
|
daily_corr.to_csv(TAB / "correlation_daily.csv")
|
|
|
|
# cross-sensor dispersion at same slot
|
|
cnt = panel_mat.notna().sum(axis=1)
|
|
mean = panel_mat.mean(axis=1)
|
|
std = panel_mat.std(axis=1)
|
|
cv = (std / mean).where((cnt >= 2) & (mean > 0))
|
|
cv_by_hour = cv.groupby(cv.index.tz_convert(TZ).hour).median()
|
|
shares = panel_mat.div(panel_mat.sum(axis=1), axis=0)
|
|
share_cv = (shares.std(axis=1) / shares.mean(axis=1)).where(cnt >= 2)
|
|
cv_by_hour.to_csv(TAB / "cv_by_hour.csv", header=["median_cv"])
|
|
|
|
# daily ratio stability (daily mean per location, panel days)
|
|
daily_mean = panel_mat.resample("1D").mean()
|
|
daily_count = panel_mat.resample("1D").count()
|
|
good_days = daily_count.min(axis=1) >= 0.8 * 96
|
|
daily_good = daily_mean[good_days]
|
|
share = daily_good.div(daily_good.sum(axis=1), axis=0)
|
|
share_cv = share.std() / share.mean()
|
|
log_ratio = np.log(daily_good.replace(0, np.nan))
|
|
pair_sd = []
|
|
for i, a in enumerate(locs):
|
|
for b in locs[i + 1:]:
|
|
pair_sd.append((log_ratio[a] - log_ratio[b]).std())
|
|
ratio_summary = {
|
|
"good_days": int(good_days.sum()),
|
|
"panel_days": int(len(good_days)),
|
|
"median_daily_share_cv": float(share_cv.median()),
|
|
"max_daily_share_cv": float(share_cv.max()),
|
|
"median_pairwise_log_ratio_sd": float(np.nanmedian(pair_sd)),
|
|
"p90_pairwise_log_ratio_sd": float(np.nanpercentile(pair_sd, 90)),
|
|
"share_cv": share_cv.round(4).to_dict(),
|
|
}
|
|
|
|
# hourly / weekday profiles (local time)
|
|
pser = panel_mat.copy()
|
|
pser.index = pser.index.tz_convert(TZ)
|
|
hourly = pser.groupby(pser.index.hour).mean()
|
|
weekday = pser.groupby(pser.index.day_of_week).mean()
|
|
hourly.to_csv(TAB / "hourly_profile.csv")
|
|
weekday.to_csv(TAB / "weekday_profile.csv")
|
|
hourly_norm = hourly / hourly.mean()
|
|
weekend_ratio = {
|
|
c: float(
|
|
weekday.loc[[5, 6], c].mean() / weekday.loc[[0, 1, 2, 3, 4], c].mean()
|
|
)
|
|
for c in locs
|
|
}
|
|
peak_hour = hourly.idxmax().to_dict()
|
|
shape_corr, _ = pairwise_corr(hourly_norm, "pearson")
|
|
off = shape_corr.where(~np.eye(len(locs), dtype=bool)).stack()
|
|
hourly_shape_mean_r = float(off.mean())
|
|
|
|
# zero / low-count distribution
|
|
zero_rows = []
|
|
for sid, g in dens.groupby("sensor_id"):
|
|
zero_rows.append(
|
|
{
|
|
"sensor_id": sid,
|
|
"name": g["name"].iloc[0],
|
|
"n": len(g),
|
|
"zeros": int((g.value == 0).sum()),
|
|
"zero_pct": pct((g.value == 0).mean()),
|
|
"lt1_pct": pct((g.value < 1).mean()),
|
|
"lt5_pct": pct((g.value < 5).mean()),
|
|
"median_nonzero": g.loc[g.value > 0, "value"].median(),
|
|
"mean_nonzero": g.loc[g.value > 0, "value"].mean(),
|
|
"negative": int((g.value < 0).sum()),
|
|
"nan": int(g.value.isna().sum()),
|
|
}
|
|
)
|
|
zero = pd.DataFrame(zero_rows).sort_values("n", ascending=False)
|
|
zero.to_csv(TAB / "zero_lowcount.csv", index=False)
|
|
lowcount = {
|
|
"all_zero_pct": pct((dens.value == 0).mean(), 2),
|
|
"all_lt1_pct": pct((dens.value < 1).mean(), 2),
|
|
"all_lt5_pct": pct((dens.value < 5).mean(), 2),
|
|
"quantization_01": bool(np.allclose((dens.value * 10).round() / 10, dens.value)),
|
|
"share_multiple_0_7": float(np.mean(np.abs(dens.value / 0.7 - (dens.value / 0.7).round()) < 1e-6)),
|
|
}
|
|
|
|
# ------------------------------------------ value grid / quantization
|
|
quant_rows = []
|
|
for (sid, nm), g in dens.groupby(["sensor_id", "name"]):
|
|
v = g["value"]
|
|
mult07 = np.isclose(v / 0.7, np.round(v / 0.7), atol=1e-9)
|
|
integer = np.isclose(v, np.round(v), atol=1e-9)
|
|
mult05 = np.isclose(v / 0.5, np.round(v / 0.5), atol=1e-9)
|
|
mult01 = np.isclose(v / 0.1, np.round(v / 0.1), atol=1e-9)
|
|
if sid == "863f75b0":
|
|
period, quantum = "legacy", 1.0
|
|
elif sid in episodic_ids:
|
|
period, quantum = "episodic", 0.7
|
|
else:
|
|
period, quantum = "current", 0.7
|
|
quant_rows.append(
|
|
{
|
|
"sensor_id": sid,
|
|
"name": nm,
|
|
"period": period,
|
|
"n": len(g),
|
|
"first": g["time"].min().strftime("%Y-%m-%d"),
|
|
"last": g["time"].max().strftime("%Y-%m-%d"),
|
|
"min": v.min(),
|
|
"max": v.max(),
|
|
"n_unique": int(v.nunique()),
|
|
"min_positive": v[v > 0].min() if (v > 0).any() else np.nan,
|
|
"effective_quantum": quantum,
|
|
"pct_mult_0_7": 100 * mult07.mean(),
|
|
"pct_integer": 100 * integer.mean(),
|
|
"pct_mult_0_7_and_integer": 100 * (mult07 & integer).mean(),
|
|
"pct_mult_0_7_only": 100 * (mult07 & ~integer).mean(),
|
|
"pct_integer_only": 100 * (~mult07 & integer).mean(),
|
|
"pct_neither": 100 * (~mult07 & ~integer).mean(),
|
|
"pct_mult_0_5": 100 * mult05.mean(),
|
|
"pct_mult_0_1": 100 * mult01.mean(),
|
|
"mean": v.mean(),
|
|
"median": v.median(),
|
|
"p95": v.quantile(0.95),
|
|
"zero_pct": 100 * (v == 0).mean(),
|
|
}
|
|
)
|
|
quant = pd.DataFrame(quant_rows).sort_values(["period", "name", "first"])
|
|
quant.to_csv(TAB / "quantization_by_device.csv", index=False)
|
|
|
|
old = dens[dens.sensor_id == "863f75b0"]
|
|
new = dens[dens.sensor_id == "f8c19730"]
|
|
scale_rows = []
|
|
for label, g, quantum in [("legacy 863f75b0", old, 1.0), ("new f8c19730", new, 0.7)]:
|
|
scale_rows.append(
|
|
{
|
|
"series": label,
|
|
"n": len(g),
|
|
"first": g.time.min().isoformat(),
|
|
"last": g.time.max().isoformat(),
|
|
"min": g.value.min(),
|
|
"max": g.value.max(),
|
|
"mean": g.value.mean(),
|
|
"median": g.value.median(),
|
|
"p95": g.value.quantile(0.95),
|
|
"p99": g.value.quantile(0.99),
|
|
"zero_pct": 100 * (g.value == 0).mean(),
|
|
"n_unique": int(g.value.nunique()),
|
|
"effective_quantum": quantum,
|
|
}
|
|
)
|
|
scale = pd.DataFrame(scale_rows)
|
|
gap_days = (new.time.min() - old.time.max()).total_seconds() / 86400
|
|
scale["gap_after_days"] = [gap_days, np.nan]
|
|
scale.to_csv(TAB / "density01_scale_comparison.csv", index=False)
|
|
|
|
# ------------------------------------------------- exact cadence / jitter
|
|
cadence_jitter_rows = []
|
|
for sid, g in core.groupby("sensor_id"):
|
|
g = g.sort_values("time")
|
|
dt = g["time"].diff().dt.total_seconds().dropna()
|
|
dt = dt[dt > 0]
|
|
near = dt[(dt > 840) & (dt < 1020)]
|
|
cadence_jitter_rows.append(
|
|
{
|
|
"sensor_id": sid,
|
|
"name": g["name"].iloc[0],
|
|
"kind": "legacy" if sid == "863f75b0" else "current",
|
|
"n_intervals": len(dt),
|
|
"median_all_s": dt.median(),
|
|
"mean_all_s": dt.mean(),
|
|
"sd_all_s": dt.std(),
|
|
"median_near_s": near.median(),
|
|
"mean_near_s": near.mean(),
|
|
"sd_near_s": near.std(),
|
|
"iqr_low_s": near.quantile(0.25),
|
|
"iqr_high_s": near.quantile(0.75),
|
|
"p05_near_s": near.quantile(0.05),
|
|
"p95_near_s": near.quantile(0.95),
|
|
"share_900_5s_pct": 100 * ((near - 900).abs() <= 5).mean(),
|
|
"share_900_30s_pct": 100 * ((near - 900).abs() <= 30).mean(),
|
|
"share_900_60s_pct": 100 * ((near - 900).abs() <= 60).mean(),
|
|
"share_14_17min_pct": 100 * len(near) / len(dt),
|
|
}
|
|
)
|
|
cadence_jitter = pd.DataFrame(cadence_jitter_rows)
|
|
cadence_jitter.to_csv(TAB / "cadence_jitter.csv", index=False)
|
|
|
|
# ------------------------------------------------------ integrity checks
|
|
integrity = pd.DataFrame(
|
|
[
|
|
("exact_duplicate_rows", int(raw.duplicated().sum())),
|
|
("duplicate_time_sensor_type", int(raw.duplicated(["time", "sensor_id", "type"]).sum())),
|
|
("duplicate_time_sensor", int(raw.duplicated(["time", "sensor_id"]).sum())),
|
|
("density_negative", int((dens.value < 0).sum())),
|
|
("density_nan", int(dens.value.isna().sum())),
|
|
("battery_negative", int((batt.value < 0).sum())),
|
|
("battery_over_100", int((batt.value > 100).sum())),
|
|
("battery_nan", int(batt.value.isna().sum())),
|
|
("core_intervals_lt_60s", int((core.groupby("sensor_id")["time"].diff().dt.total_seconds() < 60).sum())),
|
|
],
|
|
columns=["check", "count"],
|
|
)
|
|
integrity.to_csv(TAB / "integrity_checks.csv", index=False)
|
|
|
|
# -------------------------------------------------------------- anomalies
|
|
gap_rows = []
|
|
for sid, g in dens.groupby("sensor_id"):
|
|
g = g.sort_values("time")
|
|
dt = g["time"].diff().dt.total_seconds() / 3600
|
|
for i in np.where(dt > 24)[0][:10]:
|
|
gap_rows.append(
|
|
{
|
|
"sensor_id": sid,
|
|
"name": g["name"].iloc[0],
|
|
"gap_start": g["time"].iloc[i - 1].strftime("%Y-%m-%d %H:%M"),
|
|
"gap_end": g["time"].iloc[i].strftime("%Y-%m-%d %H:%M"),
|
|
"gap_hours": dt.iloc[i],
|
|
}
|
|
)
|
|
gaps = pd.DataFrame(gap_rows).sort_values("gap_hours", ascending=False)
|
|
gaps.to_csv(TAB / "large_gaps.csv", index=False)
|
|
|
|
spike_rows = []
|
|
spike_detail = []
|
|
flat_rows = []
|
|
for sid, g in core.groupby("sensor_id"):
|
|
g = g.sort_values("time").reset_index(drop=True)
|
|
med = g["value"].rolling(9, center=True, min_periods=5).median()
|
|
ratio = g["value"] / (med + 0.7)
|
|
mask = (ratio >= 5) & (g["value"] >= 10)
|
|
n_spike = int(mask.sum())
|
|
spike_rows.append(
|
|
{
|
|
"sensor_id": sid,
|
|
"name": g["name"].iloc[0],
|
|
"spikes_ratio5": n_spike,
|
|
"max_ratio": float(ratio.max()),
|
|
"spike_times": ", ".join(g.loc[mask, "time"].dt.strftime("%Y-%m-%d %H:%M").head(3)),
|
|
}
|
|
)
|
|
for pos in np.where(mask)[0]:
|
|
spike_detail.append(
|
|
{
|
|
"sensor_id": sid,
|
|
"name": g["name"].iloc[0],
|
|
"time": g["time"].iloc[pos].strftime("%Y-%m-%d %H:%M"),
|
|
"value": g["value"].iloc[pos],
|
|
"prev": g["value"].iloc[pos - 1] if pos > 0 else np.nan,
|
|
"next": g["value"].iloc[pos + 1] if pos + 1 < len(g) else np.nan,
|
|
"ratio_to_local_median": ratio.iloc[pos],
|
|
}
|
|
)
|
|
run_id = g["value"].ne(g["value"].shift()).cumsum()
|
|
runs = g.groupby(run_id).agg(
|
|
n=("value", "size"), value=("value", "first"), start=("time", "first")
|
|
)
|
|
longest = runs.loc[runs["n"].idxmax()]
|
|
flat_rows.append(
|
|
{
|
|
"sensor_id": sid,
|
|
"name": g["name"].iloc[0],
|
|
"adjacent_equal_share": (g["value"].diff() == 0).mean(),
|
|
"runs_ge3": int((runs["n"] >= 3).sum()),
|
|
"max_run": int(longest["n"]),
|
|
"max_run_hours": float(longest["n"] * 15 / 60),
|
|
"max_run_value": float(longest["value"]),
|
|
"max_run_start": longest["start"].strftime("%Y-%m-%d %H:%M"),
|
|
"share_in_runs_ge3": float(runs.loc[runs["n"] >= 3, "n"].sum() / len(g)),
|
|
}
|
|
)
|
|
spikes = pd.DataFrame(spike_rows)
|
|
spike_detail = pd.DataFrame(spike_detail).sort_values("ratio_to_local_median", ascending=False)
|
|
flat = pd.DataFrame(flat_rows)
|
|
spikes.to_csv(TAB / "spikes.csv", index=False)
|
|
spike_detail.head(15).to_csv(TAB / "top_spikes.csv", index=False)
|
|
flat.to_csv(TAB / "flatlines.csv", index=False)
|
|
|
|
near_dup = []
|
|
for sid, g in core.groupby("sensor_id"):
|
|
g = g.sort_values("time").reset_index(drop=True)
|
|
dt = g["time"].diff().dt.total_seconds()
|
|
for pos in np.where(dt < 60)[0]:
|
|
near_dup.append(
|
|
{
|
|
"sensor_id": sid,
|
|
"name": g["name"].iloc[0],
|
|
"prev_time": g["time"].iloc[pos - 1].strftime("%Y-%m-%d %H:%M:%S"),
|
|
"time": g["time"].iloc[pos].strftime("%Y-%m-%d %H:%M:%S"),
|
|
"delta_s": dt.iloc[pos],
|
|
"prev_value": g["value"].iloc[pos - 1],
|
|
"value": g["value"].iloc[pos],
|
|
}
|
|
)
|
|
near_dup = pd.DataFrame(near_dup)
|
|
near_dup.to_csv(TAB / "near_duplicate_intervals.csv", index=False)
|
|
|
|
# --------------------------------------------------------------- summary
|
|
summary = {
|
|
"rows": int(len(raw)),
|
|
"density_rows": int(len(dens)),
|
|
"battery_rows": int(len(batt)),
|
|
"sensors_total": int(raw.sensor_id.nunique()),
|
|
"sensors_core": int(len(core_ids)),
|
|
"locations_core": int(len(locs)),
|
|
"sensors_episodic": int(len(episodic_ids)),
|
|
"time_min": raw.time.min().isoformat(),
|
|
"time_max": raw.time.max().isoformat(),
|
|
"panel_start": PANEL_START.isoformat(),
|
|
"panel_end": PANEL_END.isoformat(),
|
|
"panel_days": int((PANEL_END - PANEL_START).days),
|
|
"panel_slots_total": panel_slots_total,
|
|
"all8_slots": all8_slots,
|
|
"all8_pct_panel": pct(all8_slots / panel_slots_total, 2),
|
|
"all8_pct_any": pct(all8_slots / any_slots, 2),
|
|
"all8_expected_independence": round(all8_independence, 1),
|
|
"all8_pct_30min": float(agg_sens.loc[agg_sens.bin_minutes == 30, "all8_pct"].iloc[0]),
|
|
"all8_pct_60min": float(agg_sens.loc[agg_sens.bin_minutes == 60, "all8_pct"].iloc[0]),
|
|
"all8_pct_120min": float(agg_sens.loc[agg_sens.bin_minutes == 120, "all8_pct"].iloc[0]),
|
|
"any_slots": any_slots,
|
|
"locations_per_slot_hist": {int(k): int(v) for k, v in report_hist.items()},
|
|
"monthly_coverage_min_pct": float(monthly_coverage.min().min()),
|
|
"monthly_coverage_median_pct": float(monthly_coverage.stack().median()),
|
|
"all_zero_pct": lowcount["all_zero_pct"],
|
|
"all_lt1_pct": lowcount["all_lt1_pct"],
|
|
"all_lt5_pct": lowcount["all_lt5_pct"],
|
|
"share_multiple_0_7": round(lowcount["share_multiple_0_7"], 4),
|
|
"quantization_summary": {
|
|
"devices_on_0_7_grid": int((np.isclose(quant["pct_mult_0_7"], 100)).sum()),
|
|
"devices_integer_only": int(((np.isclose(quant["pct_integer"], 100)) & (quant["pct_mult_0_7"] < 100)).sum()),
|
|
"devices_with_neither": int((quant["pct_neither"] > 0).sum()),
|
|
"pct_neither_overall": float((quant["pct_neither"] * quant["n"]).sum() / quant["n"].sum()),
|
|
"pct_integer_overall": float((quant["pct_integer"] * quant["n"]).sum() / quant["n"].sum()),
|
|
},
|
|
"density01_old": {
|
|
k: float(v) if isinstance(v, (int, float, np.integer, np.floating)) else str(v)
|
|
for k, v in scale.iloc[0].drop("gap_after_days").items()
|
|
},
|
|
"density01_new": {
|
|
k: float(v) if isinstance(v, (int, float, np.integer, np.floating)) else str(v)
|
|
for k, v in scale.iloc[1].drop("gap_after_days").items()
|
|
},
|
|
"density01_gap_days": float(gap_days),
|
|
"cadence_legacy_median_s": float(
|
|
cadence_jitter.loc[cadence_jitter.sensor_id == "863f75b0", "median_near_s"].iloc[0]
|
|
),
|
|
"cadence_new_density_median_s": float(
|
|
cadence_jitter.loc[cadence_jitter.sensor_id == "f8c19730", "median_near_s"].iloc[0]
|
|
),
|
|
"corr_pearson_mean_offdiag": float(
|
|
pear.where(~np.eye(len(locs), dtype=bool)).stack().mean()
|
|
),
|
|
"corr_pearson_min_offdiag": float(
|
|
pear.where(~np.eye(len(locs), dtype=bool)).stack().min()
|
|
),
|
|
"corr_pearson_max_offdiag": float(
|
|
pear.where(~np.eye(len(locs), dtype=bool)).stack().max()
|
|
),
|
|
"corr_spearman_mean_offdiag": float(
|
|
spear.where(~np.eye(len(locs), dtype=bool)).stack().mean()
|
|
),
|
|
"corr_log1p_mean_offdiag": float(
|
|
logp.where(~np.eye(len(locs), dtype=bool)).stack().mean()
|
|
),
|
|
"corr_detrended_mean_offdiag": float(
|
|
det_corr.where(~np.eye(len(locs), dtype=bool)).stack().mean()
|
|
),
|
|
"corr_daily_mean_offdiag": float(
|
|
daily_corr.where(~np.eye(len(locs), dtype=bool)).stack().mean()
|
|
),
|
|
"daily_corr_days": int(daily_for_corr.dropna().shape[0]),
|
|
"cv_median": float(cv.median()),
|
|
"cv_p90": float(cv.quantile(0.90)),
|
|
"cv_p95": float(cv.quantile(0.95)),
|
|
"share_cv_median": float(share_cv.median()),
|
|
"share_cv_p95": float(share_cv.quantile(0.95)),
|
|
"hourly_shape_mean_r": hourly_shape_mean_r,
|
|
"peak_hour_local": {k: int(v) for k, v in peak_hour.items()},
|
|
"weekend_weekday_ratio": {k: round(v, 3) for k, v in weekend_ratio.items()},
|
|
"ratio_stability": ratio_summary,
|
|
"coverage_bias_ratio_median": float(coverage_bias["ratio_low_to_high"].median()),
|
|
"coverage_bias_ratio_min": float(coverage_bias["ratio_low_to_high"].min()),
|
|
"coverage_bias_ratio_max": float(coverage_bias["ratio_low_to_high"].max()),
|
|
"august_coverage_battery_r": aug_r,
|
|
"duplicates": dup,
|
|
"battery_structural": battery_struct,
|
|
"battery_missing_pct": pct(paired.battery.isna().mean(), 2),
|
|
"battery_median_all": float(paired.battery.median()),
|
|
"battery_share_lt20": pct((paired.battery < 20).mean(), 1),
|
|
"battery_share_zero": pct((paired.battery == 0).mean(), 1),
|
|
"battery_offset_median_s": float(paired.offset_s.median()),
|
|
"battery_offset_p99_s": float(paired.offset_s.quantile(0.99)),
|
|
"near_duplicate_intervals": int(len(near_dup)),
|
|
"max_flatline_run": int(flat["max_run"].max()),
|
|
"max_flatline_run_hours": float(flat["max_run_hours"].max()),
|
|
"max_value": float(dens.value.max()),
|
|
"max_value_sensor": dens.loc[dens.value.idxmax(), "name"],
|
|
"max_value_time": dens.loc[dens.value.idxmax(), "time"].isoformat(),
|
|
}
|
|
with open(TAB / "summary.json", "w") as fh:
|
|
json.dump(summary, fh, indent=2)
|
|
|
|
# ------------------------------------------------------------- figures
|
|
# 1. per-location time series
|
|
daily = panel_mat.resample("1D").mean()
|
|
dcount = panel_mat.resample("1D").count()
|
|
daily = daily.where(dcount >= 80) # >=83% of 96 slots; excludes partial days
|
|
fig, axes = plt.subplots(4, 2, figsize=(11, 8), sharex=True)
|
|
for ax, c in zip(axes.ravel(), locs):
|
|
ax.plot(daily.index, daily[c], color="#4c72b0", lw=0.8, alpha=0.55, label="daily mean")
|
|
roll = daily[c].rolling(7, min_periods=5).mean()
|
|
roll = roll.where(daily[c].notna().rolling(3, min_periods=1).sum() >= 2)
|
|
ax.plot(daily.index, roll, color="#c44e52", lw=1.4, label="7-day mean")
|
|
ax.set_title(c)
|
|
ax.set_ylabel("mean density / 15 min")
|
|
ax.xaxis.set_major_formatter(mdates.DateFormatter("%b"))
|
|
axes[0, 0].legend(fontsize=6, loc="upper left")
|
|
fig.suptitle("Per-location density, 15-min readings aggregated to daily means", y=1.01)
|
|
fig.tight_layout()
|
|
fig.savefig(FIG / "fig_time_series.png", bbox_inches="tight")
|
|
plt.close(fig)
|
|
|
|
# 2. correlation heatmaps
|
|
fig, axes = plt.subplots(1, 3, figsize=(15, 4.6))
|
|
for ax, (m, title) in zip(
|
|
axes,
|
|
[(pear, "Pearson (raw)"), (spear, "Spearman (rank)"), (det_corr, "Pearson (hour-of-week removed)")],
|
|
):
|
|
im = ax.imshow(m.values, vmin=-0.2, vmax=1, cmap="RdYlBu_r")
|
|
ax.set_xticks(range(len(locs)))
|
|
ax.set_yticks(range(len(locs)))
|
|
ax.set_xticklabels(locs, rotation=60, ha="right", fontsize=6)
|
|
if ax is axes[0]:
|
|
ax.set_yticklabels(locs, fontsize=6)
|
|
else:
|
|
ax.set_yticklabels([])
|
|
ax.set_title(title)
|
|
for i in range(len(locs)):
|
|
for j in range(len(locs)):
|
|
v = m.values[i, j]
|
|
ax.text(j, i, f"{v:.2f}", ha="center", va="center", fontsize=5.5,
|
|
color="white" if abs(v) > 0.65 else "black")
|
|
ax.grid(False)
|
|
cax = fig.add_axes([0.915, 0.22, 0.012, 0.56])
|
|
fig.colorbar(im, cax=cax, label="correlation")
|
|
fig.suptitle("Cross-location correlation at matching 15-min slots (pairwise complete)", y=1.02)
|
|
fig.subplots_adjust(wspace=0.10, right=0.90)
|
|
fig.savefig(FIG / "fig_correlation_heatmap.png", bbox_inches="tight")
|
|
plt.close(fig)
|
|
|
|
# 3. missingness heatmap (location x day, % of 96 slots present)
|
|
cov_daily = panel_mat.notna().resample("1D").sum() / 96 * 100
|
|
fig, ax = plt.subplots(figsize=(12, 3.6))
|
|
im = ax.imshow(cov_daily.T.values, aspect="auto", cmap="viridis", vmin=0, vmax=100)
|
|
ax.set_yticks(range(len(locs)))
|
|
ax.set_yticklabels(locs, fontsize=7)
|
|
tick = np.arange(0, len(cov_daily), 14)
|
|
ax.set_xticks(tick)
|
|
ax.set_xticklabels([d.strftime("%d %b") for d in cov_daily.index[tick]], rotation=45, ha="right", fontsize=7)
|
|
ax.set_title("Daily slot coverage: % of 96 possible 15-min slots with a reading")
|
|
ax.grid(False)
|
|
fig.colorbar(im, ax=ax, label="% of 96 slots", pad=0.01)
|
|
fig.tight_layout()
|
|
fig.savefig(FIG / "fig_missingness_heatmap.png", bbox_inches="tight")
|
|
plt.close(fig)
|
|
|
|
# 4. distribution
|
|
fig, axes = plt.subplots(1, 3, figsize=(14, 3.9))
|
|
v = dens["value"]
|
|
axes[0].hist(v, bins=np.arange(0, 101, 0.7), color="#4c72b0")
|
|
axes[0].set_yscale("log")
|
|
axes[0].set_xlabel("density value (15-min count)")
|
|
axes[0].set_ylabel("readings (log scale)")
|
|
axes[0].set_title("Distribution, 0-100")
|
|
axes[0].axvline(1, color="red", ls="--", lw=0.8)
|
|
axes[0].text(1.5, axes[0].get_ylim()[1] * 0.5, "<1", color="red", fontsize=7)
|
|
|
|
nz = np.sort(v[v > 0].values)
|
|
ccdf = 1 - np.arange(len(nz)) / len(nz)
|
|
axes[1].loglog(nz, ccdf, color="#c44e52", lw=1)
|
|
axes[1].set_xlabel("density value > 0")
|
|
axes[1].set_ylabel("P(X >= x)")
|
|
axes[1].set_title("CCDF (log-log)")
|
|
|
|
core_dev = coverage[coverage.role == "core"].sort_values("mean")
|
|
data = [core.loc[core.sensor_id == sid, "value"].values for sid in core_dev.sensor_id]
|
|
short = {
|
|
"863f75b0": "density-01 old",
|
|
"f8c19730": "density-01 new",
|
|
"ba71377f": "soleo",
|
|
"00091a5f": "goetzentuem",
|
|
"8d7eef22": "marahaus",
|
|
"d4cce74b": "neckarufer",
|
|
"0f8519cf": "hagenbuchersee-02",
|
|
"e52c55f3": "hagenbuchersee-01",
|
|
"f4928534": "stadtgalerie",
|
|
}
|
|
labels = [short.get(sid, nm.replace("hn-", "")) for sid, nm in zip(core_dev.sensor_id, core_dev.name)]
|
|
axes[2].boxplot(data, tick_labels=labels, showfliers=False)
|
|
axes[2].set_yscale("symlog", linthresh=1)
|
|
axes[2].set_ylabel("density value (symlog)")
|
|
axes[2].tick_params(axis="x", rotation=55, labelsize=6)
|
|
axes[2].set_title("Per-device distribution")
|
|
fig.tight_layout()
|
|
fig.savefig(FIG / "fig_distribution.png", bbox_inches="tight")
|
|
plt.close(fig)
|
|
|
|
# 4b. quantization grid and density-01 scale discontinuity
|
|
fig, axes = plt.subplots(1, 2, figsize=(13, 4.2), gridspec_kw={"width_ratios": [1.6, 1]})
|
|
q = quant.copy()
|
|
shortq = {
|
|
"863f75b0": "density-01 legacy",
|
|
"f8c19730": "density-01 new",
|
|
"ba71377f": "soleo",
|
|
"00091a5f": "goetzentuem",
|
|
"8d7eef22": "marahaus",
|
|
"d4cce74b": "neckarufer",
|
|
"0f8519cf": "hagenbuchersee-02",
|
|
"e52c55f3": "hagenbuchersee-01",
|
|
"f4928534": "stadtgalerie",
|
|
"d3304b39": "bollwerksturm",
|
|
"2aadbeed": "maker-fair",
|
|
"062fe216": "niklas-01",
|
|
}
|
|
q["label"] = q["sensor_id"].map(shortq).fillna(q["name"])
|
|
q = q.sort_values(["period", "name"]).reset_index(drop=True)
|
|
y = np.arange(len(q))
|
|
left = np.zeros(len(q))
|
|
for col, color, lab in [
|
|
("pct_mult_0_7_only", "#4c72b0", "0.7 grid, non-integer"),
|
|
("pct_mult_0_7_and_integer", "#a6bddb", "0.7 grid and integer"),
|
|
("pct_integer_only", "#dd8452", "integer grid only"),
|
|
("pct_neither", "#c44e52", "neither"),
|
|
]:
|
|
vals = q[col].values
|
|
axes[0].barh(y, vals, left=left, color=color, label=lab, height=0.72)
|
|
left = left + vals
|
|
axes[0].set_yticks(y)
|
|
axes[0].set_yticklabels(q["label"], fontsize=6)
|
|
axes[0].set_xlim(0, 100)
|
|
axes[0].set_xlabel("share of readings (%)")
|
|
axes[0].set_title("Value-grid membership by device")
|
|
axes[0].legend(fontsize=6, loc="lower right", framealpha=0.9)
|
|
axes[0].grid(axis="x", alpha=0.25)
|
|
|
|
old_vals = np.sort(old["value"].unique())
|
|
new_vals = np.sort(new["value"].unique())
|
|
axes[1].plot(old_vals[old_vals <= 7], np.zeros((old_vals <= 7).sum()), "|", ms=18, color="#dd8452", label="legacy: 1.0 grid")
|
|
axes[1].plot(new_vals[new_vals <= 7], np.ones((new_vals <= 7).sum()), "|", ms=18, color="#4c72b0", label="new: 0.7 grid")
|
|
axes[1].set_yticks([0, 1])
|
|
axes[1].set_yticklabels(["863f75b0\n(legacy)", "f8c19730\n(new)"], fontsize=7)
|
|
axes[1].set_xlabel("reported value, 0-7 (zoomed)")
|
|
axes[1].set_xlim(-0.2, 7.2)
|
|
axes[1].set_ylim(-0.5, 1.5)
|
|
axes[1].set_title("Reported value ladders, density-01")
|
|
axes[1].legend(fontsize=6, loc="center right")
|
|
axes[1].grid(axis="x", alpha=0.25)
|
|
fig.tight_layout()
|
|
fig.savefig(FIG / "fig_quantization.png", bbox_inches="tight")
|
|
plt.close(fig)
|
|
|
|
fig, ax = plt.subplots(figsize=(9, 3.4))
|
|
for sid, color, lab in [("863f75b0", "#dd8452", "863f75b0 (legacy, 1.0 grid)"),
|
|
("f8c19730", "#4c72b0", "f8c19730 (new, 0.7 grid)")]:
|
|
g = dens[dens.sensor_id == sid].set_index("time")["value"].resample("1D").agg(["mean", "count"])
|
|
g = g[g["count"] >= 48]
|
|
ax.plot(g.index, g["mean"], color=color, lw=1.0, label=lab)
|
|
gap_start = old["time"].max()
|
|
gap_end = new["time"].min()
|
|
ax.axvspan(gap_start, gap_end, color="grey", alpha=0.18)
|
|
ax.text(gap_start + (gap_end - gap_start) / 2, ax.get_ylim()[1] * 0.55,
|
|
"35.2-day gap", ha="center", fontsize=7, color="dimgrey")
|
|
ax.set_ylabel("daily mean density")
|
|
ax.set_title("density-01 before and after the device replacement")
|
|
ax.legend(fontsize=7)
|
|
fig.tight_layout()
|
|
fig.savefig(FIG / "fig_density01_swap.png", bbox_inches="tight")
|
|
plt.close(fig)
|
|
|
|
# 5. cadence
|
|
fig, axes = plt.subplots(1, 2, figsize=(11, 3.8))
|
|
axes[0].hist(intervals["minutes"], bins=np.arange(0, 61, 1), color="#55a868")
|
|
axes[0].set_yscale("log")
|
|
axes[0].set_xlabel("inter-reading interval (minutes)")
|
|
axes[0].set_ylabel("count (log scale)")
|
|
axes[0].set_title("Cadence, all core devices, intervals <=60 min")
|
|
cad = cadence.set_index("name")
|
|
y = np.arange(len(cad))
|
|
axes[1].errorbar(
|
|
cad["median_min"], y,
|
|
xerr=[cad["median_min"] - cad["p25_min"], cad["p75_min"] - cad["median_min"]],
|
|
fmt="o", color="#4c72b0", ecolor="#999999", capsize=2,
|
|
)
|
|
for yy, (_, r) in zip(y, cad.iterrows()):
|
|
axes[1].text(16.6, yy, f"{100*r['share_28_34min']:.0f}% at ~30 min", va="center", fontsize=5.5)
|
|
axes[1].set_yticks(y)
|
|
axes[1].set_yticklabels(cad.index, fontsize=6)
|
|
axes[1].set_xlim(14.8, 21.5)
|
|
axes[1].set_xlabel("median interval, min (bar = IQR)")
|
|
axes[1].set_title("Nominal cadence by device")
|
|
fig.tight_layout()
|
|
fig.savefig(FIG / "fig_cadence.png", bbox_inches="tight")
|
|
plt.close(fig)
|
|
|
|
# 6. hourly / weekday profile
|
|
weekday_norm = weekday / weekday.mean()
|
|
fig, axes = plt.subplots(1, 2, figsize=(12, 3.8))
|
|
for c in locs:
|
|
axes[0].plot(hourly_norm.index, hourly_norm[c], marker="o", ms=2, lw=1, label=c)
|
|
axes[1].plot(weekday_norm.index, weekday_norm[c], marker="o", ms=2, lw=1, label=c)
|
|
axes[0].set_xlabel("hour of day (Europe/Berlin)")
|
|
axes[0].set_ylabel("mean / location mean")
|
|
axes[0].set_title("Normalized hourly profile")
|
|
axes[0].set_xticks(range(0, 24, 2))
|
|
axes[1].set_xlabel("day of week")
|
|
axes[1].set_ylabel("mean / location mean")
|
|
axes[1].set_title("Normalized weekday profile")
|
|
axes[1].set_xticks(range(7))
|
|
axes[1].set_xticklabels(["Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun"])
|
|
axes[1].legend(fontsize=5, ncol=2)
|
|
fig.tight_layout()
|
|
fig.savefig(FIG / "fig_hourly_profile.png", bbox_inches="tight")
|
|
plt.close(fig)
|
|
|
|
# 7. battery
|
|
fig, axes = plt.subplots(1, 3, figsize=(15, 3.8))
|
|
for sid, g in paired[paired.sensor_id.isin(core_ids)].groupby("sensor_id"):
|
|
g = g.set_index("time").sort_index()
|
|
daily_b = g["battery"].resample("1D").median()
|
|
axes[0].plot(daily_b.index, daily_b.values, lw=0.8, label=DEVICE_LABEL.get(sid, g["name"].iloc[0]))
|
|
axes[0].set_ylabel("battery (%)")
|
|
axes[0].set_title("Daily median battery level")
|
|
axes[0].legend(fontsize=5, ncol=2)
|
|
axes[0].xaxis.set_major_formatter(mdates.DateFormatter("%b"))
|
|
axes[1].hist(paired["battery"].dropna(), bins=np.arange(0, 102, 2), color="#8172b3")
|
|
axes[1].set_xlabel("battery (%)")
|
|
axes[1].set_ylabel("readings")
|
|
axes[1].set_title("Battery distribution (all paired readings)")
|
|
short_aug = {
|
|
"density-01": "density-01", "hn-goetzentuem-01": "goetz",
|
|
"hn-hagenbuchersee-01": "hagen-01", "hn-hagenbuchersee-02": "hagen-02",
|
|
"hn-marahaus-01": "marahaus", "hn-neckarufer-01": "neckar",
|
|
"hn-soleo-01": "soleo", "hn-stadtgalerie-01": "stadt",
|
|
}
|
|
axes[2].scatter(aug_tab["aug_battery_median"], aug_tab["aug_coverage_pct"], color="#4c72b0")
|
|
for lab, row in aug_tab.iterrows():
|
|
axes[2].annotate(short_aug.get(lab, lab), (row["aug_battery_median"], row["aug_coverage_pct"]),
|
|
fontsize=5.5, xytext=(2, 2), textcoords="offset points")
|
|
axes[2].set_xlabel("August median battery (%)")
|
|
axes[2].set_ylabel("August slot coverage (%)")
|
|
axes[2].set_title(f"August coverage vs battery (r = {aug_r:.2f})")
|
|
fig.tight_layout()
|
|
fig.savefig(FIG / "fig_battery.png", bbox_inches="tight")
|
|
plt.close(fig)
|
|
|
|
print(json.dumps(summary, indent=2))
|
|
print("\nWrote tables to", TAB)
|
|
print("Wrote figures to", FIG)
|
|
|
|
|
|
if __name__ == "__main__":
|
|
main()
|