Database,  Programming

Mengolah data APBD DKI

Berawal kekecewaan terhadap data.go.id, saya nyari-nyari data tentang DKI, khususnya tentang APBD, ketemu  di Portal Data Pemda DKI. Dan langsung saya download: 21,5 MB CSV tanpa kompresi.

Langkah pertama: buka dengan text viewer. LTF Viewer adalah program andalan saya untuk melihat data text besar, jangankan yang puluhan MB, data text ratusan MB aja dengan cepat bisa dibuka. Setelah dibuka terlihat bahwa data csv terdiri dari 55.467 baris (termasuk header di baris pertama).

LTF Viewer. Open big text file in less than one second.

Langkah kedua: coba lihat dengan pivot viewer. Dengan berbekal mesin pencari google, saya dapat program pivot viewer bernama Tad. Gampang banget makenya, langsung buka file csv, abis itu tinggal klik-klik aja nentuin urutan field pivotnya.

Pivot view dengan Tad.

Langkah ketiga: convert menjadi SQLite. Saya menggunakan program sqlite3 di Ubuntu yang jalan di Windows 10 (WSL: Windows Subsystem for Linux). Cepet banget konversinya.

Ubuntu @ Windows Subsystem for Linux

Data format csv bisa dibilang format universal untuk sharing data, selain csv ada json dan xml, tapi untuk kemudahan dan kepraktisan, gak ada yang ngalahin csv. Format csv memang mudah untuk dishare (portable) tetapi susah untuk diolah, jadi lebih baik untuk diconvert ke SQLite. 

Langkah keempat: normalisasi. Memang data hasil convert dari csv sudah bisa langsung diolah, tapi saya mau data terstruktur biar gampang buat user interfacenya. Tabel hasil konversi otomatis nanti berubah, tapi kita bisa membuat view untuk kebutuhan pivot table. Untuk mengolah database SQLite saya pakai: SQLiteStudio (Free and OpenSource).

Program favorit untuk mengolah data SQLite.

Mudahnya menggunakan SQLite, karena struktur table di SQLite tidak mengenal tipe data, jadi pada saat import kita tidak perlu buat tabel dengan struktur data yang pas. Kalau pake MySQL atau yang lainnya, tiap field harus pas tipe datanya, bahkan untuk varchar harus didefinisikan berapa panjang maksimal datanya. Kalau panjang maksimalnya kurang dari data yang mau diinput akan menimbulkan error dan data gagal diinsert.

This is a long journey, brace yourself and click “Read More”

— Blog writer —

Mulai normalisasi dengan melihat fieldnya secara umum. Sebelum mulai normalisasi, nama tablenya saya ganti dulu dari apbd_dki_2018 jadi dki saja. Saya langsung aja pakai perintah select * from dki; dan melihat field kedua dan ketiga bernama kode_urusan_program dan nama_urusan_program. Ini dia target normalisasi pertama.

Langsung tampilkan data unik dari dua field itu dengan perintah select distinct kode_urusan_program, nama_urusan_program from dki order by kode_urusan_program; hasilnya: berantakan. Ada beberapa data mempunyai kode yang sama tetapi namanya berbeda.

Data unik urusan program.

Kode 1.03 bernama “Pekerjaan Umum dan Penataan Ruang” tapi ada lagi dengan kode yang sama 1.03 juga tetapi dengan nama “Pekerjaan Umum” saja, dan ada di baris ke-8 dengan kode 1.05 bernama “Penataan Ruang”. Kesalahan data seperti ini memang sering terjadi.

Langkah normalisasinya: buat table referensi, isi table referensi, buat field referensi di table dki, isi fieldnya berdasarkan table referensi, buang field kode_urusan_program dan nama_urusan_program di table dki.

-- buat table referensi
create table urusan_program (id integer primary key, kode text, nama text);
    
-- isi table referensi
insert into urusan_program (kode, nama) 
select distinct kode_urusan_program, nama_urusan_program from dki order by kode_urusan_program;

-- buat field referensi di table dki
alter table dki add column urusan_program_id INTEGER;

-- isi field berdasarkan table referensi
;WITH a AS (SELECT id, kode, nama FROM urusan_program)
UPDATE dki
SET urusan_program_id=(SELECT id FROM a where a.kode = dki.kode_urusan_program and a.nama = dki.nama_urusan_program);

Karena SQLite tidak mempunyai perintah alter table drop column..., maka penghapusan dua field dilakukan dengan GUI dari SQLiteStudio. Tinggal dobel-klik tablenya, pilih field yang mau dihapus, tekan tombol ‘Del’, pilih tombol “Yes” untuk konfirmasi penghapusan, lakukan untuk dua field yang disebut di atas, lalu tekan tombol “Commit”.

Lakukan langkah-langkah normalisasi untuk semua field yang bersifat referensi. Untuk lebih menghemat waktu, penghapusan field bisa dilakukan terakhir secara sekaligus.

Saya tidak akan menerangkan secara detail langkah-langkahnya karena secara umum sama saja. Yang perlu diperhatikan adalah perintah SQL di atas ada yang khusus untuk SQLite, jadi untuk database lain mungkin berbeda caranya.

Karena jam pada komputer saya sudah menunjukkan jam 1:36 dini hari, maka tulisan ini untuk sementara dihentikan, akan dilanjutkan nanti. Keep watching.