LLNL-TR-2020711 M4SF-26LL010204053 - Web- accessible sorption database - INTERIM PROGRESS REPORT M Zavarin Jul 2026 This document was prepared as an account of work sponsored by an agency of the United States government. Neither the United States government nor Lawrence Livermore National Security, LLC, 07NA27344. August 19, 2026 1 CONTENTS Acknowledgments ........................................................................................................................................ 1 1 Introduction ..................................................................................................................................... 3 2 Web-based interface to extract sorption data for use in performance assessment .................. 4 3 Application of L-SCIE data extraction tool for performance assessment modeling of a shale repository ............................................................................................................................... 7 4 FY27 proposed efforts ................................................................................................................. 22 5 References ................................................................................................................................... 23 6 Appendix ....................................................................................................................................... 24 August 19, 2026 2 1 INTRODUCTION This progress report (Level 4 Milestone Number M4SF-26LL010204053) summarizes research conducted at Lawrence Livermore National Laboratory (LLNL) within the Crystalline Host Rock Properties & Processes - LLNL Number SF-26LL01020405. This research is focused on the development of a web-accessible database of sorption data for minerals and rocks (bentonite backfill and crystalline host rock) that are likely to be present in a US hosted repository, including elevated temperature data that are expected in a DPC DGR scenario. The Livermore Surface Complexation Ion Exchange (L-SCIE) sorption database has been in development for a number of years in support of the US nuclear waste repository program. It is composed of a series of linked tables and, in 2025, was transformed from a Microsoft Access© database to an open source MariaDB© SQL relational database. The details regarding the structure and content of the database have been described in the past (Zavarin et al., 2022) and the general structure remained unchanged when converting to the MariaDB format. Nevertheless, it is important to note some key properties that represent the basis for the collection of data in L-SCIE: • The data in L-SCIE are focused primarily on synthetic and mined mineral phases with a strong emphasis on simple binary sorption data. However, recent additions have included bulk rocks relevant to the nuclear waste program (granite, shale, etc.) • The database is composed of original data extracted from journal manuscripts, “grey literature” reports, or other digital databases available in the community. • Data are stored in their original form (no unit conversions performed) and directly linked to the manuscript and the associated tables and figures from which the data were extracted. • The data are not screened through any type of data quality process. • All data processing/transformation is performed external to the database via R and Python codes and associated workflows. • Links to geochemical modelling codes and optimization algorithms used to develop models based on L-SCIE data are integrated with but external to the database. • All L-SCIE database products are freely available, follow the FAIR principles (Wilkinson et al., 2016), and adhere to the Open Science philosophy (https://github.com/mzavarin/L-SCIE). This fiscal year, the L-SCIE database reached a milestone and now contains over 100,000 sorption data. Recent major additions to this database, beyond the manual extraction of data from the literature, were the inclusion of approximately 2,500 clay sorption data that were provided through a collaboration with the research group at the Paul Scherrer Institute (Marinich et al., 2023), 61,000 data (out of 70,000 total) that were extracted from the online sorption database supported by the Japanese Atomic Energy Agency (JAEA, 2025), 10,000 data that were provided through our collaboration with the SOREDA/RES3T team at the Helmholtz-Zentrum Dresden-Rossendorf (Brendler et al., 2003; Bok, 2025) and 5,000 data that were digitized at MIT (H. Wainwright) and Clemson University (B.A. Powell). It should be noted that the JAEA data are stored separately from the core L- SCIE database but reformatted and transformed to be compatible with L-SCIE. While the sorption data extracted from literature figures and tables are stored in L-SCIE using the original units and nomenclature (e.g. ppm, % sorbed, atoms/nm2), a code, written in R, is used to perform all the necessary unit conversions to yield a standardized data file in which sorption data and associated metadata (i.e. solution conditions, mineral characteristics, etc.) are all uniformly formatted. In addition, parameter uncertainties are reported (if available) or estimated based on estimates of experimental uncertainty and/or uncertainty associated with data digitization from August 19, 2026 3 figures. The “unified” datafile then becomes the source of all downstream modeling efforts described below. As part of the L-SCIE development effort, a number of publications are in preparation. Han et al. (submitted) describes the development of machine learning algorithms trained on the L-SCIE database to determine Kd values, associated parameter uncertainties, and prioritize experimental data collection activities given specific solution conditions. Han et al. (in prep) tests an automated mechanistic surface complexation modeling tool for expedited surface complexation database development. Eguiguren et al. (Accepted) presents a new automated modeling workflow that incorporates the CrunchFlow modeling code and ion exchange parameters into a fitting routine linked to the L-SCIE database. 2 WEB-BASED INTERFACE TO EXTRACT SORPTION DATA FOR USE IN PERFORMANCE ASSESSMENT Our primary goal this fiscal year was to develop a simple and web-based interface that would allow users to interrogate the database and yield relevant sorption parameters for direct use in Performance Assessment modeling. Based on guidance from the program, an open web-based platform was not pursued. Instead, we developed a standalone code with a simple user-friendly web-like interface that allows users to interact with the L-SCIE database. The interface does not require users to have any python or database background to perform the data extractions. The python code is provided here as an Appendix. This Python code is a data preparation and distribution-fitting workflow for the L-SCIE sorption database. It begins by loading a merged CSV file containing sorption measurements, then cleans up the Sorbate and Mineral fields so that naming is more consistent for downstream grouping and file creation. In particular, it removes spaces, replaces slashes with periods, and applies a few specific nomenclature corrections so that equivalent or commonly conflated materials are treated consistently. Next, the code computes log(Kd, mL/g) for every record using the sorbed concentration, aqueous concentration, and mineral loading. It also calculates ionic strength for each row by mapping electrolyte names to charges from a separate lookup table and combining the charge and concentration contributions of up to 13 electrolyte species. This creates a standardized chemistry descriptor for each observation, which is then used later as one of the filtering criteria. The script also flags whether CO2 is present in the sample conditions. It does this by searching the electrolyte and gas columns for carbonate-related terms like C(+4) and CO2(g), then storing the result in a boolean CO2? column. At the same time, it applies additional cleanup rules, such as replacing smectite with montmorillonite, converting silica and SiO2 to quartz, and correcting misclassified rocks, minerals, and sorbates. After the data are standardized, the notebook removes entries that are not relevant for the intended Kd analysis, including potentiometric titration data and any records with missing log(Kd) values. It then exports the cleaned data into separate CSV files for each unique sorbate-mineral pair. This first export step creates a large set of individual datasets, each corresponding to one radionuclide, oxidation state, and mineral combination. The user can readily identify the source of all data using the reference ID column that is included in the data export. The second half of the notebook is an interactive modeling step. A small Tkinter window (Figure 1) asks the user to enter ranges for pH and ionic strength and to choose whether CO2-bearing systems August 19, 2026 4 should be included, excluded, or ignored. The dataset is then filtered according to those criteria, and only sorbate-mineral pairs with at least five observations are retained for downstream model fitting. For each subgroup, the code writes out a filtered CSV, computes summary statistics, and fits either a triangular or uniform distribution to the log(Kd) values after removing outliers by the 1.5 InterQuartile Range (IQR) rule. Model selection (triangular vs. uniform) is based on both sample size and goodness-of-fit. The code uses a Kolmogorov-Smirnov test to judge whether the triangular fit is acceptable, and if the p-value is too low or the cleaned sample size is too small, it falls back to a uniform model. For each group, it saves a plot showing the histogram of observed values together with the fitted probability density function, and it records the fitted limits, mode when applicable, p-value, and sample counts in a summary table that is exported as model_statistics.csv. The result is a reusable pipeline for cleaning sorption data, conditioning it on geochemical constraints, and generating simplified probabilistic models for each mineral-radionuclide pair. An example graphical output for radionuclide sorption to granite in the pH range of 7 to 9, ionic strength ranging from 0.01 to 0.1, and in the presence of CO2 is plotted in Figure 2. The values, source information, and model statistics are all available via exported CSV files for further analysis. Figure 1. Tkinker dialog box that is used to extract and model parameter ranges relevant to a particular solution condition. August 19, 2026 5 Figure 2. Example graphical output for radionuclide sorption to granite in the pH range 7 to 9, ionic strength range of 0.01 to 0.1, and in the presence of CO2. August 19, 2026 6 3 APPLICATION OF L-SCIE DATA EXTRACTION TOOL FOR PERFORMANCE ASSESSMENT MODELING OF A SHALE REPOSITORY As part of a broader FY26 effort to develop a repository concept for shale rock and a test case for our approach, we applied our new data extraction tool to directly link radionuclide retardation in Performance Assessment using the L-SCIE database. For the repository concept in shale rock and to extract appropriate Kd data from L-SCIE, a model groundwater chemistry must be identified. We used water chemistries that were developed at Sandia National Laboratories to represent three mineralogically distinct geologic units that may be present in a shale deposit: chalky marl, sandstone, and primary shale (Table 1). Radionuclide sorption (log(Kd, mL/g)) values were estimated from data contained in LLNL’s L-SCIE sorption database. For the purposes of this data extraction, we selected radionuclides that have been identified as important potential contributors to the performance of a nuclear waste repository (Am, Ac, Cl, C, Cs, I, Np, Pu, Ra, Se, Sn, Tc, Th, U) and particular focus was on oxidation states relevant to reducing conditions (though some data for oxidizing condition are also provided). Some general L-SCIE data filtering and processing was performed as part of the data extraction workflow: • Ionic strength was calculated based on the background electrolyte composition associated with each sorption datapoint. • The presence and absence of CO2 was determined from metadata that indicated experiments performed under atmospheric conditions or the addition of bicarbonate into solutions as part of the experiment. • Smectite and montmorillonite were treated as equivalent mineral phases The range of solution conditions used to extract data from the database were the following (based on data compilation from C.F. Jove-Coló n, Table 1): • pH: Niobara chalky marl: 6.24-7.92; Dakota sandstone: 6.84-8.50; Pierre shale: 6.94-8.90 (+-2SD range) • Ionic strength: Niobara chalky marl: 0.070-1.757; Dakota sandstone: 0.003-0.075; Pierre shale: 0.012-0.298 (+- factor of 5 from average) • CO2: experiments performed in the presence of CO2 • Radionuclide-mineral datasets with fewer than 5 total data were rejected from consideration Four broad categories of rock types were selected and associated Kds were extracted for each radionuclide based on the following workflow: • Shale: Shale data were used. Alternatively, either clay data or argillite data were selected. If neither was available, the data for bentonite were used. Mudstone was only used for Se(-2). (with Pierre shale water chemistry) • Bentonite backfill: bentonite data were selected from the database. If bentonite data were not available, montmorillonite data were used. If montmorillonite data were not available, clay data were used. (Pierre shale water chemistry) • Limestone: calcite data were selected from the database. Alternatively, marl or dolomite data were used. (Niobara chalky mark water chemistry) • Sandy aquifer: quartz data were selected from the database. Alternatively, sand, sandstone, or sand+quartz were used. (Dakota sandstone water chemistry) August 19, 2026 7 If sufficient data were not available based on this strict geochemical constraint, a second attempt to extract relevant data allowed for a broader pH range of 6.0 to 9.0. If sufficient data were still not available, then the ionic strength criterion was removed and the presence/absence of CO2 was also removed. These are noted in the tables below. For each radionuclide-rock type above, the following workflow was used to populate the Kd data report in the following tables: • Outlier data were identified and rejected based on the 1.5×IQR criterion. However, we found that very few data were rejected. • For the remaining data, a triangular fit to the data was reported if and only if: o The number of data were 20 or more o The resulting p-value was >=0.05 • If the above criteria were not met, a uniform distribution is reported • If data for some radionuclides were not available, chemical analogs were employed as needed (e.g. Am(3+) ~ Ac(3+)) to complete the data tables. Table 1. Water chemistry Niobara formation, Dakota formation sandstone, and Pierre shale. pH Cl SO4 Ca Fe K Mg Na SiO2 HCO3 ---------------------------------------- mg/L ---------------------------------------- Niobrara Fm. (chalky marl) MIN 5.7 0.48 0 48 0.002 6.7 5.9 99 5 - MAX 9.5 19000 300 510 150 53 68 11000 13 - AVERAGE 7.1 (6.2-7.9)a 12000 54 260 20 37 39 7300 71 - Dakota Fm. (sandstone) MIN 7.0 2.6 17 2.9 - 2.6 1.5 6.9 8.5 140 MAX 8.5 1400 460 170 - 11 61 1,200 62 550 AVERAGE 7.7 (6.8-8.5)a 120 170 46 - 5.2 18 170 19 270 Pierre shale (from Von Damm 1988) MIN 3.8 6.4 0 1.1 0 0.9 0.2 12 4 330 MAX 9.6 28000 4900 690 26 220 280 18000 52 870 AVERAGE 7.9 (6.9-8.9)a 1100 450 63 1.1 14 28 1000 23 730 a Values in parentheses are ±2SD calculated from the available groundwater chemistry data for that formation. The resulting model statistics and recommended values for a Performance Assessment model of a generic shale repository are listed in Tables 2 through 5. The associated graphical representations of the data and associated model fits are shown in Figures 3 through 6. Each table and figure summarizes the results associated with each of the four rock types (shale, bentonite, limestone, sandy aquifer) and associated water chemistries, as described above. August 19, 2026 8 Table 2. Recommended shale log(Kd, mL/g) values to be used in shale disposal concept. Sorbate Mineral/Analog logKd_LL logKd_UL logKd_Mode data Am(+3) clay 4.0 5.2 Uniform 8 Ac(+3) Eu(III)-clay 4.2 5.4 Uniform 91 Cl(-) - - - 14C - - - Cs(+) clay/argillite 0.8/2.5 3.5/4.1 3.5/4.1 66/30 I(-) clay** -0.2 1.3 0.5 36 Np(+4) Pu(+4)-clay/argillite 4.0/2.9 4.6/4.7 Uniform/Uniform 5/7 Np(+5) clay/argillite 1.8/1.3 3.1/3.2 Uniform/2.6 12/64 Pu(+4) clay/argillite 4.0/2.9 4.6/4.7 Uniform/Uniform 5/7 Pu(+5) Np(+5)clay/argillite 1.8/1.3 3.1/3.2 Uniform/2.6 12/64 Ra(+2) clay -0.2 3.1 Uniform 68 Se(-2) mudstone 1.6 1.9 Uniform 12 Se(+4) shale 0.7 3.0 Uniform 26 Se(+6) clay -3.0 -3.0 Uniform 20 Tc(IV) clay/argillite* -0.1/0.0 2.7/1.6 Uniform/Uniform 53/6 Tc(VII) - - - Th(+4) clay 4.6 5.0 Uniform 55 U(+4) Th(+4)-clay 4.6 5.0 Uniform 55 U(+6) shale/clay 1.2/0.8 1.5/2.3 Uniform/Uniform 8/41 * Tc oxidation state not reported ** pH range expanded to 6-9, any ionic strength, and presence/absence of CO2 due to limited data availability August 19, 2026 9 Figure 3. Shale log(Kd, mL/g) data historgrams and associated uniform and triangular models developed for the shale disposal concept. August 19, 2026 10 Figure 3 (continued). Shale log(Kd, mL/g) data historgrams and associated uniform and August 19, 2026 11 Figure 3 (continued). Shale log(Kd, mL/g) data historgrams and associated uniform and August 19, 2026 12 Table 3. Recommended bentonite log(Kd, mL/g) values to be used in shale disposal concept. Sorbate Mineral logKd_LL logKd_UL logKd_Mode data Am(+3) Eu(+3)-bentonite 1.4 5.9 5.4 76 Ac(+3) Eu(+3)-bentonite 2.3 5.5 3.4 101 Cl(-) - - - 14C - - - Cs(+) bentonite 1.6 2.8 Uniform 162 I(-) bentonite** 0.0 1.5 0.4 43 Np(+4) bentonite 2.2 4.6 Uniform 6 Np(+5) bentonite 1.6 3.7 Uniform 8 Pu(+4) bentonite 1.0 1.9 Uniform 6 Pu(+5) Np(+5)-bentonite 1.6 3.7 Uniform 8 Ra(+2) montmorillonite 2.7 2.9 Uniform 10 Se(+4) montmorillonite 0.7 1.2 Uniform 12 Se(+6) clay -3.0 -3.0 Uniform 20 Tc(IV) Tc-clay* 0.3 1.4 Uniform 27 Tc(VII) - - - Th(+4) bentonite 4.1 7.3 Uniform 10 U(+4) Th(IV)-bentonite 4.1 7.3 Uniform 10 U(+6) bentonite 1.0 4.5 4.5 97 * Tc oxidation state not reported ** pH range expanded to 6-9, any ionic strength, and presence/absence of CO2 due to limited data availability August 19, 2026 13 Figure 4. Bentonite log(Kd, mL/g) data historgrams and associated uniform and triangular models developed for the shale disposal concept. August 19, 2026 14 Figure 4 (continued). Bentonite log(Kd, mL/g) data historgrams and associated uniform and August 19, 2026 15 Table 4. Recommended chalky marl log(Kd, mL/g) values to be used in shale disposal concept. Sorbate Mineral logKd_LL logKd_UL logKd_Mode data Am(+3) Eu(III)-calcite 1.6 4.9 Uniform 17 Ac(+3) Eu(III)-calcite 1.6 4.9 Uniform 17 Cl(-) - - 14C - - Cs(+) dolomite* -0.1 2.0 Uniform 5 I(-) marl/calcite** -3/-3 0.8/0.6 Uniform 6/12 Np(+4) Pu(IV)-calcite 2.8 2.9 Uniform 5 Np(+5) calcite 2.1 2.3 Uniform 8 Pu(+4) calcite 2.8 2.9 Uniform 5 Pu(+5) calcite 0.7 1.5 Uniform 6 Ra(+2) Ba(+2)-calcite -0.4 0.0 Uniform 8 Se(+4) calcite 0.1 1.1 Uniform 55 Se(+6) - - Tc(IV) Tc(VII) - - Th(+4) Pu(IV)-calcite 2.8 2.9 Uniform 5 U(+4) Pu(IV)-calcite 2.8 2.9 Uniform 5 U(+6) calcite 0.1 1.6 Uniform 21 * pH range expanded to 6-9 due to limited data availability. ** pH range expanded to 6-9, any ionic strength, and presence/absence of CO2 due to limited data availability August 19, 2026 16 Figure 5. Chalky marl log(Kd, mL/g) data historgrams and associated uniform and triangular models developed for the shale disposal concept. August 19, 2026 17 Figure 5 (continued). Chalky marl log(Kd, mL/g) data historgrams and associated uniform and August 19, 2026 18 Table 5. Recommended sandstone rock log(Kd, mL/g) values to be used in shale disposal concept. Sorbate Mineral logKd_LL logKd_UL logKd_Mode data Am(+3) quartz 2.6 3.1 Uniform 9 Ac(+3) Eu(III)-quartz -0.3 5.3 3.0 24 Cl(-) - - - 14C - - - Cs(+) quartz/sandstone -1.3/0.6 2.8/3.0 2.8/Uniform 22/6 I(-) sand/sand+quartz -0.8/0.8 0.3/1.6 Uniform/Uniform 7/6 Np(+4) sandstone* 1.3 3.4 Uniform 12 Np(+5) sand 0.5 3.3 Uniform 35 Pu(+4) Th(IV)- quartz*/sand* 3.5/2.4 4.2/4.2 Uniform 8/5 Pu(+5) Np(V)/sand 0.5 3.3 Uniform 35 Ra(+2) quartz 2.3 3.7 Uniform 27 Se(+4) quartz** 0.1 1.6 Uniform 14 Se(+6) - - - Tc(IV) sand** -1.0 -0.3 Uniform 4 Tc(VII) sand** -0.7 -0.4 Uniform 4 Th(+4) quartz*/sand* 3.5/2.4 4.2/4.2 Uniform 8/5 U(+4) Th(IV)- quartz*/sand* 3.5/2.4 4.2/4.2 Uniform 8/5 U(+6) quartz/sand 1.0/1.1 3.2/1.7 Uniform/Uniform 41/32 * pH range expanded to 6-9 due to limited data availability. ** pH range expanded to 6-9, any ionic strength, and presence/absence of CO2 due to limited data availability. August 19, 2026 19 Figure 6. Sandstone log(Kd, mL/g) data historgrams and associated uniform and triangular models developed for the shale disposal concept. August 19, 2026 20 Figure 6 (continued). Sandstone log(Kd, mL/g) data historgrams and associated uniform and August 19, 2026 21 Figure 6 (continued). Sandstone log(Kd, mL/g) data historgrams and associated uniform and 4 FY27 PROPOSED EFFORTS LLNL recommendations for work packages for FY27 leverage work completed in FY26 to expedite parameter extraction and statistical evaluation using the L-SCIE database platform. In FY27, data will be extracted and evaluated in support of the generic granite repository Performance Assessment model development led by Sandia National Laboratories. In addition, our development of a user- friendly data extraction and modeling platform in FY26 has led to the identification of significant sorption data gaps that we propose to correct via a literature mining effort. In particular, the incorporation of sorption data for weakly sorbing radionuclides will be a priority (iodine, selenium, 14C). These elements are not well represented in our database and a focused effort to mine data for these elements is needed. This data mining effort will be integrated into the L-SCIE platform for use in Performance Assessment modeling and general testing of sorption behavior of rock types. One additional model improvement that will be pursued is the linking of mineral-specific sorption data to bulk rock sorption data. If successful, this would significantly enhance the application of L-SCIE data to a broader range of rocks whose mineralogy can be reasonably well defined or estimated. Importantly, the proposed FY27 scope is still under development and will be negotiated with the program in the coming months. August 19, 2026 22 5 REFERENCES F. Bok "RES³T-Rossendorf Expert System for Surface and Sorption Thermodynamics, Helmholtz- Zentrum Dresden-Rossendorf." https://www.hzdr.de/res3t. (accessed 2025). V. Brendler, A. Vahle, T. Arnold, G. Bernhard, and T. Fanghänel, "RES3T-Rossendorf expert system for surface and sorption thermodynamics," Journal of Contaminant Hydrology, vol. 61, no. 1, pp. 281-291, 2003/03/01/ 2003, doi: https://doi.org/10.1016/S0169-7722(02)00129-8. Eguiguren, L., Wainwright, H.M., and Zavarin, M. Accepted. Application of large community sorption datasets to surface complexation and ion exchange model development: Uranium sorption to montmorillonite, Applied Geochem. Han, S-C, Noack, M., Molins, S., Zavarin, M., Zheng, L., Powell, B., Wainwright, H.M. Submitted. Gaussian Process-based Autonomous Experimental Design Framework for Sorption Prediction and Sorption Data Acquisition, Env. Sci. Technol. Han, S-C., and Zavarin, M., Balboni, E. In Prep. Community sorption data driven workflow for the construction of a self-consistent surface complexation model database: Selenium adsorption onto iron oxides, Env. Sci. Technol. JAEA. "Sorption and Diffusion Database System." https://migrationdb.jaea.go.jp/nmdb/db/sdb/search_1.jsp (accessed 2025). O. Marinich, M. Marques Fernandes, G. D. Miron, and D. A. Kulik, "ClaySor 2023: Update of 2SPNE SC/CE Sorption Model for Illite and Montmorillonite in GEMS Implementation," Nagra, 2023. M. D. Wilkinson et al., "The FAIR Guiding Principles for scientific data management and stewardship," Scientific Data, vol. 3, no. 1, p. 160018, 2016/03/15 2016, doi: 10.1038/sdata.2016.18. M. Zavarin et al., "Community Data Mining Approach for Surface Complexation Database Development," Environmental Science & Technology, vol. 56, no. 4, pp. 2827-2838, 2022/02/15 2022, doi: 10.1021/acs.est.1c07109. August 19, 2026 23 6 APPENDIX Python data extraction and modeling tool code is summarized below. # This part pulls in the L-SCIE data, cleans up nomenclature, filters out irrelevant data, calculates the logKd, # and produces individual logKd csv files for each radionuclide, oxidation state, and mineral pair in the database # import math import pandas as pd import numpy as np import matplotlib.pyplot as plt import seaborn as sns import sys import os import tkinter as tk from pathlib import Path from scipy.stats import triang from scipy.stats import uniform from scipy import stats import shutil # # Identify path for file input and output # path_1 = Path(r'C:\Users\zavarin1\Documents\CODES\L_SCIE\triangular_distribution_calculator') # # Pull in lscie dataset, csv # df_merged = pd.read_csv(path_1.joinpath("merged_lscie_jaea.csv"),low_memory=False) # # clean up relevant columns to facilitate downstream calculations # df_merged['Sorbate'] = df_merged['Sorbate'].astype(str) df_merged['Mineral'] = df_merged['Mineral'].astype(str) df_merged['Sorbate'] = df_merged['Sorbate'].str.replace('/', '.', regex=False) df_merged['Mineral'] = df_merged['Mineral'].str.replace('/', '.', regex=False) df_merged['Sorbate'] = df_merged['Sorbate'].str.replace(r'\s+', '', regex=True) df_merged['Mineral'] = df_merged['Mineral'].str.replace(r'\s+', '', regex=True) # # Calculate logKd values for all data # df_merged['log(Kd)(mL/g)'] = np.log10(df_merged['Sorbed_val'] / df_merged['Aq_val'] / df_merged['Mineral_val'] * 1000) # # Calculate nominal ionic stregth # Specify the columns selected_columns = ['Electrolyte1', 'Electrolyte2', 'Electrolyte3', 'Electrolyte4', 'Electrolyte5', 'Electrolyte6', 'Electrolyte7', 'Electrolyte8', 'Electrolyte9', August 19, 2026 24 'Electrolyte10', 'Electrolyte11', 'Electrolyte12', 'Electrolyte13',] # # This part only used to build the lookup table for ionic strength calculations # # unique_values = pd.unique(df_merged[selected_columns].values.ravel()) # print(unique_values) # # now use lookup table to determine electrolyte charge # df_charge_lookup = pd.read_csv(path_1.joinpath("charge_lookup.csv"),low_memory=False) lookup_dict = df_charge_lookup.set_index('Name')['Charge'].to_dict() df_merged['E1'] = df_merged['Electrolyte1'].map(lookup_dict) df_merged['E2'] = df_merged['Electrolyte2'].map(lookup_dict) df_merged['E3'] = df_merged['Electrolyte3'].map(lookup_dict) df_merged['E4'] = df_merged['Electrolyte4'].map(lookup_dict) df_merged['E5'] = df_merged['Electrolyte5'].map(lookup_dict) df_merged['E6'] = df_merged['Electrolyte6'].map(lookup_dict) df_merged['E7'] = df_merged['Electrolyte7'].map(lookup_dict) df_merged['E8'] = df_merged['Electrolyte8'].map(lookup_dict) df_merged['E9'] = df_merged['Electrolyte9'].map(lookup_dict) df_merged['E10'] = df_merged['Electrolyte10'].map(lookup_dict) df_merged['E11'] = df_merged['Electrolyte11'].map(lookup_dict) df_merged['E12'] = df_merged['Electrolyte12'].map(lookup_dict) df_merged['E13'] = df_merged['Electrolyte13'].map(lookup_dict) # # Make the ionic strength calculation # cols_to_fill = ['E1','E2','E3','E4','E5','E6','E7','E8','E9','E10','E11','E12','E13'] cols_to_fill1 = ['Electrolyte1_val','Electrolyte2_val','Electrolyte3_val','Electrolyte4_val','Electrolyte5_val','Electrolyte6_val','Electr olyte7_val','Electrolyte8_val','Electrolyte9_val','Electrolyte10_val','Electrolyte11_val','Electrolyte12_val','Electroly te13_val'] # df_merged[cols_to_fill] = df_merged[cols_to_fill].fillna(0) df_merged[cols_to_fill1] = df_merged[cols_to_fill1].fillna(0) # df_merged['Ionic_Strength'] = 0.5 * (df_merged['E1']**2 * df_merged['Electrolyte1_val'] + df_merged['E2']**2 * df_merged['Electrolyte2_val'] + df_merged['E3']**2 * df_merged['Electrolyte3_val'] + df_merged['E4']**2 * df_merged['Electrolyte4_val'] + df_merged['E5']**2 * df_merged['Electrolyte5_val'] + df_merged['E6']**2 * df_merged['Electrolyte6_val'] + df_merged['E7']**2 * df_merged['Electrolyte7_val'] + df_merged['E8']**2 * df_merged['Electrolyte8_val'] + df_merged['E9']**2 * df_merged['Electrolyte9_val'] + df_merged['E10']**2 * df_merged['Electrolyte10_val'] + df_merged['E11']**2 * df_merged['Electrolyte11_val'] + df_merged['E12']**2 * df_merged['Electrolyte12_val'] + df_merged['E13']**2 * df_merged['Electrolyte13_val'] ) # # delete the electrolyte charge columns # df_merged = df_merged.drop(columns=['E1','E2','E3','E4','E5','E6','E7','E8','E9','E10','E11','E12','E13']) # August 19, 2026 25 # look for rows that include either CO2 or C(+4) # # The text to search for search_text1 = 'C\(\+4\)' search_text2 = 'CO2\(g\)' # # Create a boolean mask boolean_mask = ( df_merged['Electrolyte1'].str.contains(search_text1, na=False) | df_merged['Electrolyte2'].str.contains(search_text1, na=False) | df_merged['Electrolyte3'].str.contains(search_text1, na=False) | df_merged['Electrolyte4'].str.contains(search_text1, na=False) | df_merged['Electrolyte5'].str.contains(search_text1, na=False) | df_merged['Electrolyte6'].str.contains(search_text1, na=False) | df_merged['Electrolyte7'].str.contains(search_text1, na=False) | df_merged['Electrolyte8'].str.contains(search_text1, na=False) | df_merged['Electrolyte9'].str.contains(search_text1, na=False) | df_merged['Electrolyte10'].str.contains(search_text1, na=False) | df_merged['Electrolyte11'].str.contains(search_text1, na=False) | df_merged['Electrolyte12'].str.contains(search_text1, na=False) | df_merged['Electrolyte13'].str.contains(search_text1, na=False) | df_merged['Gas1'].str.contains(search_text2, na=False) | df_merged['Gas2'].str.contains(search_text2, na=False) | df_merged['Gas3'].str.contains(search_text2, na=False) ) # Assign the boolean mask to a new column df_merged['CO2?'] = boolean_mask # # replace smectite with montmorillonite as they are equivalent in most cases # replace silica and SiO2 with quartz # correct Ra(+4) with Ra(+2) # df_merged['Mineral'] = df_merged['Mineral'].str.replace('smectite', 'montmorillonite') df_merged['Mineral'] = df_merged['Mineral'].str.replace('silica', 'quartz') df_merged['Mineral'] = df_merged['Mineral'].str.replace('SiO2', 'quartz') df_merged['Sorbate'] = df_merged['Sorbate'].str.replace(r'Ra\(\+4\)', 'Ra(+2)', regex=True) # # remove potentiometric titration data and NAN Kd values from dataset # df_merged = df_merged[df_merged['Sorbate'] != 'H(+1)'] df_merged = df_merged[df_merged['log(Kd)(mL/g)'] != ''] df_merged = df_merged.dropna(subset=['log(Kd)(mL/g)']) # # export the final dataset to be used for Kd analysis # #df_merged.to_csv('df_merged_filtered.csv', index=False) # # Export separate csv files for each pair of radionuclide, oxidation state, and mineral that is included in the database # files do into subdirectory called "output" # try: shutil.rmtree(path_1.joinpath("output")) print(f"Directory 'output' and its contents deleted successfully.") except FileNotFoundError: print(f"Error: Directory 'output' not found.") except OSError as e: August 19, 2026 26 print(f"Error deleting directory 'output': {e}") # Path.mkdir(path_1.joinpath('output'), parents=False, exist_ok=True) # for (Sorbate, Mineral), group_df in df_merged.groupby(['Sorbate', 'Mineral']): # Define filename based on column values filename = f"{Sorbate}_{Mineral}_data.csv" filepath = os.path.join("output", filename) # Save the subsetted data to a new CSV group_df.to_csv(filepath, index=False) print(f"Created: {filename}") # import tkinter as tk pH_min = 0 pH_max = 0 IS_min = 0 IS_max = 0 CO2 = "yes" def get_input_values(): """Function to retrieve values from the entry widgets.""" global pH_min global pH_max global IS_min global IS_max global CO2 pH_min = e1.get() pH_max = e2.get() IS_min = e3.get() IS_max = e4.get() CO2 = dropdown.get() print(f"minimum pH: {pH_min}, maximum pH: {pH_max}, minimum Ionic Strength: {IS_min}, maximum Ionic Strength: {IS_max}, CO2?: {CO2}") master.destroy() master = tk.Tk() master.title("Data Ranges and Specifications") # Create Labels tk.Label(master, text="Minimum pH").grid(row=0, column=0, padx=10, pady=5) tk.Label(master, text="Maximum pH").grid(row=1, column=0, padx=10, pady=5) tk.Label(master, text="Minimum Ionic Strength").grid(row=2, column=0, padx=10, pady=5) tk.Label(master, text="Maximum Ionic Strength").grid(row=3, column=0, padx=10, pady=5) tk.Label(master, text="CO2?").grid(row=4, column=0, padx=10, pady=5) OPTIONS = [ "yes", "no", "all" ] # Define the list of options # Create Entry widgets e1 = tk.Entry(master) e2 = tk.Entry(master) e3 = tk.Entry(master) e4 = tk.Entry(master) dropdown = tk.StringVar(master) # Create the OptionMenu widget August 19, 2026 27 dropdown.set(OPTIONS[2]) # Set the default value dropdown1 = tk.OptionMenu(master, dropdown, *OPTIONS) # Position Entry widgets using the grid manager e1.grid(row=0, column=1, padx=10, pady=5) e2.grid(row=1, column=1, padx=10, pady=5) e3.grid(row=2, column=1, padx=10, pady=5) e4.grid(row=3, column=1, padx=10, pady=5) dropdown1.grid(row=4, column=1, padx=10, pady=5) # Create a Submit Button # The 'command' option links the button to the function that gets the input submit_button = tk.Button(master, text="Submit", command=get_input_values) submit_button.grid(row=5, column=0, columnspan=2, pady=10) master.mainloop() # make sure directories exist # try: shutil.rmtree(path_1.joinpath("output_filtered")) print(f"Directory 'output_filtered' and its contents deleted successfully.") except FileNotFoundError: print(f"Error: Directory 'output_filtered' not found.") except OSError as e: print(f"Error deleting directory 'output_filtered': {e}") try: shutil.rmtree(path_1.joinpath("output_filtered_fig")) print(f"Directory 'output_filtered_fig' and its contents deleted successfully.") except FileNotFoundError: print(f"Error: Directory 'output_filtered_fig' not found.") except OSError as e: print(f"Error deleting directory 'output_filtered_fig': {e}") Path.mkdir(path_1.joinpath('output_filtered'), parents=False, exist_ok=True) Path.mkdir(path_1.joinpath('output_filtered_fig'), parents=False, exist_ok=True) # # make a new subset dataframe that only includes values within range of criteria # filtered_df = df_merged[ (df_merged['pH'] >= float(pH_min)) & (df_merged['pH'] <= float(pH_max)) & (df_merged['Ionic_Strength'] >= float(IS_min)) & (df_merged['Ionic_Strength'] <= float(IS_max)) ] if CO2 == 'yes': filtered_df = filtered_df[(filtered_df['CO2?'] == True)] if CO2 == 'no': filtered_df = filtered_df[(filtered_df['CO2?'] == False)] else: # # export the filtered dataframe # #filtered_df.to_csv('df_merged_conditionfiltered.csv', index=False) # # Find the minimum value in column 'A' min_value = filtered_df['log(Kd)(mL/g)'].min() print(f"Minimum value in Column A: {min_value}") August 19, 2026 28 # Find the maximum value in column 'B' max_value = filtered_df['log(Kd)(mL/g)'].max() print(f"Maximum value in Column B: {max_value}") # create a new dataframe that will list the statistics associated with triangular or uniform Kd distributions # stats_df = pd.DataFrame(columns=['Sorbate', 'Mineral', 'logKd_Lower_limit', 'logKd_Upper_limit', 'logKd_Mode', 'p_value', 'Total_data', "Filtered_data"]) # # make plots for every combination of mineral and sorbate # # first make a new dataframe that only includes datasets that are larger than or equal to X datapoints # subset_df = filtered_df.groupby(['Sorbate', 'Mineral']).filter(lambda x: len(x) >=5) # # Loop through every combination of radionuclide, oxidation state, and mineral to produce Kd distribution statistics # and associated plot of data and model Kds # for (Sorbate, Mineral), group_df in subset_df.groupby(['Sorbate', 'Mineral']): # # Define filenames based on radionuclide and mineral # filename = f"{Sorbate}_{Mineral}_data.csv" filename_fig = f"{Sorbate}_{Mineral}_fig.png" filepath = os.path.join("output_filtered", filename) filepath_fig = os.path.join("output_filtered_fig", filename_fig) # # Save subset dataset to a CSV # group_df.to_csv(filepath, index=False) print(f"Created: {filename}") # # Create a list of all logKd values # samples = list(group_df['log(Kd)(mL/g)']) #print(samples) # # THIS PART IS USED IF YOU WANT TO DEVELOP MODEL FIT BASED ON THE 1.5 IQR ASSESSMENT TO FIND OUTLIERS # # Calculate the first quartile (Q1) and third quartile (Q3) Q1 = np.quantile(samples,0.25) Q3 = np.quantile(samples,0.75) # Calculate the Inter-Quartile Range (IQR) IQR = Q3 - Q1 # Define the lower and upper bounds using the 1.5 * IQR rule lower_bound = Q1 - 1.5 * IQR upper_bound = Q3 + 1.5 * IQR # Filter the DataFrame to keep only the rows within the bounds samples_cleaned = list(filter(lambda x: x >= lower_bound, samples)) samples_cleaned = list(filter(lambda x: x <= upper_bound, samples_cleaned)) #print(samples_cleaned) # # calculate triangular distribution August 19, 2026 29 # params = triang.fit(samples_cleaned) c_fit, loc_fit, scale_fit = params # # calculate p value for triangular distribution # Kolmogorov-Smirnov test for goodness of fit # ks_statistic, p_value = stats.kstest(samples_cleaned, 'triang', args=(c_fit, loc_fit, scale_fit)) # print(f"\nKS statistic: {ks_statistic:.4f}") # print(f"P-value: {p_value:.4f}") # model is good if p>0.05 # # convert parameters to standard triangular distribution parameters and count total number of data # left_fit = loc_fit right_fit = loc_fit + scale_fit mode_fit = loc_fit + c_fit * scale_fit num_rows = len(samples) num_rows_iqr = len(samples_cleaned) # # if the fit to triangular distribution is less that p value of 0.05 for KS statistics, then revert to linear distribution # if p_value<0.05 or num_rows_iqr < 20: # # export statistics from each subgroup into the stats dataframe # new_row_data = {'Sorbate': Sorbate, 'Mineral': Mineral, 'logKd_Lower_limit': min(samples_cleaned), 'logKd_Upper_limit': max(samples_cleaned), 'logKd_Mode': 'Uniform', 'p_value' : p_value, 'Total_data': num_rows, 'Filtered_data': num_rows_iqr } stats_df.loc[len(stats_df)] = new_row_data # Define the range of the distribution: from 'a' (loc) to 'b' (loc + scale) a = min(samples_cleaned) # Lower bound (loc) b = max(samples_cleaned) # Upper bound scale = b - a # Scale parameter in scipy is the width (b - a) # Create a uniform distribution object # loc is the starting point, scale is the width uniform_distribution = uniform(loc=a, scale=scale) # Generate x values to plot the function x = np.linspace(math.floor(min_value), math.ceil(max_value), 500) # Extend range slightly for better visualization # x = np.linspace(a - 0.1*scale, b + 0.1*scale, 100) # Extend range slightly for better visualization # Calculate the PDF values for the x range pdf_values = uniform_distribution.pdf(x) # Plot the PDF plt.figure(figsize=(8, 5)) custom_bins = np.arange(math.floor(min_value), math.ceil(max_value), 0.25) plt.hist(samples_cleaned, bins=custom_bins, density=True, alpha=0.5, label='Sample Data Histogram') plt.plot(x, pdf_values, 'r-', lw=2, label='Fitted Uniform PDF') #plt.title('Continuous Uniform Distribution PDF') plt.xlabel('log(Kd, mL/g)') August 19, 2026 30 plt.ylabel('Probability Density') plt.xlim(math.floor(min_value), math.ceil(max_value)) # plt.title(f"Sorption Data and Uniform Fit: {Sorbate}-{Mineral}; N={num_rows_iqr}") plt.legend(loc='best') plt.grid(False) plt.savefig(filepath_fig, bbox_inches='tight', format='png') print(f"Created uniform model: {filename_fig}") plt.close() else: # # export statistics from each subgroup into the stats dataframe # new_row_data = {'Sorbate': Sorbate, 'Mineral': Mineral, 'logKd_Lower_limit': left_fit, 'logKd_Upper_limit': right_fit, 'logKd_Mode': mode_fit, 'p_value': p_value, 'Total_data': num_rows, 'Filtered_data': num_rows_iqr } stats_df.loc[len(stats_df)] = new_row_data # # plot both the data and the model results # x = np.linspace(math.floor(min_value), math.ceil(max_value), 500) # x = np.linspace(min(samples)-0.1*(max(samples)-min(samples)), max(samples)+0.1*(max(samples)- min(samples)), 100) pdf_fitted = triang.pdf(x, c=c_fit, loc=loc_fit, scale=scale_fit) plt.figure(figsize=(8, 5)) custom_bins = np.arange(math.floor(min_value), math.ceil(max_value), 0.25) plt.hist(samples_cleaned, bins=custom_bins, density=True, alpha=0.5, label='Sample Data Histogram') plt.plot(x, pdf_fitted, 'r-', lw=2, label='Fitted Triangular PDF') plt.xlabel('log(Kd, mL/g)') plt.ylabel('Probability Density') plt.xlim(math.floor(min_value), math.ceil(max_value)) # plt.title(f"Sorption Data and Triangular Fit: {Sorbate}-{Mineral}; N={num_rows_iqr}") plt.legend() plt.savefig(filepath_fig, bbox_inches='tight', format='png') print(f"Created triangular model: {filename_fig}") plt.close() # # export the model statistics # stats_df.to_csv('model_statistics.csv', index=False) August 19, 2026 31