FAN_starting_kit.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div style=\"text-align: left;\">\n",
    "<table style=\"width:100%; background-color:transparent;\">\n",
    "  <tr style=\"background-color:transparent;\">\n",
    "    <td style=\"background-color:transparent;\"><a href=\"http://www.datascience-paris-saclay.fr\">\n",
    "<img border=\"0\" src=\"http://project.inria.fr/saclaycds/files/2017/02/logoUPSayPlusCDS_990.png\" width=\"90%\"> </td>\n",
    "     <td style=\"background-color:transparent;\"><a href=\"https://www.sidetrade.com/\">\n",
    "<img border=\"0\" src=\"https://www.sidetrade.com/wp-content/uploads/22050384_10213210110060640_1095182809_o-300x117.png\" width=\"60%\"> </td>\n",
    "  </tr>\n",
    "</table> \n",
    "</div>\n",
    "\n",
    "<center><h1>FAN revenue prediction challenge</h1></center>\n",
    "<br/>\n",
    "<center>Lucy Liu (CDS), Maria Teleczuk (CDS), Clément Chastagnol (Sidetrade),<br /> Gael Varoquaux (Inria, Parietal), Alex Gramfort (Inria, Parietal), Guillaume Lemaitre (Scikit-learn @ Inria Foundation)</center>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Predicting revenue using French Attribution Notices: [RAMP studio challenge](https://ramp.studio/problems/fan_revenue_prediction)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Table of Contents\n",
    "\n",
    "0. [Introduction](#Introduction)\n",
    "1. [Data](#Data)\n",
    "3. [Score metric](#Score-metric)\n",
    "4. [Data exploration](#Data-exploration)\n",
    "5. [Predictions](#Predictions)\n",
    "6. [Record linkage](#Record-linkage)\n",
    "7. [Submission structure](#Submission-structure)\n",
    "8. [Local testing](#Local-testing-(before-submission))\n",
    "9. [Submitting to RAMP studio](#Submitting-to-[ramp.studio](http://ramp.studio))\n",
    "10. [More information](#More-information)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Introduction\n",
    "\n",
    "The objective of this challenge is to work with 'dirty data'. Most real data is dirty and the availability of high-quality, open-source ML and data analysis frameworks (such as [scikit-learn](https://scikit-learn.org/),\n",
    "[pandas](https://pandas.pydata.org/)...) means that the next frontier for tooling and automation lies in preprocessing. This challenge aims to investigate methodologies to perform statistical analysis directly on the original dirty data.\n",
    "\n",
    "There are two datasets in this challenge:\n",
    "\n",
    "* `company_revenue_TRAIN.csv` - company revenue declarations.\n",
    "* `award_notices_RAMP.csv` -  French Attribution Notices.\n",
    "\n",
    "# Aim\n",
    "\n",
    "The predictive aim of this challenge is to use `company_revenue_TRAIN.csv` and `award_notices_RAMP.csv` to predict the Revenue for each entry in the 'company financial data' dataset. It is advised that you use both datasets, as it improves the prediction (see [Score comparison](#Score-comparison)), but using only the `company_revenue_TRAIN.csv` dataset is also allowed."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data\n",
    "\n",
    "## Company financial data\n",
    "\n",
    "This dataset was built from an extract of the [National Institute of Statistics and Economic Studies (INSEE)](https://www.insee.fr/en/accueil) reference database of company revenue declarations from 2013 to 2018. Each row represents the declaration of one company for one year and the following information is provided in the columns:\n",
    "\n",
    "* `Legal_ID` - the reconcilled legal ID of the company\n",
    "* `Name` - the name of the company\n",
    "* `Activity_code (APE)` - 'Activite Principale de l'Entreprise', the main activity of the company - more information in [English](https://www.startbusinessinfrance.com/code-ape) or in [French](https://www.service-public.fr/professionnels-entreprises/vosdroits/F33050)\n",
    "* `Address` \n",
    "* `Zipcode`\n",
    "* `City`\n",
    "* `Revenue` - in Euros\n",
    "* `Headcount`\n",
    "* `Fiscal_year_end_date`\n",
    "* `Fiscal_year_duration_in_months`\n",
    "* `Year`\n",
    "\n",
    "There are a few things to note:\n",
    "\n",
    "* there are revenue declarations for the same company but different years\n",
    "* there is a large reduction in entries for the years 2017 and 2018 due to the Loi Macron law in 2017\n",
    "* the 'same company' can have several different entities, resulting in entries where the `Legal_ID` is different but the `Name`, `Address`, `City` and `Zipcode` are all the same.\n",
    "* `Revenue` can be negative. A negative revenue may be due to cancelled orders from the previous fiscal year that was recorded in the current fiscal year.\n",
    "\n",
    "\n",
    "## Award notices\n",
    "\n",
    "Every French public organisation has to issue a call for tenders when buying supplies or services (above a minimum threshold). These are called public procurement contracts. Companies then compete anonymously on these contracts and when a bid is awarded, a notice has to be legally published by the public organization on the [BOAMP](https://www.boamp.fr/) (historical data is hosted by the [DILA](https://www.dila.premier-ministre.gouv.fr/)). These are called French Attribution Notices (FAN). About 25% of awards are actually electronically published.\n",
    "\n",
    "Each contract can be divided into a maximum of 5 lots and the same company can win >1 lot of a contract. The award notices dataset comprises award notices from 2017 and 2018. Each row refers to one lot and there can be up to 5 lots referring to the same contract. The following information is provided for each lot:\n",
    "\n",
    "* `ID_call` - ID of the award notice\n",
    "* `Publication_date` of the award notice\n",
    "* `End_of_call_date` of the award notice\n",
    "* `Departments_of_publication` - the department code(s) of the award notice\n",
    "* `Department_of_provision` - the department code(s) where the contract works/goods/services were provided \n",
    "* `Call_summary` - summary of the award notice\n",
    "* `Call_title` - title of the award notice\n",
    "* `Complete_call_description` - description of the award notice\n",
    "* `Total_amount` - total amount of the contract, from all lots, in euros\n",
    "* `CPV_classes` - Common Procurement Vocabulary (CPV), a classification system for public procurement used to describe the subject of procurement contracts (more information can be found [here](https://simap.ted.europa.eu/cpv))\n",
    "Columns providing details about the company issuing the contract:\n",
    "* `Buyer_name` \n",
    "* `Buyer_address`\n",
    "* `Buyer_zipcode`\n",
    "* `Buyer_city`\n",
    "* `Buyer_email`\n",
    "* `Buyer_URL`\n",
    "\n",
    "* `Contract_awarded` - whether or not the contact was awarded\n",
    "\n",
    "Columns providing details about the winner of each lot:\n",
    "\n",
    "* `ID` - unique lot ID \n",
    "* `awarded` - whether or not the contact was awarded\n",
    "* `description` - description of the lot\n",
    "* `incumbent_name` - name of the lot winnter\n",
    "* `incumbent_address` - address of the lot winner\n",
    "* `incumbent_zipcode` - zipcode of the lot winner\n",
    "* `incumbent_city` - city of the lot winner\n",
    "* `incumbent_country` - country of the lot winner\n",
    "* `number_of_received_bids` - number of bids this lot received\n",
    "* `amount` - amount of the lot in euros\n",
    "\n",
    "**Important notes**\n",
    "\n",
    "Both data sets are very dirty. There is a lot of missing data and the column descriptions provided above are a guide only. Further, the award notices dataset is much smaller than the company revenue declarations dataset. Therefore, it is expected that many companies in the company revenue declarations dataset are not present in the award notices dataset.\n",
    "\n",
    "## Training and test\n",
    "\n",
    "The company revenue training dataset has been split into 'training' and 'test' subsets. The shapes are:\n",
    "\n",
    "* training: (1 495 948, 11)\n",
    "* test: (520 966, 11)\n",
    "\n",
    "Your model will be tested on a completely separate company revenue dataset stored on the RAMP server. This dataset has a shape of (702 181, 11). This test dataset will also be dirty but we can guarantee that the following columns will (only) be of numerical data type:\n",
    "\n",
    "* `Legal_ID`\n",
    "* `Headcount`\n",
    "* `Fiscal_year_duration_in_months`\n",
    "* `Year`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Score metric\n",
    "\n",
    "A unique score is used:\n",
    "\n",
    "$$score =  |max(5,log_{10}(max(1,y\\_true))) - max(5,log_{10}(max(1,y\\_pred)))| $$\n",
    "\n",
    "Score interpretation:\n",
    "\n",
    "* A lower score is better\n",
    "* Any `y_true` or `y_pred` value less than 1 is 'taken' as 1\n",
    "* If both the `y_true` and `y_pred` are less than 100 000, the score would be 0.\n",
    "* The score is the same regardless of the order of `y_true` and `y_pred` in the equation.\n",
    "* If the difference in raw `y_true` and `y_pred` values is the same, the score is greater for smaller magnitudes of `y_true` and `y_pred`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data exploration"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "%matplotlib inline\n",
    "import imp\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from matplotlib import pyplot as plt\n",
    "import seaborn"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Company financial data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "from problem import get_train_data\n",
    "\n",
    "X_df, y_array = get_train_data()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>Legal_ID</th>\n",
       "      <th>Name</th>\n",
       "      <th>Activity_code (APE)</th>\n",
       "      <th>Address</th>\n",
       "      <th>Zipcode</th>\n",
       "      <th>City</th>\n",
       "      <th>Headcount</th>\n",
       "      <th>Fiscal_year_end_date</th>\n",
       "      <th>Fiscal_year_duration_in_months</th>\n",
       "      <th>Year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>582296</td>\n",
       "      <td>COMICOB</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BOULOUPARIS BP 15 BOULOUPARIS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016-12-31</td>\n",
       "      <td>12.0</td>\n",
       "      <td>2016.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>582981</td>\n",
       "      <td>CIANFARANI JEAN-MICHEL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>33 AVENUE H. LAFLEUR - VICTOIRE - B.P. 4031 NO...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016-12-31</td>\n",
       "      <td>12.0</td>\n",
       "      <td>2016.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>588541</td>\n",
       "      <td>OK POULET 5EME SARL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>23 RUE PAUL BERT</td>\n",
       "      <td>98800.0</td>\n",
       "      <td>NOUMEA</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2016-12-31</td>\n",
       "      <td>12.0</td>\n",
       "      <td>2016.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Legal_ID                    Name Activity_code (APE)  \\\n",
       "0    582296                 COMICOB                 NaN   \n",
       "1    582981  CIANFARANI JEAN-MICHEL                 NaN   \n",
       "2    588541     OK POULET 5EME SARL                 NaN   \n",
       "\n",
       "                                             Address  Zipcode    City  \\\n",
       "0                      BOULOUPARIS BP 15 BOULOUPARIS      NaN     NaN   \n",
       "1  33 AVENUE H. LAFLEUR - VICTOIRE - B.P. 4031 NO...      NaN     NaN   \n",
       "2                                   23 RUE PAUL BERT  98800.0  NOUMEA   \n",
       "\n",
       "   Headcount Fiscal_year_end_date  Fiscal_year_duration_in_months    Year  \n",
       "0        NaN           2016-12-31                            12.0  2016.0  \n",
       "1        NaN           2016-12-31                            12.0  2016.0  \n",
       "2        1.0           2016-12-31                            12.0  2016.0  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "X_df.head(3)\n",
    "# Group cities by departments"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "X_df['Fiscal_year_end_date'] = pd.to_datetime(X_df['Fiscal_year_end_date'], format='%Y-%m-%d')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1495948, 10)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "X_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Legal_ID                          0.000000\n",
       "Name                              0.000002\n",
       "Activity_code (APE)               0.012293\n",
       "Address                           0.175296\n",
       "Zipcode                           0.167944\n",
       "City                              0.000313\n",
       "Headcount                         0.637439\n",
       "Fiscal_year_end_date              0.000000\n",
       "Fiscal_year_duration_in_months    0.000000\n",
       "Year                              0.000000\n",
       "dtype: float64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# proportion of NaN values\n",
    "X_df.isna().sum() / X_df.shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Legal_ID                          563929\n",
       "Name                              528646\n",
       "Activity_code (APE)                  964\n",
       "Address                           276152\n",
       "Zipcode                             7471\n",
       "City                               29498\n",
       "Headcount                           2224\n",
       "Fiscal_year_end_date                 300\n",
       "Fiscal_year_duration_in_months         1\n",
       "Year                                   6\n",
       "dtype: int64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# number of unique values\n",
    "X_df.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Legal_ID                                   int64\n",
       "Name                                      object\n",
       "Activity_code (APE)                       object\n",
       "Address                                   object\n",
       "Zipcode                                  float64\n",
       "City                                      object\n",
       "Headcount                                float64\n",
       "Fiscal_year_end_date              datetime64[ns]\n",
       "Fiscal_year_duration_in_months           float64\n",
       "Year                                     float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "X_df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>Legal_ID</th>\n",
       "      <th>Zipcode</th>\n",
       "      <th>Headcount</th>\n",
       "      <th>Fiscal_year_duration_in_months</th>\n",
       "      <th>Year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>1.495948e+06</td>\n",
       "      <td>1.244713e+06</td>\n",
       "      <td>5.423720e+05</td>\n",
       "      <td>1495948.0</td>\n",
       "      <td>1.495948e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>5.060829e+08</td>\n",
       "      <td>5.464419e+04</td>\n",
       "      <td>8.736985e+01</td>\n",
       "      <td>12.0</td>\n",
       "      <td>2.014475e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>1.417373e+08</td>\n",
       "      <td>2.755171e+04</td>\n",
       "      <td>9.002249e+03</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.246598e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>5.822960e+05</td>\n",
       "      <td>6.000000e+00</td>\n",
       "      <td>-3.900000e+01</td>\n",
       "      <td>12.0</td>\n",
       "      <td>2.013000e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>4.149439e+08</td>\n",
       "      <td>3.185000e+04</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>12.0</td>\n",
       "      <td>2.013000e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>4.895445e+08</td>\n",
       "      <td>6.053000e+04</td>\n",
       "      <td>3.000000e+00</td>\n",
       "      <td>12.0</td>\n",
       "      <td>2.014000e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>5.293306e+08</td>\n",
       "      <td>7.511600e+04</td>\n",
       "      <td>9.000000e+00</td>\n",
       "      <td>12.0</td>\n",
       "      <td>2.015000e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>9.999905e+08</td>\n",
       "      <td>9.889500e+04</td>\n",
       "      <td>5.450000e+06</td>\n",
       "      <td>12.0</td>\n",
       "      <td>2.018000e+03</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Legal_ID       Zipcode     Headcount  \\\n",
       "count  1.495948e+06  1.244713e+06  5.423720e+05   \n",
       "mean   5.060829e+08  5.464419e+04  8.736985e+01   \n",
       "std    1.417373e+08  2.755171e+04  9.002249e+03   \n",
       "min    5.822960e+05  6.000000e+00 -3.900000e+01   \n",
       "25%    4.149439e+08  3.185000e+04  1.000000e+00   \n",
       "50%    4.895445e+08  6.053000e+04  3.000000e+00   \n",
       "75%    5.293306e+08  7.511600e+04  9.000000e+00   \n",
       "max    9.999905e+08  9.889500e+04  5.450000e+06   \n",
       "\n",
       "       Fiscal_year_duration_in_months          Year  \n",
       "count                       1495948.0  1.495948e+06  \n",
       "mean                             12.0  2.014475e+03  \n",
       "std                               0.0  1.246598e+00  \n",
       "min                              12.0  2.013000e+03  \n",
       "25%                              12.0  2.013000e+03  \n",
       "50%                              12.0  2.014000e+03  \n",
       "75%                              12.0  2.015000e+03  \n",
       "max                              12.0  2.018000e+03  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "X_df.describe()\n",
    "# Fiscal year = useless feature"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "revenue_zip = pd.concat([X_df['Zipcode'], pd.Series(y_array)], axis=1)\n",
    "revenue_zip['Departement'] = revenue_zip['Zipcode'].apply(lambda x : np.trunc(x/1000) if x > 100 else x)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Distribution of Revenue"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<matplotlib.axes._subplots.AxesSubplot at 0x153403198>"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAXcAAAD8CAYAAACMwORRAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDMuMC4yLCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvOIA7rQAAIABJREFUeJzt3Xl03OV97/H3dxYto323Nkuy8YIMZrFsAqSQhNBAQk1uSggkTaFNS3Mbmtzc5vSSppeTplvS3OY2vaEtlNKSlJQSmpyaxBSyNQRiwDKLsbwjedFqraN1NKOZ5/4xM2ZsZGssjea36Ps6h2PN6KeZ7+8gffTo+3ue5yfGGJRSSrmLx+oClFJKZZ6Gu1JKuZCGu1JKuZCGu1JKuZCGu1JKuZCGu1JKuZCGu1JKuZCGu1JKuZCGu1JKuZDPqjeurKw0zc3NVr29Uko50p49e4aMMVULHWdZuDc3N9Pe3m7V2yullCOJyPF0jtO2jFJKuZCGu1JKuZCGu1JKuZCGu1JKuZCGu1JKuZCGu1JKuZCGu1JKuZCGu1oR9HaSaqXRcFeu1x8Mce2Xf8Kf/WC/hrxaMTTclasZY/jC996gbzzEP/y8iy/u6NCAVyuCZdsPKJUNO17v5ccHT/FHH7iYgUTAxwz88fZNeDxidXlKLRsNd+VaQ5OzfHFHB5c3lvIb17bgEfB4hAd/1kljeT73XLfW6hKVWjballGu9cUdHUzOzvGXt23G6xFEhPtu2sjVayr41ovHicW0PaPcS8NdudLJkWm+v7ePT16/lvU1RaefFxFu39rAyZEZXj42YmGFSi0vDXflSgf7JwB498bqt33upk21FOb6eHJPd7bLUiprNNyVKx0eiIf7uurCt30uP8fLLZtr2flGH1Ozc9kuTams0HBXrnR4YIL60nyK8vzzfv62LQ1Mh6M8va8/y5UplR0a7sqVDvVPsL7m7aP2pC1NZTRXBPhO+8ksVqVU9qQV7iJyk4gcEpGjInLfPJ+/W0QGReS1xH+/lflSlUrPXDRG5+DUGRdSzyYi3LalgZe6RjgxPJ3F6pTKjgXDXUS8wAPAzUArcKeItM5z6L8ZYy5P/PdwhutUKm3HhqcJR2PnDXeAD13ZgAh891W9sKrcJ52R+zbgqDGm0xgTBh4Hbl3espRavCOJi6kbVp0/3OtK87m8sZSfHxnKRllKZVU64V4PpDYmuxPPne1XRWSviDwpIo0ZqU6pRTg0MIEIrK06d889aVtLOXu7xwhFolmoTKnsydQF1aeAZmPMZuCHwKPzHSQi94hIu4i0Dw4OZuitlTrT4YEJmsoD5Od4Fzz2qpZyIlHDqyfGslCZUtmTTrj3AKkj8YbEc6cZY4aNMbOJhw8DW+Z7IWPMQ8aYNmNMW1VV1WLqVWpBhwcmWbdAvz1pS1M5IvByl65WVe6STrjvBtaJSIuI5AB3ADtSDxCR2pSH24EDmStRqfTNzkXpGppiQ5rhXpLvZ+OqYnbrVgTKZRYMd2PMHHAv8Azx0H7CGNMhIl8Ske2Jwz4tIh0i8jrwaeDu5SpYqfPpGpoiGjOsX+BiaqptzWW8cmKUSDS2jJUplV1pbflrjNkJ7DzruftTPv488PnMlqbUhTuU2FPmfAuYzratpYJHdx2no3ecyxtLl6s0pbJKV6gqVzkyMInPI6ypTD/ct7aUAbBb++7KRTTclascGpigubKAHF/639rVRXm0VBbwkoa7chENd+Uqhwcm0r6Ymmprcxntx0f0Bh7KNTTclWvMhKOcGJlm3QX025O2tVQwNh3h6ODkMlSmVPZpuCvX6ByaxBhYV33hI/dtzeUA2ppRrqHhrlyjdywEQENZ/gV/bWN5PquK83Qxk3INDXflGv3BGQBqS/Mu+GtFhKvWlPNi5zDGaN9dOZ+Gu3KN3mAIv1eoLMhd1NdfvaaCwYlZ3tS+u3IBDXflGv3BEDXFeXg8sqivv3ptBQC73hzOZFlKWULDXblG79gMtSUX3pJJWl0eoK4kj12dGu7K+TTclWv0j4eoLbnwi6lJIsLVayt5sVPnuyvn03BXrmCMoS8YWtLIHeKtmZGpMIcSd3NSyqk03JUrjEyFCc/FMhLuoH135Xwa7soV+oLxOe6rltCWAagvzWd1eUD77srxNNyVKyTDvW4Rc9zPdvWaCl7qHCaqfXflYBruyhX6EguYVi2xLQPx1sx4aI79veNLfi2lrKLhrlyhd2xpC5hSne67dw4t+bWUskpad2JSyu76gzNLWsCUqqY4jzVVBfzVs4d55Plj5Po9vHtDNV/cvikDlSqVHTpyV67QGwxRt8SLqam++Cub+MjWRq5fX0VpIIdv7jrG0ORsxl5fqeWmI3flCv3BUEbvf3rd+iquW18FwIG+cW7++s95tmOAj161OmPvodRy0pG7crxYzNAfDC1qN8h0bFxVREtlATvf6FuW11dqOWi4K8cbmQ4TjsaoLV6ecBcR3n/pKnZ1DjMyFV6W91Aq0zTcleP1JW7SUVuauZ772d5/aS3RmOGZjv5lew+lMknDXTleco57Ji+onq21tpjmioC2ZpRjaLgrx3tr64HlactAvDVz86W1/OLNYUa1NaMcQMNdOV5fMESO10NFQc6yvs8HEq2ZZ/dra0bZn4a7cry+4Aw1JbkZWcB0PpvqilldHuAHb2i4K/vTcFeO1ze2tJt0pEtEeM/GanZ3jehNtJXtabgrx+sbX9rt9S7E2upCZiJRBsZ1taqyNw135WinFzBlYeQO0FJRAEDX0FRW3k+pxUor3EXkJhE5JCJHReS+8xz3qyJiRKQtcyUqdW7DU2EiUZO1kXtzZQCAY8Ma7sreFgx3EfECDwA3A63AnSLSOs9xRcBngJcyXaRS55Kc456tcK8rySfH5+GYjtyVzaUzct8GHDXGdBpjwsDjwK3zHPcnwFeAUAbrU+q8+rMwxz2VxyM0lQe0LaNsL51wrwdOpjzuTjx3mohcCTQaY36QwdqUWtCpifiFzZpl2ldmPs2VBdqWUba35AuqIuIBvgb8fhrH3iMi7SLSPjg4uNS3VopT4yE8wrIvYErVUlnA8eFpYnqPVWVj6YR7D9CY8rgh8VxSEXAJ8F8icgx4B7BjvouqxpiHjDFtxpi2qqqqxVetVMLA+CwVhbn4vNmb+NVcUcDsXIy+ce1AKvtK5ydiN7BORFpEJAe4A9iR/KQxJmiMqTTGNBtjmoEXge3GmPZlqVipFKcmQtQUL/2+qRfi9IwZ7bsrG1sw3I0xc8C9wDPAAeAJY0yHiHxJRLYvd4FKnc/A+CzVRdnrt0O8LQM6113ZW1q32TPG7AR2nvXc/ec49l1LL0up9JyamOWyxpKsvmdNUR55fp0OqexNV6gqx4pEYwxPZX/k7vEIzRUFOnJXtqbhrhxraHIWY6A6yz13iF9U7dLpkMrGNNyVY51KbN5Vk+WRO8Tnup8cmWYuGsv6eyuVDg135VgDiamI2VzAlNRSGSASNfSO6XRIZU8a7sqxBhKrU61qywDamlG2peGuHGvQgtWpScnpkDpjRtmVhrtyLCtWpyZVFeVSkOPVGTPKtjTclWNZsTo1SURoqtANxJR9abgrxxoYn7VkpkxSS2WBtmWUbWm4K8c6NRGy5GJqUnNlgJOjM0R0OqSyIQ135Ujx1anhrK9OTdVUXkA0ZujT6ZDKhjTclSNZuTo1aXVFfHfI4yPamlH2o+GuHMnK1alJTclwH562rAalzkXDXTmSlatTk2qK8sjxeTgxouGu7EfDXTmSlatTkzweYXV5gOM6HVLZkIa7ciQrV6emaioPaFtG2ZKGu3KkgfFZKi1anZpqdUWAEyPTGKM3y1b2ouGuHGnA4jnuSU3lAabDUYYmw1aXotQZNNyVI52yeHVqUlNid8gTOh1S2YyGu3Ikq1enJq3W6ZDKpjTclePYYXVqUkNZPiIa7sp+NNyV4yRXp1o5xz0p1+eltjhP57or29FwV44zkFidWl1kfVsG4q0Zneuu7EbDXTnOqcTqVDv03CG+gZiO3JXdaLgrx0luPbDKBm0ZiI/chybDTM3OWV2KUqdpuCvH6QuG8HmEikKbjNwTM2Z09K7sRMNdOU7/eIjqoly8HrG6FCDelgGdMaPsRcNdOc7AeIiaEnu0ZOCtue66kEnZiYa7cpz+YIhaG4V7Sb6f0oBfR+7KVjTcleP0B0O2mOOeqqk8oD13ZStphbuI3CQih0TkqIjcN8/nPykib4jIayLyvIi0Zr5UpWAiFGEqHLXNTJmk1RUFOnJXtrJguIuIF3gAuBloBe6cJ7y/bYy51BhzOfCXwNcyXqlSpEyDtFFbBuIj956xGSLRmNWlKAWkN3LfBhw1xnQaY8LA48CtqQcYY8ZTHhYAurm1WhZ9QXvNcU9qqggQjRl6RmesLkUpIL1wrwdOpjzuTjx3BhH5lIi8SXzk/un5XkhE7hGRdhFpHxwcXEy9aoXrD9pz5L6mKj4dsmtIZ8woe8jYBVVjzAPGmLXA/wL+6BzHPGSMaTPGtFVVVWXqrdUKYocbY8+npbIQgE4Nd2UT6YR7D9CY8rgh8dy5PA58cClFKXUu/eMhSgN+8vxeq0s5Q1nAT3Gej66hSatLUQpIL9x3A+tEpEVEcoA7gB2pB4jIupSHHwCOZK5Epd7SHwzZrt8OICK0VBVybEhnzCh78C10gDFmTkTuBZ4BvMAjxpgOEfkS0G6M2QHcKyLvBSLAKHDXchatVq7+8ZDt+u1JayoLeLlrxOoylALSCHcAY8xOYOdZz92f8vFnMlyXUvPqD85ySV2J1WXMq7migO+92kMoErVd20itPLpCVTlGeC7G8NSsbUfuLVW6gZiyDw135RinJkIYY7857klrKpPTIfWiqrKehrtyjNPTIG06cm9OhLtOh1R2oOGuHKM/GL93ql1H7oW5PqqKcuka1HBX1tNwV47RF4wv7bfTdr9na6ks4JjeLFvZgIa7coyB8RC5Pg8l+X6rSzmnNZUFugWBsgUNd+UY/ePxmTIi9ri93nyaKwsYmgwzHopYXYpa4TTclWMM2HR1aqqWxEXVYzp6VxbTcFeO0Tc+Y9s57klvTYfUcFfW0nBXjmCMYWB81vYj99UVAUSgU2fMKItpuCtHGJ2OEJ6L2W6r37Pl+rzUl+bryF1ZTsNdOYITpkEm6XRIZQca7soR7L46NdWaygK6BqcwRu82qayj4a4cwa73Tp1Pc2UBE7NzDE7OWl2KWsE03JUjnBieJsfncUS4r6suAuDogG4gpqyj4a4coWtoitXlATwe+y5gStpYGw/3A/0TFleiVjINd+UIx4anaK4osLqMtFQW5lJZmMvBvnGrS1ErmIa7sr1YzHB8eJqWyoDVpaRt46oiDurIXVlIw13ZXt94iNm52On90p1g46oiDg9MEI3pjBllDQ13ZXvJfVpaHNKWAdhYW8zsXEznuyvLaLgr20uu9nTayB3gYJ+2ZpQ1NNyV7R0bmiLXIdMgky6qLsQjcKhfL6oqa2i4K9tLzpRxwjTIpDy/lzVVhTodUllGw13ZXtfQFM0OmimTFJ8xoyN3ZQ0Nd2Vr0Zjh5MiMo/rtSRtXFXFyZIbJ2TmrS1ErkIa7srXesRnC0ZijZsokbVxVDMAhbc0oC2i4K1tLTiVscmK4J7Yh0NaMsoKGu7K103PcHdiWqS/NpyjXp9MhlSU03JWtdQ1Nk+/3UlOca3UpF0xE2LCqSNsyyhIa7srWjg1P0VQRQMQ50yBTbawt4kD/uN64Q2VdWuEuIjeJyCEROSoi983z+f8pIvtFZK+I/FhEmjJfqlqJjg1NObIlk7RxVTEToTl6EzcbUSpbFgx3EfECDwA3A63AnSLSetZhrwJtxpjNwJPAX2a6ULXyzEVjnBiZduQ0yKSLa+MzZjp6ghZXolaadEbu24CjxphOY0wYeBy4NfUAY8xPjTHTiYcvAg2ZLVOtRD1jM8zFjCOnQSZtqivG6xH2dmu4q+xKJ9zrgZMpj7sTz53LJ4Cn5/uEiNwjIu0i0j44OJh+lWpFcuKGYWfL83tZX1PEXh25qyzL6AVVEfk1oA346nyfN8Y8ZIxpM8a0VVVVZfKtlQsdOx3uztt6INVlDSXs7R7Ti6oqq9IJ9x6gMeVxQ+K5M4jIe4EvANuNMXrbd7VkhwYmKMn3U1XovGmQqS5tKGFsOsLJkRmrS1ErSDrhvhtYJyItIpID3AHsSD1ARK4AHiQe7KcyX6ZaiTp6x7mkvtix0yCTLmsoBWBvz5jFlaiVZMFwN8bMAfcCzwAHgCeMMR0i8iUR2Z447KtAIfAdEXlNRHac4+WUSkskGuNg3wSX1JVYXcqSra8pIsfr0YuqKqt86RxkjNkJ7DzruftTPn5vhutSK9yRgUnC0RitdcVWl7JkOT4PF9cVs7dbR+4qe3SFqrKlfb3xUe4l9c4fuQNsri9hX884Mb1htsoS14R7LGZ45cSo1WWoDOnoCVKQ43X0HPdUmxtKmJydo3NIb5itssM14f7ckUE+9Le/4MiAbtLkBh2947TWFTvq1nrnszl5UVVbMypLXBPu/Ym9OwYndRam00Vjhv1942xywcXUpIuqC8n3e/Wiqsoa14T72EwEgMmQ3tLM6bqGppgOR9nkgoupSV6PcEm9XlRV2eOacB+dDgPo/SpdoMNlF1OTNjeU0tE7zlw0ZnUpagVwTbgHpxMjdw13x9vXEyTH5+Gi6kKrS8mozQ0lzM7FODwwaXUpagVwTbgnR+4T2pZxvI7ecS5eVYTf65pvTwCuaCwD0FldKitc89MzpiN3VzDGsK8nyCaXtWQAGsvzqS7KZfexEatLUSuA68J9IhSxuBK1FN2jM4yH5lx1MTVJRNjaXM7uLg13tfzcE+4ziQuq2pZxtH2Jfc/dsKfMfLY2l9EbDNEzpjtEquXlinA3xjCqbRlX6Ogdx+sRNqwqsrqUZbG1pRxAR+9q2bki3EORGOG5+PQyvaDqbPt6g6yrLiTP77W6lGWxcVUxRbk+Xta+u1pmrgj3ZEsGdOTudMltB9zK6xGubCqjXcNdLTNXhPvoVLwlk+f3aLg72KmJEIMTs67admA+21rKOTwwyehUeOGDlVokV4R7cuTeUBbQC6oOtr93HIDWWveO3AHamuLz3fcc1/nuavm4I9wTF1Mby/K15+5gHclwd3FbBuCyxlJyvB6d766WlavCvaEsQDgaY3YuanFFajH2947TWJ5PSb7f6lKWVZ7fy6UNJXpRVS0rV4R7cuuBhrJ8QOe6O1VHb5BNte7utydtbS5nX0+QmbAORNTycEW4B2ci5Pk9VBbmAjpjxokmQhGODU+7cmXqfLa1lBGJGl47qVsAq+XhinAfnQpTmp9DYV78ft/ad3eeg/3xO2htql8Z4d7WXI7PIzx3ZNDqUpRLuSLcx2YilAb8FOXGw11H7s7Tkdh2wO3TIJOK8/xsbS7nJwdOWV2Kcil3hPt0OB7uefELcdpzd56O3nEqCnKoLsq1upSsueHiag4NTHByZNrqUpQLuSTcI5QFUtoys7ozpNMkV6aKuOOG2Om44eIaAH5yUEfvKvNcEe6j0/G2TGGyLaMjd0cJz8U4cmpixbRkkloqC1hTVcCPDgxYXYpyIceHuzGG4EyY0kAORadH7hruTnJ4YIJI1KyYmTKpbthYzUudI3qdSGWc48N9OhwlEjWU5vvJ9XnweURH7g6zvy++MnVFhvvFNYSjMZ7XWTMqwxwf7skFTGWBHESEwjyfjoIcpqMnSCDHS3NFgdWlZF1bUxnFeT5+pLNmVIY5PtyTWw+UBOIzZQpzfTpyd5iXj41yWUMpHs/KuZia5PN6eNeGan568BSxmLG6HOUiaYW7iNwkIodE5KiI3DfP568TkVdEZE5Ebst8meeWDPeyQA4ARXl+7bk7yOhUmAN941yztsLqUixzw8XVDE+Fea1bV6uqzFkw3EXECzwA3Ay0AneKSOtZh50A7ga+nekCF5Lc7rc0MXIvyvXpTbId5MXOYQCuuWjlhvu71lfj9wo/2NtndSnKRdIZuW8DjhpjOo0xYeBx4NbUA4wxx4wxe4HYMtR4Xsl7p5YmdhLUnruz7OocJpDjZXNDqdWlWKYk4OfG1hq+92rP6dtFKrVU6YR7PXAy5XF34jlbCCYuqGrP3Zl+8eYwW5vL8Xsdf/lnST7c1sjIVJgf65x3lSFZ/YkSkXtEpF1E2gcHMzP1a3Q6QiDHS64vfkNlHbk7x6mJEEdPTXL1Cu63J123ropVxXk80X5y4YOVSkM64d4DNKY8bkg8d8GMMQ8ZY9qMMW1VVVWLeYm3SW49kBTvuWu4O8GuNxP9dg13vB7hti0N/OzwIP3BkNXlKBdIJ9x3A+tEpEVEcoA7gB3LW1b6xqbDZ9y5pzDXx+xcTHuXDrDrzWGK8nwrbtuBc7m9rZGYgSf36OhdLd2C4W6MmQPuBZ4BDgBPGGM6RORLIrIdQES2ikg38GHgQRHpWM6iU43NRCgreCvck1sQaGvG/nZ1DnNVSwXeFTi/fT6rKwJcvaaCJ9q7dc67WrK0eu7GmJ3GmPXGmLXGmD9LPHe/MWZH4uPdxpgGY0yBMabCGLNpOYtONTYdv1FHUqFu++sIPWMzHB+e1n77WW7f2sCJkWle6tL7q6qlcfwUhbHEjpBJyZ0hddtfe9N++/xuvqSWknw/j/7imNWlKIdzdLgbY07fhSnpdFtGR+629sLRIcoCfjbUFFldiq3k+b18/B1NPLO/nzcHJ60uRzmYo8N9YnaOaMycMVumUG+1Z3ujU2Ge3tfHL7euWpH7ySzk7mubyfF6+IfnOq0uRTmYo8M9mNw0LHW2jF5Qtb1/3X2CUCTGb7yz2epSbKmyMJfb2xr57is9DIzrtEi1OI4O99TtfpOSN8nWue72FInG+Nau41yztoKNq1be/u3p+u1fWsNcLMYjz3dZXYpyKEeHe3JHyDN77vGPNdzt6ZmOfvqCIX7j2harS7G11RUBbtlcx2MvnSA4o5MD1IVzdLgnR+6lKSP3PL8Hr0eY1NkytvTI8100VQR4z8Zqq0uxvd+5fg2Ts3N8U2fOqEVwdLgPTswCUFn4VriLiG4eZlOvnxzjlRNj3HV1sy5cSsOmuhJubK3hwec6OTWhvXd1YRwd7r1jIQpyvGdcUIX4jBm9YYf9/OPzXRTm+vhwW4PVpTjGH77/Ymbnonzt2cNWl6IcxtHh3jM2TV1pPiJnjgKL8nTkbjf7e8d5am8vH3vH6tPXRdTCWioL+PWrm/m39pN09AatLkc5iKPDvXcsRF1p/tueL8zVbX/t5iv/eZDiPD+/e/1FVpfiOJ9+zzpK8/386fcPYIzuOaPS4/Bwn6G+bJ5w1z3dbeWFo0P87PAg9777otM3VVHpKwn4+eyN69nVOcyz+/VmHio9jg33mXCU4akw9ecYuetUSHuIxQx/8fQB6kvz+fjVTVaX41gf3baaDTVFfOF7+3Rhk0qLY8O9NzgDQF1p3ts+V5Tn13C3iaf29rKvZ5zPvW89eX6v1eU4ls/r4f999AqmZuf41GOvEInq/QrU+Tk33Mfi4V5fGnjb54ryfDrP3QaC0xG+/PRBWmuLufUy29x217HW1xTxlds20358lL/YedDqcpTNOTbce0bPPXIvzPURisR0dGMhYwz3fXcvgxOz/MWHLtUNwjJk+2V13H1NM4+80MWO13utLkfZmGPDvXdsBo9ATfH84Q4wpRdVLfP47pM8va+fz71vA5c1llpdjqv84fsvpq2pjM9953V2H9Obeqj5OTbce8ZCrCrOw+99+ykkd4bUvrs1jgxM8MdPdfDOiyq555fWWF2O6+T4PDz06200lObzW4+2c2RgwuqSlA05ONyn553jDm/tDKnTIbNvdCrMp779CgU5Pr52+2Xajlkm5QU5PPqb28jxebjrkZfpD+oMGnUmx4b7uRYwgY7crTI6FeZjD7/EseFp/ubOK6iep2WmMqexPMA/3b2V8dAcdz3yMmOJjfSUAoeGeyxm6AvOv4AJOD33vVNvU5Y1yWA/OjjJP/x6G9deVGl1SSvCJfUlPPTxLXQNT3HXP+3Wv1bVaY4M98HJWSJRc86Re0tlAeUFOew5PprlylamwYlZPpoS7Nevr7K6pBXlmosq+cadV7CvJ8g932wnFIlaXZKyAUeGe8/pOe7z/9kvIly5uow9JzTcl9vx4Slu+/tfcGxoioc12C3zy5tW8VcfvoxdncP893/Zw3RYR/ArnSPD/XwLmJK2NJXROTjFyJT2IZfLvp4gv/p3uxififDt376K6zTYLfXBK+r58/92KT87PMjtD+7SbQpWOEeG+/kWMCVtaSoD4BVtzWRcJBrj4Z93cvuDu8jxCt/55DVcsbrM6rIUcOe21Tx8Vxtdg1Pc+o0X2Nej2wSvVI4M996xGYryfOfdF3xzQwk+j2hrJsNe7hrhlr95nj/9wQGuainnu797LRdVF1pdlkrxno01fOeT1yACH/rbX/DVZw5qm2YF8lldwGL0jIXm3Q0yVZ7fy6b6Er2omiEH+8f5q2cP88P9A9SX5vPgx7fwy601b7tRirKH1rpidtz7Tv585wEe+Omb/PueHj7//o3csrlOb3G4Qjg03GcWDHeALavLeOyl40SisXlXsqrzM8bQfnyUb+06zlN7eynM9fH7N67nE7/UQiDHkd86K0pVUS7/9yOX87GrVnP/f3Twmcdf4+s/OsIn37WWD15eT45PfybczJE/ob1jM7Q1Ldzj3dJUxiMvdLG/d1z3N7kAbw5O8h+v9fK9V7s5OTJDIMfLJ69fy+9ct4bSQM7CL6Bspa25nKd+7508va+Pv/3pm/zBk3v52rOHuWNbI7e3NZ5zSrFyNseF++TsHMGZyDkXMKW6sike6HuOj2q4n0MsZugbD3F8aIrXu4N8f28vHb3jiMC1ayv57HvX875NqyjIddy3ikrh9Qi3bK7jA5fW8l+HB3nk+S7++kdH+JsfH+H69VV8uK2RGy6uJtene+67RVo/sSJyE/B1wAs8bIz58lmfzwW+CWwBhoGPGGOOZbbUuOQ0yHRGG7Ul+dSX5rPnxCi/SctylOM4kWiMg30TPHdkkOcOD/LqyTHCc29tjXxZYylNdY0KAAAHJUlEQVT/+5ZWPnBpLatKdPsAtxER3r2hmndvqObkyDT/tvsk39lzkt997BVK8v3csrmWbS3lXFpfQnNFge4N5GALhruIeIEHgBuBbmC3iOwwxuxPOewTwKgx5iIRuQP4CvCR5Sh4oQVMZ7uyqYz2FbQt6nR4jhMj0xwfnqZvbIahyTCDE7Px0fnwFN2jM0Rj8Zsst9YW8/F3NLG2qpCmigBrqwo10FeQxvIAn3vfBj5743peODrEk3u6+fdXunnspRNAfOvslsoCmioCNFcU0FieT31pgPqyfCoKcyjK9ekFdRtLZ+S+DThqjOkEEJHHgVuB1HC/Ffhi4uMngW+IiJhluFV7OguYUrU1lfHU673s7R6jpbKAwiV+QxpjSJ6VOev5mAFD/PPGQNQYojFDLGaImpR/TbwdEo7GCM8l/ovGmI3EmJ2LEonGiERN4t8Y4aghMhdjJhJlOjzH1GyUydk5JkKR022q4EyE4HSE8bM2S/N6hIqCHFaV5HFpfQm/srmOdTWFXL22guoiDXIV/x65bn0V162vIhKNcWRgkn09QTp6g3QOTbG3O8jON/qImbd/XXGej9JADsX5fkrz/RTl+SjI8VGQ66Mw10thno/CXD+BHC95fg+5fi95Pi85Pg+5Pg85Pg9ej+D3ePB5Bb/Xg98r+LwePAKexM+qRwRJPPYI+kslDemEez1wMuVxN3DVuY4xxsyJSBCoAIYyUWSqwlwflzeWUlWUm9bxW5vLAdj+jRcATn/DnP1bR4DU7xdj4uGdDG278HqEgD/+Q1OU56Mw10dVYS4XVRVSGsihqiiX1eUBmioC1JXmUx7I0T+tVdr8Xg+tdcW01hUDjaefj0Rj9AdD9IzN0DM6w+h0mOBMJPFvfIAxNh3m5Og0U7PxAchUeI7MD+/e4vUIqd/ZyZ/X1LcU3vrFIPLW8SKQfGTelgbJr5XTxy4kecj5Tjf1Ze7/lVY+snX1wi+8BFm9SiYi9wD3JB5Oisihxb6W7955n65kGX6hWMRN5wLuOh83nQu463wccS53/Anckd6h851PUzpfmE6495D6KxwaEs/Nd0y3iPiAEuIXVs9gjHkIeCidwhZDRNqNMW3L9frZ5KZzAXedj5vOBdx1Pm46F1ja+aSzimE3sE5EWkQkh/gvnB1nHbMDuCvx8W3AT5aj366UUio9C47cEz30e4FniE+FfMQY0yEiXwLajTE7gH8EviUiR4ER0v6LQyml1HJIq+dujNkJ7DzruftTPg4BH85saYuybC0fC7jpXMBd5+OmcwF3nY+bzgWWcD6i3ROllHIf3TlIKaVcyDXhLiI3icghETkqIvdZXc9iiUijiPxURPaLSIeIfMbqmpZKRLwi8qqIfN/qWpZKREpF5EkROSgiB0TkaqtrWiwR+Wzie2yfiPyriDhqVZuIPCIip0RkX8pz5SLyQxE5kvjXEXeROce5fDXxfbZXRL4nIhe0QZYrwj1li4SbgVbgThFptbaqRZsDft8Y0wq8A/iUg88l6TPAAauLyJCvA/9pjNkIXIZDz0tE6oFPA23GmEuIT5Zw2kSIfwZuOuu5+4AfG2PWAT9OPHaCf+bt5/JD4BJjzGbgMPD5C3lBV4Q7KVskGGPCQHKLBMcxxvQZY15JfDxBPDzqra1q8USkAfgA8LDVtSyViJQA1xGfHYYxJmyMGbO2qiXxAfmJtSkBoNfiei6IMeY54rPzUt0KPJr4+FHgg1ktapHmOxdjzLPGmOR+Ii8SX2OUNreE+3xbJDg2EJNEpBm4AnjJ2kqW5K+BPwBiCx3oAC3AIPBPiTbTwyJSYHVRi2GM6QH+D3AC6AOCxphnra0qI2qMMX2Jj/uBGiuLyaDfBJ6+kC9wS7i7jogUAv8O/A9jzLjV9SyGiNwCnDLG7LG6lgzxAVcCf2eMuQKYwjl/9p8h0Yu+lfgvrDqgQER+zdqqMiuxkNLx0wFF5AvE27WPXcjXuSXc09kiwTFExE882B8zxnzX6nqW4Fpgu4gcI94qe4+I/Iu1JS1JN9BtjEn+JfUk8bB3ovcCXcaYQWNMBPgucI3FNWXCgIjUAiT+PWVxPUsiIncDtwAfu9BV/24J93S2SHAEie9l+o/AAWPM16yuZymMMZ83xjQYY5qJ/z/5iTHGsaNDY0w/cFJENiSeuoEzt752khPAO0QkkPieuwGHXhw+S+pWKHcB/2FhLUuSuEnSHwDbjTHTF/r1rgj3xEWH5BYJB4AnjDEd1la1aNcCHyc+yn0t8d/7rS5KnfZ7wGMishe4HPhzi+tZlMRfH08CrwBvEM8CR63uFJF/BXYBG0SkW0Q+AXwZuFFEjhD/6+TL53sNuzjHuXwDKAJ+mMiBv7+g19QVqkop5T6uGLkrpZQ6k4a7Ukq5kIa7Ukq5kIa7Ukq5kIa7Ukq5kIa7Ukq5kIa7Ukq5kIa7Ukq50P8Hn/d8qDIZrAwAAAAASUVORK5CYII=\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "logRev = np.log10(np.clip(y_array, a_min=1, a_max=None))\n",
    "seaborn.kdeplot(logRev)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Award notices"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This dataset is present in the `data/` directory and is added by default to **all** submissions when submitting to RAMP studio. When testing your submission locally however, it is important to copy this file into the submission directory of the submission you wish to test.\n",
    "\n",
    "For example, the starting kit submission directory (`submissions/starting_kit`) contains a copy of this dataset as it is required for RAMP to work locally. You will not need to upload this data file when you are making a submission. See for more information."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/usr/local/lib/python3.7/site-packages/IPython/core/interactiveshell.py:3020: DtypeWarning: Columns (4,12) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    },
    {
     "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>CallID</th>\n",
       "      <th>Publication_date</th>\n",
       "      <th>End_of_call_date</th>\n",
       "      <th>Departments_of_publication</th>\n",
       "      <th>Departments_of_provision</th>\n",
       "      <th>Call_summary</th>\n",
       "      <th>Call_title</th>\n",
       "      <th>Complete_call_description</th>\n",
       "      <th>Total_amount</th>\n",
       "      <th>CPV_classes</th>\n",
       "      <th>...</th>\n",
       "      <th>ID</th>\n",
       "      <th>awarded</th>\n",
       "      <th>description</th>\n",
       "      <th>incumbent_name</th>\n",
       "      <th>incumbent_address</th>\n",
       "      <th>incumbent_zipcode</th>\n",
       "      <th>incumbent_city</th>\n",
       "      <th>incumbent_country</th>\n",
       "      <th>number_of_received_bids</th>\n",
       "      <th>amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>16-119770</td>\n",
       "      <td>2016-08-11 00:00:00</td>\n",
       "      <td>2016-09-20</td>\n",
       "      <td>13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>mission de maitrise d'oeuvre relative aux trav...</td>\n",
       "      <td>MISSION DE MAITRISE D'OEUVRE RELATIVE AUX TRAV...</td>\n",
       "      <td>MISSION DE MAITRISE D'OEUVRE RELATIVE AUX TRAV...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>71000000</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>NaN</td>\n",
       "      <td>AI PROJECT</td>\n",
       "      <td>11, avenue de la Capelette</td>\n",
       "      <td>13010</td>\n",
       "      <td>Marseille</td>\n",
       "      <td>FR</td>\n",
       "      <td>13.0</td>\n",
       "      <td>83200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>15-46335</td>\n",
       "      <td>2015-03-27 00:00:00</td>\n",
       "      <td>2015-04-25</td>\n",
       "      <td>85</td>\n",
       "      <td>85</td>\n",
       "      <td>fourniture de réactifs immunosérologiques pour...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Fourniture de réactifs immunosérologiques pour...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>33696200 33696500</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Kit pour recherche d'anticorps anti-IBR sur sé...</td>\n",
       "      <td>ID-VET</td>\n",
       "      <td>310 rue Louis Pasteur</td>\n",
       "      <td>34790</td>\n",
       "      <td>Grabels</td>\n",
       "      <td>FR</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>15-46335</td>\n",
       "      <td>2015-03-27 00:00:00</td>\n",
       "      <td>2015-04-25</td>\n",
       "      <td>85</td>\n",
       "      <td>85</td>\n",
       "      <td>fourniture de réactifs immunosérologiques pour...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Fourniture de réactifs immunosérologiques pour...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>33696200 33696500</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Kit pour recherche d'anticorps anti-IBR sur mé...</td>\n",
       "      <td>IDEXX MONTPELLIER SAS</td>\n",
       "      <td>323 RUE DE LA GALERA</td>\n",
       "      <td>34090</td>\n",
       "      <td>MONTPELLIER</td>\n",
       "      <td>FR</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      CallID     Publication_date End_of_call_date Departments_of_publication  \\\n",
       "0  16-119770  2016-08-11 00:00:00       2016-09-20                         13   \n",
       "1   15-46335  2015-03-27 00:00:00       2015-04-25                         85   \n",
       "2   15-46335  2015-03-27 00:00:00       2015-04-25                         85   \n",
       "\n",
       "  Departments_of_provision                                       Call_summary  \\\n",
       "0                      NaN  mission de maitrise d'oeuvre relative aux trav...   \n",
       "1                       85  fourniture de réactifs immunosérologiques pour...   \n",
       "2                       85  fourniture de réactifs immunosérologiques pour...   \n",
       "\n",
       "                                          Call_title  \\\n",
       "0  MISSION DE MAITRISE D'OEUVRE RELATIVE AUX TRAV...   \n",
       "1                                                NaN   \n",
       "2                                                NaN   \n",
       "\n",
       "                           Complete_call_description  Total_amount  \\\n",
       "0  MISSION DE MAITRISE D'OEUVRE RELATIVE AUX TRAV...           NaN   \n",
       "1  Fourniture de réactifs immunosérologiques pour...           NaN   \n",
       "2  Fourniture de réactifs immunosérologiques pour...           NaN   \n",
       "\n",
       "         CPV_classes   ...      ID awarded  \\\n",
       "0           71000000   ...     NaN     Yes   \n",
       "1  33696200 33696500   ...       1     Yes   \n",
       "2  33696200 33696500   ...       2     Yes   \n",
       "\n",
       "                                         description         incumbent_name  \\\n",
       "0                                                NaN             AI PROJECT   \n",
       "1  Kit pour recherche d'anticorps anti-IBR sur sé...                 ID-VET   \n",
       "2  Kit pour recherche d'anticorps anti-IBR sur mé...  IDEXX MONTPELLIER SAS   \n",
       "\n",
       "            incumbent_address incumbent_zipcode incumbent_city  \\\n",
       "0  11, avenue de la Capelette             13010      Marseille   \n",
       "1       310 rue Louis Pasteur             34790        Grabels   \n",
       "2        323 RUE DE LA GALERA             34090    MONTPELLIER   \n",
       "\n",
       "   incumbent_country number_of_received_bids   amount  \n",
       "0                 FR                    13.0  83200.0  \n",
       "1                 FR                     2.0      NaN  \n",
       "2                 FR                     2.0      NaN  \n",
       "\n",
       "[3 rows x 28 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "award = pd.read_csv('data/award_notices_RAMP.csv.zip', compression='zip')\n",
    "award.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(304098, 28)"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "award.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "CallID                         object\n",
       "Publication_date               object\n",
       "End_of_call_date               object\n",
       "Departments_of_publication     object\n",
       "Departments_of_provision       object\n",
       "Call_summary                   object\n",
       "Call_title                     object\n",
       "Complete_call_description      object\n",
       "Total_amount                  float64\n",
       "CPV_classes                    object\n",
       "Buyer_name                     object\n",
       "Buyer_address                  object\n",
       "Buyer_zipcode                  object\n",
       "Buyer_city                     object\n",
       "Buyer_email                    object\n",
       "Buyer_URL                      object\n",
       "Contract_awarded               object\n",
       "Lot                             int64\n",
       "ID                             object\n",
       "awarded                        object\n",
       "description                    object\n",
       "incumbent_name                 object\n",
       "incumbent_address              object\n",
       "incumbent_zipcode              object\n",
       "incumbent_city                 object\n",
       "incumbent_country              object\n",
       "number_of_received_bids       float64\n",
       "amount                        float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "award.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>Total_amount</th>\n",
       "      <th>Lot</th>\n",
       "      <th>number_of_received_bids</th>\n",
       "      <th>amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>1.121260e+05</td>\n",
       "      <td>304098.000000</td>\n",
       "      <td>220936.000000</td>\n",
       "      <td>2.159540e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>5.177948e+08</td>\n",
       "      <td>2.073213</td>\n",
       "      <td>4.606900</td>\n",
       "      <td>1.145206e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>6.712391e+10</td>\n",
       "      <td>1.293831</td>\n",
       "      <td>7.982825</td>\n",
       "      <td>2.288319e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>9.900000e-05</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>1.967891e+05</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>3.240000e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>4.821163e+05</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>1.159775e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>1.568197e+06</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>3.674995e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>1.000000e+13</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>727.000000</td>\n",
       "      <td>1.000000e+13</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Total_amount            Lot  number_of_received_bids        amount\n",
       "count  1.121260e+05  304098.000000            220936.000000  2.159540e+05\n",
       "mean   5.177948e+08       2.073213                 4.606900  1.145206e+08\n",
       "std    6.712391e+10       1.293831                 7.982825  2.288319e+10\n",
       "min    9.900000e-05       1.000000                 0.000000  0.000000e+00\n",
       "25%    1.967891e+05       1.000000                 2.000000  3.240000e+04\n",
       "50%    4.821163e+05       2.000000                 3.000000  1.159775e+05\n",
       "75%    1.568197e+06       3.000000                 5.000000  3.674995e+05\n",
       "max    1.000000e+13       5.000000               727.000000  1.000000e+13"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "award.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "CallID                        0.000016\n",
       "Publication_date              0.000016\n",
       "End_of_call_date              0.000510\n",
       "Departments_of_publication    0.000016\n",
       "Departments_of_provision      0.674611\n",
       "Call_summary                  0.000016\n",
       "Call_title                    0.346484\n",
       "Complete_call_description     0.005426\n",
       "Total_amount                  0.631283\n",
       "CPV_classes                   0.082602\n",
       "Buyer_name                    0.000016\n",
       "Buyer_address                 0.017484\n",
       "Buyer_zipcode                 0.024084\n",
       "Buyer_city                    0.000016\n",
       "Buyer_email                   0.163766\n",
       "Buyer_URL                     0.211047\n",
       "Contract_awarded              0.000016\n",
       "Lot                           0.000000\n",
       "ID                            0.271015\n",
       "awarded                       0.034907\n",
       "description                   0.512683\n",
       "incumbent_name                0.034943\n",
       "incumbent_address             0.140126\n",
       "incumbent_zipcode             0.103414\n",
       "incumbent_city                0.061280\n",
       "incumbent_country             0.034943\n",
       "number_of_received_bids       0.273471\n",
       "amount                        0.289854\n",
       "dtype: float64"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# proportion of NA values\n",
    "award.isna().sum() / award.shape[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Predictions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Company revenue only\n",
    "\n",
    "First, let's predict using only the `comp` dataset:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1. Create a transformer that deals with missing values:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Legal_ID                                   int64\n",
       "Name                                      object\n",
       "Activity_code (APE)                       object\n",
       "Address                                   object\n",
       "Zipcode                                  float64\n",
       "City                                      object\n",
       "Headcount                                float64\n",
       "Fiscal_year_end_date              datetime64[ns]\n",
       "Fiscal_year_duration_in_months           float64\n",
       "Year                                     float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "X_df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# list of numerical columns\n",
    "num_cols = ['Legal_ID', 'Headcount', 'Fiscal_year_duration_in_months', 'Year']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.impute import SimpleImputer\n",
    "from sklearn.pipeline import Pipeline\n",
    "\n",
    "numeric_transformer = Pipeline(steps=[\n",
    "    ('impute', SimpleImputer(strategy='median'))\n",
    "])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "2. Create a transformer to split `Fiscal_year_end_date` into separate year, month, day columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.preprocessing import FunctionTransformer\n",
    "\n",
    "\n",
    "def process_date(X):\n",
    "    date = pd.to_datetime(X['Fiscal_year_end_date'], format='%Y-%m-%d')\n",
    "    return np.c_[date.dt.year, date.dt.month, date.dt.day]\n",
    "\n",
    "date_transformer = FunctionTransformer(process_date, validate=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "3. Process the `Activity_code (APE)` column. At the moment the Scikit learn `OrdinalEncoder()` does not have a `handle_unknown` argument. This means that it would not be able to handle any values in `Activity_code (APE)` which appear in 'train' but do not appear in 'test'. Here we will simply get around this by using the first 2 characters of `APE`, which are always numbers. The first 2 numbers give the broad category the companies activities fall under (e.g. 'AGRICULTURE'). This column is then converted to numeric data type, so missing values can be dealt with by using the median value (with `SimpleImputer()`)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def process_APE(X):\n",
    "    APE = X['Activity_code (APE)'].str[:2]\n",
    "    return pd.to_numeric(APE, errors='coerce').values[:, np.newaxis]\n",
    "\n",
    "APE_transformer = FunctionTransformer(process_APE, validate=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "4. Only keep the numbers in the `Zipcode` column:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def zipcodes(X):\n",
    "    zipcode_nums = pd.to_numeric(X['Zipcode'], errors='coerce')\n",
    "    zipcode_nums = zipcode_nums.apply(lambda x : np.trunc(x/1000) if x > 100 else x)\n",
    "    return zipcode_nums.values[:, np.newaxis]\n",
    "\n",
    "zipcode_transformer = FunctionTransformer(zipcodes, validate=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally the full pipeline is assembled. \n",
    "\n",
    "* for missing values in numerical columns, the 'median' is used.\n",
    "* the date column `Fiscal_year_end_date` is transformed into separated year month and day columns.\n",
    "* `Activity_code (APE)` is dealt with as described above.\n",
    "* the columns `Name`, `Address` and `City` are all dropped."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.compose import ColumnTransformer\n",
    "from sklearn.pipeline import make_pipeline\n",
    "\n",
    "zipcode_col = ['Zipcode']\n",
    "date_cols = ['Fiscal_year_end_date']\n",
    "drop_cols = ['Name', 'Address', 'City','Fiscal_year_duration_in_months', 'Zipcode']\n",
    "APE_col = ['Activity_code (APE)']\n",
    "\n",
    "preprocessor_comp = ColumnTransformer(\n",
    "    transformers=[\n",
    "        ('zipcode', make_pipeline(zipcode_transformer, SimpleImputer(strategy='median')), zipcode_col),\n",
    "        ('num', numeric_transformer,  ),\n",
    "        ('date', make_pipeline(date_transformer, SimpleImputer(strategy='median')), date_cols),\n",
    "        ('APE', make_pipeline(APE_transformer, SimpleImputer(strategy='median')), APE_col),\n",
    "        ('drop cols', 'drop', drop_cols),\n",
    "    ])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[6.00000000e+01, 5.82296000e+05, 3.00000000e+00, ...,\n",
       "        1.20000000e+01, 3.10000000e+01, 4.90000000e+01],\n",
       "       [6.00000000e+01, 5.82981000e+05, 3.00000000e+00, ...,\n",
       "        1.20000000e+01, 3.10000000e+01, 4.90000000e+01],\n",
       "       [9.80000000e+01, 5.88541000e+05, 1.00000000e+00, ...,\n",
       "        1.20000000e+01, 3.10000000e+01, 4.90000000e+01],\n",
       "       ...,\n",
       "       [6.00000000e+01, 9.95750288e+08, 3.00000000e+00, ...,\n",
       "        1.00000000e+00, 3.10000000e+01, 2.80000000e+01],\n",
       "       [6.00000000e+01, 9.95750288e+08, 3.84000000e+02, ...,\n",
       "        1.20000000e+01, 3.10000000e+01, 2.80000000e+01],\n",
       "       [6.00000000e+01, 9.95750288e+08, 3.72000000e+02, ...,\n",
       "        1.20000000e+01, 3.10000000e+01, 2.80000000e+01]])"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# make sure it works\n",
    "preprocessor_comp.fit_transform(X_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will use a Random Forest Regressor model:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.ensemble import RandomForestRegressor\n",
    "\n",
    "regressor = RandomForestRegressor(n_estimators=10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Before we can test our model, we need to define our unique scoring function:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.metrics import make_scorer\n",
    "\n",
    "def loss(y_true, y_pred):\n",
    "    \n",
    "    if isinstance(y_true, pd.Series):\n",
    "        y_true = y_true.values\n",
    "\n",
    "    true = np.maximum(5., np.log10(np.maximum(1., y_true)))\n",
    "    pred = np.maximum(5., np.log10(np.maximum(1., y_pred)))\n",
    "    \n",
    "    loss = np.mean(np.abs(true - pred))\n",
    "    \n",
    "    return loss\n",
    "    \n",
    "fan_loss = make_scorer(loss, greater_is_better=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can test our model. Note that we use `GroupShuffleSplit` using `Legal_ID` as the group so that the same company ('Legal_ID') only appears in either 'train' or 'test' but does not appear both in 'train' **and** 'test'.\n",
    "\n",
    "This reflects the same conditions of this challenge where, the private 'test' data (on RAMP) does not contain any company that also appears in the public 'train' dataset you have access to. This is because `Revenue` for the same company is often very similar."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "mean: 4.401723e-01 (+/- 7.502720e-04)\n"
     ]
    }
   ],
   "source": [
    "from sklearn.model_selection import cross_val_score\n",
    "from sklearn.model_selection import GroupShuffleSplit\n",
    "\n",
    "clf = Pipeline(steps=[\n",
    "    ('preprocessing', preprocessor_comp),\n",
    "    ('classifier', regressor)])\n",
    "\n",
    "cv = GroupShuffleSplit(n_splits=8, test_size=0.25)\n",
    "\n",
    "scores_Xdf = -cross_val_score(clf, X_df, y_array, cv=cv, scoring=fan_loss, groups=X_df['Legal_ID'], n_jobs=2)\n",
    "\n",
    "print(\"mean: %e (+/- %e)\" % (scores_Xdf.mean(), scores_Xdf.std()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Naive merge\n",
    "\n",
    "Now let us predict using a naive merge of `award` and `comp` datasets.\n",
    "\n",
    "The naive merge will only use the name of the company. To aid the merging we will convert the name to all lower case and remove punctuation and white space."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "award['Name_processed'] = award['incumbent_name'].str.lower()\n",
    "award['Name_processed'] = award['Name_processed'].str.replace('[^\\w]','')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For each company, extract 2 features:\n",
    "\n",
    "* the number of award lots won\n",
    "* the total sum of award lots won"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    0\n",
       "sum      0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "award_features = award.groupby(['Name_processed'])['amount'].agg(['count','sum'])\n",
    "award_features.isna().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next we will perform a naive merge of `X_df` and `award_features`. \n",
    "\n",
    "Be careful in this step to ensure that the **order** of `X_df` is not changed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def merge_naive(X):\n",
    "    X['Name'] = X['Name'].str.lower()     \n",
    "    X['Name'] = X['Name'].str.replace('[^\\w]','')\n",
    "    df = pd.merge(X, award_features, left_on='Name', right_on='Name_processed', how='left')\n",
    "    return df[['count','sum']]\n",
    "merge_transformer = FunctionTransformer(merge_naive, validate=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Implement the same feature transformation steps as above and include the 'merge' step."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "zipcode_col = ['Zipcode']\n",
    "date_cols = ['Fiscal_year_end_date']\n",
    "drop_cols = ['Address', 'City']\n",
    "APE_col = ['Activity_code (APE)']\n",
    "merge_col = ['Name']\n",
    "\n",
    "preprocessor_merge = ColumnTransformer(\n",
    "    transformers=[\n",
    "        ('zipcode', make_pipeline(zipcode_transformer, SimpleImputer(strategy='median')), zipcode_col),\n",
    "        ('num', numeric_transformer, num_cols),\n",
    "        ('date', make_pipeline(date_transformer, SimpleImputer(strategy='median')), date_cols),\n",
    "        ('APE', make_pipeline(APE_transformer, SimpleImputer(strategy='median')), APE_col),\n",
    "        ('merge', make_pipeline(merge_transformer, SimpleImputer(strategy='median')), merge_col),\n",
    "        ('drop cols', 'drop', drop_cols),\n",
    "    ])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[6.00000000e+01, 5.82296000e+05, 3.00000000e+00, ...,\n",
       "        4.90000000e+01, 1.00000000e+00, 2.15600900e+05],\n",
       "       [6.00000000e+01, 5.82981000e+05, 3.00000000e+00, ...,\n",
       "        4.90000000e+01, 1.00000000e+00, 2.15600900e+05],\n",
       "       [9.80000000e+01, 5.88541000e+05, 1.00000000e+00, ...,\n",
       "        4.90000000e+01, 1.00000000e+00, 2.15600900e+05],\n",
       "       ...,\n",
       "       [6.00000000e+01, 9.95750288e+08, 3.00000000e+00, ...,\n",
       "        2.80000000e+01, 9.00000000e+00, 3.19457147e+06],\n",
       "       [6.00000000e+01, 9.95750288e+08, 3.84000000e+02, ...,\n",
       "        2.80000000e+01, 9.00000000e+00, 3.19457147e+06],\n",
       "       [6.00000000e+01, 9.95750288e+08, 3.72000000e+02, ...,\n",
       "        2.80000000e+01, 9.00000000e+00, 3.19457147e+06]])"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check it works\n",
    "preprocessor_merge.fit_transform(X_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This example is implemented in RAMP within the files in the folder `submissions/starting_kit`. \n",
    "\n",
    "The transformation steps above are implemented in the file `submissions/starting_kit/feature_extractor.py` (a copy of this file is shown below). This file needs to define a class called `FeatureExtractor` which requires a `fit()` and `transform()` function. The `fit()` function takes both `X_df` and `y_array` as parameters, meaning that you are able to engineer new features using `y_array` (e.g. target encoding). The `transform()` function only takes `X_df`. We only use the `transform()` function in our simple example below.\n",
    "\n",
    "Note that the `award` dataset is being read in from the submission folder (`submissions/starting_kit`). This means that when testing locally, each submission folder should contain a copy of the award dataset `award_notices_RAMP.csv`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will use a Random Forest Regressor model again:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "regressor = RandomForestRegressor(n_estimators=10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is implemented in the file `submissions/starting_kit/regressor.py` (a copy of this file is shown below). The `Regressor` class must have a `fit()` and `predict()` function. If you are using a scikit-learn function, this can be done by simply calling `fit()` and `predict()` on the regressor, as seen below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.ensemble import RandomForestRegressor\n",
    "from sklearn.base import BaseEstimator\n",
    "\n",
    "\n",
    "class Regressor(BaseEstimator):\n",
    "    def __init__(self):\n",
    "        self.reg = RandomForestRegressor(n_estimators=5)\n",
    "\n",
    "    def fit(self, X, y):\n",
    "        self.reg.fit(X, y)\n",
    "\n",
    "    def predict(self, X):\n",
    "        return self.reg.predict(X)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can test our model within this notebook, using `preprocessor_merge` and `regressor` defined above, in a `Pipeline()`. \n",
    "\n",
    "Once you are happy with a solution, you can transfer your solution to `feature_extractor.py` and `regressor.py` files and test your submission using RAMP (see 'Submissions')."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "mean: 4.357185e-01 (+/- 8.144325e-04)\n"
     ]
    }
   ],
   "source": [
    "clf = Pipeline(steps=[\n",
    "    ('preprocessing', preprocessor_merge),\n",
    "    ('classifier', regressor)])\n",
    "\n",
    "cv = GroupShuffleSplit(n_splits=8, test_size=0.25)\n",
    "\n",
    "scores_merge = -cross_val_score(clf, X_df, y_array, cv=cv, scoring=fan_loss, groups=X_df['Legal_ID'],\n",
    "                               n_jobs=2)\n",
    "\n",
    "print(\"mean: %e (+/- %e)\" % (scores_merge.mean(), scores_merge.std()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Score comparison\n",
    "\n",
    "You can see in the below plot of the scores that using the merged data always results in better scores (smaller is better):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<matplotlib.axes._subplots.AxesSubplot at 0x12866f400>"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAYQAAAD8CAYAAAB3u9PLAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDMuMC4yLCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvOIA7rQAAFIZJREFUeJzt3X+s3fV93/HnCxsEIRZscXJB4HCphvjZhbErO+mgul1GBItkSmAqpKvEOsWKGBmZBIo3aYRgbcNZRVU1rKrVuGXuGpTSRrvBxpCtOYu2FdcmtQfGteaBE9tt1nhRvF7Asx3e++N+bnRyudf3nHOvfe3L8yFd+Xw/3/fnez7fo6/P63x/nO9JVSFJ0jkLPQBJ0pnBQJAkAQaCJKkxECRJgIEgSWoMBEkSYCBIkhoDQZIEGAiSpGbpQg+gH8uXL6/h4eGFHsai8MYbb3DhhRcu9DCkabl9zp+XXnrpcFW9v5fasyoQhoeH2bFjx0IPY1HodDqMjo4u9DCkabl9zp8k3+m11kNGkiTAQJAkNQaCJAkwECRJjYEgSQIMBElSYyBIkgADQZLUnFVfTFP/kgzUz9/alt593ENY5Kpq2r8rPvfsjPMMA+ndyUCQJAEGgiSpMRAkSYCBIElqDARJEmAgSJKangIhyW1J9ibZl2TtSeruSlJJRqa0fzDJeJKHuto2JvnLJK8MPnxJ0nyZNRCSLAGeBG4HrgPuTXLdNHXLgAeBbdMs5gnguSltvwPc1ud4JUmnSC97CCuBfVX1WlUdA54G7pimbh2wHjja3Zjk54HXgd3d7VX1LeAHgwxakjT/egmEy4ADXdMHW9uPJbkJWFFVm6e0vxf4HPCFOY5TknSKzfleRknOYeKQ0H3TzH4U+NWqGh/0njpJ1gBrAIaGhuh0OgMtR+/ka6kz1fj4uNvnAuglEA4BK7qmL29tk5YBNwCd9qZ/CTCWZDWwCrg7yReBi4G3kxytqi/1OsCq2gBsABgZGanR0dFeu+pktm7G11Jnqk6n4/a5AHoJhO3AVUmuZCII7gE+OTmzqo4Ayyenk3SAh6pqB3BLV/ujwHg/YSBJOn1mPYdQVSeAB4DngT3AV6tqd5LH2l7AQJJ8Bfhj4OokB5P840GXJUmau57OIVTVFmDLlLZHZqgdnaH90SnT9/Y0QknSaeE3lSVJgIEgSWoMBEkSYCBIkhoDQZIEGAiSpGbOt67QmeFDX3iBI28d76vP8NrNsxd1ueiCc9n1+Y/11UfS2cNAWCSOvHWc/Y9/vOf6QW4N0G+ASDq7eMhIkgQYCJKkxkCQJAEGgiSpMRAkSYBXGS0ay65dy08/tba/Tk/1+xwAvV/JJOnsYiAsEn+153EvO5U0Jx4ykiQBBoIkqTEQJEmAgSBJagwESRLQYyAkuS3J3iT7ksx4bWOSu5JUkpEp7R9MMp7koX6XKUk6PWYNhCRLgCeB24HrgHuTXDdN3TLgQWDbNIt5Aniu32VKkk6fXvYQVgL7quq1qjoGPA3cMU3dOmA9cLS7McnPA68DuwdYpiTpNOnli2mXAQe6pg8Cq7oLktwErKiqzUke7mp/L/A54Fbgoa4usy6zaxlrgDUAQ0NDdDqdHob87tTPazM+Pj7Qa+nrr9Nh0O1TczPnbyonOYeJQ0L3TTP7UeBXq2o8yUDLr6oNwAaAkZGR6vfbte8aWzf39c3jQb6p3O9zSIMaaPvUnPUSCIeAFV3Tl7e2ScuAG4BOe9O/BBhLspqJT/13J/kicDHwdpKjwEuzLFOSdJr1EgjbgauSXMnEm/Y9wCcnZ1bVEWD55HSSDvBQVe0AbulqfxQYr6ovJVl6smVKkk6/WU8qV9UJ4AHgeWAP8NWq2p3ksbYX0LeZljnIsiRJ86OncwhVtQXYMqXtkRlqR2dof3S2ZUqSFo7fVJYkAQaCJKkxECRJgIEgSWoMBEkS4G8qLyp9/+bx1v7qL7rg3P6WL+msYiAsEvsf/3hf9cNrN/fdR9Li5iEjSRJgIEiSGgNBkgQYCJKkxkCQJAEGgiSpMRAkSYCBIElqDARJEmAgSJIaA0GSBBgIkqSmp0BIcluSvUn2JVl7krq7klSSkTa9MsnO9rcryZ1dtQ8meSXJ7iSfnfuqSJLmYta7nSZZAjwJ3AocBLYnGauqV6fULQMeBLZ1Nb8CjFTViSSXAruSfB24BvgUsBI4BmxN8mxV7ZuPlZIk9a+XPYSVwL6qeq2qjgFPA3dMU7cOWA8cnWyoqjer6kSbPB+o9vhaYFvX/P8CfGLAdZAkzYNeAuEy4EDX9MHW9mNJbgJWVNU7fnElyaoku4GXgU+3AHgFuCXJ+5K8B/j7wIoB10GSNA/m/AM5Sc4BngDum25+VW0Drk9yLfBUkueqak+S9cALwBvATuBHMyx/DbAGYGhoiE6nM9chq/G11JlqfHzc7XMB9BIIh/jJT++Xt7ZJy4AbgE4SgEuAsSSrq2rHZFELgfFWu6Oqvgx8GSDJv2Ziz+MdqmoDsAFgZGSkRkdHe1szndzWzfha6kzV6XTcPhdAL4eMtgNXJbkyyXnAPcDY5MyqOlJVy6tquKqGgReB1VW1o/VZCpDkCiZOJu9v0x9o/36QifMHvzd/qyVJ6tesewjtCqEHgOeBJcDGqtqd5DEmPumPnaT7zcDaJMeBt4H7q+pwm/cHSd4HHAf+SVX9cE5romm1vbbp562fuV9VzTxT0qLU0zmEqtoCbJnS9sgMtaNdjzcBm2aou6XnUWpgM72xu0suaSq/qSxJAgwESVJjIEiSAANBktQYCJIkwECQJDUGgiQJMBAkSY2BIEkCDARJUmMgSJIAA0GS1BgIkiTAQJAkNQaCJAkwECRJjYEgSQIMBElSYyBIkgADQZLU9BQISW5LsjfJviRrT1J3V5JKMtKmVybZ2f52Jbmzq/afJdmd5JUkX0ly/txXR5I0qFkDIckS4EngduA64N4k101Ttwx4ENjW1fwKMFJVNwK3Ab+ZZGmSy4B/2ubdACwB7pnrykiSBtfLHsJKYF9VvVZVx4CngTumqVsHrAeOTjZU1ZtVdaJNng9UV/1S4IIkS4H3AH8+wPglSfNkaQ81lwEHuqYPAqu6C5LcBKyoqs1JHp4ybxWwEbgC+KUWEIeS/ArwXeAt4IWqemG6J0+yBlgDMDQ0RKfT6WW9NIvx8XFfS52x3D4XRi+BcFJJzgGeAO6bbn5VbQOuT3It8FSS54ALmNjLuBL4IfD7Sf5hVf3uNP03ABsARkZGanR0dK5DFtDpdPC11JnK7XNh9HLI6BCwomv68tY2aRlwA9BJsh/4MDA2eWJ5UlXtAcZb7d8DXq+q71fVceAPgZ8ZdCUkSXPXSyBsB65KcmWS85g4+Ts2ObOqjlTV8qoarqph4EVgdVXtaH2WAiS5ArgG2M/EoaIPJ3lPkgAfBfbM54pJkvoz6yGjqjqR5AHgeSauBtpYVbuTPAbsqKqxk3S/GVib5DjwNnB/VR0GDid5Bvg2cAL4U9phIUnSwujpHEJVbQG2TGl7ZIba0a7Hm4BNM9R9Hvh8rwOVJJ1aflNZkgQYCJKkxkCQJAEGgiSpMRAkSYCBIElqDARJEmAgSJIaA0GSBBgIkqTGQJAkAQaCJKkxECRJgIEgSWoMBEkSYCBIkhoDQZIEGAiSpMZAkCQBPQZCktuS7E2yL8nak9TdlaSSjLTplUl2tr9dSe5s7Vd3te9M8n+TfHZ+VkmSNIilsxUkWQI8CdwKHAS2Jxmrqlen1C0DHgS2dTW/AoxU1YkklwK7kny9qvYCN3Yt/xDwtflYIUnSYGYNBGAlsK+qXgNI8jRwB/DqlLp1wHrg4cmGqnqza/75QE2z/I8C/6uqvtPHuCWd5ZIM1K9qurcRzYdeDhldBhzomj7Y2n4syU3AiqraPLVzklVJdgMvA5+uqhNTSu4BvtLXqCWd9apqxr8rPvfsjPN06vSyh3BSSc4BngDum25+VW0Drk9yLfBUkueq6mjrex6wGvjnJ1n+GmANwNDQEJ1OZ65DFjA+Pu5rqTOa2+fp10sgHAJWdE1f3tomLQNuADptF/ASYCzJ6qraMVlUVXuSjLfayfbbgW9X1f+e6cmragOwAWBkZKRGR0d7GLJm0+l08LXUGWvrZrfPBdDLIaPtwFVJrmyf6O8BxiZnVtWRqlpeVcNVNQy8CKyuqh2tz1KAJFcA1wD7u5Z9Lx4ukqQzwqyB0I75PwA8D+wBvlpVu5M8lmT1LN1vZuLKop1MXEV0f1UdBkhyIRNXLv3hXFZAkjQ/ejqHUFVbgC1T2h6ZoXa06/EmYNMMdW8A7+t1oJKkU8tvKkuSAANBktQYCJIkwECQJDUGgiQJMBAkSY2BIEkCDARJUmMgSJIAA0GS1BgIkiTAQJAkNQaCJAkwECRJjYEgSQIMBElSYyBIkoAefzFNkgb1oS+8wJG3jvfdb3jt5p5rL7rgXHZ9/mN9P4d+koEg6ZQ68tZx9j/+8b76dDodRkdHe67vJzw0s54OGSW5LcneJPuSrD1J3V1JKslIm16ZZGf725Xkzq7ai5M8k+TPkuxJ8pG5r44kaVCz7iEkWQI8CdwKHAS2Jxmrqlen1C0DHgS2dTW/AoxU1YkklwK7kny9qk4AvwZsraq7k5wHvGd+VkmSNIhe9hBWAvuq6rWqOgY8DdwxTd06YD1wdLKhqt5sb/4A5wMFkOQi4GeBL7e6Y1X1w4HXQpI0Z70EwmXAga7pg63tx5LcBKyoqnccyEuyKslu4GXg0y0grgS+D/x2kj9N8ltJLhx0JSRJczfnk8pJzgGeAO6bbn5VbQOuT3It8FSS59rz3gR8pqq2Jfk1YC3wL6dZ/hpgDcDQ0BCdTmeuQxYwPj7ua6nTpt9tbZDt0+157noJhEPAiq7py1vbpGXADUAnCcAlwFiS1VW1Y7KoqvYkGW+1B4GDLSwAnmEiEN6hqjYAGwBGRkaqnysPNLN+r+KQBrZ1c9/bWt/b5wDPoXfq5ZDRduCqJFe2k7/3AGOTM6vqSFUtr6rhqhoGXgRWV9WO1mcpQJIrgGuA/VX1PeBAkqvbYj4K/MRJaknS6TXrHkK7QugB4HlgCbCxqnYneQzYUVVjJ+l+M7A2yXHgbeD+qjrc5n0G+A8tZF4D/tFcVkSSNDc9nUOoqi3Aliltj8xQO9r1eBOwaYa6ncBIrwOVJJ1a3stIkgR46wpJp9iya9fy00/NeIODmT3Vz3MA9Hd7DL2TgSDplPqrPY97L6OzhIeMJEmAgSBJagwESRJgIEiSGgNBkgQYCJKkxkCQJAEGgiSpMRAkSYCBIElqDARJEmAgSJIaA0GSBHi3U0mnwUB3I93ae5+LLji3/+XrHQwESadUv7e+hokAGaSf5sZDRpIkwECQJDU9BUKS25LsTbIvyYy/hZfkriSVZKRNr0yys/3tSnJnV+3+JC+3eTvmviqSpLmY9RxCkiXAk8CtwEFge5Kxqnp1St0y4EFgW1fzK8BIVZ1IcimwK8nXq+pEm/9zVXV4PlZEkjQ3vewhrAT2VdVrVXUMeBq4Y5q6dcB64OhkQ1W92fXmfz5QcxyvJOkU6eUqo8uAA13TB4FV3QVJbgJWVNXmJA9PmbcK2AhcAfxSV0AU8EKSAn6zqjZM9+RJ1gBrAIaGhuh0Oj0MWbMZHx/3tdQZze3z9JvzZadJzgGeAO6bbn5VbQOuT3It8FSS56rqKHBzVR1K8gHgG0n+rKq+NU3/DcAGgJGRkRodHZ3rkMXEfzZfS52xtm52+1wAvRwyOgSs6Jq+vLVNWgbcAHSS7Ac+DIxNnlieVFV7gPFWS1Udav/+JfA1Jg5NSZIWSC+BsB24KsmVSc4D7gHGJmdW1ZGqWl5Vw1U1DLwIrK6qHa3PUoAkVwDXAPuTXNhOQpPkQuBjTJyAliQtkFkPGbUrhB4AngeWABuraneSx4AdVTV2ku43A2uTHAfeBu6vqsNJfgr4WpLJMfxeVW2d68pIkgbX0zmEqtoCbJnS9sgMtaNdjzcBm6apeQ34UD8DlSSdWn5TWZIEGAiSpMZAkCQBBoIkqTEQJEmAgSBJagwESRJgIEiSGgNBkgQYCJKkZs63v5akQbR7mc08f/307VX+ztap4h6CpAVRVTP+ffOb35xxnk4dA0GSBBgIkqTGQJAkAQaCJKkxECRJgIEgSWoMBEkSYCBIkpqcTV/0SPJ94DsLPY5FYjlweKEHIc3A7XP+XFFV7++l8KwKBM2fJDuqamShxyFNx+1zYXjISJIEGAiSpMZAePfasNADkE7C7XMBeA5BkgS4hyBJagyERSrJiiSvJ/nrbfqvtenhKXW/k+Tu9viWJLuT7ExywekftaSFZCAsUlV1APgN4PHW9Diwoar2n6TbLwL/pqpurKq3TvEQpVkl8VcdTyPPISxiSc4FXgI2Ap8CbgROAL8O3AocAI61+RcDXwSOAP+9qn5xIcasxaHtiW4FXgR+BtgO/DbwBeADTHz42M3EtngDcC7waFX9xyT3AZ8A3gssAX4O+BLwd5nYZo8DG6vqmSR/G3ii1R4G7quqvzgtK7kImb6LWFUdT/IwE/8xP9amPwFcDVwHDAGvMvGf67eS3Aw8W1XPLNyotYj8DeAfAL/MRCB8ErgZWA38Cya2vT+qql9OcjHwJ0n+U+t7E/A3q+oH7ZDmMBPb7AeAPcDG9oHn14E7qur7SX4B+Fft+TQAA2Hxux34CyY+hX0D+FngK1X1I+DPk/zRQg5Oi9rrVfUyQJLdwH+uqkryMhNv8JcDq5M81OrPBz7YHn+jqn7QHt8M/H5VvQ18L8k3W/vVtO06CUzsTbh3MAcGwiKW5EYmDg19GPivSZ5e4CHp3eX/dT1+u2v6bSbee34E3FVVe7s7JVkFvNHD8gPsrqqPzMNYhSeVF61MfGT6DeCzVfVd4N8CvwJ8C/iFJEuSXMrE8VlpITwPfKZtqyT5WzPU/TfgriTnJBkCRlv7XuD9ST7S+p+b5PpTPOZFzUBYvD4FfLeqvtGm/x1wLfB/gP/JxPHbfw/88cIMT2IdEyeT/0c7pLRuhro/AA4ysc3+LvBt4EhVHQPuBtYn2QXsZOIEtgbkVUaSznhJ3ltV40neB/wJ8Heq6nsLPa7FxnMIks4Gz7Yrkc4D1hkGp4Z7CJIkwHMIkqTGQJAkAQaCJKkxECRJgIEgSWoMBEkSAP8fE1jU+n6udDwAAAAASUVORK5CYII=\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "scores = pd.DataFrame({'Xdf': scores_Xdf, 'merge': scores_merge})\n",
    "scores.boxplot()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Record linkage\n",
    "\n",
    "The problem of trying to match 'records' referring to the same entity across different data sources is called  record linkage. We used a very naive way to match companies above but there are much more sophiisticated methods and packages implementing these methods. To get you started here are two Python packages:\n",
    "\n",
    "* [Python Record Linkage Toolkit](https://recordlinkage.readthedocs.io/en/latest/index.html)\n",
    "* [Dedupe](https://github.com/dedupeio/dedupe)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Submission structure\n",
    "\n",
    "Each of your submissions should be in it's own folder within the `submissions` folder (e.g. `submissions/my_submission`). The submission directory should contain 3 files:\n",
    "\n",
    "* a copy of the `award_notices_RAMP.csv` dataset. Note that you will **not** need to upload this file when making a submission in RAMP studio as this is done automatically by RAMP studio.\n",
    "* `feature_extractor.py` - this should merge the `X_df` and the award dataset and any feature transformation you wish\n",
    "* `regressor.py` - this should implement a regressor with a `fit()` and `predict()` function\n",
    "\n",
    "See `submissions/starting_kit` for an example.\n",
    "\n",
    "You can also test your submissions using RAMP-workflow before submitting:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Local testing (before submission)\n",
    "\n",
    "It is <b><span style=\"color:red\">important that you test your submission files before submitting them</span></b>. For this we provide a unit test - `ramp_test_submission`. \n",
    "\n",
    "First, ensure that `ramp-worflow` is installed (see the [github repo](https://github.com/paris-saclay-cds/ramp-workflow) for installation instructions). \n",
    "\n",
    "Now you can use `ramp_test_submission`. This command will test on files in [`submissions/starting_kit`](/submissions/starting_kit) by default. To specify testing on a different folder use the flag `--submission`. For example to run the test on `submissions/solution1` use: `ramp_test_submission --submission solution1`.\n",
    "\n",
    "If it runs and print training and test errors on each fold, then you can submit the code.\n",
    "\n",
    "For example, below we test the starting kit:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[38;5;178m\u001b[1mTesting Prediction of annual revenue using FAN\u001b[0m\n",
      "\u001b[38;5;178m\u001b[1mReading train and test files from ./data ...\u001b[0m\n",
      "\u001b[38;5;178m\u001b[1mReading cv ...\u001b[0m\n",
      "\u001b[38;5;178m\u001b[1mTraining submissions/starting_kit ...\u001b[0m\n",
      "\u001b[38;5;178m\u001b[1mCV fold 0\u001b[0m\n",
      "\t\u001b[38;5;178m\u001b[1mscore  fan error       time\u001b[0m\n",
      "\t\u001b[38;5;10m\u001b[1mtrain\u001b[0m       \u001b[38;5;10m\u001b[1m0.14\u001b[0m  \u001b[38;5;150m53.650165\u001b[0m\n",
      "\t\u001b[38;5;12m\u001b[1mvalid\u001b[0m       \u001b[38;5;12m\u001b[1m0.44\u001b[0m  \u001b[38;5;105m15.004957\u001b[0m\n",
      "\t\u001b[38;5;1m\u001b[1mtest\u001b[0m        \u001b[38;5;1m\u001b[1m0.45\u001b[0m   \u001b[38;5;218m8.995590\u001b[0m\n",
      "\u001b[38;5;178m\u001b[1mCV fold 1\u001b[0m\n",
      "\t\u001b[38;5;178m\u001b[1mscore  fan error       time\u001b[0m\n",
      "\t\u001b[38;5;10m\u001b[1mtrain\u001b[0m       \u001b[38;5;10m\u001b[1m0.14\u001b[0m  \u001b[38;5;150m52.364490\u001b[0m\n",
      "\t\u001b[38;5;12m\u001b[1mvalid\u001b[0m       \u001b[38;5;12m\u001b[1m0.45\u001b[0m  \u001b[38;5;105m14.729081\u001b[0m\n",
      "\t\u001b[38;5;1m\u001b[1mtest\u001b[0m        \u001b[38;5;1m\u001b[1m0.46\u001b[0m   \u001b[38;5;218m8.808442\u001b[0m\n",
      "\u001b[38;5;178m\u001b[1mCV fold 2\u001b[0m\n",
      "\t\u001b[38;5;178m\u001b[1mscore  fan error       time\u001b[0m\n",
      "\t\u001b[38;5;10m\u001b[1mtrain\u001b[0m       \u001b[38;5;10m\u001b[1m0.17\u001b[0m  \u001b[38;5;150m52.110984\u001b[0m\n",
      "\t\u001b[38;5;12m\u001b[1mvalid\u001b[0m       \u001b[38;5;12m\u001b[1m0.46\u001b[0m  \u001b[38;5;105m15.061674\u001b[0m\n",
      "\t\u001b[38;5;1m\u001b[1mtest\u001b[0m        \u001b[38;5;1m\u001b[1m0.44\u001b[0m   \u001b[38;5;218m8.763458\u001b[0m\n",
      "\u001b[38;5;178m\u001b[1mCV fold 3\u001b[0m\n",
      "\t\u001b[38;5;178m\u001b[1mscore  fan error       time\u001b[0m\n",
      "\t\u001b[38;5;10m\u001b[1mtrain\u001b[0m       \u001b[38;5;10m\u001b[1m0.13\u001b[0m  \u001b[38;5;150m50.799010\u001b[0m\n",
      "\t\u001b[38;5;12m\u001b[1mvalid\u001b[0m       \u001b[38;5;12m\u001b[1m0.44\u001b[0m  \u001b[38;5;105m14.852118\u001b[0m\n",
      "\t\u001b[38;5;1m\u001b[1mtest\u001b[0m        \u001b[38;5;1m\u001b[1m0.46\u001b[0m   \u001b[38;5;218m8.665419\u001b[0m\n",
      "\u001b[38;5;178m\u001b[1mCV fold 4\u001b[0m\n"
     ]
    }
   ],
   "source": [
    "!ramp_test_submission"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Submitting to [ramp.studio](http://ramp.studio)\n",
    "\n",
    "Once you found a good model, you can submit it to [ramp.studio](http://www.ramp.studio). First, if it is your first time using RAMP, [sign up](http://www.ramp.studio/sign_up), otherwise [log in](http://www.ramp.studio/login). Then sign up for the 'FAN_revenue_prediction' challenge and the 'Saclay M2 Data Camp 2019/20' [event](https://ramp.studio/events/fan_revenue_prediction_saclay_datacamp_19). Both signups are controlled by RAMP administrators, so there **can be a delay between asking for signup and being able to submit**.\n",
    "\n",
    "Once your signup request is accepted, you can go to your [sandbox](https://ramp.studio/events/fan_revenue_prediction_saclay_datacamp_19/sandbox). Here you can either edit and save the code files on the left hand side of the page or upload your `feature_extractor.py` and `regressor.py` files on the right hand side of the page, then give your submission a name and click 'submit'. The submission is trained and tested on our backend in the same way as `ramp_test_submission` does it locally. While your submission is waiting in the queue and being trained, you can find it in the \"New submissions (pending training)\" table in [my submissions](https://ramp.studio/events/fan_revenue_prediction_saclay_datacamp_19/my_submissions). Once it is trained, you get an email, and your submission shows up on the [public leaderboard](https://ramp.studio/events/fan_revenue_prediction_saclay_datacamp_19/leaderboard). \n",
    "If there is an error (despite having tested your submission locally with `ramp_test_submission`), it will show up in the \"Failed submissions\" table in [my submissions](https://ramp.studio/events/fan_revenue_prediction_saclay_datacamp_19/my_submissions). You can click on the error to see part of the trace.\n",
    "\n",
    "After submission, do not forget to give credits to the previous submissions you reused or integrated into your submission.\n",
    "\n",
    "The data set we use at the backend is usually different from what you find in the starting kit, so the score may be different.\n",
    "\n",
    "The usual way to work with RAMP is to explore solutions, add feature transformations, select models, perhaps do some AutoML/hyperopt, etc., _locally_ then check that your submission works locally with `ramp_test_submission`. The script will print mean cross-validation scores if your submission works.\n",
    "\n",
    "The official score in this RAMP (the first score column after \"historical contributivity\" on the [leaderboard](https://ramp.studio/events/fan_revenue_prediction_saclay_datacamp_19/leaderboard)) is `FAN error`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## More information\n",
    "\n",
    "You can find more information in the [README](https://github.com/paris-saclay-cds/ramp-workflow/blob/master/README.md) of the [ramp-workflow library](https://github.com/paris-saclay-cds/ramp-workflow)."
   ]
  }
 ],
 "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.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}