The Deceivingly Easy Part

The only problem was that I didn’t know how to process the data easily as the entire thing was beyond what my laptop were able to. Fortunately, Google BigQuery came to the rescue! Something like:

SELECT KELURAHAN, TEMPATLAHIR, COUNT(*) AS CNT FROM DPTS GROUP BY KELURAHAN, TEMPATLAHIR WHERE PROVINSI = 'DKI JAKARTA'

gave us something like this under 15 seconds:

I don’t want to disclose how many times I screwed up the SQL query

As with any data exploration exercise, though, it’s never that easy. Fire up Rstudio or ipython notebook and you see there’s a problem on how many unique entries on the birthplace column:

For the purpose of this post, it looks fancier than R

120,000 divided for 5 parts of Jakarta should ideally yield ~24,000 unique hometown name instead of almost 80,000. The entire Indonesia only has 32 provinces, roughly 500 districts, and 80,000 villages. The data itself is sourced from the National ID database which is supposed to be the answer of Life, the Universe, and Everything. They used super expensive computerized system to build the database of every Indonesian ever in the whole Milky Way. It can’t be wrong.

But I can be wrong about them.

Sadly, opening the dataset in Open Refine and running the clustering function will give you bad, bad news. I can almost hear the program whispers softly in my ear, “I know how it feels. There’s nothing you can do. Just remember it’s part of the Plan.”

What you see, dear good people, is one of 22,000+ clusters of similarly spelled places. The typos are real. I actually checked several dozen of them in the Election Commission website just to make sure it’s not my scraper acting funny. Somewhere, somehow, an actual public official entered JQKARTA, JUAKARTA, JAKARTAT, or one of the many variation above instead of Jakarta in a citizen National ID as his/her birthplace.

You would have thought they actually have dropdown menu in the system instead of a textbox. Come on. It’s goddamn Jakarta, not a random marker on Google Map. It’s been there and called by that name for 50 YEARS.

Even worse, what constitutes of a birthplace is not always a city. The value actually ranges from the name of a village (without any other geographic indicator) to a country. Even if it’s spelled correctly, it might also spelled differently (Singapura instead of Singapore), shortened (Tanjung becomes Tjg or Tj), or ambiguous (is it X the village, the city, or the subdistrict? the one in that province or the other one?).

all hail r/HighQualityGifs

While the data is free, my time isn’t.