OC4IDS_Publisher_Status_Report.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "toc_visible": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "source": [
        "# OC4IDS Publisher Status Report"
      ],
      "metadata": {
        "id": "BZCAUfvh72HR"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## How to author a report\n",
        "\n",
        "1. Load the data that you want to report on using the [data import notebook](https://colab.research.google.com/github/open-contracting/oc4ids_database/blob/main/OC4IDS_Database_Data_Import.ipynb).\n",
        "2. Run the status checks using the [status checks notebook](https://github.com/open-contracting/notebooks-oc4ids/blob/main/OC4IDS_Quality_Criteria_Checks_and_Metrics.ipynb).\n",
        "3. Run the cells in [Appendix 1: Report Setup](#scrollTo=GYwdqQevW-zi).\n",
        "4. Run all cells in the [Summary](#scrollTo=H-5b3ACaQLCd), [Criteria](#scrollTo=7N-KAMJHQkad), [Checks](#scrollTo=JZ_mhib6Q_sK) and [Metrics](xmCUh-_CtLwA) sections.\n",
        "5. For each criteria and check whose methodology is `manual`:\n",
        "  1. Follow the instructions in the methodology\n",
        "  2. Add code and/or Markdown cells to document your findings (e.g. check failures).\n",
        "  3. Save the results.\n",
        "6. Run the cells in the Summary section to update the summary table.\n",
        "7. Remove this cell."
      ],
      "metadata": {
        "id": "bDxEIInHW3uN"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Introduction\n",
        "\n",
        "This report assesses the status of OC4IDS publications. It covers:\n",
        "\n",
        "* Quality [criteria](#scrollTo=7N-KAMJHQkad) that all OC4IDS publications should meet.\n",
        "* Other [checks](#scrollTo=JZ_mhib6Q_sK) on the quality of OC4IDS data.\n",
        "* [Metrics](#scrollTo=xmCUh-_CtLwA) related to the criteria and checks.\n",
        "* [Coverage](#scrollTo=yX4trq4L2Nro) measured against the OC4IDS schema, the core CoST IDS elements and the CoST IDS sustainability modules."
      ],
      "metadata": {
        "id": "TNfNpS368lL0"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Data sources\n",
        "\n",
        "This report covers data from the following OC4IDS publications:"
      ],
      "metadata": {
        "id": "JQ_EZ4QZNMqA"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# @title ### Publications\n",
        "\n",
        "%%sql\n",
        "\n",
        "SELECT\n",
        "    source_id,\n",
        "    data_version AS collection_date\n",
        "FROM\n",
        "    collection\n",
        "    JOIN run_collection ON collection.id = run_collection.collection_id\n",
        "WHERE\n",
        "    run_id = :run_id\n",
        "ORDER BY\n",
        "    source_id ASC;"
      ],
      "metadata": {
        "id": "gRYX7spMMj_E",
        "cellView": "form"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Summary\n",
        "\n",
        "This section provides a summary of criteria and check results. It is intended to support comparison between publications and assessment of the overall quality of the corpus of OC4IDS data.\n",
        "\n",
        "`True` indicates success against a criteria or check, `False` indicates failure and `None` indicates that a criteria or check was not assessed.\n",
        "\n"
      ],
      "metadata": {
        "id": "H-5b3ACaQLCd"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# @title ### Comparison table\n",
        "get_results(run_id = run_id, extra_results = manual_checks)"
      ],
      "metadata": {
        "id": "6V_VTbxjRcXM",
        "cellView": "form"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Criteria"
      ],
      "metadata": {
        "id": "7N-KAMJHQkad"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "This section assesses publications against pass/fail criteria that all publications should meet."
      ],
      "metadata": {
        "id": "7MQKyu0XLiFD"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Registered"
      ],
      "metadata": {
        "id": "wN_-IFUEpbK8"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "**Description:**\n",
        "\n",
        "The data uses an OC4IDS prefix in its project identifiers.\n",
        "\n",
        "**Methodology:** `automated`\n",
        "\n",
        "Check against the [list of registered prefixes](https://standard.open-contracting.org/infrastructure/latest/en/reference/prefixes/).\n",
        "\n",
        "**Output:**\n",
        "\n",
        "List of project identifiers without a registered prefix."
      ],
      "metadata": {
        "id": "HGRKr_QVpLen"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# @title #### Output\n",
        "get_output(run_id = run_id, check_id = 'criteria_registered')"
      ],
      "metadata": {
        "cellView": "form",
        "id": "6qEQUcTUsGvq"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# @title ### Results\n",
        "get_results(run_id = run_id, check_id='criteria_registered')"
      ],
      "metadata": {
        "cellView": "form",
        "id": "BlCvI6y_pdx8"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Discoverable\n",
        "\n",
        "**Description:**\n",
        "\n",
        "It is possible to discover the data by navigating a website whose homepage is indexed by popular web search engines.\n",
        "\n",
        "**Methodology:** `manual`\n",
        "\n",
        "Ask the publisher where the access methods are publicly listed and/or review the publisher’s website.\n",
        "\n",
        "**Output:**\n",
        "\n",
        "None"
      ],
      "metadata": {
        "id": "HiiuShTO0N0x"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# @title #### Results\n",
        "\n",
        "display_result_widgets('criteria_discoverable')"
      ],
      "metadata": {
        "id": "cOGfE_wM0PGQ",
        "cellView": "form"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Retrievable\n"
      ],
      "metadata": {
        "id": "8JPjfzP1dllK"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "**Description:**\n",
        "\n",
        "It is possible to automate the download of all the data, either using an HTML page listing bulk download URLs, or using only machine-readable data as input.\n",
        "\n",
        "**Methodology:** `manual`\n",
        "\n",
        "First review: Author and run a Python scraper.\n",
        "\n",
        "Subsequent reviews: Run the Python scraper and update if needed.\n",
        "\n",
        "**Output:**\n",
        "\n",
        "None"
      ],
      "metadata": {
        "id": "xOlpDzruFg6S"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# @title #### Results\n",
        "\n",
        "display_result_widgets('criteria_retrievable')"
      ],
      "metadata": {
        "id": "D6Lkiump9tdZ",
        "cellView": "form"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Reviewable\n",
        "\n",
        "**Description:**\n",
        "\n",
        "The OC4IDS Data Review Tool is able to report results on the data.\n",
        "\n",
        "**Methodology:** `manual`\n",
        "\n",
        "Check that libcoveoc4ids reports results.\n",
        "\n",
        "\n",
        "**Output:**\n",
        "\n",
        "None"
      ],
      "metadata": {
        "id": "c-1mtsjCdm3q"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# @title #### Results\n",
        "\n",
        "display_result_widgets('criteria_reviewable')"
      ],
      "metadata": {
        "cellView": "form",
        "id": "EIWZCNYT9wED"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Appropriate\n",
        "\n",
        "**Description:**\n",
        "\n",
        "Concepts are published in semantic accordance with the rules of the OC4IDS rather than using a non-OC4IDS field or code. There must not be more than 10 cases in which a concept is covered by a field or code in OC4IDS but is disclosed using another field or code.\n",
        "\n",
        "**Methodology:** `manual`\n",
        "\n",
        "Review the output to identify concepts covered by a field or code in OC4IDS but disclosed using another field or code.\n",
        "\n",
        "\n",
        "**Output:**\n",
        "\n",
        "List of additional fields and example values reported by the Data Review Tool."
      ],
      "metadata": {
        "id": "1DsxgMpOkSHn"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# @title #### Output\n",
        "\n",
        "%%sql\n",
        "\n",
        "SELECT\n",
        "    source_id,\n",
        "    output.key AS path,\n",
        "    output.value -> 'count' AS count,\n",
        "    output.value -> 'examples' AS examples\n",
        "FROM\n",
        "    check_results\n",
        "    CROSS JOIN jsonb_each(output) AS output\n",
        "    JOIN collection ON collection_id = collection.id\n",
        "WHERE\n",
        "    run_id = :run_id\n",
        "    AND check_id = 'criteria_appropriate'\n",
        "ORDER BY\n",
        "    source_id ASC;\n",
        "\n"
      ],
      "metadata": {
        "id": "RYw5S14TkbaA",
        "cellView": "form"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# @title #### Results\n",
        "\n",
        "display_result_widgets('criteria_appropriate')"
      ],
      "metadata": {
        "cellView": "form",
        "id": "bhCjj6NOkSHp"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Active\n",
        "\n",
        "**Description:**\n",
        "\n",
        "The data has been updated within the last 12 months.\n",
        "\n",
        "**Methodology:** `automated`\n",
        "\n",
        "There is a project with a top-level `updated` field value within the last 12 months.\n",
        "\n",
        "**Output:**\n",
        "\n",
        "None. For more information, see the [last updated metric](#scrollTo=RdJl4q6sw-pj)."
      ],
      "metadata": {
        "id": "weusVHH5HR3f"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# @title #### Results\n",
        "get_results(run_id = run_id, check_id='criteria_active')"
      ],
      "metadata": {
        "cellView": "form",
        "id": "-62LIbc2HTxO"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Documented\n",
        "\n",
        "**Description:**\n",
        "\n",
        "The publisher provides a publication policy/data user guide alongside the data.\n",
        "\n",
        "**Methodology:** `manual`\n",
        "\n",
        "Ask the publisher where the publication policy/data user guide is publicly available and/or review the publisher’s website.\n",
        "\n",
        "**Output:**\n",
        "\n",
        "None\n"
      ],
      "metadata": {
        "id": "B8MGSv4FdoTk"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# @title #### Results\n",
        "\n",
        "display_result_widgets('criteria_documented')"
      ],
      "metadata": {
        "id": "9Je0Ii1l9ypB",
        "cellView": "form"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Accessible\n",
        "\n",
        "**Description:**\n",
        "\n",
        "The data is available as a bulk download in tabular (CSV or spreadsheet) format.\n",
        "\n",
        "**Methodology:** `manual`\n",
        "\n",
        "Ask the publisher for a link to the bulk downloads and/or review the publisher’s website.\n",
        "\n",
        "**Output:**\n",
        "\n",
        "None"
      ],
      "metadata": {
        "id": "KdHoAqXfdphw"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# @title #### Results\n",
        "\n",
        "display_result_widgets('criteria_accessible')"
      ],
      "metadata": {
        "id": "H7RbhaKZ91NY",
        "cellView": "form"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Valid\n",
        "\n",
        "**Description:**\n",
        "\n",
        "The OC4IDS Data Review Tool reports no validation errors.\n",
        "\n",
        "**Methodology:** `automated`\n",
        "\n",
        "Use libcoveoc4ids to generate a list of validation errors.\n",
        "\n",
        "**Output:**\n",
        "\n",
        "None. For more information, see the [validation error count metric](#scrollTo=HYOcgsFSxKWD)."
      ],
      "metadata": {
        "id": "OCc1LeuaBlLs"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# @title #### Results\n",
        "\n",
        "get_results(run_id = run_id, check_id='criteria_valid')"
      ],
      "metadata": {
        "cellView": "form",
        "id": "0hPpMpb4KZWC"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Conformant\n",
        "\n",
        "**Description:**\n",
        "\n",
        "The OC4IDS Data Review Tool reports no structure warnings.\n",
        "\n",
        "**Methodology:** `automated`\n",
        "\n",
        "Use libcoveoc4ids to generate a list of structure warnings.\n",
        "\n",
        "**Output:**\n",
        "\n",
        "None\n"
      ],
      "metadata": {
        "id": "p-fjpLWuBoGY"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# @title ### Results\n",
        "\n",
        "get_results(run_id = run_id, check_id='criteria_conformant')"
      ],
      "metadata": {
        "cellView": "form",
        "id": "NHrR0KZHK64-"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Checks"
      ],
      "metadata": {
        "id": "JZ_mhib6Q_sK"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "This section documents the results of pass/fail checks on the quality of OC4IDS data."
      ],
      "metadata": {
        "id": "2VqJaRavLpXZ"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Sectors are standardised\n",
        "\n",
        "**Description:**\n",
        "\n",
        "Projects are classified against the OC4IDS sector codelist\n",
        "\n",
        "**Methodology:** `automated`\n",
        "\n",
        "Check that `sector` is present for at least one project and that it contains no values from outside the OC4IDS sector codelist.\n",
        "\n",
        "**Output:**\n",
        "\n",
        "List of additional sector codes."
      ],
      "metadata": {
        "id": "Qe4mOxZwHSQ4"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# @title ### Output\n",
        "\n",
        "%%sql\n",
        "SELECT\n",
        "    source_id,\n",
        "    output -> 'all_projects' AS additional_codes\n",
        "FROM\n",
        "    check_results\n",
        "    CROSS JOIN jsonb_each(output)\n",
        "    JOIN collection ON collection_id = collection.id\n",
        "WHERE\n",
        "    run_id = :run_id\n",
        "    AND check_id = 'semantics_sector_codelist'\n",
        "ORDER BY\n",
        "    source_id ASC;\n",
        "\n"
      ],
      "metadata": {
        "id": "cHj4fe5dLgOQ",
        "cellView": "form"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# @title ### Results\n",
        "\n",
        "get_results(run_id = run_id, check_id='semantics_sector_codelist')"
      ],
      "metadata": {
        "id": "hlseN_28Mmi_",
        "cellView": "form"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Public authority names are realistic\n",
        "\n",
        "**Description:**\n",
        "\n",
        "Check that a sample of public authority names are realistic e.g. they are government departments, rather than suppliers or individuals etc.\n",
        "\n",
        "**Methodology:** `manual`\n",
        "\n",
        "Review the output and check that names are realistic.\n",
        "\n",
        "**Output:**\n",
        "\n",
        "Sample of `publicAuthority.name` values."
      ],
      "metadata": {
        "id": "XSj62NU0QStw"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# @title #### Output\n",
        "\n",
        "get_output(run_id, 'semantics_public_authority_names')"
      ],
      "metadata": {
        "id": "RQWIqEptsJZk",
        "cellView": "form"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# @title ##### Results\n",
        "\n",
        "display_result_widgets('semantics_public_authority_names')"
      ],
      "metadata": {
        "id": "Q-zHHRZN-Ctg",
        "cellView": "form"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Supplier names are realistic\n",
        "\n",
        "**Description:**\n",
        "\n",
        "Check that a sample of supplier names are realistic e.g. they are private businesses, rather than government departments etc.\n",
        "\n",
        "\n",
        "**Methodology:** `manual`\n",
        "\n",
        "Review the output and check that names are realistic.\n",
        "\n",
        "**Output:**\n",
        "\n",
        "Sample of `contractingProcesses/summary/suppliers/name` values.\n"
      ],
      "metadata": {
        "id": "xvWZ1EQQQWaQ"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# @title ### Output\n",
        "\n",
        "get_output(run_id, 'semantics_supplier_names')"
      ],
      "metadata": {
        "id": "kwpltDoJsjil",
        "cellView": "form"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# @title #### Results\n",
        "\n",
        "display_result_widgets('semantics_supplier_names')"
      ],
      "metadata": {
        "id": "TPeHYKSM-ib-",
        "cellView": "form"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Project budgets are realistic\n",
        "\n",
        "**Description:**\n",
        "\n",
        "Check that project budgets are non-zero and less than 5bn USD.\n",
        "\n",
        "**Methodology:** `automated`\n",
        "\n",
        "Convert `project.budget` to USD and check against the thresholds.\n",
        "\n",
        "**Output:**\n",
        "\n",
        "List of unrealistic budgets.\n"
      ],
      "metadata": {
        "id": "iHKmCtHVQYj4"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# @title #### Output\n",
        "\n",
        "get_output(run_id, 'semantics_budgets').rename(columns={\"output\": \"budget_usd\"})"
      ],
      "metadata": {
        "id": "F2cgZN-iOhbB",
        "cellView": "form"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# @title ### Results\n",
        "\n",
        "get_results(run_id, 'semantics_budgets')"
      ],
      "metadata": {
        "id": "dEfiQhU4QrL2",
        "cellView": "form"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Contract values are realistic\n",
        "\n",
        "**Description:**\n",
        "\n",
        "Check that contract values are non-zero and less than 5bn USD.\n",
        "\n",
        "**Methodology:** `automated`\n",
        "\n",
        "Convert `contractingProcesses/summary/contractValue` to USD and check against the thresholds.\n",
        "\n",
        "**Output:**\n",
        "\n",
        "List of unrealistic contract values.\n"
      ],
      "metadata": {
        "id": "xDfgqn2oR7t-"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# @title #### Output\n",
        "\n",
        "get_output(run_id, 'semantics_contract_values').rename(columns={\"output\": \"contract_value_usd\"})"
      ],
      "metadata": {
        "id": "iW_JfUTLR7uA",
        "cellView": "form"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# @title ### Results\n",
        "\n",
        "get_results(run_id, 'semantics_contract_values')"
      ],
      "metadata": {
        "id": "0t12V9eVR7uA",
        "cellView": "form"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Funder names are realistic\n",
        "\n",
        "**Description:**\n",
        "\n",
        "Check that a sample of funder names are realistic e.g. they are government agencies, donors or multi-lateral financial institutions, rather than private businesses.\n",
        "\n",
        "**Methodology:** `manual`\n",
        "\n",
        "Review the output and check that names are realistic.\n",
        "\n",
        "**Output:**\n",
        "\n",
        "Sample of `parties/name` values.\n"
      ],
      "metadata": {
        "id": "HeNF2FIJRDRO"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# @title #### Output\n",
        "get_output(run_id, 'semantics_funder_names')"
      ],
      "metadata": {
        "id": "M0A5VbD6sp8o",
        "cellView": "form"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# @title #### Results\n",
        "\n",
        "display_result_widgets('semantics_public_funder_names')"
      ],
      "metadata": {
        "id": "klkAcbsC-bNO",
        "cellView": "form"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Dates are realistic\n",
        "\n",
        "**Description:**\n",
        "\n",
        "Check that dates are after 1st January 1970 and before 1st January 2050.\n",
        "\n",
        "**Methodology:** `automated`\n",
        "\n",
        "Check the following dates against the thresholds:\n",
        "\n",
        "* `updated`\n",
        "* `period/startDate`\n",
        "* `period/endDate`\n",
        "* `completion/endDate`\n",
        "\n",
        "**Output:**\n",
        "\n",
        "List of unrealistic dates."
      ],
      "metadata": {
        "id": "9moBFRMRQfTp"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# @title #### Output\n",
        "\n",
        "get_output(run_id, 'semantics_dates')"
      ],
      "metadata": {
        "id": "TJjNEizbS_m-",
        "cellView": "form"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# @title #### Results\n",
        "\n",
        "get_results(run_id, 'semantics_dates')"
      ],
      "metadata": {
        "id": "7U4kTbcETHJc",
        "cellView": "form"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Roles are set\n",
        "\n",
        "**Description:**\n",
        "\n",
        "Check that organization `.roles` are set according to their references.\n",
        "\n",
        "**Methodology:** `automated`\n",
        "\n",
        "Check that:\n",
        "\n",
        "* The organization referenced in `publicAuthority` has 'publicAuthority' in `.roles`.\n",
        "* The organizations referenced in `budget/sourceParty` have 'sourceParty' in `.roles`.\n",
        "* The organizations referenced in `contractingProcesses/summary/tender/tenderers` have 'tenderer' in `.roles`.\n",
        "* The organization referenced in `contractingProcesses/summary/tender/procuringEntity` has 'procuringEntity' in `.roles`.\n",
        "* The organization referenced in `contractingProcesses/summary/tender/administrativeEntity` has 'administrativeEntity' in `.roles`.\n",
        "* The organizations referenced in `contractingProcesses/summary/suppliers` have 'supplier' in `.roles`.\n",
        "\n",
        "**Output:**\n",
        "\n",
        "List of missing roles."
      ],
      "metadata": {
        "id": "FiUy7VcdThiR"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# @title ### Output\n",
        "\n",
        "get_output(run_id, 'semantics_role_coherence')"
      ],
      "metadata": {
        "id": "WOf-qggsUpUY",
        "cellView": "form"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# @title ### Results\n",
        "\n",
        "get_results(run_id, 'semantics_role_coherence')"
      ],
      "metadata": {
        "id": "ZjKxr5htUxNn",
        "cellView": "form"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Coordinates are valid\n",
        "\n",
        "**Description:**\n",
        "\n",
        "Check that project location coordinates are valid.\n",
        "\n",
        "**Methodology:** `automated`\n",
        "\n",
        "Check that `locations/geometry/coordinates` are in the range of [-90, 90] for latitudes and [-180, 180] for longitudes.\n",
        "\n",
        "**Output:**\n",
        "\n",
        "List of invalid coordinates."
      ],
      "metadata": {
        "id": "sQ7O08x6QlLN"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# @title ### Output\n",
        "\n",
        "get_output(run_id, 'semantics_coordinates')"
      ],
      "metadata": {
        "cellView": "form",
        "id": "V70Sr_BJVFPT"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# @title ### Results\n",
        "\n",
        "get_results(run_id, 'semantics_coordinates')"
      ],
      "metadata": {
        "cellView": "form",
        "id": "bF67kbIkVOZL"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Metrics"
      ],
      "metadata": {
        "id": "xmCUh-_CtLwA"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "This section provides measurements related to the criteria and checks. There are no judgements associated with these measurements, rather they provide additional context to the pass/fail criteria and checks."
      ],
      "metadata": {
        "id": "X6Z-z3P5L53X"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### New project count\n",
        "\n",
        "**Description:**\n",
        "\n",
        "A count of projects added since the previous report.\n",
        "\n",
        "**Methodology:** `automated`\n",
        "\n",
        "Identify projects added since the previous report by comparing project identifiers (`id`)."
      ],
      "metadata": {
        "id": "DOmHptJjw8Vq"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# @title Output\n",
        "\n",
        "get_metric_output(run_id, 'metrics_new_projects')"
      ],
      "metadata": {
        "id": "qGsn7Gtjt6WD",
        "cellView": "form"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Last updated date\n",
        "\n",
        "**Description:**\n",
        "\n",
        "The last updated date of the most recently updated project.\n",
        "\n",
        "\n",
        "**Methodology:** `automated`\n",
        "\n",
        "The maximum `date` amongst the projects in the dataset."
      ],
      "metadata": {
        "id": "RdJl4q6sw-pj"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# @title #### Output\n",
        "\n",
        "get_metric_output(run_id, 'metrics_last_updated')"
      ],
      "metadata": {
        "id": "bOiC9EjAt_Jj",
        "cellView": "form"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Earliest project start date\n",
        "\n",
        "**Description:**\n",
        "\n",
        "The earliest project start date.\n",
        "\n",
        "**Methodology:** `automated`\n",
        "\n",
        "The minimum `period/startDate` amongst the projects in the dataset.\n"
      ],
      "metadata": {
        "id": "TrSmaZRsxAw0"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# @title #### Output\n",
        "\n",
        "get_metric_output(run_id, 'metrics_earliest_start_date')"
      ],
      "metadata": {
        "id": "QpN42-kIvyWy",
        "cellView": "form"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Latest project end date\n",
        "\n",
        "**Description:**\n",
        "\n",
        "The latest project end date.\n",
        "\n",
        "**Methodology:** `automated`\n",
        "\n",
        "The maximum `period/endDate` amongst the projects in the dataset."
      ],
      "metadata": {
        "id": "M4HHY6LzxCwO"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# @title #### Output\n",
        "\n",
        "get_metric_output(run_id, 'metrics_latest_end_date')"
      ],
      "metadata": {
        "id": "YkRa2-eLwNNY",
        "cellView": "form"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Additional field count\n",
        "\n",
        "**Description:**\n",
        "\n",
        "A count of non-OC4IDS fields in the dataset.\n",
        "\n",
        "\n",
        "**Methodology:** `automated`\n",
        "\n",
        "Use libcoveoc4ids to generate a count of additional fields.\n"
      ],
      "metadata": {
        "id": "q_qHpzONxE4b"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# @title #### Output\n",
        "\n",
        "get_metric_output(run_id, 'metrics_additional_field_count')"
      ],
      "metadata": {
        "id": "h-UgKGnkwPvr",
        "cellView": "form"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Project count\n",
        "\n",
        "**Description:**\n",
        "\n",
        "A count of projects in the dataset.\n",
        "\n",
        "**Methodology:** `automated`\n",
        "\n",
        "Count the projects in the dataset."
      ],
      "metadata": {
        "id": "C0iSv5CLxHvi"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# @title #### Output\n",
        "\n",
        "get_metric_output(run_id, 'metrics_project_count')"
      ],
      "metadata": {
        "id": "iKZgjYoqwUSN",
        "cellView": "form"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Validation error count\n",
        "\n",
        "**Description:**\n",
        "\n",
        "A count of the validation errors reported by the OC4IDS data review tool.\n",
        "\n",
        "**Methodology:** `automated`\n",
        "\n",
        "Count the types of validation error reported by libcoveoc4ids, not the number of occurrences of each error type.\n"
      ],
      "metadata": {
        "id": "HYOcgsFSxKWD"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# @title #### Output\n",
        "\n",
        "get_metric_output(run_id, 'metrics_validation_error_count')"
      ],
      "metadata": {
        "id": "c0zYGfNuwoDE",
        "cellView": "form"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Structure warning count\n",
        "\n",
        "**Description:**\n",
        "\n",
        "A count of the structure warnings reported by the OC4IDS data review tool.\n",
        "\n",
        "**Methodology:** `automated`\n",
        "\n",
        "Count the structure warnings reported by libcoveoc4ids, not the number of occurrences of each structure warning"
      ],
      "metadata": {
        "id": "7Ri_p6D6xMw4"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# @title #### Output\n",
        "\n",
        "get_metric_output(run_id, 'metrics_structure_warning_count')"
      ],
      "metadata": {
        "id": "MomNsH_jwum8",
        "cellView": "form"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Coverage"
      ],
      "metadata": {
        "id": "yX4trq4L2Nro"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "This section measures data coverage against the OC4IDS schema, the core CoST IDS elements and the CoST IDS Sustainability modules. That is, which of the fields in the schema, or CoST IDS elements, are present in each data source."
      ],
      "metadata": {
        "id": "DgtG1mYPPmf5"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### OC4IDS"
      ],
      "metadata": {
        "id": "9Kc78jrY2O8g"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "If a field is on an object in an array, then coverage is reported for each object in the array. Example: There are 100 projects, all of which have 5 parties. The check for the `parties` field will be reported out of 100, but the checks for its child fields (like `parties.id`) will be reported out of 500.\n",
        "\n",
        "Child fields are reported in the context of their parent field. Example: There are 100 projects, 10 of which set `publicAuthority`. The check for the `publicAuthority` field will be reported out of 100, but the checks for its child fields (like `publicAuthority.id`) will be reported out of 10."
      ],
      "metadata": {
        "id": "N69wd7nxP389"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# @title #### Output\n",
        "\n",
        "collection_ids = %sql select collection_id, source_id from run_collection join collection on run_collection.collection_id = collection.id where run_id = :run_id order by source_id asc;\n",
        "\n",
        "oc4ids_coverage = pd.DataFrame()\n",
        "\n",
        "for collection_id in collection_ids['collection_id']:\n",
        "  oc4ids_coverage = pd.concat([oc4ids_coverage, get_schema_coverage(collection_id)])\n",
        "\n",
        "oc4ids_coverage = oc4ids_coverage.merge(collection_ids, on=\"collection_id\")\n",
        "\n",
        "oc4ids_coverage['coverage'] = oc4ids_coverage['coverage'].astype(float)\n",
        "\n",
        "oc4ids_coverage = oc4ids_coverage.pivot(index=['path', 'title', 'required'], columns='source_id', values='coverage')\n",
        "\n",
        "cm = sns.light_palette(\"green\", as_cmap=True)\n",
        "\n",
        "oc4ids_coverage.style \\\n",
        "  .format(precision = 2) \\\n",
        "  .background_gradient(cmap=cm)"
      ],
      "metadata": {
        "id": "KeN-VSNR4pQ0",
        "cellView": "form"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### CoST IDS"
      ],
      "metadata": {
        "id": "X-C1udI32QZ6"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "Coverage is measured based on how many projects include the required fields for an element. There may be some false positives, since more granular levels of coverage are not measured. For example, how many individual contracting processes within a project include the required fields. See this issue for a detailed explanation."
      ],
      "metadata": {
        "id": "KArVQzr8P_LD"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# @title #### Output\n",
        "\n",
        "results = get_indicator_coverage_results(run_id, 'cost_ids')\n",
        "\n",
        "results['coverage'] = results['coverage'].astype(float)\n",
        "\n",
        "results = results.pivot(index=['indicator'], columns='source_id', values='coverage')\n",
        "\n",
        "cm = sns.light_palette(\"green\", as_cmap=True)\n",
        "\n",
        "results.style \\\n",
        "  .format( precision=2) \\\n",
        "  .background_gradient(cmap=cm)"
      ],
      "metadata": {
        "id": "JLlxjXBcLY-x",
        "cellView": "form"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Sustainability modules"
      ],
      "metadata": {
        "id": "VgU4OXLV2Rik"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "Coverage is measured based on how many projects include the required fields for an element. There may be some false positives, since more granular levels of coverage are not measured. For example, how many individual contracting processes within a project include the required fields. See this issue for a detailed explanation."
      ],
      "metadata": {
        "id": "JpjqtDSCQAFq"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# @title #### Output\n",
        "\n",
        "results = get_indicator_coverage_results(run_id, 'sustainability_modules')\n",
        "\n",
        "results['coverage'] = results['coverage'].astype(float)\n",
        "\n",
        "results = results.pivot(index=['indicator'], columns='source_id', values='coverage')\n",
        "\n",
        "cm = sns.light_palette(\"green\", as_cmap=True)\n",
        "\n",
        "results.style \\\n",
        "  .format( precision=2) \\\n",
        "  .background_gradient(cmap=cm)"
      ],
      "metadata": {
        "id": "Hox7dYkM72rz"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Appendix 1: Report Setup"
      ],
      "metadata": {
        "id": "GYwdqQevW-zi"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# @title ### Install requirements\n",
        "# @markdown After running this cell, you must restart the session (⌘/Ctrl+M+. or Runtime > Restart session)\n",
        "!pip install --upgrade ipython-sql > pip.log\n",
        "!pip install --upgrade pandas>=2.2"
      ],
      "metadata": {
        "id": "xiM0ytgUcjkY"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "27yRx-oaXPtd"
      },
      "outputs": [],
      "source": [
        "# @title ### Connect to the database\n",
        "# @markdown ODS users: enter the password for the `readonly` user, from the ODS password database.\n",
        "import getpass\n",
        "\n",
        "print('Enter your credentials')\n",
        "user = 'readonly'\n",
        "password = getpass.getpass('Password:')\n",
        "\n",
        "connection_string = 'postgresql://' + user + ':' + password + '@oc4ids-database-2.cuujgua4wses.us-east-1.rds.amazonaws.com/postgres'\n",
        "%load_ext sql\n",
        "%sql $connection_string\n",
        "%config SqlMagic.autopandas = True  # Return Pandas DataFrames instead of regular result sets\n",
        "%config SqlMagic.displaycon = False  # Don't show connection string after execute\n",
        "%config SqlMagic.feedback = False  # Don't print number of rows affected by DML\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "VjuPJaBUMapU"
      },
      "outputs": [],
      "source": [
        "# @title Setup notebook environment\n",
        "\n",
        "# https://colab.research.google.com/notebooks/data_table.ipynb\n",
        "%config SqlMagic.style = '_DEPRECATED_DEFAULT'\n",
        "%load_ext google.colab.data_table\n",
        "from google.colab.data_table import DataTable\n",
        "DataTable.max_columns = 50 # Increase max columns so that dataframes with many columns are rendered as data tables\n",
        "DataTable.include_index = False # Remove the index from data tables for easier copy-pasting to Google Docs\n",
        "DataTable.num_rows_per_page = 10\n",
        "\n",
        "import functools\n",
        "import ipywidgets\n",
        "import pandas as pd\n",
        "import seaborn as sns\n",
        "\n",
        "from IPython.display import display\n",
        "\n",
        "manual_checks = {}"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# @title Choose a `run_id` to report on\n",
        "from ipywidgets import interact\n",
        "\n",
        "def set_run_id(id):\n",
        "  global run_id\n",
        "  run_id = id\n",
        "\n",
        "  global source_ids\n",
        "  source_ids = %sql select source_id from run_collection join collection on run_collection.collection_id = collection.id where run_id = :run_id order by source_id asc;\n",
        "  source_ids = source_ids['source_id']\n",
        "\n",
        "run_ids = %sql select distinct run_id from run_collection order by run_id desc;\n",
        "\n",
        "interact(set_run_id, id=run_ids['run_id']);\n",
        "print('Run id selected:', run_id)"
      ],
      "metadata": {
        "id": "F94nHDR4eaus"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# @title ### Define functions\n",
        "\n",
        "def get_results(run_id = run_id, check_id = None, extra_results = None):\n",
        "\n",
        "  query = f\"\"\"\n",
        "\n",
        "  select\n",
        "    check_id as check,\n",
        "    source_id,\n",
        "    result\n",
        "  from\n",
        "    check_results\n",
        "  join collection on\n",
        "    collection_id = collection.id\n",
        "  where\n",
        "    run_id = '{run_id}'\n",
        "    and (left(check_id, 8) = 'criteria'\n",
        "      or left(check_id, 9) = 'semantics')\n",
        "    {f\"and check_id = '{check_id}'\" if check_id else \"\"}\n",
        "  order by\n",
        "    array_position(array[\n",
        "    'criteria_registered',\n",
        "    'criteria_discoverable',\n",
        "    'criteria_retrievable',\n",
        "    'criteria_reviewable',\n",
        "    'criteria_appropriate',\n",
        "    'criteria_active',\n",
        "    'criteria_documented',\n",
        "    'criteria_accessible',\n",
        "    'criteria_valid',\n",
        "    'criteria_conformant',\n",
        "    'semantics_sector_codelist',\n",
        "    'semantics_public_authority_names',\n",
        "    'semantics_supplier_names',\n",
        "    'semantics_budgets',\n",
        "    'semantics_contract_values',\n",
        "    'semantics_funder_names',\n",
        "    'semantics_dates',\n",
        "    'semantics_role_coherence',\n",
        "    'semantics_coordinates'],\n",
        "    check_id) asc,\n",
        "    source_id asc;\n",
        "\n",
        "  \"\"\"\n",
        "\n",
        "  results = %sql {query}\n",
        "\n",
        "  if extra_results is not None:\n",
        "    for check, source in extra_results.items():\n",
        "      for source_id, result in source.items():\n",
        "        results = results._append(pd.DataFrame([{'check': check, 'source_id': source_id, 'result': result}]))\n",
        "\n",
        "  # check values are repeated for some checks. dropping duplicates to retain only the extra_results\n",
        "  results = results.drop_duplicates(subset=['check','source_id'], keep='last')\n",
        "\n",
        "  results = results.pivot(index=['check'], columns='source_id', values='result')\n",
        "\n",
        "# Rename checks from id to text\n",
        "  rename_map = {\n",
        "    'criteria_registered': 'Criteria: Registered',\n",
        "    'criteria_discoverable': 'Criteria: Discoverable',\n",
        "    'criteria_retrievable': 'Criteria: Retrievable',\n",
        "    'criteria_reviewable': 'Criteria: Reviewable',\n",
        "    'criteria_appropriate': 'Criteria: Appropriate',\n",
        "    'criteria_active': 'Criteria: Active',\n",
        "    'criteria_documented': 'Criteria: Documented',\n",
        "    'criteria_accessible': 'Criteria: Accessible',\n",
        "    'criteria_valid': 'Criteria: Valid',\n",
        "    'criteria_conformant': 'Criteria: Conformant',\n",
        "    'semantics_sector_codelist': 'Check: Sectors are standardised',\n",
        "    'semantics_public_authority_names': 'Check: Public authority names are realistic',\n",
        "    'semantics_supplier_names': 'Check: Supplier names are realistic',\n",
        "    'semantics_budgets': 'Check: Project budgets are realistic',\n",
        "    'semantics_contract_values': 'Check: Contract values are realistic',\n",
        "    'semantics_funder_names': 'Check: Funder names are realistic',\n",
        "    'semantics_dates': 'Check: Dates are realistic',\n",
        "    'semantics_role_coherence': 'Check: Roles are set',\n",
        "    'semantics_coordinates': 'Check: Coordinates are valid'\n",
        "  }\n",
        "  results.rename(index=rename_map, inplace=True)\n",
        "\n",
        "  styler = results.style\n",
        "\n",
        "  return styler.map(lambda x: 'background-color:rgba(0, 255, 0, 0.25);' if x == True else ('background-color:rgba(255, 0, 0, 0.25);' if x == False else 'background-color:rgba(100, 100, 100, 0.25);'))\n",
        "\n",
        "def get_output(run_id, check_id):\n",
        "\n",
        "  query = f\"\"\"\n",
        "\n",
        "  select\n",
        "    source_id,\n",
        "    key as project_id,\n",
        "    value as output\n",
        "  from\n",
        "    check_results\n",
        "  cross join\n",
        "    jsonb_each(output)\n",
        "  join collection on\n",
        "    collection_id = collection.id\n",
        "  where\n",
        "    run_id = '{run_id}'\n",
        "  and\n",
        "    check_id = '{check_id}'\n",
        "  order by\n",
        "    check_id, source_id;\n",
        "\n",
        "  \"\"\"\n",
        "\n",
        "  output = %sql {query}\n",
        "\n",
        "  return output\n",
        "\n",
        "def get_metric_output(run_id, check_id):\n",
        "\n",
        "  query = f\"\"\"\n",
        "\n",
        "  select\n",
        "    source_id,\n",
        "    coalesce(output->'count', output->'date') as {check_id}\n",
        "  from check_results\n",
        "  join collection on\n",
        "    collection_id = collection.id\n",
        "  where\n",
        "    run_id = '{run_id}'\n",
        "  and\n",
        "    check_id = '{check_id}'\n",
        "  order by\n",
        "    check_id, source_id;\n",
        "\n",
        "  \"\"\"\n",
        "\n",
        "  output = %sql {query}\n",
        "\n",
        "  return output\n",
        "\n",
        "def save_results(b, check_id, widgets):\n",
        "  global manual_checks\n",
        "\n",
        "  results = {source_id: widget.value for source_id, widget in widgets.items()}\n",
        "\n",
        "  manual_checks[check_id] = results\n",
        "\n",
        "def display_result_widgets(check_id):\n",
        "  global source_ids\n",
        "\n",
        "  widgets = {}\n",
        "\n",
        "  description_length = max([len(source_id) for source_id in source_ids])\n",
        "\n",
        "  for source_id in source_ids:\n",
        "\n",
        "    widgets[source_id] = ipywidgets.Dropdown(\n",
        "      options=[True, False, None],\n",
        "      value=None,\n",
        "      description=f'{source_id}:',\n",
        "      disabled=False,\n",
        "      layout={'width': '35em'},\n",
        "      style={'description_width': f'{description_length}em'}\n",
        "  )\n",
        "\n",
        "  button = ipywidgets.Button(description=\"Save\")\n",
        "\n",
        "  for widget in widgets.values():\n",
        "    display(widget)\n",
        "\n",
        "  display(button)\n",
        "\n",
        "  button.on_click(functools.partial(save_results, check_id = check_id, widgets = widgets))\n",
        "\n",
        "def get_schema_coverage(collection_id):\n",
        "\n",
        "  query = \"\"\"\n",
        "\n",
        "    WITH project_count AS (\n",
        "    SELECT\n",
        "      count(*)::NUMERIC\n",
        "    FROM\n",
        "      projects\n",
        "    WHERE\n",
        "      collection_id = :collection_id ),\n",
        "    field_counts_filtered AS (\n",
        "    SELECT\n",
        "      *\n",
        "    FROM\n",
        "      field_counts\n",
        "    WHERE\n",
        "      field_counts.collection_id = :collection_id\n",
        "    )\n",
        "    SELECT DISTINCT ON (oc4ids_schema.path)\n",
        "      :collection_id as collection_id,\n",
        "      oc4ids_schema.path,\n",
        "      title,\n",
        "      CASE\n",
        "        WHEN substring(RANGE FROM 1 FOR 1)::int = 1 THEN TRUE\n",
        "        ELSE FALSE\n",
        "      END AS required,\n",
        "      COALESCE(CASE\n",
        "        WHEN array_length(field_counts.path_array,\n",
        "        1) = 1 THEN round(field_counts.object_property::NUMERIC / (\n",
        "        SELECT\n",
        "          *\n",
        "        FROM\n",
        "          project_count),\n",
        "        2)\n",
        "        ELSE\n",
        "        CASE\n",
        "          WHEN parent_field_counts.array_count = 0 THEN round(field_counts.object_property::NUMERIC / parent_field_counts.object_property::NUMERIC,\n",
        "          2)\n",
        "          ELSE round(field_counts.object_property::NUMERIC / parent_field_counts.array_count::NUMERIC,\n",
        "          2)\n",
        "        END\n",
        "      END, 0.00) AS coverage\n",
        "    FROM\n",
        "      oc4ids_schema\n",
        "    LEFT JOIN field_counts_filtered AS field_counts ON\n",
        "      oc4ids_schema.path = field_counts.path\n",
        "    LEFT JOIN field_counts_filtered AS parent_field_counts ON\n",
        "      array_to_string(field_counts.path_array[1:array_length(field_counts.path_array,\n",
        "      1)-1],\n",
        "      '/') = parent_field_counts.PATH;\n",
        "\n",
        "  \"\"\"\n",
        "\n",
        "  results = %sql {query}\n",
        "\n",
        "  return results\n",
        "\n",
        "def get_indicator_coverage_results(run_id, indicator_source):\n",
        "\n",
        "  query = \"\"\"\n",
        "\n",
        "    select\n",
        "      collection_id,\n",
        "      source_id,\n",
        "      indicator,\n",
        "      successes/checks as coverage\n",
        "    from\n",
        "      indicator_coverage\n",
        "    join\n",
        "      collection on collection_id = collection.id\n",
        "    where\n",
        "      indicator_source = :indicator_source\n",
        "    and\n",
        "      run_id = :run_id;\n",
        "\n",
        "  \"\"\"\n",
        "\n",
        "  results = %sql {query}\n",
        "\n",
        "  return results"
      ],
      "metadata": {
        "id": "jIJx1r9WqlSS"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [],
      "metadata": {
        "id": "HVVqw4WydrxT"
      },
      "execution_count": null,
      "outputs": []
    }
  ]
}