Skip to content

Instantly share code, notes, and snippets.

@taruma

taruma/taruma_lampiran_li05a_transfer_data_excel.ipynb

Created March 29, 2022 03:10
Show Gist options
  • Save taruma/653e8ec956bde7b2f4ba230ceecd8b3d to your computer and use it in GitHub Desktop.
Save taruma/653e8ec956bde7b2f4ba230ceecd8b3d to your computer and use it in GitHub Desktop.
Download ZIP
taruma_lampiran_li05A_transfer_data_excel.ipynb
Display the source blob
Display the rendered blob
Raw
taruma_lampiran_li05a_transfer_data_excel.ipynb
{
"nbformat": 4,
"nbformat_minor": 0,
"metadata": {
"colab": {
"name": "taruma_lampiran_li05A_transfer_data_excel.ipynb",
"provenance": [],
"collapsed_sections": [],
"authorship_tag": "ABX9TyMTebg/csxg0VKuk6oIB7vL",
"include_colab_link": true
},
"kernelspec": {
"name": "python3",
"display_name": "Python 3"
},
"language_info": {
"name": "python"
}
},
"cells": [
{
"cell_type": "markdown",
"metadata": {
"id": "view-in-github",
"colab_type": "text"
},
"source": [
"<a href=\"https://colab.research.google.com/gist/taruma/653e8ec956bde7b2f4ba230ceecd8b3d/taruma_lampiran_li05a_transfer_data_excel.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
]
},
{
"cell_type": "markdown",
"source": [
"# LAMPIRAN LI-05-A TRANSFER DATA EXCEL\n",
"\n",
"Isi Lampiran: \n",
"\n",
"Memindahkan informasi/data dari excel ke excel lain. Mengekstrak data tahunan dari setiap tahun dan setiap excel ke dalam excel tunggal yang akan digunakan untuk pengolahan berikutnya. \n",
"\n",
"Catatan: Pengolahan menggunakan mesin lokal"
],
"metadata": {
"id": "FQ8m3iVLY-my"
}
},
{
"cell_type": "code",
"source": [
"# import library\n",
"\n",
"import xlwings as xw\n",
"import pandas as pd\n",
"import re\n",
"from pathlib import Path"
],
"metadata": {
"id": "ljQF8bydqO9h"
},
"execution_count": 52,
"outputs": []
},
{
"cell_type": "code",
"source": [
"ROOT_PATH = Path('./laporan_implementasi/li5')\n",
"dataset_path = ROOT_PATH / 'dataset'"
],
"metadata": {
"id": "zESC6kWeqloA"
},
"execution_count": 53,
"outputs": []
},
{
"cell_type": "code",
"source": [
"# melihat daftar file excel di dalam folder dataset\n",
"\n",
"dataset_excel_path = dataset_path.rglob('*.xls*') \n",
"dataset_excel_list = list(dataset_excel_path)\n",
"dataset_excel_list"
],
"metadata": {
"id": "m2arloGpIa_d",
"colab": {
"base_uri": "https://localhost:8080/"
},
"outputId": "5990faf9-1ccf-4476-ddc6-47c916bfe9e4"
},
"execution_count": 54,
"outputs": [
{
"output_type": "execute_result",
"data": {
"text/plain": [
"[WindowsPath('laporan_implementasi/li5/dataset/hk_daily_bandar_lampung.xlsx'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/hk_daily_bandar_lampung_filled.xlsx'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2009/DATA HUJAN PUBLIKASI 2009.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2010/ph 001.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2010/ph 003.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2010/ph 004.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2010/ph 005.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2011/PH.001 SUMUR BATU.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2011/PH.003 SUKA BUMI.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2011/PH.004 SUSUNAN BARU.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2011/PH.005 KEMILING.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2012/PH.001 TELUK BETUNG UTARA.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2012/PH.003 SUKARAME 2012.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2012/PH.004 SUMUR PUTRI 2012.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2012/PH.005 KEMILING 2012.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2013/PH.001 TELUK BETUNG UTARA.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2013/PH.003 SUKARAME.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2013/PH.004 SUMUR PUTRI.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2013/PH.005 KEMILING.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2014/PH.001 TELUK BETUNG UTARA 2014.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2014/PH.003 SUKARAME2014.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2014/PH.004 SUMUR PUTRI.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2014/PH.005 KEMILING.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2015/PH.001 TELUK BETUNG UTARA.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2015/PH.003 SUKARAME.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2015/PH.004 SUMUR PUTRI.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2015/PH.005 KEMILING.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2016/1. PH.001 TELUK BETUNG UTARA.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2016/2. PH.003 SUKARAME.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2016/3. PH.004 SUMUR PUTRI.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2016/4. PH.005 KEMILING.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2017/PH.001 TELUK BETUNG UTARA.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2017/PH.003 SUKARAME.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2017/PH.004 SUMUR PUTRI.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2017/PH.005 KEMILING.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2018/PH.001 TELUK BETUNG UTARA.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2018/PH.003 SUKARAME.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2018/PH.004 SUMUR PUTRI.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2018/PH.005 KEMILING.xls')]"
]
},
"metadata": {},
"execution_count": 54
}
]
},
{
"cell_type": "code",
"source": [
"# membaca daftar sheet untuk masing-masing berkas excel\n",
"\n",
"with xw.App(visible=False) as app:\n",
" for num, file in enumerate(dataset_excel_list, 1):\n",
" print(f'FILE {num}')\n",
" book = app.books.open(file)\n",
" print('membaca berkas: ', file.stem)\n",
" daftar_sheet = [_sheet.name for _sheet in book.sheets]\n",
" print('terdapat sheet: ', daftar_sheet)\n",
" book.close()\n",
" print('=======================')"
],
"metadata": {
"id": "QeXeTdS9rFZI",
"colab": {
"base_uri": "https://localhost:8080/"
},
"outputId": "893acb1c-82ae-4878-8300-82e9aa4d5c54"
},
"execution_count": 55,
"outputs": [
{
"output_type": "stream",
"name": "stdout",
"text": [
"FILE 1\n",
"membaca berkas: hk_daily_bandar_lampung\n",
"terdapat sheet: ['_INFO', '_TEMPLATE', 'PH001', 'PH003', 'PH004', 'PH005']\n",
"=======================\n",
"FILE 2\n",
"membaca berkas: hk_daily_bandar_lampung_filled\n",
"terdapat sheet: ['_INFO', '_TEMPLATE', 'PH001', 'PH003', 'PH004', 'PH005']\n",
"=======================\n",
"FILE 3\n",
"membaca berkas: DATA HUJAN PUBLIKASI 2009\n",
"terdapat sheet: ['PH.005', 'PH.004', 'PH.003', 'PH.001']\n",
"=======================\n",
"FILE 4\n",
"membaca berkas: ph 001\n",
"terdapat sheet: ['Data Stasiun']\n",
"=======================\n",
"FILE 5\n",
"membaca berkas: ph 003\n",
"terdapat sheet: ['Data Stasiun']\n",
"=======================\n",
"FILE 6\n",
"membaca berkas: ph 004\n",
"terdapat sheet: ['Data Stasiun']\n",
"=======================\n",
"FILE 7\n",
"membaca berkas: ph 005\n",
"terdapat sheet: ['Data Stasiun']\n",
"=======================\n",
"FILE 8\n",
"membaca berkas: PH.001 SUMUR BATU\n",
"terdapat sheet: ['Data Stasiun']\n",
"=======================\n",
"FILE 9\n",
"membaca berkas: PH.003 SUKA BUMI\n",
"terdapat sheet: ['Data Stasiun']\n",
"=======================\n",
"FILE 10\n",
"membaca berkas: PH.004 SUSUNAN BARU\n",
"terdapat sheet: ['Data Stasiun']\n",
"=======================\n",
"FILE 11\n",
"membaca berkas: PH.005 KEMILING\n",
"terdapat sheet: ['Data Stasiun']\n",
"=======================\n",
"FILE 12\n",
"membaca berkas: PH.001 TELUK BETUNG UTARA\n",
"terdapat sheet: ['Data Stasiun']\n",
"=======================\n",
"FILE 13\n",
"membaca berkas: PH.003 SUKARAME 2012\n",
"terdapat sheet: ['Data Stasiun']\n",
"=======================\n",
"FILE 14\n",
"membaca berkas: PH.004 SUMUR PUTRI 2012\n",
"terdapat sheet: ['Data Stasiun']\n",
"=======================\n",
"FILE 15\n",
"membaca berkas: PH.005 KEMILING 2012\n",
"terdapat sheet: ['Data Stasiun']\n",
"=======================\n",
"FILE 16\n",
"membaca berkas: PH.001 TELUK BETUNG UTARA\n",
"terdapat sheet: ['Data Stasiun']\n",
"=======================\n",
"FILE 17\n",
"membaca berkas: PH.003 SUKARAME\n",
"terdapat sheet: ['Data Stasiun']\n",
"=======================\n",
"FILE 18\n",
"membaca berkas: PH.004 SUMUR PUTRI\n",
"terdapat sheet: ['Data Stasiun']\n",
"=======================\n",
"FILE 19\n",
"membaca berkas: PH.005 KEMILING\n",
"terdapat sheet: ['Data Stasiun']\n",
"=======================\n",
"FILE 20\n",
"membaca berkas: PH.001 TELUK BETUNG UTARA 2014\n",
"terdapat sheet: ['Data Stasiun']\n",
"=======================\n",
"FILE 21\n",
"membaca berkas: PH.003 SUKARAME2014\n",
"terdapat sheet: ['Data Stasiun']\n",
"=======================\n",
"FILE 22\n",
"membaca berkas: PH.004 SUMUR PUTRI\n",
"terdapat sheet: ['Data Stasiun']\n",
"=======================\n",
"FILE 23\n",
"membaca berkas: PH.005 KEMILING\n",
"terdapat sheet: ['Data Stasiun']\n",
"=======================\n",
"FILE 24\n",
"membaca berkas: PH.001 TELUK BETUNG UTARA\n",
"terdapat sheet: ['Data Stasiun']\n",
"=======================\n",
"FILE 25\n",
"membaca berkas: PH.003 SUKARAME\n",
"terdapat sheet: ['Data Stasiun', '2015']\n",
"=======================\n",
"FILE 26\n",
"membaca berkas: PH.004 SUMUR PUTRI\n",
"terdapat sheet: ['Data Stasiun']\n",
"=======================\n",
"FILE 27\n",
"membaca berkas: PH.005 KEMILING\n",
"terdapat sheet: ['Data Stasiun']\n",
"=======================\n",
"FILE 28\n",
"membaca berkas: 1. PH.001 TELUK BETUNG UTARA\n",
"terdapat sheet: ['Data Stasiun', 'Data Stasiun x']\n",
"=======================\n",
"FILE 29\n",
"membaca berkas: 2. PH.003 SUKARAME\n",
"terdapat sheet: ['Data Stasiun']\n",
"=======================\n",
"FILE 30\n",
"membaca berkas: 3. PH.004 SUMUR PUTRI\n",
"terdapat sheet: ['Data Stasiun']\n",
"=======================\n",
"FILE 31\n",
"membaca berkas: 4. PH.005 KEMILING\n",
"terdapat sheet: ['Data Stasiun']\n",
"=======================\n",
"FILE 32\n",
"membaca berkas: PH.001 TELUK BETUNG UTARA\n",
"terdapat sheet: ['Data Stasiun']\n",
"=======================\n",
"FILE 33\n",
"membaca berkas: PH.003 SUKARAME\n",
"terdapat sheet: ['Data Stasiun']\n",
"=======================\n",
"FILE 34\n",
"membaca berkas: PH.004 SUMUR PUTRI\n",
"terdapat sheet: ['Data Stasiun']\n",
"=======================\n",
"FILE 35\n",
"membaca berkas: PH.005 KEMILING\n",
"terdapat sheet: ['Data Stasiun']\n",
"=======================\n",
"FILE 36\n",
"membaca berkas: PH.001 TELUK BETUNG UTARA\n",
"terdapat sheet: ['Data Stasiun']\n",
"=======================\n",
"FILE 37\n",
"membaca berkas: PH.003 SUKARAME\n",
"terdapat sheet: ['Data Stasiun']\n",
"=======================\n",
"FILE 38\n",
"membaca berkas: PH.004 SUMUR PUTRI\n",
"terdapat sheet: ['Data Stasiun']\n",
"=======================\n",
"FILE 39\n",
"membaca berkas: PH.005 KEMILING\n",
"terdapat sheet: ['Data Stasiun']\n",
"=======================\n"
]
}
]
},
{
"cell_type": "markdown",
"source": [
"Diketahui ada 4 stasiun yaitu PH 001, PH 003, PH 004, PH 005. Terlihat polanya yang berubah hanya 1 digit dibelakang."
],
"metadata": {
"id": "0D9GIW3Kd7DC"
}
},
{
"cell_type": "code",
"source": [
"# AMBIL/FILTER BERKAS YANG BERNAMA PH 001 atau kombinasinya. \n",
"\n",
"DIGIT_IDENTIFIER = [1, 3, 4, 5]\n",
"\n",
"info_dict = {}\n",
"\n",
"for num in DIGIT_IDENTIFIER:\n",
" regex = re.compile(f'.*[Pp][Hh][ .]00{num}.*')\n",
" info_dict[f'PH00{num}'] = [file for file in dataset_excel_list if regex.match(file.stem)]\n",
"\n",
"info_dict"
],
"metadata": {
"id": "fL8AZnkC3tYK",
"colab": {
"base_uri": "https://localhost:8080/"
},
"outputId": "f2b2256f-f83e-4de4-ec40-b442faa2f838"
},
"execution_count": 56,
"outputs": [
{
"output_type": "execute_result",
"data": {
"text/plain": [
"{'PH001': [WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2010/ph 001.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2011/PH.001 SUMUR BATU.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2012/PH.001 TELUK BETUNG UTARA.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2013/PH.001 TELUK BETUNG UTARA.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2014/PH.001 TELUK BETUNG UTARA 2014.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2015/PH.001 TELUK BETUNG UTARA.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2016/1. PH.001 TELUK BETUNG UTARA.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2017/PH.001 TELUK BETUNG UTARA.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2018/PH.001 TELUK BETUNG UTARA.xls')],\n",
" 'PH003': [WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2010/ph 003.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2011/PH.003 SUKA BUMI.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2012/PH.003 SUKARAME 2012.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2013/PH.003 SUKARAME.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2014/PH.003 SUKARAME2014.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2015/PH.003 SUKARAME.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2016/2. PH.003 SUKARAME.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2017/PH.003 SUKARAME.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2018/PH.003 SUKARAME.xls')],\n",
" 'PH004': [WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2010/ph 004.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2011/PH.004 SUSUNAN BARU.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2012/PH.004 SUMUR PUTRI 2012.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2013/PH.004 SUMUR PUTRI.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2014/PH.004 SUMUR PUTRI.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2015/PH.004 SUMUR PUTRI.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2016/3. PH.004 SUMUR PUTRI.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2017/PH.004 SUMUR PUTRI.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2018/PH.004 SUMUR PUTRI.xls')],\n",
" 'PH005': [WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2010/ph 005.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2011/PH.005 KEMILING.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2012/PH.005 KEMILING 2012.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2013/PH.005 KEMILING.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2014/PH.005 KEMILING.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2015/PH.005 KEMILING.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2016/4. PH.005 KEMILING.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2017/PH.005 KEMILING.xls'),\n",
" WindowsPath('laporan_implementasi/li5/dataset/PH BANDAR LAMPUNG 10 Tahun/2018/PH.005 KEMILING.xls')]}"
]
},
"metadata": {},
"execution_count": 56
}
]
},
{
"cell_type": "code",
"source": [
"# transfer data ke excel target\n",
"\n",
"EXCEL_TARGET_NAME = 'hk_daily_bandar_lampung.xlsx'\n",
"\n",
"excel_target = dataset_path / EXCEL_TARGET_NAME\n",
"excel_target.exists()"
],
"metadata": {
"id": "1qe4513tBzCj",
"colab": {
"base_uri": "https://localhost:8080/"
},
"outputId": "f0ec064a-3333-47db-e537-d6e4bde3e549"
},
"execution_count": 57,
"outputs": [
{
"output_type": "execute_result",
"data": {
"text/plain": [
"True"
]
},
"metadata": {},
"execution_count": 57
}
]
},
{
"cell_type": "code",
"source": [
"# Proses Transfer Data\n",
"\n",
"# Informasi berikut ini \n",
"NAMA_SHEET = 'Data Stasiun'\n",
"\n",
"ROW_START = 36\n",
"YEAR_COL = 'B'\n",
"DATA_COL = 'E'\n",
"ROW_DELTA = 33\n",
"RANGE_DATA_SOURCE = 'B21:M51'\n",
"\n",
"daftar_nama_stasiun = list(info_dict.keys()) # ['PH001', 'PH003', 'PH004', 'PH005']\n",
"\n",
"book_target = xw.Book(excel_target)\n",
"\n",
"\n",
"\n",
"with xw.App(visible=False) as app:\n",
" for stasiun in daftar_nama_stasiun:\n",
" row_target = ROW_START\n",
" n_excel = len(info_dict[stasiun])\n",
" print(f'MEMBACA DATA STASIUN {stasiun} SEBANYAK {n_excel} BERKAS')\n",
" for num, file in enumerate(info_dict[stasiun], 1):\n",
" # print(f'BERKAS KE-{num}')\n",
" print(f'MEMBUKA BERKAS: {file.absolute()}')\n",
" book = app.books.open(file)\n",
" year = int(file.parts[-2])\n",
" # daftar_sheet = [_sheet.name for _sheet in book.sheets]\n",
" # print(f'TERDAPAT SHEET: {daftar_sheet}')\n",
" \n",
" # BACA DATA\n",
" # print(f'MEMBACA DATA DARI {NAMA_SHEET}')\n",
" data = book.sheets[NAMA_SHEET].range(RANGE_DATA_SOURCE).options(pd.DataFrame, index=False, header=False).value\n",
"\n",
" # TRANSFER DATA\n",
" print(f'>>> TRANSFER DATA DARI {file.name} >>> {excel_target.name}')\n",
" # TAHUN\n",
" book_target.sheets[stasiun].range(YEAR_COL + str(row_target)).value = year\n",
"\n",
" # DATA\n",
" book_target.sheets[stasiun].range(DATA_COL + str(row_target)).value = data.to_numpy()\n",
"\n",
" row_target += ROW_DELTA\n",
"\n",
" print(f'TUTUP BUKU {file.name}\\n')\n",
" book.close()\n"
],
"metadata": {
"id": "zImeD6lurSk3",
"colab": {
"base_uri": "https://localhost:8080/"
},
"outputId": "d6d92a09-46a7-4dfe-f2e4-bcaf9ca30494"
},
"execution_count": 58,
"outputs": [
{
"output_type": "stream",
"name": "stdout",
"text": [
"MEMBACA DATA STASIUN PH001 SEBANYAK 9 BERKAS\n",
"MEMBUKA BERKAS: G:\\My Drive\\Colab Notebooks\\laporan_implementasi\\li5\\dataset\\PH BANDAR LAMPUNG 10 Tahun\\2010\\ph 001.xls\n",
">>> TRANSFER DATA DARI ph 001.xls >>> hk_daily_bandar_lampung.xlsx\n",
"TUTUP BUKU ph 001.xls\n",
"\n",
"MEMBUKA BERKAS: G:\\My Drive\\Colab Notebooks\\laporan_implementasi\\li5\\dataset\\PH BANDAR LAMPUNG 10 Tahun\\2011\\PH.001 SUMUR BATU.xls\n",
">>> TRANSFER DATA DARI PH.001 SUMUR BATU.xls >>> hk_daily_bandar_lampung.xlsx\n",
"TUTUP BUKU PH.001 SUMUR BATU.xls\n",
"\n",
"MEMBUKA BERKAS: G:\\My Drive\\Colab Notebooks\\laporan_implementasi\\li5\\dataset\\PH BANDAR LAMPUNG 10 Tahun\\2012\\PH.001 TELUK BETUNG UTARA.xls\n",
">>> TRANSFER DATA DARI PH.001 TELUK BETUNG UTARA.xls >>> hk_daily_bandar_lampung.xlsx\n",
"TUTUP BUKU PH.001 TELUK BETUNG UTARA.xls\n",
"\n",
"MEMBUKA BERKAS: G:\\My Drive\\Colab Notebooks\\laporan_implementasi\\li5\\dataset\\PH BANDAR LAMPUNG 10 Tahun\\2013\\PH.001 TELUK BETUNG UTARA.xls\n",
">>> TRANSFER DATA DARI PH.001 TELUK BETUNG UTARA.xls >>> hk_daily_bandar_lampung.xlsx\n",
"TUTUP BUKU PH.001 TELUK BETUNG UTARA.xls\n",
"\n",
"MEMBUKA BERKAS: G:\\My Drive\\Colab Notebooks\\laporan_implementasi\\li5\\dataset\\PH BANDAR LAMPUNG 10 Tahun\\2014\\PH.001 TELUK BETUNG UTARA 2014.xls\n",
">>> TRANSFER DATA DARI PH.001 TELUK BETUNG UTARA 2014.xls >>> hk_daily_bandar_lampung.xlsx\n",
"TUTUP BUKU PH.001 TELUK BETUNG UTARA 2014.xls\n",
"\n",
"MEMBUKA BERKAS: G:\\My Drive\\Colab Notebooks\\laporan_implementasi\\li5\\dataset\\PH BANDAR LAMPUNG 10 Tahun\\2015\\PH.001 TELUK BETUNG UTARA.xls\n",
">>> TRANSFER DATA DARI PH.001 TELUK BETUNG UTARA.xls >>> hk_daily_bandar_lampung.xlsx\n",
"TUTUP BUKU PH.001 TELUK BETUNG UTARA.xls\n",
"\n",
"MEMBUKA BERKAS: G:\\My Drive\\Colab Notebooks\\laporan_implementasi\\li5\\dataset\\PH BANDAR LAMPUNG 10 Tahun\\2016\\1. PH.001 TELUK BETUNG UTARA.xls\n",
">>> TRANSFER DATA DARI 1. PH.001 TELUK BETUNG UTARA.xls >>> hk_daily_bandar_lampung.xlsx\n",
"TUTUP BUKU 1. PH.001 TELUK BETUNG UTARA.xls\n",
"\n",
"MEMBUKA BERKAS: G:\\My Drive\\Colab Notebooks\\laporan_implementasi\\li5\\dataset\\PH BANDAR LAMPUNG 10 Tahun\\2017\\PH.001 TELUK BETUNG UTARA.xls\n",
">>> TRANSFER DATA DARI PH.001 TELUK BETUNG UTARA.xls >>> hk_daily_bandar_lampung.xlsx\n",
"TUTUP BUKU PH.001 TELUK BETUNG UTARA.xls\n",
"\n",
"MEMBUKA BERKAS: G:\\My Drive\\Colab Notebooks\\laporan_implementasi\\li5\\dataset\\PH BANDAR LAMPUNG 10 Tahun\\2018\\PH.001 TELUK BETUNG UTARA.xls\n",
">>> TRANSFER DATA DARI PH.001 TELUK BETUNG UTARA.xls >>> hk_daily_bandar_lampung.xlsx\n",
"TUTUP BUKU PH.001 TELUK BETUNG UTARA.xls\n",
"\n",
"MEMBACA DATA STASIUN PH003 SEBANYAK 9 BERKAS\n",
"MEMBUKA BERKAS: G:\\My Drive\\Colab Notebooks\\laporan_implementasi\\li5\\dataset\\PH BANDAR LAMPUNG 10 Tahun\\2010\\ph 003.xls\n",
">>> TRANSFER DATA DARI ph 003.xls >>> hk_daily_bandar_lampung.xlsx\n",
"TUTUP BUKU ph 003.xls\n",
"\n",
"MEMBUKA BERKAS: G:\\My Drive\\Colab Notebooks\\laporan_implementasi\\li5\\dataset\\PH BANDAR LAMPUNG 10 Tahun\\2011\\PH.003 SUKA BUMI.xls\n",
">>> TRANSFER DATA DARI PH.003 SUKA BUMI.xls >>> hk_daily_bandar_lampung.xlsx\n",
"TUTUP BUKU PH.003 SUKA BUMI.xls\n",
"\n",
"MEMBUKA BERKAS: G:\\My Drive\\Colab Notebooks\\laporan_implementasi\\li5\\dataset\\PH BANDAR LAMPUNG 10 Tahun\\2012\\PH.003 SUKARAME 2012.xls\n",
">>> TRANSFER DATA DARI PH.003 SUKARAME 2012.xls >>> hk_daily_bandar_lampung.xlsx\n",
"TUTUP BUKU PH.003 SUKARAME 2012.xls\n",
"\n",
"MEMBUKA BERKAS: G:\\My Drive\\Colab Notebooks\\laporan_implementasi\\li5\\dataset\\PH BANDAR LAMPUNG 10 Tahun\\2013\\PH.003 SUKARAME.xls\n",
">>> TRANSFER DATA DARI PH.003 SUKARAME.xls >>> hk_daily_bandar_lampung.xlsx\n",
"TUTUP BUKU PH.003 SUKARAME.xls\n",
"\n",
"MEMBUKA BERKAS: G:\\My Drive\\Colab Notebooks\\laporan_implementasi\\li5\\dataset\\PH BANDAR LAMPUNG 10 Tahun\\2014\\PH.003 SUKARAME2014.xls\n",
">>> TRANSFER DATA DARI PH.003 SUKARAME2014.xls >>> hk_daily_bandar_lampung.xlsx\n",
"TUTUP BUKU PH.003 SUKARAME2014.xls\n",
"\n",
"MEMBUKA BERKAS: G:\\My Drive\\Colab Notebooks\\laporan_implementasi\\li5\\dataset\\PH BANDAR LAMPUNG 10 Tahun\\2015\\PH.003 SUKARAME.xls\n",
">>> TRANSFER DATA DARI PH.003 SUKARAME.xls >>> hk_daily_bandar_lampung.xlsx\n",
"TUTUP BUKU PH.003 SUKARAME.xls\n",
"\n",
"MEMBUKA BERKAS: G:\\My Drive\\Colab Notebooks\\laporan_implementasi\\li5\\dataset\\PH BANDAR LAMPUNG 10 Tahun\\2016\\2. PH.003 SUKARAME.xls\n",
">>> TRANSFER DATA DARI 2. PH.003 SUKARAME.xls >>> hk_daily_bandar_lampung.xlsx\n",
"TUTUP BUKU 2. PH.003 SUKARAME.xls\n",
"\n",
"MEMBUKA BERKAS: G:\\My Drive\\Colab Notebooks\\laporan_implementasi\\li5\\dataset\\PH BANDAR LAMPUNG 10 Tahun\\2017\\PH.003 SUKARAME.xls\n",
">>> TRANSFER DATA DARI PH.003 SUKARAME.xls >>> hk_daily_bandar_lampung.xlsx\n",
"TUTUP BUKU PH.003 SUKARAME.xls\n",
"\n",
"MEMBUKA BERKAS: G:\\My Drive\\Colab Notebooks\\laporan_implementasi\\li5\\dataset\\PH BANDAR LAMPUNG 10 Tahun\\2018\\PH.003 SUKARAME.xls\n",
">>> TRANSFER DATA DARI PH.003 SUKARAME.xls >>> hk_daily_bandar_lampung.xlsx\n",
"TUTUP BUKU PH.003 SUKARAME.xls\n",
"\n",
"MEMBACA DATA STASIUN PH004 SEBANYAK 9 BERKAS\n",
"MEMBUKA BERKAS: G:\\My Drive\\Colab Notebooks\\laporan_implementasi\\li5\\dataset\\PH BANDAR LAMPUNG 10 Tahun\\2010\\ph 004.xls\n",
">>> TRANSFER DATA DARI ph 004.xls >>> hk_daily_bandar_lampung.xlsx\n",
"TUTUP BUKU ph 004.xls\n",
"\n",
"MEMBUKA BERKAS: G:\\My Drive\\Colab Notebooks\\laporan_implementasi\\li5\\dataset\\PH BANDAR LAMPUNG 10 Tahun\\2011\\PH.004 SUSUNAN BARU.xls\n",
">>> TRANSFER DATA DARI PH.004 SUSUNAN BARU.xls >>> hk_daily_bandar_lampung.xlsx\n",
"TUTUP BUKU PH.004 SUSUNAN BARU.xls\n",
"\n",
"MEMBUKA BERKAS: G:\\My Drive\\Colab Notebooks\\laporan_implementasi\\li5\\dataset\\PH BANDAR LAMPUNG 10 Tahun\\2012\\PH.004 SUMUR PUTRI 2012.xls\n",
">>> TRANSFER DATA DARI PH.004 SUMUR PUTRI 2012.xls >>> hk_daily_bandar_lampung.xlsx\n",
"TUTUP BUKU PH.004 SUMUR PUTRI 2012.xls\n",
"\n",
"MEMBUKA BERKAS: G:\\My Drive\\Colab Notebooks\\laporan_implementasi\\li5\\dataset\\PH BANDAR LAMPUNG 10 Tahun\\2013\\PH.004 SUMUR PUTRI.xls\n",
">>> TRANSFER DATA DARI PH.004 SUMUR PUTRI.xls >>> hk_daily_bandar_lampung.xlsx\n",
"TUTUP BUKU PH.004 SUMUR PUTRI.xls\n",
"\n",
"MEMBUKA BERKAS: G:\\My Drive\\Colab Notebooks\\laporan_implementasi\\li5\\dataset\\PH BANDAR LAMPUNG 10 Tahun\\2014\\PH.004 SUMUR PUTRI.xls\n",
">>> TRANSFER DATA DARI PH.004 SUMUR PUTRI.xls >>> hk_daily_bandar_lampung.xlsx\n",
"TUTUP BUKU PH.004 SUMUR PUTRI.xls\n",
"\n",
"MEMBUKA BERKAS: G:\\My Drive\\Colab Notebooks\\laporan_implementasi\\li5\\dataset\\PH BANDAR LAMPUNG 10 Tahun\\2015\\PH.004 SUMUR PUTRI.xls\n",
">>> TRANSFER DATA DARI PH.004 SUMUR PUTRI.xls >>> hk_daily_bandar_lampung.xlsx\n",
"TUTUP BUKU PH.004 SUMUR PUTRI.xls\n",
"\n",
"MEMBUKA BERKAS: G:\\My Drive\\Colab Notebooks\\laporan_implementasi\\li5\\dataset\\PH BANDAR LAMPUNG 10 Tahun\\2016\\3. PH.004 SUMUR PUTRI.xls\n",
">>> TRANSFER DATA DARI 3. PH.004 SUMUR PUTRI.xls >>> hk_daily_bandar_lampung.xlsx\n",
"TUTUP BUKU 3. PH.004 SUMUR PUTRI.xls\n",
"\n",
"MEMBUKA BERKAS: G:\\My Drive\\Colab Notebooks\\laporan_implementasi\\li5\\dataset\\PH BANDAR LAMPUNG 10 Tahun\\2017\\PH.004 SUMUR PUTRI.xls\n",
">>> TRANSFER DATA DARI PH.004 SUMUR PUTRI.xls >>> hk_daily_bandar_lampung.xlsx\n",
"TUTUP BUKU PH.004 SUMUR PUTRI.xls\n",
"\n",
"MEMBUKA BERKAS: G:\\My Drive\\Colab Notebooks\\laporan_implementasi\\li5\\dataset\\PH BANDAR LAMPUNG 10 Tahun\\2018\\PH.004 SUMUR PUTRI.xls\n",
">>> TRANSFER DATA DARI PH.004 SUMUR PUTRI.xls >>> hk_daily_bandar_lampung.xlsx\n",
"TUTUP BUKU PH.004 SUMUR PUTRI.xls\n",
"\n",
"MEMBACA DATA STASIUN PH005 SEBANYAK 9 BERKAS\n",
"MEMBUKA BERKAS: G:\\My Drive\\Colab Notebooks\\laporan_implementasi\\li5\\dataset\\PH BANDAR LAMPUNG 10 Tahun\\2010\\ph 005.xls\n",
">>> TRANSFER DATA DARI ph 005.xls >>> hk_daily_bandar_lampung.xlsx\n",
"TUTUP BUKU ph 005.xls\n",
"\n",
"MEMBUKA BERKAS: G:\\My Drive\\Colab Notebooks\\laporan_implementasi\\li5\\dataset\\PH BANDAR LAMPUNG 10 Tahun\\2011\\PH.005 KEMILING.xls\n",
">>> TRANSFER DATA DARI PH.005 KEMILING.xls >>> hk_daily_bandar_lampung.xlsx\n",
"TUTUP BUKU PH.005 KEMILING.xls\n",
"\n",
"MEMBUKA BERKAS: G:\\My Drive\\Colab Notebooks\\laporan_implementasi\\li5\\dataset\\PH BANDAR LAMPUNG 10 Tahun\\2012\\PH.005 KEMILING 2012.xls\n",
">>> TRANSFER DATA DARI PH.005 KEMILING 2012.xls >>> hk_daily_bandar_lampung.xlsx\n",
"TUTUP BUKU PH.005 KEMILING 2012.xls\n",
"\n",
"MEMBUKA BERKAS: G:\\My Drive\\Colab Notebooks\\laporan_implementasi\\li5\\dataset\\PH BANDAR LAMPUNG 10 Tahun\\2013\\PH.005 KEMILING.xls\n",
">>> TRANSFER DATA DARI PH.005 KEMILING.xls >>> hk_daily_bandar_lampung.xlsx\n",
"TUTUP BUKU PH.005 KEMILING.xls\n",
"\n",
"MEMBUKA BERKAS: G:\\My Drive\\Colab Notebooks\\laporan_implementasi\\li5\\dataset\\PH BANDAR LAMPUNG 10 Tahun\\2014\\PH.005 KEMILING.xls\n",
">>> TRANSFER DATA DARI PH.005 KEMILING.xls >>> hk_daily_bandar_lampung.xlsx\n",
"TUTUP BUKU PH.005 KEMILING.xls\n",
"\n",
"MEMBUKA BERKAS: G:\\My Drive\\Colab Notebooks\\laporan_implementasi\\li5\\dataset\\PH BANDAR LAMPUNG 10 Tahun\\2015\\PH.005 KEMILING.xls\n",
">>> TRANSFER DATA DARI PH.005 KEMILING.xls >>> hk_daily_bandar_lampung.xlsx\n",
"TUTUP BUKU PH.005 KEMILING.xls\n",
"\n",
"MEMBUKA BERKAS: G:\\My Drive\\Colab Notebooks\\laporan_implementasi\\li5\\dataset\\PH BANDAR LAMPUNG 10 Tahun\\2016\\4. PH.005 KEMILING.xls\n",
">>> TRANSFER DATA DARI 4. PH.005 KEMILING.xls >>> hk_daily_bandar_lampung.xlsx\n",
"TUTUP BUKU 4. PH.005 KEMILING.xls\n",
"\n",
"MEMBUKA BERKAS: G:\\My Drive\\Colab Notebooks\\laporan_implementasi\\li5\\dataset\\PH BANDAR LAMPUNG 10 Tahun\\2017\\PH.005 KEMILING.xls\n",
">>> TRANSFER DATA DARI PH.005 KEMILING.xls >>> hk_daily_bandar_lampung.xlsx\n",
"TUTUP BUKU PH.005 KEMILING.xls\n",
"\n",
"MEMBUKA BERKAS: G:\\My Drive\\Colab Notebooks\\laporan_implementasi\\li5\\dataset\\PH BANDAR LAMPUNG 10 Tahun\\2018\\PH.005 KEMILING.xls\n",
">>> TRANSFER DATA DARI PH.005 KEMILING.xls >>> hk_daily_bandar_lampung.xlsx\n",
"TUTUP BUKU PH.005 KEMILING.xls\n",
"\n"
]
}
]
},
{
"cell_type": "markdown",
"source": [
"# Changelog\n",
"\n",
"```\n",
"- 20220329 - 1.0.0 - Initial\n",
"```\n",
"\n",
"#### Copyright &copy; 2022 [Taruma Sakti Megariansyah](https://taruma.github.io)\n",
"\n",
"Source code in this notebook is licensed under a [MIT License](https://choosealicense.com/licenses/mit/). Data in this notebook is licensed under a [Creative Common Attribution 4.0 International](https://creativecommons.org/licenses/by/4.0/). \n"
],
"metadata": {
"id": "ANHgYmS8oNKK"
}
}
]
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment