# populate_FMR_Summary_BellMobility.py # # Reads all A and B merge CSV files from d:\bell_mobility\complete\ # and populates KinderMorgan_FMR_Summary.csv with: # - Max RFM 20 dB BEL for each path/direction # - Risk level: Low (<5 dB), Medium (5-15 dB), High (>15 dB) # # Usage: # python d:\bell_mobility\populate_FMR_Summary_BellMobility.py # # Output: # d:\bell_mobility\KinderMorgan_FMR_Summary.csv import os import numpy as np import pandas as pd from datetime import datetime COMPLETE_DIR = r'd:\KinderMorgan\complete' OUTPUT_FILE = r'd:\KinderMorgan\KinderMorgan_FMR_Summary.csv' ROSETTA_PATH = r'd:\KinderMorgan\KinderMorgan_Rosetta_populated.csv' COL_RFM = 'RFM 20 dB BEL' def risk_level(fmr): if fmr is None or pd.isna(fmr): return 'N/A' if fmr < 5: return 'Low' elif fmr <= 15: return 'Medium' else: return 'High' def read_csv_safe(filepath): try: return pd.read_csv(filepath, encoding='utf-8') except UnicodeDecodeError: return pd.read_csv(filepath, encoding='latin-1') # --------------------------------------------------------------------------- print("=" * 60) print(" KinderMorgan FMR Summary") print(f" Started: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}") print("=" * 60) print() # Load Rosetta Stone to get the list of paths and merge file names rosetta = pd.read_csv(ROSETTA_PATH) print(f"Paths in Rosetta Stone: {len(rosetta)}") print() rows = [] missing = [] for _, pathrow in rosetta.iterrows(): subfolder = str(pathrow['Subfolder']) amergefilename = str(pathrow['A_Rcvr_Merged_File']) bmergefilename = str(pathrow['B_Rcvr_Merged_File']) path_dir = os.path.join(COMPLETE_DIR, subfolder) a_file = os.path.join(path_dir, amergefilename + '.csv') b_file = os.path.join(path_dir, bmergefilename + '.csv') for label, filepath in [(amergefilename + '_20dBBEL', a_file), (bmergefilename + '_20dBBEL', b_file)]: if not os.path.isfile(filepath): print(f" MISSING: {filepath}") missing.append(label) rows.append({'Path and BEL': label, 'Fade Margin Reduction': '', 'Risk (H,M,L)': 'FILE NOT FOUND'}) continue try: df = read_csv_safe(filepath) if COL_RFM not in df.columns: print(f" NO RFM COLUMN: {filepath}") rows.append({'Path and BEL': label, 'Fade Margin Reduction': '', 'Risk (H,M,L)': 'NO RFM COLUMN', 'Total Buildings': '', 'Min FMR': '', 'Max FMR': '', 'Mean FMR': '', 'Median FMR': '', 'Std Dev FMR': ''}) continue max_fmr = df[COL_RFM].max() min_fmr = df[COL_RFM].min() mean_fmr = df[COL_RFM].mean() median_fmr = df[COL_RFM].median() std_fmr = df[COL_RFM].std() risk = risk_level(max_fmr) n_buildings = len(df) rows.append({'Path and BEL': label, 'Fade Margin Reduction': round(max_fmr, 1), 'Risk (H,M,L)': risk, 'Total Buildings': n_buildings, 'Min FMR': round(min_fmr, 1), 'Max FMR': round(max_fmr, 1), 'Mean FMR': round(mean_fmr, 2), 'Median FMR': round(median_fmr, 1), 'Std Dev FMR': round(std_fmr, 2)}) print(f" {label:45s} Max FMR: {max_fmr:6.1f} dB Risk: {risk}") except Exception as e: print(f" ERROR reading {filepath}: {e}") rows.append({'Path and BEL': label, 'Fade Margin Reduction': '', 'Risk (H,M,L)': f'ERROR: {e}'}) # --------------------------------------------------------------------------- COLS = ['Path and BEL', 'Fade Margin Reduction', 'Risk (H,M,L)', 'Total Buildings', 'Min FMR', 'Max FMR', 'Mean FMR', 'Median FMR', 'Std Dev FMR'] detail = pd.DataFrame(rows, columns=COLS) # --- count risk levels --- n_low = sum(1 for r in rows if r['Risk (H,M,L)'] == 'Low') n_medium = sum(1 for r in rows if r['Risk (H,M,L)'] == 'Medium') n_high = sum(1 for r in rows if r['Risk (H,M,L)'] == 'High') n_missing = sum(1 for r in rows if r['Risk (H,M,L)'] not in ('Low', 'Medium', 'High')) total_bldg = sum(r.get('Total Buildings', 0) or 0 for r in rows) # --- write output manually to match exact format --- # Summary block has no column header; detail block has column header # 4 blank rows separate the two blocks with open(OUTPUT_FILE, 'w', encoding='utf-8') as f: # summary block (no header) f.write(f'SUMMARY,,,,,,,,\n') f.write(f'Total Low Paths,{n_low},,,,,,,\n') f.write(f'Total Medium Paths,{n_medium},,,,,,,\n') f.write(f'Total High Paths,{n_high},,,,,,,\n') f.write(f'Total Missing Paths,{n_missing},,,,,,,\n') f.write(f'Total Paths,{n_low+n_medium+n_high+n_missing},,,,,,,\n') f.write(f'Total Buildings,{total_bldg},,,,,,,\n') # overall stats across all valid detail rows valid_fmr = [float(r['Fade Margin Reduction']) for r in rows if r.get('Fade Margin Reduction') not in ('', None) and str(r.get('Fade Margin Reduction')).replace('.','',1).lstrip('-').isdigit()] if valid_fmr: overall_min = round(float(np.min(valid_fmr)), 1) overall_max = round(float(np.max(valid_fmr)), 1) overall_mean = round(float(np.mean(valid_fmr)), 2) overall_median = round(float(np.median(valid_fmr)), 1) overall_std = round(float(np.std(valid_fmr)), 2) else: overall_min = overall_max = overall_mean = overall_median = overall_std = '' f.write(f'Min FMR (all paths),{overall_min},,,,,,,\n') f.write(f'Max FMR (all paths),{overall_max},,,,,,,\n') f.write(f'Mean FMR (all paths),{overall_mean},,,,,,,\n') f.write(f'Median FMR (all paths),{overall_median},,,,,,,\n') f.write(f'Std Dev FMR (all paths),{overall_std},,,,,,,\n') # 4 blank rows for _ in range(4): f.write(',,,,,,,,\n') # detail block with column header # Risk column header contains commas so must be quoted header = ','.join([f'"{c}"' if ',' in c else c for c in COLS]) f.write(header + '\n') for r in rows: line = ','.join([ str(r.get('Path and BEL', '')), str(r.get('Fade Margin Reduction', '')), str(r.get('Risk (H,M,L)', '')), str(r.get('Total Buildings', '')), str(r.get('Min FMR', '')), str(r.get('Max FMR', '')), str(r.get('Mean FMR', '')), str(r.get('Median FMR', '')), str(r.get('Std Dev FMR', '')), ]) f.write(line + '\n') print() print("=" * 60) print(f" Total Low Paths : {n_low}") print(f" Total Medium Paths : {n_medium}") print(f" Total High Paths : {n_high}") print(f" Total Missing Paths : {n_missing}") print(f" Total Paths : {n_low + n_medium + n_high + n_missing}") print(f" Total rows written : {len(rows)} (detail rows)") print(f" Output saved to : {OUTPUT_FILE}") print("=" * 60)