{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "59d19f73",
   "metadata": {},
   "source": [
    "# LNG Freight Routes\n",
    "\n",
    "Here we call Routes data from the Spark Freight API. \n",
    "\n",
    "__N.B. This guide is just for Freight Routes data. If you're looking for other API data products (such as Freight Contracts or Netbacks), please refer to their according code samples.__ "
   ]
  },
  {
   "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",
    "\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",
    "    # 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. Describing available routes\n",
    "\n",
    "We have now saved all available routes as a dictionary. We can check how this looks, and then filter the routes by several characteristics."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a2377628",
   "metadata": {},
   "outputs": [],
   "source": [
    "## Define function for listing routes from API\n",
    "def list_routes(access_token):\n",
    "    \"\"\"\n",
    "    Fetch available routes. Return route ids and Spark price release dates.\n",
    "\n",
    "    The URI used returns a list of all available Spark routes. With these routes, we can find the price breakdown of a specific route.\n",
    "\n",
    "    # Procedure:\n",
    "\n",
    "    Do a GET query to /v1.0/routes/ with a Bearer token authorization HTTP header.\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 contracts:\n",
    "reldates, refdata_dict = list_routes(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": "markdown",
   "id": "2854904d",
   "metadata": {},
   "source": [
    "# 3. Analysing a Specific Route\n",
    "\n",
    "\n",
    "Here we define the function that allows us to pull data for a specific route and release date.\n",
    "\n",
    "We then define a given route ID ('my_route') and release date ('my_release') below the function, and these values are printed out for the user to check the parameters.\n",
    "\n",
    "\n",
    "__NOTE:__ The 'congestion_laden' and 'congestion_ballast' options are limited to the following combinations: (0,0), (1,1), (4,4), (7,7), (10,10), (15,15), (20,20). Both parameters must have the same value."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c7502772",
   "metadata": {},
   "outputs": [],
   "source": [
    "## Defining the function\n",
    "\n",
    "def fetch_route_data(access_token, ticker, 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(ticker, 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"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5b025e65",
   "metadata": {},
   "source": [
    "### Storing Data as a DataFrame\n",
    "\n",
    "We extract some relevant data for the chosen route, including the spot price and forward prices. These are stored in a Pandas Dataframe for readability and ease of use."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "74ef56d3",
   "metadata": {},
   "outputs": [],
   "source": [
    "import time\n",
    "import datetime as dt\n",
    "\n",
    "def routes_history(tick, reldates, laden=None, ballast=None):\n",
    "    \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",
    "    for r in reldates:\n",
    "        try:\n",
    "            my_dict = fetch_route_data(access_token, tick, release=r,  congestion_laden=laden, congestion_ballast=ballast)\n",
    "        except:\n",
    "            print('Bad Date: ' + r)\n",
    "            \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 Cost USD'].append(data[\"costsInUsd\"][\"total\"])\n",
    "            my_route['Total'].append(data[\"costsInUsdPerMmbtu\"][\"total\"])\n",
    "\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",
    "            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(dt.datetime.strptime(data[\"deliveryPeriod\"][\"startAt\"], '%Y-%m-%d').strftime('%b%y'))\n",
    "\n",
    "        \n",
    "        # Sleep function is used to avoid API rate limiting.\n",
    "        time.sleep(0.2)\n",
    "        \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",
    "    ## Changing the data type of these columns from 'string' to numbers.\n",
    "    ## This allows us to easily plot a forward curve, as well as perform statistical analysis on the prices.\n",
    "    for col in [\"Total\",\"Hire\",\"Fuel\",\"Port\",\"Canal\",\"Congestion\", \"Carbon\"]:\n",
    "        my_route_df[col] = pd.to_numeric(my_route_df[col])\n",
    "        my_route_df[col + 'Diff'] = my_route_df[col].diff(periods=-1)\n",
    "    \n",
    "    return my_route_df"
   ]
  },
  {
   "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": "6272154a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Choosing when to start data from\n",
    "ix = reldates.index('2026-01-02')\n",
    "\n",
    "# Calling data\n",
    "full_df = routes_history(sab_gat_id, reldates[:ix+1], laden =None, ballast=None)\n",
    "full_df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "78bc38a2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Filtering for a specific month\n",
    "mdf = full_df[full_df[\"Period\"] == 'M+1']\n",
    "mdf.head(3)"
   ]
  }
 ],
 "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
}