02_addcontr_distribute.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import random\n",
    "import os\n",
    "\n",
    "from datetime import date\n",
    "from tqdm import tqdm_notebook\n",
    "from shutil import copy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(7127, 14)\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>addcontr_date</th>\n",
       "      <th>rationale_type</th>\n",
       "      <th>rationale</th>\n",
       "      <th>contract_id</th>\n",
       "      <th>date_signed</th>\n",
       "      <th>contract_end</th>\n",
       "      <th>tender_id</th>\n",
       "      <th>procuring_entity</th>\n",
       "      <th>contract_value</th>\n",
       "      <th>region</th>\n",
       "      <th>tenderURL</th>\n",
       "      <th>price_start</th>\n",
       "      <th>price_addcontr</th>\n",
       "      <th>row_added_manually</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017-10-10</td>\n",
       "      <td>volumeCuts, itemPriceVariation</td>\n",
       "      <td>Згідно довідки Торгово-промислової палати Укра...</td>\n",
       "      <td>244af7a6ee364ed3a8b3cff3cedabc3f</td>\n",
       "      <td>2017-07-31</td>\n",
       "      <td>2017-12-30</td>\n",
       "      <td>UA-2017-05-15-002292-b</td>\n",
       "      <td>Державний вищий навчальний заклад \"Університет...</td>\n",
       "      <td>1093519.824</td>\n",
       "      <td>Київська область</td>\n",
       "      <td>https://prozorro.gov.ua/tender/UA-2017-05-15-0...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017-08-29</td>\n",
       "      <td>volumeCuts</td>\n",
       "      <td>Зменшення обсягів закупівлі з метою приведення...</td>\n",
       "      <td>28cf7ff64b564839b13c8c91c98676dc</td>\n",
       "      <td>2017-08-02</td>\n",
       "      <td>2017-12-31</td>\n",
       "      <td>UA-2017-06-21-000460-b</td>\n",
       "      <td>Квартирно - експлуатаційний відділ м. Херсона</td>\n",
       "      <td>1124468.370</td>\n",
       "      <td>Херсонська область</td>\n",
       "      <td>https://prozorro.gov.ua/tender/UA-2017-06-21-0...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017-09-20</td>\n",
       "      <td>itemPriceVariation</td>\n",
       "      <td>Підвищення вартості природного газу на ринку У...</td>\n",
       "      <td>28cf7ff64b564839b13c8c91c98676dc</td>\n",
       "      <td>2017-08-02</td>\n",
       "      <td>2017-12-31</td>\n",
       "      <td>UA-2017-06-21-000460-b</td>\n",
       "      <td>Квартирно - експлуатаційний відділ м. Херсона</td>\n",
       "      <td>1124468.370</td>\n",
       "      <td>Херсонська область</td>\n",
       "      <td>https://prozorro.gov.ua/tender/UA-2017-06-21-0...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  addcontr_date                  rationale_type  \\\n",
       "0    2017-10-10  volumeCuts, itemPriceVariation   \n",
       "1    2017-08-29                      volumeCuts   \n",
       "2    2017-09-20              itemPriceVariation   \n",
       "\n",
       "                                           rationale  \\\n",
       "0  Згідно довідки Торгово-промислової палати Укра...   \n",
       "1  Зменшення обсягів закупівлі з метою приведення...   \n",
       "2  Підвищення вартості природного газу на ринку У...   \n",
       "\n",
       "                        contract_id date_signed contract_end  \\\n",
       "0  244af7a6ee364ed3a8b3cff3cedabc3f  2017-07-31   2017-12-30   \n",
       "1  28cf7ff64b564839b13c8c91c98676dc  2017-08-02   2017-12-31   \n",
       "2  28cf7ff64b564839b13c8c91c98676dc  2017-08-02   2017-12-31   \n",
       "\n",
       "                tender_id                                   procuring_entity  \\\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",
       "   contract_value              region  \\\n",
       "0     1093519.824    Київська область   \n",
       "1     1124468.370  Херсонська область   \n",
       "2     1124468.370  Херсонська область   \n",
       "\n",
       "                                           tenderURL price_start  \\\n",
       "0  https://prozorro.gov.ua/tender/UA-2017-05-15-0...               \n",
       "1  https://prozorro.gov.ua/tender/UA-2017-06-21-0...               \n",
       "2  https://prozorro.gov.ua/tender/UA-2017-06-21-0...               \n",
       "\n",
       "  price_addcontr  row_added_manually  \n",
       "0                              False  \n",
       "1                              False  \n",
       "2                              False  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'''\n",
    "Import data, previously filtered in the script 01_addcontr_explore (contract value > 1 000 000 UAH)\n",
    "'''\n",
    "columns = ['addcontr_date','rationale_type','rationale','contract_id','date_signed','contract_end','tender_id','procuring_entity','contract_value','region','tenderURL']\n",
    "\n",
    "data_addcontr_gas_short = pd.read_excel('02_data/addcontr_gas_1mln_v1.xlsx')[columns]\n",
    "data_addcontr_gas_short.loc[:,'date_signed'] = data_addcontr_gas_short.loc[:,'date_signed'].map(lambda x: x.date())\n",
    "data_addcontr_gas_short.loc[:,'addcontr_date'] = data_addcontr_gas_short.loc[:,'addcontr_date'].map(lambda x: x.date() if x != 'unknown' else x)\n",
    "data_addcontr_gas_short.loc[:,'contract_end'] = data_addcontr_gas_short.loc[:,'contract_end'].map(lambda x: x.date() if x != 'not specified' else x)\n",
    "\n",
    "data_addcontr_gas_short.loc[:,'price_start'] = \"\"\n",
    "data_addcontr_gas_short.loc[:,'price_addcontr'] = \"\"\n",
    "data_addcontr_gas_short.loc[:,'row_added_manually'] = False\n",
    "\n",
    "print(data_addcontr_gas_short.shape)\n",
    "data_addcontr_gas_short.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''\n",
    "Function to create an extract based on randomly selected contract ids,\n",
    "also makes a copy of docs for respective contract ids,\n",
    "which later will be sent to a person (student or volunteer), who is respobsible for manual work with the data.\n",
    "\n",
    "Please, take into account, that only a tiny sample of docs is uploaded to github,\n",
    "so this function will probably not work from the very beginning.\n",
    "You can fix this by dowloading all the docs from ProZorro with the script 00_addcontr_get_data.\n",
    "It is more than 10 Gb of data and it might take several hours to download all of them.\n",
    "'''\n",
    "\n",
    "def create_xlsx_and_filecopies(name, random_contracts):\n",
    "    number = 1\n",
    "    filename = '%s%i.xlsx' % (name, number)\n",
    "\n",
    "    while filename in os.listdir('03_for_students/01_distribution_data'):\n",
    "        number += 1\n",
    "        filename = '%s%i.xlsx' % (name, number)\n",
    "        \n",
    "    writer = pd.ExcelWriter('03_for_students/01_distribution_data/%s' % filename)\n",
    "    data_addcontr_gas_short_fordistr.loc[data_addcontr_gas_short_fordistr['contract_id'].isin(random_contracts)].to_excel(writer,'contracts_short',index = False)\n",
    "    pd.DataFrame(columns = ['contract_id','month','year','amount_1000m3','supply_implied']).to_excel(writer,'schedule',index = False)\n",
    "    pd.DataFrame(columns = ['contract_id','contract_value','price_start','total_supply','months_of_supply','monthly_supply']).to_excel(writer,'imply_supply',index = False)\n",
    "    writer.save()\n",
    "    print(\"Збережено тестове завдання \" + filename)\n",
    "    \n",
    "    base_folder = '00_contracts_pdf'\n",
    "    foldername = filename.split('.')[0]\n",
    "    destination_folder = os.path.join('03_for_students/01_distribution_data',foldername)\n",
    "    \n",
    "    for random_contract in random_contracts:\n",
    "        date_signed = data_addcontr_gas_short_fordistr.loc[data_addcontr_gas_short_fordistr['contract_id'] == random_contract].iloc[0,:]['date_signed']\n",
    "        current_id = data_addcontr_gas_short_fordistr.loc[data_addcontr_gas_short_fordistr['contract_id'] == random_contract].iloc[0,:]['contract_id']\n",
    "        source = os.path.join(base_folder,str(date_signed),current_id)\n",
    "        destination = os.path.join(destination_folder,str(date_signed),current_id)\n",
    "\n",
    "        for file in os.listdir(source):\n",
    "            if not os.path.exists(destination):\n",
    "                os.makedirs(destination)\n",
    "            try:\n",
    "                copy(os.path.join(source,file),destination)\n",
    "            except FileNotFoundError:\n",
    "                print(\"Не вдалося скопіювати файл %s (шлях %s)\" % (file, destination))\n",
    "            \n",
    "    return filename"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Розподілено 354 договорів\n",
      "Для розподілу доступно 5081 рядків\n",
      "['64c5d17bccad44229c99432280fdca81', '4ebb5b5ca700461da9d3987a3ddda5fd', '0ba29c5123b941b0a01a1b1a62389a68', '2961792da4814646bda187a5aba1cf10', '41f206eb5c1d4e35b38c3d628cc419cc']\n",
      "Збережено тестове завдання addcontr_gas_test23.xlsx\n",
      "Оновлено: розподілено 359 договорів\n"
     ]
    }
   ],
   "source": [
    "'''\n",
    "Randomly select 5 contracts for creating a test task for a student or volunteer,\n",
    "only takes data, which has not been distributed already\n",
    "'''\n",
    "used_contract_ids = pd.read_csv('03_for_students/distributed_ids.csv')\n",
    "print(\"Розподілено %i договорів\" %(len(used_contract_ids)))\n",
    "\n",
    "data_addcontr_gas_short_fordistr = data_addcontr_gas_short.loc[~data_addcontr_gas_short['contract_id'].isin(used_contract_ids['contract_id'])]\n",
    "print(\"Для розподілу доступно %i рядків\" %len(data_addcontr_gas_short_fordistr))\n",
    "\n",
    "random_contracts = []\n",
    "\n",
    "while len(random_contracts) < 5:\n",
    "    chosen = random.choice(data_addcontr_gas_short_fordistr['contract_id'].unique())\n",
    "    if not chosen in random_contracts:\n",
    "        random_contracts.append(chosen)\n",
    "print(random_contracts)\n",
    "\n",
    "filename = create_xlsx_and_filecopies('addcontr_gas_test', random_contracts)\n",
    "filenames = [filename for contract in random_contracts]\n",
    "\n",
    "used_contract_ids = used_contract_ids.append(pd.DataFrame({'contract_id':random_contracts,'document_name':filenames}), ignore_index = True)\n",
    "used_contract_ids.to_csv('03_for_students/distributed_ids.csv', index = False)\n",
    "\n",
    "print(\"Оновлено: розподілено %i договорів\" %(len(used_contract_ids)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Розподілено 1195 договорів\n",
      "Для розподілу доступно 182 рядків\n",
      "['852ee7757e8248f8a127dde4d964d9ab', 'fb80d6eb1c534698b463b2428f51d2b4', '969afe72136d4a97a678f4e52d941993', 'c1bac663a35f4c4ba54df791a85c7560', 'e879b98bbebd471286c315182346998f', '700359ac7ee34f88a3afb1df0f2585eb', '5ea45bf8db4b4b07a773c98439e47b4c', 'ec20f3f8f7794c10b2dd5181fa7a97de', '6b8b4c3d08404a07ba59e4bb6b6a407e', '1c4f45261fd24389a91a32f0654fd927', '6f049658df4b460592f6a04f10fbdd1e', '157ad459e9f342fbbfd7c5274be2a7f0', '3c547fc341f847759c71eaadcf9939fc', 'b37544508b0a467ab4ab76106295b8a3', 'b53af271b58a4aa9a37818f24fdaf44b', 'a1611ddfea254ea3956b80d4bfe840ae', '9574c968a51746f3b3cad7359a31b5c5', 'e2eb526a02aa4299a6abd4b3554c35d9', '3217b2b3dad345acbe5d43fdc58b8ebe', '91b52380228947bebff552cf660685e0', '9e21b15d979d400fb2d95aed775695c6', '27b49557cb314088ba2923cd62874235', 'b65c5a0d03e243e5bd9695abf4f1c30b', '50eeec3c096f4367be088248d60f0caa', 'b3eeeebb5c314b4dab1b37e6c97dc285', '9dd81ae8442b4fcbbc5eece6bad8e1d0', 'd69b2c962aa148fe8f44d7eb060e72dd', 'ebcc60cdc64c40abb6e433aef6506852', 'b1ea5d3256bd4acca529a781ad369034', '529d19137a264a59b6022f53bf292ccc']\n",
      "Збережено тестове завдання addcontr_gas_extract32.xlsx\n",
      "Оновлено: розподілено 1225 договорів\n"
     ]
    }
   ],
   "source": [
    "'''\n",
    "Randomly select approximately 200 rows of data for creating a standard task for a student or volunteer,\n",
    "only takes data, which has not been distributed already\n",
    "'''\n",
    "used_contract_ids = pd.read_csv('03_for_students/distributed_ids.csv')\n",
    "print(\"Розподілено %i договорів\" %(len(used_contract_ids)))\n",
    "\n",
    "data_addcontr_gas_short_fordistr = data_addcontr_gas_short.loc[~data_addcontr_gas_short['contract_id'].isin(used_contract_ids['contract_id'])]\n",
    "print(\"Для розподілу доступно %i рядків\" %len(data_addcontr_gas_short_fordistr))\n",
    "\n",
    "random_contracts = []\n",
    "\n",
    "if len(data_addcontr_gas_short_fordistr) >= 200:\n",
    "    while len(data_addcontr_gas_short_fordistr.loc[data_addcontr_gas_short_fordistr['contract_id'].isin(random_contracts)]) < 200:\n",
    "        chosen = random.choice(data_addcontr_gas_short_fordistr['contract_id'].unique())\n",
    "        if not chosen in random_contracts:\n",
    "            random_contracts.append(chosen)\n",
    "else:\n",
    "    random_contracts = list(data_addcontr_gas_short_fordistr['contract_id'].unique())\n",
    "\n",
    "print(random_contracts)\n",
    "\n",
    "filename = create_xlsx_and_filecopies('addcontr_gas_extract', random_contracts)\n",
    "filenames = [filename for contract in random_contracts]\n",
    "\n",
    "used_contract_ids = used_contract_ids.append(pd.DataFrame({'contract_id':random_contracts,'document_name':filenames}), ignore_index = True)\n",
    "used_contract_ids.to_csv('03_for_students/distributed_ids.csv', index = False)\n",
    "\n",
    "print(\"Оновлено: розподілено %i договорів\" %(len(used_contract_ids)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Кількість contract_id у списку розподілених: 1225\n",
      "Кількість унікальних contract_id у списку розподілених: 1225\n"
     ]
    }
   ],
   "source": [
    "'''\n",
    "Need this code to double check, if no contract ids were distributed twice\n",
    "Is used in case of baseless paranoia\n",
    "'''\n",
    "used_contract_ids_test = pd.read_csv('03_for_students/distributed_ids.csv')\n",
    "print(\"Кількість contract_id у списку розподілених: %i\" % len(used_contract_ids_test))\n",
    "print(\"Кількість унікальних contract_id у списку розподілених: %i\" %len(used_contract_ids_test['contract_id'].unique()))"
   ]
  }
 ],
 "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
}