<jupyter_start><jupyter_text>OC4IDS Data Feedback NotebookUse this notebook to provide feedback on OC4IDS data.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).  Setup Enter credentials:<jupyter_code># Get database credentials. OCDS Helpdesk analysts, see https://crm.open-contracting.org/issues/6335.
import getpass

print('Enter your credentials')
user = input('Username:')
password = get<PASSWORD>.getpass('Password:')<jupyter_output><empty_output><jupyter_text>Setup notebook environment:<jupyter_code>!pip install --upgrade ipython-sql > pip.log

connection_string = 'postgresql://' + user + ':' + password + '@database-1.cmc8bohiuyg3.us-east-1.rds.amazonaws.com/postgres'

# https://pypi.org/project/ipython-sql/
%load_ext sql 
%sql $connection_string
%config SqlMagic.autopandas = True  # Return Pandas DataFrames instead of regular result sets
%config SqlMagic.displaycon = False  # Don't show connection string after execute
%config SqlMagic.feedback = False  # Don't print number of rows affected by DML

# https://colab.research.google.com/notebooks/data_table.ipynb
%load_ext google.colab.data_table

# Import some useful analysis libraries
import pandas as pd
from scipy import stats
import numpy as np

# Define function for rendering collapsible JSON in a notebook
# Remove once https://github.com/open-contracting/kingfisher-colab/issues/40 is done
import json
from IPython.display import HTML

def render_json(jstr):
  if type(jstr) != str:
    jstr = json.dumps(jstr)
  return HTML("""
<script src="https://rawgit.com/caldwell/renderjson/master/renderjson.js"></script>
<script>
renderjson.set_show_to_level(1)
document.body.appendChild(renderjson(%s))
new ResizeObserver(google.colab.output.resizeIframeToContent).observe(document.body)
</script>
""" % jstr)

# Install and setup plotting library
!pip install seaborn >> pip.log

import seaborn as sns
import matplotlib.pyplot as plt
import matplotlib.ticker as tkr

colab_dark_style = {
  'figure.facecolor': '#383838',
  'axes.edgecolor': '#d5d5d5',
  'axes.facecolor': '#383838',
  'axes.labelcolor': '#d5d5d5',
  'text.color': '#d5d5d5',
  'xtick.bottom': True,
  'xtick.color': '#d5d5d5',
  'ytick.bottom': True,
  'ytick.color': '#d5d5d5',
}

sns.set_style('dark', colab_dark_style)

# Define function to apply number formatting to axis labels
# Maybe this can also be moved to Kingfisher-Colab?
# Needs updating to support other locales
def format_thousands(axis):
  axis.set_major_formatter(tkr.FuncFormatter(lambda x, pos: '{:,.0f}'.format(x)))<jupyter_output><empty_output><jupyter_text>Analysis  Choose a collection to query Get the list of collections:<jupyter_code>%%sql

select
  collection.id as collection_id,
  source_id,
  data_version,
  count(*) as project_count
from
  collection
join
  projects on collection.id = projects.collection_id
group by
  collection.id,
  source_id,
  data_version
 order by
  collection.id desc;<jupyter_output><empty_output><jupyter_text>Set the `collection_id` to query:<jupyter_code>collection_id = 30<jupyter_output><empty_output><jupyter_text>Scope Count projects:<jupyter_code>%%sql

select
    count(*)
from
    projects
where
    collection_id = :collection_id<jupyter_output><empty_output><jupyter_text>Count contracting processes:<jupyter_code>%%sql

select
    count(*)
from
    projects
cross join
    jsonb_array_elements(data -> 'contractingProcesses')
where
    collection_id = :collection_id;<jupyter_output><empty_output><jupyter_text>Structure and format Get a list of validation errors reported by CoVE:<jupyter_code>%%sql

select
  replace(trim('"' from (validation_errors -> 0)::text), '\', '')::jsonb -> 'message' as error,
  regexp_replace(jsonb_array_elements(validation_errors -> 1) ->> 'path', '/\d+', '', 'g') as path,
  count(*)
from
  collection_check
cross join
  jsonb_array_elements(cove_output -> 'validation_errors') as validation_errors
where
  collection_id = :collection_id
group by
  error,
  path
order by
  error,
  count desc;<jupyter_output><empty_output><jupyter_text>Get a list of structure warnings reported by CoVE:<jupyter_code>%%sql

with warnings_list as (
    select
        key as warning_type,
        trim('"' from paths::text) as paths
    from
        collection_check
    cross join
        jsonb_each(cove_output -> 'structure_warnings')
    cross join
        jsonb_array_elements(value) as paths
    where
      collection_id = :collection_id
)
select
    warning_type,
    regexp_replace(paths, '/\d+', '', 'g') as path,
    count(*) as count
from
    warnings_list
group by
    warning_type,
    path
order by
    count desc<jupyter_output><empty_output><jupyter_text>Conformance  Project identifier prefix Get the project prefixes used in the data:<jupyter_code>%%sql

select distinct
  substring(project_id from 1 for 13) as prefix
from
    projects
where
  collection_id = :collection_id<jupyter_output><empty_output><jupyter_text>Check that the data uses a [registered prefix](https://standard.open-contracting.org/infrastructure/latest/en/guidance/identifiers/existing-prefixes).  Duplicate project identifiers Get a list of project ids which appear more than once:<jupyter_code>%%sql

select
  project_id,
  count(*)
from
  projects
where
  collection_id = :collection_id
group by
  project_id
having
  count(*) > 1
order by
  count(*) desc;<jupyter_output><empty_output><jupyter_text>Updated date Get the earliest and latest updated dates:<jupyter_code>%%sql

select
  min(data ->> 'updated') as min_date,
  max(data ->> 'updated') as maxb_date
from
  projects
where
  collection_id = :collection_id<jupyter_output><empty_output><jupyter_text>Additional fields Get a list of additional fields reported by CoVE:<jupyter_code>%%sql

select
    key as path,
    value -> 'count' as count,
    value -> 'examples' as examples,
    value -> 'additional_field_descendance' as descendents
from
    collection_check
cross join
    jsonb_each(cove_output -> 'additional_fields')
where
  collection_id = :collection_id<jupyter_output><empty_output><jupyter_text>Organization references For each organization reference in the schema:* Count how many organization references have an `.id` that does not match the `.id` of any parties.* Count how many projects have an organization reference with an `.id` that does not match the `.id` of any parties.* 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.* Get the `.id` and `.name` of the first 3 organization references where `.id` does not match the `.id` of any parties.<jupyter_code>%%sql

WITH party_ids AS (
  SELECT
    project_id,
    parties ->> 'id' AS id
  FROM
    projects
    CROSS JOIN jsonb_array_elements(data -> 'parties') AS parties
  WHERE
    collection_id = :collection_id
),
organization_references AS (
  SELECT
    'publicAuthority' AS path,
    projects.project_id AS project_id,
    data -> 'publicAuthority' ->> 'name' AS name,
    data -> 'publicAuthority' ->> 'id' AS id
  FROM
    projects
  WHERE
    collection_id = :collection_id
  UNION ALL
  SELECT
    'budget/budgetBreakdown/sourceParty' AS path,
    projects.project_id AS project_id,
    budgetBreakdown -> 'sourceParty' ->> 'name' AS name,
    budgetBreakdown -> 'sourceParty' ->> 'id' AS id
  FROM
    projects
    CROSS JOIN jsonb_array_elements(data -> 'budget' -> 'budgetBreakdown') AS budgetBreakdown
  WHERE
    collection_id = :collection_id
  UNION ALL
  SELECT
    'contractingProcesses/summary/tender/tenderers' AS path,
    projects.project_id AS project_id,
    tenderers ->> 'name' AS name,
    tenderers ->> 'id' AS id
  FROM
    projects
    CROSS JOIN jsonb_array_elements(data -> 'contractingProcesses') AS contractingProcesses
    CROSS JOIN jsonb_array_elements(contractingProcesses -> 'summary' -> 'tender' -> 'tenderers') AS tenderers
  WHERE
    collection_id = :collection_id
  UNION ALL
  SELECT
    'contractingProcesses/summary/tender/procuringEntity' AS path,
    projects.project_id AS project_id,
    contractingProcesses -> 'summary' -> 'tender' -> 'procuringEntity' ->> 'name' AS name,
    contractingProcesses -> 'summary' -> 'tender' -> 'procuringEntity' ->> 'id' AS id
  FROM
    projects
    CROSS JOIN jsonb_array_elements(data -> 'contractingProcesses') AS contractingProcesses
  WHERE
    collection_id = :collection_id
  UNION ALL
  SELECT
    'contractingProcesses/summary/tender/administrativeEntity' AS path,
    projects.project_id AS project_id,
    contractingProcesses -> 'summary' -> 'tender' -> 'administrativeEntity' ->> 'name' AS name,
    contractingProcesses -> 'summary' -> 'tender' -> 'administrativeEntity' ->> 'id' AS id
  FROM
    projects
    CROSS JOIN jsonb_array_elements(data -> 'contractingProcesses') AS contractingProcesses
  WHERE
    collection_id = :collection_id
  UNION ALL
  SELECT
    'contractingProcesses/summary/suppliers' AS path,
    projects.project_id AS project_id,
    suppliers ->> 'name' AS name,
    suppliers ->> 'id' AS id
  FROM
    projects
    CROSS JOIN jsonb_array_elements(data -> 'contractingProcesses') AS contractingProcesses
    CROSS JOIN jsonb_array_elements(contractingProcesses -> 'summary' -> 'suppliers') AS suppliers
   WHERE
    collection_id = :collection_id
)
SELECT
  path,
  count(*) as broken_ref_count,
  count(distinct(project_id)) as project_with_broken_ref_count,
  (array_agg(project_id))[1:3] as first_3_projects,
  (array_agg(id || ': ' || name))[1:3] as first_3_references
FROM
  organization_references
  LEFT JOIN party_ids USING (id, project_id)
WHERE
  organization_references.id IS NOT NULL
  AND party_ids.id IS NULL
GROUP BY
  path
ORDER BY
  count(*) DESC;<jupyter_output><empty_output><jupyter_text>Organization identifier schemes **`parties/identifier`** Get a list of organization identifier schemes used in `parties/identifier`:<jupyter_code>%%sql

select
  parties -> 'identifier' -> 'scheme' as scheme,
  count(*)
from
  projects
cross join
  jsonb_array_elements(data -> 'parties') as parties
where
  collection_id = :collection_id
group by
  scheme;<jupyter_output><empty_output><jupyter_text>Check that the schemes appear in [org-id.guide](http://org-id.guide/). **`parties/additionalIdentifiers`** Get a list of organization identifier schemes used in `parties/additionalIdentifiers`:<jupyter_code>%%sql

select
  additionalIdentifiers -> 'scheme' as scheme,
  count(*)
from
  projects
cross join
  jsonb_array_elements(data -> 'parties') as parties
cross join
  jsonb_array_elements(parties -> 'additionalIdentifiers') as additionalIdentifiers
where
  collection_id = :collection_id
group by
  scheme;<jupyter_output><empty_output><jupyter_text>Coherence  Project status Count projects by status:<jupyter_code>%%sql project_status <<

-- count projects by status
with projects_by_status as (
  select
    coalesce(data ->> 'status', 'None') as status,
    count(*) as project_count
  from
    projects
  where
    collection_id = :collection_id
  group by
    status
)
-- join to project status codelist
select
  status,
  coalesce(project_count,0) as project_count
from
  (values
    ('identification', 1),
    ('preparation', 2),
    ('implementation', 3),
    ('completion', 4),
    ('completed', 5),
    ('cancelled', 6),
    ('None', 7)
  ) as codelist (status, ordering)
left join
  projects_by_status using (status) 
order by
  ordering asc;
project_status_chart = sns.catplot(data = project_status, kind="bar", x="status", y="project_count")

plt.xticks(rotation=90)

for ax in project_status_chart.axes.flat:
  format_thousands(ax.yaxis)<jupyter_output><empty_output><jupyter_text>Project sector Count projects by sector:<jupyter_code>%%sql project_sector <<

select
  coalesce(data ->> 'sector', 'None') as sector,
  count(*) as project_count
from
  projects
where
  collection_id = :collection_id
group by
  sector
order by
  project_count desc;
project_sector_chart = sns.catplot(data = project_sector, kind="bar", x="project_count", y="sector")

for ax in project_sector_chart.axes.flat:
  format_thousands(ax.xaxis)<jupyter_output><empty_output><jupyter_text>Project type Count projects by type:<jupyter_code>%%sql project_type <<

select
  coalesce(data ->> 'type', 'None') as type,
  count(*) as project_count
from
  projects
where
  collection_id = :collection_id
group by
  type
order by
  count(*) desc;
project_type_chart = sns.catplot(data = project_type, kind="bar", x="project_count", y="type")

for ax in project_type_chart.axes.flat:
  format_thousands(ax.xaxis)<jupyter_output><empty_output><jupyter_text>Public authority Count projects by public authority (top 10):<jupyter_code>%%sql public_authority <<

select
  coalesce(data -> 'publicAuthority' ->> 'name', 'None') as public_authority,
  count(*) as project_count
from
  projects
where
  collection_id = :collection_id
group by
  public_authority
order by
  project_count desc
limit
  10;
public_authority_chart = sns.catplot(data = public_authority, kind="bar", x="project_count", y="public_authority")

for ax in public_authority_chart.axes.flat:
  format_thousands(ax.xaxis)<jupyter_output><empty_output><jupyter_text>Project budget Get a list of projects and budgets:<jupyter_code>%%sql project_budget <<

select
  coalesce((data -> 'budget' -> 'amount' ->> 'amount')::numeric) as budget,
  coalesce(data -> 'budget' -> 'amount' ->> 'currency', 'None') as currency
from
  projects
where
  collection_id = :collection_id
and
  coalesce((data -> 'budget' -> 'amount' ->> 'amount')::numeric) >0;

project_budget['budget'] = pd.to_numeric(project_budget['budget'])

grid = sns.displot(project_budget, x='budget', log_scale=True, col='currency')
plt.xticks(rotation=45)
grid.fig.subplots_adjust(top=0.9)
grid.fig.suptitle('Project budget distribution, log scale')
for ax in grid.axes.flat:
    ax.set_xticklabels(ax.get_xticklabels(), rotation=45)
    format_thousands(ax.xaxis)<jupyter_output><empty_output><jupyter_text>Organization roles Count parties by role:<jupyter_code>%%sql

select
  parties -> 'roles' as roles,
  count(*) as count
from
  projects
cross join
  jsonb_array_elements(data -> 'parties') as parties
where
  collection_id = :collection_id
group by
  roles;<jupyter_output><empty_output><jupyter_text>Organization identifiers Get a random sample of 3 organization identifiers:<jupyter_code>%%sql

select
  id as project_id,
  parties ->> 'name' as name,
  parties -> 'identifier' ->> 'scheme' as scheme,
  parties -> 'identifier' ->> 'id' as id,
  parties -> 'identifier' ->> 'legalName' as legalName
from
  projects
cross join
  jsonb_array_elements(data -> 'parties') as parties
where
  collection_id = :collection_id
order by
  random()
limit
  3;<jupyter_output><empty_output><jupyter_text>Contracting processes per project Get the number of contracting processes for each project:<jupyter_code>%%sql contracting_process_counts <<

select
  jsonb_array_length(coalesce(data -> 'contractingProcesses', '[]'::jsonb)) as contracting_process_count,
  count(*) as project_count
from
  projects
where
  collection_id = :collection_id
group by
  contracting_process_count
order by
  contracting_process_count asc;
contracting_process_count_chart = sns.catplot(data=contracting_process_counts, kind='bar', x='contracting_process_count', y='project_count')

for ax in contracting_process_count_chart.axes.flat:
  format_thousands(ax.yaxis)<jupyter_output><empty_output><jupyter_text>Contract value Choose a currency to query:<jupyter_code>currency = 'UAH'<jupyter_output><empty_output><jupyter_text>Get the value of each contract:<jupyter_code>%%sql contract_value <<

select
  round((contractingProcesses -> 'summary' -> 'contractValue' ->> 'amount')::numeric, 2) as value,
  contractingProcesses -> 'summary' -> 'contractValue' ->> 'currency' as currency
from
  projects
cross join
  jsonb_array_elements(data -> 'contractingProcesses') as contractingProcesses
where
  collection_id = :collection_id
--and
--  contractingProcesses -> 'summary' -> 'contractValue' ->> 'currency' = :currency
and
  contractingProcesses -> 'summary' -> 'contractValue' ->> 'amount' is not null;
contract_value['value'] = pd.to_numeric(contract_value['value'])

grid = sns.displot(contract_value, x='value', log_scale=True, col='currency')
grid.fig.subplots_adjust(top=0.9)
grid.fig.suptitle('Contract value distribution, log scale')
for ax in grid.axes.flat:
    ax.set_xticklabels(ax.get_xticklabels(), rotation=45)
    format_thousands(ax.xaxis)<jupyter_output><empty_output><jupyter_text>Coverage 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.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.<jupyter_code>%%sql

WITH project_count AS (
SELECT
	count(*)::NUMERIC
FROM
	projects
WHERE
	collection_id = :collection_id ),
field_counts_filtered AS (
SELECT 
	*
FROM 
	field_counts
WHERE
	field_counts.collection_id = :collection_id
)
SELECT DISTINCT ON (oc4ids_schema.path)
	oc4ids_schema.path,
	title,
	CASE
		WHEN substring(RANGE FROM 1 FOR 1)::int = 1 THEN TRUE
		ELSE FALSE
	END AS required,
	CASE
		WHEN array_length(field_counts.path_array,
		1) = 1 THEN round(field_counts.object_property::NUMERIC / (
		SELECT
			*
		FROM
			project_count),
		2)
		ELSE
		CASE
			WHEN parent_field_counts.array_count = 0 THEN round(field_counts.object_property::NUMERIC / parent_field_counts.object_property::NUMERIC,
			2)
			ELSE round(field_counts.object_property::NUMERIC / parent_field_counts.array_count::NUMERIC,
			2)
		END
	END AS coverage
FROM
	oc4ids_schema
LEFT JOIN field_counts_filtered AS field_counts ON
	oc4ids_schema.path = field_counts.path
LEFT JOIN field_counts_filtered AS parent_field_counts ON
	array_to_string(field_counts.path_array[1:array_length(field_counts.path_array,
	1)-1],
	'/') = parent_field_counts.PATH;<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10521743851457734, 'fraction_numerical': 0.006677886964547478, 'mean_word_length': 5.164658634538153, 'pattern_counts': {'":': 0, '<': 124, '<?xml version=': 0, '>': 244, 'https://': 7, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '26', 'dir': 'jupyter-structured-clean-dedup', 'id': '550889', 'license': 'BSD-3-Clause', 'n_tokens_mistral': 6197, 'n_tokens_neox': 5980, 'n_words': 1564, 'path': 'OC4IDS_Data_Feedback_Notebook.ipynb', 'repo_name': 'open-contracting/notebooks-oc4ids', 'size': '18413'}