Read_NCEI.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "f4a7eba7",
   "metadata": {},
   "outputs": [],
   "source": [
    "import gcsfs\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import re\n",
    "import openpyxl"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "685e66f0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define path to NCEI metadata and get system path with gcsfs\n",
    "path = 'noaa-passive-bioacoustic/big_query_metadata'\n",
    "fs = gcsfs.GCSFileSystem(path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "677e78cf",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "noaa-passive-bioacoustic/big_query_metadata/NCEI_ADEON_PAD_metadata.xlsx\n"
     ]
    }
   ],
   "source": [
    "file_list = fs.ls(path)\n",
    "allData = pd.DataFrame()\n",
    "# Iterate over files. Currently won't read .xlsx, only .csv\n",
    "for file in file_list[1:]:\n",
    "    print(file)\n",
    "    with fs.open(file,'rb') as f:\n",
    "#         if 'xlsx' in file:\n",
    "#             df = pd.read_excel(f,sheet_name=1)\n",
    "        if 'csv' in file:\n",
    "            df = pd.read_csv(f)\n",
    "            \n",
    "            # Concatenate all dataframes into single dataframe\n",
    "            if 'SHAPE' in df.columns:\n",
    "                allData = pd.concat([allData,df[ [\"FILE_NAME\" , \"START_DATE\", \"START_TIME\", df.filter(like='RATE').columns[0], \n",
    "                                                 \"SENSOR_DEPTH\", \"SHAPE\"] ] ], axis=0, ignore_index=True )\n",
    "            else:\n",
    "                allData = pd.concat([allData,df[ [\"FILE_NAME\" , \"START_DATE\", \"START_TIME\", df.filter(like='RATE').columns[0], \n",
    "                                                 \"SENSOR_DEPTH\", \"LAT\", \"LON\" ] ] ], axis=0, ignore_index=True ) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "29b36e43",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Parser for lat/lon formats\n",
    "def cruise_track_parser(s: str):\n",
    "    i = 0\n",
    "    j = len(s) - 1\n",
    "    while s[i] != \"(\":\n",
    "        i += 1\n",
    "    while s[j] != \")\":\n",
    "        j -= 1\n",
    "    s = s[i+1:j]\n",
    "    s = s.split(\",\")\n",
    "    output = []\n",
    "    sep = re.compile(r\"[\\s\\t]+\")\n",
    "    for entry in s:\n",
    "        e = re.split(sep, entry.strip())\n",
    "        output.append([float(e[0][1:]), float(e[1][:-1])])\n",
    "    return np.array(output)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "999240cc",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Modify data for consistent formatting\n",
    "\n",
    "MIN_LAT = np.zeros(allData.shape[0])\n",
    "MIN_LON = np.zeros(allData.shape[0])\n",
    "MAX_LAT = np.zeros(allData.shape[0])\n",
    "MAX_LON = np.zeros(allData.shape[0])\n",
    "SAMPLE_RATE = np.zeros(allData.shape[0])\n",
    "SENSOR_DEPTH = np.zeros(allData.shape[0])\n",
    "\n",
    "for index, row in allData.iterrows():\n",
    "        \n",
    "    if type(row['SHAPE'])==float:\n",
    "        minLat = row['LAT']\n",
    "        maxLat = row['LAT']\n",
    "        minLon = row['LON']\n",
    "        maxLon = row['LON']\n",
    "        \n",
    "    elif 'MULTIPOINT' in row['SHAPE']:\n",
    "        tmp = cruise_track_parser(row['SHAPE'])\n",
    "        minLat = np.min(tmp[:,1])\n",
    "        maxLat = np.max(tmp[:,1])\n",
    "        minLon = np.min(tmp[:,0])\n",
    "        maxLon = np.max(tmp[:,0])\n",
    "        \n",
    "    elif 'POINT' in row['SHAPE']:\n",
    "        tmp = cruise_track_parser(row['SHAPE'])\n",
    "        lat = tmp[0][1] # row['SHAPE'].split(' ')[1][2:]\n",
    "        lon = tmp[0][0] # row['SHAPE'].split(' ')[2][:-2]\n",
    "        minLat = float(lat)\n",
    "        maxLat = float(lat)\n",
    "        minLon = float(lon)\n",
    "        maxLon = float(lon)\n",
    "        \n",
    "    MIN_LAT[index] = minLat\n",
    "    MAX_LAT[index] = maxLat\n",
    "    MIN_LON[index] = minLon\n",
    "    MAX_LON[index] = maxLon\n",
    "    \n",
    "    if np.isnan(row['SAMPLE_RATE']):\n",
    "        SAMPLE_RATE[index] = row['SAMPLE_RATE_Hz']\n",
    "    else:\n",
    "        SAMPLE_RATE[index] = 1000*row['SAMPLE_RATE']\n",
    "        \n",
    "    if row['SENSOR_DEPTH'] < 0:\n",
    "        SENSOR_DEPTH[index] = abs(row['SENSOR_DEPTH'])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4f14d971",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop unneeded columns and replace others with new formatting\n",
    "allData = allData.drop(['SHAPE','LAT','LON', 'SAMPLE_RATE_Hz','SAMPLE_RATE','SAMPLE_RATE_Hz','SENSOR_DEPTH'],axis=1)\n",
    "allData['MIN_LAT'] = MIN_LAT\n",
    "allData['MAX_LAT'] = MIN_LAT\n",
    "allData['MIN_LON'] = MIN_LON\n",
    "allData['MAX_LON'] = MAX_LON\n",
    "allData['SAMPLE_RATE'] = SAMPLE_RATE\n",
    "allData['SENSOR_DEPTH'] = SENSOR_DEPTH"
   ]
  }
 ],
 "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.8.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}