{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from datetime import datetime\n",
    "from netCDF4 import Dataset as ds\n",
    "import numpy as np\n",
    "import dateutil as du \n",
    "import parser\n",
    "import pytz\n",
    "import dateutil.parser \n",
    "import pandas as pd\n",
    "from pandas import DataFrame\n",
    "import csv\n",
    "#import glob, os\n",
    "from os.path import expanduser\n",
    "from pathlib import Path\n",
    "import xarray as xr "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "### Load .CSV and \n",
    "### subset bleaching events (conditional, e.g. exclude NaN)\n",
    "### Filter only CodeID, site names and country\" \n",
    "df = pd.read_csv('CBdbV2.csv',encoding='latin-1', low_memory=False)\n",
    "df = df[['RECORD_ID','COUNTRY','LAT_corrected','LON_corrected','SEVERITY_CODE','DATE','MONTH','YEAR']]\n",
    "df = pd.DataFrame(df)\n",
    "#### Convert index into column\n",
    "#df.reset_index(level=0, inplace=True)\n",
    "df['ITEM_ID'] = df.index.astype(str).str.zfill(7)   # add '0s' at the beginning of the number and convert as string \n",
    "df = df[pd.to_numeric(df['MONTH'], errors='coerce').notnull()] # Drop rows that do not have as.numeric DATE (DAY)\n",
    "df['MONTH'] = df['MONTH'].apply(lambda x: int(float(x)))\n",
    "df['MONTH'] = df['MONTH'].apply('{0:0>2}'.format)\n",
    "df['DUMMY_DAY'] = df['MONTH'].apply(lambda x: 15 if x == '02' else 16) \n",
    "df['dummy_date'] = df.apply(lambda x: '%s-%s-%s' % (x['YEAR'], int(float(x['MONTH'])), x['DUMMY_DAY']), axis=1) # create a column in datetime format; %s - String (or any object with a string representation, like numbers), %d - Integers %f - Floating point numbers\n",
    "df['dummy_date'] = df['dummy_date'].apply(lambda x: datetime.strptime(x, \"%Y-%m-%d\")) # use datetime funtion to convert string 'full_date' to datetime \n",
    "### Re-order by column starting by 'ID' and renaming LAT and LON\n",
    "df['dummy_date'] = pd.to_datetime(df['dummy_date']).dt.to_period('M')\n",
    "df['dummy_date'] = df['dummy_date'].apply(lambda x: x.to_timestamp()) # convert Period M to timestamp \n",
    "df = df.reindex(columns=['ITEM_ID','COUNTRY','LAT_corrected','LON_corrected','SEVERITY_CODE','MONTH','YEAR','dummy_date'])\n",
    "df.rename(columns={'LAT_corrected': 'lat', 'LON_corrected': 'lon'}, inplace=True) # use \"implace = True\" to rename the existing DataFrame (rather than creating a copy)\n",
    "df = df.dropna(axis=0, how='any')  # Drop row with NaN if any"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.loc[(df['YEAR'] >= 2001) & (df['YEAR'] <= 2017)] \n",
    "len(df) # the value should be 34,830 (# rows) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['dummy_date']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Function to slice the filename with .nc extention, this will be used to open the files in directory\n",
    "def file_info(ncfilename):\n",
    "    with ds(ncfilename,'r') as ncdat:\n",
    "        #string = str(ncfilename)\n",
    "        #filename = string[-16:] \n",
    "        array = xr.open_dataset(ncfilename) # open_dataset works fine with either full path or filename.nc \n",
    "        return array  \n",
    "def array_sel(array):\n",
    "    dsloc = array.sel(lon=lon,lat=lat,time=time,method='nearest') \n",
    "    clt = dsloc['cldarea_total_daynight_mon']\n",
    "    return clt "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "home = Path(expanduser(\"~\"))\n",
    "x = []\n",
    "for row in df.itertuples():\n",
    "        lat = row.lat,\n",
    "        lon = row.lon\n",
    "        time = row.dummy_date\n",
    "        path = home / Path(\"F:/...file.nc\")\n",
    "        array = file_info(path)\n",
    "        clt_val = array_sel(array)\n",
    "        x.append(clt_val)\n",
    "clt = [item[0].values for item in x]\n",
    "df['CLT'] = clt\n",
    "df.to_csv('CLT.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "file_extension": ".py",
  "interpreter": {
   "hash": "3f71bcf4079f0e07257118d373ab1ac71537af286b04ad3506191c41e8119b06"
  },
  "kernelspec": {
   "display_name": "Python 3.7.6 64-bit ('base': conda)",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "name": "python",
   "version": "3.7.6"
  },
  "mimetype": "text/x-python",
  "name": "python",
  "npconvert_exporter": "python",
  "orig_nbformat": 2,
  "pygments_lexer": "ipython3",
  "version": 3
 },
 "nbformat": 4,
 "nbformat_minor": 2
}