{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "59d19f73",
   "metadata": {},
   "source": [
    "# LNG Freight Routes - Component Analysis\n",
    "\n",
    "Looking at the underlying components that make up our Freight Routes prices to understand which component is the dominant factor \n",
    "\n",
    "For a full explanation of how to import our Freight Routes data, please refer to our Python Jupyter Notebook Code Samples:\n",
    "\n",
    "https://www.sparkcommodities.com/api/code-examples/jupyter.html"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d1d7ca7a",
   "metadata": {},
   "source": [
    "### Have any questions?\n",
    "\n",
    "If you have any questions regarding our API, or need help accessing specific datasets, please contact us at:\n",
    "\n",
    "__data@sparkcommodities.com__"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9e00ae34",
   "metadata": {},
   "source": [
    "## 1. Importing Data\n",
    "\n",
    "Here we define the functions that allow us to retrieve the valid credentials to access the Spark API.\n",
    "\n",
    "This section can remain unchanged for most Spark API users."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4f265f07",
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "import os\n",
    "import sys\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from base64 import b64encode\n",
    "from urllib.parse import urljoin\n",
    "from pprint import pprint\n",
    "from io import StringIO\n",
    "import datetime\n",
    "import time\n",
    "\n",
    "try:\n",
    "    from urllib import request, parse\n",
    "    from urllib.error import HTTPError\n",
    "except ImportError:\n",
    "    raise RuntimeError(\"Python 3 required\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c32eb493",
   "metadata": {},
   "outputs": [],
   "source": [
    "API_BASE_URL = \"https://api.sparkcommodities.com\"\n",
    "\n",
    "\n",
    "def retrieve_credentials(file_path=None):\n",
    "    \"\"\"\n",
    "    Find credentials either by reading the client_credentials file or reading\n",
    "    environment variables\n",
    "    \"\"\"\n",
    "    if file_path is None:\n",
    "        client_id = os.getenv(\"SPARK_CLIENT_ID\")\n",
    "        client_secret = os.getenv(\"SPARK_CLIENT_SECRET\")\n",
    "        if not client_id or not client_secret:\n",
    "            raise RuntimeError(\n",
    "                \"SPARK_CLIENT_ID and SPARK_CLIENT_SECRET environment vars required\"\n",
    "            )\n",
    "    else:\n",
    "        # Parse the file\n",
    "        if not os.path.isfile(file_path):\n",
    "            raise RuntimeError(\"The file {} doesn't exist\".format(file_path))\n",
    "\n",
    "        with open(file_path) as fp:\n",
    "            lines = [l.replace(\"\\n\", \"\") for l in fp.readlines()]\n",
    "\n",
    "        if lines[0] in (\"clientId,clientSecret\", \"client_id,client_secret\"):\n",
    "            client_id, client_secret = lines[1].split(\",\")\n",
    "        else:\n",
    "            print(\"First line read: '{}'\".format(lines[0]))\n",
    "            raise RuntimeError(\n",
    "                \"The specified file {} doesn't look like to be a Spark API client \"\n",
    "                \"credentials file\".format(file_path)\n",
    "            )\n",
    "\n",
    "    print(\">>>> Found credentials!\")\n",
    "    \n",
    "    return client_id, client_secret\n",
    "\n",
    "\n",
    "def do_api_post_query(uri, body, headers):\n",
    "    \"\"\"\n",
    "    OAuth2 authentication requires a POST request with client credentials before accessing the API.\n",
    "    This POST request will return an Access Token which will be used for the API GET request.\n",
    "    \"\"\"\n",
    "    url = urljoin(API_BASE_URL, uri)\n",
    "\n",
    "    data = json.dumps(body).encode(\"utf-8\")\n",
    "\n",
    "    # HTTP POST request\n",
    "    req = request.Request(url, data=data, headers=headers)\n",
    "    try:\n",
    "        response = request.urlopen(req)\n",
    "    except HTTPError as e:\n",
    "        print(\"HTTP Error: \", e.code)\n",
    "        print(e.read())\n",
    "        sys.exit(1)\n",
    "\n",
    "    resp_content = response.read()\n",
    "\n",
    "    # The server must return HTTP 201. Raise an error if this is not the case\n",
    "    assert response.status == 201, resp_content\n",
    "\n",
    "    # The server returned a JSON response\n",
    "    content = json.loads(resp_content)\n",
    "\n",
    "    return content\n",
    "\n",
    "\n",
    "def do_api_get_query(uri, access_token, format='json'):\n",
    "    \"\"\"\n",
    "    After receiving an Access Token, we can request information from the API.\n",
    "    Supports both JSON (default) and CSV responses.\n",
    "    \"\"\"\n",
    "    url = urljoin(API_BASE_URL, uri)\n",
    "\n",
    "    if format == 'json':\n",
    "        headers = {\n",
    "            \"Authorization\": \"Bearer {}\".format(access_token),\n",
    "            \"Accept\": \"application/json\",\n",
    "        }\n",
    "    elif format == 'csv':\n",
    "        headers = {\n",
    "            \"Authorization\": \"Bearer {}\".format(access_token),\n",
    "            \"Accept\": \"text/csv\",\n",
    "        }\n",
    "    else:\n",
    "        raise ValueError(\"The format parameter only takes 'csv' or 'json' as inputs\")\n",
    "\n",
    "    print(\"Fetching {}\".format(url))\n",
    "\n",
    "    # HTTP GET request\n",
    "    req = request.Request(url, headers=headers)\n",
    "    try:\n",
    "        response = request.urlopen(req)\n",
    "    except HTTPError as e:\n",
    "        print(\"HTTP Error: \", e.code)\n",
    "        print(e.read())\n",
    "        sys.exit(1)\n",
    "\n",
    "    resp_content = response.read()\n",
    "\n",
    "    # The server must return HTTP 200. Raise an error if this is not the case\n",
    "    assert response.status == 200, resp_content\n",
    "\n",
    "    # Storing response based on requested format\n",
    "    if format == 'json':\n",
    "        content = json.loads(resp_content)\n",
    "    elif format == 'csv':\n",
    "        content = resp_content\n",
    "\n",
    "    return content\n",
    "\n",
    "\n",
    "def get_access_token(client_id, client_secret):\n",
    "\n",
    "    payload = \"{}:{}\".format(client_id, client_secret).encode()\n",
    "    headers = {\n",
    "        \"Authorization\": b64encode(payload).decode(),\n",
    "        \"Accept\": \"application/json\",\n",
    "        \"Content-Type\": \"application/json\",\n",
    "    }\n",
    "    body = {\n",
    "        \"grantType\": \"clientCredentials\",\n",
    "    }\n",
    "\n",
    "    content = do_api_post_query(uri=\"/oauth/token/\", body=body, headers=headers)\n",
    "\n",
    "    print(\n",
    "        \">>>> Successfully fetched an access token {}****, valid {} seconds.\".format(\n",
    "            content[\"accessToken\"][:5], content[\"expiresIn\"]\n",
    "        )\n",
    "    )\n",
    "\n",
    "    return content[\"accessToken\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1e890e9e",
   "metadata": {},
   "source": [
    "## N.B. Credentials\n",
    "\n",
    "Here we call the above functions, and input the file path to our credentials.\n",
    "\n",
    "N.B. You must have downloaded your client credentials CSV file before proceeding. Please refer to the API documentation if you have not dowloaded them already.  Instructions for downloading your credentials can be found here:\n",
    "\n",
    "https://www.sparkcommodities.com/api/introduction.html\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "51b8a89c",
   "metadata": {},
   "outputs": [],
   "source": [
    "## Input your file location here\n",
    "client_id, client_secret = retrieve_credentials(file_path=\"/tmp/client_credentials.csv\")\n",
    "\n",
    "# Authenticate:\n",
    "access_token = get_access_token(client_id, client_secret)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2345f33a",
   "metadata": {},
   "source": [
    "# 2. Defining functions to fetch Routes data\n",
    "\n",
    "All endpoint information for LNG Freight Routes can be found in on the API website, and in the dedicated code sample:\n",
    "\n",
    "https://www.sparkcommodities.com/api/lng-freight/routes.html"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a2377628",
   "metadata": {},
   "outputs": [],
   "source": [
    "## Define function for listing routes reference data\n",
    "\n",
    "def list_routes_refdata(access_token):\n",
    "\n",
    "    content = do_api_get_query(uri=\"/v1.0/routes/\", access_token=access_token)\n",
    "\n",
    "    reldates = content[\"data\"][\"sparkReleaseDates\"]\n",
    "\n",
    "    data_dict = content[\"data\"]\n",
    "\n",
    "    return reldates, data_dict\n",
    "\n",
    "\n",
    "# Fetch all reference data\n",
    "reldates, refdata_dict = list_routes_refdata(access_token)\n",
    "\n",
    "# formatting as a Dataframe\n",
    "available_df = pd.json_normalize(refdata_dict['routes'])\n",
    "available_df = available_df.rename(columns={\n",
    "    'uuid' : 'UUID',\n",
    "    'via' : 'Via',\n",
    "    'loadPort.uuid' : 'LoadPortUUID',\n",
    "    'loadPort.type' : 'LoadPortType',\n",
    "    'loadPort.region' : 'LoadPortRegion',\n",
    "    'loadPort.name' : 'LoadPortName',\n",
    "    'dischargePort.uuid' : 'DischargePortUUID',\n",
    "    'dischargePort.type' : 'DischargePortType',\n",
    "    'dischargePort.region' : 'DischargePortRegion',\n",
    "    'dischargePort.name' : 'DischargePortName',\n",
    "})\n",
    "\n",
    "available_df.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c7502772",
   "metadata": {},
   "outputs": [],
   "source": [
    "## Defining function to fetch routes cost data\n",
    "\n",
    "def fetch_route_data(access_token, route_uuid, release, congestion_laden= None, congestion_ballast= None):\n",
    "\n",
    "    query_params = \"?release-date={}\".format(release)\n",
    "    if congestion_laden is not None:\n",
    "        query_params += \"&congestion-laden-days={}\".format(congestion_laden)\n",
    "    if congestion_ballast is not None:\n",
    "        query_params += \"&congestion-ballast-days={}\".format(congestion_ballast)\n",
    "\n",
    "    uri = \"/v1.0/routes/{}/{}\".format(route_uuid, query_params)\n",
    "\n",
    "    content = do_api_get_query(\n",
    "        uri=uri,\n",
    "        access_token=access_token,\n",
    "    )\n",
    "\n",
    "    data = content[\"data\"]\n",
    "\n",
    "    return data\n",
    "\n",
    "\n",
    "## Defining function to fetch historical data for a range of release dates & store as Dataframe\n",
    "\n",
    "def routes_history(route_uuid, reldates, laden=None, ballast=None):\n",
    "    \n",
    "    # initiliasing data dictionary\n",
    "    my_route  = {\n",
    "            \"ReleaseDate\": [],\n",
    "            \"Period\": [],\n",
    "            \"CalMonth\":[],\n",
    "            \"StartDate\": [],\n",
    "            \"EndDate\": [],\n",
    "            \"Total\": [],\n",
    "            \"Hire\": [],\n",
    "            \"Fuel\": [],\n",
    "            \"Port\": [],\n",
    "            \"Canal\": [],\n",
    "            \"Carbon\": [],\n",
    "            \"Congestion\": [],\n",
    "        }\n",
    "\n",
    "    # iterating through release dates\n",
    "    for r in reldates:\n",
    "        try:\n",
    "            my_dict = fetch_route_data(access_token, route_uuid, release=r,  congestion_laden=laden, congestion_ballast=ballast)\n",
    "        except:\n",
    "            print('Bad Date: ' + r)\n",
    "            continue\n",
    "        \n",
    "        # Appending all data for full forward curve per release date\n",
    "        for data in my_dict[\"dataPoints\"]:\n",
    "            my_route['StartDate'].append(data[\"deliveryPeriod\"][\"startAt\"])\n",
    "            my_route['EndDate'].append(data[\"deliveryPeriod\"][\"endAt\"])\n",
    "            my_route['Period'].append(data[\"deliveryPeriod\"][\"name\"])\n",
    "\n",
    "            my_route['Total'].append(data[\"costsInUsdPerMmbtu\"][\"total\"])\n",
    "            my_route['Hire'].append(data[\"costsInUsdPerMmbtu\"][\"hire\"])\n",
    "            my_route['Fuel'].append(data[\"costsInUsdPerMmbtu\"][\"fuel\"])\n",
    "            my_route['Port'].append(data[\"costsInUsdPerMmbtu\"][\"port\"])\n",
    "            my_route['Canal'].append(data[\"costsInUsdPerMmbtu\"][\"canal\"])\n",
    "            \n",
    "            # Add congestion and/or carbon costs if applicable\n",
    "            try:\n",
    "                my_route['Congestion'].append(data[\"costsInUsdPerMmbtu\"][\"congestion\"])\n",
    "            except:\n",
    "                 my_route['Congestion'].append(0)\n",
    "            \n",
    "            try:\n",
    "                my_route['Carbon'].append(data[\"costsInUsdPerMmbtu\"][\"carbon\"])\n",
    "            except:\n",
    "                 my_route['Carbon'].append(0)\n",
    "\n",
    "            my_route['ReleaseDate'].append(r)\n",
    "            my_route['CalMonth'].append(datetime.datetime.strptime(data[\"deliveryPeriod\"][\"startAt\"], '%Y-%m-%d').strftime('%b%y'))\n",
    "\n",
    "        # Sleep function is used to avoid API rate limiting.\n",
    "        time.sleep(0.2)\n",
    "    \n",
    "    # convert into Pandas dataframe\n",
    "    my_route_df = pd.DataFrame(my_route)\n",
    "\n",
    "    ## Changing the data types to pandas datetime\n",
    "    for col in ['ReleaseDate', 'StartDate', 'EndDate']:\n",
    "        my_route_df[col] = pd.to_datetime(my_route_df[col])\n",
    "\n",
    "    # sorting data by Release Date (latest first)\n",
    "    my_route_df = my_route_df.sort_values('ReleaseDate')\n",
    "    \n",
    "    ## Changing the data type of these columns from 'string' to numbers.\n",
    "    for col in [\"Total\",\"Hire\",\"Fuel\",\"Port\",\"Canal\",\"Congestion\", \"Carbon\"]:\n",
    "        my_route_df[col] = pd.to_numeric(my_route_df[col])\n",
    "    \n",
    "    return my_route_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0c2cec53",
   "metadata": {},
   "source": [
    "# 3. Defining Inputs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c8e8b626",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Retrieving UUID for route of interest\n",
    "sab_gat_id = available_df[(available_df[\"LoadPortName\"] == \"Sabine Pass\") & \\\n",
    "                    (available_df[\"DischargePortName\"] == \"Gate\")]['UUID'].values[0]\n",
    "\n",
    "# Example of retrieving a route UUID with a specific via point\n",
    "#sab_fut_id = available_df[(available_df[\"LoadPortName\"] == \"Sabine Pass\") & \\\n",
    "#                    (available_df[\"Via\"] == \"cogh\") & \\\n",
    "#                    (available_df[\"DischargePortName\"] == \"Futtsu\")]['UUID'].values[0]\n",
    "\n",
    "print(sab_gat_id)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7ca4127b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Choosing when to start data from\n",
    "ix = reldates.index('2026-01-02')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "95ddd387",
   "metadata": {},
   "source": [
    "# 4. Calling Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6272154a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calling data based on defined functions and inputs above\n",
    "full_df = routes_history(sab_gat_id, reldates[:ix+1], laden =None, ballast=None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "78bc38a2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Filtering for a specific month\n",
    "mdf = full_df[full_df[\"Period\"] == 'Spot (Physical)'].sort_values('ReleaseDate').reset_index(drop=True)\n",
    "mdf.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c399f3fd",
   "metadata": {},
   "source": [
    "# 5. Plotting"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b14233ae",
   "metadata": {},
   "outputs": [],
   "source": [
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "from matplotlib.ticker import PercentFormatter\n",
    "\n",
    "sns.set_style('whitegrid')\n",
    "\n",
    "# Assigning colours for each cost component\n",
    "comp_colours = {\n",
    "    'Hire':  '#52BE80',\n",
    "    'Fuel':  '#008bcc',\n",
    "    'Port': '#C0392B',\n",
    "    'Canal':  '#E67E22',\n",
    "    'Carbon': '#1E8449',\n",
    "    'Congestion': '#7F8C8D'\n",
    "}\n",
    "\n",
    "# Create a copy of mdf to modify for plotting\n",
    "ms = mdf.copy()\n",
    "x  = ms['ReleaseDate'].copy()\n",
    "\n",
    "# Initialising figure\n",
    "fig1, (ax1, ax12) = plt.subplots(\n",
    "    2, 1, figsize=(14, 8.6), sharex=True,\n",
    "    gridspec_kw={'height_ratios': [10, 3], 'hspace': 0.30}\n",
    ")\n",
    "fig1.subplots_adjust(top=0.78, bottom=0.10, left=0.06, right=0.96)\n",
    "\n",
    "# Plotting the components as a cumulative filled curve\n",
    "cum = np.zeros(len(ms))\n",
    "for col in comp_colours:\n",
    "    v   = ms[col].fillna(0).to_numpy(float)\n",
    "    new = cum + v\n",
    "    ax1.fill_between(x, cum, new, color=comp_colours[col], alpha=0.75,\n",
    "                      label=col, linewidth=0)\n",
    "    cum = new\n",
    "\n",
    "# Plotting total Routes cost as a line plot\n",
    "ax1.plot(x, ms['Total'], color='#111111', linewidth=1.5, label='Total Route Cost')\n",
    "\n",
    "ax1.axhline(0, color='grey', linewidth=0.9)\n",
    "ax1.set_ylabel('$/MMBtu')\n",
    "ax1.set_xlim([x.iloc[0] - pd.Timedelta(1, 'day'), x.iloc[-1] + pd.Timedelta(1, 'day')])\n",
    "ax1.legend(loc='upper left', frameon=True, edgecolor='#dddddd', fontsize=10, ncol=2)\n",
    "\n",
    "\n",
    "for col in comp_colours:\n",
    "    ax12.plot(x, ms[col]/ms['Total'], color=comp_colours[col])\n",
    "\n",
    "ax12.set_ylim(0,1)     \n",
    "ax12.set_xlabel('Release Date', fontsize=12)\n",
    "ax12.set_ylabel(f'% Contribution', fontsize=12)\n",
    "ax12.yaxis.set_major_formatter(PercentFormatter(xmax=1))\n",
    "\n",
    "\n",
    "plt.show()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "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.13.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}