component_setup_usability.ipynb

{
  "cells": [
    {
      "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)"
      ]
    }
  ],
  "metadata": {
    "colab": {
      "provenance": [],
      "toc_visible": true
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}