{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Python API Example - Gas Transit Tariff Calendar & Cost Changelog\n",
    "\n",
    "This guide is designed to provide an example of how to access the Spark API:\n",
    "- The path to your client credentials is the only input needed to run this script (just before Section 2)\n",
    "- This script has been designed to display the raw outputs of requests from the API, and then shows you how to format those outputs to enable easy reading and analysis\n",
    "- This script can be copied and pasted by customers for quick use of the API\n",
    "\n",
    "__N.B. This guide is just for the Gas Transit Tariff Calendar and Cost Changelog data. If you're looking for other API data products (such as contract prices, Freight routes or Netbacks), please refer to their according code example files.__ "
   ]
  },
  {
   "cell_type": "markdown",
   "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__\n",
    "\n",
    "or refer to our API website for more information about this endpoint: https://www.sparkcommodities.com/api/gas/transit.html"
   ]
  },
  {
   "cell_type": "markdown",
   "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": "49475424",
   "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 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": "abdd44e8",
   "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": "f4be32b8",
   "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"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3b292830",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Insert file path to your client credentials 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": "9e882500",
   "metadata": {},
   "source": [
    "# 2. Reference Data\n",
    "\n",
    "Here we fetch the relevant reference data for the endpoint - this is a list of all available transit route options available on the endpoint. Countries for each hub are also included for easy reference.\n",
    "\n",
    "Both the Tariff Calendar and the Cost Changelog use the same `origin` and `destination` hub codes as the Gas Transit Costs endpoint, so this same reference data applies to all three."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d83493a4",
   "metadata": {},
   "outputs": [],
   "source": [
    "from io import StringIO\n",
    "\n",
    "## Defining the function\n",
    "\n",
    "def fetch_ref_data(access_token, format='json'):\n",
    "\n",
    "    uri=\"v1.0/gas/transit/reference-data/\"\n",
    "    print(uri)\n",
    "    \n",
    "    content = do_api_get_query(\n",
    "        uri=uri, access_token=access_token, format=format\n",
    "    )\n",
    "    \n",
    "    if format == 'json':\n",
    "        my_dict = content\n",
    "    elif format == 'csv':\n",
    "        # if there's no data to show, returns raw response (empty string) and \"No Data to Show\" message\n",
    "        if len(content) == 0:\n",
    "            my_dict = content\n",
    "            print('No Data to Show')\n",
    "        else:\n",
    "            my_dict = content.decode('utf-8')\n",
    "            my_dict = pd.read_csv(StringIO(my_dict))  # automatically converting into a Pandas DataFrame when choosing CSV format\n",
    "    \n",
    "    return my_dict\n",
    "\n",
    "ref_df = fetch_ref_data(access_token, format='csv')\n",
    "ref_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1980cfe7",
   "metadata": {},
   "source": [
    "# 3. Tariff Calendar\n",
    "\n",
    "Here we define a function to fetch the Gas Transit Tariff Calendar. This dataset is a forward-looking view of expected tariff changes: for each operator and route it gives the tariff convention, when the current period ends, what the next change is expected to be, and whether that date is firm. The available parameters can be found on our API docs:\n",
    "\n",
    "https://www.sparkcommodities.com/api/gas/transit.html\n",
    "\n",
    "__N.B.:__ This endpoint is a current snapshot rather than a historical series, and provides the option to return a JSON or CSV formatted response. Metadata is only available in the JSON format."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bc43881f",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "def fetch_tariff_calendar(access_token, status=None, origin=None, destination=None, format='json'):\n",
    "\n",
    "    query_params = \"\"\n",
    "\n",
    "    if origin is not None:\n",
    "        query_params += \"&origin={}\".format(origin)\n",
    "\n",
    "    if destination is not None:\n",
    "        query_params += \"&destination={}\".format(destination)\n",
    "\n",
    "    if status is not None:\n",
    "        query_params += \"&status={}\".format(status)\n",
    "\n",
    "    # the leading & is swapped for a ? so the query string is valid for any\n",
    "    # combination of the optional filters\n",
    "    if len(query_params) > 0:\n",
    "        query_params = \"?{}\".format(query_params[1:])\n",
    "\n",
    "    content = do_api_get_query(\n",
    "        uri=\"/v1.0/gas/transit/costs/calendar/{}\".format(query_params),\n",
    "        access_token=access_token, format=format\n",
    "    )\n",
    "\n",
    "    if format == 'json':\n",
    "        data = content\n",
    "    elif format == 'csv':\n",
    "        # if there's no data to show, returns raw response (empty string) and \"No Data to Show\" message\n",
    "        if len(content) == 0:\n",
    "            data = content\n",
    "            print('No Data to Show')\n",
    "        else:\n",
    "            data = content.decode('utf-8')\n",
    "            data = pd.read_csv(StringIO(data))  # automatically converting into a Pandas DataFrame when choosing CSV format\n",
    "\n",
    "    return data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2c3c430f",
   "metadata": {},
   "source": [
    "#### JSON Response"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4470545b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calling the full tariff calendar - JSON response\n",
    "calendar_json = fetch_tariff_calendar(access_token, format='json')\n",
    "calendar_json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f846497f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# checking metaData\n",
    "calendar_json['metaData']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9d137e99",
   "metadata": {},
   "source": [
    "#### CSV Response"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f11970e8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calling the full tariff calendar - CSV response\n",
    "calendar_df = fetch_tariff_calendar(access_token, format='csv')\n",
    "calendar_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "172e5077",
   "metadata": {},
   "outputs": [],
   "source": [
    "# listing the column names of the DataFrame\n",
    "calendar_df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4b8ca96c",
   "metadata": {},
   "source": [
    "#### Filtering\n",
    "\n",
    "`status`, `origin` and `destination` all filter server-side and can be combined."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "553e4da3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# filtering to provisional entries only\n",
    "provisional_df = fetch_tariff_calendar(access_token, status='provisional', format='csv')\n",
    "provisional_df[['Operator', 'Name', 'Country', 'Origin', 'Destination', 'Status', 'NextChangeWhat']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4f9a636a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# next dated change per route, earliest first\n",
    "calendar_df['NextChangeStart'] = pd.to_datetime(calendar_df['NextChangeStart'])\n",
    "\n",
    "upcoming = calendar_df.dropna(subset=['NextChangeStart']).sort_values('NextChangeStart')\n",
    "upcoming[['NextChangeStart', 'Name', 'Country', 'Origin', 'Destination', 'Status', 'NextChangeWhat']]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "56e757c0",
   "metadata": {},
   "source": [
    "# 4. Cost Changelog\n",
    "\n",
    "Here we define a function to fetch the Gas Transit Cost Changelog - the log of amendments already made to the Gas Transit Costs dataset, with the value before and after each change and a note explaining it. The available parameters can be found on our API docs:\n",
    "\n",
    "https://www.sparkcommodities.com/api/gas/transit.html\n",
    "\n",
    "__N.B.:__ `limit` cannot be combined with `start`/`end` - use `offset` to page within a date range. This endpoint provides the option to return a JSON or CSV formatted response. Metadata is only available in the JSON format.\n",
    "\n",
    "Where a requested date range contains no changelog entries, the CSV response is empty and the function returns an empty DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7454b0d0",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "def fetch_transit_changelog(access_token, origin=None, destination=None, start=None, end=None,\n",
    "                            limit=None, offset=None, format='json'):\n",
    "\n",
    "    query_params = \"\"\n",
    "\n",
    "    if origin is not None:\n",
    "        query_params += \"&origin={}\".format(origin)\n",
    "\n",
    "    if destination is not None:\n",
    "        query_params += \"&destination={}\".format(destination)\n",
    "\n",
    "    if start is not None:\n",
    "        query_params += \"&start={}\".format(start)\n",
    "    if end is not None:\n",
    "        query_params += \"&end={}\".format(end)\n",
    "\n",
    "    # N.B. limit cannot be combined with start/end\n",
    "    if limit is not None:\n",
    "        query_params += \"&limit={}\".format(limit)\n",
    "    if offset is not None:\n",
    "        query_params += \"&offset={}\".format(offset)\n",
    "\n",
    "    # the leading & is swapped for a ? so the query string is valid for any\n",
    "    # combination of the optional parameters\n",
    "    if len(query_params) > 0:\n",
    "        query_params = \"?{}\".format(query_params[1:])\n",
    "\n",
    "    content = do_api_get_query(\n",
    "        uri=\"/v1.0/gas/transit/costs/changelog/{}\".format(query_params),\n",
    "        access_token=access_token, format=format\n",
    "    )\n",
    "\n",
    "    if format == 'json':\n",
    "        data = content\n",
    "    elif format == 'csv':\n",
    "        # if there's no data to show, returns an empty DataFrame and \"No Data to Show\" message\n",
    "        if len(content) == 0:\n",
    "            data = pd.DataFrame()\n",
    "            print('No Data to Show')\n",
    "        else:\n",
    "            data = content.decode('utf-8')\n",
    "            data = pd.read_csv(StringIO(data))  # automatically converting into a Pandas DataFrame when choosing CSV format\n",
    "\n",
    "    return data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "572bfa0a",
   "metadata": {},
   "source": [
    "#### JSON Response"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a79d6f7b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calling the 3 most recent entry dates - JSON response\n",
    "changelog_json = fetch_transit_changelog(access_token, limit=3, format='json')\n",
    "changelog_json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4413c7bd",
   "metadata": {},
   "outputs": [],
   "source": [
    "# checking metaData\n",
    "changelog_json['metaData']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f74217e8",
   "metadata": {},
   "source": [
    "#### CSV Response"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "35c1cdad",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calling a date range of changelog entries - CSV response\n",
    "today = datetime.datetime.now().strftime('%Y-%m-%d')\n",
    "\n",
    "changelog_df = fetch_transit_changelog(access_token, start='2026-01-01', end=today, format='csv')\n",
    "changelog_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1b246894",
   "metadata": {},
   "outputs": [],
   "source": [
    "# listing the column names of the DataFrame\n",
    "changelog_df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "63b02d67",
   "metadata": {},
   "source": [
    "## N.B. Historical Data Limits\n",
    "\n",
    "Currently, a maximum of 1 year's worth of changelog entries can be called at one time due to the size of the data file. \n",
    "\n",
    "If more data points are required, the below code can be used: the function calls the historical data 1 year at a time and combines the data into one Pandas DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d5b69f2b",
   "metadata": {},
   "outputs": [],
   "source": [
    "import datetime\n",
    "\n",
    "def loop_historical_data(access_token, start, end, origin=None, destination=None):\n",
    "    \n",
    "    hist_diff = (datetime.datetime.strptime(end, '%Y-%m-%d') - datetime.datetime.strptime(start, '%Y-%m-%d')).days\n",
    "    t = 0\n",
    "\n",
    "    w = 365\n",
    "\n",
    "    while t < hist_diff:\n",
    "        # initialising dataframe\n",
    "        if t == 0 and hist_diff>w:\n",
    "            diff_end = datetime.datetime.strftime(datetime.datetime.strptime(start, '%Y-%m-%d') + pd.Timedelta(days=w), '%Y-%m-%d')\n",
    "            hist_df = fetch_transit_changelog(access_token, start=start, end=diff_end, origin=origin,\n",
    "                        destination=destination, format='csv')\n",
    "        \n",
    "        elif t==0 and hist_diff<=w:\n",
    "            hist_df = fetch_transit_changelog(access_token, start=start, end=end, origin=origin,\n",
    "                        destination=destination, format='csv')\n",
    "\n",
    "        # appending additional historical data\n",
    "        else:\n",
    "            if t < hist_diff-w:\n",
    "                diff_start = datetime.datetime.strftime(datetime.datetime.strptime(start, '%Y-%m-%d') + pd.Timedelta(days=t+1), '%Y-%m-%d')\n",
    "                diff_end = datetime.datetime.strftime(datetime.datetime.strptime(diff_start, '%Y-%m-%d') + pd.Timedelta(days=w), '%Y-%m-%d')\n",
    "                historical_addition = fetch_transit_changelog(access_token, start=diff_start, end=diff_end, origin=origin,\n",
    "                        destination=destination, format='csv')\n",
    "                try:\n",
    "                    hist_df = pd.concat([hist_df,historical_addition])\n",
    "                #exception if hist_df is empty\n",
    "                except:\n",
    "                    hist_df = historical_addition.copy()\n",
    "\n",
    "            else:\n",
    "                diff_start = datetime.datetime.strftime(datetime.datetime.strptime(start, '%Y-%m-%d') + pd.Timedelta(days=t+1), '%Y-%m-%d')\n",
    "                diff_end = datetime.datetime.strftime(datetime.datetime.strptime(diff_start, '%Y-%m-%d') + pd.Timedelta(days=hist_diff-t), '%Y-%m-%d')\n",
    "                historical_addition = fetch_transit_changelog(access_token, start=diff_start, end=diff_end, origin=origin,\n",
    "                        destination=destination, format='csv')\n",
    "                hist_df = pd.concat([hist_df, historical_addition])\n",
    "        \n",
    "        #looping by year\n",
    "        t += w\n",
    "\n",
    "    hist_df['EntryDate'] = pd.to_datetime(hist_df['EntryDate'])\n",
    "    hist_df = hist_df.drop_duplicates()\n",
    "\n",
    "    return hist_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2023130c",
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "hist_df = loop_historical_data(access_token, start='2025-01-01', end='2026-07-28',\n",
    "                                origin=None, destination=None)\n",
    "\n",
    "hist_df\n",
    "\"\"\""
   ]
  }
 ],
 "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
}