Interpreting the Results of Airport Water Monitoring (2017)

Below is the uncorrected machine-read text of this chapter, intended to provide our own search engines and external engines with highly rich, chapter-representative searchable text of each book. Because it is UNCORRECTED material, please consider the following text as a useful but insufficient proxy for the authoritative book pages.

E-1 A p p e n d i x e General Use of Analysis Tool The ACRP Water Quality Data Analysis Tool (ACRP WQ Tool) provides tabular and graphi- cal statistical summaries of water quality data. The Tool is intended to assist in analyzing, sum- marizing, and interpreting airport water quality data to help solve possible problems related to compliance and monitoring. Specifically, the Tool will help determine the quality of the data and if there are enough data points to conduct a statistical analysis, deal with outliers and non detects, and create monitoring reports. The Tool is intended to provide users with a standardized analy- sis and reporting of water monitoring data using sophisticated analytical techniques not readily available in spreadsheet software. It was intentionally developed without a polished user inter- face to stay within budgetary constraints of the research and to provide users with the ability to adapt the Tool to their situation-specific needs. Users of the Tool should plan for up to an hour to initially become familiar with the file management aspect of the Tool. Once the initial learning curve has been passed, regular use of the Tool should be relatively straightforward. System Requirements • The Tool has been tested in a Microsoft® Windows 7 environment; user experience may differ in other operating system environments. • The data template file is a macro-enabled Microsoft Excel™ spreadsheet. • Each instance of the Tool requires at least 1 GB of storage space. • The Tool does not require traditional “installation”; therefore, administrator privileges should generally not be required for use. For users operating within strict security settings, adminis- trator privileges may be required to enable macros within Microsoft Excel. Using the ACRP Water Quality Data Analysis Tool Step 1: Download and Unzip the Tool Download the Tool from the ACRP Research Report 166 summary web page on the TRB website (www.trb.org) and unzip to a local hard drive or mapped network location. The Tool is completely portable between Microsoft Windows operating systems and does not require any installation. However, the file and folder structure of the “ACRP_Tool” folder must remain unchanged (see Figure E-1) in addition to the following considerations. The “ACRP_Tool” folder and the “run_ACRP_Tool.bat” file must be kept in the same folder. This folder must be on a mapped drive (such as your C: drive) and not on a Universal Naming Convention path (e.g., a server on your network such as “\\My-Server-01\Data. . . .”). The path Quick Start Guide for the Water Quality Data Analysis Tool

E-2 interpreting the Results of Airport Water Monitoring to the Tool should not have any spaces. For the best user experience, copy the Tool and .bat file to a new folder on your C: drive such as “C:\ACRP_WQ_Tool”. Step 2: Create Input File The ACRP WQ Tool requires a .csv file with specific headers and data types to work correctly. Packaged with the Tool is a macro-enabled Microsoft Excel workbook designed to assist you in creating a suitable input file. To begin, open the “ACRP_DataTemplate.xlsm” workbook and click “Enable Content” (see Figure E-2), then follow the instructions on the “ReadMe” tab. The “Data” tab is where you will input your water quality data to be used for analysis with the ACRP WQ Tool. This worksheet contains the headers “location”, “parameter”, “value”, “quali- fier”, “unit”, “threshold” (Figure E-3). The “location” column is a unique monitoring location or outfall that may contain multiple parameters. The “parameter” column contains the differ- ent analytes or water quality constituents for each “location”. The “value” column contains the analytical value of the data point – note that there cannot be null (blank) values in this field and Figure E-2. Macros must be enabled so workbook can be used. Figure E-1. Structure of files and folders within the “ACRP_Tool” folder must not be changed.

Quick Start Guide for the Water Quality data Analysis Tool E-3 you should remove null values prior to copying them to the template. The “qualifier” column indicates if the data point is a nondetect; the only symbol allowed in this field is “U” and the value in the “value” column should be the lab detection limit. If other qualifiers are used, such as “J” values, these should be removed or converted to “U”. The “unit” column is a text field. Each “location” and “parameter” pair must only contain a single unit type. The ACRP WQ Tool does not do unit conversion, thus mismatching units will cause the Tool to report erroneous values. The “threshold” column may be a monitoring benchmark, effluent limit, or other compliance stan- dard. The “threshold” column can only have a single value for each “location” and “parameter” pair. When copying data to the “Data” tab, you must not overwrite the column headers, as the Tool uses the specific header names as keys during the analysis. If you wish to design your own .csv file from scratch, make sure the file contains the same headers and encloses any commas (,) in field names with double quotation marks. We recommend that your data contain only standard characters found on the keyboard (i.e., do not include special characters). Once your data is copied to the Tool, highlight all of your data, including the column headers, and press the export button. You will be prompted to save your data – we recommend that the .csv file be saved to a mapped drive (such as C:) for use with the Tool. Once the file has saved, copy the full file path to the clipboard. Step 3: Run the Tool To run the ACRP WQ Tool, double click on the “run_ACRP_Tool.bat” file – the Tool will execute in a DOS command window. Depending on your system and where the Tool is located, it may require several minutes to first initialize. As long as a DOS window remains open, the Tool is booting up. When prompted (Figure E-4), enter or paste the full path of your data .csv file (e.g., “C:\my_data\test_data.csv”) into the DOS window without any quotation marks and press Enter. If the Tool successfully finds your data file, it will begin to create reports in the same folder as your data. If the Tool cannot find your data, you will be re-prompted to enter Figure E-3. Column names are used as keys during analysis.

E-4 interpreting the Results of Airport Water Monitoring the path. Any “location” and “parameter” pairs that have less than three values will be skipped and a report for those pairs will not be generated. If you are re-running an analysis, the Tool will overwrite any previous reports – if you wish to keep previous reports, move them to a different folder away from the Data.csv file. Step 4: Analyze the Results A .pdf file will be created for every “location” and “parameter” pair. The report will contain both tabular and graphical results. Please see Chapter 2 of the guidebook for more detailed guid- ance on use of statistics. Below is a summary of the tabular results and the definition of each statistic: • Count: the total number of data points included in your “location” and “parameter” pair. • Number of NDs: the number of nondetect values counted by the Tool. If this number is not correct, please check that all nondetect values are correctly flagged in the “qual” column with an upper case “U”. • Min: Max: the lower and upper bounds of the data. • Mean (95% confidence interval): The arithmetic mean and 95% confidence interval about the mean for the data. Confidence interval computed using the bias-corrected bootstrap routine (see Chapter 2). • Standard Deviation: The arithmetic standard deviation. • Log. Mean (95% confidence interval): Same as the arithmetic mean, but computed on the natural logarithms of the data. Confidence interval computed using the bias-corrected boot- strap routine (see Chapter 2). • Log. Standard Deviation: Same as arithmetic standard deviation, but computed on the natural logarithms of the data. • Geo. Mean (95% confidence interval): The exponentiation of logarithmic mean. Confidence interval computed using the bias-corrected bootstrap routine (see Chapter 2). • Coeff. of Variation: The arithmetic standard deviation divided by the mean. Figure E-4. The prompt to enter the full path of your data .csv file.

Quick Start Guide for the Water Quality data Analysis Tool E-5 • Skewness: The arithmetic skew coefficient. • Median (95% confidence interval): The median and 95% confidence interval about the median for the data. Confidence interval computed using the bias-corrected bootstrap routine (see Chapter 2). • Quartiles: The 25th and 75th percentiles of the data. The graphical outputs include a boxplot and a cumulative distribution probability plot as shown in Figure E-5. The boxplot figure included with each report provides a graphical repre- sentation of many of the above statistics. It is particularly useful for visualizing the distribution of the data and identifying potential outliers. The cumulative distribution plot is also useful for understanding the distribution of the data; however, this plot relates these statistics to individual data points and displays the percentage of other data points below its value. The extrapolated nondetect values are also shown if present. Extrapolation routine is based on a regression-on- order statistics (ROS) method as described in Chapter 2. Figure E-5. Summary of D_Phos data at monitoring location 7A. Outlier > Q3 + 1.5 × IQR (note 1) Mean (note 2) Max. data ≤ Q3 + 1.5 × IQR Min. data ≥ Q1 – 1.5 × IQR Notes: 1) Interquartile range: IQR = Q3 – Q1 2) Geometric means are plotted only for bacteria data Otherwise, arithmetic means are shown. Upper 95% CI about the median Lower 95% CI about the median 25th percentile, Q1 75th percentile, Q3 50th percentile, median

E-6 interpreting the Results of Airport Water Monitoring Troubleshooting The ACRP WQ Tool is relatively robust, however, some common user errors may cause the Tool to misbehave. The most common cause of error is providing the Tool an input path that is not a .csv file. For example, the Tool will attempt to run if you provide the path “C:\my_data\ my_folder” even if you meant to provide the path “C:\my_data\my_folder\data.csv”. In this case, the Tool may crash. Please double check all paths before running the Tool. The second most common error occurs when users try to re-run an analysis while having a previously generated .pdf report open. The ACRP WQ Tool cannot write to a file while a previ- ously generated .pdf report is open. To ensure that this does not happen, make sure all .pdf files created by the Tool are closed before running the Tool. Other common errors relate to the characters in the data file path and DOS limitations. For example, the maximum length for a Windows file path is 255 characters. If the Tool is having difficulty saving a .pdf file, check that the file path [i.e., the location of the file (e.g., “C:\my_data\ my_folder”) plus the filename consisting of the “location” and “parameter” (e.g., “7A_D_Phos”)] plus “.pdf” do not exceed this limit.

Abbreviations and acronyms used without definitions in TRB publications: A4A Airlines for America AAAE American Association of Airport Executives AASHO American Association of State Highway Officials AASHTO American Association of State Highway and Transportation Officials ACI–NA Airports Council International–North America ACRP Airport Cooperative Research Program ADA Americans with Disabilities Act APTA American Public Transportation Association ASCE American Society of Civil Engineers ASME American Society of Mechanical Engineers ASTM American Society for Testing and Materials ATA American Trucking Associations CTAA Community Transportation Association of America CTBSSP Commercial Truck and Bus Safety Synthesis Program DHS Department of Homeland Security DOE Department of Energy EPA Environmental Protection Agency FAA Federal Aviation Administration FAST Fixing America’s Surface Transportation Act (2015) FHWA Federal Highway Administration FMCSA Federal Motor Carrier Safety Administration FRA Federal Railroad Administration FTA Federal Transit Administration HMCRP Hazardous Materials Cooperative Research Program IEEE Institute of Electrical and Electronics Engineers ISTEA Intermodal Surface Transportation Efficiency Act of 1991 ITE Institute of Transportation Engineers MAP-21 Moving Ahead for Progress in the 21st Century Act (2012) NASA National Aeronautics and Space Administration NASAO National Association of State Aviation Officials NCFRP National Cooperative Freight Research Program NCHRP National Cooperative Highway Research Program NHTSA National Highway Traffic Safety Administration NTSB National Transportation Safety Board PHMSA Pipeline and Hazardous Materials Safety Administration RITA Research and Innovative Technology Administration SAE Society of Automotive Engineers SAFETEA-LU Safe, Accountable, Flexible, Efficient Transportation Equity Act: A Legacy for Users (2005) TCRP Transit Cooperative Research Program TDC Transit Development Corporation TEA-21 Transportation Equity Act for the 21st Century (1998) TRB Transportation Research Board TSA Transportation Security Administration U.S.DOT United States Department of Transportation

TRA N SPO RTATIO N RESEA RCH BO A RD 500 Fifth Street, N W W ashington, D C 20001 A D D RESS SERV ICE REQ U ESTED ISBN 978-0-309-44622-8 9 7 8 0 3 0 9 4 4 6 2 2 8 9 0 0 0 0 Interpreting the Results of A irport W ater M onitoring: A G uidebook A CRP Research Report 166 TRB