OC4IDS_Indicator_Coverage.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "colab_type": "text",
        "id": "view-in-github"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/open-contracting/notebooks-oc4ids/blob/indicator_coverage/OC4IDS_Indicator_Coverage.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "RYluNfWQv-xH"
      },
      "source": [
        "# OC4IDS Indicator Coverage\n",
        "\n",
        "Use this notebook to calculate the coverage of the indicators defined in the [OC4IDS Indicators spreadsheet](https://docs.google.com/spreadsheets/d/1Vo6-Jis-J61PB_33QQx1YKnmQnI7M4rTq6CTMckAFsE/edit#gid=882740051).\n",
        "\n",
        "The notebook generates:\n",
        "\n",
        "* A CSV file containing a copy of the [Indicators sheet](https://docs.google.com/spreadsheets/d/1Vo6-Jis-J61PB_33QQx1YKnmQnI7M4rTq6CTMckAFsE/edit#gid=2023307133), annotated with:\n",
        "  * The coverage for the indicator\n",
        "  * The coverage for the each field needed to calculate the indicator\n",
        "* Charts showing:\n",
        "  * How many indicators are calculable for at least one project, grouped by use case\n",
        "  * How many indicators are calculable for less than 33%, 33-66% and greater than 66% of projects:\n",
        "\n",
        "Coverage is measured based on how many projects include the required fields for an indicator. There may be some false positives, since more granular levels of coverage are not measured. For example, how many individual contracting processes within a project include the required fields. See [this issue](https://github.com/open-contracting/kingfisher-views/issues/29#issuecomment-680326386) for a detailed explanation."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "trtuxKpsToCS"
      },
      "source": [
        "## Setup"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "1CphynSNKxqb"
      },
      "source": [
        "Enter your OC4IDS database credentials. OCDS Helpdesk analysts and OCP staff can find credentials in [CRM-6335](https://crm.open-contracting.org/issues/6335)."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "27yRx-oaXPtd",
        "vscode": {
          "languageId": "python"
        }
      },
      "outputs": [],
      "source": [
        "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": "PZShlCS_ToCe",
        "vscode": {
          "languageId": "python"
        }
      },
      "outputs": [],
      "source": [
        "# https://ocdskit.readthedocs.io/\n",
        "!pip install ocdskit\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",
        "!pip install --upgrade ipython-sql > pip.log\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",
        "\n",
        "import csv\n",
        "import matplotlib.pyplot as plt\n",
        "import pandas as pd\n",
        "import requests\n",
        "import seaborn as sns\n",
        "\n",
        "from google.colab import files\n",
        "\n",
        "def get_gsheet(doc_id, sheet_id):\n",
        "\n",
        "  url = f'https://docs.google.com/spreadsheets/d/{doc_id}/export?format=csv&gid={sheet_id}'\n",
        "  response = requests.get(url)\n",
        "  content = response.content.decode('utf-8').splitlines(keepends=True)\n",
        "\n",
        "  return csv.DictReader(content, quotechar='\"')"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "kp_b-P2X4oM8"
      },
      "source": [
        "## Choose a collection to query"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "qiPI00RHToCq"
      },
      "source": [
        "Set the `collection_id` to query:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "dZv0vp_bToCr",
        "vscode": {
          "languageId": "python"
        }
      },
      "outputs": [],
      "source": [
        "collection_id = 41"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "5Ej0K5JkToCo"
      },
      "source": [
        "If you don't know which collection you need, run the next cell and use the **Filter** button to filter the collection table to find the collection. You can use the `source_id` column to filter on the name of the data source entered when the data was imported. Use the value from the `id` column to update the previous cell."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "VO2tADcLToCp",
        "vscode": {
          "languageId": "python"
        }
      },
      "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": "Y8rX2LkkwFdj"
      },
      "source": [
        "## Calculate coverage"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "2WF0fWUfUdmC"
      },
      "source": [
        "Download the OC4IDS schema and generate a [mapping sheet](https://ocdskit.readthedocs.io/en/latest/cli/schema.html#mapping-sheet):"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Gyi-3pMxwKPI",
        "vscode": {
          "languageId": "python"
        }
      },
      "outputs": [],
      "source": [
        "%%shell\n",
        "\n",
        "curl 'https://standard.open-contracting.org/infrastructure/latest/en/project-schema.json' > project-schema.json\n",
        "ocdskit mapping-sheet project-schema.json > project-schema.csv"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "HpFOhujPnHfm"
      },
      "source": [
        "Calculate coverage:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "F5gqx0hoALW8",
        "vscode": {
          "languageId": "python"
        }
      },
      "outputs": [],
      "source": [
        "with open('project-schema.csv', 'r') as f:\n",
        "\n",
        "  oc4ids_fields = [field['path'] for field in csv.DictReader(f)]\n",
        "\n",
        "indicator_reader = get_gsheet('1Vo6-Jis-J61PB_33QQx1YKnmQnI7M4rTq6CTMckAFsE', '2023307133')\n",
        "indicators = {indicator['id']: indicator for indicator in indicator_reader}\n",
        "\n",
        "method_reader = get_gsheet('1Vo6-Jis-J61PB_33QQx1YKnmQnI7M4rTq6CTMckAFsE', '835339370')\n",
        "\n",
        "method_query_template = \"\"\"\n",
        "\n",
        "    SELECT\n",
        "      SUM(CASE WHEN ARRAY{fields} <@ paths THEN 1 ELSE 0 END) as successes,\n",
        "      count(*) as checks,\n",
        "      SUM(CASE WHEN ARRAY{fields} <@ paths THEN 1 ELSE 0 END) / count(*)::numeric as percentage\n",
        "    FROM\n",
        "      project_fields\n",
        "    WHERE\n",
        "      collection_id = {collection_id};\n",
        "\n",
        "\"\"\"\n",
        "\n",
        "field_query_template = \"\"\"\n",
        "\n",
        "    SELECT\n",
        "      path,\n",
        "      distinct_projects\n",
        "    FROM\n",
        "      field_counts\n",
        "    WHERE\n",
        "      collection_id = {collection_id}\n",
        "    AND\n",
        "      path IN {fields}\n",
        "\n",
        "\"\"\"\n",
        "\n",
        "for method in method_reader:\n",
        "\n",
        "  method['fields'] = {field: {'coverage': 0} for field in method['fields'].split('\\n')}\n",
        " \n",
        "  # Check that the field paths for this method are valid\n",
        "  for field in method['fields'].keys():\n",
        "    if len(field) > 0:\n",
        "      assert field in oc4ids_fields, f'Found invalid fields in method {method[\"id\"]}: {field}. ' \\\n",
        "                                    'Update the OC4IDS Indicators spreadsheet to correct the field path.'\n",
        "\n",
        "  # Calculate coverage for this method\n",
        "  method_query = method_query_template.format(collection_id = collection_id, fields = list(method['fields'].keys()))\n",
        "  method_coverage = %sql {method_query}\n",
        "\n",
        "  method['coverage'] = {'successes': method_coverage['successes'][0],\n",
        "                        'checks': method_coverage ['checks'][0],\n",
        "                        'percentage': method_coverage['percentage'][0]}\n",
        "\n",
        "  # Calculate coverage for each field in this method\n",
        "  field_query = field_query_template.format(collection_id = collection_id, fields = tuple(method['fields'].keys()))\n",
        "  field_coverage = %sql {field_query}\n",
        "\n",
        "  for field, coverage in field_coverage.set_index('path').to_dict('index').items():\n",
        "    method['fields'][field]['coverage'] = coverage['distinct_projects'] / method['coverage']['checks']\n",
        "\n",
        "  # Add method details to indicators\n",
        "  if 'methods' not in indicators[method['indicator_id']]:\n",
        "    indicators[method['indicator_id']]['methods'] = [method]\n",
        "  else:\n",
        "    indicators[method['indicator_id']]['methods'].append(method)\n",
        "\n",
        "for indicator in indicators.values():\n",
        "\n",
        "  # Calcuate indicator coverage, i.e. the coverage of the best method\n",
        "  indicator['coverage'] = -1\n",
        "\n",
        "  for method in indicators[indicator['id']]['methods']:\n",
        "    \n",
        "    if method['coverage']['percentage'] > indicator['coverage']:\n",
        "\n",
        "      indicator['best_method'] = method['id']\n",
        "      indicator['coverage'] = method['coverage']['percentage']\n",
        "      indicator['successes'] = method['coverage']['successes']\n",
        "      indicator['checks'] = method['coverage']['checks']\n",
        "      indicator['field_coverage'] = '\\n'.join([f'{path}: {\"{:.0%}\".format(details[\"coverage\"])}' for path, details in method['fields'].items()])\n",
        "      indicator['missing_fields'] = '\\n'.join([path for path, details in method['fields'].items() if details[\"coverage\"] == 0])\n",
        "\n",
        "# Generate indicator coverage report\n",
        "with open('coverage.csv', 'w') as f:\n",
        "\n",
        "  fieldnames = indicator_reader.fieldnames\n",
        "  fieldnames.extend(['best_method', 'coverage', 'successes', 'checks', 'field_coverage', 'missing_fields'])\n",
        "\n",
        "  writer = csv.DictWriter(f, fieldnames = fieldnames)\n",
        "  writer.writeheader()\n",
        "\n",
        "  for indicator in indicators.values():\n",
        "    row = dict(indicator)\n",
        "    row.pop('methods')\n",
        "    writer.writerow(row)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "yAxruFzNx3FB"
      },
      "source": [
        "Download coverage report:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "zoDL0w-WxhfA",
        "vscode": {
          "languageId": "python"
        }
      },
      "outputs": [],
      "source": [
        "files.download('coverage.csv')"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "FxSMTG4Cajcj"
      },
      "source": [
        "Generate coverage summary:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "M8d2-G-OalA6",
        "vscode": {
          "languageId": "python"
        }
      },
      "outputs": [],
      "source": [
        "coverage_summary = pd.DataFrame.from_dict(indicators, orient='index')\n",
        "coverage_summary = coverage_summary[coverage_summary['deprecated'] == 'FALSE']"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "VhYQNv7rnbns"
      },
      "source": [
        "Plot how many indicators are calculable for at least one project, grouped by use case:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "6XAI5Ttch0gD",
        "vscode": {
          "languageId": "python"
        }
      },
      "outputs": [],
      "source": [
        "coverage_summary['calculable'] = coverage_summary['coverage'] > 0\n",
        "calculable = coverage_summary[['id', 'calculable', 'use_case']].groupby(['use_case','calculable']).count()\n",
        "calculable.reset_index(inplace=True)\n",
        "\n",
        "plt.figure(figsize = (15,8))\n",
        "ax = sns.histplot(calculable, x='use_case', hue='calculable', weights='id', multiple='stack')\n",
        "plt.xticks(rotation=45)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "rJgfjz-Injxj"
      },
      "source": [
        "Plot how many indicators are calculable for less than 33%, 33-66% and greater than 66% of projects:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "M8UYJCyCjcfz",
        "vscode": {
          "languageId": "python"
        }
      },
      "outputs": [],
      "source": [
        "def coverage_bin(coverage):\n",
        "  if coverage < 0.33:\n",
        "    return '<33%'\n",
        "  elif coverage <= 0.66:\n",
        "    return '33-66%'\n",
        "  else:\n",
        "    return  '>66%'\n",
        "\n",
        "coverage_summary['coverage_bin'] = coverage_summary.apply(lambda x: coverage_bin(x['coverage']), axis=1)\n",
        "\n",
        "ax = sns.countplot(x='coverage_bin', order=['<33%', '33-66%', '>66%'], data=coverage_summary)"
      ]
    }
  ],
  "metadata": {
    "colab": {
      "include_colab_link": true,
      "name": "OC4IDS Indicator Coverage",
      "provenance": []
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}