reedho / pemilu2024

pemilu2024

TLDR;

Source data is taken from this xlsx file provided by https://data-pemilu.vercel.app/.

As of 15-Feb-2024 15:49 WIB, the exported parquet file size is 85MB. It is desirable to provide diff for subsequent update cycle.

All of the generated (manually, for now) files are below:

File Name	Size	Created At	Info
./ppwp_tps.csv.bz	41M	12:55 PM Feb 15	Initial scrapping result, taken from the xlsx above
./ppwp_tps.parquet	85M	05:28 PM Feb 15	Initial csv -> parquet
./ppwp_tps__001.parquet	17M	10:54 PM Feb 15	Update 1: updated_at > 2024-02-14 20:24:41.538
./ppwp_tps__002.parquet	2.9M	09:49 AM Feb 16	Update 2: updated_at > 2024-02-15 14:39:15.109

---

Data from new upstream servers for now logged in here.

NOTES

Parquet files was generated using duckdb, more or less like so:

--- convert the xlsx file to csv
--- e.g. in this case the exported file is 'ppwp_tps.csv'

# --- create table t0 schema
create table t0 (
    kode varchar primary key,
    provinsi_kode varchar,
    kabupaten_kota_kode varchar,
    kecamatan_kode varchar,
    kelurahan_desa_kode varchar,
    tps varchar,
    suara_paslon_1 integer,
    suara_paslon_2 integer,
    suara_paslon_3 integer,
    chasil_hal_1 varchar,
    chasil_hal_2 varchar,
    chasil_hal_3 varchar,
    suara_sah integer,
    suara_total integer,
    pemilih_dpt_j integer,
    pemilih_dpt_l integer,
    pemilih_dpt_p integer,
    pengguna_dpt_j integer,
    pengguna_dpt_l integer,
    pengguna_dpt_p integer,
    pengguna_dptb_j integer,
    pengguna_dptb_l integer,
    pengguna_dptb_p integer,
    suara_tidak_sah integer,
    pengguna_total_j integer,
    pengguna_total_l integer,
    pengguna_total_p integer,
    pengguna_non_dpt_j integer,
    pengguna_non_dpt_l integer,
    pengguna_non_dpt_p integer,
    psu varchar,
    ts timestamp,
    status_suara boolean,
    status_adm boolean,
    updated_at timestamp,
    created_at timestamp,
    url_page varchar,
    provinsi_nama varchar,
    kabupaten_kota_nama varchar,
    kecamatan_nama varchar,
    kelurahan_desa_nama varchar,
    url_api varchar
);

copy t0 from 'ppwp_tps.csv' with (header true, delimiter '\t', auto_detect false);

--- export to parquet
copy t0 to 'ppwp_tps.parquet' (format parquet);

Subsequent updates will be created as follow:

attach 'dbname=pemilu_2024 user=pantau password=ZubQ7IHFn1sTCP8C8rgw3T24QIiJktb8 host=vps.zakiego.com port=54325' as source_db (type postgres);

--- find the max `updated_at` from prev table
select max(updated_at) from t0;

--- create table t1 from querying source_db where updated_at > t0 max updated_at
create table t1 as select * from source_db.ppwp_tps where updated_at > (select max(updated_at) from t0);

--- export to parquet
copy t1 to 'ppwp_tps__001.parquet' (format parquet);

That's all for now. Contributions, both small and large, are welcome.

---

Pemilu 2024 Parquet File by Ridho is marked with CC0 1.0 Universal