This article explains how to combine Open Data from Nationale Ombudsman and CBS into statistics.
Instructions
The Nationale Ombudsman is used by a number of municipalities as an escalation step after the internal complaint procedure failed to resolve an issue. Goal of the article is to relate the number of complaints at the Nationale Ombudsman office with the number of inhabitants.
First of all, a data set in spreadsheet format was retrieved which included details on the number of received complaints per municipality started in 2018:
| Bestand | Gewijzigd |
|---|---|
| Microsoft Excel-spreadsheet rapport.xlsx Nationale Ombudsman received complaints per municipality in 2018 | 27-08-2019 by Guido Leenders |
The data set also contained noise in the sense that:
- offices were included which are not legally a municipality,
- unmapped resolution codes were included.
The spreadsheet contains the name of the municipality as used by the Nationale Ombudsman plus the resolution code.
The following resolution codes for complaints were registered using an in-memory lookup table:
create or replace table afdoeningswijze_codes@inmemorystorage
as
select '01' code, 'geluisterd' omschrijving
union all select '03', 'vraag beantwoord'
union all select '10', 'verwijzen of informeren (geen overheid)'
union all select '20', 'verwijzen of informeren (geen overheid)'
union all select '21', 'verwijzen of informeren; kennelijk ongegrond'
union all select '23', 'klacht te laat ingediend'
union all select '24', 'klacht terugverwijzen voor interne klachtbehandeling'
union all select '25', 'bezwaar, beroep, lopende procedure bij rechter, rechterlijke uitspraak'
union all select '26', 'niet eens met wettelijke regeling of beleid'
union all select '30', 'oplossing door interventie'
union all select '40', 'bulk interventie'
union all select '50', 'bemiddeling'
union all select '51', 'goed gesprek'
union all select '60', 'tussentijds beëindigd'
union all select '71', 'brief na onderzoek'
union all select '73', 'brief na onderzoek uit eigen beweging'
union all select '81', 'rapport na onderzoek'
union all select '82', 'rapport na interventie'
union all select '83', 'rapport na bemiddeling'
union all select '84', 'rapport na onderzoek uit eigen beweging'
As an alternative, this lookup table could also have been populated using for instance a mass insert or a select from csvtable.
The Dutch CBS provides statistics on the number of inhabitants at the end of 2018 using the following Invantive SQL query on CBS data set 37230:
create or replace table municipalities@inmemorystorage
as
select rgo.Title
, volk.BevolkingAanHetEindeVanDePeriode_15
from cbsnl37230ned_typeddataset volk
join cbsnl37230ned_regios rgo
on rgo.Key = volk.Regios
and rgo.Key like 'GM%'
where volk.perioden='2018MM12'
--
-- Exclude no longer existing municipalities.
--
and volk.BevolkingAanHetBeginVanDePeriode_1 is not null
In the next step, we combine data from the Nationale Ombudsman with the CBS municipality statistics on individual complaint level:
create or replace table statdetails@inmemorystorage
as
select coalesce(mpy.title, stat.orgaan_naam)
orgaan_naam
label 'Gemeente'
, mpy.bevolkingaanheteindevandeperiode_15
label 'Aantal Inwoners'
, stat.afdoeningswijze_code
afdoeningswijze_code
label 'Afdoeningswijzecode'
, coalesce(ace.omschrijving, stat.afdoeningswijze_code)
afdoeningswijze_omschrijving
label 'Afdoeningswijze'
from exceltable
( worksheet 'rapport'
passing file 'c:\temp\rapport.xlsx'
skip first 1 rows
columns orgaan_naam varchar2 position next
, afdoeningswijze_code varchar2 position next
) stat
left
outer
join municipalities@inmemorystorage mpy
on mpy.Title = stat.orgaan_naam
left
outer
join afdoeningswijze_codes@inmemorystorage ace
on ace.code = stat.afdoeningswijze_code
where stat.orgaan_naam is not null
--
-- Exclude non-municipalities.
--
and stat.orgaan_naam
not in
( 'Besturen van de rechtbanken'
, '(BsGW)'
, 'Belastingsamenwerking Gemeenten en Waterschappen'
, 'Belastingdienst (Ministerie van Financiën)'
, 'Besturen van de rechtbanken'
, 'Bonaire'
, 'BSGR Belastingsamenwerking Gouwe-Rijnland'
, 'De Friese Meren (per 1 juli 2015 De Fryske Marren)'
, 'Dienst Uitvoering Onderwijs (Ministerie van Onderwijs, Cultuur en Wetenschap)'
, 'Dienst wegverkeer'
, 'Gesloten Jeugdzorg'
, 'Groesbeek'
, 'Groningen'
, 'Het Hogeland'
, 'Niet bekend'
, 'Noord-Holland'
, 'Alle Gemeenten'
, 'Alle gemeenten'
, 'Overige diensten (algemeen) (Den Haag)'
, 'Overige diensten (algemeen) (Limburg)'
, 'Overige diensten (algemeen) (Oost Nederland)'
, 'Overige diensten (algemeen) (Rotterdam)'
, 'Saba'
, 'Sociale verzekeringsbank'
, 'Stadsbank Oost Nederland'
, 'Statia'
, 'Stichting Veilig Thuis'
, 'Súdwest Fryslân'
, 'Uitvoeringsinstituut werknemersverzekeringen'
, 'Veiligheidsregio Zaanstreek-Waterland'
, 'Waadhoeke is sinds 1 januari 2018 een Nederlandse gemeente, die ontstond uit de voormalige gemeenten Franekeradeel, het Bildt, Menaldumadeel en uit vier dorpen van de voormalige gemeente Littenseradee'
, 'Westerkwartier'
, '1'
)
The statistics can now be grouped per municipality and the (constant) count of inhabitants. A filter is added to skip low volume and small municipalities. Some municipalities might have entered or left the Nationale Ombudsman office for escalation of complaints; there is no pro rato calculation applied for this lacking data. Also, part of the complaints received might at time of data set being established (August 2019) not yet have entered an end state.
The resulting aggregated results are retrieved using:
Of the remaining 117 municipalities 78 had no complaint at all that entered into a phase that required a written document. The top scorers are displayed below:
But, given the low volume of complaints received only one written document suffices to reach the top of the charts, so it is not really a meaningful ranking. From the municipalities with a more significant number of complaints, Hilversum (22, 9%) and Apeldoorn (29, 7%) ranked high.
Although the analysis completed successfully, the limited data quality and low volume of registered complaints per municipality make it hard to establish solid facts and/or trends from the data.