notebooks/COMTRADE_SQL_request_notebook.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Import the needed packages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import psycopg2\n",
    "import pandas as pd\n",
    "import time, datetime\n",
    "import json\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Define a function that load the comtrade json files into a dictionary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "def load_comtrade_info():\n",
    "    comtrade_dictionary={}\n",
    "    json_data=open('lib/classificationHS.json')\n",
    "    comtrade_dictionary['comcodes']= pd.DataFrame(json.load(json_data)['results'])\n",
    "    json_data.close()\n",
    "    json_data=open('lib/partnerAreas.json')\n",
    "    comtrade_dictionary['partners']= pd.DataFrame(json.load(json_data)['results'])\n",
    "    json_data.close()\n",
    "    json_data=open('lib/reporterAreas.json')\n",
    "    comtrade_dictionary['reporters']= pd.DataFrame(json.load(json_data)['results'])\n",
    "    json_data.close()\n",
    "        \n",
    "    return comtrade_dictionary"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Define three function which lookup comtrade codes in the comtrade dictionary\n",
    "I will change the country search code to use the ISO country names.. perhaps but needs SQL query to get these codes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_reporter_code(comtrade_dictionary, country):\n",
    "    '''\n",
    "    Search the reporter country code\n",
    "    '''\n",
    "    return comtrade_dictionary['reporters']['id'][comtrade_dictionary['reporters']['text'].str.contains(country)].iloc[0]\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_partner_code(comtrade_dictionary, country):\n",
    "    '''\n",
    "    Search the partner country code\n",
    "    '''\n",
    "    return comtrade_dictionary['partners']['id'][comtrade_dictionary['partners']['text'].str.contains(country)].iloc[0]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_commodity_code(comtrade_dictionary, commodity):\n",
    "    '''\n",
    "    Search the commodity code\n",
    "    '''\n",
    "    commodity_code = comtrade_dictionary['comcodes'][comtrade_dictionary['comcodes']['text'].str.contains(commodity)]\n",
    "    if commodity_code.empty:\n",
    "        print(commodity + ' not found.')\n",
    "        return -1\n",
    "    else:\n",
    "        #take first one if more than one result\n",
    "        commodity_code = commodity_code.iloc[0]\n",
    "    print(commodity_code['text'])\n",
    "    return commodity_code\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Connect to the database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = psycopg2.connect(\n",
    "                 dbname = \"comtrade\", # could also be \"hmrc\"\n",
    "                 host = \"data-science-pgsql-dev-01.c8kuuajkqmsb.eu-west-2.rds.amazonaws.com\",\n",
    "                 user = \"trade_read\",\n",
    "                 password = \"2fs@9!^43g\")\n",
    "\n",
    "cur = conn.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Get the column names and print them out"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                  0                            1     2\n",
      "0             rowid                       bigint  64.0\n",
      "1    classification                         text   NaN\n",
      "2              year                      integer  32.0\n",
      "3            period                      integer  32.0\n",
      "4       period_desc                         text   NaN\n",
      "5   aggregate_level                      integer  32.0\n",
      "6      is_leaf_code                      integer  32.0\n",
      "7   trade_flow_code                      integer  32.0\n",
      "8        trade_flow                         text   NaN\n",
      "9     reporter_code                      integer  32.0\n",
      "10         reporter                         text   NaN\n",
      "11     reporter_iso                         text   NaN\n",
      "12     partner_code                      integer  32.0\n",
      "13          partner                         text   NaN\n",
      "14      partner_iso                         text   NaN\n",
      "15   commodity_code                         text   NaN\n",
      "16        commodity                         text   NaN\n",
      "17    qty_unit_code                      integer  32.0\n",
      "18         qty_unit                         text   NaN\n",
      "19              qty                       bigint  64.0\n",
      "20     netweight_kg                       bigint  64.0\n",
      "21  trade_value_usd                       bigint  64.0\n",
      "22             flag                      integer  32.0\n",
      "23           source                      integer  32.0\n",
      "24         inserted  timestamp without time zone   NaN\n"
     ]
    }
   ],
   "source": [
    "cur.execute(\"select COLUMN_NAME, DATA_TYPE, NUMERIC_PRECISION from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='comtrade'\")\n",
    "column_names=pd.DataFrame(cur.fetchall())\n",
    "print(column_names)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Lookup the needed comtrade codes for the SQL request"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "02 - Meat and edible meat offal\n"
     ]
    }
   ],
   "source": [
    "comtrade_dict = load_comtrade_info()\n",
    "uk_code = get_reporter_code(comtrade_dict, 'United Kingdom')\n",
    "brazil_code = get_partner_code(comtrade_dict, 'Brazil')\n",
    "beef = get_commodity_code(comtrade_dict, 'Meat')['id']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Download the comtrade data and put it into a pandas DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Request took: 0:00:03.434107\n"
     ]
    }
   ],
   "source": [
    "t0 = time.perf_counter()\n",
    "\n",
    "cur.execute(\"SELECT partner, netweight_kg, trade_value_usd, period, commodity_code FROM comtrade WHERE \"\\\n",
    "            \"partner_code = %s \"\\\n",
    "            \"AND period  BETWEEN 201401 AND 201612\"\\\n",
    "            \"AND commodity_code = %s\"\\\n",
    "            \"AND reporter_code = %s\", (brazil_code, beef, uk_code))\n",
    "\n",
    "exports_imports = pd.DataFrame(cur.fetchall(), columns=['partner', 'netweight_kg', 'trade_value_usd', 'period', 'commodity_code'])\n",
    "\n",
    "t1 = time.perf_counter()\n",
    "print('Request took: ' +str(datetime.timedelta(seconds=t1-t0)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   partner netweight_kg  trade_value_usd  period commodity_code\n",
      "0   Brazil         None         15256598  201401             02\n",
      "1   Brazil         None         16656425  201402             02\n",
      "2   Brazil         None         11085225  201403             02\n",
      "3   Brazil         None         10171527  201404             02\n",
      "4   Brazil         None         13035358  201405             02\n",
      "5   Brazil         None            58908  201406             02\n",
      "6   Brazil         None         17121928  201406             02\n",
      "7   Brazil         None          4807262  201407             02\n",
      "8   Brazil         None         18362832  201408             02\n",
      "9   Brazil         None         21727710  201409             02\n",
      "10  Brazil         None         14655838  201410             02\n",
      "11  Brazil         None         15864259  201411             02\n",
      "12  Brazil         None         19937311  201412             02\n",
      "13  Brazil         None         13648491  201501             02\n",
      "14  Brazil         None         15625678  201502             02\n",
      "15  Brazil         None         13298818  201503             02\n",
      "16  Brazil         None         11826041  201504             02\n",
      "17  Brazil         None         13741690  201505             02\n",
      "18  Brazil         None         14585639  201506             02\n",
      "19  Brazil         None          6701257  201507             02\n",
      "20  Brazil         None          9868042  201508             02\n",
      "21  Brazil         None         13424598  201509             02\n",
      "22  Brazil         None         10922985  201510             02\n",
      "23  Brazil         None         10366607  201511             02\n",
      "24  Brazil         None         12565388  201512             02\n",
      "25  Brazil         None         10727174  201601             02\n",
      "26  Brazil         None         10307013  201602             02\n",
      "27  Brazil         None         11678385  201603             02\n",
      "28  Brazil         None         10516824  201604             02\n",
      "29  Brazil         None         14753017  201605             02\n",
      "30  Brazil         None         12797941  201606             02\n",
      "31  Brazil         None            53985  201607             02\n",
      "32  Brazil         None          8675602  201607             02\n",
      "33  Brazil         None         13375990  201608             02\n",
      "34  Brazil         None         13233384  201609             02\n",
      "35  Brazil         None         10188808  201610             02\n",
      "36  Brazil         None         12922691  201611             02\n",
      "37  Brazil         None         10163683  201612             02\n"
     ]
    }
   ],
   "source": [
    "print(exports_imports)"
   ]
  },
  {
   "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.6.5rc1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}