Search - Navigators.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Navigators\n",
    "Navigators provide the ability to drill into the result set providing a summary of the distribution.  They are particularly useful when you are interested in gathering the domain of values for a particular Property.  Navigators can be used against a specific View, used in conjunction with either a query, a filter or both.  Navigators can be simple or very powerful, but provide a very useful way to capture results in logical buckets.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<refinitiv.dataplatform.core.session.desktop_session.DesktopSession at 0x195039b0>"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import refinitiv.dataplatform as rdp\n",
    "import pandas as pd\n",
    "import datetime as dt\n",
    "import dateutil.relativedelta\n",
    "\n",
    "rdp.open_desktop_session('Your API Key here')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'1.0.0a7.post1'"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.set_option('display.max_colwidth', 140)\n",
    "rdp.__version__"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Example - Property values\n",
    "The most basic usage of navigators is to determine the available values within a given Property.  For example, when searching for assets, we may want to narrow down the specific country an asset is traded in.  We can determine the available countries to work with."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'Label': 'United States', 'Count': 11826864},\n",
       " {'Label': 'Eurobond Market', 'Count': 3617762},\n",
       " {'Label': 'Colombia', 'Count': 1264380},\n",
       " {'Label': 'China (Mainland)', 'Count': 1231247},\n",
       " {'Label': 'South Korea', 'Count': 533448},\n",
       " {'Label': 'Germany', 'Count': 469694},\n",
       " {'Label': 'Canada', 'Count': 374139},\n",
       " {'Label': 'France', 'Count': 221040},\n",
       " {'Label': 'India', 'Count': 186007},\n",
       " {'Label': 'Japan', 'Count': 157262},\n",
       " {'Label': 'Chile', 'Count': 151908},\n",
       " {'Label': 'Italy', 'Count': 135888},\n",
       " {'Label': 'Australia', 'Count': 86496},\n",
       " {'Label': 'Spain', 'Count': 58537},\n",
       " {'Label': 'Switzerland', 'Count': 58249},\n",
       " {'Label': 'Russia', 'Count': 56044},\n",
       " {'Label': 'Austria', 'Count': 55356},\n",
       " {'Label': 'Norway', 'Count': 52819},\n",
       " {'Label': 'United Kingdom', 'Count': 50463},\n",
       " {'Label': 'Malaysia', 'Count': 49591}]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# The following will list the countries assets are listed.  We can use this output to filter based on a specific country.\n",
    "response = rdp.Search.search(\n",
    "    top = 0,\n",
    "    navigators = \"RCSCountryLeaf(buckets:20)\"   # Show the top 20\n",
    ")\n",
    "response.data.raw[\"Navigators\"][\"RCSCountryLeaf\"][\"Buckets\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Example - Vessels\n",
    "If I want to perform a basic search for tankers and capture the Hull Types, I could start with the following basic query:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>DocumentTitle</th>\n",
       "      <th>HullType</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>FPSO YUUM KAK NAAB, LNG Tanker, BW OFFSHORE SINGAPORE PTE LTD, Gulf of Mexico|East Mexico Tanker Zone|Mexico</td>\n",
       "      <td>Single Hull</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>MOZAH, LNG Tanker, NAKILAT SHIPPING QATAR LTD, North Pacific Ocean (Japan)|Taiwan Tanker Zone</td>\n",
       "      <td>Single Hull</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>UMM SLAL, Liquefied Natural Gas; LNG Tanker, NAKILAT SHIPPING QATAR LTD, Java Sea|Singapore Tanker Zone|Singapore Strait Waypoint</td>\n",
       "      <td>Single Hull</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BU SAMRA, LNG Tanker, STASCO SHIP MANAGEMENT, Gulf of Oman|Arabian Gulf Tanker Zone|Qatar</td>\n",
       "      <td>Double Hull</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AL MAYEDA, LNG Tanker, STASCO SHIP MANAGEMENT, Gulf of Oman|Arabian Gulf Tanker Zone</td>\n",
       "      <td>Double Hull</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9995</th>\n",
       "      <td>CHANG FENG YOU 11, Other Tanker, East China Sea|Korea Tanker Zone|China (Mainland)</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9996</th>\n",
       "      <td>HU CHONG HUO 0201, Other Tanker, Shanghai|East China Sea|Shanghai Tanker Zone|China (Mainland)</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9997</th>\n",
       "      <td>ZHONG HAI 16, Other Tanker, Shanghai|Shanghai Tanker Zone|Yangtze River|China (Mainland)</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9998</th>\n",
       "      <td>OOSTZEE, Other Tanker, English Channel|UK Continent Tanker Zone|ARA Tanker Zone|Netherlands</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9999</th>\n",
       "      <td>YUESHAOGUANHUO2138, Other Tanker, South China Sea|Canton Tanker Zone|Hai-Nan Strait Waypoint</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10000 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                                                                          DocumentTitle  \\\n",
       "0                          FPSO YUUM KAK NAAB, LNG Tanker, BW OFFSHORE SINGAPORE PTE LTD, Gulf of Mexico|East Mexico Tanker Zone|Mexico   \n",
       "1                                         MOZAH, LNG Tanker, NAKILAT SHIPPING QATAR LTD, North Pacific Ocean (Japan)|Taiwan Tanker Zone   \n",
       "2     UMM SLAL, Liquefied Natural Gas; LNG Tanker, NAKILAT SHIPPING QATAR LTD, Java Sea|Singapore Tanker Zone|Singapore Strait Waypoint   \n",
       "3                                             BU SAMRA, LNG Tanker, STASCO SHIP MANAGEMENT, Gulf of Oman|Arabian Gulf Tanker Zone|Qatar   \n",
       "4                                                  AL MAYEDA, LNG Tanker, STASCO SHIP MANAGEMENT, Gulf of Oman|Arabian Gulf Tanker Zone   \n",
       "...                                                                                                                                 ...   \n",
       "9995                                                 CHANG FENG YOU 11, Other Tanker, East China Sea|Korea Tanker Zone|China (Mainland)   \n",
       "9996                                     HU CHONG HUO 0201, Other Tanker, Shanghai|East China Sea|Shanghai Tanker Zone|China (Mainland)   \n",
       "9997                                           ZHONG HAI 16, Other Tanker, Shanghai|Shanghai Tanker Zone|Yangtze River|China (Mainland)   \n",
       "9998                                        OOSTZEE, Other Tanker, English Channel|UK Continent Tanker Zone|ARA Tanker Zone|Netherlands   \n",
       "9999                                       YUESHAOGUANHUO2138, Other Tanker, South China Sea|Canton Tanker Zone|Hai-Nan Strait Waypoint   \n",
       "\n",
       "         HullType  \n",
       "0     Single Hull  \n",
       "1     Single Hull  \n",
       "2     Single Hull  \n",
       "3     Double Hull  \n",
       "4     Double Hull  \n",
       "...           ...  \n",
       "9995         <NA>  \n",
       "9996         <NA>  \n",
       "9997         <NA>  \n",
       "9998         <NA>  \n",
       "9999         <NA>  \n",
       "\n",
       "[10000 rows x 2 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rdp.search(\n",
    "    view = rdp.SearchViews.VesselPhysicalAssets,\n",
    "    query = \"tanker\",\n",
    "    select = \"DocumentTitle, HullType\",\n",
    "    top = 10000\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here, we can see a list of the different tankers and their Hull Types. Within this collection, while I can clearly see there are 2 different Hull Types: \"Single\" and \"Double\", I'm also seeing duplicates.  Instead of going through the effort to process the above result to pull out the unique list of Hull Types, I will instead use a Navigator - which was designed to do this work for me.\n",
    "\n",
    "Using the above query, I will apply a Navigator by selecting the 'HullType' Property.  What this does is instruct search to bucket all unique Hull Types and summarize the total found for each. To capture the results, I will need to utilize the Content Layer API call."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'Label': 'Double Hull', 'Count': 16883},\n",
       " {'Label': 'Single Hull', 'Count': 5131}]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "response=rdp.Search.search(\n",
    "    view = rdp.SearchViews.VesselPhysicalAssets,\n",
    "    query = \"tanker\",\n",
    "    top = 0,       # I'm not interested in the hits, only details within the navigator response section\n",
    "    navigators = \"HullType\"\n",
    ")\n",
    "response.data.raw[\"Navigators\"][\"HullType\"][\"Buckets\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Example - Listing of exchanges per asset category\n",
    "In the following example, I'm going to list the top 3 asset types in Canada and for each, display the top 2 exchanges trading on those assets."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "response=rdp.Search.search(\n",
    "    view = rdp.SearchViews.EquityQuotes,\n",
    "    filter = \"AssetState eq 'AC' and RCSExchangeCountryLeaf eq 'Canada'\",    # Filter on all active (AC) assets within Canada\n",
    "    top = 0,\n",
    "    navigators = \"RCSAssetCategoryLeaf(buckets:3, sub:ExchangeName(buckets:2))\"  #  For the top 3 categories, show the top 2 exchanges\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1. Equity Cash Option\n",
      "\t1. Montreal Options Exchange (43216)\n",
      "\t2. Montreal Exchange (26)\n",
      "2. Ordinary Share\n",
      "\t1. Aequitas NEO-L (3251)\n",
      "\t2. Aequitas NEO-N (3250)\n",
      "3. Equity Future\n",
      "\t1. Montreal Exchange (7891)\n",
      "\t2. Montreal Options Exchange (109)\n"
     ]
    }
   ],
   "source": [
    "# Pretty display of the listing\n",
    "rank = 1\n",
    "for asset in response.data.raw[\"Navigators\"][\"RCSAssetCategoryLeaf\"][\"Buckets\"]:\n",
    "    print(f'{rank}. {asset[\"Label\"]}')\n",
    "    exchangeRank = 1\n",
    "    for exchange in asset[\"ExchangeName\"][\"Buckets\"]:\n",
    "        print(f'\\t{exchangeRank}. {exchange[\"Label\"]} ({exchange[\"Count\"]})')\n",
    "        exchangeRank += 1\n",
    "    rank += 1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Example - Outstanding Government debt in the last 3 months.\n",
    "In this example, I will be using some nested expressions to summarize categories within the buckets I collect.  Specifically, I'm looking for government debt ('govt') issued any time after 3-months ago using the following filter:\n",
    "- \"DBType eq 'govt' and IssueDate gt today-3months\" (DBType identifies government debt)\n",
    "    \n",
    "Based on the result set, I want categorize or create buckets (top 5) based on the domicile of the issuer.  For each domicile, sum the outstanding debt and also include the maximum coupon rate for each one.\n",
    "- RCSDomicileLeaf(buckets:5, desc:sum_FaceOutstandingUSD, calc:max_CouponRate)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get date N months ago\n",
    "months = 3\n",
    "date = dt.datetime.today() - dateutil.relativedelta.relativedelta(months=months)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'Label': 'United States',\n",
       "  'Count': 69,\n",
       "  'max_CouponRate': 1.625,\n",
       "  'sum_FaceOutstandingUSD': 2311296405500.0},\n",
       " {'Label': 'Japan',\n",
       "  'Count': 56,\n",
       "  'max_CouponRate': 0.6,\n",
       "  'sum_FaceOutstandingUSD': 1579333218038.0},\n",
       " {'Label': 'China (Mainland)',\n",
       "  'Count': 46,\n",
       "  'max_CouponRate': 3.97,\n",
       "  'sum_FaceOutstandingUSD': 255746353840.0},\n",
       " {'Label': 'Hong Kong',\n",
       "  'Count': 37,\n",
       "  'max_CouponRate': 3.5,\n",
       "  'sum_FaceOutstandingUSD': 138684763938.0},\n",
       " {'Label': 'Italy',\n",
       "  'Count': 18,\n",
       "  'max_CouponRate': 1.8,\n",
       "  'sum_FaceOutstandingUSD': 123828421726.0}]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Retrieve the top 5 outstanding government debt in the last 3 months\n",
    "response = rdp.Search.search(\n",
    "    view = rdp.SearchViews.GovCorpInstruments,\n",
    "    filter = \"DbType eq 'govt' and IssueDate gt \" + date.strftime(\"%Y-%m-%d\"),\n",
    "    top = 0,\n",
    "    navigators = \"RCSDomicileLeaf(buckets:5, desc:sum_FaceOutstandingUSD, calc:max_CouponRate)\"\n",
    ")\n",
    "response.data.raw[\"Navigators\"][\"RCSDomicileLeaf\"][\"Buckets\"]"
   ]
  },
  {
   "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}