01_clean_data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d6621cf3-1da2-4578-8193-4c48db995840",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import datetime\n",
    "import matplotlib.pyplot as plt\n",
    "import numpy as np\n",
    "from pathlib import Path\n",
    "import utils\n",
    "import xarray as xr\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6b09ee8c-459b-46bc-89b8-9cc1e05eed8d",
   "metadata": {},
   "source": [
    "# Quinsy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f9c9e73d-5203-487e-aff9-c4413d0878a2",
   "metadata": {},
   "outputs": [],
   "source": [
    "sites_files = list(Path('data_from_cruise/quinsy/LogFiles').glob('*Manual_Fix*.txt'))\n",
    "quinsy = pd.DataFrame()\n",
    "for fn in sites_files:\n",
    "    quinsy_add = pd.read_csv(fn, parse_dates=[['Date', 'Time']])\n",
    "    quinsy = pd.concat((quinsy, quinsy_add))\n",
    "quinsy = quinsy[quinsy['A-Frame_Extended Longitude']<13]\n",
    "quinsy.to_csv('data_cleaned/quinsy.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5c15d636-f5da-448a-9e9e-c270bbe2d598",
   "metadata": {},
   "source": [
    "# YUCO"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "feea541e-50b2-42c3-9b86-d75b3bc3c6fd",
   "metadata": {},
   "outputs": [],
   "source": [
    "yuco_files = list(Path('data_from_cruise/yuco/exports/YUCO-00410025').glob('*/*.csv'))\n",
    "\n",
    "yuco = pd.DataFrame()\n",
    "for fn in yuco_files:\n",
    "    yuco_add = pd.read_csv(fn)\n",
    "    yuco_add.columns = yuco_add.columns.str.replace('�', 'deg')\n",
    "    yuco = pd.concat((yuco, yuco_add))\n",
    "yuco.dropna(how='all', axis=1, inplace=True)\n",
    "yuco = yuco[yuco['INX Latitude (deg)'] > 50]\n",
    "yuco = yuco[yuco['INX Latitude (deg)'] < 55]\n",
    "\n",
    "yuco = yuco[yuco['INX Longitude (deg)'] > 10]\n",
    "yuco = yuco[yuco['AUV Status'] =='MISSION']\n",
    "# check the two lats, check good/bad gps\n",
    "# oxygen is weird, spikes, steps\n",
    "# salinity correction for oxgyen\n",
    "# check pressure sensors\n",
    "# altitude\n",
    "# velocities are weird\n",
    "yuco['datetime'] = pd.to_datetime(yuco['Timestamp (s) UTC+0'],  unit='s')\n",
    "yuco = yuco.sort_values('datetime')\n",
    "yuco.index = np.arange(len(yuco))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6c67e9cd-fc07-4158-9f8d-669523a7c207",
   "metadata": {},
   "outputs": [],
   "source": [
    "#list(yuco)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6cb01f74-ecc3-4837-a553-b79f210062ad",
   "metadata": {},
   "outputs": [],
   "source": [
    "yuco.to_csv('data_cleaned/yuco.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1ec8cbc6-399e-40a5-b457-5d55bda66b9a",
   "metadata": {},
   "source": [
    "406556 rows × 42 columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "18c72c70-b4fd-49c0-a595-0f946819ea97",
   "metadata": {},
   "outputs": [],
   "source": [
    "yuco"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "113a2cc8-9b45-43e9-a4ce-ce5ad85ce71d",
   "metadata": {},
   "outputs": [],
   "source": [
    "ds_yuco = utils.ds_from_df(yuco)\n",
    "ds_yuco.attrs[\"instrument name\"] = \"Seaber YUCO AUV\" \n",
    "ds_yuco.attrs[\"instrument serial\"] = \"YUCO-00410025\"\n",
    "ds_yuco.attrs[\"instrument calibration date\"] = \"2021-03-12\"\n",
    "ds_yuco.attrs[\"platform_type\"] = \"autonomous underwater vehicle\"\n",
    "ds_yuco.attrs[\"platform_type_SDN\"] = \"SDN:L06::25\"\n",
    "ds_yuco.attrs[\"platform_type_URI\"] = \"https://vocab.nerc.ac.uk/collection/L06/current/25/\"\n",
    "ds_yuco.attrs[\"sensor_model\"] = \"RBR legato CTD\"\n",
    "ds_yuco.attrs[\"sensor_model_SDN\"] = \"SDN:L22::TOOL1745\"\n",
    "ds_yuco.attrs[\"sensor_model_URI\"] = \"https://vocab.nerc.ac.uk/collection/L22/current/TOOL1745/\"\n",
    "\n",
    "date_str = ds_yuco.attrs[\"date_created\"]\n",
    "iso_str = date_str.split(\".\")[0].replace(\"-\", \"\").replace(\" \", \"T\").replace(\":\", \"\") + \"Z\"\n",
    "fn = f\"Baltic-sea_YUCO_OLAMUR-WP4_{iso_str}\"\n",
    "ds_yuco.attrs[\"dataset_id\"] = fn\n",
    "ds_yuco.attrs[\"title\"] = fn\n",
    "ds_yuco.attrs[\"summary\"] = \"AUV data collected during the Offshore Low-trophic Aquaculture in Multi-Use Scenario Realisation (OLAMUR)\"\n",
    "ds_yuco"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "40f422e9-3d9a-49e4-a156-8c01538e2313",
   "metadata": {},
   "outputs": [],
   "source": [
    "ds_yuco.to_netcdf(f\"data_for_erddap/yuco/{fn}.nc\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "517d5152-df5a-4702-9bf1-24536bac87d4",
   "metadata": {},
   "source": [
    "# CTD"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "085f1a9c-f8a1-4a2d-8d8e-90a96d0f87ea",
   "metadata": {},
   "outputs": [],
   "source": [
    "ctd_files = list(Path('data_from_cruise/ctd/').glob('*.TOB'))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bb57a43e-dc99-453b-8efd-af3bfac98095",
   "metadata": {},
   "outputs": [],
   "source": [
    "positions = pd.read_csv('data_from_cruise/ctd/Positions.txt', sep=' - ', names=['CTD_id', 'location', 'comment'], engine='python')\n",
    "\n",
    "loc_table = positions.location.str.split(expand=True)\n",
    "positions['lat'] = loc_table[0].astype(int) + loc_table[1].str[:-2].astype(float) / 60\n",
    "positions['lon'] = loc_table[2].astype(int) + loc_table[3].str[:-1].astype(float) / 60\n",
    "\n",
    "positions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0b1a18e3-528e-4007-af9a-f83169982d4e",
   "metadata": {},
   "outputs": [],
   "source": [
    "def clean_csv(fn):\n",
    "    with open(fn,encoding = \"ISO-8859-1\") as fp:\n",
    "        for i, line in enumerate(fp):\n",
    "            if 'Datasets' in line:\n",
    "                variables_line = line\n",
    "            if '[ Volt]' in line:\n",
    "                units_line = line\n",
    "            if '          1 ' in line:\n",
    "                skips = i\n",
    "                break\n",
    "    variables = variables_line.split()[2:]\n",
    "    units = units_line.replace(' ', '')[2:-2].split('][')\n",
    "    var_names = ['sample [number]'] + [f'{var} [{unit}]' for var, unit in zip(variables, units)]\n",
    "    df = pd.read_csv(fn, skiprows=skips,  encoding = \"ISO-8859-1\", names = var_names, parse_dates = [['IntD [Date]', 'IntT [Time]']], delim_whitespace=True)\n",
    "    df = df.rename({'IntD [Date]_IntT [Time]': \"datetime\"}, axis=1)\n",
    "    \n",
    "    if not 'DO [μmol/L]' in list(df):\n",
    "        df['DO [μmol/L]'] = df['DO_mg [mg/l]'] * 31.252\n",
    "    cast_name = fn.name.split('.')[-2]\n",
    "    cast_loc = cast_name.split('_')[-2]\n",
    "    if 'I60' in cast_name:\n",
    "        df[\"latitude\"] = positions[positions.CTD_id==cast_name].lat.mean()\n",
    "        df[\"longitude\"] = positions[positions.CTD_id==cast_name].lon.mean()\n",
    "        print(cast_name)\n",
    "        cast_num = cast_name.split('_')[1]\n",
    "        cast_name = f\"OLAMUR_20240528_CTD{cast_num}_1\"\n",
    "        print(cast_name)\n",
    "    else:\n",
    "        loc_df = quinsy[quinsy['[Mainline] Name']==cast_loc]\n",
    "        df[\"latitude\"] = loc_df['A-Frame_Extended Latitude'].mean()\n",
    "        df[\"longitude\"] = loc_df['A-Frame_Extended Longitude'].mean()\n",
    "    fn_out = Path(f'data_cleaned/ctd/csv/{cast_name}.csv')\n",
    "    df.to_csv(fn_out, index=False)\n",
    "    df[\"cast_name\"] = cast_name\n",
    "\n",
    "    return df\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "51b421c3-6caf-434a-8db9-24f0c9ba9d79",
   "metadata": {},
   "outputs": [],
   "source": [
    "for fn in ctd_files:\n",
    "    df = clean_csv(fn)\n",
    "    cast_name = fn.name.split('.')[-2][7:]\n",
    "    if 'I60' in str(fn):\n",
    "        print(cast_name)\n",
    "        cast_num = cast_name.split('_')[1]\n",
    "        cast_name = f\"20240528_CTD{cast_num}_1\"\n",
    "        print(cast_name)\n",
    "    if np.isnan(df.longitude).all():\n",
    "        print(\"location not found, skipping:\", cast_name)\n",
    "        continue\n",
    "    ds_ctd = utils.ds_from_df(df)\n",
    "    ds_ctd.attrs[\"instrument name\"] = \"Sea & Sun Technology\" \n",
    "    ds_ctd.attrs[\"instrument serial\"] = \"CTM1794\"\n",
    "    ds_ctd.attrs[\"instrument calibration date\"] = \"2021-07-28\"\n",
    "    ds_ctd.attrs[\"platform_type\"] = \"lowered unmanned submersible\"\n",
    "    ds_ctd.attrs[\"platform_type_SDN\"] = \"SDN:L06::26\"\n",
    "    ds_ctd.attrs[\"platform_type_URI\"] = \"https://vocab.nerc.ac.uk/collection/L06/current/26/\"\n",
    "    ds_ctd.attrs[\"sensor_model\"] = \"Sea & Sun Technology CTD 90 M Series II\"\n",
    "\n",
    "    \n",
    "    date_str = ds_ctd.attrs[\"date_created\"]\n",
    "    iso_str = date_str.split(\".\")[0].replace(\"-\", \"\").replace(\" \", \"T\").replace(\":\", \"\") + \"Z\"\n",
    "    fn = f\"Baltic-sea_{cast_name}_OLAMUR-WP4_{iso_str}\"\n",
    "    ds_ctd.attrs[\"dataset_id\"] = fn\n",
    "    ds_ctd.attrs[\"title\"] = fn\n",
    "    ds_ctd.attrs[\"summary\"] = \"CTD data collected during the Offshore Low-trophic Aquaculture in Multi-Use Scenario Realisation (OLAMUR) project 2023-2024\"\n",
    "    ds_ctd.to_netcdf(f\"data_for_erddap/ctd/{fn}.nc\")\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a14eb2f1-e025-4fb8-85ba-55937f3b7572",
   "metadata": {},
   "outputs": [],
   "source": [
    "ds_ctd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "07230ca0-0f48-4fa9-bdea-1351fd255a85",
   "metadata": {},
   "outputs": [],
   "source": [
    "ds_yuco"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1dfaeff3-687c-4b27-87f8-57fbf9ade2fc",
   "metadata": {},
   "source": [
    "# gridded ctd data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e8cf2ef9-cb79-4e83-92e7-a39ede2cc3b2",
   "metadata": {},
   "source": [
    "\"\"\"\n",
    "ds = xr.Dataset()\n",
    "casts = df_ctd.cast_number.unique()\n",
    "box_depth = 1\n",
    "pressure_bins = np.arange(0.5, 31.5, box_depth)\n",
    "ds['cast_number'] = ('cast_number', casts, {\"name\": \"cast_number\"})\n",
    "ds['pressure_bin'] = ('pressure_bin',pressure_bins, {\"name\": \"pressure_bin\"})\n",
    "vars = list(ds_ctd)\n",
    "vars.append(\"datetime\")\n",
    "\n",
    "for name in vars:\n",
    "    if \"cast\" in name:\n",
    "        continue\n",
    "    if name == \"datetime\":\n",
    "        values = np.empty((len(pressure_bins), len(casts)), dtype=datetime.datetime)\n",
    "    else:\n",
    "        values = np.empty((len(pressure_bins), len(casts)))\n",
    "    pressure = df_clean['pressure']\n",
    "    for i, cast_num in enumerate(casts):\n",
    "        df_cast = df_clean[df_clean.cast_number == cast_num]\n",
    "        for j, pressure_centre in enumerate(pressure_bins):\n",
    "            max_pressure = pressure_centre + 0.5 * box_depth\n",
    "            min_pressure = pressure_centre - 0.5 * box_depth\n",
    "            min_box = df_cast[df_cast.pressure >= min_pressure]\n",
    "            box = min_box[min_box.pressure < max_pressure]\n",
    "            if box[name].dtype =='<M8[ns]':\n",
    "                try:\n",
    "                    values[j, i] = box[name].values[0]\n",
    "                except:\n",
    "                    values[j, i] = None\n",
    "            else:\n",
    "                values[j, i] = np.nanmean(box[name])\n",
    "    ds[name] = (('pressure_bin', 'cast_number'), values, utils.attrs_dict[name])\n",
    "ds.attrs = utils.attrs\n",
    "ds_ctd_gridded = ds\n",
    "#ds_ctd_gridded.to_netcdf(\"data_cleaned/ctd/nc/ctd_gridded.nc\")\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b50cae3f-6307-49ac-8777-d7dc270cecf3",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}