Formatted Title

From Spreadsheets to Scripts: Optimizing Data Analytics Workflow with an R Shiny Application for a Complex Groundwater Site

Background/Objectives

The digital data landscape in the environmental industry is rapidly evolving. As we adapt to client and public expectations and strive to improve the economy, efficiency, and efficacy of our daily work, the environmental industry needs improved environmental data analytics practices and tools. Fort Hall Mine Landfill (FHML) is located in southern Idaho and consists of active waste cells regulated under RCRA and a capped unlined dump (Cell 1) closed in 1983. Tetrachloroethene (PCE) and trichloroethene (TCE) have contaminated groundwater in the canyon and downgradient areas, impacting municipal and domestic supply wells. Routine groundwater monitoring involves 128 wells, 221 regulated analytes, and 3 sampling events per year. Routine reporting includes data screening against promulgated standards, data visualization with GIS mapping and hundreds of time series concentration plots, and statistical analysis, including confidence limit calculations, comparison against background, and Mann Kendall trends, where interpretation differs by regulatory category. Additionally, annual results letters are prepared for owners of sampled domestic wells. The objectives of this project were to develop a data analytics tool that provided the following: 1. comprehensive, efficient, and accurate data analysis to satisfy project requirements, 2. different options for data input to the application, 3. reusable intellectual property (IP) for use at other similar sites, and 4. accessibility for staff without programming expertise.

Approach/Activities

To satisfy project objectives, an internal user application was developed in R Shiny, which is an open-source R package for producing web applications from Posit Software. The application resides on a protected server and is accessible by all company employees; however, the scripts are protected and accessible by the programming team only in a version-controlled environment (i.e., GitHub). The process to overhaul the spreadsheet workflow and rebuild in R Shiny took approximately one year for the FHML site, and the project required the collaboration of an interdisciplinary team of experts, including software developers, database managers, statisticians, remediation engineers, and business technology specialists. Quality and checking procedures were developed and implemented iteratively to test the product outputs for aesthetics, quality, and accuracy. For the FHML project, a live database connection was established via the Earthsoft EQuIS representational state transfer (REST) application programming interface (API). After a beta version was available for use, select team members used the application to replace previous Excel workbook workflows and generate report and presentation materials.

Results/Lessons Learned

The FHML site R shiny application is now being used to efficiently develop reports and presentation materials. The three primary components of the application include data screening against regulatory standards, data visualization in time series and bar graphs available in multiple formats and variable combinations at the click of a button, and statistical analysis in accordance with relevant guidance and regulatory requirements. Additional features are being considered, such as interactive mapping to facilitate efficient project meetings and client communication. Two additional applications were developed under this project – one for broader application, and one to efficiently interface with EPA’s ProUCL statistical program. The IP from the FHML application was efficiently reused in less than two months to develop the broader-use application, which is currently out for limited-release in a test group phase, during which feedback from multiple user teams will be incorporated to generate the next version. This application does not employ the API, which increases both its versatility and user responsibility. The ProUCL application allows the user to capitalize on EPA’s robust statistical tool, quickly generating formatted input files and summarizing output files. Lessons learned and quality and technical review procedures developed since the original vision of this project will be shared, with an emphasis on how our development methods can be implemented by the audience at their similar environmental sites.