template_relevant_checks_registry_all.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": "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": "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": {
        "collapsed": false
      },
      "source": [
        "## Check the MVP status of the Data registry publications\n",
        "\n",
        "Use this notebook to check which publications in the Data Registry pass the MVP Relevant and Active criteria, for example, for updating the MEL1 tracker upon OCP Rapid Reflection meetings."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "collapsed": false
      },
      "outputs": [],
      "source": [
        "# @title Get all the publications from the registry { display-mode: \"form\" }\n",
        "\n",
        "publications = get_publications()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "collapsed": false
      },
      "source": [
        "### Check for non-frozen publications whose latest data has not been updated in the previous four calendar quarters\n",
        "\n",
        "From the list, check also the \"last_retrieved\" and \"update_frequency\" columns. If the data is not being retrieved, check the publication log in the Data Registry to check if there is a problem with either a job or the source data itself."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "collapsed": false
      },
      "outputs": [],
      "source": [
        "non_frozen_publications = [item for item in publications if not item[\"frozen\"] and item[\"date_to\"]]\n",
        "today = datetime.now(tz=timezone.utc)\n",
        "past_year = today - relativedelta(years=1)\n",
        "lapsed_publications = [\n",
        "    item\n",
        "    for item in non_frozen_publications\n",
        "    if datetime.strptime(item[\"date_to\"], \"%Y-%m-%d\").astimezone(timezone.utc) < past_year\n",
        "]\n",
        "lapsed_publications_table = pd.DataFrame(lapsed_publications)\n",
        "lapsed_publications_table"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "collapsed": false
      },
      "source": [
        "### Check non-relevant publications\n",
        "Check which active publications pass and not pass the \"Relevant\" criterion."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "collapsed": false
      },
      "outputs": [],
      "source": [
        "results = []\n",
        "active_publications = [item for item in non_frozen_publications if item not in lapsed_publications]\n",
        "for publication in active_publications:\n",
        "    year = publication[\"date_to\"][:4]\n",
        "    if int(year) > today.year:\n",
        "        year = today.year\n",
        "    file_name = download_file(publication, year)\n",
        "    field_table = cardinal_calculate_coverage(file_name)\n",
        "    fields_list = field_table.iloc[:, 0].tolist()\n",
        "    relevant, relevant_table = is_relevant(fields_list)\n",
        "    relevant_table[\"publisher\"] = publication[\"label\"]\n",
        "    relevant_table[\"relevant\"] = relevant\n",
        "    results.append(relevant_table)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "collapsed": false
      },
      "source": [
        "Filter the non-relevant ones"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "collapsed": false
      },
      "outputs": [],
      "source": [
        "result = pd.concat(results)\n",
        "not_relevant_publishers = result[~result[\"relevant\"]]\n",
        "non_relevant_rules = (\n",
        "    not_relevant_publishers[not_relevant_publishers[\"possible_to_calculate\"] == \"No\"]\n",
        "    .groupby(\"publisher\")\n",
        "    .apply(lambda x: \", \".join(x[\"rule\"].astype(str) + \": \" + x[\"missing_fields\"].astype(str)))\n",
        "    .reset_index()\n",
        "    .rename(columns={0: \"failed rules\"})\n",
        ")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "collapsed": false
      },
      "source": [
        "Check the results"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "collapsed": false
      },
      "outputs": [],
      "source": [
        "non_relevant_rules"
      ]
    }
  ],
  "metadata": {
    "colab": {
      "name": "template_relevant_checks_registry_all",
      "provenance": [],
      "toc_visible": true
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    },
    "language_info": {
      "codemirror_mode": {
        "name": "ipython",
        "version": 2
      },
      "file_extension": ".py",
      "mimetype": "text/x-python",
      "name": "python",
      "nbconvert_exporter": "python",
      "pygments_lexer": "ipython2",
      "version": "2.7.6"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}