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