template_basic_criteria_checks.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "sfg_SbQWBCmW"
      },
      "source": [
        "## Setup\n",
        "\n",
        "*You must run the cells in this section each time you connect to a new runtime. For example, when you return to the notebook after an idle timeout, when the runtime crashes, or when you restart or factory reset the runtime.*"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "sowWi_Ve_Lm3"
      },
      "source": [
        "Install requirements (*Note: ocdskingfishercolab installs google-colab, which expects specific versions of pandas and numpy*):\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "X4nmyvOa_Ls7"
      },
      "outputs": [],
      "source": [
        "! pip install --upgrade pip > pip.log\n",
        "! pip install --upgrade 'ocdskingfishercolab<0.4' ipywidgets psycopg2-binary >> pip.log"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "tezDNj4uAOHq"
      },
      "outputs": [],
      "source": [
        "# @title Import packages and load extensions { display-mode: \"form\" }\n",
        "\n",
        "import gzip\n",
        "import json\n",
        "import os\n",
        "import shutil\n",
        "import tempfile\n",
        "from collections import Counter\n",
        "from datetime import datetime, timezone\n",
        "from pathlib import Path\n",
        "\n",
        "import numpy as np\n",
        "import pandas as pd\n",
        "from dateutil.relativedelta import relativedelta\n",
        "from google.colab.data_table import DataTable\n",
        "from google.colab.files import download\n",
        "from ipywidgets import widgets\n",
        "from ocdskingfishercolab import (\n",
        "    authenticate_gspread,\n",
        "    calculate_coverage,\n",
        "    download_dataframe_as_csv,\n",
        "    format_thousands,\n",
        "    render_json,\n",
        "    save_dataframe_to_sheet,\n",
        "    save_dataframe_to_spreadsheet,\n",
        "    set_dark_mode,\n",
        "    set_light_mode,\n",
        "    set_spreadsheet_name,\n",
        ")\n",
        "\n",
        "# Load https://pypi.org/project/ipython-sql/\n",
        "%load_ext sql\n",
        "# Load https://colab.research.google.com/notebooks/data_table.ipynb\n",
        "%load_ext google.colab.data_table"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "--8vgOiP_58f"
      },
      "outputs": [],
      "source": [
        "# @title Configure the notebook environment { display-mode: \"form\" }\n",
        "\n",
        "# Increase max columns so that Pandas DataFrames with many columns are rendered as data tables.\n",
        "DataTable.max_columns = 50\n",
        "# Remove the index from data tables for easier copy-pasting to Google Docs.\n",
        "DataTable.include_index = False\n",
        "\n",
        "# Return Pandas DataFrames instead of regular result sets.\n",
        "%config SqlMagic.autopandas = True\n",
        "# Don't print number of rows affected.\n",
        "%config SqlMagic.feedback = False\n",
        "\n",
        "# If you set Tools > Settings > Site > Theme to dark, uncomment this line.\n",
        "# set_dark_mode()\n",
        "# If you are creating plots to copy-paste into reports, uncomment this line.\n",
        "# set_light_mode()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "IhnbdjqU1e6p"
      },
      "source": [
        "## Charts Setup\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": "code",
      "execution_count": null,
      "metadata": {
        "id": "elpUvMf61Ym6"
      },
      "outputs": [],
      "source": [
        "! pip install altair pyarrow==14.0.0 >> pip.log"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "P1aenztz1zK3"
      },
      "source": [
        "Import chart packages and define chart functions. The currently available chart functions are:\n",
        "\n",
        "* Release count\n",
        "* Objects per stage\n",
        "* Releases by month\n",
        "* Objects per year\n",
        "* Top buyers\n",
        "* Usability indicators"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Bip37aP917XY"
      },
      "outputs": [],
      "source": [
        "# @title Chart functions { display-mode: \"form\" }\n",
        "import altair as alt\n",
        "\n",
        "\n",
        "class MissingColumnsError(Exception):\n",
        "    def __init__(self, columns):\n",
        "        super().__init__(f\"The source data is missing one or more of these columns: {columns}\")\n",
        "\n",
        "\n",
        "chart_properties = {\n",
        "    \"width\": 600,\n",
        "    \"height\": 350,\n",
        "    \"padding\": 50,\n",
        "    \"title\": alt.TitleParams(text=\"\", subtitle=[\"\"], fontSize=18),\n",
        "}\n",
        "chart_axis = {\n",
        "    \"titleFontSize\": 14,\n",
        "    \"labelFontSize\": 14,\n",
        "    \"labelPadding\": 5,\n",
        "    \"ticks\": False,\n",
        "    \"domain\": False,\n",
        "}\n",
        "\n",
        "\n",
        "def check_columns(columns, data):\n",
        "    # check if input contains the right columns\n",
        "    if not columns.issubset(data.columns):\n",
        "        raise MissingColumnsError(columns)\n",
        "\n",
        "\n",
        "def plot_release_count(release_counts):\n",
        "    check_columns({\"collection_id\", \"release_type\", \"release_count\", \"ocid_count\"}, release_counts)\n",
        "    return (\n",
        "        alt.Chart(release_counts)\n",
        "        .mark_bar()\n",
        "        .encode(\n",
        "            x=alt.X(\n",
        "                \"release_count\",\n",
        "                type=\"ordinal\",\n",
        "                axis=alt.Axis(title=\"release count\", labelAngle=0),\n",
        "            ),\n",
        "            y=alt.Y(\n",
        "                \"ocid_count\",\n",
        "                type=\"quantitative\",\n",
        "                axis=alt.Axis(title=\"ocid count\", format=\"~s\", tickCount=5),\n",
        "            ),\n",
        "            color=alt.Color(\n",
        "                \"release_type\",\n",
        "                type=\"nominal\",\n",
        "                title=\"release type\",\n",
        "                scale=alt.Scale(range=[\"#D6E100\", \"#FB6045\", \"#23B2A7\", \"#6C75E1\"]),\n",
        "            ),\n",
        "            tooltip=[\n",
        "                alt.Tooltip(\"release_count\", title=\"release count\"),\n",
        "                alt.Tooltip(\"ocid_count\", title=\"ocid count\", format=\"~s\"),\n",
        "                alt.Tooltip(\"release_type\", title=\"release type\"),\n",
        "                alt.Tooltip(\"collection_id\", title=\"collection id\"),\n",
        "            ],\n",
        "        )\n",
        "        .properties(**chart_properties)\n",
        "        .configure_axis(**chart_axis)\n",
        "        .configure_view(strokeWidth=0)\n",
        "    )\n",
        "\n",
        "\n",
        "def plot_objects_per_stage(objects_per_stage):\n",
        "    check_columns({\"stage\", \"object_count\"}, objects_per_stage)\n",
        "    stages = [\"planning\", \"tender\", \"awards\", \"contracts\", \"implementation\"]\n",
        "    return (\n",
        "        alt.Chart(objects_per_stage)\n",
        "        .mark_bar(fill=\"#d6e100\")\n",
        "        .encode(\n",
        "            x=alt.X(\n",
        "                \"stage\",\n",
        "                type=\"ordinal\",\n",
        "                scale=alt.Scale(domain=stages),\n",
        "                sort=stages,\n",
        "                axis=alt.Axis(title=\"stage\", labelAngle=0),\n",
        "            ),\n",
        "            y=alt.Y(\n",
        "                \"object_count\",\n",
        "                type=\"quantitative\",\n",
        "                axis=alt.Axis(title=\"number of objects\", format=\"~s\", tickCount=len(stages)),\n",
        "            ),\n",
        "            tooltip=[\n",
        "                alt.Tooltip(\"stage\", title=\"stage\"),\n",
        "                alt.Tooltip(\"object_count\", title=\"number of objects\"),\n",
        "            ],\n",
        "        )\n",
        "        .properties(**chart_properties)\n",
        "        .configure_axis(**chart_axis)\n",
        "        .configure_view(strokeWidth=0)\n",
        "    )\n",
        "\n",
        "\n",
        "def plot_releases_by_month(release_dates):\n",
        "    check_columns({\"date\", \"collection_id\", \"release_type\", \"release_count\"}, release_dates)\n",
        "    max_rows = 5000\n",
        "    # check if number of rows is more than 5000\n",
        "    if release_dates.shape[0] > max_rows:\n",
        "        alt.data_transformers.disable_max_rows()\n",
        "\n",
        "    # draw chart\n",
        "    return (\n",
        "        alt.Chart(release_dates)\n",
        "        .mark_line(strokeWidth=3)\n",
        "        .encode(\n",
        "            x=alt.X(\"date\", timeUnit=\"yearmonth\", axis=alt.Axis(title=\"year and month\")),\n",
        "            y=alt.Y(\n",
        "                \"release_count\",\n",
        "                type=\"quantitative\",\n",
        "                aggregate=\"sum\",\n",
        "                axis=alt.Axis(title=\"number of releases\", format=\"~s\", tickCount=5),\n",
        "                scale=alt.Scale(zero=False),\n",
        "            ),\n",
        "            color=alt.Color(\n",
        "                \"release_type\",\n",
        "                type=\"nominal\",\n",
        "                scale=alt.Scale(range=[\"#D6E100\", \"#FB6045\", \"#23B2A7\", \"#6C75E1\"]),\n",
        "                legend=alt.Legend(title=\"release type\"),\n",
        "            ),\n",
        "            tooltip=[\n",
        "                alt.Tooltip(\"date\", timeUnit=\"yearmonth\", title=\"date\"),\n",
        "                alt.Tooltip(\"release_count\", aggregate=\"sum\", title=\"number of releases\"),\n",
        "                alt.Tooltip(\"release_type\", title=\"release type\"),\n",
        "            ],\n",
        "        )\n",
        "        .properties(**chart_properties)\n",
        "        .configure_axis(**chart_axis)\n",
        "        .configure_view(strokeWidth=0)\n",
        "    )\n",
        "\n",
        "\n",
        "def plot_objects_per_year(objects_per_year):\n",
        "    check_columns({\"year\", \"tenders\", \"awards\"}, objects_per_year)\n",
        "    stages = [\"tenders\", \"awards\"]\n",
        "    return (\n",
        "        alt.Chart(objects_per_year)\n",
        "        .transform_fold(stages)\n",
        "        .mark_line(strokeWidth=3)\n",
        "        .encode(\n",
        "            x=alt.X(\n",
        "                \"year\",\n",
        "                type=\"quantitative\",\n",
        "                axis=alt.Axis(title=\"year\", format=\".0f\", tickCount=objects_per_year.shape[0]),\n",
        "            ),\n",
        "            y=alt.Y(\n",
        "                \"value\",\n",
        "                type=\"quantitative\",\n",
        "                axis=alt.Axis(title=\"number of objects\", format=\"~s\", tickCount=5),\n",
        "                scale=alt.Scale(zero=False),\n",
        "            ),\n",
        "            color=alt.Color(\n",
        "                \"key\",\n",
        "                type=\"nominal\",\n",
        "                title=\"object type\",\n",
        "                scale=alt.Scale(domain=stages, range=[\"#D6E100\", \"#FB6045\"]),\n",
        "            ),\n",
        "            tooltip=[\n",
        "                alt.Tooltip(\"year\", title=\"year\", type=\"quantitative\"),\n",
        "                alt.Tooltip(\"value\", title=\"number of objects\", type=\"quantitative\"),\n",
        "                alt.Tooltip(\"key\", title=\"object type\", type=\"nominal\"),\n",
        "            ],\n",
        "        )\n",
        "        .properties(**chart_properties)\n",
        "        .configure_axis(**chart_axis)\n",
        "        .configure_view(strokeWidth=0)\n",
        "    )\n",
        "\n",
        "\n",
        "def plot_top_buyers(buyers):\n",
        "    check_columns({\"name\", \"total_tenders\"}, buyers)\n",
        "    return (\n",
        "        alt.Chart(buyers)\n",
        "        .mark_bar(fill=\"#d6e100\")\n",
        "        .encode(\n",
        "            x=alt.X(\n",
        "                \"total_tenders\",\n",
        "                type=\"quantitative\",\n",
        "                axis=alt.Axis(title=\"number of tenders\", format=\"~s\", tickCount=5),\n",
        "            ),\n",
        "            y=alt.Y(\n",
        "                \"name\",\n",
        "                type=\"ordinal\",\n",
        "                axis=alt.Axis(title=\"buyer\", labelAngle=0),\n",
        "                sort=alt.SortField(\"total_tenders\", order=\"descending\"),\n",
        "            ),\n",
        "            tooltip=[\n",
        "                alt.Tooltip(\"name\", title=\"buyer\", type=\"nominal\"),\n",
        "                alt.Tooltip(\"total_tenders\", title=\"number of tenders\", type=\"quantitative\"),\n",
        "            ],\n",
        "        )\n",
        "        .properties(**chart_properties)\n",
        "        .configure_axis(**chart_axis)\n",
        "        .configure_view(strokeWidth=0)\n",
        "    )\n",
        "\n",
        "\n",
        "def plot_usability_indicators(data, lang=\"English\"):\n",
        "    labels = {\n",
        "        \"English\": {\n",
        "            \"nrow\": \"row_number(indicator)\",\n",
        "            \"sort\": \"calculation\",\n",
        "            \"y_sort\": \"indicator\",\n",
        "            \"groupby\": \"Use case\",\n",
        "            \"title\": \"number of indicators\",\n",
        "            \"tooltip_missing\": \"Missing Fields\",\n",
        "        },\n",
        "        \"Spanish\": {\n",
        "            \"nrow\": \"row_number(Indicador)\",\n",
        "            \"sort\": \"¿Se puede calcular?\",\n",
        "            \"y_sort\": \"Indicador\",\n",
        "            \"groupby\": \"Caso de Uso\",\n",
        "            \"title\": \"Número de indicadores\",\n",
        "            \"tooltip_missing\": \"Campos faltantes\",\n",
        "        },\n",
        "    }\n",
        "    return (\n",
        "        alt.Chart(data)\n",
        "        .transform_window(\n",
        "            nrow=labels[lang][\"nrow\"],\n",
        "            frame=[None, None],\n",
        "            sort=[{\"field\": labels[lang][\"sort\"]}],\n",
        "            groupby=[labels[lang][\"groupby\"]],\n",
        "        )\n",
        "        .mark_circle(size=250, opacity=1)\n",
        "        .encode(\n",
        "            x=alt.X(\n",
        "                \"nrow\",\n",
        "                type=\"quantitative\",\n",
        "                axis=alt.Axis(title=[labels[lang][\"title\"], \"\"], orient=\"top\", tickCount=5),\n",
        "            ),\n",
        "            y=alt.Y(\n",
        "                labels[lang][\"groupby\"],\n",
        "                type=\"nominal\",\n",
        "                sort=alt.Sort(field=labels[lang][\"y_sort\"], op=\"count\", order=\"descending\"),\n",
        "            ),\n",
        "            color=alt.Color(\n",
        "                labels[lang][\"sort\"],\n",
        "                type=\"nominal\",\n",
        "                scale=alt.Scale(range=[\"#fb6045\", \"#d6e100\"]),\n",
        "                legend=alt.Legend(title=[labels[lang][\"sort\"]]),\n",
        "            ),\n",
        "            tooltip=[\n",
        "                alt.Tooltip(labels[lang][\"y_sort\"], type=\"nominal\"),\n",
        "                alt.Tooltip(labels[lang][\"groupby\"], type=\"nominal\"),\n",
        "                alt.Tooltip(labels[lang][\"sort\"], type=\"nominal\"),\n",
        "                alt.Tooltip(labels[lang][\"tooltip_missing\"], type=\"nominal\"),\n",
        "            ],\n",
        "        )\n",
        "        .properties(**chart_properties)\n",
        "        .configure_axis(**chart_axis)\n",
        "        .configure_view(strokeWidth=0)\n",
        "    )"
      ]
    },
    {
      "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": "EEE8NyL9UXi7"
      },
      "source": [
        "## Setup Kingfisher Process"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "hVRJEe8Ry04F"
      },
      "source": [
        "### Connect to the database"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "sX1Mk084L8gN"
      },
      "outputs": [],
      "source": [
        "import getpass\n",
        "\n",
        "from ocdskingfishercolab import (\n",
        "    list_collections,\n",
        "    list_source_ids,\n",
        "    set_search_path,\n",
        ")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "F8fwq_F6XMLs"
      },
      "source": [
        "Enter your PostgreSQL credentials and connect to the Kingfisher Process database:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "27yRx-oaXPtd"
      },
      "outputs": [],
      "source": [
        "user = input(\"Username:\")\n",
        "password = getpass.getpass(\"Password:\")\n",
        "\n",
        "# Don't show connection string after execute.\n",
        "%config SqlMagic.displaycon = False\n",
        "\n",
        "connection_string = (\n",
        "    \"postgresql://\"\n",
        "    + user\n",
        "    + \":\"\n",
        "    + password\n",
        "    + \"@postgres.kingfisher.open-contracting.org/kingfisher_process?sslmode=require\"\n",
        ")\n",
        "%sql $connection_string"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "0h0nJSx6Mzm9"
      },
      "source": [
        "### Choose collections and schema\n",
        "\n",
        "*Use this section to choose the collections and schema that you want to query.*"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "IKDe1itVq7dQ"
      },
      "source": [
        "#### Set the collection(s)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "-EVkYkVUsP9f"
      },
      "source": [
        "Update `collection_ids` with the `id`(s) of the [Kingfisher Process collection(s)](https://kingfisher-process.readthedocs.io/en/latest/data-model.html#collections):"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "IKn0TqQIsIGY"
      },
      "outputs": [],
      "source": [
        "collection_ids = (2358, 2359)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ewGMrRM9nNCl"
      },
      "source": [
        "If you don't know which collections you need, run the next cell and use the **Filter** button to filter the [collection table](https://kingfisher-process.readthedocs.io/en/latest/database-structure.html#collection-table) to find the collection(s). You can use the `source_id` column to filter on the `name` of the [Kingfisher Collect spider](https://kingfisher-collect.readthedocs.io/en/latest/spiders.html) used to collect the data. Use the value(s) from the `id` column to update the previous cell."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "ITTvCttGsWj1"
      },
      "outputs": [],
      "source": [
        "list_collections()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "aupr2bJKm4wr"
      },
      "source": [
        "#### Set the schema"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "y3mo29DCm4wu"
      },
      "source": [
        "Update `schema_name` with the name of the [Kingfisher Summarize schema](https://kingfisher-summarize.readthedocs.io/en/latest/index.html#how-it-works)."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "RtNddeQWm4wx"
      },
      "outputs": [],
      "source": [
        "schema_name = \"view_data_collection_2358_2359\"\n",
        "set_search_path(schema_name)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "4Ns5ujbNWbID"
      },
      "source": [
        "If you don't know which schema you need, run the next cell and use the **Filter** button to filter the [selected collections table](https://kingfisher-summarize.readthedocs.io/en/latest/database.html#summaries-selected-collections) to find the schema. You can use the `collection_id` column to filter on the `id` of the collections that you identified in the previous step. Alternatively, you can filter on the `source_id` column. Use the value from the `schema` column to update the previous cell."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "aVg-KWfvzcfa"
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "SELECT\n",
        "    summaries.selected_collections.*,\n",
        "    source_id\n",
        "FROM\n",
        "    summaries.selected_collections\n",
        "INNER JOIN\n",
        "    collection\n",
        "    ON summaries.selected_collections.collection_id = collection.id\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "LKjwIzCZoJYw"
      },
      "source": [
        "If you can't find a schema containing the collections that you want to query, you can create a schema using [Kingfisher Summarize](https://ocdsdeploy.readthedocs.io/en/latest/use/kingfisher-summarize.html)."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "pP9KsYyT1Xnd"
      },
      "source": [
        "## Check for data collection and processing errors\n",
        "\n",
        "Kingfisher Collect and Kingfisher Process log messages that might indicate OCDS implementation errors or API stability issues.\n",
        "\n",
        "Confirm any errors or warnings by manually checking the OCDS publication."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "i_rbrQG_3_-x"
      },
      "source": [
        "### Kingfisher Collect\n",
        "\n",
        "See [how to review the Kingfisher Collect crawl’s log file](https://kingfisher-collect.readthedocs.io/en/latest/logs.html).\n",
        "\n",
        "**Note:** There is an open [pull request](https://github.com/open-contracting/notebooks-ocds/pull/44) to automate this. If interested, please comment on the pull request to prioritize it."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "1U1_PR0J4DQA"
      },
      "source": [
        "### Kingfisher Process\n",
        "\n",
        "Users add a note when starting a crawl and when running the `load` command.\n",
        "\n",
        "Kingfisher Process adds notes (the `note` column) at different levels (the `code` column):\n",
        "\n",
        "- `INFO`\n",
        "  - *load*: The Kingfisher Collect crawl's reason for closing and statistics (the `data` column).\n",
        "- `WARNING`\n",
        "  - *compile*: When a record has undated releases, linked releases or no releases, but a compiled release can be calculated (by merging remaining dated releases, using the `compiledRelease` field, or using an undated release with a 'compiled' tag).\n",
        "- `ERROR`\n",
        "  - *load*: When the input data is invalid JSON.\n",
        "  - *compile*: When no compiled release can be calculated for a record.\n",
        "\n",
        "📗 **You can check the full list of note types and what they mean in the Kingfisher Process's [documentation](https://kingfisher-process.readthedocs.io/en/latest/database.html#collection-note-table)**📗\n",
        "\n",
        "\n",
        "List the notes for each of your collections:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "U0x8mo8Yyj39"
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "SELECT\n",
        "    collection_id,\n",
        "    code,\n",
        "    note,\n",
        "    data\n",
        "FROM\n",
        "    collection_note\n",
        "WHERE\n",
        "    collection_id IN :collection_ids\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "cE548sDyZyG5"
      },
      "source": [
        "## Check for structure and format errors\n",
        "\n",
        "Kingfisher Collect reformats data sources as release packages or record packages. Check the `data_type` class attribute of the [spider](https://github.com/open-contracting/kingfisher-collect/tree/main/kingfisher_scrapy/spiders). If it is not 'release_package' or 'record_package', recommend to the publisher to package their data."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "gtzHL_Sul8i3"
      },
      "source": [
        "Kingfisher Process checks data against the OCDS schema using [lib-cove-ocds](https://github.com/open-contracting/lib-cove-ocds), same as the [OCDS Data Review Tool](https://review.standard.open-contracting.org). For release collections, Kingfisher Process stores check results in the `release_check` table. For record collections, Kingfisher Process stores check results in the `record_check` table."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ydqNZpiWWJVT"
      },
      "source": [
        "### Confirm that checks are complete"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "cyhI3LAhXZ44"
      },
      "source": [
        "If a crawl is scheduled using Kingfisher Collect, by default, Kingfisher Process performs structural checks. Checking data is the slowest step. For large collections, it is recommended to skip the `check` step or to collect only a sample. Otherwise, there can be a backlog of data to check.\n",
        "\n",
        "Use this query to determine whether checks are complete for your collection(s).\n",
        "\n",
        "If checks are in progress, you should wait for the checks to finish before running the queries in this section."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "nKEuouhtWTiP"
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "SELECT\n",
        "    collection_id,\n",
        "    'release' AS collection_type,\n",
        "    CASE\n",
        "        WHEN count(release.id) = count(release_check.id)\n",
        "            THEN\n",
        "                'complete'\n",
        "        WHEN count(release_check.id) = 0\n",
        "            THEN\n",
        "                'not_started'\n",
        "        ELSE\n",
        "            'in_progress'\n",
        "    END AS check_status,\n",
        "    count(release_check.id)::text\n",
        "    || '/'\n",
        "    || count(release.id)::text AS check_progress\n",
        "FROM\n",
        "    release_check\n",
        "RIGHT JOIN release ON release_check.release_id = release.id\n",
        "WHERE\n",
        "    collection_id IN :collection_ids\n",
        "GROUP BY\n",
        "    collection_id\n",
        "UNION\n",
        "SELECT\n",
        "    collection_id,\n",
        "    'record' AS collection_type,\n",
        "    CASE\n",
        "        WHEN count(record.id) = count(record_check.id)\n",
        "            THEN\n",
        "                'complete'\n",
        "        WHEN count(record_check.id) = 0\n",
        "            THEN\n",
        "                'not_started'\n",
        "        ELSE\n",
        "            'in_progress'\n",
        "    END AS check_status,\n",
        "    count(record_check.id)::text\n",
        "    || '/'\n",
        "    || count(record.id)::text AS check_progress\n",
        "FROM\n",
        "    record_check\n",
        "RIGHT JOIN record ON record_check.record_id = record.id\n",
        "WHERE\n",
        "    collection_id IN :collection_ids\n",
        "GROUP BY\n",
        "    collection_id;\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "uTeVs0jnlgHu"
      },
      "source": [
        "### Error summary"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "XGxn_SwCllq4"
      },
      "source": [
        "Summarize the errors from the `release_check` and `record_check` tables."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "ncEmto9mc_wI"
      },
      "outputs": [],
      "source": [
        "%%sql structure_and_format_error_summary <<\n",
        "WITH errors AS (\n",
        "    SELECT\n",
        "        collection_id,\n",
        "        errors ->> 'type' AS error_type,\n",
        "        left(\n",
        "            errors ->> 'description',\n",
        "            49000\n",
        "        ) AS error,\n",
        "        ocid,\n",
        "        errors ->> 'field' AS field,\n",
        "        errors ->> 'value' AS value,\n",
        "        row_number() OVER (\n",
        "            PARTITION BY\n",
        "                collection_id,\n",
        "                errors ->> 'type',\n",
        "                left(\n",
        "                    errors ->> 'description',\n",
        "                    49000\n",
        "                )\n",
        "        ) AS rownum\n",
        "    FROM\n",
        "        release_check AS rc\n",
        "    CROSS JOIN\n",
        "        jsonb_array_elements(cove_output -> 'validation_errors') AS errors\n",
        "    INNER JOIN release AS r ON rc.release_id = r.id\n",
        "    WHERE\n",
        "        collection_id IN :collection_ids\n",
        "    UNION ALL\n",
        "    SELECT\n",
        "        collection_id,\n",
        "        errors ->> 'type' AS error_type,\n",
        "        left(\n",
        "            errors ->> 'description',\n",
        "            49000\n",
        "        ) AS error,\n",
        "        ocid,\n",
        "        errors ->> 'field' AS field,\n",
        "        errors ->> 'value' AS value,\n",
        "        row_number() OVER (\n",
        "            PARTITION BY\n",
        "                collection_id,\n",
        "                errors ->> 'type',\n",
        "                left(\n",
        "                    errors ->> 'description',\n",
        "                    49000\n",
        "                )\n",
        "        ) AS rownum\n",
        "    FROM\n",
        "        record_check AS rc\n",
        "    CROSS JOIN\n",
        "        jsonb_array_elements(cove_output -> 'validation_errors') AS errors\n",
        "    INNER JOIN record AS r ON rc.record_id = r.id\n",
        "    WHERE\n",
        "        collection_id IN :collection_ids\n",
        "),\n",
        "\n",
        "examples AS (\n",
        "    SELECT\n",
        "        collection_id,\n",
        "        error_type,\n",
        "        field,\n",
        "        error,\n",
        "        array_agg(ocid) AS example_ocids,\n",
        "        array_agg(value) AS example_values\n",
        "    FROM\n",
        "        errors\n",
        "    WHERE\n",
        "        rownum <= 3\n",
        "    GROUP BY\n",
        "        collection_id,\n",
        "        error_type,\n",
        "        field,\n",
        "        error\n",
        ")\n",
        "\n",
        "SELECT\n",
        "    collection_id,\n",
        "    error_type,\n",
        "    errors.field,\n",
        "    error,\n",
        "    count(*) AS count,\n",
        "    example_ocids,\n",
        "    example_values\n",
        "FROM\n",
        "    errors\n",
        "INNER JOIN examples USING (collection_id, error_type, error)\n",
        "GROUP BY\n",
        "    collection_id,\n",
        "    error_type,\n",
        "    errors.field,\n",
        "    error,\n",
        "    example_ocids,\n",
        "    example_values;\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "zeOz07-jedqT"
      },
      "outputs": [],
      "source": [
        "structure_and_format_error_summary"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ZfdR-wgKpFmT"
      },
      "source": [
        "### Error details"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "tCw4uaq3mwVT"
      },
      "source": [
        "List all errors from the `release_check` and `record_check` tables."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "QKRzlq22m0S-"
      },
      "outputs": [],
      "source": [
        "%%sql structure_and_format_errors <<\n",
        "SELECT\n",
        "    collection_id,\n",
        "    'release' AS collection_type,\n",
        "    errors ->> 'type' AS error_type,\n",
        "    errors ->> 'field' AS field,\n",
        "    left(\n",
        "        errors ->> 'description',\n",
        "        49000\n",
        "    ) AS error,\n",
        "    ocid,\n",
        "    errors ->> 'value' AS value\n",
        "FROM\n",
        "    release_check AS rc\n",
        "CROSS JOIN jsonb_array_elements(cove_output -> 'validation_errors') AS errors\n",
        "INNER JOIN release AS r ON rc.release_id = r.id\n",
        "WHERE\n",
        "    collection_id IN :collection_ids\n",
        "UNION ALL\n",
        "SELECT\n",
        "    collection_id,\n",
        "    'record' AS collection_type,\n",
        "    errors ->> 'type' AS error_type,\n",
        "    errors ->> 'field' AS field,\n",
        "    left(\n",
        "        errors ->> 'description',\n",
        "        49000\n",
        "    ) AS error,\n",
        "    ocid,\n",
        "    errors ->> 'value' AS value\n",
        "FROM\n",
        "    record_check AS rc\n",
        "CROSS JOIN jsonb_array_elements(cove_output -> 'validation_errors') AS errors\n",
        "INNER JOIN record AS r ON rc.record_id = r.id\n",
        "WHERE\n",
        "    collection_id IN :collection_ids\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "q3cKo_dnvdvQ"
      },
      "outputs": [],
      "source": [
        "structure_and_format_errors"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "7fpo23ErO_lL"
      },
      "source": [
        "## Perform manual conformance checks"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "B_nDBOyG2279"
      },
      "source": [
        "[Conforming publications](https://standard.open-contracting.org/latest/en/schema/conformance_and_extensions/#publication-conformance):\n",
        "\n",
        "* Use a [registered OCID prefix](https://standard.open-contracting.org/latest/en/schema/identifiers/#contracting-process-identifier-ocid).\n",
        "* The [OCDS Data Review Tool](https://standard.open-contracting.org/review/) can report results on the data.\n",
        "* Must not use terms from outside the OCDS schema where its terms would suffice.\n",
        "\n",
        "Use this section to:\n",
        "\n",
        "* Check if the proper OCID prefix is in use.\n",
        "* Check if the OCDS Data Review Tool is able to report results on data.\n",
        "* Identify fields in local extensions and additional fields and codes that should be mapped to fields and codes in the OCDS schema and extensions.\n",
        "* Identify deprecated fields, in order to recommend a change to the publisher's OCDS implementation.\n",
        "\n",
        "\n",
        "You can use the following resources to find fields and codes with similar semantics:\n",
        "\n",
        "* [OCDS Schema and Codelist Reference](https://standard.open-contracting.org/latest/en/schema/), for fields in the core OCDS schema.\n",
        "* [OCDS Extensions Field and Code Search](https://open-contracting.github.io/editor-tools/), for fields and codes in OCDS extensions.\n",
        "* [GitHub Issue Tracker](https://github.com/open-contracting/standard/issues), for discussions about adding new fields and codes.\n",
        "\n",
        "Check that field and code names [conform to the style guide](https://ocds-standard-development-handbook.readthedocs.io/en/latest/meta/schema_style_guide.html#field-and-code-names) and report any issues to the publisher.\n",
        "\n",
        "If you cannot find a suitable mapping for an additional field or code, [open a GitHub issue](https://github.com/open-contracting/standard/issues) to describe the semantics of the field or code and to discuss how to model it. Report any issues to the publisher.\n",
        "\n",
        "**Note:** This section depends on the `check` step of Kingfisher Process having completed. See *Check for structure and format errors >Confirm that checks are complete*. If the checks are not complete or the OCDS Data Review Tool is not able to report results on data, **the publisher don't pass that conformance criterion**"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "collapsed": false
      },
      "source": [
        "### OCID prefix\n",
        "\n",
        "Check that the data uses the OCID prefix that was issued to this specific publisher.\n",
        "\n",
        "You only need to run either the *Release prefixes* or *Record prefixes* section, depending on the publication's data format.\n",
        "\n",
        "Update the ocid prefix in the appropriate cell. Prefixes can be found at [the list of registered prefixes](https://docs.google.com/spreadsheets/d/1E5ZVhc8VhGOakCq4GegvkyFYT974QQb-sSjvOfaxH7s/pubhtml?gid=506986894&single=true&widget=true)."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "collapsed": false
      },
      "source": [
        "#### **Release prefixes**\n",
        "\n",
        "Notify the publisher of any incorrect prefixes."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "collapsed": false
      },
      "outputs": [],
      "source": [
        "# Do not remove the final % character\n",
        "ocid_prefix = \"%\""
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "collapsed": false
      },
      "outputs": [],
      "source": [
        "%%sql ocid_prefix_release_check <<\n",
        "SELECT ocid\n",
        "FROM\n",
        "    release_summary\n",
        "WHERE\n",
        "    collection_id IN :collection_ids\n",
        "    AND ocid NOT LIKE :ocid_prefix\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "collapsed": false
      },
      "outputs": [],
      "source": [
        "ocid_prefix_release_check"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "collapsed": false
      },
      "source": [
        "#### **Record prefixes**\n",
        "\n",
        "Notify the publisher of any incorrect prefixes."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "collapsed": false
      },
      "outputs": [],
      "source": [
        "%%sql ocid_prefix_record_check <<\n",
        "SELECT ocid\n",
        "FROM\n",
        "    record_summary\n",
        "WHERE\n",
        "    collection_id IN :collection_ids\n",
        "    AND ocid NOT LIKE :ocid_prefix\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "collapsed": false
      },
      "outputs": [],
      "source": [
        "ocid_prefix_record_check"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "4oFJe2cw9HlA"
      },
      "source": [
        "### Local extensions"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "72PpTzAP9NSI"
      },
      "source": [
        "For each field and code in extensions authored by the publisher, in addition to the above checks, consider whether to [review the extension in detail](https://docs.google.com/document/d/1CS_TMubqoYaucT8JXPTgLS-mF4eMIifX-6mD0xpWg9M/edit)."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "LzOXXZob9gA2"
      },
      "source": [
        "List the extensions declared in the package metadata.\n",
        "\n",
        "**Note:** This query should be kept in sync with the query in *Check scope > Extensions*.\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Yul28_sU9gA2"
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "SELECT\n",
        "    collection_id,\n",
        "    release_type,\n",
        "    jsonb_array_elements(package_data -> 'extensions') AS ocds_extension,\n",
        "    count(*) AS count\n",
        "FROM\n",
        "    release_summary\n",
        "WHERE\n",
        "    collection_id IN :collection_ids\n",
        "    AND package_data IS NOT NULL\n",
        "GROUP BY\n",
        "    collection_id,\n",
        "    release_type,\n",
        "    ocds_extension\n",
        "ORDER BY\n",
        "    collection_id ASC,\n",
        "    release_type ASC,\n",
        "    count DESC;\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "CeabYppc20t4"
      },
      "source": [
        "### Additional fields"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "52thQMhSsTe8"
      },
      "source": [
        "[lib-cove-ocds](https://github.com/open-contracting/lib-cove-ocds) reports additional fields in the following scenarios:\n",
        "\n",
        "* Fields from undeclared extensions.\n",
        "* Fields with language variations, e.g. `title_es`. You do not need to report language variations to the publisher, but you should check that the field [conforms to the rules for language variations](https://standard.open-contracting.org/latest/en/schema/reference/#language).\n",
        "* OCDS 1.0 data using extension fields. You should report the fields to the publisher and recommend that they upgrade to OCDS 1.1.\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "rOaRxbxgpzKw"
      },
      "source": [
        "List additional fields.\n",
        "\n",
        "By default, results are reported for a sample of 10% of releases. For small collections, you can set `sample_size` to `1` to return results for the full collection. For large collections, you can reduce the sample size."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "KQlVv7YZjbED"
      },
      "outputs": [],
      "source": [
        "sample_size = 0.1"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "RbJJ5k0SmOTB"
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "WITH check_results AS (\n",
        "    SELECT\n",
        "        *,\n",
        "        CASE\n",
        "            WHEN (release_type IN ('record', 'embedded_release'))\n",
        "                THEN\n",
        "                    record_check\n",
        "            ELSE\n",
        "                release_check\n",
        "        END AS results\n",
        "    FROM\n",
        "        release_summary\n",
        "    WHERE\n",
        "        collection_id IN :collection_ids\n",
        "        AND release_type <> 'compiled_release'\n",
        "        AND random() < :sample_size\n",
        "),\n",
        "\n",
        "counts AS (\n",
        "    SELECT\n",
        "        collection_id,\n",
        "        release_type,\n",
        "        additional_fields ->> 'path' AS path,\n",
        "        additional_fields ->> 'field_name' AS field,\n",
        "        sum((additional_fields ->> 'count')::int) AS count\n",
        "    FROM\n",
        "        check_results\n",
        "    CROSS JOIN\n",
        "        jsonb_array_elements(\n",
        "            results -> 'all_additional_fields'\n",
        "        ) AS additional_fields\n",
        "    GROUP BY\n",
        "        collection_id,\n",
        "        release_type,\n",
        "        field,\n",
        "        path\n",
        "    ORDER BY\n",
        "        path ASC,\n",
        "        count DESC\n",
        "),\n",
        "\n",
        "examples AS (\n",
        "    SELECT DISTINCT ON (\n",
        "        collection_id,\n",
        "        release_type,\n",
        "        additional_fields ->> 'path',\n",
        "        additional_fields ->> 'field_name')\n",
        "        collection_id,\n",
        "        release_type,\n",
        "        additional_fields ->> 'path' AS path,\n",
        "        additional_fields ->> 'field_name' AS field,\n",
        "        additional_fields ->> 'examples' AS examples\n",
        "    FROM\n",
        "        check_results\n",
        "    CROSS JOIN\n",
        "        jsonb_array_elements(\n",
        "            results -> 'all_additional_fields'\n",
        "        ) AS additional_fields\n",
        "    WHERE\n",
        "        jsonb_array_length(additional_fields -> 'examples') > 0\n",
        ")\n",
        "\n",
        "SELECT\n",
        "    counts.collection_id,\n",
        "    counts.release_type,\n",
        "    counts.path,\n",
        "    counts.field,\n",
        "    count,\n",
        "    examples\n",
        "FROM\n",
        "    counts\n",
        "LEFT JOIN examples\n",
        "    USING (\n",
        "        collection_id,\n",
        "        release_type,\n",
        "        path,\n",
        "        field\n",
        "    )\n",
        "ORDER BY\n",
        "    path,\n",
        "    field;\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "v_6_-h72u3_Q"
      },
      "source": [
        "#### Additional field examples\n",
        "\n",
        "Generate a release package containing an example release for each additional field:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "oMBWTfjljvdc"
      },
      "outputs": [],
      "source": [
        "%%sql additional_field_examples <<\n",
        "WITH additional_field_releases AS (\n",
        "    SELECT\n",
        "        ocid,\n",
        "        release.release_id,\n",
        "        data_id,\n",
        "        additional_fields ->> 'path' AS path,\n",
        "        additional_fields ->> 'field_name' AS field\n",
        "    FROM\n",
        "        release_check\n",
        "    CROSS JOIN\n",
        "        jsonb_array_elements(\n",
        "            cove_output -> 'all_additional_fields'\n",
        "        ) AS additional_fields\n",
        "    INNER JOIN release ON release_check.release_id = release.id\n",
        "    WHERE\n",
        "        collection_id IN :collection_ids\n",
        "        AND random() < :sample_size\n",
        "),\n",
        "\n",
        "additional_fields AS (\n",
        "    SELECT DISTINCT\n",
        "        path,\n",
        "        field\n",
        "    FROM\n",
        "        additional_field_releases\n",
        "),\n",
        "\n",
        "examples AS (\n",
        "    SELECT DISTINCT ON (\n",
        "        additional_fields.path,\n",
        "        additional_fields.field)\n",
        "        additional_fields.path,\n",
        "        additional_fields.field,\n",
        "        ocid,\n",
        "        release_id,\n",
        "        data_id,\n",
        "        data\n",
        "    FROM\n",
        "        additional_fields\n",
        "    INNER JOIN additional_field_releases\n",
        "        ON\n",
        "            additional_fields.path = additional_field_releases.path\n",
        "            AND additional_fields.field = additional_field_releases.field\n",
        "    INNER JOIN data ON data.id = data_id\n",
        "    ORDER BY\n",
        "        additional_fields.path,\n",
        "        additional_fields.field\n",
        ")\n",
        "\n",
        "SELECT jsonb_build_object('releases', jsonb_agg(data)) AS release_package\n",
        "FROM\n",
        "    examples\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "YPHZX987mOG-"
      },
      "outputs": [],
      "source": [
        "render_json(additional_field_examples[\"release_package\"][0])"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "tMVFNCDrmb0l"
      },
      "source": [
        "### Additional codes"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "HIiCZQni2kbA"
      },
      "source": [
        "List additional codes in the context of an open codelist.\n",
        "\n",
        "Using additional codes in the context of a closed codelist is an error, and is reported in the *Check for structure and format errors* section."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "V8Gx2WAOqERy"
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "WITH check_results AS (\n",
        "    SELECT\n",
        "        *,\n",
        "        CASE\n",
        "            WHEN (release_type IN ('record', 'embedded_release'))\n",
        "                THEN\n",
        "                    record_check\n",
        "            ELSE\n",
        "                release_check\n",
        "        END AS results\n",
        "    FROM\n",
        "        release_summary\n",
        "    WHERE\n",
        "        collection_id IN :collection_ids\n",
        "        AND release_type <> 'compiled_release'\n",
        ")\n",
        "\n",
        "SELECT\n",
        "    collection_id,\n",
        "    release_type,\n",
        "    additional_open_codelist_values.value -> 'codelist' AS codelist,\n",
        "    codes.value AS code,\n",
        "    count(*) AS occurrences\n",
        "FROM\n",
        "    check_results\n",
        "CROSS JOIN\n",
        "    jsonb_each(\n",
        "        results -> 'additional_open_codelist_values'\n",
        "    ) AS additional_open_codelist_values\n",
        "CROSS JOIN jsonb_array_elements(value -> 'values') AS codes\n",
        "GROUP BY\n",
        "    collection_id,\n",
        "    release_type,\n",
        "    codelist,\n",
        "    code\n",
        "ORDER BY\n",
        "    collection_id,\n",
        "    release_type,\n",
        "    codelist,\n",
        "    count(*) DESC\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "MiFFM6zOlGTp"
      },
      "source": [
        "### Deprecated fields"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "kzUGDNQqrlKq"
      },
      "source": [
        "Before a field or codelist is removed from the standard, it is first marked as [deprecated](https://standard.open-contracting.org/latest/en/governance/deprecation/#deprecation).\n",
        "\n",
        "Use this section to check for deprecated fields."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "fZisRK9kr6MZ"
      },
      "source": [
        "List deprecated fields:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Oc0ex2vNlIOU"
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "SELECT DISTINCT ON (collection_id, path, deprecated_version, explanation)\n",
        "    collection_id,\n",
        "    regexp_replace(trim('\"' FROM paths::text), '\\/[0-9]+', '', 'g')\n",
        "    || '/'\n",
        "    || (deprecated_fields ->> 'field') AS path,\n",
        "    deprecated_fields -> 'explanation' -> 0 AS deprecated_version,\n",
        "    deprecated_fields -> 'explanation' -> 1 AS explanation,\n",
        "    ocid AS example_ocid\n",
        "FROM\n",
        "    release_check\n",
        "CROSS JOIN\n",
        "    jsonb_array_elements(\n",
        "        cove_output -> 'deprecated_fields'\n",
        "    ) AS deprecated_fields\n",
        "CROSS JOIN jsonb_array_elements(deprecated_fields -> 'paths') AS paths\n",
        "INNER JOIN release ON release_check.release_id = release.id\n",
        "WHERE\n",
        "    collection_id IN :collection_ids;\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "tnSzwOTTRbgE"
      },
      "source": [
        "## Calculate general statistics"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "rjpK7uocUeW4"
      },
      "source": [
        "Use this section to have an overview of the dataset in terms of fields published, stages covered and key summary statistics.  You can add any additional queries as needed.  "
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "7VRH7RmPRfBD"
      },
      "source": [
        "###Fields published"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "uVaXeoF7U6bV"
      },
      "source": [
        "Calculate the fields published"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "6KgBNO1QUuL4"
      },
      "outputs": [],
      "source": [
        "%%sql fields_table <<\n",
        "SELECT\n",
        "    path,\n",
        "    distinct_releases\n",
        "FROM\n",
        "    field_counts\n",
        "WHERE\n",
        "    release_type = 'compiled_release'\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "J7Eieoo9U8Ku"
      },
      "outputs": [],
      "source": [
        "fields_table"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Z-KSlo8Vb69M"
      },
      "source": [
        "Save fields table in spreadsheet:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "9CaaBbFLb6dl"
      },
      "outputs": [],
      "source": [
        "set_spreadsheet_name(\"Usability\")\n",
        "save_dataframe_to_sheet(fields_table, \"fields\")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "shGR-DXCcDkx"
      },
      "source": [
        "### Stages covered"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "APMabfEPcPui"
      },
      "outputs": [],
      "source": [
        "%%sql stages <<\n",
        "WITH field_counts AS (\n",
        "    SELECT *\n",
        "    FROM\n",
        "        field_counts\n",
        "    WHERE\n",
        "        release_type = 'compiled_release'\n",
        "        AND path IN (\n",
        "            'planning',\n",
        "            'tender',\n",
        "            'awards',\n",
        "            'contracts',\n",
        "            'contracts/implementation'\n",
        "        )\n",
        ")\n",
        "\n",
        "SELECT\n",
        "    CASE\n",
        "        WHEN paths.path = 'contracts/implementation'\n",
        "            THEN\n",
        "                'implementation'\n",
        "        ELSE\n",
        "            paths.path\n",
        "    END AS stage,\n",
        "    CASE\n",
        "        WHEN\n",
        "            paths.path IN ('planning', 'tender', 'contracts/implementation')\n",
        "            THEN\n",
        "                greatest(field_counts.object_property, 0)\n",
        "        ELSE\n",
        "            greatest(field_counts.array_count, 0)\n",
        "    END AS object_count\n",
        "FROM (\n",
        "    SELECT\n",
        "        unnest(\n",
        "            ARRAY[\n",
        "                'planning',\n",
        "                'tender',\n",
        "                'awards',\n",
        "                'contracts',\n",
        "                'contracts/implementation'\n",
        "            ]\n",
        "        ) AS path\n",
        ") AS paths\n",
        "LEFT JOIN field_counts USING (path)\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "f40SbJg0cbvp"
      },
      "outputs": [],
      "source": [
        "stages"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "zGNrN_occdW5"
      },
      "outputs": [],
      "source": [
        "plot_objects_per_stage(stages)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "TbpO9Jc6c6wQ"
      },
      "source": [
        "### Number of tenders and awards by date"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "twYK088Dc79V"
      },
      "outputs": [],
      "source": [
        "%%sql dates <<\n",
        "WITH tenders AS (\n",
        "    SELECT\n",
        "        extract(YEAR FROM tenderperiod_startdate) AS year,\n",
        "        count(*) AS tenders\n",
        "    FROM\n",
        "        tender_summary\n",
        "    WHERE\n",
        "        release_type = 'compiled_release'\n",
        "        AND tenderperiod_startdate IS NOT NULL\n",
        "    GROUP BY\n",
        "        year\n",
        "    ORDER BY\n",
        "        year\n",
        "),\n",
        "\n",
        "awards AS (\n",
        "    SELECT\n",
        "        extract(YEAR FROM date) AS year,\n",
        "        count(*) AS awards\n",
        "    FROM\n",
        "        awards_summary\n",
        "    WHERE\n",
        "        release_type = 'compiled_release'\n",
        "        AND date IS NOT NULL\n",
        "    GROUP BY\n",
        "        year\n",
        "    ORDER BY\n",
        "        year\n",
        ")\n",
        "\n",
        "SELECT\n",
        "    t.year::integer,\n",
        "    tenders,\n",
        "    awards\n",
        "FROM\n",
        "    tenders AS t\n",
        "FULL JOIN awards AS a ON t.year = a.year\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "cPEHTK67Mp6P"
      },
      "outputs": [],
      "source": [
        "dates"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "ZFUPPW51dF4m"
      },
      "outputs": [],
      "source": [
        "plot_objects_per_year(dates)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "g5n8QT44dKT1"
      },
      "source": [
        "### Procurement methods used"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "zybYaAktdPeq"
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "SELECT\n",
        "    procurementmethod,\n",
        "    count(DISTINCT ocid),\n",
        "    round((count(*) * 100.0 / sum(count(*)) OVER ()), 1) AS proportion\n",
        "FROM\n",
        "    tender_summary\n",
        "WHERE\n",
        "    release_type = 'compiled_release'\n",
        "GROUP BY\n",
        "    procurementmethod\n",
        "ORDER BY\n",
        "    proportion DESC\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "9nGLJhu9dgyU"
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "SELECT\n",
        "    tender ->> 'procurementMethodDetails' AS method,\n",
        "    count(DISTINCT ocid),\n",
        "    round((count(*) * 100.0 / sum(count(*)) OVER ()), 1) AS proportion\n",
        "FROM\n",
        "    tender_summary\n",
        "WHERE\n",
        "    release_type = 'compiled_release'\n",
        "GROUP BY\n",
        "    method\n",
        "ORDER BY\n",
        "    proportion DESC\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "_QlQDgw1hzdL"
      },
      "source": [
        "### Number of procedures by buyer"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "3V7AjxJXh17K"
      },
      "outputs": [],
      "source": [
        "%%sql buyers <<\n",
        "SELECT\n",
        "    identifier AS party_id,\n",
        "    party -> 'name' AS name,\n",
        "    count(DISTINCT ocid) AS total_tenders\n",
        "FROM\n",
        "    parties_summary\n",
        "WHERE\n",
        "    roles::text ILIKE '%%buyer%%'\n",
        "    AND release_type = 'compiled_release'\n",
        "GROUP BY\n",
        "    identifier,\n",
        "    party -> 'name'\n",
        "ORDER BY\n",
        "    total_tenders DESC\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "u-fpK7O-iEya"
      },
      "outputs": [],
      "source": [
        "buyers.head(10)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "KmgHVCXaiCeG"
      },
      "outputs": [],
      "source": [
        "plot_top_buyers(buyers.head(10))"
      ]
    },
    {
      "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_basic_criteria_checks",
      "provenance": [],
      "toc_visible": true
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}