template_structure_and_format_feedback.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": "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",
        "  - *load*: When Kingfisher Collect yielded a `FileError` item.\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",
        "**Note:** `FileError` items in Kingfisher Process are [scheduled for removal](https://github.com/open-contracting/kingfisher-process/issues/366), as they also appear in the crawl's log file.\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": "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": "73ktdYktAVoO"
      },
      "source": [
        "## Check scope"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "T4Gb2g11BXwt"
      },
      "source": [
        "\n",
        "\n",
        "Use this section to check:\n",
        "\n",
        "* how many releases, records and compiled releases your data contains\n",
        "* what stages of the contracting process your data covers\n",
        "* what date range your data covers\n",
        "\n",
        "If you are preparing an [Ad-hoc structure and format feedback](https://docs.google.com/document/d/1_k7eA2rI-k5EH8VESkVAB73wa_qrpplL-7dKgMLTGZc/edit#heading=h.i7tpu8c49dcv), you might skip this section."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ycCwGfGkA6au"
      },
      "source": [
        "### Release and record counts"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "s-i5qCmvCsHA"
      },
      "source": [
        "Collections in Kingfisher Process contain either [releases](https://standard.open-contracting.org/latest/en/schema/reference/), [records](https://standard.open-contracting.org/latest/en/schema/records_reference/) or [compiled releases](https://standard.open-contracting.org/latest/en/schema/records_reference/#compiled-release). Kingfisher Process creates compiled release collections from release or record collections.\n",
        "\n",
        "Use this section to check that the data contains the expected number of releases, records and compiled releases. Where possible, you should check these numbers against the total number of results available in the frontend of the data source.\n",
        "\n",
        "Count the number of releases, records and compiled releases, for each collection.\n",
        "\n",
        "**Note:** These columns are not yet populated in version 2 of Kingfisher Process. Comment on [this issue](https://github.com/open-contracting/kingfisher-process/issues/370) to prioritize it."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "MVJ6sIWeAZzD"
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "SELECT\n",
        "    id AS collection_id,\n",
        "    cached_releases_count AS releases_count,\n",
        "    cached_records_count AS records_count,\n",
        "    cached_compiled_releases_count AS compiled_releases_count\n",
        "FROM\n",
        "    collection\n",
        "WHERE\n",
        "    id IN :collection_ids\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "HD9LE4SLCzPg"
      },
      "source": [
        "### Contracting process stages"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "o5hZPAo5yHMm"
      },
      "source": [
        "Use this section to check that the data covers the expected stages of the contracting process."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Tfq5haelyH3E"
      },
      "source": [
        "#### Release tags"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "cTZiHSkvC3__"
      },
      "source": [
        "[Release tags](https://standard.open-contracting.org/latest/en/schema/codelists/#release-tag) indicate the stage of a contracting process to which a release is related."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "EqsaSZj6DkuS"
      },
      "source": [
        "Count the number of releases, for each release tag:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "WwgeZMX5Ame4"
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "SELECT\n",
        "    collection_id,\n",
        "    release_type,\n",
        "    tag,\n",
        "    count(*)\n",
        "FROM\n",
        "    release_summary\n",
        "GROUP BY\n",
        "    collection_id,\n",
        "    release_type,\n",
        "    tag\n",
        "ORDER BY\n",
        "    collection_id\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "vTzQeN_kyJ_E"
      },
      "source": [
        "#### Objects per stage"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "8EmYSnoDnOmT"
      },
      "source": [
        "In OCDS, data is organized into objects, for each stage of a contracting process. Each compiled release has: at most one `Planning` object, at most one `Tender` object, any number of `Award` objects, and any number of `Contract` objects. Each `Contract` object has at most one `Implementation` object. As such, the number of `Award` objects can exceed the number of unique OCIDs, but the number of `Tender` objects can't."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "DtRec1tNnPz6"
      },
      "source": [
        "Plot a count of objects per stage:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Oe8W_HLCKUsO"
      },
      "outputs": [],
      "source": [
        "%%sql objects_per_stage <<\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 (\n",
        "    SELECT *\n",
        "    FROM\n",
        "        field_counts\n",
        "    WHERE\n",
        "        collection_id IN :collection_ids\n",
        "        AND release_type = 'compiled_release'\n",
        "        AND path IN (\n",
        "            'planning',\n",
        "            'tender',\n",
        "            'awards',\n",
        "            'contracts',\n",
        "            'contracts/implementation'\n",
        "        )\n",
        ") AS field_counts USING (path)\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "mKo6Q4HimvQZ"
      },
      "outputs": [],
      "source": [
        "plot_objects_per_stage(objects_per_stage)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "9Ui1BMMBFgGu"
      },
      "source": [
        "### Date ranges"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ybx8YrW7hRWC"
      },
      "source": [
        "\n",
        "Use this section to check that the data covers the expected date range."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "V5RSYoTSHRZE"
      },
      "source": [
        "Calculate the earliest and latest `date`, `awards/date` and `contracts/dateSigned`:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "qy0VRQ3IDwsG"
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "SELECT\n",
        "    collection_id,\n",
        "    release_type,\n",
        "    'release_date' AS date_type,\n",
        "    min(date) AS min,\n",
        "    max(date) AS max\n",
        "FROM\n",
        "    release_summary\n",
        "GROUP BY\n",
        "    collection_id,\n",
        "    release_type,\n",
        "    date_type\n",
        "UNION ALL\n",
        "SELECT\n",
        "    collection_id,\n",
        "    release_type,\n",
        "    'award_date' AS date_type,\n",
        "    min(first_award_date) AS min,\n",
        "    max(last_award_date) AS max\n",
        "FROM\n",
        "    release_summary\n",
        "GROUP BY\n",
        "    collection_id,\n",
        "    release_type,\n",
        "    date_type\n",
        "UNION ALL\n",
        "SELECT\n",
        "    collection_id,\n",
        "    release_type,\n",
        "    'contract_datesigned' AS date_type,\n",
        "    min(first_contract_datesigned) AS min,\n",
        "    max(last_contract_datesigned) AS max\n",
        "FROM\n",
        "    release_summary\n",
        "GROUP BY\n",
        "    collection_id,\n",
        "    release_type\n",
        "ORDER BY\n",
        "    collection_id,\n",
        "    release_type,\n",
        "    date_type;\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "daHiVoJkexWi"
      },
      "source": [
        "### Release date distribution\n",
        "\n",
        "Use this section to check that releases are distributed as expected."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "DynLHB_12cZ3"
      },
      "source": [
        "Plot the count of releases per month:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "0xTqCh28LOQj"
      },
      "outputs": [],
      "source": [
        "%%sql release_dates <<\n",
        "SELECT\n",
        "    collection_id::text,\n",
        "    release_type,\n",
        "    date,\n",
        "    count(*) AS release_count\n",
        "FROM\n",
        "    release_summary AS rs\n",
        "WHERE\n",
        "    collection_id IN :collection_ids\n",
        "GROUP BY\n",
        "    collection_id,\n",
        "    release_type,\n",
        "    date\n",
        "ORDER BY\n",
        "    date ASC;\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "OM2aiiJzAmzM"
      },
      "outputs": [],
      "source": [
        "# Resample by month\n",
        "release_dates[\"date\"] = release_dates[\"date\"].dt.strftime(\"%Y-%m\")\n",
        "release_dates = (\n",
        "    release_dates.groupby([\"collection_id\", \"release_type\", \"date\"]).agg({\"release_count\": \"sum\"}).reset_index()\n",
        ")\n",
        "\n",
        "plot_releases_by_month(release_dates)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ugoDOjsdkLXN"
      },
      "source": [
        "### Extensions"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "9cxNPLXN8wwc"
      },
      "source": [
        "Use this section to check which extensions the data uses."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "U7iYgIdW8z1c"
      },
      "source": [
        "List the extensions declared in the package metadata:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "qFbWPY5Eq7fv"
      },
      "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": "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"
      ]
    }
  ],
  "metadata": {
    "colab": {
      "name": "template_structure_and_format_feedback",
      "provenance": [],
      "toc_visible": true
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}