Download the Dataset

from erddapy import ERDDAP
import os
import pandas as pd
from tqdm.notebook import tqdm

e = ERDDAP(
    server = "https://cioosatlantic.ca/erddap",
    protocol = "tabledap"
)

e.dataset_id = 'allDatasets'
e.variables = ['datasetID', 'institution', 'title', 'minTime', 'maxTime']

# only grab data from county with data within study period
e.constraints = {'maxTime>=': '2020-09-01', 'minTime<=': '2024-08-31'}
df_allDatasets = e.to_pandas()

df_CMAR_datasets = df_allDatasets[df_allDatasets['institution'].str.contains('CMAR') & df_allDatasets['title'].str.contains('Water Quality Data')].copy()
df_CMAR_datasets['county'] = df_CMAR_datasets['title'].str.removesuffix(' County Water Quality Data')

df_CMAR_datasets.sample(5)

e.variables = [
 'waterbody',
 'station',
# 'sensor_type',
# 'sensor_serial_number',
# 'rowSize',
# 'lease',
# 'latitude',
# 'longitude',
 'deployment_start_date',
 'deployment_end_date',
# 'string_configuration',
 'time',
 'depth',
# 'depth_crosscheck_flag',
# 'dissolved_oxygen',
# 'salinity',
# 'sensor_depth_measured',
 'temperature',
# 'qc_flag_dissolved_oxygen',
# 'qc_flag_salinity',
# 'qc_flag_sensor_depth_measured',
 'qc_flag_temperature']

e.constraints = { "time>=": "2020-09-01", "time<=": "2024-08-31" }

%%time

os.makedirs('data', exist_ok=True)

for index, row in df_CMAR_datasets.iterrows():

    csvfile = f"data/{row['county']}.csv"

    if os.path.exists(csvfile):
        continue

    print(f"Downloading {row['title']}...")
    e.dataset_id = row['datasetID']
    df = e.to_pandas()

    df.to_csv(csvfile, index=False)

!ls -lh data/

os.makedirs('segments', exist_ok=True)

all_segment_metadata = []
for index, row in tqdm(list(df_CMAR_datasets.iterrows())):

    csvfile = f"data/{row['county']}.csv"

    df = pd.read_csv(csvfile)
    
    df['segment'] = df[['waterbody', 'station', 'depth (m)',
                     'deployment_start_date (UTC)', 'deployment_end_date (UTC)',
                     ]].agg(lambda x: row['county'] + '_' + '_'.join([str(y) for y in x]), axis=1)

    df_metadata = df[['segment', 'waterbody', 'station', 'depth (m)',
                     'deployment_start_date (UTC)', 'deployment_end_date (UTC)',
                     ]]

    df_metadata = df_metadata.drop_duplicates()
    all_segment_metadata.append(df_metadata)
    
    df_data = df.drop(columns=['waterbody', 'station', 'depth (m)',
                                 'deployment_start_date (UTC)', 'deployment_end_date (UTC)',
                              ])
    
    df_data = df_data.sort_values(by=['segment', 'time (UTC)'])

    df_data.set_index(['segment', 'time (UTC)'], inplace=True)

    for key, segment_df in df_data.groupby(level=0):
        csvfile = f'segments/{key}.csv'
        segment_df = segment_df.droplevel(0)
        segment_df.to_csv(csvfile)

df_metadata = pd.concat(all_segment_metadata)
df_metadata.set_index('segment', inplace=True)
df_metadata.to_csv('metadata.csv')

!ls -lh segments/ | wc

df_metadata.head(8)