01_addcontr_explore.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "from datetime import date\n",
    "from tqdm import tqdm_notebook"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(24524, 19)\n",
      "Index(['contract_id', 'date_signed', 'procuring_entity', 'procuring_entity_id',\n",
      "       'procuring_entity_type', 'region', 'contract_value', 'quantity',\n",
      "       'units', 'item_desc', 'cpv', 'contract_start', 'contract_end',\n",
      "       'addcontr_date', 'rationale_type', 'rationale', 'tender_id',\n",
      "       'procurement_type', 'tenderURL'],\n",
      "      dtype='object')\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>contract_id</th>\n",
       "      <th>date_signed</th>\n",
       "      <th>procuring_entity</th>\n",
       "      <th>procuring_entity_id</th>\n",
       "      <th>procuring_entity_type</th>\n",
       "      <th>region</th>\n",
       "      <th>contract_value</th>\n",
       "      <th>quantity</th>\n",
       "      <th>units</th>\n",
       "      <th>item_desc</th>\n",
       "      <th>cpv</th>\n",
       "      <th>contract_start</th>\n",
       "      <th>contract_end</th>\n",
       "      <th>addcontr_date</th>\n",
       "      <th>rationale_type</th>\n",
       "      <th>rationale</th>\n",
       "      <th>tender_id</th>\n",
       "      <th>procurement_type</th>\n",
       "      <th>tenderURL</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>244af7a6ee364ed3a8b3cff3cedabc3f</td>\n",
       "      <td>2017-08-01</td>\n",
       "      <td>Державний вищий навчальний заклад \"Університет...</td>\n",
       "      <td>34716922</td>\n",
       "      <td>general</td>\n",
       "      <td>Київська область</td>\n",
       "      <td>1093519.824</td>\n",
       "      <td>162450</td>\n",
       "      <td>MTQ</td>\n",
       "      <td>Природний газ для потреб Львівського навчально...</td>\n",
       "      <td>09120000-6</td>\n",
       "      <td>2017-08-01</td>\n",
       "      <td>2017-12-31</td>\n",
       "      <td>2017-10-11</td>\n",
       "      <td>volumeCuts, itemPriceVariation</td>\n",
       "      <td>Згідно довідки Торгово-промислової палати Укра...</td>\n",
       "      <td>UA-2017-05-15-002292-b</td>\n",
       "      <td>aboveThresholdUA</td>\n",
       "      <td>https://prozorro.gov.ua/tender/UA-2017-05-15-0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>28cf7ff64b564839b13c8c91c98676dc</td>\n",
       "      <td>2017-08-02</td>\n",
       "      <td>Квартирно - експлуатаційний відділ м. Херсона</td>\n",
       "      <td>8294952</td>\n",
       "      <td>general</td>\n",
       "      <td>Херсонська область</td>\n",
       "      <td>1124468.370</td>\n",
       "      <td>202501</td>\n",
       "      <td>MTQ</td>\n",
       "      <td>Природний газ</td>\n",
       "      <td>09120000-6</td>\n",
       "      <td>2017-08-02</td>\n",
       "      <td>2017-12-31</td>\n",
       "      <td>2017-08-29</td>\n",
       "      <td>volumeCuts</td>\n",
       "      <td>Зменшення обсягів закупівлі з метою приведення...</td>\n",
       "      <td>UA-2017-06-21-000460-b</td>\n",
       "      <td>aboveThresholdUA</td>\n",
       "      <td>https://prozorro.gov.ua/tender/UA-2017-06-21-0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>28cf7ff64b564839b13c8c91c98676dc</td>\n",
       "      <td>2017-08-02</td>\n",
       "      <td>Квартирно - експлуатаційний відділ м. Херсона</td>\n",
       "      <td>8294952</td>\n",
       "      <td>general</td>\n",
       "      <td>Херсонська область</td>\n",
       "      <td>1124468.370</td>\n",
       "      <td>202501</td>\n",
       "      <td>MTQ</td>\n",
       "      <td>Природний газ</td>\n",
       "      <td>09120000-6</td>\n",
       "      <td>2017-08-02</td>\n",
       "      <td>2017-12-31</td>\n",
       "      <td>2017-09-20</td>\n",
       "      <td>itemPriceVariation</td>\n",
       "      <td>Підвищення вартості природного газу на ринку У...</td>\n",
       "      <td>UA-2017-06-21-000460-b</td>\n",
       "      <td>aboveThresholdUA</td>\n",
       "      <td>https://prozorro.gov.ua/tender/UA-2017-06-21-0...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        contract_id date_signed  \\\n",
       "0  244af7a6ee364ed3a8b3cff3cedabc3f  2017-08-01   \n",
       "1  28cf7ff64b564839b13c8c91c98676dc  2017-08-02   \n",
       "2  28cf7ff64b564839b13c8c91c98676dc  2017-08-02   \n",
       "\n",
       "                                    procuring_entity  procuring_entity_id  \\\n",
       "0  Державний вищий навчальний заклад \"Університет...             34716922   \n",
       "1      Квартирно - експлуатаційний відділ м. Херсона              8294952   \n",
       "2      Квартирно - експлуатаційний відділ м. Херсона              8294952   \n",
       "\n",
       "  procuring_entity_type              region  contract_value  quantity units  \\\n",
       "0               general    Київська область     1093519.824    162450   MTQ   \n",
       "1               general  Херсонська область     1124468.370    202501   MTQ   \n",
       "2               general  Херсонська область     1124468.370    202501   MTQ   \n",
       "\n",
       "                                           item_desc         cpv  \\\n",
       "0  Природний газ для потреб Львівського навчально...  09120000-6   \n",
       "1                                      Природний газ  09120000-6   \n",
       "2                                      Природний газ  09120000-6   \n",
       "\n",
       "  contract_start contract_end addcontr_date                  rationale_type  \\\n",
       "0     2017-08-01   2017-12-31    2017-10-11  volumeCuts, itemPriceVariation   \n",
       "1     2017-08-02   2017-12-31    2017-08-29                      volumeCuts   \n",
       "2     2017-08-02   2017-12-31    2017-09-20              itemPriceVariation   \n",
       "\n",
       "                                           rationale               tender_id  \\\n",
       "0  Згідно довідки Торгово-промислової палати Укра...  UA-2017-05-15-002292-b   \n",
       "1  Зменшення обсягів закупівлі з метою приведення...  UA-2017-06-21-000460-b   \n",
       "2  Підвищення вартості природного газу на ринку У...  UA-2017-06-21-000460-b   \n",
       "\n",
       "   procurement_type                                          tenderURL  \n",
       "0  aboveThresholdUA  https://prozorro.gov.ua/tender/UA-2017-05-15-0...  \n",
       "1  aboveThresholdUA  https://prozorro.gov.ua/tender/UA-2017-06-21-0...  \n",
       "2  aboveThresholdUA  https://prozorro.gov.ua/tender/UA-2017-06-21-0...  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'''\n",
    "Import data, previously downloaded by the script 00_addcontr_get_data\n",
    "'''\n",
    "\n",
    "data_addcontr = pd.read_excel('02_data/00_data_addcontr_all_v5.xlsx','Sheet1')\n",
    "print(data_addcontr.shape)\n",
    "print(data_addcontr.columns)\n",
    "\n",
    "data_addcontr.loc[:,'date_signed'] = pd.to_datetime(data_addcontr['date_signed']).map(lambda x: x.date())\n",
    "data_addcontr.loc[:,'addcontr_date'] = data_addcontr['addcontr_date'].map(lambda x: pd.to_datetime(x).date() if x != 'unknown' else x)\n",
    "data_addcontr['contract_start'] = data_addcontr.loc[:,'contract_start'].map(lambda x: pd.to_datetime(x).date() if x != 'not specified' else x)\n",
    "data_addcontr['contract_end'] = data_addcontr.loc[:,'contract_end'].map(lambda x: pd.to_datetime(x).date() if x != 'not specified' else x)\n",
    "\n",
    "data_addcontr.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "3647\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>contract_start</th>\n",
       "      <th>contract_end</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>19572</th>\n",
       "      <td>2017-08-01</td>\n",
       "      <td>2017-12-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>378</th>\n",
       "      <td>2017-08-01</td>\n",
       "      <td>2017-12-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>2017-08-01</td>\n",
       "      <td>2018-01-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107</th>\n",
       "      <td>2017-08-01</td>\n",
       "      <td>2017-12-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017-08-01</td>\n",
       "      <td>2017-12-31</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      contract_start contract_end\n",
       "19572     2017-08-01   2017-12-31\n",
       "378       2017-08-01   2017-12-31\n",
       "123       2017-08-01   2018-01-31\n",
       "107       2017-08-01   2017-12-31\n",
       "0         2017-08-01   2017-12-31"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'''\n",
    "Create table with duration of each contract,\n",
    "starting from 2017-08-01 and ending no later than 2018-12-31\n",
    "'''\n",
    "contract_dates = data_addcontr.drop_duplicates(subset = 'contract_id')[['contract_start','contract_end']]\n",
    "contract_dates = contract_dates.loc[(contract_dates['contract_start']!='not specified')&(contract_dates['contract_end']!='not specified')].sort_values(by = ['contract_start'])\n",
    "contract_dates = contract_dates.loc[(contract_dates['contract_start']>=date(2017,8,1))&(contract_dates['contract_end']<=date(2018,12,31))]\n",
    "print(len(contract_dates))\n",
    "contract_dates.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "449821199bba4e31ada0e5bf80c557e1",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(IntProgress(value=0, max=3647), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAY4AAAEcCAYAAADQqlM0AAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDIuMi4yLCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvhp/UCwAAIABJREFUeJztvXucZFV5r/98q3t6ZrjIXURgBCNek+Ml8wPzMxfEGxoVTeL1qERM8JxgjDEn8ZJEjYgHPREPxltGASERBgQNE4IiooBoEBgkchXG4TINw1yqeqa7p6vrtt/zx949U9N0d93WrtpV9T7z2Z+pWnvvd39XddV+91rvu9aSmeE4juM4zZLrtQDHcRynv3DH4TiO47SEOw7HcRynJdxxOI7jOC3hjsNxHMdpCXccjuM4Tku443Acx3Fawh2H4ziO0xLuOBzHcZyWcMfhOI7jtMRorwWkwaGHHmrHHHNMr2U4juP0FevXr99uZoc1Om4gHccxxxzDbbfd1msZjuM4fYWkh5s5zruqHMdxnJZwx+E4juO0hDsOx3EcpyXccTiO4zgt4Y7DcRzHaQl3HI7jOE5LuONwus6ll17KpZde2msZjuO0yUCO43Cyzfj4eK8lOH1KrVbj0fIuZq224H4zo7TIvnqiKGIyquw+p2zRXjbq3+9lu1ZlV1ShimFmVCxCAFJT+s2MKtbUse3aOG3/X+PAAw/s6BqNcMfhOE5PMDN2VctM1MpUq1UenN3JlkqRCtHum/LcDbJarbLTKpRrVYpW5Ym39T02F9tXTxRFVBc5ZzEbZkbNImoYBhhGhMWOgyYdR3JuJzSycUr5QHccjuP0N1EUsb1c5LHpHdxc2h4/0Uc1ilGV2ahKmYhqtcp0tcKs7X1TnrtBVqlSSd6vYIRcBnrZhRhp0mHUn5U2IyMjqV/DHYfjOEEws9gZzM7y4+nHKVSKTEYVZmoVdkUVds5Os4PYKdTY8+TeLEIIEBE5Or855hKrg0Yul75TdcfhOM5eVCoV7i3tAOKYwpbyDPlykUmr7O46mms1lJNOHTOjmnTjzM7Osj2qUKZGpQ0H0YhSrLJjO4PqOCqVzj+bRqTmOCStAG4ElifXudzMPi7pG8DvATuTQ//YzO6QJOBc4DXATFJ+e2LrVODvkuM/ZWYXpqXbcQaJKIooVEt7WgNW2x1Arg8QR1HEdFSlVCpxf3GCPGUgdhzFWo1Zq8UB4cTuXKshqnvfyDmEulEP6g2/n0izxVECTjKzaUnLgJskfTfZ99dmdvm8418NHJdsJwBfAU6QdDDwcWA1YMB6SevMbCJF7Y6TSeYCyoVqaXdmkZkxUy2zrTpL2SKiKGJXrcKu8iyba0WK1HYHdiP2BH/rA8Rzr0tRiWKdQwjJKPIupi7Q1zEOMzNgOnm7LNmWTAYALkrOu1nSgZKOAE4ErjWzAoCka4GTgUvS0u44vaZcLrN+11a2lovsqJUoVsoUqMTZRlEtbjHUdRtVajVK9Q7BjBJlSoG7iTqhloS9OyWOc7jj6CWpxjgkjQDrgWcAXzKzn0n6n8BZkj4GXAd82MxKwJHAprrTx5OyxcrnX+t04HSAVatWpVAbx0mPcrnMHbN5SqUSd88U2FyaZnutRDGKu4jKVFru1R9BjGQg+6ieKJjjyIo7zB7xs3e6pOo4zKwGvEDSgcB3JP068BHgcWAMWAN8CPgkC7c9bYny+ddak9hj9erV/q1y+oYdO3bw5W13s40ypXKJHVQoB2gpHMwYY8uWBdGYJXKAmhxwN4ysXLky9Wt0JavKzHZIuh442cz+MSkuSboA+F/J+3Hg6LrTjgIeS8pPnFd+fZp6HSctoiji0akd3DSzhQoRpVKJe3dtYROV3XGF0UAthVGMlQH6u3PIb9R9RF/HOCQdBlQSp7ESeDnwGUlHmNnmJIvqDcBdySnrgPdJWkscHN+ZHHcN8GlJByXHvZK41eI4mcfM2FHcxc92bWVHrUyxWOSeYp68xT3+JUpJeukeBEEcxxQRu2ZnO7Yj/Am/H8jFc59QGpv/jQpPmi2OI4ALkzhHDrjMzK6S9MPEqQi4A/gfyfFXE6fibiBOx303gJkVJJ0J3Joc98m5QLnjZI1yuczPi9uZKs3yUGWaYqXM46Vp8rUyFaBiJUp79b7mnvAjjH8wnd+oI2iYH9V0hlIX+s2dzshZ/L3p6xiHmf0CeOEC5SctcrwBZyyy73zg/KACHScwk5OTfO3xu3jIisxWyxQxKlR3p7xCfKNudLM2wgSRQ+Hpr/1BMhSzvx2H4wwbhUKBWyvbl8x+mvtxL0Xr8x+1TzN6FstQ6XdGk/wsp3XccThOICqV5lJmG92sq7tnXO2MQ1nB2Fj6gdJ+ZRSRyw2e41ixYkXq13DH4TgdEkURm3YW+Fb54SCxiVC3siplxug8NXNQ018NqA1g7Ma7qhwn40xOTnLB9vvYWCywnUowxxFiZPQkxmR5JjN6ssagxm6Ko8XUr+GOw3HaZMeOHZzz6HruZxcVQq/DPHhPwlkjaw4xlCOLojRmGtsbdxyO0yI7d+7kwslfsWVyO/czuzvhtZlA8zAzsntFDWch+ikJwR2H4zRJrVbjvvzjfHPbPTxEmSq1vUZJDGrXRyhEmPEpTu9xx+E4DSiVSvysuI2dO3dy9fRGti0yj1Q/PTG2QkiHmKXxKYOKB8cdJwMUCgUun7qf6cr07tXHFmJurWzH6SXuOBynx8zMzHBF4X42swtQxiYpH068S3BpupE67Y7DcRahUqlw02O/4j/ZmcqKeM5gMgyOzR2H4yxCoVDg8tKDNDu/bOgbxjDcgAaRXqf5eovDcbrM7OwsN+56nLJFjG8fJ99rQU7f0etYl8c4HKeLTE5OsmbznWy0IhEwQ+ejrtvBWxpO1nHH4TgJ+XyeW6v5vaZBb4VQ6bi97upw+hvvqnKcLlEsFrlmZlPbTgPCdVHEjsPTep328K4qx0mZcrnMTdsf5oc7x3mIqV7LAeKpS9xxOO3ijsNxUqZQKHDJxH1MBGgrhFoYaIyRIFNzeKxkOBkbG0v9Gqk5DkkrgBuB5cl1Ljezj0s6FlgLHAzcDrzTzMqSlgMXAb8J5IG3mNlDia2PAO8BasD7zeyatHQ7w8X09DQ7IMj0ezlELsAQwWXASK7zBZgGdR0NZ2lGRtJfvKstxyHptcQ3/hvM7OFFDisBJ5nZtKRlwE2Svgt8EPi8ma2V9FVih/CV5P8JM3uGpLcCnwHeIum5wFuB5wFPBX4g6ZlmVmtHu+PUY2ZNzZ/UzNN7DSMKMFSwBqgLU2M7g0mpVEr9Gg0dh6R184uA3wb+O7FzWBCLO9qmk7fLks2Ak4C3J+UXAp8gdhynJK8BLge+qPhx6RRgrZmVgAclbQCOB/6zkXbH6SZh8/c9xpE2g9qVl5UYx3OAP6l7L+DZZnZ1oxMljQDrgWcAXwJ+Bewws7nklXHgyOT1kcAmADOrStoJHJKU31xntv4cx+kKzay1Mag3IseZTzOOY8rMbqgvkNRU+knSnfQCSQcC3yF2Qk84bM7sIvsWK98LSacDpwOsWrWqGXmOE5RBnVZ9UBnUhbey0uJ4XtI9VCB+2r8KWNHKRcxsh6TrgRcDB0oaTVodRwGPJYeNA0cD45JGgQPqrnl0nbn6c+qvsQZYA7B69erB+zY4walWq2wsh0vB7fVUE2nhLSlnPs04jqcCI8B+wLHAm4BnSfpd4B4z277QSZIOAyqJ01gJvJw44P0j4I+IM6tOBa5MTlmXvP/PZP8PzcySGMvFks5JtBwH3NJOZR2nnkKhwHcY9xtjA/zz6S8yMXLczObmedsKbASuk/QL4KXA9mRbiCOAC5M4Rw64zMyuknQPsFbSp4CfA+clx58H/Etd6+atyfXvlnQZcA9QBc7wjConBDt37mQbBBp9MbgMapfOoJKVriokPR/4neTtj83sy43OMbNfAC9coHwjcVbU/PJZ4tbMQrbOAs5qRqvjtEqoLiZ/MneGhYajlST9BfBN4MnJ9q+S/jxtYY6TFrVajUeKk0HjG+DP5M7w0EyL4z3ACWa2C0DSZ4jjEP+UpjDHSYMoitiwdTPfmNnIBDuC2vbgeH8xqPXKRIyD+JOtjynUGMRP2xkKCoUCXyr8F5uTqQR9DfHGDOoNdlDrlRXHcQHwM0nfSd6/gT0BbcfpGwqFAv97yy08iudWtIIHx/uLWi3973czWVXnJGMwfpvYPb/bzH6etjDHCU2hUODhBVbcGNQnT2c4yUSLQ9L7zOyLxDPZOk7fYrZwBCLUrLaOkwVyufS/y810VZ0GfDFtIY6TJmZGMVp4fb8oCWs7ziCQmXEcjtOvRFHEr7Y9zsUzD7G9Wlj4GAgyHbp3eTlZICuO4yhJX5hfaGbvT0GP4wSlUCjwhe2389gSjiHUDT8ege6Ow+ktmYhxAH+dugrHSYmJiYklnQb4Dd9xWqUZx5EHrjYzX5LM6SsmJye5uPxgw+OyNnAvVAvIu86Gk6y0ON4KnCvpCuACM7s3ZU2O0zFmxqPbt3IfJXINbp5Zu8F615nTCZlwHGb2DklPAt4GXCDJiAcFXmJmYSf7cZxA5PN5vly8j3IXWxKjGZtnN2stKac7ZCU4jplNJi2OlcAHgDcCfy3pC2bmc1Y5maOZ2MYcoUZG1zCfxMTpOZlwHJJeRzyW49eAfwGON7OtkvYB7sUnO3QyRrFY5Lry5q5fN2tP+FnrgnMGh2ZaHG8CPm9mN9YXmtmMpNPSkeU47VGr1bhryzg/DTzz7TDjDqi/yEqM411L7LsurBzH6YxCocA3dv2SYgudRiPIg8hL4I6jv8iE43CcfqFSqXDz1ofZ2mJ3US5QbMJvsE4W6GvHIelo4CLgKcTxxzVmdq6kTwB/CmxLDv2omV2dnPMR4oWjasD7zeyapPxk4FxgBPi6mZ2dlm6nf8nn81zZdEh8D1WEfMqRRfF6OfNpJjj+ZDPbOq/sWWb2ywanVoG/MrPbJe0PrJd0bbLv82b2j/NsPpd4zMjzgKcCP5D0zGT3l4BXAOPArZLWmdk9jbQ7w0OpVOIH+Y1MtHGuoOFYD8dx9tBMi+PHkv7ezC4DkPRXxK2C5y51kpltBjYnr6ck3QscucQppwBrzawEPChpA3B8sm+DmW1Mrr82OdYdh7ObQqHAj8i35ADmnjhzuONwek+wgZ8Z6ao6EVgj6U3A4cQpuMcvecY8JB0DvBD4GfAS4H2S3gXcRtwqmSB2KjfXnTbOHkezaV75Ca1c3xl8pqen2dFmKmzcSeWrAg4bWRtZH0pPN1YAbBgRTFoO3wN+CzgGuMjMppu9gKT9gCuAD5jZJPAV4jEhLyBukXxu7tCFLr9E+fzrnC7pNkm3bdu2bYFTnEGlWq2ysdz6JAbxdOoWbPM1PfoLI+zfv9OthlEl6niLovSnFWzoOJK4xAnArwOvAT4v6R+XPmv3ucuIncY3zezbAGa2xcxqyaSJX2NP62UcOLru9KOAx5Yo3wszW2Nmq81s9WGHHdaMPGdAyOfzXMF4r2W423CGhma6qr5kZv+WvN4h6f8HPtLoJMUdbecB95rZOXXlRyStGIinLrkreb0OuFjSOcTB8eOAW4hbHMdJOhZ4lDiA/vYmdDtDQKFQ4LPb15PvtRCyN3LcGU4yMeVIndOYe18FzmzC9kuAdwJ3SrojKfso8DZJLyD+nT0EvDexe7eky4iD3lXgDDOrQbzuOXANcTru+WZ2dxPXd4aAfD7PQyy8JGy3yVp6Z9b0ON0hE2uOS5piTyt87ltoZvakpc4zs5tY+Ft79RLnnAWctUD51Uud5zidMKjrX2RNj9MdspJVdS5wEnCWmf1Hynocp+vEU45kZ+S43/CdTshEi8PM/k7SYcDfS/og8DEz+0nqyhynS4QaABj/mLLjgJzhJBOOQ9KLkpffAI4Fvixpk5m9Nk1hjtMtQqXS1hAKYCfU+iChcEfWX2QiOM6ecRZzFIB9U9DiOD2hBkQB5qqqka0BZaEI4Tjc+XSPbozjaKar6qWpq3CcNpGUmelCmknH7ccbaNZaQM7SZKLFIeljC5Wb2SfDy3Gc1jCbG7XtZJlBdj5ZexjIhOMAdiX/fwD4vylqcZyBZ5BvoMPKYvMihSJrjgma66r6HICkd8y9dpxBIms/zKzpcXpLq9+HrIzjmMMfkxynC6T9BOv0F622UjPRVSXp34lVP13SurlyM3t9msIcp1tks/soa3qcrDK/RZIJxwHMzYTr3VROpoiiiEK11LEd7xpy+pn5399MdFWZ2Q2SngYcZ2Y/kLQP8WSDjtNTCoUC34we7jgdt/6H507EaZWsfWeyMnL8T4HTgYOJF2A6Evgq8LJ0pTnO0uzYsYPNAQbu1XdVeXzB6Xey0lV1BvFiSz8DMLMHJD05VVWO0yN8TY30yNqTeSiyFiPrhuNopk1TMrPy3BtJo2TpU3Icpy/wm8bg0EyL4wZJHwVWSnoF8GfAv6cry3G6x6A+CTtOWjTjOD4MvAe4k3i1vquBr6cpynEaMTU1xaWlBwkRlchaXMMdWXcY1M85E8FxM4uAryUbEE+1Lmk/4B4z256iPsdZkEKhwF0Ug9gavFuH46RLM1lVX1ig+C3APwCPAgs6DklHAxcBTyGOH60xs3MlHQxcChxDvOb4m81sQnHy8bnAa4AZ4I/N7PbE1qnA3yWmP2VmFzZbQWcwKZfL7A68ZaT3PNQTbNaCrYPKoH7OmZhWHTgFmD9D7uvN7MsNzqsCf2Vmt0vaH1gv6Vrgj4HrzOxsSR8m7gr7EPBq4LhkOwH4CnBC4mg+Dqwm/iuvl7TOzCaaqqEz0GSpu0HJP8fpJZkYAAjk5z/hS/pAo5PMbDOwOXk9Jele4jEgpwAnJoddCFxP7DhOAS6yOJfsZkkHSjoiOfZaMysk174WOBm4pAntjtM1Qi1BmyVn6PQXOWBkJP3x2c04juMk/YB45b9x4Cpa/FZLOgZ4IfFYkMMTp4KZba4bE3IksKnutPGkbLFyZwgpFAp8deoB8uU8kK3uhioWYMXx+MflLRenHXJkp6vqROIpRvYjXnP8fwG/kcQwtpvZkhHKJIh+BfABM5tcohm10I7FEl6ecKeQdDrxCHdWrVq1lCSnj8nn86wvb+m1jEUJt6hUNpyh03/UarXUr9FMVtX6eUXnSfoscXD8K8Cti50raRmx0/immX07Kd4i6YiktXEEsDUpHweOrjv9KOCxpPzEeeXXL6BzDbAGYPXq1f6rGxJCdOssIxfkCX8EUJA2h+O0z7Jly1K/RlvfcjP7GzM7zcyWchoCzgPuNbNz6natA05NXp8KXFlX/i7FvBjYmXRpXQO8UtJBkg4CXpmUOUPI3BrjITdhgex495IzHDSTjvti4J+A5wBjxA9W02Z2QINTXwK8E7hT0h1J2UeBs4HLJL0HeAR4U7LvauJU3A3E6bjvBjCzgqQz2dOy+eRcoNwZLnbu3MmFpV/t1R0UIsZRrbPUCc20fjzw7aRNtVpN/RrNxDi+CLwV+BZxSuy7iFNml8TMbmLxX8gTZtZNsqnOWMTW+cD5TWh1BphCocD9dL7+RlpkbQS646RFU0vHmtkGSSNmVgMukPTTlHU5zhOoVquk/yzVPs3MrJulLLBm8VZSf5GVadVnJI0BdyRB8c3AvunKchwnK3hLyplPM47jncRxjfcBf0mc+fSHaYpynG7iT9SO0xrNpOM+nLwsEqfgOk7XMTOKUZY7qhyn9+TIyJQjkqbYu1NWxLHsJ6WmynHmkc/n+Vr1V08on2stZKHVkAUNznCTIyPTqgMbzOyFqStxnCWYmJjgsQVSZuf637PQDz+CfACg03My0eIAVkh6PlACNpvZzpQ1Oc4TmBv4txhZWCs8AtRnGVPO4JGVrKrHiQcArgSeKqkAvNvMbktVmeMA09PTXL7zQR4ubQ44D1Q6VBL35Ti9JCtzVb20/r2k3wa+SjwY0HFSpVAo8KOpcUpUei2lIf04RsMZPLLS4tgLM7tJ0p+lIcZx5lMqlZjuA6cBHhx3skEmYhySTq1fyEnSc4DPAb+TpjDHKRaLXFfe3GsZTePBcScLZCWr6g8kPQU4h3jd79cRDwZ0nFTJ5/PcxI4lj/GnfMfZQ2bGcQBvBP6ZeBW+fwZOMLP+6Dtw+ppisciSq4Sx92p57kScYadbEbZmHMcLiIPhTwGeS7z6H2Z2e6rKHKcJ6lNgszCWIwTuAJfGP5/e04zj+Fzd6/2S9waclIoix2mB+ttHu2M5snYjypoDzNrnkzU9WSMrXVUvT6ZTd5yukc/n+VL5/obH1XdVtYvfiJYma47M056XJivpuBskXQ6cb2b3pi3IcSAev7GJxs8rzYzWbuQY/EbUDP759AtR1Plqlo1oxnH8N+IVAM+TlCNeiW+tmU2mqswZSiqVCvfMTvBAubDkFCNzLGsiBdZbFM4wMTIykvo1mhk5PgV8DfiapN8FLgE+n7RCzjSzDQudJ+l84LXAVjP79aTsE8CfAtuSwz5qZlcn+z4CvAeoAe83s2uS8pOBc4nXBPm6mZ3dZl2dPiCfz3PB5D1MMt3UKuA1rKGDadSicMfiDBKZ6KqSNAL8PvBu4Bji4Pg3iQcAXg08c5FTv0G8XvlF88o/b2b/OO8azyVu1TwPeCrwA0lzdr8EvAIYB26VtM7M7mmk2+lPpqam2FSZbvr4eIWOzkJwIeIkjpMVMjFXFfAA8CPg/5hZ/VrjlyctkAUxsxslHdOkjlOIu79KwIOSNgDHJ/s2mNlGAElrk2PdcQwg7YwU99aC43SfpmIcZrbgI6CZvb+Na75P0ruA24C/MrMJ4Ejg5rpjxpMyiAce1pef0MY1nYxjZjy6fSs/bTBSfD65pL3gOE5MJtJxF3MabfIV4EziDuczibu9TmPhX77BglHPBTvwJJ0OnA6watWqEFqdLpLP5zl38k52tXhe1qdad5xuk4kYR0jMbMvca0lfA65K3o4DR9cdehTwWPJ6sfL5ttcAawBWr17td5M+Y7EV/hqRpVTa0QFt/Xh3YH+RlUkOgyHpCDOb68R+I3BX8nodcLGkc4iD48cBtxB/W4+TdCzwKHEA/e3d1Oyky8TEBGsmH2BreXtb52fppjaKyJF+KmS3ydJn7DQmE+m4c0h6BvAPwDLg02Z2R4PjLwFOBA6VNA58HDhR0guIHxEfAt4LYGZ3S7qMOOhdBc6YG60u6X3ANcTpuOeb2d2tVNDJLtVqldsef5j10Za2u5xCjGoOdWMMlZk1qC0XpztkIsZRx1eAtUCBZJbcpQ42s7ctUHzeEsefBZy1QPnVxGm/zgARRRE/2byRb0ebkpt/7xJiQ02pUcOgje62+cR6fF0Ppz2yMnJ8jkPM7DwASR9MSY8zJFSrVf5l8j4mei1kN9mIk+zBp4frF7LWlVetVlO/RjMDAP8geXmgpDcSf04Hp6rKGXiq1epup9HrH16vr+/0N1n7/mQlOP665P8bgNcnr29JR47jdJ+szf6aNbJ2YwzFoNYrKzGOf/JFm5w0yUZKba+vn10GdUqWQXUcWYlxfB14UdpCHKdXDOoNxFmaUA8sw/j9acZxjEo6iHmfjJkV0pHkOP2Jp9EOJ1lzHFmJcTwLWM8TV+l8eiqKHKfLhHryrGGeRjuEZKOrdQ9Z6aq6x8xemLoSx+lz4gTapX+0WXs6dQaPbsxV5Y9HjuM4Tks00+L4rdRVOE6b+BO843SfZhzHv0t6k5ntAEgC5WvN7FXpSnMGGUlNrSneiBCOI5TzcSfm9JocMDqa/ty1zVzhsDmnAWBmE5KenKImZwgws8yspRFqAOBIoBm33AE57ZIjO1lVNUmrzOwRAElPI0spBM5QE9+ssxGqGyXHiDqf0joHEGD070ggO05/kZVp1f8WuEnSDcn73yVZac9xek2WpgvJAbkM3aiz9Nk4g0UzS8d+T9KLgBcTfw//0szaW3XHcQJTw4g6nMo8VNdQmRojkd+qnd7S09lxJT3bzO5LnAbsWbJ1VdJ15fNXOT3HAMtIz2kFqJD+j9ZxlqLX06p/kLhL6nML7DPgpFQUOU6XCTXyd4TcQE4G6PQXPZ0d18xOT/5/aeoqHKeHhOqqWjaga447/UU3guMN01EkvXze+8MkrW3ivPMlbZV0V13ZwZKulfRA8v9BSbkkfUHSBkm/qOseQ9KpyfEPSDq1teo5TveI4y2db5606GSdZrKqPiHpMDO7RNK7gb8B/qGJ874BfBG4qK7sw8B1Zna2pA8n7z8EvBo4LtlOIF7f/ARJBwMfB1YT/5rWS1pnZtlZcdRpi1ADAEMQqsURrzje+U0/a+tf+LiS/iIrCzmdDFwm6a+B/wJe0syU6mZ2o6Rj5hWfApyYvL4QuJ7YcZwCXGTx7Fw3SzpQ0hHJsdfOXU/StYmeS5rQ7WSYUAMAQ9zUQt6oQ9QpTqNNf4bTZnHH0V9kZXbcMeA04GtAATBJB7e5HsfhZrYZwMw2141APxLYVHfceFK2WLnjBCMCFCQ4HmbkeNbI2rThztJ0Y3bcZhzHevaMJfp14A8Ivx7HQr+2xcYvLfipSDqdZGDiqlWrwilzBp5wKb1GLsAodn/Cd7JOMwMAjw14vS2SjkhaG0cAW5PyceDouuOOIh43Ms6erq258usX0bkGWAOwevVqfzxyus4YI+44nJ6TiayqOSQdKulTkj4r6ejGZyzIOmAuM+pU4Mq68ncl2VUvBnYmXVrXAK+UdFCSgfXKpMxxMkhEDnW8udNw2iWn7ATH5/g6cCdxS+CbxHNWLYqkS4hbC4dKGifOjjqbOND+HuAR4E3J4VcDrwE2ADPAuyFe11zSmcCtyXGf9LXOnXqydKONEOVkHUDH6QU5g1ot/e9gK45jlZm9AUDSHzY62Mzetsiuly1wrAFnLGLnfOD8FnQ6Q0wvA9TexeQMCw0dR91gvJWSXkj8y9g3VVWO0yTz01+FtTzNeqgbvmcfOVkgK1lVc3NVPQ6ck7zemY4cx2mN+Tfr+H1rTfURyMyaHo7TKZkYx+FzVTmDTm5Ax184w0kmguOSvrBQuZm9P7wcZ1jI0pQjWYtLeKzE6YRMOA7i6UA+lrYQZ7jI0pQjFmj4X8g5r9xtOFmmGceRN7MLU1cbwNAJAAAWi0lEQVTiOG0QYp6pUHNVDWpLYVDrNahkpcXxbEl3ALPEYzh+AnzJzGZTVeY4TRBqninPhlocdxz9RVbGcTyHOPFkJfBU4kF7XwfekaIux2kKD2ynjzuO/iITLQ4ze7ju7d3AtZI+k54kx2kewzLjOAb1Bjuo9QpF1j6fXC791PJFHYekJ5nZZLKY0nzccTiZIAKijKxdkbUFmJzukDXH0euuqouB17L3tOpzhJ5W3XH6npEm5s1q5iaTtRuR01/0tMVhZq9N/g85rbrjBCXETXY00ESJy1DDEeiL6R2VIOmbztGdfmpnMBkbG0v9Gkt1Vf2+mf3HAuX7A582sz9PVZkz0IQaAJitpWMb21hsPqua7Tk7AujCfEPOYNLrKUfOlfQUMztvrkDS24Gz8NlqnQ4JNQAwxMSC1TpLnTCKOlvIyX2FE4BKpZL6NZZyHL8D/IekI4G1wJeBMvByM/tV6socpwk8HrA4/tkMJz1Nx02Wd/094NvAR4E/NrO1qStynD4mRCvKcTqh59Oqm9mUpFcTd029XdK/+Yhxp13K5TI/LWxi0+wkJQvTnA7RVRXqyTzuqkp/vWfHWYpej+OYYs8vcm7xpoKkGvGifU9KXZ3T95gZxajKrl27uOzx+/h5ZTtRrcwI4kks74qGHOzOWFqIUYRynTuOfXM5RkfccTi9ZZ999kn9Gkt1Ve2f1kUlPQRMEa+4UzWz1clAw0uBY4CHgDeb2YTiDrtzidcknyHuMrs9LW1OOCYnJ7mwcD8TVNi1axebmKEEHAAsI8fR+3b+7DGKlnQKkCwnu8QxK5RD6vwpbX+NduVpLxRj5Bp+dk7/se++6S/Q2sqa46F5qZltr3v/YeA6Mztb0oeT9x8CXg0cl2wnAF9J/ncySqVS4bbCo1y9fQP3M5NkLe3NMsSRY/t1fK3RBk6hGZaRCxJQXKYcy0fi9QT7gTGFqbeTLUa60OrtpeOYzynAicnrC4HriR3HKcBFFkd8bpZ0oKQjzGxzT1Q6S1KpVPjJ5o1cOrWBPLVFF3FdRo7DxwZv6frl8qd4Z/DpleMw4PuSDPhnM1sDHD7nDJKMricnxx4JbKo7dzwpc8eRIarVKndsf5Srph5hvDzBRIPjVyjHM/c5qOPrrsiN+PxQjlPHsmXLUr9GrxzHS8zsscQ5XCvpviWOXeiu8IQ0GkmnA6cDrFq1KoxKpyFRFPFgfitrJzcyPptnW5Or6VUtYke18wS95RoZuO6WQayT0z16no6bFmb2WPL/VknfAY4Htsx1QUk6AtiaHD4OHF13+lHEC0rNt7kGWAOwevVqT6ZPkSiKGJ+c4AdT42yvzvLoTJ7N1JKZMJt7/q9gjM9Od6xlLJejX2IKzeLdXU4nrMqtZHQ03Vt71x2HpH2BXDJGZF/glcAngXXAqcDZyf9XJqesA94naS1xUHynxzd6w9TUFJdNPcTs7CwPFAtso0K5zUnNR5Xj0LGVHWta7gFeZ0BYHqjbdVCD44cD30l+7KPAxWb2PUm3ApdJeg/wCPFKgwBXE6fibiBOx3139yU7AIVCgRunH6VMmXKHtpaT49gVBwTRlRU83uJ0wspcmC7K0Vz6t/WuOw4z2wg8f4HyPPCyBcoNOKML0pwlmJ2d5brpTUx37DJiilbjvplCx3b2Hxkl1+EYjFBPelmLTTx9bP/Uuyyc4cS/Vc6iRFFEoVpidnaW7z++gRvp/EY/R5ZusMEYwCo5zkK443CeQLFY5LsTD/Pg7E7ylJmdnWWLFQk5SdkKcjx7n4VWJc4+i3VJhepqCEUZo1xLf4ptJ1uszI2m/j10x+FQq9V4pDjJA9N5flmdYnp6mgdqU+yiuuCo7xCULWJzufOsql6wWJdU1rqqVmRMj9Mdnrpsn9QD5O44HPL5PF+cuJMd1VlmgNoSI75DEWHsrHYeL9l/ZJSVI8s6ilGEinFk7UadNT1Od+jpehzO4DI7O8sN05spFHexyYpsn9rOQ/Rnl8ZyjbBCcdP8wJGxtgLlB48u76vJCR2n17jjGBKKxSLX7YqHv0xMTPDTaoFdUYkSYFiQ9b9bYVQ5Dh/rfPrnA+qcxT659man9adyx2kNdxxDQj6f58qpeMXfYlSk2GM9I8BBoys6ttON/lzHcfbGHccQUK1WuXvnVgo9dxd7qJkxEWCuqjFTZhxH1rKqnOHEs6qcIOTzea5gvNcy9iInsSLACNcQK/c5jtMa7jgGlHK5zK1Tj7OpMsNj+cfI91rQPCIzZqPOk30tt8wH3jk9oxtP91nEHccAMjU1xfmb7+K+yiSzQBwCzxYVjC3lmY7t7McIy0bTX3+gGVb4hIvOkOCOY0AolUr8eOejPFzdRWGiwO3sDDSr1OLE+Uvt3Sj9/to/DOtTtbM47jj6kFKpxM0zWylWK0xUS8xUSowXJxmv7mIWqFDpyqiMeP2N3rK1WiQXxS2qZkZuLzTYz2e1dZzWcMfRJ1QqFe4t7aBUKnFLfpz7bBeVWo2yGWUqlNpcF6MTDJpc72+RkwOwPDfCSC7OqmrGcSw0mjqU4xjUp/JigFiU0z08q8phZmaGK/Mb2TC7gwIVSuUSEwHWw+g1IxIHjI51bOegkRUdp+PORo0nWPFWiePswR1HxikUCnx38iGKPWhRpEmcVdX5jFiPRNMsr6YflG6mNfOc5QdmJlDvOGnijiPjzM7OUkyiCYM0m1IVY3s5zIDEZRnJZjpuZD+WLXPH4Qw+7jgyRrVa5Z7Jbdwy9ThbKZMv5YlCBQQCMMZIx/Na5RDLcyMctWK/QKqyQVmG+foXfYNni7VP3zgOSScD5xJPc/R1Mzu7x5LaJooits7u4uHKFMValcLsDJuquygRUalU2Do7xfYkjtF28Dk1wuRSyQgycjxLzBJRqXUeSPapS5ys0xe/XEkjwJeAVwDjwK2S1pnZPb1Vtjizs7P8eGYLURQxMTvDo1GRGkYURcxGVWaqZYoWUY0iZqIys0kMwzLoKuopE0GH0ZblAWsYai2NEOyTGw0yb5Y/CTtZpy8cB3A8sMHMNgJIWgucAnTVcZgZU+VZHqpME0URO2tlSlGNYrXCzijOc4qiiF21CtuL02ylQpQ4hlLiEKIBC3K3S8UiirVKxzfIMXKsHOnsRhtqwaMSBgFaHDMBbPQjvi5K/9AvjuNIYFPd+3HghDQuNLfI0a6oGjuBqEI1eUI2M2arFSaJ95WsRtXi7om59NgoiijValT2ajmMsDwNsX3KCDmQmKyWOx5CvmxUSOroxr98JJeZVovj9AP94jgW+lXv1d8h6XTgdIBVq1alI0Ji5bIxVtL5+INh5uGjjuTI0ZU8e99DOrZ10MhYx0+p3jXkOK3RL45jHDi67v1RwGP1B5jZGmANwOrVq9vuRF+xYgWvWnFsu6c7zXDab/RageM4HdAvHYq3AsdJOlbSGPBWYF2PNTmO4wwlfdHiMLOqpPcB1xCn455vZnf3WJbjOM5Q0heOA8DMrgau7rUOx3GcYadfuqocx3GcjOCOw3Ecx2kJdxyO4zhOS7jjcBzHcVrCHYfjOI7TEjLL8pR67SFpG/Bwipc4FNjudobKTpa0uB23kxZPM7PDGh00kI4jbSTdZmar3c7w2MmSFrfjdjq10yneVeU4juO0hDsOx3EcpyXccbTHGrczdHaypMXtuJ2e4jEOx3EcpyW8xeE4juO0hDsOx3EcpyXccTiO4zgt4Y7DcRzHaQl3HA2Q9HRJ50v6lKT9JH1N0l2SviXpmBbs5CSdJuk/JP2XpPWS1ko6sUU9B0g6W9J9kvLJdm9SdmCL1VvsGt9t8rgnSfrfkv5F0tvn7ftyCC2JrY+1ePyrJL1n/t9H0mlNnCtJb5b0puT1yyR9QdKfSer49yLphy0ef+i89+9I9JyuFhdKl/RGSQcnrw+TdJGkOyVdKumoJm2cI+klrVx3CVsHS/qYpD9JPuu/lXSVpP8j6aAWbb1U0hclXSnpiuT38IwWzh+V9F5J35P0i+Q3+l1J/0PSstZrt+h1msqKkjSS6Dlz/uct6e9C6WkXz6pqgKQbgUuAA4B3ABcAlwGvBP67mZ3UpJ0LiKdB+QHwR8Ak8GPgQ8CVZvZPTdq5BvghcKGZPZ6UPQU4FXi5mb2iSTsvWmwXcJWZHdGEjSuAB4CbgdOACvB2MytJut3MFrtGS0h6xMxWNXnsp4HfBm4HXgf837nPthlNicN7MjBG/DdaDvw78Bpgi5n9RQu6fzG/CHgm8EsAM/tvTdjYrTm5YfwOcDHwWmDczP6yBT33mNlzk9eXEv/dvgW8nPi73PC7oz3T+RwGXApcYmY/b1bDPFtXA3cCTwKek7y+DHgF8HwzO6VJO2cDhwPXAW8AHgTuB/4M+LSZfasJG5cAO4ALgfGk+Cji39XBZvaWFup18GK7gP8ys4ZOWtLXgX2AW4B3AjeY2QeTfcF+W21jZr4tsQE/r3v9yGL7mrDzi3nvb07+Xw7c24KdX7azb4Fja8QO6EcLbMUmbdwx7/3fAj8BDgFub/FznlxkmwKqLdi5ExhNXh9IvGrk55v9ewF3Jv8vA/LAWPJ+dG5fC1rWAf8KPBt4GnAMsCl5/bQ2vn+3A/vW6WtVzy/rXq9f6m/ZSA9wHPD3wN3AfcDHgWe2qOeO5H8Bj7ajp/5vVvd3+kny+iDgrlY/mwX23d9ivWrARmIHNrfNvS83aeMXda9HicdvfDu5XzR930lr866qxkSSninp/wP2kbQaIGkGj7RgpyLp15JzXwSUAcysBLTS7HtY0t9IOnyuQNLhkj5EfFNqlnuB95rZS+dvND+J2vL67hszO4v4C34jsfNohR3AcWb2pHnb/sDmFuyMmlk10bODuNXxJEnfIm5FNGLu3Apwq5nN/Z2qxDeEpjGz1wNXEH8mzzezh4CKmT1sZs1OwrlS0gsl/SYwYma76vS1pAe4XtInJa1MXr8B4m4eYGeTNiy5/gNmdqaZPQ94M7CC1pd2ziVdUkcD+811LUo6hOb+VnNEdU/5TyX5XZrZBLFTaoaJpHty9/dZcffyW4CJFrRA7CRONLNj67anm9mxwJYmbeyuv5lVzex04A7ih739WtQTnl57rqxvwMuIuxbuJe4CuQLYAGwFTmnBzknAI8RdOw8CJyTlhwGfbcHOQcBniJ/yCsl2b1J2cAt2/gh41iL73tCkjc8Sd4/NLz8ZeKDFz/lTwPGL7PtMC3auAn5vEftRE+d/F9hvgfKnALe0+R3aFziHuAUy3uK581uDRyTlhwC3tWhrGfCJ5Hv4CBARt+guBlY1aSPY0y7wNuIb6RbgD4m7cX8APAqc3oKdtxB3n30/qdfvJ+WHARc3aeMY4q63bcTdXPcnv/FLgWNbrNcZxA8KC+378yZt/Ctw8gLlf0L88BHkb9Du5jGONkgClhNm1tITXxLMPMTM0pwWeahJnqYxs+IC+440s0fbtLsvcTfR1g60PR/4LTP7ars26myNAMvNbKbN8w8gbp3lWzxvPzObbueai9gbIY61ViWNAi8g7rZqpZU5F1d4OrDB4pZmJ5oOSTT573QRRnstoB+Q9GzgFOBI4qb6Y8CVxE/9rfAs4BRJ9XbWmdm9gXS+28wuyIKddmwkN7OT2ftzvqaVG4GZFRVnnr1+ATtNOY0ldLTsNBayJenAVuoU4nNZzJaklmyZ2XRIPcTdLifP+01saMNODfg14HcldaRpvjOV9Aozu7YNTU8ghK2QetrFYxwNSGIHa4n7Sm8Bbk1er5X04QB2LmnFTgP+IUN2WrIh6V3Ewd8TibNJ9gVeCqxP9nXFTigdoWy5nu5qWoTzAtgIaSuknrbwrqoGSLofeJ7Fwcj68jHgbjM7rst25qd47t5FnNWyvFt2QmlJbP2SOO6zY175QcDPzOyZ3bATSkcoW66nO3YkrVtsF3CSme3bjJZQtkLqSQPvqmpMRJypMT8L5ohkX7ftHA68iidmegj4aZfthNIyd85CTzERzWfGhLATSkcoW66nO3Z+h3ic1vz4jYDjW9ASylZIPcFxx9GYDwDXSXqAPemuq4BnAO/rgZ2riLN+7pi/Q9L1XbYTSgvAWcDtkr7P3p/PK4Azu2gnlI5QtlxPd+zcDMyY2Q3zdyQtmlYIYSuknuB4V1UTJLndxxMHA0U8svTWNrKqgtgZVJKuhVex9+dzjcX5+F2zE0pHKFuup7uanCbodT5wP260kGM+bHZCaUlsvTYLdkLpCGXL9QxnvULq6XTzFkcbKNBcMYNoJ5SWkLY6tZO1Orme7tjJWr1C6ukUT8dtj1aDgMNkJ5SWkLY6tZO1Orme7tjJWr1C6ukIdxzt8Tq3k6qNOd6bETuhdISy5Xq6Yydr9QqppyPccTRA0vslHV1fZmbjix0/THZCaUlsjUl6l6SXJ+/fDrxL0hlqYT2ETu2E0hHKlusZznqF1JMGHuNogKSdwC7gV8TrcnzLzLa5nXBaElvfJE4P34d4ptz9iKeRfhnx9/TUbtgJpSOULdcznPUKqScVeh2dz/oG/Jy4ZfZK4qH+24DvES/wsv8w2wmlJbH1i+T/UeLZUkeS92LeWiZp2gmlI5Qt1zOc9QqpJ43Nu6oaY2YWmdn3zew9xKO/v0w8ydvGIbcTSgvEazOMAfsTP2UdkJQvJ54OvFt2QukIZcv1dMdO1uoVUk9wfOR4Y/bKZLB4rql1wDolU3gPsZ1QWiBusdxHvAjP3wLfkrQReDHx5JDdshNKRyhbrqc7drJWr5B6guMxjgZIeqaZ3e920tNSZ++pAGb2mKQDidfCfsTMbummnVA6QtlyPd2xk7V6hdQTGnccHaBAi9oMop1QWkLa6tRO1urkerpjJ2v1CqmnXTzG0Rn3uJ1UbYS21amdrNXJ9XTHTtbqFVJPW3iMowGSPrjYLlpYNH4Q7YTSEtJWp3ayVifX0x07WatXSD1p4C2OxnwaOIg4u6F+24/WPr9BtBNKS0hbndrJWp1cT3fsZK1eIfWEp9f5wFnfiBck+s1F9m0aZjuhtGSpXlmrk+sZznqF1JPG5sHxBkh6FlCwBUZESzrczLYMq51QWkLa6tRO1urkerpjJ2v1CqknDdxxOI7jOC3R+76yjCPpAElnS7pPUj7Z7k3KDhxmO6G0ZKleWauT6xnOeoXUkwbuOBpzGTABnGhmh5jZIcBLk7JvDbmdUFpC2urUTtbq5Hq6Yydr9QqpJzy9DrJkfQN+2c6+YbATSkuW6pW1Orme4axXSD1pbN7iaMzDkv5G0uFzBZIOl/QhYNOQ2wmlJaStTu1krU6upzt2slavkHqC446jMW8BDgFukDQhqQBcDxwMvHnI7YTSEtJWp3ayVifX0x07WatXSD3h6XWTpx824NnEE4ztN6/85GG3E0pLluqVtTq5nuGsV0g9obeeXrwfNuD9wC+BfwMeAk6p23f7MNsJpSVL9cpanVzPcNYrpJ40tp5evB824E4Sjw8cA9wG/EXy/ufDbCeUlizVK2t1cj3DWa+QetLYfJLDxoxYMoWxmT0k6UTgcklPg70XMhpCO6G0hLTVqZ2s1cn1dMdO1uoVUk9wPDjemMclvWDuTfLHfC1wKPAbQ24nlJaQtjq1k7U6uZ7u2MlavULqCU+vmzxZ34CjgKcssu8lw2wnlJYs1StrdXI9w1mvkHrS2HyuKsdxHKclvKvKcRzHaQl3HI7jOE5LuONwHMdxWsIdh+M4jtMS7jgcx3Gclvh/QrJJpbevmBoAAAAASUVORK5CYII=\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "'''\n",
    "Vizualize contract durations:\n",
    "each line is one contract\n",
    "'''\n",
    "\n",
    "for i in tqdm_notebook(range(len(contract_dates))):\n",
    "    x, y = [contract_dates.iloc[i,:][0], contract_dates.iloc[i,:][1]], [i, i]\n",
    "    plt.plot(x, y, color = '#3fd4ad', linewidth = 0.1)\n",
    "    \n",
    "mode = contract_dates['contract_end'].mode()[0]\n",
    "x_vert1, y_vert1 = [date(2017,12,31), date(2017,12,31)], [0,len(contract_dates)]\n",
    "\n",
    "plt.plot(x_vert1, y_vert1, color  = '#666666')\n",
    "\n",
    "ticks = [date(2017,month,15) for month in range(8,13)]\n",
    "ticks = ticks + [date(2018,month,15) for month in range(1,13)]\n",
    "labels = ['%s-%s' % (tick.year, ('0' + str(tick.month))[-2:]) for tick in ticks]\n",
    "\n",
    "plt.xticks(ticks, labels, rotation='vertical')\n",
    "plt.ylabel('Кількість укладених угод')\n",
    "#plt.savefig('01_visuals/contracts_duration_all_2018-11-13.png', dpi=300, bbox_inches = 'tight')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAYcAAAEeCAYAAABhd9n1AAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDIuMi4yLCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvhp/UCwAAHmRJREFUeJzt3X20HVWZ5/Hvj0QYQA1vMWoCXGzi+NIzImYI3Xa3ttghgCPokjZ29xBduGIrtnZPr4HQ0zMZX7CD/YKyZqCHIWhwlIhoN5lBhYiiyx4DhIC8BUiUt8jbbROwldY28swfe185ufvcU+eeuufUSc7vs1atW2dXPWfvW1XJc6tq1y5FBGZmZq32aboBZmY2fJwczMys4ORgZmYFJwczMys4OZiZWcHJwczMCk4OZmZWcHIwM7OCk4OZmRVmN92AXh122GExNjbWdDPMzPYYt9xyyz9GxNxu1t1jk8PY2BibNm1quhlmZnsMSQ92u64vK5mZWcHJwczMCk4OZmZWcHIwM7OCk4OZmRWcHMzMrODkYGZmBScHMzMrODmYmVlhj31Cuo6xldf0HPvA6lNmsCVmZsPJZw5mZlZwcjAzs0JlcpB0maQnJN3ZUnaIpA2StuafB+dySbpQ0jZJt0s6tiVmeV5/q6TlLeWvkXRHjrlQkmb6lzQzs+np5szh08DSSWUrgesjYiFwff4McBKwME8rgIshJRNgFbAYOA5YNZFQ8jorWuIm12VmZgNWmRwi4lvAjknFpwJr8/xa4LSW8ssj2QgcJOlFwInAhojYERE7gQ3A0rzs+RHxnYgI4PKW7zIzs4b0es9hXkQ8CpB/viCXzwcebllvey7rVL69TbmZmTVopm9It7tfED2Ut/9yaYWkTZI2jY+P99hEMzOr0mtyeDxfEiL/fCKXbwcOb1lvAfBIRfmCNuVtRcQlEbEoIhbNndvVm+7MzKwHvSaH9cBEj6PlwNUt5WfkXkvHA0/ly07XAkskHZxvRC8Brs3L/knS8bmX0hkt32VmZg2pfEJa0hXA64HDJG0n9TpaDVwp6UzgIeD0vPqXgZOBbcDTwLsAImKHpI8AN+f1PhwREze530vqEbU/8JU8mZlZgyqTQ0S8Y4pFJ7RZN4Czpviey4DL2pRvAn61qh1mZjY4fkLazMwKTg5mZlZwcjAzs4KTg5mZFZwczMys4ORgZmYFJwczMys4OZiZWcHJwczMCk4OZmZWcHIwM7OCk4OZmRWcHMzMrODkYGZmBScHMzMrODmYmVnBycHMzApODmZmVnByMDOzgpODmZkVnBzMzKzg5GBmZgUnBzMzKzg5mJlZwcnBzMwKTg5mZlZwcjAzs4KTg5mZFZwczMys4ORgZmYFJwczMyvUSg6S/kTSXZLulHSFpH8l6ShJN0raKunzkvbN6+6XP2/Ly8davufcXH6vpBPr/UpmZlZXz8lB0nzgA8CiiPhVYBawDDgfuCAiFgI7gTNzyJnAzog4Grggr4ekV+S4VwJLgYskzeq1XWZmVl/dy0qzgf0lzQYOAB4F3gBclZevBU7L86fmz+TlJ0hSLl8XET+LiPuBbcBxNdtlZmY19JwcIuIHwF8BD5GSwlPALcCTEbErr7YdmJ/n5wMP59hdef1DW8vbxJiZWQPqXFY6mPRX/1HAi4EDgZParBoTIVMsm6q8XZ0rJG2StGl8fHz6jTYzs67Uuaz0RuD+iBiPiJ8DXwJ+HTgoX2YCWAA8kue3A4cD5OVzgB2t5W1idhMRl0TEoohYNHfu3BpNNzOzTuokh4eA4yUdkO8dnADcDXwDeFteZzlwdZ5fnz+Tl389IiKXL8u9mY4CFgI31WiXmZnVNLt6lfYi4kZJVwGbgV3ArcAlwDXAOkkfzWVrcsga4DOStpHOGJbl77lL0pWkxLILOCsiftFru8zMrL6ekwNARKwCVk0q/j5tehtFxE+B06f4nvOA8+q0xczMZo6fkDYzs4KTg5mZFZwczMys4ORgZmYFJwczMys4OZiZWcHJwczMCk4OZmZWcHIwM7OCk4OZmRWcHMzMrODkYGZmBScHMzMrODmYmVnBycHMzApODmZmVnByMDOzgpODmZkVnBzMzKzg5GBmZgUnBzMzKzg5mJlZwcnBzMwKTg5mZlZwcjAzs4KTg5mZFZwczMys4ORgZmYFJwczMys4OZiZWcHJwczMCrWSg6SDJF0l6R5JWyT9mqRDJG2QtDX/PDivK0kXStom6XZJx7Z8z/K8/lZJy+v+UmZmVk/dM4dPAl+NiJcBrwK2ACuB6yNiIXB9/gxwErAwTyuAiwEkHQKsAhYDxwGrJhKKmZk1o+fkIOn5wG8BawAi4l8i4kngVGBtXm0tcFqePxW4PJKNwEGSXgScCGyIiB0RsRPYACzttV1mZlZfnTOHlwDjwKck3SrpUkkHAvMi4lGA/PMFef35wMMt8dtz2VTlZmbWkDrJYTZwLHBxRLwa+AnPXkJqR23KokN5+QXSCkmbJG0aHx+fbnvNzKxLdZLDdmB7RNyYP19FShaP58tF5J9PtKx/eEv8AuCRDuWFiLgkIhZFxKK5c+fWaLqZmXUyu9fAiHhM0sOS/nVE3AucANydp+XA6vzz6hyyHni/pHWkm89PRcSjkq4FPtZyE3oJcG6v7eq3sZXX9Bz7wOpTZrAlZmb903NyyP4I+KykfYHvA+8inY1cKelM4CHg9Lzul4GTgW3A03ldImKHpI8AN+f1PhwRO2q2y8zMaqiVHCLiNmBRm0UntFk3gLOm+J7LgMvqtMXMzGaOn5A2M7OCk4OZmRWcHMzMrODkYGZmBScHMzMrODmYmVnBycHMzApODmZmVnByMDOzgpODmZkVnBzMzKzg5GBmZgUnBzMzKzg5mJlZwcnBzMwKTg5mZlZwcjAzs4KTg5mZFZwczMys4ORgZmYFJwczMys4OZiZWcHJwczMCk4OZmZWcHIwM7OCk4OZmRWcHMzMrODkYGZmBScHMzMrODmYmVnBycHMzAq1k4OkWZJulfR/8+ejJN0oaaukz0vaN5fvlz9vy8vHWr7j3Fx+r6QT67bJzMzqmYkzhw8CW1o+nw9cEBELgZ3Ambn8TGBnRBwNXJDXQ9IrgGXAK4GlwEWSZs1Au8zMrEe1koOkBcApwKX5s4A3AFflVdYCp+X5U/Nn8vIT8vqnAusi4mcRcT+wDTiuTrvMzKyeumcOnwDOBp7Jnw8FnoyIXfnzdmB+np8PPAyQlz+V1/9leZsYMzNrQM/JQdKbgCci4pbW4jarRsWyTjGT61whaZOkTePj49Nqr5mZda/OmcNrgTdLegBYR7qc9AngIEmz8zoLgEfy/HbgcIC8fA6wo7W8TcxuIuKSiFgUEYvmzp1bo+lmZtZJz8khIs6NiAURMUa6ofz1iPh94BvA2/Jqy4Gr8/z6/Jm8/OsREbl8We7NdBSwELip13aZmVl9s6tXmbZzgHWSPgrcCqzJ5WuAz0jaRjpjWAYQEXdJuhK4G9gFnBURv+hDu8zMrEszkhwi4gbghjz/fdr0NoqInwKnTxF/HnDeTLTFzMzq8xPSZmZWcHIwM7OCk4OZmRWcHMzMrODkYGZmBScHMzMrODmYmVnBycHMzApODmZmVnByMDOzgpODmZkVnBzMzKzg5GBmZgUnBzMzK/TjfQ42hbGV1/Qc+8DqU2awJWZmnfnMwczMCk4OZmZWcHIwM7OCk4OZmRWcHMzMrODkYGZmBScHMzMrODmYmVnBycHMzApODmZmVnByMDOzgpODmZkVnBzMzKzg5GBmZgUnBzMzKzg5mJlZoefkIOlwSd+QtEXSXZI+mMsPkbRB0tb88+BcLkkXStom6XZJx7Z81/K8/lZJy+v/WmZmVkedM4ddwJ9GxMuB44GzJL0CWAlcHxELgevzZ4CTgIV5WgFcDCmZAKuAxcBxwKqJhGJmZs3oOTlExKMRsTnP/xOwBZgPnAqszautBU7L86cCl0eyEThI0ouAE4ENEbEjInYCG4ClvbbLzMzqm5F7DpLGgFcDNwLzIuJRSAkEeEFebT7wcEvY9lw2VbmZmTWkdnKQ9Fzgi8AfR8SPOq3apiw6lLera4WkTZI2jY+PT7+xZmbWlVrJQdJzSInhsxHxpVz8eL5cRP75RC7fDhzeEr4AeKRDeSEiLomIRRGxaO7cuXWabmZmHdTprSRgDbAlIv6mZdF6YKLH0XLg6pbyM3KvpeOBp/Jlp2uBJZIOzjeil+QyMzNryOwasa8F/gNwh6TbctmfAauBKyWdCTwEnJ6XfRk4GdgGPA28CyAidkj6CHBzXu/DEbGjRrvMzKymnpNDRHyb9vcLAE5os34AZ03xXZcBl/XaFjMzm1l+QtrMzApODmZmVnByMDOzgpODmZkV6vRWsgEaW3lNrfgHVp8yQy0xs1HgMwczMys4OZiZWcHJwczMCk4OZmZWcHIwM7OCk4OZmRWcHMzMrODkYGZmBScHMzMrODmYmVnBycHMzApODmZmVvDAeyOizsB9HrTPbPT4zMHMzApODmZmVvBlJesrX84y2zP5zMHMzAo+c7BKdd9CZ2Z7Hp85mJlZwcnBzMwKTg5mZlZwcjAzs4KTg5mZFZwczMys4K6sNrTqdqH1Q3RmvXNysL2Wn842693QXFaStFTSvZK2SVrZdHvMzEbZUJw5SJoF/A/gd4DtwM2S1kfE3c22zEaVzzps1A1FcgCOA7ZFxPcBJK0DTgWcHGyP01RiaXKYEyfEvc+wJIf5wMMtn7cDixtqi1lj9tRxrJpqd92ktCcm8kEl4mFJDmpTFsVK0gpgRf74Y0n39ljfYcA/7mGxo1q3273nxA68bp0/I/U2WXedentxZNdrRkTjE/BrwLUtn88Fzu1jfZv2tNhRrdvt3nNi3e7Bt7uf07D0VroZWCjpKEn7AsuA9Q23ycxsZA3FZaWI2CXp/cC1wCzgsoi4q+FmmZmNrKFIDgAR8WXgywOq7pI9MHZU63a795zYJuse1Xb3jfJ1LzMzs18alnsOZmY2RJwczMysMDT3HPpF0stIT1vPJz078QiwPiK2NNowM7Mhtlffc5B0DvAOYB3pqWuABaSususiYnVTbRsESfNoSYoR8XiXcSINadKaUG+KaR4skg4BIiJ2DqLdQ1BvY9t7hOseuX09KHt7crgPeGVE/HxS+b7AXRGxsCJ+DumBvNOAubn4CeBqYHVEPNllOwZ6IEk6BvhbYA7wg1y8AHgSeF9EbO4QuwS4CNg6KfboHHtdRd1HAB8HTsj1CXg+8HVgZUQ80Kd2N1LvDLS77vYeubpHdV8PXNNP4fVzAu4BjmxTfiRwbxfx1wLnAC9sKXthLtvQRfwxwEZgC/C1PN2Ty46tiF0CbAO+Alyap6/msiUVsbcBi9uUHw98tyJ2CzDWpvwoYEsXv/N3gLcDs1rKZpHO1jb2sd2N1DsE23vk6h7VfT3oqfEG9PWXg6Ut/8FekqeJ/2CXdhE/ZQLptKzpAwnY2mHZtqpYYHab8n2rYruoe8plM9HuJuod8u29V9Y9qvt60NNefUM6Ir4q6aU8e2lG5PdFRMQvuviKByWdDayNfCkoXyJ6J7uPIjuVAyPixjbt2ijpwIrY2Tx7n6TVD4DnVMR+RdI1wOUt7TwcOIOUHDu5jPQ+jXWTYpcBaypiAW6RdBGwdlL8cuDWPra7qXrrxtfd3qNY96ju64Haq+851CXpYGAlqbfTvFz8GGncp/MjYkdF/IXAr9D+QLo/It7fIfZc4HdJN9MnH0hXRsRfVNR9Es/20ppIiusjPYnekaRXAG9uE1v5fo18P+fMdnUDayLiZ/1od1P1zkR8ne09inWP8r4eJCeHPtuTD6Ree4JYb5rc3qNad1P2iN+56etawz4BJwIXk/4quTrPV96vmOE2HAIcPI315wCrSfctfpinLbnsoIrYI0hnK0+QrpFuzfPraHMPpE38bOA9pPs8twPfzfN/CDynj+1upN4h2N4jV/eo7utBT403YJgn4BOkwQCXAb+Rp2W57JPDeiAxdS+rlVT0sqJGT5C87hWkBHo8qZvegjx/MfD5Pra7kXqHYHuPXN2juq8HPTXegGGegPumKBcVvSLyek394+m5l1Wn36vL37lT3W235wy1u5F6h3x775V1j+q+HvTksZU6+6mk49qU/zvgp13Ej0XE+RHx2ERBRDwW6cnsIypiD4uIz0dLr6qI+EVErAMOrYh9UNLZuWcVkHpZ5SfGq3pZ3SLpIkmLJb04T4tz75CqniAAOyWdLumXx5akfSS9Hai6vlqn3U3VWze+7vYexbpHdV8PVtPZaZgn4FjgRuBu4Lo8bcllr+ki/jrgbGBeS9k80tnE1ypi15GeplwMvDhPi3PZlRWxBwPnkx6425mnLbnskIrYfYH3krrl3QHcmeffB+zXxe88BnweGAfuI10OG89lR/Wx3Y3UOwTbe+TqHtV9PejJvZW6IOmFtPQYipYzgYq4nrvC1u2uNwwkHUrqEVfnxe97TL02eN7X/ePkUEFpfKWl7D6+0bXR5bhKTZF0ImlMqNZ2Xx0RHR/UkTSblJSKWFJS+nmH8InvaDcS7tURcU+/2t1kvXXiZ2h7j1zdo7qvB8nJoQNJZwCrSJeHWgfK+h3gQxFxeRffMfADSdIngJeSHr5rHY32DNKNrw92iL2CNIjY2kmxy0mnzW+vaHfPI+HWbHcj9c5Au+tu75Gre1T39cA1fV1rmCfgXtp0OSVdd+zYKyKv13NXWOp11+u5lxU1eoJMrEObvuak661VdddpdyP1DsP2HrW6R3VfD3pyb6XORPqLfbJn8rIqJ0fEyRGxLiK+nad1wCnAyRWxx0bEeyNiY0Rsz9PGiHgv8OqK2Dq9rOr0BIG0bV7cpvxFeVknddrdVL114+tu71Gse1T39UDt1QPvzYDzgM2SruPZbmpHkC4rfaSL+J9KOi4ibppU3vWBBHwxIp6BdCABp1N9IL0TuFjS83j29PVw4Ed5WSfLSD0vLpK0k5QEDyKNlb+sIhbgj4HrJW1l9212NDDlWFIz0O6m6q0bX3d7j2Ldo7qvB8r3HCrkHkcnsnuPoWujizFRJB1LugzU7kB6X0Tc0iF2jHQgvYGUDFoPpJURcX8X9ffUy6olvqeeIDmJ9ToSbp3eYY3UO4PxPfe8GbW6R3lfD4rPHCpExE5J32D3N7l1dQoY6a1Qi3s5kCK9zert0PM/njnA61rbLamrXlaTe4JI6ronyETzW6ZnWn72td0N1tvo9h7RukdyXw+S7zl0IOkYSRuBG0h/xf8l8E1JG/NZQTffMXEgvQ74TeB1kg7qMvZluWfGKuC/SDonH1xVcWcAm4HXAwcABwK/TXpC84yK2HNIvUAE3ATcnOfXSVrZRd1LSA8l/TfSfZVTgA8BW/OyfrW7kXpnoN11t/fI1T2q+3rgmr4jPswT9V8peAbwPdKlpT/P09/msjMqYs/J9a8E/iBPKyfKKmJ77mVFjZ4geb06b7Cr0+5G6h2C7T1ydY/qvh705MtKndV5kxvAfyYNs7Hb6Wa+j3Ejqa/0VM4EXhmTnmeQ9DfAXaSRXadSp5fVRE+QByeVd9MTBOq9wa5Ou5uqt2583e09inWP6r4eKCeHzuq+UrCpA6lOL6s6PUGg3qsQ67S7qXrrxtfd3qNY96ju64Fyb6UKqvcmt+XAfyU9YV0cSBHx6Q6xS4H/Trq2WhxIUf2EdZ1eVnV7gtR5zWiddjdS7wy0u+72Hrm6R3VfD5KTQ581/A93Hrv3snq8yzi11DsxbMdNMc2DRT2+CrHXdg9BvY1t7xGue+T29aD4slIHuafRuaQzhxfk4idI4xutnnwvoZ2o0RWWHrvrSTqGdON7DimhCFgg6UnS8xWbO8QuIQ0LvpXdx5M6WtL7IuK6irqPAD5Oej7jqVw2h2efz3igT+1upN4ZaHfd7T1ydY/qvh64pu+ID/NE/VcKHgNsJPWu2AB8jTQO/EbS8BidYpcA20jvxr00T1/NZUsqYnvuZUWNniB5vTpvsKvT7kbqHYLtPXJ1j+q+HvTUeAOGeaL+KwWb+sfT6XWE26pigdltyvetiu2i7qouirXa3US9Q76998q6R3VfD3ryZaXOHpR0NrA28jXFfK3xnXT3SsE6XWHrdNer08uqTk8QyK9CJA1L3Bq/nOpXIdZpd1P11o2vu71Hse5R3dcD5RvSHah8k1sAj9PFm9xy/IXAr9D+QLo/IqbsvibpXOB3SU9UTj6QroyIv6iou04vq5dPEdtNT5Bab7Drtd1N1TsT8XW29yjWPcr7epCcHKZB0m+SehrcEV3ePBqVA8nM9jJNX9ca5onUxWxi/t2kU9ZVwD9QMYRFw+2eQ3qCegvwwzxtyWXFo/+TYpdO+p5LgduBzwHzuqh7NvAe0o3024Hv5vk/pM3QAZNi/23L/HNIw42sBz4GHDCM9Q7B9h65ukd1Xw968sB7nbVe238PqZfQh0g9iX6/KljSHEmrJW2R9MM8bcllHQffyw/BtX7PpZJul/S5fN+jkytJw3z/dkQcGhGHkgYHexL4QkXsx1rm/xp4DPj3pEHC/mdFLMBnSL20PsTug6K9CvjfFbGfbplfTXrg76+B/UndB4exXmh2e49i3aO6rwer6ew0zBPpL5KDgUOBTZOW3dpFfM9dYYHNLfOXAh8FjgT+BPj7itiee1lNqve2Sctu6xTbRd1VA5Pd2loX+a9A0iW124ex3iHf3ntl3aO6rwc9ubdSZ3OAW0gHTkh6YUQ8Jum5uazKWESc31oQ6V0OqyW9axrtWBQRx+T5C5SG5eikTi+rF0j6j6Tf7/mSFPnopbsh3uu8wW6OpLfmuveLPOhgRISkqptjdet9C+n3m2690Oz2HsW6mzrGoNntPVBD16BhEhFjEfGSiDgq/5x4Sc8zwFu6+IoHJZ3dehlI0jylcd27OpAk/Sn5QGpZVrXf3k462/mmpJ2SdpDeSXEIqQdUJ/+L9Oa655K6Ch6W2/1C0l9aVZYBbwMek3SfpPtIp89vpfpViN8knWa/Cdg4sd1y3VUvOpqo9/Fc79Zp1Pst0jg9vdQLzW7vUay77jH2Jurv6xsk7ajxO3+a6W/vgXJvpT6a1BV2YviNia6wq6PDMBqSVk0quigixvOB9PGIqHqxyMtIj+ZvjIgft5QvjepB+15G6iF143Rj83qLSd1+vwe8nPTQ393RXQ+txcAzEXGz0uBqS4F7uolt+Y5DSX+dfSIi/qDbuEnfcXnVNu4QO+1ebS2xv5Fj7+wmNm+veyLiKUkHkI63Y0nDun8sIp6aZuyrgburYnP8B4C/i4hunvmZydh9gXeQxiXaDJwE/Drpd74kJg1zPyl2P1IC+UFEfE3S7+XYLVWxLd9xNOmPw8OBXaT3NFxRtb0mxS7IsVu7jR00J4eGSHpXRHyqH7H5H95ZpAP+GOCDEXF1XrY5IqZ8i52kPyINHzzt2LzOKtI/1tmkIUOOI/219kbSgIPn9Sl2fZviN5DG2yEi3tyP2Bx/U0Qcl+ffTdr2f0/quPB/ImLKd2+0iX0/8HfdxOaYu4BXRcQuSZcAPwG+CJyQy986jdingau6ic3xT+X6vkfqcfOF6PJVtpNir8ix413GfpZ0jOxPGlvpQNI2O4H0f9qUl11bYg8g3UR+LvClHEtEvLOi7g+Qzjq+RboZfhvpUtZbSGMr3dCP2EY0fdNjVCfgoX7FAncAz83zY8Am0n/yUHEjvU5sS/ws0j++HwHPz+X7U31TuU7sZlJPldeTXsn6euDRPP+6ithbe42dvF1IPU/m5vkDSWcPfYnN621pmd88aVnVjd2eY1u22z6kRLYGGCc9JbwceF4fY2/PP2eTzsRn5c/ddFzoObb1GM3zBwA35Pkjuvy31VNsE5NvSPeRpNunWkR64rovsaQD8McAEfGApNcDV0k6kuob6XViAXZFGlL8aUnfi4gf5e/6Z0lVI8rWiV0EfJD09r3/FBG3SfrniPhmF21+TY1YgH3yJcR9SH+5jud2/0TSrj7GAtzZcib5XUmLImKTpJcCVZdI6sTmZsYzpPeVXCfpOaQzv3cAfwXM7VPsPvnS0oGk/2TnADuA/ageWqZO7ITZwC9yzPPyL/NQ/h36GTtQTg79NY/0LofJ9xYE/L8+xj4m6ZiIuA0gIn4s6U2ksV3+TR9jAf5F0gER8TTpP93U6DSkctV/8D3H5v9oLpD0hfzzcbo8vuvEZnV6tdXtEfdu4JOS/px0Q/U7kh4mdXh4dx9jmdy+SNfr1wPrJe3fx9g1pNGNZ5ES+hckfZ90b2tdH2MhdSu/WdJG4LeA8wEkzSUlmX7FDpzvOfSRpDXApyLi222WfS4ifq9PsQtIf4U/1mbZayPiH/oRm9fZL9qMbSPpMOBFEXFHP2LbxJwCvDYi/qzbmJmInfQ9B5CefL2/37GSnge8hDxgY0zj5TW9xkp6aUTc1209MxWb418MEBGPKD1Q+kbS5dab+hmb419J6mhxZ0TcM8129xw7aE4OZmZW8HMOZmZWcHIwM7OCk4OZmRWcHMzMrODkYGZmhf8Pka2GVDOQe7oAAAAASUVORK5CYII=\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "269\n"
     ]
    }
   ],
   "source": [
    "'''\n",
    "Distribution of contract values\n",
    "Contracts for more than 10 mln UAH are filtered out as outliers\n",
    "'''\n",
    "plt.hist(data_addcontr.loc[data_addcontr['contract_value']<10**7]['contract_value'], bins = 20)\n",
    "ticks = [(i*10**6)/2 for i in range(20)]\n",
    "plt.xticks(ticks, rotation='vertical')\n",
    "#plt.yscale('log')\n",
    "plt.show()\n",
    "data_addcontr['contract_value']\n",
    "print(len(data_addcontr.loc[data_addcontr['contract_value']>10**7]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''\n",
    "Function for creating description for different contact value thresholds\n",
    "'''\n",
    "def printouts(threshold, data_addcontr):\n",
    "    contracts_all = data_addcontr['contract_id']\n",
    "    contracts = data_addcontr.loc[data_addcontr['contract_value']>threshold]\n",
    "    contracts_unique = contracts.drop_duplicates(subset='contract_id')\n",
    "    contracts_sum = contracts_unique['contract_value'].sum()\n",
    "    contracts_sum_all = data_addcontr.drop_duplicates(subset='contract_id')['contract_value'].sum()\n",
    "    \n",
    "    print(\"При порозі %s грн доведеться опрацювати %i додугод до %i договорів\" %(threshold,len(contracts),len(contracts_unique)))\n",
    "    print(\"Це %.2f%% від усіх додугод та %.2f%% від усіх договорів\" %(len(contracts)/len(contracts_all)*100,len(contracts_unique)/len(contracts_all.unique())*100))\n",
    "    print(\"Сума опрацьованих угод за таких умов %.2f млн грн, що становить %.2f%% від загальної суми\" %(contracts_sum/10**6,contracts_sum/contracts_sum_all*100))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "При порозі 200000 грн доведеться опрацювати 20441 додугод до 3291 договорів\n",
      "Це 83.35% від усіх додугод та 74.90% від усіх договорів\n",
      "Сума опрацьованих угод за таких умов 5619.07 млн грн, що становить 97.91% від загальної суми\n"
     ]
    }
   ],
   "source": [
    "#threshold 200 000 UAH (official procurement threshold in most cases)\n",
    "printouts(2*10**5, data_addcontr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "При порозі 500000 грн доведеться опрацювати 14091 додугод до 2176 договорів\n",
      "Це 57.46% від усіх додугод та 49.52% від усіх договорів\n",
      "Сума опрацьованих угод за таких умов 5236.25 млн грн, що становить 91.24% від загальної суми\n"
     ]
    }
   ],
   "source": [
    "#threshold 500 000 UAH\n",
    "printouts(5*10**5, data_addcontr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "При порозі 1000000 грн доведеться опрацювати 8250 додугод до 1207 договорів\n",
      "Це 33.64% від усіх додугод та 27.47% від усіх договорів\n",
      "Сума опрацьованих угод за таких умов 4551.86 млн грн, що становить 79.31% від загальної суми\n"
     ]
    }
   ],
   "source": [
    "#threshold 1 000 000 UAH, which is chosen as optimal threshold for further analysis\n",
    "printouts(10**6, data_addcontr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "При порозі 2000000 грн доведеться опрацювати 3661 додугод до 534 договорів\n",
      "Це 14.93% від усіх додугод та 12.15% від усіх договорів\n",
      "Сума опрацьованих угод за таких умов 3616.13 млн грн, що становить 63.01% від загальної суми\n"
     ]
    }
   ],
   "source": [
    "#threshold 2 000 000 UAH\n",
    "printouts(2*10**6, data_addcontr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(8250, 19)\n"
     ]
    }
   ],
   "source": [
    "'''\n",
    "Export data to contract with value exceeding 1 mln UAH\n",
    "'''\n",
    "data_addcontr_filtered = data_addcontr.loc[data_addcontr['contract_value']>10**6]\n",
    "print(data_addcontr_filtered.shape)\n",
    "data_addcontr_filtered.to_excel('02_data/addcontr_gas_1mln_v2.xlsx', index = False)"
   ]
  }
 ],
 "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.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}