#Configured for Dallas Path POC on Version 18 # Version 17 proto changes the header for the plots to header = 0, instead of 1. Fixes a small bug. # Version 16. Added an autocopy to the G Drive on JA computer. # Version 15Modifications for new FNC file v8 and 2nd60paths # Builds from Version 14. Deletes columns and renames the files and # charts to the site names in the correct direction. # includes both A & B directions, merge data and produce the histogram charts. # this also adds columns for the site names in addition to the call signs. # Earlier versions work and build up to this function. # It depends on the File Naming Conventions file to locate the input data. # Only change to 12 to 13 is the conversion to log files. # CONFIGURATION / REQUIRED CHANGES # FNC FILE IS ON DRIVE C\SCEPROJECT and must be populated correctly for this to work. Line 59 # Configure the output file, for the H Drive. 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 VERSION 8 EIGHT of the File naming convention is being used.") 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 = int(input("Enter the PATH number from the FILE NAMING CONVENTIONS SPREADSHEET: ")) # ----------------------------------------------------------------------------------- # Load the CSV file (replace 'samplecsv.csv' with your actual file name) import pandas as pd filenaming = pd.read_csv('C:\SCEProject\SCE_Path_File_Naming_Convention_v8.CSV') # changed this to be the same as Eric colnumber = 0. rownumber = 0. rownumber = pathnumber - 1 AReceiver = str(filenaming.iloc[rownumber, 1]) BReceiver = str(filenaming.iloc[rownumber, 2]) amergefilename = str(filenaming.iloc[rownumber, 21]) bmergefilename = str(filenaming.iloc[rownumber, 22]) print() # ----------------------------------------------------------------------------------- # these four are used to calculate composite interference. AIntFunction = str(filenaming.iloc[rownumber, 23]) # returns Interference_Obstruction BIntFunction = str(filenaming.iloc[rownumber, 23]) ATerrFunction = str(filenaming.iloc[rownumber, 24]) # returns Terrain_Obstruction BTerrFunction = str(filenaming.iloc[rownumber, 24]) drive = str(filenaming.iloc[rownumber, 25]) # drive letter filefolder = str(filenaming.iloc[rownumber, 26]) subfolder = str(filenaming.iloc[rownumber, 27]) #this gets the file names fileAInt = str(filenaming.iloc[rownumber, 7]) fileBInt = str(filenaming.iloc[rownumber, 8]) fileATerr = str(filenaming.iloc[rownumber, 5]) fileBTerr = str(filenaming.iloc[rownumber, 6]) #this gets the file versions fileAIntVER = fileAInt[-8:] fileBIntVER = fileBInt[-8:] fileATerrVER = fileATerr[-8:] fileBTerrVER = fileBTerr[-8:] AIntInputfile = drive + ':' + '\\' + filefolder + '\\' + str(pathnumber) + '_' + subfolder + '\\' + subfolder + '_' + AIntFunction + fileAIntVER + '.csv' BIntInputfile = drive + ':' + '\\' + filefolder + '\\' + str(pathnumber) + '_' + subfolder + '\\' + subfolder + '_' + BIntFunction + fileBIntVER + '.csv' ATerrInputfile = drive + ':' + '\\' + filefolder + '\\' + str(pathnumber) + '_' + subfolder + '\\' + subfolder + '_' + ATerrFunction + fileATerrVER + '.csv' BTerrInputfile = drive + ':' + '\\' + filefolder + '\\' + str(pathnumber) + '_' + subfolder + '\\' + subfolder + '_' + BTerrFunction + fileBTerrVER + '.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 versions, do it in the File Name Conventions file.") print() print(f"The output files will be in a folder called MERGED in the PATH directory") 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 #A-direction Ainterference_data = pd.read_csv(AIntInputfile) Aobstruction_data = pd.read_csv(ATerrInputfile) #B-direction Binterference_data = pd.read_csv(BIntInputfile) Bobstruction_data = pd.read_csv(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 Amerged_data.insert(3, 'Site Name and Direction', amergefilename) Bmerged_data.insert(3, 'Site Name and Direction', bmergefilename) # ----------------------------------------save the results to CSV files ---------------------------------------- #change directory to c and PythonProjects ABfilepath = drive + ':' + '\\' + filefolder + '\\' + str(pathnumber) + '_' + subfolder print() print("................creating directory and saving files...................") os.chdir(ABfilepath) # Specify the path for the new directory (use double backslashes) newdirectory = "MergedABFilesAndCharts_Path_" + str(pathnumber) # Create the directory if it doesn't exist if not os.path.exists(newdirectory): os.makedirs(newdirectory) #change directory after before the save os.chdir(newdirectory) # Save the merged data to a new CSV file 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') # ----------------------------------------------------------------------------------------------- print() #----------------------------------------------------------- # copy the spreadsheets and charts to google drive (optional) import subprocess #name of the target drive and directory destination_folder = 'H:\\GDrive\\My Drive\\Spatial Datalyst\\Nokia_BCHydro\\' + str(pathnumber) + '_' + filenameIn + '\\' + 'MergedABFileAndCharts_Path_' + str(pathnumber) #destination_folder = 'E:\\AvistaPowerE\\' + str(pathnumber) + '_' + filenameIn + '\\' + 'MergeABFileAndCharts_Path_' + str(pathnumber) # # Create the directory if it doesn't exist if not os.path.exists(destination_folder): os.makedirs(destination_folder) # copy the files # Execute the DOS copy command #subprocess.run(['copy', f'{newdirectory}\\*.*', destination_folder], shell=True) subprocess.run(['copy', r'*.*', destination_folder], shell=True) print(f"\nAll CSV files from '{newdirectory}' have been copied to '{destination_folder}'.") print("-------------------------- DONE!---------------------------\n") rightnowEND = datetime.now() Finishtime = rightnowEND - now print('Finish Time is ', Finishtime)