structure_and_format_feedback_template.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": "2MRnhij4pmZO"
   },
   "source": [
    "Set name of spreadsheet to export results to:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "drokzszlphmK"
   },
   "outputs": [],
   "source": [
    "spreadsheet_name = 'feedback_results'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "F8fwq_F6XMLs"
   },
   "source": [
    "Enter credentials:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "27yRx-oaXPtd"
   },
   "outputs": [],
   "source": [
    "import getpass\n",
    "\n",
    "print('Enter your Kingfisher credentials')\n",
    "user = input('Username:')\n",
    "password = getpass.getpass('Password:')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "DZASSenGpprv"
   },
   "source": [
    "Setup notebook environment:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "--8vgOiP_58f"
   },
   "outputs": [],
   "source": [
    "# Install Kingfisher Colab and required packages\n",
    "%shell pip install --upgrade 'ocdskingfishercolab<0.4' psycopg2-binary > pip.log\n",
    "\n",
    "# Import libraries and functions\n",
    "from google.colab.data_table import DataTable\n",
    "from ocdskingfishercolab import (\n",
    "    list_source_ids,\n",
    "    list_collections,\n",
    "    render_json,\n",
    "    set_spreadsheet_name,\n",
    "    save_dataframe_to_sheet,\n",
    "    save_dataframe_to_spreadsheet,\n",
    "    set_search_path)\n",
    "import pandas as pd\n",
    "\n",
    "# Load https://pypi.org/project/ipython-sql/\n",
    "%load_ext sql \n",
    "\n",
    "# Load https://colab.research.google.com/notebooks/data_table.ipynb\n",
    "%load_ext google.colab.data_table\n",
    "DataTable.max_columns = 50 # Increase max columns so that dataframes with many columns are rendered as data tables\n",
    "DataTable.include_index = False # Remove the index from data tables for easier copy-pasting to Google Docs\n",
    "\n",
    "# Set config\n",
    "%config SqlMagic.autopandas = True  # Return Pandas DataFrames instead of regular result sets\n",
    "%config SqlMagic.displaycon = False  # Don't show connection string after execute\n",
    "%config SqlMagic.feedback = False  # Don't print number of rows affected by DML\n",
    "set_spreadsheet_name(spreadsheet_name) # Set name of spreadsheet for exporting results\n",
    "\n",
    "# Connect to database\n",
    "connection_string = 'postgresql://' + user + ':' + password + '@postgres-readonly.kingfisher.open-contracting.org/ocdskingfisherprocess?sslmode=require'\n",
    "%sql $connection_string\n",
    "  \n",
    "# Install and setup plotting library\n",
    "# Maybe this can also be moved to Kingfisher-Colab?\n",
    "!pip install seaborn\n",
    "\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "import matplotlib.ticker as tkr\n",
    "\n",
    "colab_dark_style = {'figure.facecolor': '#383838',\n",
    "                       'axes.edgecolor': '#d5d5d5',\n",
    "                       'axes.facecolor': '#383838',\n",
    "                       'axes.labelcolor': '#d5d5d5',\n",
    "                       'text.color': '#d5d5d5',\n",
    "                       'xtick.color': '#d5d5d5',\n",
    "                       'ytick.color': '#d5d5d5'}\n",
    "\n",
    "sns.set_style('dark', colab_dark_style)\n",
    "\n",
    "# Define function to apply number formatting to axis labels\n",
    "# Maybe this can also be moved to Kingfisher-Colab?\n",
    "# Needs updating to support other locales\n",
    "def format_thousands(axis):\n",
    "  axis.set_major_formatter(tkr.FuncFormatter(lambda x, pos: '{:,.0f}'.format(x)))"
   ]
  },
  {
   "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",
    "\n",
    "SELECT\n",
    "    summaries.selected_collections.*,\n",
    "    source_id\n",
    "FROM\n",
    "    summaries.selected_collections\n",
    "    JOIN collection 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's [add command](https://kingfisher-summarize.readthedocs.io/en/latest/cli.html#add)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "pP9KsYyT1Xnd"
   },
   "source": [
    "## Check for data collection and processing errors"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "KGevotqJ_sN1"
   },
   "source": [
    "### Collection notes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "0-ufvqTg_yP9"
   },
   "source": [
    "Generate a list of notes for each collection from the `collection_note` table. \n",
    "Users can add notes when starting a spider or via the cli. Transforms can also add notes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "U0x8mo8Yyj39"
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT\n",
    "    collection_id,\n",
    "    note\n",
    "FROM\n",
    "    collection_note\n",
    "WHERE\n",
    "    collection_id IN :collection_ids\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "Q-YliQK-_5PP"
   },
   "source": [
    "### Collection file errors"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "fY0i9io4_90a"
   },
   "source": [
    "Generate a summary of errors and warnings from the `collection_file` table.\n",
    "Kingfisher Collect and the `local-load` command report errors when they cannot retrieve a file.\n",
    "Kingfisher Process reports warnings when it needs to modify the contents of a file in order to store it.\n",
    "Presently, the only warning is about the removal of control characters."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "E5CgVL562bbd"
   },
   "outputs": [],
   "source": [
    "%%sql collection_file_error_summary <<\n",
    "\n",
    "SELECT\n",
    "    collection_id,\n",
    "    warnings,\n",
    "    errors,\n",
    "    count(*),\n",
    "    (array_agg(filename ORDER BY random()))[1:3] AS example_filenames,\n",
    "    (array_agg(url ORDER BY random()))[1:3] AS example_urls\n",
    "FROM\n",
    "    collection_file\n",
    "WHERE\n",
    "    collection_id IN :collection_ids\n",
    "    AND (errors IS NOT NULL\n",
    "        OR warnings IS NOT NULL)\n",
    "GROUP BY\n",
    "    1,\n",
    "    2,\n",
    "    3\n",
    "ORDER BY\n",
    "    4 DESC;\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "CY2izeiS_JGi"
   },
   "outputs": [],
   "source": [
    "collection_file_error_summary"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "2mXcFyssACKR"
   },
   "source": [
    "Generate a full list of errors and warnings from the `collection_file` table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "pHsH4fqP0rnY"
   },
   "outputs": [],
   "source": [
    "%%sql collection_file_errors <<\n",
    "\n",
    "SELECT\n",
    "    collection_id,\n",
    "    filename,\n",
    "    warnings,\n",
    "    url,\n",
    "    errors\n",
    "FROM\n",
    "    collection_file\n",
    "WHERE\n",
    "    collection_id IN :collection_ids\n",
    "    AND (errors IS NOT NULL\n",
    "        OR warnings IS NOT NULL);\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "_THCeewgwE8K"
   },
   "outputs": [],
   "source": [
    "collection_file_errors"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "MRDaacPxAIOh"
   },
   "source": [
    "### Collection file item errors"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "lBboFieoAKyY"
   },
   "source": [
    "Generate a summary of errors and warnings from the `collection_file_item` table.\n",
    "Kingfisher Process reports errors when it cannot load a file item."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "vZ77JoaW3-bV"
   },
   "outputs": [],
   "source": [
    "%%sql collection_file_item_error_summary <<\n",
    "\n",
    "SELECT\n",
    "    collection_id,\n",
    "    cfi.warnings,\n",
    "    cfi.errors,\n",
    "    count(*)\n",
    "FROM\n",
    "    collection_file_item AS cfi\n",
    "    JOIN collection_file AS cf ON cfi.collection_file_id = cf.id\n",
    "WHERE\n",
    "    cf.collection_id IN :collection_ids\n",
    "    AND (cfi.errors IS NOT NULL\n",
    "        OR cfi.warnings IS NOT NULL)\n",
    "GROUP BY\n",
    "    1,\n",
    "    2,\n",
    "    3\n",
    "ORDER BY\n",
    "    4 DESC;\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "2N8uzloY_O8x"
   },
   "outputs": [],
   "source": [
    "collection_file_item_error_summary"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "e6h9arTAAQDX"
   },
   "source": [
    "Generate a full list of errors and warnings from the `collection_file_item` table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "TCD9v-mW08xL"
   },
   "outputs": [],
   "source": [
    "%%sql collection_file_item_errors <<\n",
    "\n",
    "SELECT\n",
    "    cfi.number,\n",
    "    cfi.warnings,\n",
    "    cfi.errors\n",
    "FROM\n",
    "    collection_file_item AS cfi\n",
    "    JOIN collection_file AS cf ON cfi.collection_file_id = cf.id\n",
    "WHERE\n",
    "    cf.collection_id IN :collection_ids\n",
    "    AND (cfi.errors IS NOT NULL\n",
    "        OR cfi.warnings IS NOT NULL);\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "AwcWIslQ_Tmq"
   },
   "outputs": [],
   "source": [
    "collection_file_item_errors"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "jYavV9OEAThs"
   },
   "source": [
    "### Check errors"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "ZNTCykfZAVnr"
   },
   "source": [
    "A summary of errors from the `release_check_error` and `record_check_error` tables.\n",
    "CoVE reports errors when it cannot check a release or record."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "wXHs3ZIXZF4G"
   },
   "outputs": [],
   "source": [
    "%%sql check_error_summary <<\n",
    "\n",
    "WITH errors AS (\n",
    "    SELECT\n",
    "        collection_id,\n",
    "        'release' AS TYPE,\n",
    "        release.id AS release_id,\n",
    "        release_check_error.error\n",
    "    FROM\n",
    "        release_check_error\n",
    "        JOIN RELEASE ON release_check_error.release_id = release.id\n",
    "    WHERE\n",
    "        release.collection_id IN :collection_ids\n",
    "    UNION\n",
    "    SELECT\n",
    "        collection_id,\n",
    "        'record' AS TYPE,\n",
    "        record.id AS record_id,\n",
    "        record_check_error.error\n",
    "    FROM\n",
    "        record_check_error\n",
    "        JOIN record ON record_check_error.record_id = record.id\n",
    "    WHERE\n",
    "        record.collection_id IN :collection_ids\n",
    ")\n",
    "SELECT\n",
    "    collection_id,\n",
    "    TYPE,\n",
    "    error,\n",
    "    count(*),\n",
    "    (array_agg(release_id ORDER BY random()))[1:3] AS example_release_ids\n",
    "FROM\n",
    "    errors\n",
    "GROUP BY\n",
    "    1,\n",
    "    2,\n",
    "    3\n",
    "ORDER BY\n",
    "    4 DESC;\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "F3RrH_3t_egR"
   },
   "outputs": [],
   "source": [
    "check_error_summary"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "xY94PIGGAXpi"
   },
   "source": [
    "Generate a full list of errors from the `release_check_error` and `record_check_error` tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "7AtixVvm3Nx-"
   },
   "outputs": [],
   "source": [
    "%%sql check_errors <<\n",
    "\n",
    "SELECT\n",
    "    collection_id,\n",
    "    'release' AS TYPE,\n",
    "    release.id AS release_id,\n",
    "    release_check_error.error\n",
    "FROM\n",
    "    release_check_error\n",
    "    JOIN RELEASE ON release_check_error.release_id = release.id\n",
    "WHERE\n",
    "    release.collection_id IN :collection_ids\n",
    "UNION\n",
    "SELECT\n",
    "    collection_id,\n",
    "    'record' AS TYPE,\n",
    "    record.id AS record_id,\n",
    "    record_check_error.error\n",
    "FROM\n",
    "    record_check_error\n",
    "    JOIN record ON record_check_error.record_id = record.id\n",
    "WHERE\n",
    "    record.collection_id IN :collection_ids;\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "4UOCStcb_k5O"
   },
   "outputs": [],
   "source": [
    "check_errors"
   ]
  },
  {
   "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"
   ]
  },
  {
   "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 generates 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."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "VXxxMiKcBOCf"
   },
   "source": [
    "Generate a count of releases, records and compiled releases for each collection."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "MVJ6sIWeAZzD"
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\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 the contracting process an OCDS release relates to."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "EqsaSZj6DkuS"
   },
   "source": [
    "Generate a summary of releases by `tag`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "WwgeZMX5Ame4"
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\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",
    "\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": [
    "Generate and plot a count of objects per stage:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "Oe8W_HLCKUsO"
   },
   "outputs": [],
   "source": [
    "%%sql objects_per_stage << \n",
    "\n",
    "SELECT\n",
    "    CASE WHEN paths.path = 'contracts/implementation' THEN\n",
    "        'implementation'\n",
    "    ELSE\n",
    "        paths.path\n",
    "    END AS stage,\n",
    "    CASE WHEN paths.path IN ('planning',\n",
    "        'tender',\n",
    "        'contracts/implementation') THEN\n",
    "        GREATEST (object_property,\n",
    "            0)\n",
    "    ELSE\n",
    "        GREATEST (array_count,\n",
    "            0)\n",
    "    END AS object_count\n",
    "FROM (\n",
    "    SELECT\n",
    "        unnest(ARRAY['planning', 'tender', 'awards', 'contracts', 'contracts/implementation']) AS path) AS paths\n",
    "    LEFT JOIN (\n",
    "        SELECT\n",
    "            *\n",
    "        FROM\n",
    "            field_counts\n",
    "        WHERE\n",
    "            collection_id IN :collection_ids\n",
    "            AND release_type = 'compiled_release'\n",
    "            AND path IN ('planning', 'tender', 'awards', 'contracts', 'contracts/implementation')) AS field_counts USING (path)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "mKo6Q4HimvQZ"
   },
   "outputs": [],
   "source": [
    "objects_per_stage_chart = sns.catplot(x=\"stage\", y=\"object_count\", kind=\"bar\", data=objects_per_stage).set_xticklabels(rotation=90)\n",
    "\n",
    "for ax in objects_per_stage_chart.axes.flat:\n",
    "  format_thousands(ax.yaxis)\n",
    "\n",
    "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": [
    "Generate a summary of the earliest and latest `date`, `awards/date` and `contracts/dateSigned`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "qy0VRQ3IDwsG"
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\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",
    "\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",
    "\n",
    "SELECT\n",
    "    collection_id::text,\n",
    "    release_type,\n",
    "    date,\n",
    "    count(*) AS release_count\n",
    "FROM\n",
    "    release_summary 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",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "OM2aiiJzAmzM"
   },
   "outputs": [],
   "source": [
    "# Resample by month\n",
    "release_dates = release_dates.set_index('date')\n",
    "release_dates = release_dates.groupby(['collection_id', 'release_type']).resample(\"M\").sum()\n",
    "\n",
    "fig, ax = plt.subplots(figsize = [15,5])\n",
    "sns.lineplot(data = release_dates, x='date', y='release_count', hue = 'collection_id', style = 'release_type')\n",
    "\n",
    "format_thousands(ax.yaxis)\n",
    "sns.despine()"
   ]
  },
  {
   "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": [
    "Generate a list of extensions declared in the package metadata:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "qFbWPY5Eq7fv"
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT\n",
    "    collection_id,\n",
    "    release_type,\n",
    "    jsonb_array_elements(package_data -> 'extensions') AS 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",
    "    EXTENSION\n",
    "ORDER BY\n",
    "    collection_id,\n",
    "    release_type,\n",
    "    count DESC;\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "cE548sDyZyG5"
   },
   "source": [
    "## Check for structure and format errors"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "gtzHL_Sul8i3"
   },
   "source": [
    "Kingfisher Process checks data against the OCDS schema using [CoVE](https://github.com/OpenDataServices/cove). 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": [
    "By default, Kingfisher Process checks all data, therefore there is often a long queue of collections to be checked. Use the following query to confirm that checks are complete for your collection(s).\n",
    "\n",
    "If checks for your collection(s) have not started yet, you can use the [`check collection` command](https://kingfisher-process.readthedocs.io/en/latest/cli/check-collection.html) to start the checks manually.\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",
    "\n",
    "SELECT\n",
    "    collection_id,\n",
    "    'release' AS collection_type,\n",
    "    CASE WHEN count(release.id) = count(release_check.id) THEN\n",
    "        'complete'\n",
    "    WHEN count(release_check.id) = 0 THEN\n",
    "        'not_started'\n",
    "    ELSE\n",
    "        'in_progress'\n",
    "    END AS check_status,\n",
    "    count(release_check.id)::text || '/' || 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 WHEN count(record.id) = count(record_check.id) THEN\n",
    "        'complete'\n",
    "    WHEN count(record_check.id) = 0 THEN\n",
    "        'not_started'\n",
    "    ELSE\n",
    "        'in_progress'\n",
    "    END AS check_status,\n",
    "    count(record_check.id)::text || '/' || 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",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "uTeVs0jnlgHu"
   },
   "source": [
    "### Error summary"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "XGxn_SwCllq4"
   },
   "source": [
    "Generate a summary of 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",
    "\n",
    "WITH errors AS (\n",
    "    SELECT\n",
    "        collection_id,\n",
    "        errors ->> 'type' AS error_type,\n",
    "    LEFT (errors ->> 'description',\n",
    "        49000) AS error,\n",
    "    ocid,\n",
    "    errors ->> 'value' AS value,\n",
    "    row_number() OVER (PARTITION BY collection_id,\n",
    "        errors ->> 'type',\n",
    "    LEFT (errors ->> 'description',\n",
    "    49000)) AS rownum\n",
    "FROM\n",
    "    release_check rc\n",
    "    CROSS JOIN jsonb_array_elements(cove_output -> 'validation_errors') AS errors\n",
    "    JOIN RELEASE 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 (errors ->> 'description',\n",
    "        49000) AS error,\n",
    "    ocid,\n",
    "    errors ->> 'value' AS value,\n",
    "    row_number() OVER (PARTITION BY collection_id,\n",
    "        errors ->> 'type',\n",
    "    LEFT (errors ->> 'description',\n",
    "    49000)) AS rownum\n",
    "FROM\n",
    "    record_check rc\n",
    "    CROSS JOIN jsonb_array_elements(cove_output -> 'validation_errors') AS errors\n",
    "    JOIN record r ON rc.record_id = r.id\n",
    "WHERE\n",
    "    collection_id IN :collection_ids\n",
    "),\n",
    "examples AS (\n",
    "    SELECT\n",
    "        collection_id,\n",
    "        error_type,\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",
    "        error\n",
    ")\n",
    "SELECT\n",
    "    collection_id,\n",
    "    error_type,\n",
    "    error,\n",
    "    count(*) AS count,\n",
    "    example_ocids,\n",
    "    example_values\n",
    "FROM\n",
    "    errors\n",
    "    JOIN examples USING (collection_id, error_type, error)\n",
    "GROUP BY\n",
    "    collection_id,\n",
    "    error_type,\n",
    "    error,\n",
    "    example_ocids,\n",
    "    example_values;\n",
    "\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": [
    "Generate a full list of 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",
    "\n",
    "SELECT\n",
    "    collection_id,\n",
    "    'release' AS collection_type,\n",
    "    errors ->> 'type' AS error_type,\n",
    "    errors ->> 'field' AS field,\n",
    "    LEFT (errors ->> 'description',\n",
    "        49000) AS error,\n",
    "    ocid,\n",
    "    errors ->> 'value' AS value\n",
    "FROM\n",
    "    release_check rc\n",
    "    CROSS JOIN jsonb_array_elements(cove_output -> 'validation_errors') AS errors\n",
    "    JOIN RELEASE 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 (errors ->> 'description',\n",
    "        49000) AS error,\n",
    "    ocid,\n",
    "    errors ->> 'value' AS value\n",
    "FROM\n",
    "    record_check rc\n",
    "    CROSS JOIN jsonb_array_elements(cove_output -> 'validation_errors') AS errors\n",
    "    JOIN record 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": {
   "authorship_tag": "ABX9TyPpPT0FsHcqUr/d/JNLgAmy",
   "name": "setup_notebook",
   "provenance": []
  },
  "kernelspec": {
   "display_name": "Python 3",
   "name": "python3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}