r""" build_oncor_network_definition.py ---------------------------------- Populates the Oncor Network Definition CSV from FCC ULS bulk DAT files. Inputs: D:\FCC_ULS\ -- EN.dat, HD.dat, FR.dat, PA.dat, LO.dat, AN.dat Output: D:\oncor\Oncor_Network_Definition_populated.csv Rules: - Site A must be an active Oncor/TXU/affiliated license (entity filter applied) - Site B (receiver) can be ANY active licensee in the ULS database - Both ends must have lowest transmit frequency in 5925-7125 MHz (6 GHz band) - One row per PA.dat record (directional): Site A = transmitter, Site B = receiver - Each physical path produces two rows: A->B and B->A - entity_name = Site A entity, receiver_entity_name = Site B entity - Bearings and path length calculated from lat/lon (EPSG:4326), NOT from ULS PA fields - Columns flagged "to be added later" are left blank - Path ID format: ONC-0001, ONC-0002, ... """ import sys import math import pandas as pd from pathlib import Path # ── Configuration ────────────────────────────────────────────────────────────── ULS_DIR = Path(r"D:\FCC_ULS") OUTPUT_FILE = Path(r"D:\oncor\Oncor_Network_Definition_populated.csv") # Exact entity names to include (case-insensitive, full match) ENTITY_NAMES_EXACT = { "oncor license holdings company, llc", "oncor license holdings company llc", # no-comma variant "hcl / oncor", "hcl /oncor", # no-space variant } ACTIVE_STATUS = {"A"} MICROWAVE_SERVICES = { "MG", "MW", "CF", "TP", "TI", "WU", "YD", "GB", } FREQ_MIN_MHZ = 5925.0 FREQ_MAX_MHZ = 7125.0 LICENSE_STATUS_LABELS = { "A": "Active", "C": "Cancelled", "E": "Expired", "T": "Terminated", "L": "Pending", "P": "Pending", } # ── Column schemas ───────────────────────────────────────────────────────────── EN_COLS = { 0: "record_type", 1: "unique_system_identifier", 2: "uls_file_number", 3: "ebf_number", 4: "call_sign", 5: "entity_type", 6: "licensee_id", 7: "entity_name", 8: "first_name", 9: "mi", 10: "last_name", 11: "suffix", 12: "phone", 13: "fax", 14: "email", 15: "street_address", 16: "city", 17: "state", 18: "zip_code", 19: "po_box", 20: "attention_line", 21: "sgin", 22: "frn", 23: "applicant_type_code", 24: "applicant_type_other", 25: "status_code", 26: "status_date", } HD_COLS = { 0: "record_type", 1: "unique_system_identifier", 2: "uls_file_number", 3: "ebf_number", 4: "call_sign", 5: "license_status", 6: "radio_service_code", 7: "grant_date", 8: "expired_date", 9: "cancellation_date", 10: "eligibility_rule_num", 11: "applicant_type_code_reserved", 42: "effective_date", 43: "last_action_date", } FR_COLS = { 0: "record_type", 1: "unique_system_identifier", 2: "uls_file_number", 3: "ebf_number", 4: "call_sign", 5: "frequency_action_performed", 6: "location_number", 7: "antenna_number", 8: "class_station_code", 9: "op_altitude_code", 10: "frequency_assigned", 11: "frequency_upper_band", 12: "frequency_offset", 13: "frequency_channel_block", 14: "emission_code", 15: "digital_modulation_code", 16: "frequency_number", 17: "status_code", 18: "status_date", } PA_COLS = { 0: "record_type", 1: "unique_system_identifier", 2: "uls_file_number", 3: "ebf_number", 4: "call_sign", 5: "location_action_performed", 6: "location_number", 7: "path_number", 8: "transmit_location_number", 9: "transmit_antenna_number", 10: "receiver_location_number", 11: "receiver_antenna_number", 12: "mas_dms_ind", 13: "path_azimuth", 14: "elevation_angle", 15: "receiver_height_above_ground", 16: "receiver_call_sign", 17: "path_loss", 18: "receiver_threshold_criteria", 19: "receiver_threshold_value", 20: "passive_receiver_indicator", 21: "passive_coordinates_text", 22: "status_code", 23: "status_date", 24: "path_loss_approach", 25: "path_number_system_id", } LO_COLS = { 0: "record_type", 1: "unique_system_identifier", 2: "uls_file_number", 3: "ebf_number", 4: "call_sign", 5: "location_action_performed", 6: "location_type_code", 7: "location_description", 8: "location_number", 9: "site_status_code", 10: "corresponding_fixed_location", 11: "location_address", 12: "location_city", 13: "location_county", 14: "location_state", 15: "radius_of_operation", 16: "area_of_operation_code", 17: "clearance_indicator", 18: "ground_elevation", 19: "lat_degrees", 20: "lat_minutes", 21: "lat_seconds", 22: "lat_direction", 23: "lon_degrees", 24: "lon_minutes", 25: "lon_seconds", 26: "lon_direction", 27: "max_latitudinal_deg", 28: "max_longitudinal_deg", 29: "nepa", 30: "quiet_zone_notification_date", 31: "tower_registration_number", 32: "height_of_support_structure", 33: "overall_height_above_ground", 34: "structure_type", 35: "airport_id", 36: "location_name", 42: "status_code", 43: "status_date", } AN_COLS = { 0: "record_type", 1: "unique_system_identifier", 2: "uls_file_number", 3: "ebf_number", 4: "call_sign", 5: "antenna_action_performed", 6: "location_number", 7: "antenna_number", 8: "licensee_antenna_number", 9: "antenna_type", 10: "height_to_tip", 11: "height_center_raat", 12: "antenna_make", 13: "antenna_model", 14: "tilt", 15: "polarization_code", 16: "beamwidth", 17: "gain", 18: "azimuth", 19: "height_above_avg_terrain", 20: "diversity_height", 21: "diversity_gain", 22: "diversity_beam", 31: "passive_repeater_indicator", 32: "back_to_back_passive_indicator", 33: "status_code", 34: "status_date", } # ── Output columns ───────────────────────────────────────────────────────────── OUTPUT_COLS = [ "path_number", "entity_name", "receiver_entity_name", "unique_system_identifier", "Path_ID", "ven_file", "kml_file", "A_Call_Sign", "A_Latitude", "A_Longitude", "a_meanelev (M)", "a_raat (M)", "a_site_name", "b_Call_Sign", "b_Latitude", "b_Longitude", "b_meanelev (M)", "b_raat (M)", "b_site_name", "dat_a_output", "dat_b_output", "Path_Length (KM)", "State", "Path_Overall_Status", "A Site Elev AMSL (M)", "A Site Main Ant Height AGL (M)", "A Site Diversity Ant Height AGL (M)", "A Site Main Ant Height AMSL (M)", "A Site Diversity Ant Height AMSL (M)", "A Main Antenna Make", "A Main Antenna Model", "A Diversity Antenna Make", "A Diversity Antenna Model", "B Site Elev AMSL (M)", "B Site Main Ant Height AGL (M)", "B Site Diversity Ant Height AGL (M)", "B Site Main Ant Height AMSL (M)", "B Site Diversity Ant Height AMSL (M)", "B Main Antenna Make", "B Main Antenna Model", "B Diversity Antenna Make", "B Diversity Antenna Model", "Path Length (Miles)", "USGS 1M Points", "GLO 30 Points", "Total Points", "A Main Antenna WC File", "A Diversity Antenna WC", "B Main Antenna WC", "B Diversity Antenna WC File", "AIntFunction", "ATerrFunction", "channels", "bandwidth (MHz)", "Site_A _xmt_fr_frequency_assigned_MHz", "Site_B _xmt_fr_frequency_assigned_MHz", "Site_A_rx_ant_gain_dBi", "Site_A_Rx_tilt_deg", "Site_B_rx_ant_gain_dBi", "Site_B_Rx_tilt_deg", "A_to_B_Bearing_deg", "B_to_A_Bearing_deg", "bandwidth_modulation", "freq_note", ] # ── Helpers ──────────────────────────────────────────────────────────────────── def load_dat(filepath: Path, col_map: dict, label: str) -> pd.DataFrame: if not filepath.exists(): print(f" [WARN] {label} not found: {filepath}") return pd.DataFrame() print(f" Loading {label}...") try: df = pd.read_csv( filepath, sep="|", header=None, dtype=str, encoding="latin-1", on_bad_lines="skip", low_memory=False, ) rename = {k: v for k, v in col_map.items() if k < len(df.columns)} df.rename(columns=rename, inplace=True) print(f" → {len(df):,} rows") return df except Exception as e: print(f" [ERROR] {label}: {e}") return pd.DataFrame() def dms_to_dd(deg, mins, secs, direction): try: dd = float(deg) + float(mins) / 60.0 + float(secs) / 3600.0 if str(direction).strip().upper() in ("S", "W"): dd = -dd return round(dd, 6) except Exception: return None def haversine_km(lat1, lon1, lat2, lon2): try: R = 6371.0 phi1, phi2 = math.radians(lat1), math.radians(lat2) dphi = math.radians(lat2 - lat1) dlam = math.radians(lon2 - lon1) a = math.sin(dphi/2)**2 + math.cos(phi1)*math.cos(phi2)*math.sin(dlam/2)**2 return round(2 * R * math.asin(math.sqrt(a)), 4) except Exception: return None def calc_bearing(lat1, lon1, lat2, lon2): try: phi1, phi2 = math.radians(lat1), math.radians(lat2) dlam = math.radians(lon2 - lon1) x = math.sin(dlam) * math.cos(phi2) y = math.cos(phi1)*math.sin(phi2) - math.sin(phi1)*math.cos(phi2)*math.cos(dlam) return round(math.degrees(math.atan2(x, y)) % 360, 2) except Exception: return None def overall_status(a_status, b_status): a, b = str(a_status).strip().upper(), str(b_status).strip().upper() if a == "A" and b == "A": return "Active" if a in ("C","T") or b in ("C","T"): return "Cancelled/Terminated" if a == "E" or b == "E": return "Expired" return "Mixed" # ── Main ─────────────────────────────────────────────────────────────────────── def main(): print(f"\n{'='*60}") print(" Oncor Network Definition Builder") print(f"{'='*60}\n") # ── Step 1: Load full EN — two indexes: Oncor entities + all call signs ─── print("Step 1: Loading EN.dat...") en = load_dat(ULS_DIR / "EN.dat", EN_COLS, "EN") if en.empty: sys.exit("ERROR: EN.dat required.") en["call_sign"] = en["call_sign"].str.strip().str.upper() en["entity_name"] = en["entity_name"].str.strip() # Oncor/TXU target UIDs mask = en["entity_name"].str.lower().str.strip().isin(ENTITY_NAMES_EXACT) en_oncor = en[mask].copy() oncor_ids = set(en_oncor["unique_system_identifier"].dropna().unique()) print(f" → {len(oncor_ids):,} Oncor/TXU unique system identifiers") # Full uid -> entity name map (ALL entities, for receiver lookup) en_uid_to_entity = ( en.drop_duplicates(subset=["unique_system_identifier"]) .set_index("unique_system_identifier")["entity_name"] ) # Full call_sign -> uid map (ALL entities) en_cs_to_uid = ( en.drop_duplicates(subset=["call_sign"]) .set_index("call_sign")["unique_system_identifier"] ) print(f" → {len(en_uid_to_entity):,} total entities in EN.dat\n") # ── Step 2: Load full HD — two scopes: Oncor active + all active microwave ─ print("Step 2: Loading HD.dat...") hd = load_dat(ULS_DIR / "HD.dat", HD_COLS, "HD") hd["call_sign"] = hd["call_sign"].str.strip().str.upper() hd["license_status"] = hd["license_status"].str.strip().str.upper() # Oncor active microwave licenses (Site A candidates) hd_oncor = hd[ hd["unique_system_identifier"].isin(oncor_ids) & hd["radio_service_code"].isin(MICROWAVE_SERVICES) & hd["license_status"].isin(ACTIVE_STATUS) ].copy() oncor_active_ids = set(hd_oncor["unique_system_identifier"].dropna().unique()) print(f" → {len(hd_oncor):,} active Oncor/TXU microwave licenses") # ALL active microwave licenses (for Site B lookups) hd_all_mw = hd[ hd["radio_service_code"].isin(MICROWAVE_SERVICES) & hd["license_status"].isin(ACTIVE_STATUS) ].copy() all_active_mw_ids = set(hd_all_mw["unique_system_identifier"].dropna().unique()) print(f" → {len(hd_all_mw):,} total active microwave licenses (all entities)\n") # Build HD index for field lookups (all active microwave) hd_idx = ( hd_all_mw.drop_duplicates(subset=["unique_system_identifier"]) .set_index("unique_system_identifier") ) uid_to_cs = hd_idx["call_sign"].str.strip().str.upper() cs_to_uid = {v: k for k, v in uid_to_cs.items()} def hd_field(uid, field): try: val = hd_idx.loc[uid, field] if isinstance(val, pd.Series): val = val.iloc[0] return str(val).strip() if pd.notna(val) else "" except KeyError: return "" # ── Step 3: Load LO for ALL active microwave UIDs ───────────────────────── print("Step 3: Loading LO.dat...") lo = load_dat(ULS_DIR / "LO.dat", LO_COLS, "LO") lo_t = lo[lo["unique_system_identifier"].isin(all_active_mw_ids)].copy() lo_t["location_number"] = pd.to_numeric(lo_t["location_number"], errors="coerce") def compute_coords(row): lat = dms_to_dd(row.get("lat_degrees"), row.get("lat_minutes"), row.get("lat_seconds"), row.get("lat_direction")) lon = dms_to_dd(row.get("lon_degrees"), row.get("lon_minutes"), row.get("lon_seconds"), row.get("lon_direction")) return pd.Series({"latitude": lat, "longitude": lon}) lo_t[["latitude","longitude"]] = lo_t.apply(compute_coords, axis=1) lo_idx = lo_t.set_index(["unique_system_identifier","location_number"]).sort_index() print(f" → {lo_t['latitude'].notna().sum():,} sites with coordinates\n") def lo_get(uid, loc, field): try: row = lo_idx.loc[(uid, loc)] if isinstance(row, pd.DataFrame): row = row.iloc[0] val = row.get(field, "") return str(val).strip() if pd.notna(val) else "" except KeyError: return "" def lo_coord(uid, loc): try: row = lo_idx.loc[(uid, loc)] if isinstance(row, pd.DataFrame): row = row.iloc[0] return row.get("latitude"), row.get("longitude") except KeyError: return None, None # ── Step 4: Load AN for ALL active microwave UIDs ───────────────────────── print("Step 4: Loading AN.dat...") an = load_dat(ULS_DIR / "AN.dat", AN_COLS, "AN") an_t = an[an["unique_system_identifier"].isin(all_active_mw_ids)].copy() an_t["location_number"] = pd.to_numeric(an_t["location_number"], errors="coerce") an_t["antenna_number"] = pd.to_numeric(an_t["antenna_number"], errors="coerce") an_t = an_t.sort_values(["unique_system_identifier","location_number","antenna_number"]) print() def get_antenna(uid, loc, diversity=False): sub = an_t[ (an_t["unique_system_identifier"] == uid) & (an_t["location_number"] == loc) ] if sub.empty: return None, None, None, None if diversity: sub = sub[sub["antenna_type"].str.upper().str.strip() == "D"] if sub.empty: return None, None, None, None row = sub.iloc[0] return ( str(row.get("antenna_make","")).strip() or None, str(row.get("antenna_model","")).strip() or None, str(row.get("height_center_raat","")).strip() or None, str(row.get("diversity_height","")).strip() or None, ) def get_rx_gain_tilt(uid, loc): sub = an_t[ (an_t["unique_system_identifier"] == uid) & (an_t["location_number"] == loc) ] if sub.empty: return None, None row = sub.iloc[0] return ( str(row.get("gain","")).strip() or None, str(row.get("tilt","")).strip() or None, ) # ── Step 5: Load FR for ALL active microwave UIDs + build 6 GHz filter ──── print("Step 5: Loading FR.dat and building 6 GHz filter...") fr = load_dat(ULS_DIR / "FR.dat", FR_COLS, "FR") fr_t = fr[fr["unique_system_identifier"].isin(all_active_mw_ids)].copy() fr_t["frequency_assigned"] = pd.to_numeric(fr_t["frequency_assigned"], errors="coerce") fr_t["location_number"] = pd.to_numeric(fr_t["location_number"], errors="coerce") # Lowest transmit freq per (uid, location_number) fr_min_idx = ( fr_t.groupby(["unique_system_identifier","location_number"])["frequency_assigned"] .min() ) def lowest_freq(uid, loc): try: val = fr_min_idx.loc[(uid, loc)] return round(float(val), 4) if pd.notna(val) else None except KeyError: return None def in_band(uid, loc): f = lowest_freq(uid, loc) return f is not None and FREQ_MIN_MHZ <= f <= FREQ_MAX_MHZ def get_emission(uid, loc): sub = fr_t[ (fr_t["unique_system_identifier"] == uid) & (fr_t["location_number"] == loc) ].dropna(subset=["emission_code"]) if sub.empty: return None, None row = sub.sort_values("frequency_assigned").iloc[0] return ( str(row.get("emission_code","")).strip() or None, str(row.get("frequency_upper_band","")).strip() or None, ) print(f" → Frequency index built for {len(fr_min_idx):,} (uid, location) combinations\n") # ── Step 6: Load PA — Oncor as transmitter, any active entity as receiver ── print("Step 6: Loading PA.dat and applying filters...") pa = load_dat(ULS_DIR / "PA.dat", PA_COLS, "PA") # Site A: Oncor/TXU active licenses only pa_t = pa[pa["unique_system_identifier"].isin(oncor_active_ids)].copy() pa_t["transmit_location_number"] = pd.to_numeric(pa_t["transmit_location_number"], errors="coerce") pa_t["receiver_location_number"] = pd.to_numeric(pa_t["receiver_location_number"], errors="coerce") pa_t["call_sign"] = pa_t["call_sign"].str.strip().str.upper() pa_t["receiver_call_sign"] = pa_t["receiver_call_sign"].str.strip().str.upper() total_pa = len(pa_t) # Resolve receiver call sign -> uid using the full EN call sign map # Try HD first (faster), fall back to EN def resolve_receiver_uid(cs): uid = cs_to_uid.get(cs) if uid: return uid # Fall back to EN map for receivers not in active MW HD return en_cs_to_uid.get(cs) pa_t["uid_rcv"] = pa_t["receiver_call_sign"].map(resolve_receiver_uid) # Require receiver uid to resolve and be an active microwave license pa_t = pa_t[ pa_t["uid_rcv"].notna() & pa_t["uid_rcv"].isin(all_active_mw_ids) ].copy() print(f" → {total_pa:,} Oncor PA records") print(f" → {len(pa_t):,} after requiring receiver to be an active microwave licensee") # 6 GHz band filter: both ends must be in band pa_t = pa_t[ pa_t.apply( lambda r: in_band(r["unique_system_identifier"], r["transmit_location_number"]) and in_band(r["uid_rcv"], r["receiver_location_number"]), axis=1 ) ].copy() print(f" → {len(pa_t):,} after 6 GHz band filter ({FREQ_MIN_MHZ:.0f}-{FREQ_MAX_MHZ:.0f} MHz, both ends)\n") # ── Step 6b: Deduplicate — keep only one direction per call sign pair ───── # For any pair where both A->B and B->A exist, keep the first occurrence. seen_pairs = set() deduped_rows = [] for _, row in pa_t.iterrows(): cs_a = str(row["call_sign"]).strip().upper() cs_b = str(row["receiver_call_sign"]).strip().upper() key = frozenset([cs_a, cs_b]) if key not in seen_pairs: seen_pairs.add(key) deduped_rows.append(row) pa_t = pd.DataFrame(deduped_rows).reset_index(drop=True) print(f" → {len(pa_t):,} paths after deduplication (one direction per pair)\n") # ── Step 7: Build output rows ───────────────────────────────────────────── print("Step 7: Building network definition rows...") rows = [] missing_coords = [] # Oncor entity name index en_oncor_idx = ( en_oncor.drop_duplicates(subset=["unique_system_identifier"]) .set_index("unique_system_identifier")["entity_name"] ) for path_num, (_, pa_row) in enumerate(pa_t.iterrows(), start=1): path_id = f"ONC_{path_num:04d}" uid_a = pa_row["unique_system_identifier"] uid_b = pa_row["uid_rcv"] loc_a = pa_row["transmit_location_number"] loc_b = pa_row["receiver_location_number"] cs_a = str(uid_to_cs.get(uid_a, pa_row.get("call_sign",""))).strip() cs_b = str(uid_to_cs.get(uid_b, pa_row.get("receiver_call_sign",""))).strip() # Entity names — Site A from Oncor EN subset, Site B from full EN a_entity = str(en_oncor_idx.get(uid_a, en_uid_to_entity.get(uid_a, ""))).strip() b_entity = str(en_uid_to_entity.get(uid_b, "")).strip() # Coordinates a_lat, a_lon = lo_coord(uid_a, loc_a) b_lat, b_lon = lo_coord(uid_b, loc_b) # Geometry — calculated only, never from ULS PA fields path_km = path_mi = a_to_b = b_to_a = None if all(v is not None for v in [a_lat, a_lon, b_lat, b_lon]): path_km = haversine_km(a_lat, a_lon, b_lat, b_lon) path_mi = round(path_km * 0.621371, 4) if path_km else None a_to_b = calc_bearing(a_lat, a_lon, b_lat, b_lon) b_to_a = calc_bearing(b_lat, b_lon, a_lat, a_lon) else: missing_coords.append(path_id) # Location fields a_elev = lo_get(uid_a, loc_a, "ground_elevation") b_elev = lo_get(uid_b, loc_b, "ground_elevation") state = lo_get(uid_a, loc_a, "location_state") or lo_get(uid_b, loc_b, "location_state") # Site names: location_name (col 36) -> location_description (col 7) # -> city + state as last resort def site_name(uid, loc): name = lo_get(uid, loc, "location_name") if name and name not in ("T", "F", "M", ""): return name desc = lo_get(uid, loc, "location_description") if desc and desc not in ("T", "F", "M", ""): return desc city = lo_get(uid, loc, "location_city") state_lo = lo_get(uid, loc, "location_state") if city or state_lo: return ", ".join(filter(None, [city, state_lo])) return "" a_site_name = site_name(uid_a, loc_a) b_site_name = site_name(uid_b, loc_b) # Antenna fields a_ant_make, a_ant_model, a_raat, _ = get_antenna(uid_a, loc_a, diversity=False) a_div_make, a_div_model, _, a_div_h = get_antenna(uid_a, loc_a, diversity=True) b_ant_make, b_ant_model, b_raat, _ = get_antenna(uid_b, loc_b, diversity=False) b_div_make, b_div_model, _, b_div_h = get_antenna(uid_b, loc_b, diversity=True) # If B-side antenna make/model is missing, assume same as A-side # Heights and RAAT are site-specific — leave blank if unavailable if b_ant_make is None: b_ant_make = a_ant_make if b_ant_model is None: b_ant_model = a_ant_model if b_div_make is None: b_div_make = a_div_make if b_div_model is None: b_div_model = a_div_model a_rx_gain, a_rx_tilt = get_rx_gain_tilt(uid_a, loc_a) b_rx_gain, b_rx_tilt = get_rx_gain_tilt(uid_b, loc_b) def amsl(elev, agl): try: return round(float(elev) + float(agl), 2) except Exception: return None a_status_code = hd_field(uid_a, "license_status") b_status_code = hd_field(uid_b, "license_status") a_xmt_freq = lowest_freq(uid_a, loc_a) b_xmt_freq = lowest_freq(uid_b, loc_b) a_emission, a_bw = get_emission(uid_a, loc_a) rows.append({ "path_number": path_id, "entity_name": a_entity, "Path_ID": f"{cs_a}_{cs_b}", "receiver_entity_name": b_entity, "unique_system_identifier": uid_a, "ven_file": "", "kml_file": "", "A_Call_Sign": cs_a, "A_Latitude": a_lat, "A_Longitude": a_lon, "a_meanelev (M)": a_elev, "a_raat (M)": a_raat, "a_site_name": a_site_name, "b_Call_Sign": cs_b, "b_Latitude": b_lat, "b_Longitude": b_lon, "b_meanelev (M)": b_elev, "b_raat (M)": b_raat, "b_site_name": b_site_name, "dat_a_output": "", "dat_b_output": "", "Path_Length (KM)": path_km, "State": state, "Path_Overall_Status": overall_status(a_status_code, b_status_code), "A Site Elev AMSL (M)": a_elev, "A Site Main Ant Height AGL (M)": a_raat, "A Site Diversity Ant Height AGL (M)": a_div_h, "A Site Main Ant Height AMSL (M)": amsl(a_elev, a_raat), "A Site Diversity Ant Height AMSL (M)": amsl(a_elev, a_div_h), "A Main Antenna Make": a_ant_make, "A Main Antenna Model": a_ant_model, "A Diversity Antenna Make": a_div_make, "A Diversity Antenna Model": a_div_model, "B Site Elev AMSL (M)": b_elev, "B Site Main Ant Height AGL (M)": b_raat, "B Site Diversity Ant Height AGL (M)": b_div_h, "B Site Main Ant Height AMSL (M)": amsl(b_elev, b_raat), "B Site Diversity Ant Height AMSL (M)": amsl(b_elev, b_div_h), "B Main Antenna Make": b_ant_make, "B Main Antenna Model": b_ant_model, "B Diversity Antenna Make": b_div_make, "B Diversity Antenna Model": b_div_model, "Path Length (Miles)": path_mi, "USGS 1M Points": "", "GLO 30 Points": "", "Total Points": "", "A Main Antenna WC File": "", "A Diversity Antenna WC": "", "B Main Antenna WC": "", "B Diversity Antenna WC File": "", "AIntFunction": "", "ATerrFunction": "", "channels": "", "bandwidth (MHz)": a_bw, "Site_A _xmt_fr_frequency_assigned_MHz": a_xmt_freq, "Site_B _xmt_fr_frequency_assigned_MHz": b_xmt_freq, "Site_A_rx_ant_gain_dBi": a_rx_gain, "Site_A_Rx_tilt_deg": a_rx_tilt, "Site_B_rx_ant_gain_dBi": b_rx_gain, "Site_B_Rx_tilt_deg": b_rx_tilt, "A_to_B_Bearing_deg": a_to_b, "B_to_A_Bearing_deg": b_to_a, "bandwidth_modulation": a_emission, "freq_note": "", }) # ── Step 8: Write output ────────────────────────────────────────────────── print("Step 8: Writing output...") out_df = pd.DataFrame(rows, columns=OUTPUT_COLS) OUTPUT_FILE.parent.mkdir(parents=True, exist_ok=True) out_df.to_csv(OUTPUT_FILE, index=False) print(f"\n{'='*60}") print(" SUMMARY") print(f"{'='*60}") print(f" Frequency band: {FREQ_MIN_MHZ:.0f}-{FREQ_MAX_MHZ:.0f} MHz") print(f" License filter: Active only") print(f" Total directional paths: {len(out_df):,}") print(f" Paths with coordinates: {out_df['A_Latitude'].notna().sum():,}") print(f" Paths missing coords: {len(missing_coords):,}") print(f" Unique receiver entities: {out_df['receiver_entity_name'].nunique():,}") if missing_coords: print(f"\n Paths missing coordinates:") for pid in missing_coords[:20]: print(f" {pid}") print(f"\n Output: {OUTPUT_FILE}") print(f"{'='*60}\n") if __name__ == "__main__": main()