NetCDF to CSV

Convert MARIS standard NetCDF4 files into CSV files for the legacy SQL import pipeline.

Each step is a single-responsibility function that transforms a dict of DataFrames keyed by sample group. They are composed in to_csv() below.

Read NetCDF groups

Open a standardised MARIS NetCDF file, extract each group’s variable arrays, and return them as {group_name: DataFrame}.

source

read_nc_grps

def read_nc_grps(
    fname, # Path to netcdf file
):

Read a MARIS NetCDF file and return {group: DataFrame} dict

fname_in = Path('../files/nc/100_HELCOM_MORS_2024.nc')
dfs = read_nc_grps(fname_in)
test_eq(list(dfs), ['BIOTA', 'SEAWATER', 'SEDIMENT'])
dfs['BIOTA'].head(1).T
0
SMP_ID_PROVIDER BBFFG1999001
LON 13.72
LAT 54.220001
SMP_DEPTH 0.0
TIME 929404800
STATION BGBODD
NUCLIDE 4
VALUE 841.0
UNIT 4
UNC 58.869999
DL 1
BIO_GROUP 11
SPECIES 96
BODY_PART 54
DRYWT NaN
WETWT NaN
PERCENTWT 0.1692

Filter columns

Keep only columns in the CSV schema. The NetCDF has internal columns (like SMP_ID) not part of the CSV output.

source

keep_csv_cols

def keep_csv_cols(
    dfs:dict, # dict of DataFrames keyed by sample group
    cols:list={'AREA': 'area', 'BODY_PART': 'bodypar_id', 'BOTTOM': 'slicedown', 'COUNT_MET': 'counmet_id', 'DL': 'detection', 'DLV': 'detection_lim', 'DRYWT': 'drywt', 'FILT': 'filtered', 'LAB': 'lab_id', 'LAT': 'latitude', 'LON': 'longitude', 'NUCLIDE': 'nuclide_id', 'PERCENTWT': 'percentwt', 'PREP_MET': 'prepmet_id', 'PROFILE_ID': 'profile_id', 'REF_ID': 'ref_id', 'SAL': 'salinity', 'SAMP_MET': 'sampmet_id', 'SAMPLE_TYPE': 'samptype_id', 'SED_TYPE': 'sedtype_id', 'SMP_DEPTH': 'sampdepth', 'SMP_ID_PROVIDER': 'samplabcode', 'SPECIES': 'species_id', 'STATION': 'station', 'TAXONDB': 'taxondb', 'TAXONDBID': 'taxondb_id', 'TAXONDBURL': 'taxondb_url', 'TAXONNAME': 'taxonname', 'TAXONRANK': 'taxonrank', 'TAXONREPNAME': 'taxonrepname', 'TEMP': 'temperatur', 'TIME': 'begperiod', 'TOP': 'sliceup', 'TOT_DEPTH': 'totdepth', 'UNC': 'uncertaint', 'UNIT': 'unit_id', 'VALUE': 'activity', 'VOL': 'volume', 'WETWT': 'wetwt'}, # columns to keep (defaults to CSV_VARS)
):

Keep only columns listed in cols

test = {'SEAWATER': pd.DataFrame(dict(SMP_ID=[1], TIME=[1], VALUE=[1], EXTRA=[1]))}
res = keep_csv_cols(test)
test_eq(list(res['SEAWATER'].columns), ['TIME', 'VALUE'])

Decode time

NetCDF stores time as seconds since epoch. Convert to datetime.

source

decode_time

def decode_time(
    dfs:dict, # dict of DataFrames keyed by sample group
):

Decode TIME from epoch seconds to datetime

test = {'SEAWATER': pd.DataFrame({'TIME': [1672531200]})}
decode_time(test)
test_eq(test['SEAWATER']['TIME'].iloc[0], pd.Timestamp('2023-01-01'))

Add sample type

SQL import expects a sample type column. Each group has a fixed identifier.

source

add_sample_type

def add_sample_type(
    dfs:dict, # dict of DataFrames keyed by sample group
):

Add SAMPLE_TYPE column using group ID mapping

Each group gets its MARIS sample type ID from SMP_TYPE_LUT:

test = {'SEAWATER': pd.DataFrame({'VALUE': [1]}), 'BIOTA': pd.DataFrame({'VALUE': [2]})}
add_sample_type(test)
test_eq(test['SEAWATER']['SAMPLE_TYPE'].iloc[0], 1)
test_eq(test['BIOTA']['SAMPLE_TYPE'].iloc[0], 2)

Add reference ID

Optional column from Zotero / INIS archive location. Omitted if not provided.

source

add_ref_id

def add_ref_id(
    dfs:dict, # dict of DataFrames keyed by sample group
    ref_id:int=None, # Reference ID to add as REF_ID column
):

Add REF_ID column if ref_id is provided

test = {'SEAWATER': pd.DataFrame({'VALUE': [1]})}
add_ref_id(test)
test_eq('REF_ID' in test['SEAWATER'].columns, False)
test = {'SEAWATER': pd.DataFrame({'VALUE': [1]})}
add_ref_id(test, ref_id=42)
test_eq(test['SEAWATER']['REF_ID'].iloc[0], 42)

Add taxon information

Map BIOTA species IDs to scientific names and database references via the MARIS species lookup table.

source

get_taxon_cols

def get_taxon_cols()->dict: # {col_name: {species_id: value}} mapping

Read species lookup table, return {col_name: {species_id: value}} dict

Exported source 
TAXON_COLS = dict(Taxonname='TAXONNAME', Taxonrank='TAXONRANK', TaxonDB='TAXONDB', TaxonDBID='TAXONDBID', TaxonDBURL='TAXONDBURL')

source

add_taxon_info

def add_taxon_info(
    dfs:dict, # dict of DataFrames keyed by sample group
):

Add taxon columns to BIOTA from species lookup

add_taxon_info adds the taxon columns to BIOTA, looked up by species ID. An ID missing from the species table gets 'Unknown':

test = {'BIOTA': pd.DataFrame({'SPECIES': [99, 96, -999]})}
add_taxon_info(test)
test_eq(test['BIOTA']['TAXONNAME'].tolist(), ['Gadus morhua', 'Fucus vesiculosus', 'Unknown'])
test_eq(test['BIOTA']['TAXONRANK'].tolist(), ['species', 'species', 'Unknown'])

Map lookup-table columns

Convert integer enum IDs to display names. DL and FILT use the Excel name column rather than the sanitised version.

source

map_lut

def map_lut(
    dfs:dict, # dict of DataFrames keyed by sample group
    cols:list, # Column names to map
    key:str='name', # LUT key column
    value:str='id', # LUT value column
    reverse:bool=True, # Reverse the mapping direction
):

Map columns using get_lut

test = {'SEAWATER': pd.DataFrame({'DL': [1, 2, 3]})}
map_lut(test, ['DL'])
test_eq(test['SEAWATER']['DL'].tolist(), ['=', '<', 'ND'])
test = {'SEAWATER': pd.DataFrame({'FILT': [0, 1, 2]})}
map_lut(test, ['FILT'])
test_eq(test['SEAWATER']['FILT'].tolist(), ['Not available', 'Yes', 'No'])

A group without the column is left unchanged:

test = {'SEAWATER': pd.DataFrame({'VALUE': [1]})}
map_lut(test, ['DL'])
test_eq(list(test['SEAWATER'].columns), ['VALUE'])

Decode remaining enumerated columns

CSV_DTYPES marks decoded vs encoded columns. DL and FILT are excluded since they use a different LUT column.

source

decode_csv_vars

def decode_csv_vars(
    dfs:dict, # dict of DataFrames keyed by sample group
):

Decode enumerated columns marked as state='decoded' in CSV_DTYPES

NoteFEEDBACK TO MARIS DATA TEAM

It would be cleaner/more consistent to expect all MARIS LUT values in their encoded (integer) form during SQL import, rather than receiving a mix of decoded display names and encoded IDs.

Write CSV files

Rename columns via CSV_VARS, then write one file per group.

source

to_csv_files

def to_csv_files(
    dfs:dict, # dict of DataFrames keyed by sample group
    fname_in:str, # Input NetCDF file path
    dest_out:str=None, # Destination path stem; defaults to `fname_in` without its suffix
):

Rename columns and write one CSV per group

to_csv_files renames the columns to their CSV names and writes one file for each group:

test = {'SEAWATER': pd.DataFrame({'TIME': [1], 'VALUE': [2]})}
with tempfile.TemporaryDirectory() as d:
    p, = to_csv_files(test, Path(d)/'test')
    test_eq(list(pd.read_csv(p).columns), ['begperiod', 'activity'])

Entry point

to_csv composes the helpers in order.

source

to_csv

def to_csv(
    fname_in:str, # MARIS standard NetCDF file path
    dest_out:str=None, # Destination path stem; defaults to `fname_in` without its suffix
    ref_id:int=None, # Reference ID to add as REF_ID column
):

Convert MARIS standard NetCDF file to import-ready CSV files

Usage example

to_csv converts a MARIS standard NetCDF file to import-ready CSV files, one for each sample type group. This example writes them to a temporary directory:

tmp = tempfile.TemporaryDirectory()
paths = to_csv(fname_in, dest_out=Path(tmp.name)/fname_in.stem, ref_id=100)
test_eq([p.name for p in paths], ['100_HELCOM_MORS_2024_BIOTA.csv', '100_HELCOM_MORS_2024_SEAWATER.csv', '100_HELCOM_MORS_2024_SEDIMENT.csv'])

The output CSV includes 23 columns: sample metadata, activity, detection limit info, taxon details for biota, and reference ID. Below the first row:

df = pd.read_csv(paths[0])
tmp.cleanup()
test_eq(df.shape[1], 23)
df.head(1).T
0
bodypar_id 54
detection =
drywt NaN
latitude 54.22
longitude 13.72
nuclide_id 4
percentwt 0.1692
sampdepth 0.0
samplabcode BBFFG1999001
species_id 96
station BGBODD
begperiod 1999-06-15
uncertaint 58.87
unit_id 4
activity 841.0
wetwt NaN
samptype_id 2
ref_id 100
taxonname Fucus vesiculosus
taxonrank species
taxondb Wikidata
taxondb_id Q754755
taxondb_url https://www.wikidata.org/wiki/Q754755