notebook.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Found duplicate URL: https://podaac-opendap.jpl.nasa.gov/opendap/allData/insitu/L2/spurs1/mooring/\n",
      "Found duplicate URL: https://podaac-opendap.jpl.nasa.gov/opendap/allData/insitu/L2/spurs2/mooring/\n",
      "Finished reading URLS.\n",
      "Finished reading preprocess/remapped_varialbes.txt\n",
      "Finished reading preprocess/varz_to_depth_assignment.txt\n",
      "done.\n"
     ]
    }
   ],
   "source": [
    "import xarray as xr\n",
    "import netCDF4 as nc\n",
    "import pandas as pd\n",
    "import urllib.request\n",
    "from urllib.parse import urlparse\n",
    "import os, json, requests\n",
    "from bs4 import BeautifulSoup\n",
    "import ipywidgets as widgets\n",
    "from collections import defaultdict\n",
    "import re\n",
    "\n",
    "debug = False # Set to True to include detailed print statements (for debugging).\n",
    "\n",
    "# Get available spurs datasets using podaac URL.\n",
    "spurs_source='https://podaac.jpl.nasa.gov/api/cmr/dataset?format=umm_json&page_size=38&sortField=score&ids=Projects&values=SPURS&search='\n",
    "series={}\n",
    "with urllib.request.urlopen(spurs_source) as url:\n",
    "    data = json.loads(url.read().decode())\n",
    "    for item in data['items']:\n",
    "        for element in item['umm']['RelatedUrls']:\n",
    "            if (element['Type']=='USE SERVICE API'):\n",
    "                dataset_name = item['umm']['CollectionCitations'][0]['SeriesName']\n",
    "                series[dataset_name] = element['URL']\n",
    "                \n",
    "total_file_count = 0\n",
    "urls = set()\n",
    "for source in series.values():\n",
    "    if source in urls:\n",
    "        print ('Found duplicate URL: ' + source)\n",
    "        continue\n",
    "    urls.add(source)\n",
    "    soup = BeautifulSoup(requests.get(source).content, 'html.parser')\n",
    "    for a in soup.find_all('a', href=True):\n",
    "        if a['href'].endswith('.nc') and 'viewers' not in a['href']:\n",
    "            total_file_count += 1\n",
    "\n",
    "print ('Finished reading URLS.')\n",
    "\n",
    "varz_to_mapped_name = {}\n",
    "mapped_names = set()\n",
    "skip = True\n",
    "with open(\"preprocess/remapped_varialbes.txt\") as fp:\n",
    "    for line in fp:\n",
    "        if skip:\n",
    "            skip = False\n",
    "            continue\n",
    "        groups = re.match(r\"(.*) : (.*)\", line).groups()\n",
    "        varz_to_mapped_name[groups[0].strip()] = groups[1].strip()\n",
    "        mapped_names.add(groups[1].strip())\n",
    "sorted_mapped_names = sorted(mapped_names)\n",
    "print ('Finished reading preprocess/remapped_varialbes.txt')\n",
    "\n",
    "# Read preprocessed data to populate depth mapping.\n",
    "varz_to_depth_assignment = {}\n",
    "skip = True\n",
    "with open(\"preprocess/varz_to_depth_assignment.txt\") as fp:\n",
    "    for line in fp:\n",
    "        if skip:\n",
    "            skip = False\n",
    "            continue\n",
    "        groups = re.match(r\"(.*) : (.*)\", line).groups()\n",
    "        varz_to_depth_assignment[groups[0].strip()] = groups[1].strip()\n",
    "print ('Finished reading preprocess/varz_to_depth_assignment.txt')\n",
    "\n",
    "print ('done.')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "d90a6373970f4195b802412b3500a707",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "SelectMultiple(description='Please select SPURS data sources', layout=Layout(width='600px'), options={'SPURS-1…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Display dataset selection widget.\n",
    "\n",
    "style = {'description_width': 'initial'}\n",
    "w1 = widgets.SelectMultiple(\n",
    "    options=series,\n",
    "    rows=10,\n",
    "    layout=widgets.Layout(width='600px'),\n",
    "    description='Please select SPURS data sources',\n",
    "    style=style,\n",
    "    disabled=False\n",
    ")  \n",
    "display(w1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "selected: https://podaac-opendap.jpl.nasa.gov/opendap/allData/insitu/L2/spurs1/ecomapper/\n",
      "dataset_name: spurs1_ecomapper\n",
      "processing file: SPURS_Knorr_Ecomapper1\n",
      "chunking the dataframe (to handle memory issues) - chunkparams: \n",
      "{'obs': 5}\n",
      "Processed 1 / 2 files.\n",
      "processing file: SPURS_Knorr_Ecomapper2\n",
      "chunking the dataframe (to handle memory issues) - chunkparams: \n",
      "{'obs': 55}\n",
      "Processed 2 / 2 files.\n",
      "\n",
      "\n",
      "done.\n"
     ]
    }
   ],
   "source": [
    "import dask.dataframe\n",
    "from datetime import datetime\n",
    "\n",
    "# Get NetCDF files for selected datasets. Load data in dataframes.\n",
    "\n",
    "debug = False # set to True to print more debugging statements.\n",
    "ignore_depth_assignment = True # set to false to consider depth assignment (note that there are some files with multiple depth assingment values, we throw error for such cases)\n",
    "url_prefix = 'https://podaac-opendap.jpl.nasa.gov/opendap/allData/insitu/L2/'\n",
    "\n",
    "file_name_to_dataset_name = {}\n",
    "file_name_to_var_name_dict = {}\n",
    "file_name_to_coords = {}\n",
    "file_name_to_records = {}\n",
    "file_name_to_dask_records = {}\n",
    "file_name_to_matched_depth_assignment_varz_to_depth_map = {}\n",
    "\n",
    "times = ['time', 'Time', 'TIME', 'JULD', 'ctd_time']\n",
    "special_cols = ['time', 'lat', 'latitude', 'lon', 'longitude', 'depth', 'z', 'juld']\n",
    "\n",
    "total_file_count = 0\n",
    "urls = set()\n",
    "for source in w1.value:\n",
    "    if source in urls:\n",
    "        print ('Found duplicate URL: ' + source)\n",
    "        continue\n",
    "    urls.add(source)\n",
    "    soup = BeautifulSoup(requests.get(source).content, 'html.parser')\n",
    "    for a in soup.find_all('a', href=True):\n",
    "        if a['href'].endswith('.nc') and 'viewers' not in a['href']:\n",
    "            total_file_count += 1\n",
    "\n",
    "processed_files = []\n",
    "for source in w1.value:\n",
    "    url_index = source.index(url_prefix)\n",
    "    # use url to get dataset name, for example that name for dataset in https://podaac/../../spurs1/adcp/ will be spurs1_adcp\n",
    "    dataset_name = source[(url_index + len(url_prefix)):-1].replace('/', '_')\n",
    "    print (\"selected: \" + source)\n",
    "    print ('dataset_name: ' + dataset_name)\n",
    "    soup = BeautifulSoup(requests.get(source).content, 'html.parser')\n",
    "    for a in soup.find_all('a', href=True):\n",
    "        if a['href'].endswith('.nc') and 'viewers' not in a['href']:\n",
    "            netcdf=source+a['href']\n",
    "            \n",
    "            # Download and process files for this dataset.\n",
    "            base_dir = 'netcdf/'\n",
    "            full_file_name = os.path.basename(urlparse(netcdf).path)\n",
    "            file_name = full_file_name[0:full_file_name.index('.nc')]\n",
    "            file_name_to_dataset_name[file_name] = dataset_name\n",
    "            file_name_to_var_name_dict[file_name] = {}\n",
    "            file_name_to_coords[file_name] = []\n",
    "            \n",
    "            urllib.request.urlretrieve(netcdf, base_dir + file_name)\n",
    "            netcdf_data = xr.open_dataset(base_dir + file_name,decode_times=False,engine='netcdf4')\n",
    "            standard_name = lambda v: v is not None\n",
    "            netcdf_data = netcdf_data.filter_by_attrs(standard_name=standard_name)\n",
    "            \n",
    "            print ('processing file: ' + file_name)\n",
    "            \n",
    "            vars_has_time = False\n",
    "            coords_has_time = False\n",
    "            \n",
    "            # Required for depth_assignment changes.\n",
    "            matched_depth_assignment_varz_to_depth_map = {}\n",
    "            \n",
    "            for coord in netcdf_data.coords:\n",
    "                file_name_to_coords[file_name].append(coord)\n",
    "            \n",
    "            # Here symbol is like u, v, etc (i.e., variable name present in data frame)\n",
    "            for symbol in netcdf_data.keys():\n",
    "                if symbol.strip().lower() in special_cols:\n",
    "                    file_name_to_coords[file_name].append(symbol)\n",
    "                    if debug:\n",
    "                        print ('symbol: ' + symbol + ' is treated as coord as it is in special cols: ' + ','.join(special_cols))\n",
    "                    continue\n",
    "                file_name_to_var_name_dict[file_name][symbol] = netcdf_data[symbol].standard_name\n",
    "                if symbol in times:\n",
    "                    vars_has_time = True\n",
    "                # Following is for depth_assignment changes.\n",
    "                standard_name = netcdf_data[symbol].standard_name\n",
    "                key = symbol + '*' + standard_name\n",
    "                if key not in varz_to_mapped_name.keys():\n",
    "                    # ignore symbols not present in preprocess/remapped_varaibles.txt.\n",
    "                    continue\n",
    "                if key in varz_to_depth_assignment.keys():\n",
    "                    # stores depth_assignment for the varz present in this file that also has a depth assignment defined in varz_to_depth_assignment map.\n",
    "                    #\n",
    "                    # for example, air_temp_10*air_temperature is a varz, if it is present in data_frame then depth has to be set to 10.\n",
    "                    # we store <air_temperature, 10> as the matched values for this data_frame (to be used while exporting data).\n",
    "                    # we assert that only 1 such varz should match in the data_frame (hence we expect only 1 depth value).\n",
    "                    # so we populate 1 depth value for a file in file_name_to_depth_assignment map.\n",
    "                    \n",
    "                    # print ('adding depth_assignment match: symbol*standard_name->mapped_name: ' + key + ' -> ' + varz_to_mapped_name[key])\n",
    "                    matched_depth_assignment_varz_to_depth_map[key] = varz_to_depth_assignment[key]\n",
    "            file_name_to_matched_depth_assignment_varz_to_depth_map[file_name] = matched_depth_assignment_varz_to_depth_map\n",
    "            \n",
    "            if debug:\n",
    "                print (netcdf_data)\n",
    "                \n",
    "            for t in times:\n",
    "                if t in netcdf_data.keys():\n",
    "                    time_var = t\n",
    "            \n",
    "            time_origin=netcdf_data[time_var].units.split(' ')[2] # 1950-01-01\n",
    "            if netcdf_data[time_var].units.split(' ')[0] == 'seconds':\n",
    "                time_unit = 's'\n",
    "            else:\n",
    "                time_unit='d'\n",
    "                \n",
    "            if debug:\n",
    "                if time_var == 'JULD':\n",
    "                    print ('Found JULD')\n",
    "                if time_origin != '1970-01-01':\n",
    "                    print ('Found time_origin: ' + time_origin)\n",
    "                \n",
    "                print (time_var)\n",
    "                print (time_unit)\n",
    "                print (time_origin)\n",
    "            \n",
    "            chunksize = 100\n",
    "            chunkprams = {}\n",
    "            for k,v in dict(netcdf_data.dims).items():\n",
    "                divs = [1000, 100, 10]\n",
    "                for d in divs: \n",
    "                    chunksize = int(v/d)\n",
    "                    if d == 1000 and chunksize > 1:\n",
    "                        break\n",
    "                    if chunksize < 10:\n",
    "                        chunksize = v\n",
    "                        break\n",
    "                if v > chunksize :\n",
    "                    chunkprams[k] = chunksize\n",
    "            \n",
    "            print ('chunking the dataframe (to handle memory issues) - chunkparams: ')        \n",
    "            print (chunkprams)\n",
    "            \n",
    "            dd = netcdf_data.chunk(chunkprams).to_dask_dataframe()\n",
    "            dd[time_var] = dd[time_var].map(lambda ts: pd.to_datetime(ts, unit=time_unit, origin=time_origin.rstrip('Z')))\n",
    "            file_name_to_dask_records[file_name] = dd\n",
    "            processed_files.append(a['href'])\n",
    "            \n",
    "            if debug:\n",
    "                print (file_name_to_var_name_dict[file_name])\n",
    "                print (file_name_to_coords[file_name])\n",
    "\n",
    "            print ('Processed ' + str(len(processed_files)) + ' / ' + str(total_file_count) + ' files.')\n",
    "print ('\\n')\n",
    "\n",
    "if debug:\n",
    "    for file_name, matched_depth_assignment_varz_to_depth_map in file_name_to_matched_depth_assignment_varz_to_depth_map.items():\n",
    "        print ('File: ' + file_name + ' has depth_assignment.')\n",
    "        for varz, depth in matched_depth_assignment_varz_to_depth_map.items():\n",
    "            print ('symbol*standard_name : depth -> ' + varz +  ' : ' +str(depth))\n",
    "\n",
    "print ('done.')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "e5fc3ba188694af7a0dba04b6c57186a",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "SelectMultiple(description='1. SPURS_Knorr_Ecomapper1', layout=Layout(width='600px'), options=('coord : time',…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "34c6b92d873449b4997b9ec35bd41389",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "SelectMultiple(description='2. SPURS_Knorr_Ecomapper2', layout=Layout(width='600px'), options=('coord : time',…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Display coords/variable selection widget.\n",
    "# Format of coords displayed: 'coord : coord_name'\n",
    "# Format of vars displayed: 'var_name : var_standard_name'\n",
    "\n",
    "variable_widgets = []\n",
    "file_name_to_values = {}\n",
    "for file_name in file_name_to_var_name_dict.keys():\n",
    "    values = []\n",
    "    for c in file_name_to_coords[file_name]:\n",
    "        found = False\n",
    "        for k in varz_to_mapped_name.keys():\n",
    "            if found:\n",
    "                break\n",
    "            keys = k.split('*')\n",
    "            symbol = keys[0]\n",
    "            standard_name = keys[1]\n",
    "            if c == symbol or c == standard_name:\n",
    "                values.append('coord : ' + varz_to_mapped_name[k])\n",
    "                found = True\n",
    "    for symbol,standard_name in file_name_to_var_name_dict[file_name].items():\n",
    "        if symbol + '*' + standard_name in varz_to_mapped_name.keys():\n",
    "            values.append( symbol + ' : ' + varz_to_mapped_name[symbol + '*' + standard_name])\n",
    "    file_name_to_values[file_name] = values\n",
    "\n",
    "file_number = 1\n",
    "for file_name in file_name_to_var_name_dict.keys():\n",
    "    description = str(file_number) + \". \" + file_name\n",
    "    file_number += 1\n",
    "    style = {'description_width': 'initial'}\n",
    "    w = widgets.SelectMultiple(\n",
    "        options=file_name_to_values[file_name],\n",
    "        rows=10,\n",
    "        layout=widgets.Layout(width='600px'),\n",
    "        description=description,\n",
    "        style=style,\n",
    "        disabled=False\n",
    "    )\n",
    "    variable_widgets.append(w)\n",
    "    display(w)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Including all coords for selected vars ...\n",
      "SPURS_Knorr_Ecomapper1 : coords: time,z,longitude,latitude\n",
      "SPURS_Knorr_Ecomapper2 : coords: time,z,longitude,latitude\n",
      "SPURS_Knorr_Ecomapper1 : vars: conductivity*conductivity\n",
      "SPURS_Knorr_Ecomapper2 : vars: YSI_temperature*temperature\n",
      "done.\n"
     ]
    }
   ],
   "source": [
    "# Set this to True in order to include all coords for a file, when vars are selected.\n",
    "should_include_all_coords = True\n",
    "\n",
    "file_name_to_selected_coords = defaultdict(list)\n",
    "file_name_to_selected_varz = defaultdict(list)\n",
    "for w in variable_widgets:\n",
    "    idx = w.description.index(' ')\n",
    "    file_name = w.description[idx+1:]\n",
    "    for file_var, file_standard_var in file_name_to_var_name_dict[file_name].items():\n",
    "        if file_var + '*' + file_standard_var in varz_to_mapped_name.keys():\n",
    "            mapped_name = varz_to_mapped_name[file_var + '*' + file_standard_var]\n",
    "            if file_var + ' : ' + mapped_name in w.value:\n",
    "                file_name_to_selected_varz[file_name].append(file_var + '*' + file_standard_var)\n",
    "    for coord in file_name_to_coords[file_name]:\n",
    "        if (should_include_all_coords and len(file_name_to_selected_varz[file_name]) > 0) or 'coord : ' + coord in w.value:\n",
    "            file_name_to_selected_coords[file_name].append(coord)\n",
    "\n",
    "if should_include_all_coords:\n",
    "    print ('Including all coords for selected vars ...')\n",
    "else:\n",
    "    print ('Including only selected coords and vars ...')\n",
    "\n",
    "for file_name in sorted(file_name_to_selected_coords):\n",
    "    selected_coords = file_name_to_selected_coords[file_name]\n",
    "    if len(selected_coords) == 0:\n",
    "        continue\n",
    "    print (str(file_name) + \" : coords: \" + ','.join(selected_coords))\n",
    "for file_name in sorted(file_name_to_selected_varz.keys()):\n",
    "    selected_vars = file_name_to_selected_varz[file_name]\n",
    "    if len(selected_vars) == 0:\n",
    "        continue\n",
    "    print (str(file_name) + \" : vars: \" + ','.join(selected_vars))\n",
    "print ('done.')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total csv_cols (coords + vars): 101\n",
      "Done.\n"
     ]
    }
   ],
   "source": [
    "# Read csv header from preprocess/final_header.txt.\n",
    "import csv\n",
    "\n",
    "debug = False\n",
    "\n",
    "csv_cols = ['dataset_name', 'file_name']\n",
    "with open('preprocess/final_header.txt', 'r') as f:\n",
    "    for line in f:\n",
    "        csv_cols.append(line.strip())\n",
    "\n",
    "# Write csv header to output/sprus_data.csv.\n",
    "with open('output/spurs_data.csv', 'w', newline='') as outcsv:\n",
    "    writer = csv.writer(outcsv)\n",
    "    writer.writerow(csv_cols)\n",
    "print ('Total csv_cols (coords + vars): ' + str(len(csv_cols)))\n",
    "\n",
    "header_dict = {}\n",
    "for i in range(len(csv_cols)):\n",
    "    header_dict[csv_cols[i]] = i\n",
    "\n",
    "if debug == True:\n",
    "    print (header_dict)\n",
    "    print (file_name_to_selected_coords)\n",
    "    print (file_name_to_selected_varz)\n",
    "print ('Done.')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Data for file: SPURS_Knorr_Ecomapper1 in dataset: spurs1_ecomapper\n",
      "['spurs1_ecomapper', 'SPURS_Knorr_Ecomapper1', Timestamp('2012-09-30 16:02:50.860003328'), 0.06300000101327896, 26.1494083404541, -38.33799743652344, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 6.203744411468506, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '']\n",
      "['spurs1_ecomapper', 'SPURS_Knorr_Ecomapper1', Timestamp('2012-09-30 16:02:51.879993600'), 0.02800000086426735, 26.14940643310547, -38.3380012512207, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 6.202433109283447, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '']\n",
      "['spurs1_ecomapper', 'SPURS_Knorr_Ecomapper1', Timestamp('2012-09-30 16:02:52.910002176'), 0.02199999988079071, 26.149404525756836, -38.33800506591797, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 6.202518463134766, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '']\n",
      "['spurs1_ecomapper', 'SPURS_Knorr_Ecomapper1', Timestamp('2012-09-30 16:02:53.910007040'), 0.07000000029802322, 26.149402618408203, -38.3380012512207, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 6.200241565704346, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '']\n",
      "['spurs1_ecomapper', 'SPURS_Knorr_Ecomapper1', Timestamp('2012-09-30 16:02:54.910001408'), 0.039000000804662704, 26.14940071105957, -38.3380012512207, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 6.194874286651611, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '']\n",
      "['spurs1_ecomapper', 'SPURS_Knorr_Ecomapper1', Timestamp('2012-09-30 16:02:55.919993856'), 0.06599999964237213, 26.149394989013672, -38.33799362182617, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 6.187694549560547, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '']\n",
      "['spurs1_ecomapper', 'SPURS_Knorr_Ecomapper1', Timestamp('2012-09-30 16:02:56.919998720'), 0.06199999898672104, 26.149391174316406, -38.337989807128906, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 6.185419082641602, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '']\n",
      "['spurs1_ecomapper', 'SPURS_Knorr_Ecomapper1', Timestamp('2012-09-30 16:02:57.919993088'), 0.04899999871850014, 26.149385452270508, -38.33797836303711, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 6.1938934326171875, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '']\n",
      "['spurs1_ecomapper', 'SPURS_Knorr_Ecomapper1', Timestamp('2012-09-30 16:02:58.919997696'), 0.04800000041723251, 26.149383544921875, -38.33797073364258, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 6.203681945800781, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '']\n",
      "['spurs1_ecomapper', 'SPURS_Knorr_Ecomapper1', Timestamp('2012-09-30 16:02:59.920002560'), 0.03799999877810478, 26.149381637573242, -38.33796691894531, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 6.2053375244140625, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '']\n",
      "reached max_records_per_file: 10\n",
      "Data for file: SPURS_Knorr_Ecomapper2 in dataset: spurs1_ecomapper\n",
      "['spurs1_ecomapper', 'SPURS_Knorr_Ecomapper2', Timestamp('2012-09-29 08:22:32.480004096'), 0.02500000037252903, 25.499526977539062, -38.50054931640625, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 27.139999389648438, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '']\n",
      "['spurs1_ecomapper', 'SPURS_Knorr_Ecomapper2', Timestamp('2012-09-29 08:22:33.479998464'), 0.06599999964237213, 25.499523162841797, -38.500553131103516, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 27.139999389648438, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '']\n",
      "['spurs1_ecomapper', 'SPURS_Knorr_Ecomapper2', Timestamp('2012-09-29 08:22:34.480003072'), 0.04399999976158142, 25.49949836730957, -38.50054168701172, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 27.139999389648438, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '']\n",
      "['spurs1_ecomapper', 'SPURS_Knorr_Ecomapper2', Timestamp('2012-09-29 08:22:35.480007936'), 0.06499999761581421, 25.49949073791504, -38.50053405761719, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 27.1299991607666, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '']\n",
      "['spurs1_ecomapper', 'SPURS_Knorr_Ecomapper2', Timestamp('2012-09-29 08:22:36.480002304'), 0.04600000008940697, 25.499492645263672, -38.500526428222656, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 27.1200008392334, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '']\n",
      "['spurs1_ecomapper', 'SPURS_Knorr_Ecomapper2', Timestamp('2012-09-29 08:22:37.480006912'), 0.05000000074505806, 25.499492645263672, -38.50052261352539, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 27.1200008392334, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '']\n",
      "['spurs1_ecomapper', 'SPURS_Knorr_Ecomapper2', Timestamp('2012-09-29 08:22:38.480001792'), 0.0430000014603138, 25.499486923217773, -38.50051498413086, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 27.1200008392334, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '']\n",
      "['spurs1_ecomapper', 'SPURS_Knorr_Ecomapper2', Timestamp('2012-09-29 08:22:39.480006144'), 0.06700000166893005, 25.499483108520508, -38.50050354003906, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 27.1200008392334, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '']\n",
      "['spurs1_ecomapper', 'SPURS_Knorr_Ecomapper2', Timestamp('2012-09-29 08:22:40.480000768'), 0.052000001072883606, 25.499486923217773, -38.50048828125, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 27.1299991607666, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '']\n",
      "['spurs1_ecomapper', 'SPURS_Knorr_Ecomapper2', Timestamp('2012-09-29 08:22:41.480005376'), 0.050999999046325684, 25.49949073791504, -38.50047302246094, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 27.1299991607666, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '']\n",
      "reached max_records_per_file: 10\n",
      "Done.\n"
     ]
    }
   ],
   "source": [
    "import csv\n",
    "pd.options.mode.chained_assignment = None\n",
    "\n",
    "debug = False\n",
    "\n",
    "# limiting to 3 during demo as CSV file is getting too big. \n",
    "# Set to -1 to load all records for every selected file.\n",
    "max_records_per_file = 10\n",
    "\n",
    "# Consider following special columns as coordinates.\n",
    "special_cols = ['time', 'lat', 'latitude', 'lon', 'longitude', 'depth', 'z', 'juld']\n",
    "\n",
    "selected_file_names = [name for name in sorted(file_name_to_selected_coords.keys())]\n",
    "for file_name in [name for name in sorted(file_name_to_selected_varz.keys())]:\n",
    "    selected_vars = file_name_to_selected_varz[file_name]\n",
    "    if len(selected_vars) == 0:\n",
    "            continue\n",
    "    if file_name not in selected_file_names:\n",
    "        selected_file_names.append(file_name)\n",
    "if debug:\n",
    "    print ('selected_file_names: ' + ','.join(selected_file_names))\n",
    "\n",
    "for file_name in selected_file_names:\n",
    "    dataset_name = file_name_to_dataset_name[file_name]\n",
    "    print ('Data for file: ' + file_name + ' in dataset: ' + dataset_name)\n",
    "    \n",
    "    # Store df coords/vars to csv coords/vars for easier lookup while preparing CSV row from df row.\n",
    "    df_cols = []\n",
    "    df_to_csv_dict = {}\n",
    "    \n",
    "    for coord in file_name_to_selected_coords[file_name]:\n",
    "        _coord = coord\n",
    "        special_coord = coord.lower().strip()\n",
    "        if special_coord in special_cols:\n",
    "            if 'lat' in special_coord:\n",
    "                special_coord = 'latitude'\n",
    "            elif 'lon' in special_coord:\n",
    "                special_coord = 'longitude'\n",
    "            elif 'z' in special_coord:\n",
    "                special_coord = 'depth'\n",
    "            elif 'juld' in special_coord:\n",
    "                special_coord = 'time'\n",
    "            _coord = special_coord\n",
    "            \n",
    "        if _coord not in csv_cols:\n",
    "            print ('coord: ' + _coord + ' NOT FOUND in csv cols - THIS SHOULD NOT HAPPEN.')\n",
    "        else:\n",
    "            df_cols.append(coord)\n",
    "            df_to_csv_dict[coord] = _coord\n",
    "            \n",
    "    if debug:\n",
    "        print ('coords...')\n",
    "        print ('df_cols: ' + ','.join(df_cols))\n",
    "        print ('df_to_csv_dict: ' + str(df_to_csv_dict))\n",
    "    \n",
    "    for key in file_name_to_selected_varz[file_name]:\n",
    "        # key = symbol + '*' + standard_name\n",
    "        if key in varz_to_mapped_name.keys():\n",
    "            mapped_name = varz_to_mapped_name[key]\n",
    "            symbol = key.split('*')[0]\n",
    "            special_symbol = symbol.lower().strip()\n",
    "            if symbol in df_to_csv_dict.keys() or special_symbol in df_to_csv_dict.keys():\n",
    "                print ('symbol: ' + symbol + ' already present in df_to_csv_dict - THIS SHOULD BE RARE.')\n",
    "                continue\n",
    "            if special_symbol in special_cols:\n",
    "                print ('symbol: ' + symbol + ' is in special cols, treat it as coordinate - THIS SHOULD BE RARE.')\n",
    "                if 'lat' in special_symbol:\n",
    "                    special_symbol = 'latitude'\n",
    "                elif 'lon' in special_symbol:\n",
    "                    special_symbol = 'longitude'\n",
    "                elif 'z' in special_symbol:\n",
    "                    special_symbol = 'depth'\n",
    "                mapped_name = special_symbol\n",
    "            df_cols.append(symbol)\n",
    "            df_to_csv_dict[symbol] = mapped_name\n",
    "    if debug:\n",
    "        print ('vars...')\n",
    "        print ('df_cols: ' + ','.join(df_cols))\n",
    "        print ('df_to_csv_dict: ' + str(df_to_csv_dict))\n",
    "    \n",
    "    df_header = list(df_to_csv_dict.keys())\n",
    "    dd = file_name_to_dask_records[file_name]\n",
    "    _df = dd[df_header]\n",
    "        \n",
    "    matched_depth_assignment_varz_to_depth_map = file_name_to_matched_depth_assignment_varz_to_depth_map[file_name]\n",
    "    \n",
    "    # Prepare csv_row by populating csv_indexes for coor/var in df. By default include dataset_name, file_name.\n",
    "    csv_row = ['']*len(header_dict)\n",
    "    csv_row[0] = dataset_name \n",
    "    csv_row[1] = file_name\n",
    "    csv_row_count = 0\n",
    "    \n",
    "    with open('output/spurs_data.csv', 'a+', newline='') as out_csv:\n",
    "        writer = csv.writer(out_csv)\n",
    "        for index, df_row in _df.iterrows():\n",
    "            depth_values = []\n",
    "            for df_col in df_cols:\n",
    "                if df_col not in df_to_csv_dict.keys():\n",
    "                    print ('df coord/var: ' + df_col + ' not in df_to_csv_dict - THIS SHOULD NOT HAPPEN.')\n",
    "                    continue\n",
    "                csv_col = df_to_csv_dict[df_col]\n",
    "                \n",
    "                if debug:\n",
    "                    print ('df_col: ' + df_col)\n",
    "                    print ('csv_col: ' + csv_col)\n",
    "                \n",
    "                if csv_col not in header_dict:\n",
    "                    print ('csv coord/var: ' + csv_col + ' not in csv header cols - THIS SHOULD NOT HAPPEN.')\n",
    "                    continue\n",
    "\n",
    "                csv_idx = header_dict[csv_col]\n",
    "                csv_row[csv_idx] = df_row[df_col]\n",
    "\n",
    "                if debug:\n",
    "                    print ('appending csv_col: ' + csv_col + 'at csv_idx: ' + str(csv_idx) + ' value: ' + str(df_row[df_col]) + ' for df_col: ' + df_col)\n",
    "            \n",
    "                # print ('CHECKING FOR DEPTH ASSIGNMENT UPDATES....')\n",
    "                for varz, depth_assignment in matched_depth_assignment_varz_to_depth_map.items():\n",
    "                    # print ('df_col: ' + df_col + ' symbol*standard_name : depth ' + varz + ' : '+ str(depth_assignment))\n",
    "                    if df_col == varz.split('*')[0]:\n",
    "                        depth_values.append(depth_assignment)\n",
    "            \n",
    "            # print row before writing to csv.\n",
    "            if len(depth_values) > 0:\n",
    "                depth_idx = header_dict['depth']\n",
    "                for depth_value in depth_values:\n",
    "                    csv_row_with_depth = csv_row\n",
    "                    csv_row_with_depth[depth_idx] = depth_value\n",
    "                    print (csv_row_with_depth)\n",
    "                    writer.writerow(csv_row_with_depth)\n",
    "            else:\n",
    "                print (csv_row)\n",
    "                writer.writerow(csv_row)\n",
    "            csv_row_count += 1\n",
    "            if max_records_per_file != -1 and csv_row_count >= max_records_per_file:\n",
    "                print ('reached max_records_per_file: ' + str(max_records_per_file))\n",
    "                break\n",
    "\n",
    "print ('Done.')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.9.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}