ICS - Ion Chromatography System

Ion Chromatography (IC) is used to measure the concentration of dissolved ionic species in solution. It provides quantitative analysis of anions and cations, commonly used for analyzing process solutions, leachates, and water samples.

Method Overview

Property	Value
Full Name	Ion Chromatography System (ICS)
Purpose	Measures dissolved ion concentrations in solutions
Output Units	ppm or mg/L
SharePoint Location	Analytical Data > ICS, IC Data
File Format	XLS (Excel with Summary sheet)

Analytes Measured

Ion chromatography typically measures common anions and cations:

Ion Type	Common Analytes
Anions	Fluoride (F⁻), Chloride (Cl⁻), Nitrate (NO₃⁻), Sulfate (SO₄²⁻), Phosphate (PO₄³⁻)
Cations	Sodium (Na⁺), Potassium (K⁺), Calcium (Ca²⁺), Magnesium (Mg²⁺), Ammonium (NH₄⁺)

Data Pipeline

SharePoint Sources

Type	Site	Folders	Files
ICS Data	Analytical Data	ICS, IC Data	.xls

Dagster Assets

The ICS data pipeline consists of the following assets in apps/datasmart/src/datasmart/assets/analytical/ics.py:

ics_data (sharepoint_multi_asset)
├── ics_data (analytical.ics_data)
├── ics_meta (analytical.ics_meta)
└── ics_qc (qaqc.ics_qc)

Asset Descriptions

Asset	Schema	Description
ics_data	analytical	Processed sample measurements with normalized sample IDs
ics_meta	analytical	File metadata (filename, path, URL, creation date)
ics_qc	qaqc	QC/reference samples (water blanks, standard solutions)

Database Tables

analytical.ics_data

Processed ICS measurement data with normalized sample IDs.

Column	Type	Description
original_sample_id	string	Raw sample ID from file
sample_id	string	Normalized sample ID
unit	string	Measurement unit (ppm, mg/L)
instrument	string	ICS instrument identifier
analyte	string	Ion being measured
value	float	Measured concentration
file_id	string	SharePoint file identifier

analytical.ics_meta

File metadata for tracking data provenance.

Column	Type	Description
file_id	string	SharePoint file identifier
file_name	string	Original filename
path	string	SharePoint folder path
url	string	SharePoint file URL
date_created	datetime	File creation timestamp

qaqc.ics_qc

QC samples separated for quality control analysis.

Column	Type	Description
original_sample_id	string	QC sample identifier
measurement_type	string	Type of measurement
unit	string	Measurement unit
instrument	string	Instrument identifier
analyte	string	Ion being measured
value	float	Measured concentration
file_id	string	SharePoint file identifier

File Format

XLS File Structure

ICS data files are Excel files with a "Summary" sheet containing:

1. Header rows (rows 1-4 after offset): Contains measurement metadata

   • Row 1: Measurement type
   • Row 2: Unit
   • Row 3: Instrument
   • Row 4: Analyte name

2. Data rows: Sample measurements starting after header rows

The data is extracted starting from row 10, column B (after a 9-row offset and 1-column offset).

Data Melting

The wide-format Excel data is melted to long format with columns:

• original_sample_id - Sample identifier from first column
• measurement_type - From header row 1
• unit - From header row 2
• instrument - From header row 3
• analyte - From header row 4
• value - Numeric measurement value

Non-numeric values (n.a., na, empty) are filtered out during processing.

QC Sample Identification

Samples are automatically classified as QC if their original_sample_id:

• Contains "water" (case-insensitive) - identifies water blanks
• Ends with "ppm" (case-insensitive) - identifies standard solutions

QC samples are stored in qaqc.ics_qc while regular samples go to analytical.ics_data.

Usage Examples

Query ICS Data

from shared.db.sql import SQL

# Get all ICS data
ics = SQL.read("SELECT * FROM analytical.ics_data")

# Filter by analyte
chloride = SQL.read("""
    SELECT * FROM analytical.ics_data
    WHERE analyte ILIKE '%chloride%'
""")

# Filter by project
project_data = SQL.read("""
    SELECT * FROM analytical.ics_data
    WHERE sample_id LIKE 'P800%'
""")

Query with Metadata

# Join data with file metadata
ics_with_meta = SQL.read("""
    SELECT
        d.sample_id, d.analyte, d.value, d.unit,
        m.file_name, m.date_created
    FROM analytical.ics_data d
    JOIN analytical.ics_meta m ON d.file_id = m.file_id
    WHERE d.sample_id IS NOT NULL
    ORDER BY m.date_created DESC
""")

Pivot to Wide Format

# Get data in wide format (one column per analyte)
ics_wide = SQL.read("""
    SELECT
        sample_id,
        MAX(CASE WHEN analyte ILIKE '%chloride%' THEN value END) as chloride,
        MAX(CASE WHEN analyte ILIKE '%sulfate%' THEN value END) as sulfate,
        MAX(CASE WHEN analyte ILIKE '%nitrate%' THEN value END) as nitrate
    FROM analytical.ics_data
    WHERE sample_id IS NOT NULL
    GROUP BY sample_id
""")

Analyze QC Data

# Check QC sample results
qc_data = SQL.read("""
    SELECT
        original_sample_id, analyte, value, unit
    FROM qaqc.ics_qc
    ORDER BY original_sample_id, analyte
""")

# Water blank analysis
water_blanks = SQL.read("""
    SELECT * FROM qaqc.ics_qc
    WHERE original_sample_id ILIKE '%water%'
""")

Compare with Other Methods

# Join ICS with ICP for solution vs solid comparison
combined = SQL.read("""
    SELECT
        i.sample_id,
        ics.analyte, ics.value as ics_value,
        i.Ca as icp_Ca, i.Mg as icp_Mg
    FROM analytical.ics_data ics
    LEFT JOIN analytical.icp_compiled i
        ON ics.sample_id = i.sample_id
    WHERE ics.sample_id IS NOT NULL
""")

Sample ID Processing

The pipeline uses the SampleID class to normalize sample identifiers:

from data_infrastructure.sample_id import SampleID

# Normalize sample IDs
df = SampleID.update_sample_id(
    df,
    is_material=True,
    project_team_map=project_team_map,
    crosswalk=sample_id_crosswalk,
    extended=True,
)

Related Assets

Asset	Description
sample_id_crosswalk	Maps messy sample IDs to canonical format
project_team_map	Maps old process area codes to new project team codes

Troubleshooting

Missing Samples

If a sample is missing from ics_data:

1. Check if it exists in the source file on SharePoint
2. Verify the file was processed (check ics_meta for the file)
3. Check if the sample was classified as QC (check ics_qc)
4. Verify the value was numeric (non-numeric values are filtered)

Sample ID Normalization Issues

If sample IDs aren't normalizing correctly:

1. Check the original_sample_id column for the raw value
2. Review the sample_id_crosswalk table for applicable mappings
3. Verify the sample ID format matches expected patterns

QC Classification Issues

If samples are incorrectly classified as QC or not QC:

1. Check if original_sample_id contains "water" or ends with "ppm"
2. These patterns trigger automatic QC classification
3. Update sample naming conventions to avoid false matches