#Configured for Oncor Project on Version 19 # Version 19: Switched from SCE File Naming Convention to ATT Rosetta Stone CSV. # Rosetta Stone file: d:\att\ATT_Rosetta_populated.csv # Lookup is now by path_num value (not row offset). Path numbers start at 1000. # Full input file paths are read directly from the Rosetta Stone (no path construction needed). # Version 18: Configured for Dallas Path POC. # Version 17: Changes header for plots to header = 0. Fixes a small bug. # Version 16: Added autocopy to G Drive on JA computer. # Version 15: Modifications for new FNC file v8 and 2nd60paths. # Builds from Version 14. Deletes columns and renames files and charts to site names. # Includes both A & B directions, merge data and produce histogram charts. # Also adds columns for site names in addition to call signs. # CONFIGURATION / REQUIRED CHANGES # ROSETTA STONE FILE IS AT d:\att\ATT_Rosetta_populated.csv (now includes A_Int_File, B_Int_File, A_Ter_File, B_Ter_File columns) # Configure the output copy folder if needed. Line ~379 # ------------------------------------------------------------------------------------- #Preambles import os os.system('cls') import sys #use this later to tag the output file from datetime import datetime now = datetime.now() filetime = now.strftime("%Y%m%d") #----------------------------------------------------------------------------------------- print() print("----------------------- S T A R T I N G N O W----------------------") print("Start Time is:", now) print() # get the row number and lookup the file name and path for in and out. print("Note: Oncor Rosetta Stone CSV is being used (d:\\oncor\\Oncor_Rosetta_populated.csv).") print() print("This program will produce A, B Merged and six charts for a path.") input("Press ENTER to Continue...") #get the path number, and watch the row number to make sure they don't need an offset pathnumber = input("Enter the PATH number from the FILE NAMING CONVENTIONS SPREADSHEET: ").strip() # ----------------------------------------------------------------------------------- # Load the ATT Rosetta Stone CSV import pandas as pd filenaming = pd.read_csv(r'd:\oncor\Oncor_Rosetta_populated.csv') # Lookup by path_num value (not row offset - path numbers start at 1000) pathrow = filenaming[filenaming['path_num'] == pathnumber] if pathrow.empty: print(f"ERROR: Path number {pathnumber} not found in the Rosetta Stone file. Stopping.") sys.exit() pathrow = pathrow.iloc[0] # get the single matched row as a Series AReceiver = str(pathrow['A_Rcvr_Call_Sign']) BReceiver = str(pathrow['B_Rcvr_Call_Sign']) amergefilename = str(pathrow['A_Rcvr_Merged_File']) bmergefilename = str(pathrow['B_Rcvr_Merged_File']) print() # ----------------------------------------------------------------------------------- drive = str(pathrow['Drive']) filefolder = str(pathrow['Folder']) subfolder = str(pathrow['Subfolder']) # Build input file paths from Subfolder name - consistent naming convention base = r'd:\oncor\complete' + '\\' + subfolder + '\\' + subfolder AIntInputfile = base + '_A_Interference_Obstruction.csv' BIntInputfile = base + '_B_Interference_Obstruction.csv' ATerrInputfile = base + '_A_Terrain_Obstruction.csv' BTerrInputfile = base + '_B_Terrain_Obstruction.csv' filenameIn = subfolder print() print(f"Here's what about to run AReceiver: ", AReceiver) print(f" BReceiver: ", BReceiver) print() print(f" A Interference Input File : ", AIntInputfile) print(f" B Interference Input File : ", BIntInputfile) print() print(f" A Terrain Input File : ", ATerrInputfile) print(f" B Terrain Input File : ", BTerrInputfile) print() print(f" A merge Output file : ", amergefilename) print(f" B merge Output file : ", bmergefilename) print() print(f"If you need to change file names or paths, do it in the ATT Rosetta Stone file.") print() print(f"The output files will be saved to d:\\att\\complete\\{subfolder}") print() # ask if correct user_input = ("Is this correct? (y/n)? : ") # Print a message for confirmatoin user_input = input("Do you want to continue? (y/n): ") if user_input.lower() == "y": print("Continuing the program...") else: print(" Stopping it now fat fingers! ") print() sys.exit() # ---------------------------------------------------------------------------------------------- # ABOVE WORKS TO CAPTURE THE FILENAMES and INPUT DATA for ANY Path. #------------------------------------------------------------------------------------------------ import pandas as pd # Read the CSV files (try UTF-8 first, fall back to latin-1) def read_csv_safe(filepath, **kwargs): try: return pd.read_csv(filepath, encoding='utf-8', **kwargs) except UnicodeDecodeError: return pd.read_csv(filepath, encoding='latin-1', **kwargs) #A-direction Ainterference_data = read_csv_safe(AIntInputfile) Aobstruction_data = read_csv_safe(ATerrInputfile) #B-direction Binterference_data = read_csv_safe(BIntInputfile) Bobstruction_data = read_csv_safe(BTerrInputfile) print() print("Click through the charts, they are saved automatically.") # do an inner join Amerged_data = pd.merge(Ainterference_data, Aobstruction_data, left_on='Building', right_on='Int Building', how='inner') Bmerged_data = pd.merge(Binterference_data, Bobstruction_data, left_on='Building', right_on='Int Building', how='inner') # Create a 'Site Check' column by subtracting IntBuilding from Building Amerged_data['Site_Check'] = Amerged_data['Building'] - Amerged_data['Int Building'] Bmerged_data['Site_Check'] = Bmerged_data['Building'] - Bmerged_data['Int Building'] # Check for discrepancies in the 'Site Check' column discrepanciesA = Amerged_data[Amerged_data['Site_Check'] != 0] if not discrepanciesA.empty: print(f"A Direction # discrepancies found in {len(discrepanciesA)} rows. Please verify the data alignment.") discrepanciesB = Bmerged_data[Bmerged_data['Site_Check'] != 0] if not discrepanciesB.empty: print(f"B Direction # discrepancies found in {len(discrepanciesB)} rows. Please verify the data alignment.") print() print("...............checked for errors.............") # Create a 'Composite Interference' column by subtracting Obstruction Loss from Raw Interference Amerged_data['Composite_Interference'] = Amerged_data['Raw Interference (dBm)'] - Amerged_data['Obstruction Loss (dB)'] Bmerged_data['Composite_Interference'] = Bmerged_data['Raw Interference (dBm)'] - Bmerged_data['Obstruction Loss (dB)'] # --------------------------------------------------- R F M --------------------------------------------------------- # Now, recalculate RFM with the composite interference data. import math import numpy as np # do the A direction RFM calculations. Amerged_data['RFM 10 dB BEL'] = np.round(10 * np.log10((np.power(10, (-10 + Amerged_data['Composite_Interference']) / 10)) + np.power(10, -94 / 10)) + 94, decimals=1) Amerged_data['RFM 20 dB BEL'] = np.round(10 * np.log10((np.power(10, (-20 + Amerged_data['Composite_Interference']) / 10)) + np.power(10, -94 / 10)) + 94, decimals=1) Amerged_data['RFM 30 dB BEL'] = np.round(10 * np.log10((np.power(10, (-30 + Amerged_data['Composite_Interference']) / 10)) + np.power(10, -94 / 10)) + 94, decimals=1) # -------------------------------------------------- Now the b-side ------------------------------------------------------ Bmerged_data['RFM 10 dB BEL'] = np.round(10 * np.log10((np.power(10, (-10 + Bmerged_data['Composite_Interference']) / 10)) + np.power(10, -94 / 10)) + 94, decimals=1) Bmerged_data['RFM 20 dB BEL'] = np.round(10 * np.log10((np.power(10, (-20 + Bmerged_data['Composite_Interference']) / 10)) + np.power(10, -94 / 10)) + 94, decimals=1) Bmerged_data['RFM 30 dB BEL'] = np.round(10 * np.log10((np.power(10, (-30 + Bmerged_data['Composite_Interference']) / 10)) + np.power(10, -94 / 10)) + 94, decimals=1) #--------------------------------------------add the adaptive modulation calculations in new columns---------------- #A-Direction Amerged_data['1024 QAM'] = np.round(100 * ((31557600 - ((315.576 * (10 ** (Amerged_data['RFM 20 dB BEL'] / 10))) / (10 ** ((3 * 0) / 10)))) / 31557600), 5) Amerged_data['512 QAM'] = np.round(100 * ((31557600 - ((315.576 * (10 ** (Amerged_data['RFM 20 dB BEL'] / 10))) / (10 ** ((3 * 1) / 10)))) / 31557600), 5) Amerged_data['256 QAM'] = np.round(100 * ((31557600 - ((315.576 * (10 ** (Amerged_data['RFM 20 dB BEL'] / 10))) / (10 ** ((3 * 2) / 10)))) / 31557600), 5) Amerged_data['128 QAM'] = np.round(100 * ((31557600 - ((315.576 * (10 ** (Amerged_data['RFM 20 dB BEL'] / 10))) / (10 ** ((3 * 3) / 10)))) / 31557600), 5) Amerged_data['64 QAM'] = np.round(100 * ((31557600 - ((315.576 * (10 ** (Amerged_data['RFM 20 dB BEL'] / 10))) / (10 ** ((3 * 4) / 10)))) / 31557600), 5) Amerged_data['32 QAM'] = np.round(100 * ((31557600 - ((315.576 * (10 ** (Amerged_data['RFM 20 dB BEL'] / 10))) / (10 ** ((3 * 5) / 10)))) / 31557600), 5) Amerged_data['16 QAM'] = np.round(100 * ((31557600 - ((315.576 * (10 ** (Amerged_data['RFM 20 dB BEL'] / 10))) / (10 ** ((3 * 6) / 10)))) / 31557600), 5) Amerged_data['4 QAM'] = np.round(100 * ((31557600 - ((315.576 * (10 ** (Amerged_data['RFM 20 dB BEL'] / 10))) / (10 ** ((3 * 8) / 10)))) / 31557600), 5) #B-Direction Bmerged_data['1024 QAM'] = np.round(100 * ((31557600 - ((315.576 * (10 ** (Bmerged_data['RFM 20 dB BEL'] / 10))) / (10 ** ((3 * 0) / 10)))) / 31557600), 5) Bmerged_data['512 QAM'] = np.round(100 * ((31557600 - ((315.576 * (10 ** (Bmerged_data['RFM 20 dB BEL'] / 10))) / (10 ** ((3 * 1) / 10)))) / 31557600), 5) Bmerged_data['256 QAM'] = np.round(100 * ((31557600 - ((315.576 * (10 ** (Bmerged_data['RFM 20 dB BEL'] / 10))) / (10 ** ((3 * 2) / 10)))) / 31557600), 5) Bmerged_data['128 QAM'] = np.round(100 * ((31557600 - ((315.576 * (10 ** (Bmerged_data['RFM 20 dB BEL'] / 10))) / (10 ** ((3 * 3) / 10)))) / 31557600), 5) Bmerged_data['64 QAM'] = np.round(100 * ((31557600 - ((315.576 * (10 ** (Bmerged_data['RFM 20 dB BEL'] / 10))) / (10 ** ((3 * 4) / 10)))) / 31557600), 5) Bmerged_data['32 QAM'] = np.round(100 * ((31557600 - ((315.576 * (10 ** (Bmerged_data['RFM 20 dB BEL'] / 10))) / (10 ** ((3 * 5) / 10)))) / 31557600), 5) Bmerged_data['16 QAM'] = np.round(100 * ((31557600 - ((315.576 * (10 ** (Bmerged_data['RFM 20 dB BEL'] / 10))) / (10 ** ((3 * 6) / 10)))) / 31557600), 5) Bmerged_data['4 QAM'] = np.round(100 * ((31557600 - ((315.576 * (10 ** (Bmerged_data['RFM 20 dB BEL'] / 10))) / (10 ** ((3 * 8) / 10)))) / 31557600), 5) # Note section above b-direction, made change do 'Bmerged_data' on 12/16. Does not affect RFM or charts. It did fix a bug. #sort by building Amerged_data = Amerged_data.sort_values(by='Building') Bmerged_data = Bmerged_data.sort_values(by='Building') #-----this is the new section for version 12. # Delete the columns the customer doesn't care about. Amerged_data.pop('Raw Interference (dBm)') Amerged_data.pop('Path') Amerged_data.pop('Int Building') Amerged_data.pop('Near Rx Site') Amerged_data.pop('Far Tx Site') Amerged_data.pop('Path Clearance Description') Amerged_data.pop('Obstruction Loss (dB)') Amerged_data.pop('Site_Check') Bmerged_data.pop('Raw Interference (dBm)') Bmerged_data.pop('Path') Bmerged_data.pop('Int Building') Bmerged_data.pop('Near Rx Site') Bmerged_data.pop('Far Tx Site') Bmerged_data.pop('Path Clearance Description') Bmerged_data.pop('Obstruction Loss (dB)') Bmerged_data.pop('Site_Check') # ---------------------------------------insert a column with the site names ----------------------------------- # the variables for amergefilneame and bmergefilename have already been collected in cols 21,22 # DataFrame.insert(loc, column, value, allow_duplicates=_NoDefault.no_default) is the syntax # Site Name and Direction comes from the input CSV - no insert needed # ----------------------------------------save the results to CSV files ---------------------------------------- # Output directory: d:\att\complete\ (directories already exist) outputpath = r'd:\oncor\complete' + '\\' + subfolder print() print("................saving files to", outputpath, "...................") os.chdir(outputpath) # Save the merged data directly into the path directory Amerged_data.to_csv(amergefilename + '.csv', index=False) Bmerged_data.to_csv(bmergefilename + '.csv', index=False) filenameoutA = amergefilename + '.csv' filenameoutB = bmergefilename + '.csv' print() print("...........................starting the charts...........................") #------------------------------------------ MAKE CHARTS ----------------------------------- # this code will produce graph files for histograms from the composite interference import numpy as np import matplotlib.pyplot as plt import pandas as pd import seaborn as sns #define the function #-------------------------------------- A Direction------------------------------------- def Amakecharts(colnumber, titlename, filename): # Read the CSV file (adjust the filename as needed) Adatafromcsv = pd.read_csv(filenameoutA, sep=',', header=0, usecols=[colnumber], index_col=None, dtype=float ) # made change above to header equal zero row #round the data to the nearest integer Adatafromcsv = round(Adatafromcsv,0) # to replicate GK's rounding technique, use the COUNT/COUNTIF function # Plot a bar chart plt.figure(figsize=(14,8)) values, bins, bars = plt.hist(Adatafromcsv.iloc[:,0], bins=30, range=(0,30), rwidth=0.75, color='steelblue', edgecolor='black') plt.yscale('log') plt.ylabel('Number of Interference Paths') plt.xlabel('Fade Margin Reduction (db)') plt.title(titlename) plt.bar_label(bars, fontsize=10, color='black') #plt.show() plt.savefig(filename) #comment line out below to skip display. also in b charts #plt.show() #Body of Code - this works Amakecharts (4, amergefilename + ' 10dB Building Entry Loss ' + filenameIn + ' Path Number ' + str(pathnumber), filenameIn + '_A_10dBBEL') Amakecharts (5, amergefilename + ' 20dB Building Entry Loss ' + filenameIn + ' Path Number ' + str(pathnumber), filenameIn + '_A_20dBBEL') Amakecharts (6, amergefilename + ' 30dB Building Entry Loss ' + filenameIn + ' Path Number ' + str(pathnumber), filenameIn + '_A_30dBBEL') # ----------------------------------------------------------------------------------------------- print() # ---------------------------------B Direction --------------------------------------------- def Bmakecharts(colnumber, titlename, filename): # Read the CSV file (adjust the filename as needed) Bdatafromcsv = pd.read_csv(filenameoutB, sep=',', header=0, usecols=[colnumber], index_col=None, dtype=float ) # made change above to header equal zero row #round the data to the nearest integer Bdatafromcsv = round(Bdatafromcsv,0) # to replicate GK's rounding technique, use the COUNT/COUNTIF function # Plot a bar chart plt.figure(figsize=(14,8)) values, bins, bars = plt.hist(Bdatafromcsv.iloc[:,0], bins=30, range=(0,30), rwidth=0.75, color='forestgreen', edgecolor='black') plt.yscale('log') plt.ylabel('Number of Interference Paths') plt.xlabel('Fade Margin Reduction (db)') plt.title(titlename) plt.bar_label(bars, fontsize=10, color='black') #plt.show() plt.savefig(filename) #comment out below and above in acharts for speed. #plt.show() #B-side charts Bmakecharts (4, bmergefilename + ' 10dB Building Entry Loss ' + filenameIn + ' Path Number ' + str(pathnumber), filenameIn + '_B_10dBBEL') Bmakecharts (5, bmergefilename + ' 20dB Building Entry Loss ' + filenameIn + ' Path Number ' + str(pathnumber), filenameIn + '_B_20dBBEL') Bmakecharts (6, bmergefilename + ' 30dB Building Entry Loss ' + filenameIn + ' Path Number ' + str(pathnumber), filenameIn + '_B_30dBBEL') # --------------------------------- 3D COMBINED BEL CHART --------------------------------- def make_3d_bel_chart(mergefilename, titlename, filename): import matplotlib.patches as mpatches df = pd.read_csv(mergefilename) counts_10 = [0] * 30 counts_20 = [0] * 30 counts_30 = [0] * 30 for col, counts in [('RFM 10 dB BEL', counts_10), ('RFM 20 dB BEL', counts_20), ('RFM 30 dB BEL', counts_30)]: vals = df[col].dropna().round(0).astype(int) for v in vals: if 0 <= v < 30: counts[v] += 1 DATA = { '10 dB BEL': counts_10, '20 dB BEL': counts_20, '30 dB BEL': counts_30, } SERIES_COLORS = [ {'front': '#1d4ed8', 'side': '#3b82f6', 'top': '#93c5fd'}, {'front': '#15803d', 'side': '#22c55e', 'top': '#86efac'}, {'front': '#b91c1c', 'side': '#ef4444', 'top': '#fca5a5'}, ] BAR_WIDTH = 0.22 BAR_GAP = 0.03 GROUP_PAD = 0.12 DX = 0.20 DZ = 0.10 DEPTH_STEP = 0.22 BG_COLOR = '#f8fafc' series_names = list(DATA.keys()) series_data = list(DATA.values()) n_series = len(series_data) n_bins = 30 log_max = np.log10(10000) group_step = BAR_WIDTH * n_series + BAR_GAP * (n_series - 1) + GROUP_PAD def log_height(val): if val <= 0: return 0.0 return np.log10(max(val, 1)) def draw_bar(ax, x0, y0, bh, colors, zorder): kw = dict(linewidth=0.3, edgecolor='#00000033') ax.fill([x0, x0 + BAR_WIDTH, x0 + BAR_WIDTH, x0], [y0, y0, y0 + bh, y0 + bh], color=colors['front'], zorder=zorder, **kw) ax.fill([x0 + BAR_WIDTH, x0 + BAR_WIDTH + DX, x0 + BAR_WIDTH + DX, x0 + BAR_WIDTH], [y0, y0 + DZ, y0 + bh + DZ, y0 + bh], color=colors['side'], zorder=zorder + 1, **kw) ax.fill([x0, x0 + BAR_WIDTH, x0 + BAR_WIDTH + DX, x0 + DX], [y0 + bh, y0 + bh, y0 + bh + DZ, y0 + bh + DZ], color=colors['top'], zorder=zorder + 2, **kw) fig, ax = plt.subplots(figsize=(16, 8)) ax.set_facecolor(BG_COLOR) fig.patch.set_facecolor(BG_COLOR) for ix in range(n_bins - 1, -1, -1): for s in range(n_series - 1, -1, -1): val = series_data[s][ix] if val <= 0: continue bh = log_height(val) depth_offset = (n_series - 1 - s) * DEPTH_STEP x0 = ix * group_step + s * (BAR_WIDTH + BAR_GAP) + depth_offset y0 = (n_series - 1 - s) * DZ * 0.4 zorder = ix * 10 + s * 3 + 1 draw_bar(ax, x0, y0, bh, SERIES_COLORS[s], zorder) grid_labels = ['1', '10', '100', '1k', '10k'] for exp in range(int(log_max) + 1): ax.axhline(exp, color='#bbbbbb', linewidth=0.5, zorder=0, linestyle='--') ax.text(-0.6, exp, grid_labels[exp], va='center', ha='right', fontsize=9, color='#555') mid_series = n_series // 2 for ix in range(n_bins): depth_offset = (n_series - 1 - mid_series) * DEPTH_STEP x_center = (ix * group_step + mid_series * (BAR_WIDTH + BAR_GAP) + BAR_WIDTH / 2 + depth_offset) ax.text(x_center, -0.22, str(ix), ha='center', va='top', fontsize=8, color='#555') ax.set_xlim(-1.2, n_bins * group_step + 1.0) ax.set_ylim(-0.45, log_max + 0.7) ax.axis('off') ax.set_title(titlename, fontsize=12, color='#333', pad=16) ax.text(-1.1, log_max / 2, 'Number of Interference Paths (log scale)', rotation=90, va='center', ha='right', fontsize=10, color='#555') ax.text(n_bins * group_step / 2, -0.48, 'Fade Margin Reduction (dB)', ha='center', va='top', fontsize=10, color='#555') legend_patches = [ mpatches.Patch(facecolor=SERIES_COLORS[i]['side'], edgecolor='#33333355', label=series_names[i]) for i in range(n_series) ] ax.legend(handles=legend_patches, loc='upper right', fontsize=10, framealpha=0.85, edgecolor='#ccc', fancybox=False) plt.tight_layout() plt.savefig(filename, dpi=150, bbox_inches='tight', facecolor=BG_COLOR) plt.close(fig) # Call the 3D combined charts - A and B directions make_3d_bel_chart(filenameoutA, amergefilename + ' · Interference Paths by Fade Margin Reduction · Path ' + str(pathnumber), filenameIn + '_A_3D_BEL_Chart.png') make_3d_bel_chart(filenameoutB, bmergefilename + ' · Interference Paths by Fade Margin Reduction · Path ' + str(pathnumber), filenameIn + '_B_3D_BEL_Chart.png') # ----------------------------------------------------------------------------------------------- print() print("-------------------------- DONE!---------------------------\n") rightnowEND = datetime.now() Finishtime = rightnowEND - now print('Finish Time is ', Finishtime)