template_red_flags_checks_registry.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "sfg_SbQWBCmW"
      },
      "source": [
        "## Setup\n",
        "\n",
        "*You must run the cells in this section each time you connect to a new runtime. For example, when you return to the notebook after an idle timeout, when the runtime crashes, or when you restart or factory reset the runtime.*"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "sowWi_Ve_Lm3"
      },
      "source": [
        "Install requirements (*Note: ocdskingfishercolab installs google-colab, which expects specific versions of pandas and numpy*):\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "X4nmyvOa_Ls7"
      },
      "outputs": [],
      "source": [
        "! pip install --upgrade pip > pip.log\n",
        "! pip install --upgrade 'ocdskingfishercolab<0.4' ipywidgets psycopg2-binary >> pip.log"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "tezDNj4uAOHq"
      },
      "outputs": [],
      "source": [
        "# @title Import packages and load extensions { display-mode: \"form\" }\n",
        "\n",
        "import gzip\n",
        "import json\n",
        "import os\n",
        "import shutil\n",
        "import tempfile\n",
        "from collections import Counter\n",
        "from datetime import datetime, timezone\n",
        "from pathlib import Path\n",
        "\n",
        "import numpy as np\n",
        "import pandas as pd\n",
        "from dateutil.relativedelta import relativedelta\n",
        "from google.colab.data_table import DataTable\n",
        "from google.colab.files import download\n",
        "from ipywidgets import widgets\n",
        "from ocdskingfishercolab import (\n",
        "    authenticate_gspread,\n",
        "    calculate_coverage,\n",
        "    download_dataframe_as_csv,\n",
        "    format_thousands,\n",
        "    render_json,\n",
        "    save_dataframe_to_sheet,\n",
        "    save_dataframe_to_spreadsheet,\n",
        "    set_dark_mode,\n",
        "    set_light_mode,\n",
        "    set_spreadsheet_name,\n",
        ")\n",
        "\n",
        "# Load https://pypi.org/project/ipython-sql/\n",
        "%load_ext sql\n",
        "# Load https://colab.research.google.com/notebooks/data_table.ipynb\n",
        "%load_ext google.colab.data_table"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "--8vgOiP_58f"
      },
      "outputs": [],
      "source": [
        "# @title Configure the notebook environment { display-mode: \"form\" }\n",
        "\n",
        "# Increase max columns so that Pandas DataFrames with many columns are rendered as data tables.\n",
        "DataTable.max_columns = 50\n",
        "# Remove the index from data tables for easier copy-pasting to Google Docs.\n",
        "DataTable.include_index = False\n",
        "\n",
        "# Return Pandas DataFrames instead of regular result sets.\n",
        "%config SqlMagic.autopandas = True\n",
        "# Don't print number of rows affected.\n",
        "%config SqlMagic.feedback = False\n",
        "\n",
        "# If you set Tools > Settings > Site > Theme to dark, uncomment this line.\n",
        "# set_dark_mode()\n",
        "# If you are creating plots to copy-paste into reports, uncomment this line.\n",
        "# set_light_mode()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "25lVf3PwsmmV"
      },
      "source": [
        "## Setup Cardinal"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "6fzwlrSLOF3w"
      },
      "source": [
        "### Install Cardinal"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "1H6SWBCTDq5G"
      },
      "source": [
        "This notebook uses [Cardinal](https://cardinal.readthedocs.io/en/latest/), a Rust package to calculate red flags and the coverage of OCDS data."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "6syz0fkkEdgj"
      },
      "outputs": [],
      "source": [
        "# @title Install { display-mode: \"form\" }\n",
        "\n",
        "! curl -sSOL https://github.com/open-contracting/cardinal-rs/releases/download/0.0.5/ocdscardinal-0.0.5-linux-64-bit.zip\n",
        "! unzip -oj ocdscardinal-0.0.5-linux-64-bit.zip ocdscardinal-0.0.5-linux-64-bit/ocdscardinal\n",
        "\n",
        "def cardinal_calculate_coverage(file_name):\n",
        "    coverage = !./ocdscardinal coverage $file_name\n",
        "    fields = (\n",
        "        pd.DataFrame.from_dict(json.loads(coverage[0]), orient=\"index\", columns=[\"count\"])\n",
        "        .reset_index()\n",
        "        .rename(columns={\"index\": \"path\"})\n",
        "    )\n",
        "    # Leaves only object members\n",
        "    fields_table = fields[fields.path.str.contains(\"[a-z]$\")].copy()\n",
        "    fields_table[\"path\"] = fields_table[\"path\"].str.replace(r\"[][]|^/\", \"\", regex=True)\n",
        "    return fields_table"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "vpPltvi1tjEs"
      },
      "source": [
        "## Setup download data from the Data Registry"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "CLc-_oJntg93"
      },
      "outputs": [],
      "source": [
        "# @title Data registry functions{ display-mode: \"form\" }\n",
        "import requests\n",
        "\n",
        "DATA_REGISTRY_BASE_URL = \"https://data.open-contracting.org/en/\"\n",
        "PUBLICATIONS_URL = f\"{DATA_REGISTRY_BASE_URL}publications.json\"\n",
        "\n",
        "\n",
        "def get_publications():\n",
        "    publications = requests.get(PUBLICATIONS_URL, timeout=10).json()\n",
        "    for publication in publications:\n",
        "        publication[\"label\"] = f\"{publication['country']} - {publication['title']}\"\n",
        "    return publications\n",
        "\n",
        "\n",
        "def get_publication_select_box():\n",
        "    return widgets.Dropdown(\n",
        "        options=sorted([entry[\"label\"] for entry in get_publications()]),\n",
        "        description=\"Publication:\",\n",
        "        disabled=False,\n",
        "    )\n",
        "\n",
        "\n",
        "def get_available_years(publication):\n",
        "    years = [\"full\"]\n",
        "    if publication[\"date_from\"] and publication[\"date_to\"]:\n",
        "        year_from = int(publication[\"date_from\"][:4])\n",
        "        year_to = int(publication[\"date_to\"][:4])\n",
        "        years.extend(list(range(year_from, year_to + 1)))\n",
        "    return years\n",
        "\n",
        "\n",
        "def get_years_select_box(publication_select_box):\n",
        "    selected_publication = next(\n",
        "        entry for entry in get_publications() if entry[\"label\"] == publication_select_box.value\n",
        "    )\n",
        "    return (\n",
        "        widgets.Dropdown(\n",
        "            options=get_available_years(selected_publication),\n",
        "            description=\"Year:\",\n",
        "            disabled=False,\n",
        "        ),\n",
        "        selected_publication,\n",
        "    )\n",
        "\n",
        "\n",
        "def download_file(selected_publication, selected_year):\n",
        "    file_name = f\"{selected_publication['source_id']}-{selected_year}.jsonl\"\n",
        "    download_url = (\n",
        "        f'{DATA_REGISTRY_BASE_URL}publication/{selected_publication[\"id\"]}/download?name={selected_year}.jsonl.gz'\n",
        "    )\n",
        "    response = requests.get(download_url, timeout=10)\n",
        "    with tempfile.NamedTemporaryFile() as gz_file:\n",
        "        gz_file.write(response.content)\n",
        "        with gzip.open(gz_file.name) as i, Path(file_name).open(\"wb\") as o:\n",
        "            shutil.copyfileobj(i, o)\n",
        "    return file_name"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "vpPltvi1tjEs"
      },
      "source": [
        "## Select a publication to download from the [Data Registry](https://data.open-contracting.org/) and generates its field list"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "CLc-_oJntg93"
      },
      "outputs": [],
      "source": [
        "# @title Select the publication to download { display-mode: \"form\" }\n",
        "\n",
        "publication_select_box = get_publication_select_box()\n",
        "\n",
        "publication_select_box"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "collapsed": false
      },
      "outputs": [],
      "source": [
        "# @title Select year to download or \"full\" to download all the available years { display-mode: \"form\" }\n",
        "\n",
        "selected_year, selected_publication = get_years_select_box(publication_select_box)\n",
        "selected_year"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "collapsed": false
      },
      "outputs": [],
      "source": [
        "# @title Download the selected file { display-mode: \"form\" }\n",
        "\n",
        "file_name = download_file(selected_publication, selected_year.value)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "collapsed": false
      },
      "outputs": [],
      "source": [
        "# @title Extract the list of available fields { display-mode: \"form\" }\n",
        "\n",
        "fields_table = cardinal_calculate_coverage(file_name)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "s75VrpzLRNkx"
      },
      "source": [
        "## Red flags analysis setup"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "pSR2il4VRPmJ"
      },
      "source": [
        "Use this section to setup the functions needed to perform a usability analysis of the dataset, to identify if a publisher has the necessary fields to calculate 73 red flags indicators. "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "zVnZn4E1kHg1"
      },
      "outputs": [],
      "source": [
        "# @title Red flags functions { display-mode: \"form\" }\n",
        "def check_red_flags_indicators(result):\n",
        "\n",
        "    gc = authenticate_gspread()\n",
        "\n",
        "    # NEW Red Flags to OCDS mapping #Public\n",
        "    worksheet = gc.open_by_key(\"1GACSPd64X5Tm-nu6LKttyEpaEp1CLsaCUGrEutljnFU\").get_worksheet(1)\n",
        "\n",
        "    # get_all_values gives a list of rows.\n",
        "    rows = worksheet.get_all_values()\n",
        "    # Convert to a DataFrame and render.\n",
        "\n",
        "    indicators = pd.DataFrame(rows)\n",
        "    indicators = indicators.rename(columns=indicators.iloc[0]).drop(indicators.index[0])\n",
        "    indicatorsdf = indicators.iloc[:, [0, 5, 6, 7]]\n",
        "\n",
        "    return result.merge(indicatorsdf, on=\"R_id\")\n",
        "\n",
        "\n",
        "def most_common_fields_to_calculate_indicators(indicators_dict, fields_table):\n",
        "    fields = list(indicators_dict.values())\n",
        "    fields = [item[1:] for item in fields]\n",
        "    flat_list = [item for sublist in [item for sublist in fields for item in sublist] for item in sublist]\n",
        "    fields_list = Counter(flat_list)\n",
        "\n",
        "    fields_count = (\n",
        "        pd.DataFrame.from_dict(fields_list, orient=\"index\")\n",
        "        .reset_index()\n",
        "        .rename(columns={\"index\": \"field\", 0: \"number of indicators\"})\n",
        "    )\n",
        "\n",
        "    fields_count = fields_count.sort_values(\"number of indicators\", ascending=False).reset_index(drop=True)\n",
        "    fields_count[\"published\"] = np.where(fields_count[\"field\"].isin(fields_table[\"path\"]), \"yes\", \"no\")\n",
        "\n",
        "    return fields_count\n",
        "\n",
        "\n",
        "def get_coverage(indicators_dic):\n",
        "    coverage = []\n",
        "    for i in indicators_dic.values():\n",
        "        fields = [item for sublist in i for item in sublist][1:]\n",
        "        result = calculate_coverage(fields, \"release_summary\")\n",
        "        result_value = pd.to_numeric(result[\"total_percentage\"][0])\n",
        "        coverage.append(result_value)\n",
        "    return coverage\n",
        "\n",
        "\n",
        "def get_red_flags_dictionary(fields_list):\n",
        "    \"\"\"\n",
        "    To calculate some indicators there are alternative fields that can be used, for example to calculate the number of tenderers  both the `tender/numberOfTenderers` or the `tender/tenderers/id` could be used.  This section checks which fields are available in the publication.\n",
        "    \"\"\"\n",
        "    # buyers\n",
        "    buyer = [\"buyer/name\", \"buyer/id\"]\n",
        "    procuring = [\"tender/procuringEntity/name\", \"tender/procuringEntity/id\"]\n",
        "    parties = [\"parties/name\", \"parties/id\", \"parties/roles\"]\n",
        "    if not any(item not in fields_list for item in buyer):\n",
        "        buyer_var = buyer\n",
        "    elif not any(item not in fields_list for item in procuring):\n",
        "        buyer_var = procuring\n",
        "    elif not any(item not in fields_list for item in parties):\n",
        "        buyer_var = parties\n",
        "    else:\n",
        "        buyer_var = buyer\n",
        "\n",
        "    # bidders\n",
        "    if \"tender/tenderers/id\" in fields_list:\n",
        "        bidders_val = \"tender/tenderers/id\"\n",
        "    elif \"bids/details/tenderers/id\" in fields_list:\n",
        "        bidders_val = \"bids/details/tenderers/id\"\n",
        "    else:\n",
        "        bidders_val = \"tender/tenderers/id\"\n",
        "\n",
        "    # number of tendereres\n",
        "    if \"tender/numberOfTenderers\" in fields_list:\n",
        "        bidders_val2 = \"tender/numberOfTenderers\"\n",
        "    elif \"tender/tenderers/id\" in fields_list:\n",
        "        bidders_val2 = \"tender/tenderers/id\"\n",
        "    elif \"bids/details/tenderers/id\" in fields_list:\n",
        "        bidders_val2 = \"bids/details/tenderers/id\"\n",
        "    elif \"bids/statistics/value\" in fields_list:\n",
        "        bidders_val2 = \"bids/statistics/value\"\n",
        "    else:\n",
        "        bidders_val2 = \"tender/numberOfTenderers\"\n",
        "\n",
        "    # suppliers\n",
        "    supplier = [\"awards/suppliers/id\"]\n",
        "    parties = [\"parties/id\", \"parties/roles\"]\n",
        "    if not any(item not in fields_list for item in supplier):\n",
        "        sup_var = supplier\n",
        "    elif not any(item not in fields_list for item in parties):\n",
        "        sup_var = parties\n",
        "    else:\n",
        "        sup_var = supplier\n",
        "\n",
        "    if \"awards/suppliers/id\" in fields_list:\n",
        "        sup_val = \"awards/suppliers/id\"\n",
        "    elif \"bids/details/tenderers/id\" in fields_list:\n",
        "        bidders_val = \"bids/details/tenderers/id\"\n",
        "    else:\n",
        "        bidders_val = \"tender/tenderers/id\"\n",
        "\n",
        "    # awards and contracts fields\n",
        "    aw = [\"awards/status\", \"awards/date\", \"awards/value/amount\", \"awards/value/currency\"]\n",
        "    con = [\"contracts/status\", \"contracts/dateSigned\", \"contracts/value/amount\", \"contracts/value/currency\"]\n",
        "    if not any(item not in fields_list for item in aw):\n",
        "        awards_val3 = aw\n",
        "    elif not any(item not in fields_list for item in con):\n",
        "        awards_val3 = con\n",
        "    else:\n",
        "        awards_val3 = aw\n",
        "\n",
        "    # items\n",
        "    tender_it = [\"tender/items/classification/id\", \"tender/items/classification/scheme\"]\n",
        "    awards_it = [\"awards/items/classification/id\", \"awards/items/classification/scheme\"]\n",
        "    contracts_it = [\"contracts/items/classification/id\", \"contracts/items/classification/scheme\"]\n",
        "    if not any(item not in fields_list for item in tender_it):\n",
        "        item_var = tender_it\n",
        "    elif not any(item not in fields_list for item in awards_it):\n",
        "        item_var = awards_it\n",
        "    elif not any(item not in fields_list for item in contracts_it):\n",
        "        item_var = contracts_it\n",
        "    else:\n",
        "        item_var = tender_it\n",
        "\n",
        "    # unit items\n",
        "    unit_tender_it = [\"tender/items/unit/value/amount\", \"tender/items/unit/value/currency\"]\n",
        "    unit_awards_it = [\"awards/items/unit/value/amount\", \"awards/items/unit/value/amount\"]\n",
        "    unit_contracts_it = [\"contracts/items/unit/value/amount\", \"contracts/items/unit/value/amount\"]\n",
        "    if not any(item not in fields_list for item in unit_tender_it):\n",
        "        unit_item_var = unit_tender_it\n",
        "    elif not any(item not in fields_list for item in unit_awards_it):\n",
        "        unit_item_var = unit_awards_it\n",
        "    elif not any(item not in fields_list for item in unit_contracts_it):\n",
        "        unit_item_var = unit_contracts_it\n",
        "    else:\n",
        "        unit_item_var = unit_tender_it\n",
        "\n",
        "    # date fields\n",
        "    if \"tender/tenderPeriod/startDate\" in fields_list:\n",
        "        date_var = \"tender/tenderPeriod/startDate\"\n",
        "    elif \"awards/date\" in fields_list:\n",
        "        date_var = \"awards/date\"\n",
        "    else:\n",
        "        date_var = \"tender/tenderPeriod/startDate\"\n",
        "\n",
        "    # amounts fields\n",
        "    if \"tender/value/amount\" in fields_list:\n",
        "        amount_var = \"tender/value/amount\"\n",
        "    elif \"bids/details/value/amount\" in fields_list:\n",
        "        amount_var = \"bids/details/value/amount\"\n",
        "    elif \"awards/value/amount\" in fields_list:\n",
        "        amount_var = \"awards/value/amount\"\n",
        "    elif \"contracts/value/amount\" in fields_list:\n",
        "        amount_var = \"contracts/value/amount\"\n",
        "    else:\n",
        "        amount_var = \"bids/details/value/amount\"\n",
        "\n",
        "    # winning bids\n",
        "    win_bid = [\"bids/awards/relatedBid\"]\n",
        "    win_bid2 = [\"bids/details/tenderers/id\", \"awards/suppliers/id\"]\n",
        "    if not any(item not in fields_list for item in win_bid):\n",
        "        win_bid_var = win_bid\n",
        "    elif not any(item not in fields_list for item in win_bid2):\n",
        "        win_bid_var = win_bid2\n",
        "    else:\n",
        "        win_bid_var = win_bid\n",
        "\n",
        "    # bidder info\n",
        "    if \"parties/contactPoint/telephone\" in fields_list:\n",
        "        bidders_info_var = \"parties/contactPoint/telephone\"\n",
        "    elif \"parties/address/streetAddress\" in fields_list:\n",
        "        bidders_info_var = \"parties/address/streetAddress\"\n",
        "    elif \"parties/address/postalCode\" in fields_list:\n",
        "        bidders_info_var = \"parties/address/postalCode\"\n",
        "    else:\n",
        "        bidders_info_var = \"parties/contactPoint/telephone\"\n",
        "\n",
        "    # contact info\n",
        "    if \"parties/contactPoint/telephone\" in fields_list:\n",
        "        contact_info_var = \"parties/contactPoint/telephone\"\n",
        "    elif \"parties/contactPoint/email\" in fields_list:\n",
        "        contact_info_var = \"parties/contactPoint/email\"\n",
        "    elif \"parties/contactPoint/name\" in fields_list:\n",
        "        contact_info_var = \"parties/contactPoint/name\"\n",
        "    else:\n",
        "        contact_info_var = \"parties/contactPoint/telephone\"\n",
        "\n",
        "    # implementation values\n",
        "    imp_val = [\"contracts/implementation/finalValue/amount\", \"contracts/implementation/finalValue/currency\"]\n",
        "    imp_val2 = [\n",
        "        \"contracts/implementation/transactions/value/amount\",\n",
        "        \"contracts/implementation/transactions/value/currency\",\n",
        "    ]\n",
        "    if not any(item not in fields_list for item in imp_val):\n",
        "        imp_val_var = imp_val\n",
        "    elif not any(item not in fields_list for item in imp_val2):\n",
        "        imp_val_var = imp_val2\n",
        "    else:\n",
        "        imp_val_var = imp_val\n",
        "\n",
        "    return {\n",
        "        \"Planning documents not available\": [[\"R001\"], [\"planning/documents/documentType\"]],\n",
        "        \"Manipulation of procurement thresholds\": [\n",
        "            [\"R002\"],\n",
        "            [\n",
        "                \"tender/value/amount\",\n",
        "                \"tender/value/currency\",\n",
        "                \"tender/procurementMethod\",\n",
        "                \"tender/tenderPeriod/startDate\",\n",
        "                *buyer_var,\n",
        "            ],\n",
        "        ],\n",
        "        \" The submission period is too short\": [\n",
        "            [\"R003\"],\n",
        "            [\"tender/tenderPeriod/startDate\", \"tender/tenderPeriod/endDate\", \"tender/procurementMethod\"],\n",
        "        ],\n",
        "        \"Failure to adequately advertise the request for bids\": [\n",
        "            [\"R004\"],\n",
        "            [\"tender/documents/documentType\", \"tender/documents/datePublished\", \"tender/tenderPeriod/startDate\"],\n",
        "        ],\n",
        "        \"Key tender information and documents are not available\": [\n",
        "            [\"R005\"],\n",
        "            [\n",
        "                \"tender/documents/documentType\",\n",
        "                \"tender/documents/datePublished\",\n",
        "                \"tender/tenderPeriod/startDate\",\n",
        "                \"tender/tenderPeriod/endDate\",\n",
        "            ],\n",
        "        ],\n",
        "        \"Unreasonable prequalification requirements\": [[\"R006\"], [\"tender/eligibilityCriteria\"]],\n",
        "        \"Unreasonable technical specifications\": [\n",
        "            [\"R007\"],\n",
        "            [\n",
        "                \"tender/documents/documentType\",\n",
        "                \"tender/procurementMethod\",\n",
        "                \"tender/items/classification/id\",\n",
        "                \"tender/items/classification/scheme\",\n",
        "                *buyer_var,\n",
        "                \"tender/value/amount\",\n",
        "            ],\n",
        "        ],\n",
        "        \"Unreasonable participation fees\": [\n",
        "            [\"R008\"],\n",
        "            [\n",
        "                \"tender/participationFees/value/amount\",\n",
        "                \"tender/participationFees/value/currency\",\n",
        "                \"tender/value/amount\",\n",
        "            ],\n",
        "        ],\n",
        "        \"Buyer increases the cost of the bidding documents\": [\n",
        "            [\"R009\"],\n",
        "            [\"tender/participationFees/value/amount\", \"tender/participationFees/value/currency\", \"date\"],\n",
        "        ],\n",
        "        \"Unjustified use of non competitive procedure\": [\n",
        "            [\"R010\"],\n",
        "            [\"tender/procurementMethod\", \"tender/procurementMethodDetails\", \"tender/procurementMethodRationale\"],\n",
        "        ],\n",
        "        \"Splitting purchases to avoid procurement thresholds\": [\n",
        "            [\"R011\"],\n",
        "            [\n",
        "                \"tender/procurementMethod\",\n",
        "                *item_var,\n",
        "                \"tender/value/amount\",\n",
        "                \"tender/value/currency\",\n",
        "                \"tender/tenderPeriod/startDate\",\n",
        "                *buyer_var,\n",
        "            ],\n",
        "        ],\n",
        "        \"Direct awards in contravention of the provisions of the procurement plan\": [\n",
        "            [\"R012\"],\n",
        "            [\"tender/procurementMethod\", \"tender/procurementMethodDetails\", \"planning/documents/documentType\"],\n",
        "        ],\n",
        "        \"High use of non competitive methods\": [[\"R013\"], [\"tender/procurementMethod\", *buyer_var]],\n",
        "        \"Short time between tender advertising and bid opening\": [\n",
        "            [\"R014\"],\n",
        "            [\"tender/tenderPeriod/startDate\", \"tender/bidOpening/date\", \"tender/procurementMethod\"],\n",
        "        ],\n",
        "        \"Long time between bid opening and bid evaluation\": [\n",
        "            [\"R015\"],\n",
        "            [\"tender/bidOpening/date\", \"tender/awardPeriod/startDate\", \"tender/procurementMethod\"],\n",
        "        ],\n",
        "        \"Tender value is higher or lower than average for this item category\": [\n",
        "            [\"R016\"],\n",
        "            [\"tender/value/amount\", \"tender/value/currency\", *item_var, \"tender/procurementMethod\"],\n",
        "        ],\n",
        "        \"Unreasonably low or high line item\": [[\"R017\"], [*item_var, *unit_item_var]],\n",
        "        \"Single bid received\": [[\"R018\"], [\"tender/procurementMethod\", bidders_val2]],\n",
        "        \"Low number of bidders for item and procuring entity\": [\n",
        "            [\"R019\"],\n",
        "            [\"tender/procurementMethod\", *item_var, *buyer_var, bidders_val2],\n",
        "        ],\n",
        "        \"Tender has a complaint\": [[\"R020\"], [\"complaints/id\"]],\n",
        "        \"High use of discretionary evaluation criteria\": [[\"R021\"], [\"tender/awardCriteria\", *buyer_var]],\n",
        "        \"Wide disparity in bid prices\": [\n",
        "            [\"R022\"],\n",
        "            [\"bids/details/id\", \"bids/details/value/amount\", \"bids/details/value/currency\", \"bids/details/status\"],\n",
        "        ],\n",
        "        \"Fixed multiple bid prices\": [\n",
        "            [\"R023\"],\n",
        "            [\"bids/details/id\", \"bids/details/value/amount\", \"bids/details/value/currency\", \"bids/details/status\"],\n",
        "        ],\n",
        "        \"Price close to winning bid\": [\n",
        "            [\"R024\"],\n",
        "            [\n",
        "                \"bids/details/id\",\n",
        "                \"bids/details/value/amount\",\n",
        "                \"bids/details/value/currency\",\n",
        "                \"bids/details/status\",\n",
        "                *win_bid_var,\n",
        "            ],\n",
        "        ],\n",
        "        \"Excessive unsuccessful bids\": [[\"R025\"], [\"awards/suppliers/id\", \"bids/details/status\", bidders_val]],\n",
        "        \"Prevalence of consortia\": [\n",
        "            [\"R026\"],\n",
        "            [\"awards/suppliers/id\", \"awards/suppliers/name\", \"awards/status\", \"awards/date\", *item_var],\n",
        "        ],\n",
        "        \"Missing bidders\": [[\"R027\"], [\"tender/procurementMethod\", *item_var, bidders_val, date_var]],\n",
        "        \"Identical bid prices\": [\n",
        "            [\"R028\"],\n",
        "            [\n",
        "                \"bids/details/id\",\n",
        "                \"bids/details/value/amount\",\n",
        "                \"bids/details/value/currency\",\n",
        "                \"bids/details/tenderers/id\",\n",
        "            ],\n",
        "        ],\n",
        "        \"Bid prices deviate from Benford’s Law\": [[\"R029\"], [amount_var, *item_var]],\n",
        "        \"Late bid won\": [\n",
        "            [\"R030\"],\n",
        "            [\n",
        "                \"bids/details/id\",\n",
        "                \"bids/details/date\",\n",
        "                \"bids/details/status\",\n",
        "                \"tender/tenderPeriod/endDate\",\n",
        "                *win_bid_var,\n",
        "            ],\n",
        "        ],\n",
        "        \"Winning bid price very close or higher than estimated price\": [\n",
        "            [\"R031\"],\n",
        "            [\n",
        "                \"bids/details/id\",\n",
        "                \"bids/details/value/amount\",\n",
        "                \"bids/details/value/currency\",\n",
        "                \"bids/details/status\",\n",
        "                \"tender/value/amount\",\n",
        "                \"tender/value/currency\",\n",
        "                *win_bid_var,\n",
        "            ],\n",
        "        ],\n",
        "        \"Bidders share same beneficial owner\": [\n",
        "            [\"R032\"],\n",
        "            [\"parties/roles\", \"parties/id\", \"parties/beneficialOwners/name\", \"parties/beneficialOwners/id\"],\n",
        "        ],\n",
        "        \"Bidders share same major shareholder\": [\n",
        "            [\"R033\"],\n",
        "            [\n",
        "                \"parties/roles\",\n",
        "                \"parties/id\",\n",
        "                \"parties/shareholders/shareholder/id\",\n",
        "                \"parties/shareholders/shareholding\",\n",
        "            ],\n",
        "        ],\n",
        "        \" Bids submitted in same order\": [\n",
        "            [\"R034\"],\n",
        "            [\n",
        "                \"bids/details/id\",\n",
        "                \"bids/details/date\",\n",
        "                \"bids/details/tenderers/id\",\n",
        "                \"bids/details/tenderers/name\",\n",
        "                \"bids/details/status\",\n",
        "            ],\n",
        "        ],\n",
        "        \"All except winning bid disqualified\": [\n",
        "            [\"R035\"],\n",
        "            [\"bids/details/id\", \"bids/details/status\", \"awards/status\", *win_bid_var],\n",
        "        ],\n",
        "        \"Lowest bid disqualified \": [\n",
        "            [\"R036\"],\n",
        "            [\n",
        "                \"tender/awardCriteria\",\n",
        "                \"bids/details/id\",\n",
        "                \"bids/details/value/amount\",\n",
        "                \"bids/details/value/currency\",\n",
        "                \"bids/details/status\",\n",
        "            ],\n",
        "        ],\n",
        "        \"Poorly supported disqualifications\": [\n",
        "            [\"R037\"],\n",
        "            [\n",
        "                \"tender/awardCriteria\",\n",
        "                \"bids/details/id\",\n",
        "                \"bids/details/value/amount\",\n",
        "                \"bids/details/value/currency\",\n",
        "                \"bids/details/status\",\n",
        "                \"bids/details/documents\",\n",
        "            ],\n",
        "        ],\n",
        "        \"Excessive disqualified bids\": [[\"R038\"], [\"bids/details/id\", \"bids/details/status\", bidders_val, *buyer_var]],\n",
        "        \"Unanswered bidder questions\": [\n",
        "            [\"R039\"],\n",
        "            [\"tender/enquiries/date\", \"tender/enquiries/dateAnswered\", \"tender/enquiries/answer\", \"tender/status\"],\n",
        "        ],\n",
        "        \"High share of buyers contracts\": [\n",
        "            [\"R040\"],\n",
        "            [*buyer_var, \"awards/status\", \"awards/date\", \"awards/suppliers/id\", \"awards/suppliers/name\"],\n",
        "        ],\n",
        "        \"Physical similarities in documents by different bidders\": [\n",
        "            [\"R041\"],\n",
        "            [\"bids/details/id\", \"bids/details/tenderers/id\", \"bids/documents/documentType\"],\n",
        "        ],\n",
        "        \"Bidder has abnormal address or phone number\": [[\"R042\"], [bidders_info_var]],\n",
        "        \"Bidder has same contact information as project official\": [\n",
        "            [\"R043\"],\n",
        "            [\"parties/roles\", \"parties/id\", contact_info_var],\n",
        "        ],\n",
        "        \"Business similarities between bidders\": [[\"R044\"], [\"parties/roles\", \"parties/id\", bidders_info_var]],\n",
        "        \"Bidder is not listed in  business registries\": [[\"R045\"], [\"parties/roles\", \"parties/id\"]],\n",
        "        \"Bidder is debarred or on sanctions list\": [[\"R046\"], [\"parties/roles\", \"parties/id\"]],\n",
        "        \"Supplier is not traceable on the web\": [\n",
        "            [\"R047\"],\n",
        "            [\"awards/suppliers/name\", \"awards/suppliers/id\", \"parties/contactPoint/url\"],\n",
        "        ],\n",
        "        \"Heterogeneous supplier\": [[\"R048\"], [*item_var, \"awards/suppliers/id\", \"awards/suppliers/name\"]],\n",
        "        \"Direct awards below threshold\": [\n",
        "            [\"R049\"],\n",
        "            [\"awards/suppliers/id\", \"awards/suppliers/name\", \"awards/date\", \"tender/procurementMethod\", *buyer_var],\n",
        "        ],\n",
        "        \" High market share\": [\n",
        "            [\"R050\"],\n",
        "            [\n",
        "                \"awards/suppliers/id\",\n",
        "                \"awards/suppliers/name\",\n",
        "                *buyer_var,\n",
        "                \"awards/value/amount\",\n",
        "                \"awards/value/currency\",\n",
        "                *item_var,\n",
        "                \"awards/date\",\n",
        "                \"awards/status\",\n",
        "            ],\n",
        "        ],\n",
        "        \"High market concentration\": [\n",
        "            [\"R051\"],\n",
        "            [\"awards/suppliers/id\", \"awards/suppliers/name\", *awards_val3, *item_var],\n",
        "        ],\n",
        "        \"Small initial purchase from supplier followed by much larger purchases\": [\n",
        "            [\"R052\"],\n",
        "            [\"awards/suppliers/id\", \"awards/suppliers/name\", \"tender/procurementMethod\", *buyer_var, *awards_val3],\n",
        "        ],\n",
        "        \"Co-bidding pairs have same recurrent winner\": [\n",
        "            [\"R053\"],\n",
        "            [\"bids/details/id\", \"bids/details/status\", *win_bid_var],\n",
        "        ],\n",
        "        \"Direct award followed by change orders  that exceed the competitive threshold\": [\n",
        "            [\"R054\"],\n",
        "            [\n",
        "                \"tender/procurementMethod\",\n",
        "                \"awards/value/amount\",\n",
        "                \"awards/value/currency\",\n",
        "                \"contracts/value/amount\",\n",
        "                \"contracts/value/currency\",\n",
        "                \"contracts/amendments/description\",\n",
        "            ],\n",
        "        ],\n",
        "        \"Multiple direct awards above or just below competitive threshold\": [\n",
        "            [\"R055\"],\n",
        "            [\"tender/procurementMethod\", \"awards/suppliers/id\", \"awards/suppliers/name\", *awards_val3, *buyer_var],\n",
        "        ],\n",
        "        \"Winning bid does not meet the award criteria\": [\n",
        "            [\"R056\"],\n",
        "            [\"tender/awardCriteria\", \"bids/details/status\", \"bids/details/documents\", *win_bid_var],\n",
        "        ],\n",
        "        \" Bid rotation\": [\n",
        "            [\"R057\"],\n",
        "            [\n",
        "                \"bids/details/tenderers/id\",\n",
        "                \"bids/details/tenderers/name\",\n",
        "                \"awards/suppliers/id\",\n",
        "                \"awards/suppliers/name\",\n",
        "                \"bids/details/value/amount\",\n",
        "                \"bids/details/value/currency\",\n",
        "                *item_var,\n",
        "            ],\n",
        "        ],\n",
        "        \"Heavily discounted bid\": [\n",
        "            [\"R058\"],\n",
        "            [\n",
        "                \"bids/details/id\",\n",
        "                \"bids/details/value/amount\",\n",
        "                \"bids/details/value/currency\",\n",
        "                \"bids/details/status\",\n",
        "                *win_bid_var,\n",
        "            ],\n",
        "        ],\n",
        "        \"Large difference between the award value and final contract amount\": [\n",
        "            [\"R059\"],\n",
        "            [\n",
        "                \"awards/id\",\n",
        "                \"awards/status\",\n",
        "                \"awards/value/amount\",\n",
        "                \"awards/value/currency\",\n",
        "                \"contracts/awardID\",\n",
        "                \"contracts/value/amount\",\n",
        "                \"contracts/value/currency\",\n",
        "                \"contracts/status\",\n",
        "            ],\n",
        "        ],\n",
        "        \"Long time between award date and contract signature date\": [\n",
        "            [\"R060\"],\n",
        "            [\"awards/date\", \"contracts/dateSigned\", \"tender/procurementMethod\"],\n",
        "        ],\n",
        "        \"Decision period extremely short\": [\n",
        "            [\"R061\"],\n",
        "            [\"tender/tenderPeriod/endDate\", \"awards/date\", \"tender/procurementMethod\"],\n",
        "        ],\n",
        "        \"Decision period extremely long\": [\n",
        "            [\"R062\"],\n",
        "            [\"tender/tenderPeriod/endDate\", \"awards/date\", \"tender/procurementMethod\"],\n",
        "        ],\n",
        "        \"Contract is not published\": [[\"R063\"], [\"contracts/documents/documentType\"]],\n",
        "        \"Contract has modifications\": [[\"R064\"], [\"contracts/status\", \"contracts/amendments/description\"]],\n",
        "        \"Contract amendments to reduce line items\": [\n",
        "            [\"R065\"],\n",
        "            [\"contracts/status\", \"contracts/amendments/description\", \"contracts/amendments/rationale\"],\n",
        "        ],\n",
        "        \"Contract amendments to increase line items\": [\n",
        "            [\"R066\"],\n",
        "            [\"contracts/status\", \"contracts/amendments/description\", \"contracts/amendments/rationale\"],\n",
        "        ],\n",
        "        \"Delivery failure\": [\n",
        "            [\"R067\"],\n",
        "            [\n",
        "                \"contracts/implementation/milestones/type\",\n",
        "                \"contracts/implementation/milestones/dueDate\",\n",
        "                \"contracts/implementation/milestones/dateMet\",\n",
        "            ],\n",
        "        ],\n",
        "        \"Contract transactions exceed contract amount\": [\n",
        "            [\"R068\"],\n",
        "            [\"contracts/value/amount\", \"contracts/value/currency\", *imp_val_var],\n",
        "        ],\n",
        "        \"Contract amendments to increase price\": [\n",
        "            [\"R069\"],\n",
        "            [\"contracts/status\", \"contracts/amendments/description\", \"contracts/amendments/rationale\"],\n",
        "        ],\n",
        "        \"Losing bidders are hired as subcontractors\": [\n",
        "            [\"R070\"],\n",
        "            [\n",
        "                \"contracts/relatedProcesses\",\n",
        "                \"contracts/relatedProcesses/relationship\",\n",
        "                \"awards/suppliers/id\",\n",
        "                bidders_val,\n",
        "            ],\n",
        "        ],\n",
        "        \"A contractor subcontracts all or most of the work received\": [\n",
        "            [\"R071\"],\n",
        "            [\"awards/hasSubcontracting\", \"awards/subcontracting/minimumPercentage\"],\n",
        "        ],\n",
        "        \"High prevalence of subcontracts\": [[\"R072\"], [\"awards/hasSubcontracting\", *buyer_var]],\n",
        "        \"Discrepancies between work completed and contract specifications\": [\n",
        "            [\"R073\"],\n",
        "            [\n",
        "                \"contracts/status\",\n",
        "                \"contracts/documents/documentType\",\n",
        "                \"contracts/implementation/documents/documentType\",\n",
        "            ],\n",
        "        ],\n",
        "    }\n",
        "\n",
        "\n",
        "def redflags_checks(fields_list, indicators_dic, check_coverage=False):\n",
        "    \"\"\"Returns a table of the usability checks.\n",
        "    It indicates if the fields needed to calculate a particular indicator are present.\n",
        "    Set check_coverage=True to check for coverage\"\"\"\n",
        "\n",
        "    results_list = []\n",
        "    missing_fields = []\n",
        "\n",
        "    for i in indicators_dic.values():\n",
        "        check = any(item not in fields_list for item in i[1])\n",
        "        result = \"missing fields\" if check else \"possible to calculate\"\n",
        "        missing = [i[1][j] for j in range(len(i[1])) if i[1][j] not in fields_list]\n",
        "        missing_fields.append(missing)\n",
        "        results_list.append(result)\n",
        "\n",
        "    # Generate dataframe\n",
        "\n",
        "    indicatordf = pd.DataFrame(\n",
        "        list(\n",
        "            zip(\n",
        "                list(indicators_dic),\n",
        "                [indicators_dic[i][0] for i in indicators_dic],\n",
        "                [indicators_dic[i][1:] for i in indicators_dic],\n",
        "                strict=True,\n",
        "            )\n",
        "        ),\n",
        "        columns=[\"red_flag\", \"R_id\", \"fields needed\"],\n",
        "    )\n",
        "    indicatordf[\"R_id\"] = indicatordf[\"R_id\"].apply(lambda x: \", \".join(map(str, x)))\n",
        "    indicatordf[\"fields needed\"] = indicatordf[\"fields needed\"].astype(str).str.replace(r\"\\[|\\]|'\", \"\", regex=True)\n",
        "    indicatordf[\"calculation\"] = results_list\n",
        "    indicatordf[\"missing fields\"] = missing_fields\n",
        "    indicatordf[\"missing fields\"] = indicatordf[\"missing fields\"].apply(lambda x: \", \".join(map(str, x)))\n",
        "\n",
        "    if check_coverage:\n",
        "        # Calculate coverage\n",
        "        coverage = []\n",
        "        for i in indicators_dic.values():\n",
        "            fields = [item for sublist in i for item in sublist][1:]\n",
        "            result = calculate_coverage(fields, \"release_summary\")\n",
        "            result_value = pd.to_numeric(result[\"total_percentage\"][0])\n",
        "            coverage.append(result_value)\n",
        "        indicatordf[\"coverage\"] = coverage\n",
        "    return indicatordf"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "s75VrpzLRNkx"
      },
      "source": [
        "## Usability analysis"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "HTkFw9jajocD"
      },
      "source": [
        "Generate a list of the fields published:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "MgZ3TsCOjozN"
      },
      "outputs": [],
      "source": [
        "fields_list = fields_table.iloc[:, 0].tolist()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "xrxFlHz9Hk48"
      },
      "outputs": [],
      "source": [
        "indicators_dic = get_red_flags_dictionary(fields_list)\n",
        "result = redflags_checks(fields_list, indicators_dic)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Fvej9yl_H5oE"
      },
      "source": [
        "### Export results"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "EBmaz2VRITHf"
      },
      "source": [
        "#### Load use case indicators spreadsheet"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "collapsed": false
      },
      "outputs": [],
      "source": [
        "result_final = check_red_flags_indicators(result)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "sbcQgxItIodv"
      },
      "source": [
        "#### Table of results"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "hMJH_IpAIm9V"
      },
      "outputs": [],
      "source": [
        "result_final"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "collapsed": false
      },
      "source": [
        "#### Results summary"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "collapsed": false
      },
      "outputs": [],
      "source": [
        "table = result_final.groupby(\"calculation\").agg(total_red_flags=(\"R_id\", \"count\")).reset_index()\n",
        "table[\"%\"] = round(table[\"total_red_flags\"] / table[\"total_red_flags\"].sum() * 100, 1)\n",
        "table"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "collapsed": false
      },
      "source": [
        "#### Most common fields to indicators"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "collapsed": false
      },
      "outputs": [],
      "source": [
        "common_fields = most_common_fields_to_calculate_indicators(indicators_dic, fields_table)\n",
        "common_fields"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "3001zqj9IxHF"
      },
      "source": [
        "#### Save the table to a spreadsheet"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Qxb5b7wsI0GM"
      },
      "outputs": [],
      "source": [
        "set_spreadsheet_name(\"Red Flags\")\n",
        "save_dataframe_to_sheet(result_final, \"red_flags_table\")\n",
        "save_dataframe_to_sheet(common_fields, \"common_fields_table\")\n",
        "save_dataframe_to_sheet(fields_table, \"fields_list\")"
      ]
    }
  ],
  "metadata": {
    "colab": {
      "name": "template_red_flags_checks_registry",
      "provenance": [],
      "toc_visible": true
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}