template_red_flags_checks.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": "EEE8NyL9UXi7"
      },
      "source": [
        "## Setup Kingfisher Process"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "hVRJEe8Ry04F"
      },
      "source": [
        "### Connect to the database"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "sX1Mk084L8gN"
      },
      "outputs": [],
      "source": [
        "import getpass\n",
        "\n",
        "from ocdskingfishercolab import (\n",
        "    list_collections,\n",
        "    list_source_ids,\n",
        "    set_search_path,\n",
        ")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "F8fwq_F6XMLs"
      },
      "source": [
        "Enter your PostgreSQL credentials and connect to the Kingfisher Process database:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "27yRx-oaXPtd"
      },
      "outputs": [],
      "source": [
        "user = input(\"Username:\")\n",
        "password = getpass.getpass(\"Password:\")\n",
        "\n",
        "# Don't show connection string after execute.\n",
        "%config SqlMagic.displaycon = False\n",
        "\n",
        "connection_string = (\n",
        "    \"postgresql://\"\n",
        "    + user\n",
        "    + \":\"\n",
        "    + password\n",
        "    + \"@postgres.kingfisher.open-contracting.org/kingfisher_process?sslmode=require\"\n",
        ")\n",
        "%sql $connection_string"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "0h0nJSx6Mzm9"
      },
      "source": [
        "### Choose collections and schema\n",
        "\n",
        "*Use this section to choose the collections and schema that you want to query.*"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "IKDe1itVq7dQ"
      },
      "source": [
        "#### Set the collection(s)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "-EVkYkVUsP9f"
      },
      "source": [
        "Update `collection_ids` with the `id`(s) of the [Kingfisher Process collection(s)](https://kingfisher-process.readthedocs.io/en/latest/data-model.html#collections):"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "IKn0TqQIsIGY"
      },
      "outputs": [],
      "source": [
        "collection_ids = (2358, 2359)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ewGMrRM9nNCl"
      },
      "source": [
        "If you don't know which collections you need, run the next cell and use the **Filter** button to filter the [collection table](https://kingfisher-process.readthedocs.io/en/latest/database-structure.html#collection-table) to find the collection(s). You can use the `source_id` column to filter on the `name` of the [Kingfisher Collect spider](https://kingfisher-collect.readthedocs.io/en/latest/spiders.html) used to collect the data. Use the value(s) from the `id` column to update the previous cell."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "ITTvCttGsWj1"
      },
      "outputs": [],
      "source": [
        "list_collections()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "aupr2bJKm4wr"
      },
      "source": [
        "#### Set the schema"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "y3mo29DCm4wu"
      },
      "source": [
        "Update `schema_name` with the name of the [Kingfisher Summarize schema](https://kingfisher-summarize.readthedocs.io/en/latest/index.html#how-it-works)."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "RtNddeQWm4wx"
      },
      "outputs": [],
      "source": [
        "schema_name = \"view_data_collection_2358_2359\"\n",
        "set_search_path(schema_name)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "4Ns5ujbNWbID"
      },
      "source": [
        "If you don't know which schema you need, run the next cell and use the **Filter** button to filter the [selected collections table](https://kingfisher-summarize.readthedocs.io/en/latest/database.html#summaries-selected-collections) to find the schema. You can use the `collection_id` column to filter on the `id` of the collections that you identified in the previous step. Alternatively, you can filter on the `source_id` column. Use the value from the `schema` column to update the previous cell."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "aVg-KWfvzcfa"
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "SELECT\n",
        "    summaries.selected_collections.*,\n",
        "    source_id\n",
        "FROM\n",
        "    summaries.selected_collections\n",
        "INNER JOIN\n",
        "    collection\n",
        "    ON summaries.selected_collections.collection_id = collection.id\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "LKjwIzCZoJYw"
      },
      "source": [
        "If you can't find a schema containing the collections that you want to query, you can create a schema using [Kingfisher Summarize](https://ocdsdeploy.readthedocs.io/en/latest/use/kingfisher-summarize.html)."
      ]
    },
    {
      "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": {},
      "outputs": [],
      "source": [
        "%%sql fields_table <<\n",
        "SELECT\n",
        "    path,\n",
        "    distinct_releases\n",
        "FROM\n",
        "    field_counts\n",
        "WHERE\n",
        "    release_type = 'compiled_release'\n"
      ]
    },
    {
      "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_dict = get_red_flags_dictionary(fields_list)\n",
        "result = redflags_checks(fields_list, indicators_dict, check_coverage=True)\n",
        "result[\"coverage\"] = get_coverage(indicators_dict)"
      ]
    },
    {
      "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": {
        "id": "LXRG5Di4JTC6"
      },
      "source": [
        "#### Most common fields for indicators"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "dU4jyCZyJaqx"
      },
      "source": [
        "This table shows the most frequent fields used to calculate indicators and if they are published.  You can use this table to highlight to the publisher the key data gaps.  "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "XZVErXpYJXxV"
      },
      "outputs": [],
      "source": [
        "common_fields = most_common_fields_to_calculate_indicators(indicators_dict, fields_table)\n",
        "common_fields"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "3001zqj9IxHF"
      },
      "source": [
        "#### Save tables to 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",
      "provenance": [],
      "toc_visible": true
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}