A Python program that takes a CSV of road condition data and uses an Excel file to calculate the PCI values
This program will take a csv file of road condition samples and will import the data into an Excel file that calculates the Pavement Condition Index (PCI) value for each sample. It will then extract the calculated values from the Excel file. The PCI values are then appended to the end of the original data and a CSV file is exported to the original file's directory.
The Input file contains data collected by someone in the field during a road condition survey. It contains information such as the begin location, end location, sample length, sample width, and different types of distress.
The PCI value is calculated based on the Sample's Area and the amount of each type of distress ( Alligator Cracking, Transverse Cracking, Weathering, etc.). An example of an input file is shown below.
The Excel file containing the PCI calculator is complex. Each line in the Calc Many sheet shown below is a separate background sheet. Each row on the Calc Many sheet is a separate sample and contains the distress values for that sample.The highlighted cell in yellow is the calculated PCI value for that sample.
There is a separate background sheet for each row on the Calc Many sheet. Below is an example of the background sheet for a sample. Due to the amount of calculations required on this sheet, I found it easier to import the values into the Excel file and extract the calculated value rather than trying to replicate the sheet in Python.
The exported file contains the information from the input file and appends the calculated PCI value to the end of each sample. The screenshot shown below is an example of the exported file and has the PCI value circled in red.
The program is a simple GUI that will open a file browser when you click on the button. Once the file is selected, the GUI will add a continuous progress bar to show that it is responsive. The command window will show the progress to the user as shown below. The values are calculated in batches of 90 due to the limitations of the Excel file.
Once the program is finished with calculating the values, the GUI will show the progress bar at 100% and will show the exported file location on the command window.
If you have python installed, you can run the program from the command line using python3 PCICalculator.py
There is a PCICalculator.exe file in the dist folder. This exe was created on a Windows 10 system so that it could run on a Windows system without Python installed. In either case, Excel will need to be installed on the computer.