0_Data_Preprocessing.ipynb

{
  "cells": [
    {
      "attachments": {},
      "cell_type": "markdown",
      "metadata": {
        "id": "WohOGpEFS1UV"
      },
      "source": [
        "# Install Library yang diperlukan"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 523
        },
        "id": "Y6j-9fvuSkK_",
        "outputId": "da1af056-02b8-4c9e-82d4-82ed40e1576a"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "Looking in indexes: https://pypi.org/simple, https://us-python.pkg.dev/colab-wheels/public/simple/\n",
            "Collecting pdfplumber\n",
            "  Downloading pdfplumber-0.9.0-py3-none-any.whl (46 kB)\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m46.1/46.1 kB\u001b[0m \u001b[31m2.5 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[?25hCollecting pdfminer.six==20221105 (from pdfplumber)\n",
            "  Downloading pdfminer.six-20221105-py3-none-any.whl (5.6 MB)\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m5.6/5.6 MB\u001b[0m \u001b[31m44.4 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[?25hCollecting Pillow>=9.1 (from pdfplumber)\n",
            "  Downloading Pillow-9.5.0-cp310-cp310-manylinux_2_28_x86_64.whl (3.4 MB)\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m3.4/3.4 MB\u001b[0m \u001b[31m73.6 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[?25hCollecting Wand>=0.6.10 (from pdfplumber)\n",
            "  Downloading Wand-0.6.11-py2.py3-none-any.whl (143 kB)\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m143.6/143.6 kB\u001b[0m \u001b[31m15.6 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[?25hRequirement already satisfied: charset-normalizer>=2.0.0 in /usr/local/lib/python3.10/dist-packages (from pdfminer.six==20221105->pdfplumber) (2.0.12)\n",
            "Requirement already satisfied: cryptography>=36.0.0 in /usr/local/lib/python3.10/dist-packages (from pdfminer.six==20221105->pdfplumber) (40.0.2)\n",
            "Requirement already satisfied: cffi>=1.12 in /usr/local/lib/python3.10/dist-packages (from cryptography>=36.0.0->pdfminer.six==20221105->pdfplumber) (1.15.1)\n",
            "Requirement already satisfied: pycparser in /usr/local/lib/python3.10/dist-packages (from cffi>=1.12->cryptography>=36.0.0->pdfminer.six==20221105->pdfplumber) (2.21)\n",
            "Installing collected packages: Wand, Pillow, pdfminer.six, pdfplumber\n",
            "  Attempting uninstall: Pillow\n",
            "    Found existing installation: Pillow 8.4.0\n",
            "    Uninstalling Pillow-8.4.0:\n",
            "      Successfully uninstalled Pillow-8.4.0\n",
            "Successfully installed Pillow-9.5.0 Wand-0.6.11 pdfminer.six-20221105 pdfplumber-0.9.0\n"
          ]
        },
        {
          "data": {
            "application/vnd.colab-display-data+json": {
              "pip_warning": {
                "packages": [
                  "PIL"
                ]
              }
            }
          },
          "metadata": {},
          "output_type": "display_data"
        }
      ],
      "source": [
        "!pip install pdfplumber"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "cQbUX5WeSzvb",
        "outputId": "ec087108-beea-4f3d-8881-977d1b1faa23"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "Mounted at /content/drive\n"
          ]
        }
      ],
      "source": [
        "from google.colab import drive\n",
        "drive.mount('/content/drive')"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "tJc7UodVTRSO"
      },
      "outputs": [],
      "source": [
        "import os\n",
        "os.chdir('/content/drive/MyDrive/_Tetris')"
      ]
    },
    {
      "attachments": {},
      "cell_type": "markdown",
      "metadata": {
        "id": "2HS4kx24TJTr"
      },
      "source": [
        "Ini adalah script untuk mengubah pdf lampiran hasil CPNS jadi csv\n",
        "Caveat: kalo langsung prosess 20rb halaman, agak keselek di tengah, jadi mending di-iterate beberapa halaman\n",
        "Saya yakin ada cara yang lebih elegan. Feel free to give me any suggestions\n",
        "\n",
        "Cara run </br>\n",
        "python export_data_to_csv.py index_halaman_start index_halaman_end\n",
        "\n",
        "Misal </br>\n",
        "python export_data_to_csv.py 0 100"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "3FhahzICTXML"
      },
      "outputs": [],
      "source": [
        "# import library\n",
        "import pdfplumber\n",
        "import pandas as pd\n",
        "import datetime as dt\n",
        "import sys"
      ]
    },
    {
      "attachments": {},
      "cell_type": "markdown",
      "metadata": {
        "id": "KFeFGOeKysGJ"
      },
      "source": [
        "# Data Tahap 1"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "kVU9RzyOSTCo"
      },
      "outputs": [],
      "source": [
        "def check_for_jabatan(page):\n",
        "    '''\n",
        "    Fungsi untuk mengecek keberadaan informasi jurusan pilihan\n",
        "    Input: page (ex: pdf.pages[0])\n",
        "    Output: \n",
        "        - dicitionary berisi informasi jurusan pilihan\n",
        "    '''\n",
        "    if \"Lokasi Formasi :\" in page.extract_text():\n",
        "        text = pg.extract_text()\n",
        "        pendidikan = pg.extract_tables()[1][0][1]\n",
        "        jurusan_strings = text.split(\"Jurusan : \")[1].split(\"Lokasi\")[0]\n",
        "        lokasi_front = \"\"\n",
        "        jurusan = jurusan_strings.split(\"\\n\")[0]\n",
        "\n",
        "        lokasi_string = text.split(\"Lokasi Formasi : \")[1].split(\"Jenis\")[0]\n",
        "\n",
        "        return {\n",
        "            \"kode_jurusan\": jurusan.split(\" - \")[0],\n",
        "            \"jurusan\": jurusan.split(\" - \")[1],\n",
        "            \"kode_lokasi\": lokasi_string.split(\" - \")[0],\n",
        "            \"lokasi_formasi\": lokasi_string.split(\" - \")[1].split(\"\\n\")[0],\n",
        "            \"pendidikan\": pendidikan.replace(\"\\n\", \" \")\n",
        "        }\n",
        "    else:\n",
        "        return {}\n",
        "\n",
        "def check_for_detail_tables(page):\n",
        "    '''\n",
        "    Fungsi untuk mengecek keberadaan tabel perorangan\n",
        "    Input: page (ex: pdf.pages[0])\n",
        "    Output: \n",
        "        - found (binary, iya tidaknya sebuah halaman punya tabel perorangan)\n",
        "        - df_returned (tabel perorangan jika ada)\n",
        "    '''\n",
        "    found = False\n",
        "    df_returned = pd.DataFrame()\n",
        "    for table in pg.extract_tables():\n",
        "        df = pd.DataFrame(table)\n",
        "\n",
        "        if (df.shape[1] == 9): #jumlah kolom = 9 \n",
        "            found = True\n",
        "            df_returned = df\n",
        "    return found, df_returned\n",
        "\n",
        "\n",
        "def split_df(df_):\n",
        "    '''\n",
        "    Fungsi untuk split tabel perorangan. Kadang ada satu halaman dengan lebih dari satu tabel.\n",
        "    Input: df_ (dataframe tabel perorangan)\n",
        "    Output: \n",
        "        - list berisi beberapa dataframe untuk tiap individu\n",
        "    '''\n",
        "    dfs = []\n",
        "    header_indexes = list(df_[df_[1]==\"No Peserta\"].index)\n",
        "    header_indexes.append(len(df_))\n",
        "    for i in range(len(header_indexes)-1):\n",
        "        splitted_df = df_.iloc[header_indexes[i]: header_indexes[i+1], :]\n",
        "        splitted_df.index = range(len(splitted_df))\n",
        "        dfs.append(splitted_df)\n",
        "    return dfs\n",
        "\n",
        "def get_info_from_table(df_, idx):\n",
        "    '''\n",
        "    Fungsi untuk mengekstrak informasi dari tabel perorangan \n",
        "    Input: df_ (dataframe tabel perorangan)\n",
        "    Output: \n",
        "        - dicitionary berisi informasi perorangan yang telah diekstrak\n",
        "    '''\n",
        "    base_data =  {\n",
        "        \"no_peserta\": df_.iloc[idx,1],\n",
        "        \"kode_pendidikan\": df_.iloc[idx,2],\n",
        "        \"total_twk\": df_.iloc[idx,4],\n",
        "        \"total_tiu\": df_.iloc[idx,5],\n",
        "        \"total_tkp\": df_.iloc[idx,6],\n",
        "        \"total_skd\": df_.iloc[idx,7],\n",
        "        \"keterangan\": df_.iloc[idx,8]\n",
        "    }\n",
        "\n",
        "    return {**base_data}"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "O6G0bH8zSeRn"
      },
      "outputs": [],
      "source": [
        "start_index = 0\n",
        "end_index = 929\n",
        "export_filename = \"spmb_\"+str(start_index)+\"_\"+str(end_index)+\"_\"+str(dt.datetime.now())+\".csv\"\n",
        "pdf = pdfplumber.open('datasets/Lampiran B-099.02720.06.2022 Pengumuman Hasil Tahap 1 Tahun 2022.pdf')\n",
        "\n",
        "# start iterating\n",
        "result = []\n",
        "last_jabatan = {}\n",
        "\n",
        "start_time = dt.datetime.now()\n",
        "for i in range(start_index, end_index):\n",
        "    pg = pdf.pages[i]\n",
        "\n",
        "    # jika halaman tsb ada info tentang lowongan, simpan\n",
        "    current_jabatan = check_for_jabatan(pg)\n",
        "    if current_jabatan != {}:\n",
        "        last_jabatan = current_jabatan\n",
        "    \n",
        "    is_detail_found, detail_df = check_for_detail_tables(pg)\n",
        "    # jika ketemu ada tabel perorangan\n",
        "    if is_detail_found:\n",
        "        splitted_df = split_df(detail_df)\n",
        "        for df_ in splitted_df:\n",
        "            for i in range(2, len(df_)):\n",
        "                details = get_info_from_table(df_, i)\n",
        "                if current_jabatan == {}:\n",
        "                    # kalo ada info lowongan di halaman yang sama, pakai info lowongan tsb\n",
        "                    details.update(last_jabatan)\n",
        "                else:\n",
        "                    # kalo ga, pake info lowongan terakhir\n",
        "                    details.update(current_jabatan)\n",
        "                    last_jabatan = current_jabatan\n",
        "                result.append(details)\n",
        "\n",
        "    # untuk logging\n",
        "    if i%100 == 99:\n",
        "        curr_time = dt.datetime.now()\n",
        "        print(\"Done for \"+str(i), curr_time-start_time)\n",
        "        start_time = dt.datetime.now()\n",
        "\n",
        "res = pd.DataFrame(result)\n",
        "res.to_csv(export_filename)"
      ]
    }
  ],
  "metadata": {
    "colab": {
      "provenance": []
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}