template_relevant_checks_fieldlist.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": "OO6GyDGyfKmp"
      },
      "source": [
        "## Setup field list"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "II0mATgefP8R"
      },
      "source": [
        "To run this notebook, you need to have the list of fields that the publisher is intended to publish. You can get that list from their mapping template. We currently don't have an automatic way to extract the list from the template, so you must get this list manually.\n",
        "\n",
        "Upload an Excel file that includes the list of fields available in a column (no header).  Change the name of the file below. The list of fields must have the object/field_name format, for example `tender/id`."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "LnELmZSngWT9"
      },
      "outputs": [],
      "source": [
        "file_name = \"ADD FILE\""
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "0bcLPiPXgeJ-"
      },
      "source": [
        "Transform the file into a dataframe\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "ajkXtOdFggt9"
      },
      "outputs": [],
      "source": [
        "fields_table = pd.read_excel(file_name, header=None, names=[\"path\", \"count\"])\n",
        "fields_list = fields_table.iloc[:, 0].tolist()\n",
        "fields_table"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "s75VrpzLRNkx"
      },
      "source": [
        "## Usability 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 71 procurement indicators related to market opportunity (market description, competition, supplier performance), value for money, internal efficiency, public integrity and service delivery.  For an OCDS publisher, it also calculates the proportion of unique procedures for which it is possible to calculate the indicator (coverage).\n",
        "\n",
        "The usability checks includes all the indicators listed on [OCP's use case guide](https://docs.google.com/spreadsheets/d/1j-Y0ktZiOyhZzi-2GSabBCnzx6fF5lv8h1KYwi_Q9GM/edit#gid=1183427361) and the [Indicators to diagnose the performance of a procurement market document](https://docs.google.com/document/d/1vSJk9-qWSTQEx9ZZc7BUhQZMHvTRcyDYVS2sl8HB__k/edit#heading=h.nrnq1ajwwpqe)."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "zVnZn4E1kHg1"
      },
      "outputs": [],
      "source": [
        "# @title Usability functions { display-mode: \"form\" }\n",
        "\n",
        "RELEVANT_RULES = {\n",
        "    \"who\": [\n",
        "        \"buyer/id\",\n",
        "        \"buyer/name\",\n",
        "        \"tender/procuringEntity/id\",\n",
        "        \"tender/procuringEntity/name\",\n",
        "    ],\n",
        "    \"bought what\": [\n",
        "        \"tender/items/classification/id\",\n",
        "        \"awards/items/classification/id\",\n",
        "        \"contracts/items/classification/id\",\n",
        "        \"tender/items/classification/description\",\n",
        "        \"awards/items/classification/description\",\n",
        "        \"contracts/items/classification/description\",\n",
        "        \"tender/items/description\",\n",
        "        \"awards/items/description\",\n",
        "        \"contracts/items/description\",\n",
        "        \"tender/description\",\n",
        "        \"awards/description\",\n",
        "        \"contracts/description\",\n",
        "        \"tender/title\",\n",
        "        \"awards/title\",\n",
        "        \"contracts/title\",\n",
        "    ],\n",
        "    \"from whom\": [\n",
        "        \"awards/suppliers/id\",\n",
        "        \"awards/suppliers/name\",\n",
        "    ],\n",
        "    \"for how much\": [\n",
        "        \"awards/value/amount\",\n",
        "        \"contracts/value/amount\",\n",
        "        [\n",
        "            \"awards/items/quantity\",\n",
        "            \"awards/items/unit/value/amount\",\n",
        "        ],\n",
        "        [\n",
        "            \"contracts/items/quantity\",\n",
        "            \"contracts/items/unit/value/amount\",\n",
        "        ],\n",
        "    ],\n",
        "    \"when\": [\n",
        "        \"tender/tenderPeriod/endDate\",\n",
        "        \"awards/date\",\n",
        "        \"contracts/dateSigned\",\n",
        "    ],\n",
        "    \"how\": [\n",
        "        \"tender/procurementMethod\",\n",
        "        \"tender/procurementMethodDetails\",\n",
        "    ],\n",
        "}\n",
        "\n",
        "\n",
        "def get_indicators_dictionary(fields_list):\n",
        "    \"\"\"\n",
        "    Check which alternative fields are available for indicators.\n",
        "\n",
        "    For example, the number of tenderers can use either `tender/numberOfTenderers` or `tender/tenderers/id`.\n",
        "    \"\"\"\n",
        "    # U002\n",
        "    buyer = [\"buyer/name\", \"buyer/id\"]\n",
        "    procuring = [\"tender/procuringEntity/name\", \"tender/procuringEntity/id\"]\n",
        "    parties = [\"parties/identifier/name\", \"parties/identifier/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",
        "    # U003\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",
        "    # U008\n",
        "    if \"tender/items/classification/id\" in fields_list and \"tender/items/classification/scheme\" in fields_list:\n",
        "        items_val = [\"tender/items/classification/id\", \"tender/items/classification/scheme\"]\n",
        "    elif \"awards/items/classification/id\" in fields_list and \"awards/items/classification/scheme\" in fields_list:\n",
        "        items_val = [\"awards/items/classification/id\", \"awards/items/classification/scheme\"]\n",
        "    elif \"contracts/items/classification/id\" in fields_list and \"contractsitems/classification/scheme\" in fields_list:\n",
        "        items_val = [\"contracts/items/classification/id\", \"contracts/items/classification/scheme\"]\n",
        "    else:\n",
        "        items_val = [\"tender/items/classification/id\", \"tender/items/classification/scheme\"]\n",
        "\n",
        "    # U012\n",
        "    if \"contracts/id\" in fields_list and \"contracts/status\" in fields_list:\n",
        "        awards_val = [\"contracts/id\", \"contracts/status\"]\n",
        "    elif \"awards/id\" in fields_list and \"awards/status\" in fields_list:\n",
        "        awards_val = [\"awards/id\", \"awards/status\"]\n",
        "    else:\n",
        "        awards_val = [\"contracts/id\", \"contracts/status\"]\n",
        "\n",
        "    # U013, UC14\n",
        "    awards = [\"awards/id\", \"awards/status\", \"awards/value/amount\", \"awards/value/currency\"]\n",
        "    contracts = [\"contracts/id\", \"contracts/status\", \"contracts/value/amount\", \"contracts/value/currency\"]\n",
        "    if not any(item not in fields_list for item in contracts):\n",
        "        awards_val2 = contracts\n",
        "    elif not any(item not in fields_list for item in awards):\n",
        "        awards_val2 = awards\n",
        "    else:\n",
        "        awards_val2 = contracts\n",
        "\n",
        "    # U015\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",
        "    else:\n",
        "        bidders_val2 = \"tender/numberOfTenderers\"\n",
        "\n",
        "    # U034\n",
        "    aw = [\n",
        "        \"awards/status\",\n",
        "        \"awards/value/amount\",\n",
        "        \"awards/value/currency\",\n",
        "        \"awards/items/classification/id\",\n",
        "        \"awards/items/classification/scheme\",\n",
        "    ]\n",
        "    con = [\n",
        "        \"contracts/status\",\n",
        "        \"contracts/value/amount\",\n",
        "        \"contracts/value/currency\",\n",
        "        \"contracts/items/classification/id\",\n",
        "        \"contracts/items/classification/scheme\",\n",
        "    ]\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",
        "    # U042\n",
        "    if \"awards/status\" in fields_list:\n",
        "        awards_val4 = \"awards/status\"\n",
        "    elif \"contracts/status\" in fields_list:\n",
        "        awards_val4 = \"contracts/status\"\n",
        "    else:\n",
        "        awards_val4 = \"awards/status\"\n",
        "\n",
        "    # U061\n",
        "    if \"contracts/period/startDate\" in fields_list:\n",
        "        contract_date = \"contracts/period/startDate\"\n",
        "    elif \"awards/contractPeriod/startDate\" in fields_list:\n",
        "        contract_date = \"awards/contractPeriod/startDate\"\n",
        "    else:\n",
        "        contract_date = \"contracts/period/startDate\"\n",
        "\n",
        "    # U065\n",
        "    aw2 = [\"awards/items/quantity\", \"awards/items/unit\"]\n",
        "    con2 = [\"contracts/items/quantity\", \"contracts/items/unit\"]\n",
        "    if not any(item not in fields_list for item in aw):\n",
        "        awards_val5 = aw2\n",
        "    elif not any(item not in fields_list for item in con):\n",
        "        awards_val5 = con2\n",
        "    else:\n",
        "        awards_val5 = aw2\n",
        "\n",
        "    # U066, U067\n",
        "    if \"planning/budget/amount/amount\" in fields_list and \"planning/budget/amount/currency\":\n",
        "        planning = [\"planning/budget/amount/amount\", \"planning/budget/amount/currency\"]\n",
        "    elif \"tender/value/amount\" in fields_list and \"tender/value/currency\":\n",
        "        planning = [\"tender/value/amount\", \"tender/value/currency\"]\n",
        "    else:\n",
        "        planning = [\"planning/budget/amount/amount\", \"planning/budget/amount/currency\"]\n",
        "\n",
        "    return {\n",
        "        \"Total number of procedures\": [[\"U001\"], [\"ocid\"]],\n",
        "        \"Total number of procuring entities\": [[\"U002\"], [\"ocid\", *buyer_var]],\n",
        "        \"Total number of unique bidders\": [[\"U003\"], [\"ocid\", bidders_val]],\n",
        "        \"Total number of awarded suppliers\": [\n",
        "            [\"U004\"],\n",
        "            [\"awards/id\", \"awards/suppliers/id\", \"awards/suppliers/name\", \"awards/status\"],\n",
        "        ],\n",
        "        \"Total number of procedures by year or month\": [[\"U005\"], [\"ocid\", \"date\"]],\n",
        "        \"Total value awarded\": [[\"U006\"], [\"ocid\", \"awards/status\", \"awards/value/amount\", \"awards/value/currency\"]],\n",
        "        \"Share of procedures by status\": [[\"U007\"], [\"ocid\", \"tender/status\"]],\n",
        "        \"Number of procedures by item type\": [[\"U008\"], [\"ocid\", *items_val]],\n",
        "        \"Proportion of procedures by procurement category\": [[\"U009\"], [\"ocid\", \"tender/mainProcurementCategory\"]],\n",
        "        \"Percent of tenders by procedure type\": [[\"U010\"], [\"ocid\", \"tender/procurementMethod\"]],\n",
        "        \"Percent of tenders awarded by means of competitive procedures\": [\n",
        "            [\"U011\"],\n",
        "            [\"ocid\", \"tender/procurementMethod\", \"awards/status\"],\n",
        "        ],\n",
        "        \"Percent of contracts awarded under each procedure type\": [\n",
        "            [\"U012\"],\n",
        "            [\"ocid\", \"tender/procurementMethod\", *awards_val],\n",
        "        ],\n",
        "        \"Total contracted value awarded under each procedure type\": [\n",
        "            [\"U013\"],\n",
        "            [\"ocid\", \"tender/procurementMethod\", *awards_val2],\n",
        "        ],\n",
        "        \"Total awarded value of tenders awarded by means of competitive procedures\": [\n",
        "            [\"U014\"],\n",
        "            [\"ocid\", \"tender/procurementMethod\", *awards_val2],\n",
        "        ],\n",
        "        \"Proportion of single bid tenders\": [[\"U015\"], [\"ocid\", \"tender/procurementMethod\", bidders_val2]],\n",
        "        \"Proportion of value awarded in single bid tenders vs competitive tenders\": [\n",
        "            [\"U016\"],\n",
        "            [\n",
        "                \"ocid\",\n",
        "                \"tender/procurementMethod\",\n",
        "                \"awards/status\",\n",
        "                \"awards/value/amount\",\n",
        "                \"awards/value/currency\",\n",
        "                bidders_val2,\n",
        "            ],\n",
        "        ],\n",
        "        \"Mean number of bidders per tender\": [[\"U017\"], [\"ocid\", \"tender/procurementMethod\", bidders_val2]],\n",
        "        \"Median number of bidders per tender\": [[\"U018\"], [\"ocid\", \"tender/procurementMethod\", bidders_val2]],\n",
        "        \"Mean number of bidders by item type\": [\n",
        "            [\"U019\"],\n",
        "            [\"ocid\", \"tender/procurementMethod\", bidders_val2, *items_val],\n",
        "        ],\n",
        "        \"Number of suppliers by item type\": [\n",
        "            [\"U020\"],\n",
        "            [\"awards/id\", \"awards/suppliers/id\", \"awards/suppliers/name\", *items_val],\n",
        "        ],\n",
        "        \"Number of new bidders in a system \": [\n",
        "            [\"U021\"],\n",
        "            [\"tender/id\", \"tender/tenderers/id\", \"tender/tenderPeriod/startDate\"],\n",
        "        ],\n",
        "        \"Percent of new bidders to all bidders \": [\n",
        "            [\"U022\"],\n",
        "            [\"tender/id\", \"tender/tenderers/id\", \"tender/tenderPeriod/startDate\"],\n",
        "        ],\n",
        "        \"Percent of tenders with at least three participants deemed qualified\": [\n",
        "            [\"U023\"],\n",
        "            [\"ocid\", \"bids/details/tenderers/id\", \"bids/details/id\", \"bids/details/status\"],\n",
        "        ],\n",
        "        \"Mean percent of bids which are disqualified\": [\n",
        "            [\"U024\"],\n",
        "            [\"tender/id\", \"bids/details/id\", \"bids/details/status\"],\n",
        "        ],\n",
        "        \"Percent of contracts awarded to top 10 suppliers with largest contracted totals\": [\n",
        "            [\"U025\"],\n",
        "            [\"awards/id\", \"awards/suppliers/id\", \"awards/suppliers/name\", *awards_val2],\n",
        "        ],\n",
        "        \"Mean number of unique suppliers per buyer\": [\n",
        "            [\"U026\"],\n",
        "            [\"ocid\", \"awards/suppliers/id\", \"awards/suppliers/name\", *buyer_var],\n",
        "        ],\n",
        "        \"Number of new awarded suppliers \": [\n",
        "            [\"U027\"],\n",
        "            [\"awards/id\", \"awards/suppliers/id\", \"awards/suppliers/name\", \"awards/date\"],\n",
        "        ],\n",
        "        \"Percent of awards awarded to new suppliers\": [\n",
        "            [\"U028\"],\n",
        "            [\"awards/id\", \"awards/suppliers/id\", \"awards/suppliers/name\", \"awards/date\"],\n",
        "        ],\n",
        "        \"Total awarded value awarded to new suppliers\": [\n",
        "            [\"U029\"],\n",
        "            [\n",
        "                \"awards/id\",\n",
        "                \"awards/suppliers/id\",\n",
        "                \"awards/suppliers/name\",\n",
        "                \"awards/date\",\n",
        "                \"awards/value/amount\",\n",
        "                \"awards/value/currency\",\n",
        "            ],\n",
        "        ],\n",
        "        \"Percent of new suppliers to all suppliers\": [\n",
        "            [\"U030\"],\n",
        "            [\"awards/id\", \"awards/suppliers/id\", \"awards/suppliers/name\", \"awards/date\"],\n",
        "        ],\n",
        "        \"Percent of growth of new awarded suppliers in a system\": [\n",
        "            [\"U031\"],\n",
        "            [\"awards/id\", \"awards/suppliers/id\", \"awards/suppliers/name\", \"awards/date\"],\n",
        "        ],\n",
        "        \"Percent of total awarded value awarded to recurring suppliers\": [\n",
        "            [\"U032\"],\n",
        "            [\n",
        "                \"awards/id\",\n",
        "                \"awards/suppliers/id\",\n",
        "                \"awards/suppliers/name\",\n",
        "                \"awards/date\",\n",
        "                \"awards/value/amount\",\n",
        "                \"awards/value/currency\",\n",
        "            ],\n",
        "        ],\n",
        "        \"Mean number of bids necessary to win\": [\n",
        "            [\"U033\"],\n",
        "            [\"ocid\", \"tender/tenderers/id\", \"awards/suppliers/id\", \"awards/suppliers/name\"],\n",
        "        ],\n",
        "        \"Market concentration, market share of the largest company in the market\": [\n",
        "            [\"U034\"],\n",
        "            [\"awards/suppliers/id\", \"awards/suppliers/name\", *awards_val3],\n",
        "        ],\n",
        "        \"Proportion of contracts awarded by supplier by non competitive procedures\": [\n",
        "            [\"U035\"],\n",
        "            [\"ocid\", \"tender/procurementMethod\", \"awards/status\", \"awards/suppliers/id\", \"awards/suppliers/name\"],\n",
        "        ],\n",
        "        \"Region of the supplier\": [[\"U036\"], [\"parties/roles\", \"parties/identifier/id\", \"parties/address/region\"]],\n",
        "        \"Number of bids submitted by supplier\": [[\"U037\"], [\"awards/suppliers/id\", bidders_val]],\n",
        "        \"Success rate of bidders\": [\n",
        "            [\"U038\"],\n",
        "            [\"ocid\", \"tender/tenderers/id\", \"awards/suppliers/id\", \"awards/suppliers/name\"],\n",
        "        ],\n",
        "        \"Number of unique items classifications awarded by supplier\": [\n",
        "            [\"U039\"],\n",
        "            [\"awards/id\", \"awards/suppliers/id\", \"awards/suppliers/name\", *items_val],\n",
        "        ],\n",
        "        \"Total value awarded by supplier\": [[\"U040\"], [\"awards/suppliers/id\", \"awards/suppliers/name\", *awards_val2]],\n",
        "        \"Share of total value awarded by supplier\": [\n",
        "            [\"U041\"],\n",
        "            [\"awards/suppliers/id\", \"awards/suppliers/name\", *awards_val2],\n",
        "        ],\n",
        "        \"Total number of contracts awarded by supplier\": [\n",
        "            [\"U042\"],\n",
        "            [\"awards/id\", \"awards/suppliers/id\", \"awards/suppliers/name\", awards_val4],\n",
        "        ],\n",
        "        \"Number of procuring entities by supplier\": [\n",
        "            [\"U043\"],\n",
        "            [\"ocid\", \"awards/suppliers/id\", \"awards/suppliers/name\", *buyer_var],\n",
        "        ],\n",
        "        \"Share of single bid awards by supplier\": [\n",
        "            [\"U044\"],\n",
        "            [\n",
        "                \"ocid\",\n",
        "                \"awards/suppliers/id\",\n",
        "                \"awards/suppliers/name\",\n",
        "                \"awards/status\",\n",
        "                \"tender/procurementMethod\",\n",
        "                bidders_val2,\n",
        "            ],\n",
        "        ],\n",
        "        \"Percent of tenders with linked procurement plans\": [[\"U045\"], [\"tender/id\", \"tender/documents/documentType\"]],\n",
        "        \"Percent of contracts which publish information on debarments\": [\n",
        "            [\"U046\"],\n",
        "            [\"contracts/id\", \"contracts/implementation/documents/documentType\"],\n",
        "        ],\n",
        "        \"The percent of tenders for which the tender documentation was added after publication of the announcement \": [\n",
        "            [\"U047\"],\n",
        "            [\n",
        "                \"tender/id\",\n",
        "                \"tender/documents/documentType\",\n",
        "                \"tender/documents/documentType\",\n",
        "                \"tender/documents/datePublished\",\n",
        "            ],\n",
        "        ],\n",
        "        \"Mean number of contract amendments per buyer\": [\n",
        "            [\"U048\"],\n",
        "            [\"ocid\", \"contracts/id\", \"contracts/amendments\", *buyer_var],\n",
        "        ],\n",
        "        \"Percent of tenders which have been closed for more than 30 days, but whose basic awards information is not published\": [\n",
        "            [\"U049\"],\n",
        "            [\n",
        "                \"tender/id\",\n",
        "                \"tender/tenderPeriod/endDate\",\n",
        "                \"awards/id\",\n",
        "                \"awards/date\",\n",
        "                \"awards/status\",\n",
        "                \"awards/value/amount\",\n",
        "                \"awards/suppliers/id\",\n",
        "                \"awards/suppliers/name\",\n",
        "            ],\n",
        "        ],\n",
        "        \"Percent of awards which are older than 30 days, but whose contract is not published\": [\n",
        "            [\"U050\"],\n",
        "            [\n",
        "                \"awards/id\",\n",
        "                \"awards/date\",\n",
        "                \"contracts/awardID\",\n",
        "                \"contracts/status\",\n",
        "                \"contracts/dateSigned\",\n",
        "                \"contracts/documents/documentType\",\n",
        "            ],\n",
        "        ],\n",
        "        \"Percent of tenders that do not specify place of delivery\": [\n",
        "            [\"U051\"],\n",
        "            [\"ocid\", \"tender/items/deliveryLocation\", \"tender/items/deliveryAddress\"],\n",
        "        ],\n",
        "        \"Percent of tenders that do not specify date of delivery\": [\n",
        "            [\"U052\"],\n",
        "            [\n",
        "                \"tender/milestones/id\",\n",
        "                \"tender/milestones/type\",\n",
        "                \"tender/milestones/description\",\n",
        "                \"tender/milestones/dueDate\",\n",
        "            ],\n",
        "        ],\n",
        "        \"Percent of tenders with short titles for example fewer than 10 characters in the title\": [\n",
        "            [\"U053\"],\n",
        "            [\"tender/id\", \"tender/title\"],\n",
        "        ],\n",
        "        \"Percent of tenders with short descriptions for instance fewer than 30 characters in the description\": [\n",
        "            [\"U054\"],\n",
        "            [\"tender/id\", \"tender/description\"],\n",
        "        ],\n",
        "        \"Percent of tenders that do not include detailed item codes or item descriptions\": [\n",
        "            [\"U055\"],\n",
        "            [\"tender/id\", \"tender/items/classification/id\", \"tender/items/classification/scheme\"],\n",
        "        ],\n",
        "        \"Percent of contracts that do not have amendments\": [[\"U056\"], [\"contracts/id\", \"contracts/amendments\"]],\n",
        "        \"Percent of contracts which publish contract implementation details financial\": [\n",
        "            [\"U057\"],\n",
        "            [\n",
        "                \"contracts/implementation/transactions/id\",\n",
        "                \"contracts/implementation/transactions/value/amount\",\n",
        "                \"contracts/implementation/transactions/value/currency\",\n",
        "            ],\n",
        "        ],\n",
        "        \"Percent of contracts which publish contract implementation details physical\": [\n",
        "            [\"U058\"],\n",
        "            [\n",
        "                \"contracts/implementation/milestones/type\",\n",
        "                \"contracts/implementation/milestones/id\",\n",
        "                \"contracts/implementation/milestones/dueDate\",\n",
        "                \"contracts/implementation/milestones/status\",\n",
        "            ],\n",
        "        ],\n",
        "        \"Average duration of tendering period days\": [\n",
        "            [\"U059\"],\n",
        "            [\"ocid\", \"tender/tenderPeriod/startDate\", \"tender/tenderPeriod/endDate\"],\n",
        "        ],\n",
        "        \"Average duration of decision period days\": [[\"U060\"], [\"ocid\", \"tender/tenderPeriod/endDate\", \"awards/date\"]],\n",
        "        \"Average days from award date to start of implementation\": [\n",
        "            [\"U061\"],\n",
        "            [\"awards/id\", \"awards/date\", contract_date],\n",
        "        ],\n",
        "        \"Days between award date and tender start date\": [\n",
        "            [\"U062\"],\n",
        "            [\"ocid\", \"tender/tenderPeriod/startDate\", \"awards/date\"],\n",
        "        ],\n",
        "        \"Percent of canceled tenders to awarded tenders\": [[\"U063\"], [\"ocid\", \"tender/status\", \"awards/status\"]],\n",
        "        \"Percent of contracts which are canceled\": [[\"U064\"], [\"contracts/id\", \"contracts/status\"]],\n",
        "        \"Price variation of same item across all awards\": [[\"U065\"], awards_val3 + awards_val5],\n",
        "        \"Percent of contracts that exceed budget\": [\n",
        "            [\"U066\"],\n",
        "            [\"ocid\", \"contracts/status\", \"contracts/value/amount\", \"contracts/value/currency\", *planning],\n",
        "        ],\n",
        "        \"Mean percent overrun of contracts that exceed budget\": [\n",
        "            [\"U067\"],\n",
        "            [\"ocid\", \"contracts/status\", \"contracts/value/amount\", \"contracts/value/currency\", *planning],\n",
        "        ],\n",
        "        \"Total percent savings difference between budget and contract value\": [\n",
        "            [\"U068\"],\n",
        "            [\n",
        "                \"ocid\",\n",
        "                \"planning/budget/amount/amount\",\n",
        "                \"planning/budget/amount/currency\",\n",
        "                \"contracts/value/amount\",\n",
        "                \"contracts/value/currency\",\n",
        "            ],\n",
        "        ],\n",
        "        \"Total percent savings difference between tender value estimate and contract value\": [\n",
        "            [\"U069\"],\n",
        "            [\n",
        "                \"ocid\",\n",
        "                \"tender/value/amount\",\n",
        "                \"tender/value/currency\",\n",
        "                \"contracts/value/amount\",\n",
        "                \"contracts/value/currency\",\n",
        "            ],\n",
        "        ],\n",
        "        \"Percent of contracts completed on time \": [\n",
        "            [\"U070\"],\n",
        "            [\"contracts/id\", \"contracts/period/endDate\", \"contracts/status\"],\n",
        "        ],\n",
        "        \"Share of contracts whose milestones are completed on time\": [\n",
        "            [\"U071\"],\n",
        "            [\n",
        "                \"contracts/id\",\n",
        "                \"contracts/implementation/milestones/dueDate\",\n",
        "                \"contracts/implementation/milestones/dateMet\",\n",
        "            ],\n",
        "        ],\n",
        "    }\n",
        "\n",
        "\n",
        "def usability_checks(fields_list, indicators_dic):\n",
        "    \"\"\"\n",
        "    Return a table of the usability checks.\n",
        "\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=[\"indicator\", \"U_id\", \"fields needed\"],\n",
        "    )\n",
        "    indicatordf[\"U_id\"] = indicatordf[\"U_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",
        "    return indicatordf\n",
        "\n",
        "\n",
        "def check_usability_indicators(lang, result):\n",
        "\n",
        "    gc = authenticate_gspread()\n",
        "\n",
        "    if lang.value == \"English\":\n",
        "        # Use case guide: Indicators linked to OCDS #public\n",
        "        spreadsheet_key = \"1j-Y0ktZiOyhZzi-2GSabBCnzx6fF5lv8h1KYwi_Q9GM\"\n",
        "    else:\n",
        "        # [ES] of Use case guide: Indicators linked to OCDS #public\n",
        "        spreadsheet_key = \"14e-6nFC371D5foInL23k0is_ebKD69tJ0pkatwPqm6c\"\n",
        "\n",
        "    worksheet = gc.open_by_key(spreadsheet_key).sheet1\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",
        "\n",
        "    if lang.value == \"English\":\n",
        "        indicatorsdf = indicators.iloc[:, [0, 3, 4, 9]]\n",
        "        result_final = result.merge(indicatorsdf, on=\"U_id\")\n",
        "    else:\n",
        "        indicatorsdf = indicators.iloc[:, [0, 3, 4, 5, 9]]\n",
        "        result_final = indicatorsdf.merge(result, on=\"U_id\").drop([\"indicator\"], axis=1)\n",
        "        result_final = result_final.rename(\n",
        "            columns={\n",
        "                \"fields needed\": \"Campos necesarios\",\n",
        "                \"calculation\": \"¿Se puede calcular?\",\n",
        "                \"missing fields\": \"Campos faltantes\",\n",
        "                \"coverage\": \"Cobertura\",\n",
        "            },\n",
        "        )\n",
        "        result_final = result_final.replace(\n",
        "            {\"¿Se puede calcular?\": {\"possible to calculate\": \"sí\", \"missing fields\": \"campos faltantes\"}}\n",
        "        )\n",
        "    return result_final\n",
        "\n",
        "\n",
        "def is_relevant(field_list):\n",
        "    final_result = []\n",
        "    for key, value in RELEVANT_RULES.items():\n",
        "        rule_result = {\"rule\": key, \"possible_to_calculate\": \"No\", \"available_fields\": [], \"missing_fields\": []}\n",
        "        for field in value:\n",
        "            if isinstance(field, str):\n",
        "                if field in field_list:\n",
        "                    rule_result[\"possible_to_calculate\"] = \"Yes\"\n",
        "                    rule_result[\"available_fields\"].append(field)\n",
        "                else:\n",
        "                    rule_result[\"missing_fields\"].append(field)\n",
        "            else:\n",
        "                missing = [item for item in field if item not in field_list]\n",
        "                rule_result[\"available_fields\"].extend(item for item in field if item in field_list)\n",
        "                rule_result[\"missing_fields\"].extend(missing)\n",
        "                if not missing:\n",
        "                    rule_result[\"possible_to_calculate\"] = \"Yes\"\n",
        "        final_result.append(rule_result)\n",
        "\n",
        "    final_result = pd.DataFrame(final_result)\n",
        "    relevant = (final_result[\"possible_to_calculate\"] == \"Yes\").all()\n",
        "    return relevant, final_result\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 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_usability_language_select_box():\n",
        "    style = {\"description_width\": \"initial\"}\n",
        "    languages = [\"Spanish\", \"English\"]\n",
        "    return widgets.Dropdown(options=languages, description=\"language\", style=style)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "s75VrpzLRNkx"
      },
      "source": [
        "## Relevance analysis"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "pSR2il4VRPmJ"
      },
      "source": [
        "Use this section to assess if the publication contains the required fields to answer \"who bought what from whom, for how much, when and how\" for some subset of contracting processes."
      ]
    },
    {
      "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": [
        "relevant, result = is_relevant(fields_list)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Fvej9yl_H5oE"
      },
      "source": [
        "### Does the publication pass the relevant criterion?"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "collapsed": false
      },
      "outputs": [],
      "source": [
        "relevant"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "QaMrXgqBH-nD"
      },
      "source": [
        "### Why?"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "GVZIlbRaIAFc"
      },
      "outputs": [],
      "source": [
        "result"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "collapsed": false
      },
      "source": [
        "### Manually check for other fields\n",
        "If the main OCDS fields are not available to answer the relevant question, you should manually check if others might be used instead. This involves not only checking for the existence of a field but its content too. For example:\n",
        "- If you cannot answer \"who\", you could check if they disclose \"buyer\" or \"procuringEntity\" roles as part of the parties array.\n",
        "- If you cannot answer \"from whom\", you could check if they disclose the \"supplier\" role as part of the parties array.\n",
        "\n",
        "If a quick check yields no alternative field, do not spend more time. If you cannot easily find the relevant field, neither will another user."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "collapsed": false
      },
      "outputs": [],
      "source": [
        "fields_table"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "3001zqj9IxHF"
      },
      "source": [
        "#### Save the table to a spreadsheet"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Qxb5b7wsI0GM"
      },
      "outputs": [],
      "source": [
        "save_dataframe_to_sheet(result, \"relevant table\")"
      ]
    }
  ],
  "metadata": {
    "colab": {
      "name": "template_relevant_checks_fieldlist",
      "provenance": [],
      "toc_visible": true
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}