Before the Dashboard - When the Data Starts Talking Back

24 Oct 2026

A Status Check on CoCT SR Layer

Disclaimer: This post is not to discredit datasets shared by The City of Cape Town but is a personal reflection on practical data quality issues that became an opportunity of learning on the subject.

I have always been intrigued by the City of Cape Town’s (CoCT) service request data. I perceive it to be a proxy of the municipality’s pulse. So I find myself returning to it. Equipped with a better data science toolbox, I set out to approach it programmatically and not by bruteforce nor rudiment.

I downloaded a snapshot of the City’s Service Request dataset covering January 2023 to October 2026. It contained more than 5.3 million rows. The first temptation was “What can I visualise?” But the fisrt question was “What does this data believe it is?”

The intention was to have a live tap to the source but, the endpoint was soon after discontinued and I had to work with the snapshot.

1. Puttting A Source Data Contract in Place

A pre-profiling step was putting in place a source data contract which recorded the CoCT ArcGIS layer URL, service item ID and layer type. The contract goes on to describe 14 expected fields and their roles:

The following fields were identified for key checks such as whether requests repeat, whether dates are plausible and whether coordinate values are complete and valid.

Profiling showed that Notification is repeated and as such could not be treated as a unique row key.

The contract established the expectations against which the data could be tested were it to be provisioned constantly. It is the dataset’s point of entry into the pipeline and does not assume that the data is clean.

2. Profiling 5.3 million service-request records

A copy of the data was profiled by measuring:

• Total rows
• Distinct business keys
• Nulls and blanks
• Date ranges
• Distinct values
• Repeated-key rates
• Coordinate completeness and
• Rows by time period.

The following were some key findings:

THE BUSINESS KEY

1. The snapshot contained 5,372,466 rows. Notification was not unique and 709,092 request numbers appeared more than once, producing 946,523 extra rows, approximately 17.6% of the table.

A row is therefore not necessarily a request. Counting rows directly would overstate totals.

1. 566,194 ‘Notification’ values were exact copies apart from ‘ObjectId’. Another 142,898 repeated values differed in at least one field, commonly Ward, Coordinates, Dates, Sub_Council, Notification_type or Suburb.

As such, The data rejected a simple rule to “keep the latest row” - 7,815 requests had both open and closed rows and 107,994 repeated groups had tied latest timestamps with different content.

TEXT AS DISTRIBUTIONS

For each text field, record raw distinct values, values after trimming and casenormalisation, nulls, blanks, singleton values and coverage of the top values.

C3_Complaint_Type behaved like a controlled category: 1,154 raw values and 85.3% of rows covered by the top 100. Notification_type had 1,630,557 raw values and 1,308,510 singletons.

After removing embedded dates and long reference numbers, Notification_type fell to 352,024 distinct values and 90.0% of rows belonged to values seen at least ten times.

PLACEHOLDERS AND MISSINGNESS

The data uses #, 0, 000, MUL, NUL, Not assigned, blanks and other sentinel values. As a highlight:

Approximately 37% of Ward rows had no usable value and about 39% of Sub_Council rows had no usable value. Suburb used # for 8.0% of rows in addition to nulls and blanks.

COORDINATE ISSUES - COMPLETENESS VS VALIDITY

A populated coordinate was not automatically a valid location. So I had to test whether values are non-zero, numerically plausible, in a recognised coordinate layout, convertible to latitude and longitude and inside or near the metro boundary.

Some had to be completely rejected and a decision had to be made for downstream processing whether to use a generalised location such as Suburb or Ward. It turned out also that two coordinate systems where in use, hinting of diseparate originating systems.

TEMPORAL COVERAGE

Also checked monthly volumes, gaps, future or implausible dates, created versus changed versus completed dates and repeated requests with conflicting dates.

Whole months where found to be missing in some instances. For example request from February 2025 are missing entirely. Hinting on failed copy/ update operation.

3. The Rulebook

Profiling told what was happening and brought to bear all the data issues. A rulebook was then necessary to record decisions I made about it for the benefit of subsequent data handling steps.

Every rule had to have an ID, the rule itself, its status and the setting controlling it.

A Rulebook extract dealing with the handling of Suburb data.

Rulebook Extract

4. A Data Quality Report

The data-quality report here turns the profiling results into decisions. It shows what was checked, what was found, why it matters and what happens next.

Snapshot: 7 October 2026
Rows profiled: 5,372,466

Summarily:

Overally the data is judged as WARN(ING). It means that the data requires documented treatment before it can support trustworthy metrics, i.e. should not be sent directly to a dashboard without deduplication, standardisation and validation

The most important risk is duplication. Because Notification repeats, the dataset contains more rows than requests. A chart showing “number of service requests” would be misleading if it simply counted records.

The second major issue is geographic uncertainty. Missing values are represented in several ways, and coordinates are incomplete. A map therefore needs both valid-location logic and an explicit way to show requests that cannot be placed confidently.

In Conclusion

The profiling produced a useful ‘map of uncertainty.’ I learned that repeated rows were not necessarily duplicate requests; missingness was encoded as more than nulls; one field mixed categories, dates, identifiers and free text and coordinates could be present without being trustworthy.

Most importantly, each discovery became a rule, a test or another question to be answered. The profiling did not make the data look clean. It is made assumptions visible before going to the dashboard.

How CS50 Propped My Studies in Data Science

20 Mar 2026

A Courtesy email from GitHub

CS50 is in the rear view mirror now. It’s been almost one and half months since I started my formal studies and the lessons I picked from Dr Malan’s class have come in handy. This post is a reflection on how CS50 set me up to do well as I started to study. I will focus on four things.

1. A Development Environment

It was a no brainer to find a codespace simliar to the CS50’s development enironment to use for my task assignments. I quickly grabbed a ready-to-go Codespace, with R as a language of choice. Running the codespace from a GitHub repository was intuitive. This gave an environment that I was familiar with and I could focus on the task at hand and not environment tweaking and installation headaches. I did not even consider having a local dev environment.

Use of Visual Studio and the Terminal were familiar territory. The Codespace I chose has the R IDE, R Studio but, I found myself using that less, preffering rather to stay at the terminal. CS50 taught me enough commands and directory navigation to be productive.

2. The Power of Pseudocode

Breaking the assignment task into parts and using that as pseudocode is a useful skill I picked up in CS50. This programmatic thinking helps simplify the problem into digestible chunks and functions are easy to develop and the bigger picture easy to conceive. A block at a time, I can come up with the solution, without being overwhelmed by the entire elephant. This pseudo coding also enhance code documentation. It is easy to track what I need to do.

3. Learning A ‘New’ Language

In CS50 I touched C, Python, some JS, Flask and HTML. For my studies however I settled on R, a language I had spend sometime with. CS50’s philosophy was to instruct enough such that one can teach themselves any other programming language. The fundamentals remain the same, just that the syntax has changed.

4. Debugging and AI Coding Tools

Introduction to Computer Science, gave an environment with some training wheels. With my new setup, I have the liberty to extend functionality by including code-autocompete, utilise Copilot to explain code and assist in refining implementations. The goal now is not to learn programming but to impement good algorithms and solve problems.

This is not all I have benefited, I got enough grounding to take off this journey and now I focus on thinking as a Data Scientist and mastering the necessary tools. I’m spending some time with code:

Some Time With Code

Week 8, 9 and Te..: WebTech for The Spatial Scientist.

14 Jan 2026

From Week 8, work and other pressing issues broke my week-on-week streak! Necessary learnings where gotten nonetheless. With a planned more intense study schedule, I will make use of “work-family-study-time balance” lessons learnt when the studies assume.

A Modal Pop-up - JS/ CSS

Content in Week 8 was not too foreign. Being the network administrator of my fibre to the home network, lessons on TCP/IP was familiar ground. I knew enough of how traffic traverses the web - my home network is customised with a PiHole and has network wide DNS filtering.

Having setup my blogging site in 2015 from scratch and through several iterations, writing HTML code felt at home. I have in my portfolio, some web-mapping projects which leverage HTML. The twist came when it was time to delve deeper in Java Script. Earlier learning through the course drove home the fact that the course is really about learning to learn new languages more quickly and easily.

In the years past I have fiddled with the LAMP Stack. Experiencing Flask in Week 9 was a breath of fresh air. Quickly seeing the results of one’s development efforts. Early on, it was not easy untangling the interlinked parts (documents) of a Flask app. But once understood, it makes sense as the power of templating and frameworks is exposed. It becomes rather convenient.

A Flask App. Running

Meeting The Objective

One of the objectives of this blog series was to:

• Have a fresh and solid base for when starting the PGDip studies

As the deadline for application for the PG Diploma drew close. I pushed to get across the completion line of CS50. There just wasn’t enough time to finish Week 10, The Project. I successfully completed Week 0 - 9 with the required assignment passmarks.

I used the tasks transcript to ‘motivate’ for my ‘Computer Science Course equivalent to a University First year course. It turned out I didn’t need to take this course! 20 years ago I did Computer Science for Engineers for my BSc Studies and that was sufficient. Nothing though replaces the 6 months of refreshing my knowledge, un-learning, re-learning and cementing current knowings though, before 31 October 2025.

With time, I must still do the Final Project to get the Harvard Certificate. ..before then, let me drop the morphed on-wall study schedule.

Personal CS50 Schedule

Week 7: Reflecting on CS50’s SQL as a Spatial Scientist.

05 Sep 2025

As opportunity would have it, this week I was on a work sponsored esri training course, ArcPro Advanced. One of the topics covered was Simple SQL Queries. CS50’s SQL Topic became an opportunity to re-learn SQL, dive deeper and master the proper crafting of queries.

The first part turned out to be a great easy-going refresher. As a spatial scientist I ask spatial data questions all the time. As geohipsters, we actually have a cute file format, geopackage which is essentially a sqlite database. In the course of the material. I couldn’t help reminisce PostGIS and SpatialSQL. I have a decent exposure to SQL but, further on with Week 7 content, queries became complex and my learning began to compound.

I noticed a personal preferance when it came to constructing queries, favouring

JOIN ... ON ... ---Inner Joins  

versus

SELECT ... FROM ( SELECT ... ---Subqueries/ nested SELECT statements  

Which was an interesting introversion of how I understood things. At the end of it, it was great mastering the alternative.

The brief discussion of SQL Injection attacks during lectue was very intriguing. A peek into cybersecurity.

Learnings for the geo-person

1. Geospatial software leverage relational databases ubiquitously. As such, the topic of databases is one that cannot be side stepped in the profession. It is one that can actually be divergent, as some projects demand tools which emphasise data storage versus a spatial view. Few years back I was intrigued by a command line spatial data viewer which connected to PostGRES with a PostGIS extension. Not long DBeaver, a universal databae tool, debuted with a spatial viewer.
   Something that was domain specific to GIS.

2. A great geospatial scientist should know above average SQL. Increasingly, it is apparent that spatial is just another column in the database. But the gis tech, has the advantage of spatial thinking.

3. After completing this section, I can now do nested and more complex queries. Behind every icon in the spatial manipulation software GUI. The ready to use Intersect icon, is actually a

        SELECT spatial_features FROM spatial_table_A  
            JOIN spatial_table_B  
            ON spatially_congruent_features
   

4. My greatest gain from this section was learning to work with database informaton programmatically. This came through the Fiftyville task of Problem Set 7.

5. In all honesty, I underestimated the depth of topic for this section. It was a great break from general purpose programming and I learnt much much more on SQL. I now appreciate better, the responsibilities of a DBA. The prospects of the average geospatial scientist expanding into webmapping and scripting become greater.

That’s Week 7, now looking forward to HTML, CSS and JavaScript. Again topics not too foreign but another opportunity to re-learn stuff. As a parting note…Little bobby tables :laughing: .

`Robert'); DROP TABLE Students;--`?

Week 6: Learning a Programming Language, Again! From C to Python, Quickly.

19 Aug 2025

As a spatial scientist, starting on Python was particularly interesting. The language is largely preferred for spatial data manipulation operations. At this point, I hadn’t delved Python to any great depth except encountering it in scripts at my 9 - 5 job, in esri software.

In this week’s Lecture, Shot and Section, Python is really dealt like rock skipping, touching the surface but, never sinking to depth. It was really about “How to Teach Yourself a New Programming Language”. Even with the Problem Set, the same was reiterated.

If I should gripe a little, every time the codespace had to be updated, I lost my theme - Cobalt 2. It however was not a pain getting things back to the way they were.

Codespace My Way - A Dark Mode

Learning a New Programming Language

It was not a struggle following highlights of differences between C and Python. What I had to contend with was ‘throwing away’ all the attention to detail and ‘pseudo-control’ over what the code was doing. Python is high level and I had to actively ignore the under the hood stuff I knew coming from C and ‘trust’ the computer to do the right thing. The compactness of Python code was a breath of fresh air!

Because with Python indentation matters, it sort of forced one to create clean code from the start. The CS50 coding environment screamed Problem, when a (tab) was missing.

After this brief run with Python, I found myself also asking the popular question Python or R. Because of the time I have invested in R for Data Science, I have developed some fondness for R. Curious about what the future holds.

Revisiting Code

I grappled briefly converting my for loops from C to Python (sentimental-mario-less). But because I understood my code, with a little experimentation, I eventually got the code to work.

In one of the problems, I found myself using Debug50 to trace variable changes. The problem brief from C had been twisted from dealing with integers to now dealing with floating point numbers in Python. It was exciting experiencing first hand float point precision! The computer was working with 0.009999999999999967 whereas my expectation was 0.01. As a consequence, my function returned 0.0. With some research I employed round(change,2) which solved the problem. A successful debug, always scratches the inquirer’s itch.

I also found a bug in my older code which I had inadvertently gotten away with. It was with a word counting function. Occurrence of ‘.’ or space indicated end of a word but hadn’t factored where they occurred together viz. (dot)(space). I therefore ended up with an inflated word count.

Coding fatigue is a real thing. Even after six weeks of coding, tiny errors are still possible and fundamentals of debugging still have to be observed. This was after my program just hung on execution because I had missed code to increment a counter.

Reflections

Again I learn that every problem at first glance looks insurmountable. The problem set on DNA, felt overwhelmed on first encounter. But by applying modular programming, sub-tasking and pseudo-coding, I was soon on my way disintegrating it piece by piece.

I am looking forward to SQL and having to take a break from general purpose programming languages. The week-to-week tempo I adopted in the face of limited time makes everything feel like a rat race, albeit exciting, in the face of a weekend deadline.

Let me put an end of post bar hereunder in the form of a progress bar, which surprisingly is blue from red at the moment.

The Gradebook - Blue Zone Progress