OC4IDS_Data_Feedback_Notebook.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "-Ygw2Mk3Maol"
      },
      "source": [
        "# OC4IDS Data Feedback Notebook\n",
        "\n",
        "Use this notebook to provide feedback on OC4IDS data.\n",
        "\n",
        "Before running this notebook you first need to [import your data](https://colab.research.google.com/github/open-contracting/oc4ids_database/blob/main/OC4IDS_Database_Data_Import.ipynb)."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "oy2zRxSkUjro"
      },
      "source": [
        "## Setup"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "1CphynSNKxqb"
      },
      "source": [
        "Enter credentials:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "27yRx-oaXPtd"
      },
      "outputs": [],
      "source": [
        "# Get database credentials. OCDS Helpdesk analysts, see https://crm.open-contracting.org/issues/6335.\n",
        "import getpass\n",
        "\n",
        "print('Enter your credentials')\n",
        "user = input('Username:')\n",
        "password = getpass.getpass('Password:')"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "OnGZ5D-zK9e6"
      },
      "source": [
        "Setup notebook environment:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "VjuPJaBUMapU"
      },
      "outputs": [],
      "source": [
        "!pip install --upgrade ipython-sql > pip.log\n",
        "\n",
        "connection_string = 'postgresql://' + user + ':' + password + '@oc4ids-database-2.cuujgua4wses.us-east-1.rds.amazonaws.com/postgres'\n",
        "\n",
        "# https://pypi.org/project/ipython-sql/\n",
        "%load_ext sql\n",
        "%sql $connection_string\n",
        "%config SqlMagic.autopandas = True  # Return Pandas DataFrames instead of regular result sets\n",
        "%config SqlMagic.displaycon = False  # Don't show connection string after execute\n",
        "%config SqlMagic.feedback = False  # Don't print number of rows affected by DML\n",
        "\n",
        "# https://colab.research.google.com/notebooks/data_table.ipynb\n",
        "%load_ext google.colab.data_table\n",
        "from google.colab.data_table import DataTable\n",
        "DataTable.max_columns = 50 # Increase max columns so that dataframes with many columns are rendered as data tables\n",
        "DataTable.include_index = False # Remove the index from data tables for easier copy-pasting to Google Docs\n",
        "\n",
        "# Import some useful analysis libraries\n",
        "import pandas as pd\n",
        "from scipy import stats\n",
        "import numpy as np\n",
        "\n",
        "# Define function for rendering collapsible JSON in a notebook\n",
        "# Remove once https://github.com/open-contracting/kingfisher-colab/issues/40 is done\n",
        "import json\n",
        "from IPython.display import HTML\n",
        "\n",
        "def render_json(jstr):\n",
        "  if type(jstr) != str:\n",
        "    jstr = json.dumps(jstr)\n",
        "  return HTML(\"\"\"\n",
        "<script src=\"https://rawgit.com/caldwell/renderjson/master/renderjson.js\"></script>\n",
        "<script>\n",
        "renderjson.set_show_to_level(1)\n",
        "document.body.appendChild(renderjson(%s))\n",
        "new ResizeObserver(google.colab.output.resizeIframeToContent).observe(document.body)\n",
        "</script>\n",
        "\"\"\" % jstr)\n",
        "\n",
        "# Install and setup plotting library\n",
        "!pip install seaborn >> pip.log\n",
        "\n",
        "import seaborn as sns\n",
        "import matplotlib.pyplot as plt\n",
        "import matplotlib.ticker as tkr\n",
        "\n",
        "colab_dark_style = {\n",
        "  'figure.facecolor': '#383838',\n",
        "  'axes.edgecolor': '#d5d5d5',\n",
        "  'axes.facecolor': '#383838',\n",
        "  'axes.labelcolor': '#d5d5d5',\n",
        "  'text.color': '#d5d5d5',\n",
        "  'xtick.bottom': True,\n",
        "  'xtick.color': '#d5d5d5',\n",
        "  'ytick.bottom': True,\n",
        "  'ytick.color': '#d5d5d5',\n",
        "}\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": "emzPHH-lMaqG"
      },
      "source": [
        "## Analysis"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "kp_b-P2X4oM8"
      },
      "source": [
        "### Choose a collection to query"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "1lk0i6tEMaqH"
      },
      "source": [
        "Get the list of collections:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "vp4FUPxaYP5T"
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "\n",
        "select\n",
        "  collection.id as collection_id,\n",
        "  source_id,\n",
        "  data_version,\n",
        "  count(*) as project_count\n",
        "from\n",
        "  collection\n",
        "join\n",
        "  projects on collection.id = projects.collection_id\n",
        "group by\n",
        "  collection.id,\n",
        "  source_id,\n",
        "  data_version\n",
        " order by\n",
        "  collection.id desc;"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "RdGm2QfIW2Dy"
      },
      "source": [
        "Set the `collection_id` to query:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "uVLcOKqHMaqH"
      },
      "outputs": [],
      "source": [
        "collection_id = 30"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "eI8B5YytMaqL"
      },
      "source": [
        "### Scope"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "FigbfCGbMaqM"
      },
      "source": [
        "Count projects:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "zsr_R5ljMaqM"
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "\n",
        "select\n",
        "    count(*)\n",
        "from\n",
        "    projects\n",
        "where\n",
        "    collection_id = :collection_id"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "pLwrf7U1MaqO"
      },
      "source": [
        "Count contracting processes:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "qN2McEj0MaqP"
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "\n",
        "select\n",
        "    count(*)\n",
        "from\n",
        "    projects\n",
        "cross join\n",
        "    jsonb_array_elements(data -> 'contractingProcesses')\n",
        "where\n",
        "    collection_id = :collection_id;"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "A2rFDMv5Maqc"
      },
      "source": [
        "Get the earliest and latest updated dates:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "8l7Bs8qpMaqe"
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "\n",
        "select\n",
        "  min(data ->> 'updated') as min_date,\n",
        "  max(data ->> 'updated') as maxb_date\n",
        "from\n",
        "  projects\n",
        "where\n",
        "  collection_id = :collection_id"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "6QBMlzymMaqS"
      },
      "source": [
        "### Structure and format"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "y0q6F_I8oXPC"
      },
      "source": [
        "Get a list of validation errors reported by CoVE:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "AF3DK0svoWKw"
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "\n",
        "select\n",
        "  replace(trim('\"' from (validation_errors -> 0)::text), '\\', '')::jsonb -> 'message' as error,\n",
        "  regexp_replace(jsonb_array_elements(validation_errors -> 1) ->> 'path', '/\\d+', '', 'g') as path,\n",
        "  count(*)\n",
        "from\n",
        "  collection_check\n",
        "cross join\n",
        "  jsonb_array_elements(cove_output -> 'validation_errors') as validation_errors\n",
        "where\n",
        "  collection_id = :collection_id\n",
        "group by\n",
        "  error,\n",
        "  path\n",
        "order by\n",
        "  error,\n",
        "  count desc;"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Fzeqa6CBMaqT"
      },
      "source": [
        "Get a list of structure warnings reported by CoVE:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "-_p6_WA8MaqT"
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "\n",
        "with warnings_list as (\n",
        "    select\n",
        "        key as warning_type,\n",
        "        trim('\"' from paths::text) as paths\n",
        "    from\n",
        "        collection_check\n",
        "    cross join\n",
        "        jsonb_each(cove_output -> 'structure_warnings')\n",
        "    cross join\n",
        "        jsonb_array_elements(value) as paths\n",
        "    where\n",
        "      collection_id = :collection_id\n",
        ")\n",
        "select\n",
        "    warning_type,\n",
        "    regexp_replace(paths, '/\\d+', '', 'g') as path,\n",
        "    count(*) as count\n",
        "from\n",
        "    warnings_list\n",
        "group by\n",
        "    warning_type,\n",
        "    path\n",
        "order by\n",
        "    count desc"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "WtdUUiiiMaqX"
      },
      "source": [
        "### Conformance"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Us11kt9cMaqX"
      },
      "source": [
        "#### Project identifier prefix"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "DpRuDzrYMaqY"
      },
      "source": [
        "Get the project prefixes used in the data:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "MdVCNKk0MaqY"
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "\n",
        "select distinct\n",
        "  substring(project_id from 1 for 13) as prefix\n",
        "from\n",
        "    projects\n",
        "where\n",
        "  collection_id = :collection_id\n",
        "    "
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "BGxRHIqI5DLN"
      },
      "source": [
        "Check that the data uses a [registered prefix](https://standard.open-contracting.org/infrastructure/latest/en/guidance/identifiers/#existing-prefixes)."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "RBgIdb4Qvu0k"
      },
      "source": [
        "#### Duplicate project identifiers"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "7QEb8ZixXGFm"
      },
      "source": [
        "Get a list of project ids which appear more than once:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Qb3si_3g7lnd"
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "\n",
        "select\n",
        "  project_id,\n",
        "  count(*)\n",
        "from\n",
        "  projects\n",
        "where\n",
        "  collection_id = :collection_id\n",
        "group by\n",
        "  project_id\n",
        "having\n",
        "  count(*) > 1\n",
        "order by\n",
        "  count(*) desc;"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "fGihA30uMaqi"
      },
      "source": [
        "#### Additional fields"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "vlJqd3WYMaqi"
      },
      "source": [
        "Get a list of additional fields reported by CoVE:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "3S2AF1ZZMaqi"
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "\n",
        "select\n",
        "    key as path,\n",
        "    value -> 'count' as count,\n",
        "    value -> 'examples' as examples,\n",
        "    value -> 'additional_field_descendance' as descendents\n",
        "from\n",
        "    collection_check\n",
        "cross join\n",
        "    jsonb_each(cove_output -> 'additional_fields')\n",
        "where\n",
        "  collection_id = :collection_id\n",
        "    "
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "_IMp58mdMaqk"
      },
      "source": [
        "#### Organization references"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ORDKDyoWYiJN"
      },
      "source": [
        "For each organization reference in the schema:\n",
        "\n",
        "* Count how many organization references have an `.id` that does not match the `.id` of any parties.\n",
        "* Get the project `id` of the first 3 projects which have an organization reference with an `.id` that does not match the `.id` of any parties."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "RBjaXvoDMaqm"
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "\n",
        "WITH party_ids AS (\n",
        "  SELECT\n",
        "    project_id,\n",
        "    parties ->> 'id' AS id\n",
        "  FROM\n",
        "    projects\n",
        "    CROSS JOIN jsonb_array_elements(data -> 'parties') AS parties\n",
        "  WHERE\n",
        "    collection_id = :collection_id\n",
        "),\n",
        "organization_references AS (\n",
        "  SELECT\n",
        "    'publicAuthority' AS path,\n",
        "    projects.project_id AS project_id,\n",
        "    data -> 'publicAuthority' ->> 'name' AS name,\n",
        "    data -> 'publicAuthority' ->> 'id' AS id\n",
        "  FROM\n",
        "    projects\n",
        "  WHERE\n",
        "    collection_id = :collection_id\n",
        "  UNION ALL\n",
        "  SELECT\n",
        "    'budget/budgetBreakdown/sourceParty' AS path,\n",
        "    projects.project_id AS project_id,\n",
        "    budgetBreakdown -> 'sourceParty' ->> 'name' AS name,\n",
        "    budgetBreakdown -> 'sourceParty' ->> 'id' AS id\n",
        "  FROM\n",
        "    projects\n",
        "    CROSS JOIN jsonb_array_elements(data -> 'budget' -> 'budgetBreakdown') AS budgetBreakdown\n",
        "  WHERE\n",
        "    collection_id = :collection_id\n",
        "  UNION ALL\n",
        "  SELECT\n",
        "    'contractingProcesses/summary/tender/tenderers' AS path,\n",
        "    projects.project_id AS project_id,\n",
        "    tenderers ->> 'name' AS name,\n",
        "    tenderers ->> 'id' AS id\n",
        "  FROM\n",
        "    projects\n",
        "    CROSS JOIN jsonb_array_elements(data -> 'contractingProcesses') AS contractingProcesses\n",
        "    CROSS JOIN jsonb_array_elements(contractingProcesses -> 'summary' -> 'tender' -> 'tenderers') AS tenderers\n",
        "  WHERE\n",
        "    collection_id = :collection_id\n",
        "  UNION ALL\n",
        "  SELECT\n",
        "    'contractingProcesses/summary/tender/procuringEntity' AS path,\n",
        "    projects.project_id AS project_id,\n",
        "    contractingProcesses -> 'summary' -> 'tender' -> 'procuringEntity' ->> 'name' AS name,\n",
        "    contractingProcesses -> 'summary' -> 'tender' -> 'procuringEntity' ->> 'id' AS id\n",
        "  FROM\n",
        "    projects\n",
        "    CROSS JOIN jsonb_array_elements(data -> 'contractingProcesses') AS contractingProcesses\n",
        "  WHERE\n",
        "    collection_id = :collection_id\n",
        "  UNION ALL\n",
        "  SELECT\n",
        "    'contractingProcesses/summary/tender/administrativeEntity' AS path,\n",
        "    projects.project_id AS project_id,\n",
        "    contractingProcesses -> 'summary' -> 'tender' -> 'administrativeEntity' ->> 'name' AS name,\n",
        "    contractingProcesses -> 'summary' -> 'tender' -> 'administrativeEntity' ->> 'id' AS id\n",
        "  FROM\n",
        "    projects\n",
        "    CROSS JOIN jsonb_array_elements(data -> 'contractingProcesses') AS contractingProcesses\n",
        "  WHERE\n",
        "    collection_id = :collection_id\n",
        "  UNION ALL\n",
        "  SELECT\n",
        "    'contractingProcesses/summary/suppliers' AS path,\n",
        "    projects.project_id AS project_id,\n",
        "    suppliers ->> 'name' AS name,\n",
        "    suppliers ->> 'id' AS id\n",
        "  FROM\n",
        "    projects\n",
        "    CROSS JOIN jsonb_array_elements(data -> 'contractingProcesses') AS contractingProcesses\n",
        "    CROSS JOIN jsonb_array_elements(contractingProcesses -> 'summary' -> 'suppliers') AS suppliers\n",
        "   WHERE\n",
        "    collection_id = :collection_id\n",
        ")\n",
        "SELECT\n",
        "  path as \"Organization reference path\",\n",
        "  count(*) as \"Errors\",\n",
        "  (array_agg(project_id))[1:3] as \"Example project IDs\"\n",
        "FROM\n",
        "  organization_references\n",
        "  LEFT JOIN party_ids USING (id, project_id)\n",
        "WHERE\n",
        "  organization_references.id IS NOT NULL\n",
        "  AND party_ids.id IS NULL\n",
        "GROUP BY\n",
        "  path\n",
        "ORDER BY\n",
        "  count(*) DESC;"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "sA1bn2H2cPYD"
      },
      "source": [
        "#### Organization identifier schemes"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "pXXs6sikc0eu"
      },
      "source": [
        "**`parties/identifier`**"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "7STHFHyci4TY"
      },
      "source": [
        "Get a list of organization identifier schemes used in `parties/identifier`:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "_F2xnBHpcR80"
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "\n",
        "select\n",
        "  parties -> 'identifier' -> 'scheme' as scheme,\n",
        "  count(*)\n",
        "from\n",
        "  projects\n",
        "cross join\n",
        "  jsonb_array_elements(data -> 'parties') as parties\n",
        "where\n",
        "  collection_id = :collection_id\n",
        "group by\n",
        "  scheme;"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "n48kvLIGi_M_"
      },
      "source": [
        "Check that the schemes appear in [org-id.guide](http://org-id.guide/)."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "W9ZBAaobc5CA"
      },
      "source": [
        "**`parties/additionalIdentifiers`**"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "7unGliajjRc9"
      },
      "source": [
        "Get a list of organization identifier schemes used in `parties/additionalIdentifiers`:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "6lN3g4mrc8zU"
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "\n",
        "select\n",
        "  additionalIdentifiers -> 'scheme' as scheme,\n",
        "  count(*)\n",
        "from\n",
        "  projects\n",
        "cross join\n",
        "  jsonb_array_elements(data -> 'parties') as parties\n",
        "cross join\n",
        "  jsonb_array_elements(parties -> 'additionalIdentifiers') as additionalIdentifiers\n",
        "where\n",
        "  collection_id = :collection_id\n",
        "group by\n",
        "  scheme;"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "LOsLSGRhMaqo"
      },
      "source": [
        "### Coherence"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "KmU04Zk0Maqo"
      },
      "source": [
        "#### Project status"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "l3OvG1oBMaqp"
      },
      "source": [
        "Count projects by status:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "rS0XluxsMaqp"
      },
      "outputs": [],
      "source": [
        "%%sql project_status <<\n",
        "\n",
        "-- count projects by status\n",
        "with projects_by_status as (\n",
        "  select\n",
        "    coalesce(data ->> 'status', 'None') as status,\n",
        "    count(*) as project_count\n",
        "  from\n",
        "    projects\n",
        "  where\n",
        "    collection_id = :collection_id\n",
        "  group by\n",
        "    status\n",
        ")\n",
        "-- join to project status codelist\n",
        "select\n",
        "  status,\n",
        "  coalesce(project_count,0) as project_count\n",
        "from\n",
        "  (values\n",
        "    ('identification', 1),\n",
        "    ('preparation', 2),\n",
        "    ('implementation', 3),\n",
        "    ('completion', 4),\n",
        "    ('completed', 5),\n",
        "    ('cancelled', 6),\n",
        "    ('None', 7)\n",
        "  ) as codelist (status, ordering)\n",
        "left join\n",
        "  projects_by_status using (status) \n",
        "order by\n",
        "  ordering asc;"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "ychmRg9HhOK6"
      },
      "outputs": [],
      "source": [
        "project_status_chart = sns.catplot(data = project_status, kind=\"bar\", x=\"status\", y=\"project_count\")\n",
        "\n",
        "plt.xticks(rotation=90)\n",
        "\n",
        "for ax in project_status_chart.axes.flat:\n",
        "  format_thousands(ax.yaxis)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "kkwh_Jd5Maqr"
      },
      "source": [
        "#### Project sector"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "5HjA7-o4Maqs"
      },
      "source": [
        "Count projects by sector:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Mym_RorwMaqs"
      },
      "outputs": [],
      "source": [
        "%%sql project_sector <<\n",
        "\n",
        "select\n",
        "  coalesce(data ->> 'sector', 'None') as sector,\n",
        "  count(*) as project_count\n",
        "from\n",
        "  projects\n",
        "where\n",
        "  collection_id = :collection_id\n",
        "group by\n",
        "  sector\n",
        "order by\n",
        "  project_count desc;"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "SU6G4w8hhgDP"
      },
      "outputs": [],
      "source": [
        "project_sector_chart = sns.catplot(data = project_sector, kind=\"bar\", x=\"project_count\", y=\"sector\")\n",
        "\n",
        "for ax in project_sector_chart.axes.flat:\n",
        "  format_thousands(ax.xaxis)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "E1k8rcPjMaqv"
      },
      "source": [
        "#### Project type"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "rP_0Qd03Maqv"
      },
      "source": [
        "Count projects by type:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "H3Vek_YtMaqv"
      },
      "outputs": [],
      "source": [
        "%%sql project_type <<\n",
        "\n",
        "select\n",
        "  coalesce(data ->> 'type', 'None') as type,\n",
        "  count(*) as project_count\n",
        "from\n",
        "  projects\n",
        "where\n",
        "  collection_id = :collection_id\n",
        "group by\n",
        "  type\n",
        "order by\n",
        "  count(*) desc;"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "-M1qRRuthnL1"
      },
      "outputs": [],
      "source": [
        "project_type_chart = sns.catplot(data = project_type, kind=\"bar\", x=\"project_count\", y=\"type\")\n",
        "\n",
        "for ax in project_type_chart.axes.flat:\n",
        "  format_thousands(ax.xaxis)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "06NAYhtAMaq8"
      },
      "source": [
        "#### Public authority"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "0NXnBvnaMaq9"
      },
      "source": [
        "Count projects by public authority (top 10):"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "DgqSFkrGMaq9"
      },
      "outputs": [],
      "source": [
        "%%sql public_authority <<\n",
        "\n",
        "select\n",
        "  coalesce(data -> 'publicAuthority' ->> 'name', 'None') as public_authority,\n",
        "  count(*) as project_count\n",
        "from\n",
        "  projects\n",
        "where\n",
        "  collection_id = :collection_id\n",
        "group by\n",
        "  public_authority\n",
        "order by\n",
        "  project_count desc\n",
        "limit\n",
        "  10;"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "x3PRRZ49kIuM"
      },
      "outputs": [],
      "source": [
        "public_authority_chart = sns.catplot(data = public_authority, kind=\"bar\", x=\"project_count\", y=\"public_authority\")\n",
        "\n",
        "for ax in public_authority_chart.axes.flat:\n",
        "  format_thousands(ax.xaxis)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "7ip-7SoXMarA"
      },
      "source": [
        "#### Project budget"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "zFrONGBWkZfK"
      },
      "source": [
        "Get a list of projects and budgets:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "eb_2qPtbh-74"
      },
      "outputs": [],
      "source": [
        "%%sql project_budget <<\n",
        "\n",
        "select\n",
        "  coalesce((data -> 'budget' -> 'amount' ->> 'amount')::numeric) as budget,\n",
        "  coalesce(data -> 'budget' -> 'amount' ->> 'currency', 'None') as currency\n",
        "from\n",
        "  projects\n",
        "where\n",
        "  collection_id = :collection_id\n",
        "and\n",
        "  coalesce((data -> 'budget' -> 'amount' ->> 'amount')::numeric) >0;\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "9eCKL6Qh7iWP"
      },
      "outputs": [],
      "source": [
        "project_budget['budget'] = pd.to_numeric(project_budget['budget'])\n",
        "\n",
        "grid = sns.displot(project_budget, x='budget', log_scale=True, col='currency')\n",
        "plt.xticks(rotation=45)\n",
        "grid.fig.subplots_adjust(top=0.9)\n",
        "grid.fig.suptitle('Project budget distribution, log scale')\n",
        "for ax in grid.axes.flat:\n",
        "    ax.set_xticklabels(ax.get_xticklabels(), rotation=45)\n",
        "    format_thousands(ax.xaxis)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "bm9jlHH8MarE"
      },
      "source": [
        "#### Organization roles"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "p5ewyLgYk9bu"
      },
      "source": [
        "Count parties by role:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "ks6KnDRQMarE"
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "\n",
        "select\n",
        "  parties -> 'roles' as roles,\n",
        "  count(*) as count\n",
        "from\n",
        "  projects\n",
        "cross join\n",
        "  jsonb_array_elements(data -> 'parties') as parties\n",
        "where\n",
        "  collection_id = :collection_id\n",
        "group by\n",
        "  roles;"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "jBy1ujx9dUiU"
      },
      "source": [
        "#### Organization identifiers"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "tTWbe_h6lECZ"
      },
      "source": [
        "Get a random sample of 3 organization identifiers:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "9mj13UuuMarK"
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "\n",
        "select\n",
        "  id as project_id,\n",
        "  parties ->> 'name' as name,\n",
        "  parties -> 'identifier' ->> 'scheme' as scheme,\n",
        "  parties -> 'identifier' ->> 'id' as id,\n",
        "  parties -> 'identifier' ->> 'legalName' as legalName\n",
        "from\n",
        "  projects\n",
        "cross join\n",
        "  jsonb_array_elements(data -> 'parties') as parties\n",
        "where\n",
        "  collection_id = :collection_id\n",
        "order by\n",
        "  random()\n",
        "limit\n",
        "  3;"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "HNDCwR2jeqiV"
      },
      "source": [
        "#### Contracting processes per project"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "-eYbzXWjlM9_"
      },
      "source": [
        "Get the number of contracting processes for each project:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "j0hgDvDxdm4s"
      },
      "outputs": [],
      "source": [
        "%%sql contracting_process_counts <<\n",
        "\n",
        "select\n",
        "  jsonb_array_length(coalesce(data -> 'contractingProcesses', '[]'::jsonb)) as contracting_process_count,\n",
        "  count(*) as project_count\n",
        "from\n",
        "  projects\n",
        "where\n",
        "  collection_id = :collection_id\n",
        "group by\n",
        "  contracting_process_count\n",
        "order by\n",
        "  contracting_process_count asc;"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "3lDBaQXFviMr"
      },
      "outputs": [],
      "source": [
        "contracting_process_count_chart = sns.catplot(data=contracting_process_counts, kind='bar', x='contracting_process_count', y='project_count')\n",
        "\n",
        "for ax in contracting_process_count_chart.axes.flat:\n",
        "  format_thousands(ax.yaxis)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "3rG9rOmOEn2m"
      },
      "source": [
        "#### Contract value"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "dXripSwBm6sd"
      },
      "source": [
        "Choose a currency to query:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "BgjMOu0am8bD"
      },
      "outputs": [],
      "source": [
        "currency = 'UAH'"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "DBb_xIX-mzwA"
      },
      "source": [
        "Get the value of each contract:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "_R_Sb60rEx_I"
      },
      "outputs": [],
      "source": [
        "%%sql contract_value <<\n",
        "\n",
        "select\n",
        "  round((contractingProcesses -> 'summary' -> 'contractValue' ->> 'amount')::numeric, 2) as value,\n",
        "  contractingProcesses -> 'summary' -> 'contractValue' ->> 'currency' as currency\n",
        "from\n",
        "  projects\n",
        "cross join\n",
        "  jsonb_array_elements(data -> 'contractingProcesses') as contractingProcesses\n",
        "where\n",
        "  collection_id = :collection_id\n",
        "--and\n",
        "--  contractingProcesses -> 'summary' -> 'contractValue' ->> 'currency' = :currency\n",
        "and\n",
        "  contractingProcesses -> 'summary' -> 'contractValue' ->> 'amount' is not null;"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "_RCi3LgZwv_U"
      },
      "outputs": [],
      "source": [
        "contract_value['value'] = pd.to_numeric(contract_value['value'])\n",
        "\n",
        "grid = sns.displot(contract_value, x='value', log_scale=True, col='currency')\n",
        "grid.fig.subplots_adjust(top=0.9)\n",
        "grid.fig.suptitle('Contract value distribution, log scale')\n",
        "for ax in grid.axes.flat:\n",
        "    ax.set_xticklabels(ax.get_xticklabels(), rotation=45)\n",
        "    format_thousands(ax.xaxis)\n",
        "  "
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "XN14YWjatSSb"
      },
      "source": [
        "### Coverage"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "km3OvxxB9GmP"
      },
      "source": [
        "If a field is on an object in an array, then coverage is reported for each object in the array. Example: There are 100 projects, all of which have 5 parties. The check for the `parties` field will be reported out of 100, but the checks for its child fields (like `parties.id`) will be reported out of 500.\n",
        "\n",
        "Child fields are reported in the context of their parent field. Example: There are 100 projects, 10 of which set `publicAuthority`. The check for the `publicAuthority` field will be reported out of 100, but the checks for its child fields (like `publicAuthority.id`) will be reported out of 10."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "6Yojv315uhy3"
      },
      "outputs": [],
      "source": [
        "%%sql coverage <<\n",
        "\n",
        "WITH project_count AS (\n",
        "SELECT\n",
        "\tcount(*)::NUMERIC\n",
        "FROM\n",
        "\tprojects\n",
        "WHERE\n",
        "\tcollection_id = :collection_id ),\n",
        "field_counts_filtered AS (\n",
        "SELECT \n",
        "\t*\n",
        "FROM \n",
        "\tfield_counts\n",
        "WHERE\n",
        "\tfield_counts.collection_id = :collection_id\n",
        ")\n",
        "SELECT DISTINCT ON (oc4ids_schema.path)\n",
        "\toc4ids_schema.path,\n",
        "\ttitle,\n",
        "\tCASE\n",
        "\t\tWHEN substring(RANGE FROM 1 FOR 1)::int = 1 THEN TRUE\n",
        "\t\tELSE FALSE\n",
        "\tEND AS required,\n",
        "\tCOALESCE(CASE\n",
        "\t\tWHEN array_length(field_counts.path_array,\n",
        "\t\t1) = 1 THEN round(field_counts.object_property::NUMERIC / (\n",
        "\t\tSELECT\n",
        "\t\t\t*\n",
        "\t\tFROM\n",
        "\t\t\tproject_count),\n",
        "\t\t2)\n",
        "\t\tELSE\n",
        "\t\tCASE\n",
        "\t\t\tWHEN parent_field_counts.array_count = 0 THEN round(field_counts.object_property::NUMERIC / parent_field_counts.object_property::NUMERIC,\n",
        "\t\t\t2)\n",
        "\t\t\tELSE round(field_counts.object_property::NUMERIC / parent_field_counts.array_count::NUMERIC,\n",
        "\t\t\t2)\n",
        "\t\tEND\n",
        "\tEND, 0.00) AS coverage\n",
        "FROM\n",
        "\toc4ids_schema\n",
        "LEFT JOIN field_counts_filtered AS field_counts ON\n",
        "\toc4ids_schema.path = field_counts.path\n",
        "LEFT JOIN field_counts_filtered AS parent_field_counts ON\n",
        "\tarray_to_string(field_counts.path_array[1:array_length(field_counts.path_array,\n",
        "\t1)-1],\n",
        "\t'/') = parent_field_counts.PATH;\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "S91s0IP_swmY"
      },
      "outputs": [],
      "source": [
        "coverage"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "UO3i5BVOC1YG"
      },
      "source": [
        "#### Important fields\n",
        "\n",
        "Check the coverage of fields needed to calculate OC4IDS indicators"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "_k6K9uUOqHul"
      },
      "outputs": [],
      "source": [
        "import csv\n",
        "import requests\n",
        "\n",
        "# Get the OC4IDS indicators\n",
        "response = requests.get('https://docs.google.com/spreadsheets/d/1Vo6-Jis-J61PB_33QQx1YKnmQnI7M4rTq6CTMckAFsE/export?format=csv&gid=835339370')\n",
        "reader = csv.DictReader(response.text.splitlines(keepends=True))\n",
        "\n",
        "# Count the number of indicators each field is needed for\n",
        "fields = {}\n",
        "for row in reader:\n",
        "  for field in row['fields'].splitlines():\n",
        "    if field in fields:\n",
        "      fields[field]['indicators'] += 1\n",
        "    else:\n",
        "      fields[field] = {'indicators': 1}\n",
        "\n",
        "# Add the number of indicators to the coverage DataFrame\n",
        "coverage.set_index('path', inplace=True)\n",
        "coverage['indicators'] = 0\n",
        "for field in fields:\n",
        "    coverage.loc[field, 'indicators'] = fields[field]['indicators']\n",
        "\n",
        "# Sort and display the DataFrame\n",
        "coverage.reset_index(inplace=True)\n",
        "coverage.sort_values(by=['indicators'], ascending=False)"
      ]
    }
  ],
  "metadata": {
    "colab": {
      "collapsed_sections": [
        "Us11kt9cMaqX",
        "RBgIdb4Qvu0k",
        "mnjUxidtMaqc",
        "fGihA30uMaqi",
        "KmU04Zk0Maqo",
        "kkwh_Jd5Maqr",
        "E1k8rcPjMaqv",
        "06NAYhtAMaq8",
        "7ip-7SoXMarA",
        "bm9jlHH8MarE",
        "jBy1ujx9dUiU",
        "HNDCwR2jeqiV",
        "3rG9rOmOEn2m"
      ],
      "name": "OC4IDS_Data_Feedback_Notebook.ipynb",
      "provenance": [],
      "toc_visible": true
    },
    "kernelspec": {
      "display_name": "Python 3",
      "language": "python",
      "name": "python3"
    },
    "language_info": {
      "codemirror_mode": {
        "name": "ipython",
        "version": 3
      },
      "file_extension": ".py",
      "mimetype": "text/x-python",
      "name": "python",
      "nbconvert_exporter": "python",
      "pygments_lexer": "ipython3",
      "version": "3.8.2"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}