501 lines
15 KiB
Plaintext
501 lines
15 KiB
Plaintext
{
|
|
"cells": [
|
|
{
|
|
"cell_type": "markdown",
|
|
"id": "5d345c6c",
|
|
"metadata": {},
|
|
"source": [
|
|
"# Manipulasi `deploy_data.xlsx`\n",
|
|
"\n",
|
|
"Notebook ini mengubah data deploy berbentuk wide menjadi format long yang sama dengan `data testing.xlsx`: `Tanggal`, `Harga Medium 1`, `Harga Medium 2`, dan `rata2`."
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": null,
|
|
"id": "ffcf2b96",
|
|
"metadata": {},
|
|
"outputs": [
|
|
{
|
|
"data": {
|
|
"text/plain": [
|
|
"WindowsPath('c:/Users/LENOVO/Documents/TA/Nbeats/deploy_data.xlsx')"
|
|
]
|
|
},
|
|
"execution_count": 1,
|
|
"metadata": {},
|
|
"output_type": "execute_result"
|
|
}
|
|
],
|
|
"source": [
|
|
"from pathlib import Path\n",
|
|
"import re\n",
|
|
"import numpy as np\n",
|
|
"import pandas as pd\n",
|
|
"import matplotlib.pyplot as plt\n",
|
|
"\n",
|
|
"ROOT = Path.cwd()\n",
|
|
"if ROOT.name == 'Streamlit':\n",
|
|
" ROOT = ROOT.parent\n",
|
|
"INPUT_FILE = ROOT / 'deploy_data.xlsx'\n",
|
|
"REFERENCE_FILE = ROOT / 'data testing.xlsx'\n",
|
|
"OUTPUT_XLSX = ROOT / 'data deploy hasil.xlsx'\n",
|
|
"OUTPUT_CSV = ROOT / 'data deploy hasil.csv'\n",
|
|
"MEDIUM_1_LABEL = 'Beras Kualitas Medium I'\n",
|
|
"MEDIUM_2_LABEL = 'Beras Kualitas Medium II'\n",
|
|
"USE_DAILY_CALENDAR = True\n",
|
|
"INPUT_FILE"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": 2,
|
|
"id": "15b6be12",
|
|
"metadata": {},
|
|
"outputs": [
|
|
{
|
|
"name": "stdout",
|
|
"output_type": "stream",
|
|
"text": [
|
|
"Ukuran deploy: (3, 194)\n",
|
|
"Kolom deploy: ['No', 'Komoditas (Rp)', '01/ 01/ 2026', '02/ 01/ 2026', '05/ 01/ 2026'] ... ['23/ 09/ 2026', '24/ 09/ 2026', '25/ 09/ 2026']\n",
|
|
"Baris komoditas:\n"
|
|
]
|
|
},
|
|
{
|
|
"data": {
|
|
"text/html": [
|
|
"<div>\n",
|
|
"<style scoped>\n",
|
|
" .dataframe tbody tr th:only-of-type {\n",
|
|
" vertical-align: middle;\n",
|
|
" }\n",
|
|
"\n",
|
|
" .dataframe tbody tr th {\n",
|
|
" vertical-align: top;\n",
|
|
" }\n",
|
|
"\n",
|
|
" .dataframe thead th {\n",
|
|
" text-align: right;\n",
|
|
" }\n",
|
|
"</style>\n",
|
|
"<table border=\"1\" class=\"dataframe\">\n",
|
|
" <thead>\n",
|
|
" <tr style=\"text-align: right;\">\n",
|
|
" <th></th>\n",
|
|
" <th>No</th>\n",
|
|
" <th>Komoditas (Rp)</th>\n",
|
|
" </tr>\n",
|
|
" </thead>\n",
|
|
" <tbody>\n",
|
|
" <tr>\n",
|
|
" <th>0</th>\n",
|
|
" <td>I</td>\n",
|
|
" <td>Beras</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>1</th>\n",
|
|
" <td>1</td>\n",
|
|
" <td>Beras Kualitas Medium I</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>2</th>\n",
|
|
" <td>2</td>\n",
|
|
" <td>Beras Kualitas Medium II</td>\n",
|
|
" </tr>\n",
|
|
" </tbody>\n",
|
|
"</table>\n",
|
|
"</div>"
|
|
],
|
|
"text/plain": [
|
|
" No Komoditas (Rp)\n",
|
|
"0 I Beras\n",
|
|
"1 1 Beras Kualitas Medium I\n",
|
|
"2 2 Beras Kualitas Medium II"
|
|
]
|
|
},
|
|
"metadata": {},
|
|
"output_type": "display_data"
|
|
},
|
|
{
|
|
"name": "stdout",
|
|
"output_type": "stream",
|
|
"text": [
|
|
"Schema referensi: ['Tanggal', 'Harga Medium 1', 'Harga Medium 2', 'rata2']\n"
|
|
]
|
|
}
|
|
],
|
|
"source": [
|
|
"deploy_raw = pd.read_excel(INPUT_FILE)\n",
|
|
"reference = pd.read_excel(REFERENCE_FILE)\n",
|
|
"print('Ukuran deploy:', deploy_raw.shape)\n",
|
|
"print('Kolom deploy:', list(deploy_raw.columns[:5]), '...', list(deploy_raw.columns[-3:]))\n",
|
|
"print('Baris komoditas:')\n",
|
|
"display(deploy_raw.iloc[:, :2])\n",
|
|
"print('Schema referensi:', reference.columns.tolist())"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": 3,
|
|
"id": "09ac66e7",
|
|
"metadata": {},
|
|
"outputs": [],
|
|
"source": [
|
|
"def parse_number(value):\n",
|
|
" if pd.isna(value):\n",
|
|
" return np.nan\n",
|
|
" text = str(value).strip().replace('Rp', '').replace(' ', '')\n",
|
|
" if not text:\n",
|
|
" return np.nan\n",
|
|
" # Sumber memakai koma sebagai pemisah ribuan, misalnya 13,850.\n",
|
|
" text = re.sub(r'[^0-9,.-]', '', text)\n",
|
|
" if text.count(',') > 0 and '.' not in text:\n",
|
|
" text = text.replace(',', '')\n",
|
|
" else:\n",
|
|
" text = text.replace(',', '')\n",
|
|
" return pd.to_numeric(text, errors='coerce')\n",
|
|
"\n",
|
|
"def parse_deploy_date(value):\n",
|
|
" text = str(value).strip().replace(' ', '')\n",
|
|
" return pd.to_datetime(text, format='%d/%m/%Y', errors='coerce')\n",
|
|
"\n",
|
|
"def find_row(label):\n",
|
|
" labels = deploy_raw['Komoditas (Rp)'].astype(str).str.strip()\n",
|
|
" matches = deploy_raw.loc[labels.eq(label)]\n",
|
|
" if matches.empty:\n",
|
|
" raise ValueError(f'Baris komoditas tidak ditemukan: {label}')\n",
|
|
" return matches.iloc[0]"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": 4,
|
|
"id": "0c6bdf3e",
|
|
"metadata": {},
|
|
"outputs": [
|
|
{
|
|
"data": {
|
|
"text/html": [
|
|
"<div>\n",
|
|
"<style scoped>\n",
|
|
" .dataframe tbody tr th:only-of-type {\n",
|
|
" vertical-align: middle;\n",
|
|
" }\n",
|
|
"\n",
|
|
" .dataframe tbody tr th {\n",
|
|
" vertical-align: top;\n",
|
|
" }\n",
|
|
"\n",
|
|
" .dataframe thead th {\n",
|
|
" text-align: right;\n",
|
|
" }\n",
|
|
"</style>\n",
|
|
"<table border=\"1\" class=\"dataframe\">\n",
|
|
" <thead>\n",
|
|
" <tr style=\"text-align: right;\">\n",
|
|
" <th></th>\n",
|
|
" <th>Tanggal</th>\n",
|
|
" <th>Harga Medium 1</th>\n",
|
|
" <th>Harga Medium 2</th>\n",
|
|
" <th>rata2</th>\n",
|
|
" </tr>\n",
|
|
" </thead>\n",
|
|
" <tbody>\n",
|
|
" <tr>\n",
|
|
" <th>0</th>\n",
|
|
" <td>2026-01-01</td>\n",
|
|
" <td>13500.0</td>\n",
|
|
" <td>12250.0</td>\n",
|
|
" <td>12875.0</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>1</th>\n",
|
|
" <td>2026-01-02</td>\n",
|
|
" <td>13500.0</td>\n",
|
|
" <td>12250.0</td>\n",
|
|
" <td>12875.0</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>2</th>\n",
|
|
" <td>2026-01-03</td>\n",
|
|
" <td>13500.0</td>\n",
|
|
" <td>12250.0</td>\n",
|
|
" <td>12875.0</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>3</th>\n",
|
|
" <td>2026-01-04</td>\n",
|
|
" <td>13500.0</td>\n",
|
|
" <td>12250.0</td>\n",
|
|
" <td>12875.0</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>4</th>\n",
|
|
" <td>2026-01-05</td>\n",
|
|
" <td>13500.0</td>\n",
|
|
" <td>12250.0</td>\n",
|
|
" <td>12875.0</td>\n",
|
|
" </tr>\n",
|
|
" </tbody>\n",
|
|
"</table>\n",
|
|
"</div>"
|
|
],
|
|
"text/plain": [
|
|
" Tanggal Harga Medium 1 Harga Medium 2 rata2\n",
|
|
"0 2026-01-01 13500.0 12250.0 12875.0\n",
|
|
"1 2026-01-02 13500.0 12250.0 12875.0\n",
|
|
"2 2026-01-03 13500.0 12250.0 12875.0\n",
|
|
"3 2026-01-04 13500.0 12250.0 12875.0\n",
|
|
"4 2026-01-05 13500.0 12250.0 12875.0"
|
|
]
|
|
},
|
|
"execution_count": 4,
|
|
"metadata": {},
|
|
"output_type": "execute_result"
|
|
}
|
|
],
|
|
"source": [
|
|
"medium_1 = find_row(MEDIUM_1_LABEL)\n",
|
|
"medium_2 = find_row(MEDIUM_2_LABEL)\n",
|
|
"date_columns = [column for column in deploy_raw.columns if parse_deploy_date(column) is not pd.NaT]\n",
|
|
"dates = pd.Series([parse_deploy_date(column) for column in date_columns], name='Tanggal')\n",
|
|
"result = pd.DataFrame({\n",
|
|
" 'Tanggal': dates,\n",
|
|
" 'Harga Medium 1': [parse_number(medium_1[column]) for column in date_columns],\n",
|
|
" 'Harga Medium 2': [parse_number(medium_2[column]) for column in date_columns],\n",
|
|
"})\n",
|
|
"result = result.sort_values('Tanggal').drop_duplicates('Tanggal').set_index('Tanggal')\n",
|
|
"\n",
|
|
"if USE_DAILY_CALENDAR:\n",
|
|
" full_dates = pd.date_range(result.index.min(), result.index.max(), freq='D')\n",
|
|
" result = result.reindex(full_dates)\n",
|
|
" result.index.name = 'Tanggal'\n",
|
|
" result[['Harga Medium 1', 'Harga Medium 2']] = result[['Harga Medium 1', 'Harga Medium 2']].interpolate(\n",
|
|
" method='linear', limit_direction='both'\n",
|
|
" )\n",
|
|
"\n",
|
|
"result['rata2'] = result[['Harga Medium 1', 'Harga Medium 2']].mean(axis=1)\n",
|
|
"result = result.reset_index()\n",
|
|
"result = result[['Tanggal', 'Harga Medium 1', 'Harga Medium 2', 'rata2']]\n",
|
|
"result.head()"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": 5,
|
|
"id": "4a0c5328",
|
|
"metadata": {},
|
|
"outputs": [
|
|
{
|
|
"name": "stdout",
|
|
"output_type": "stream",
|
|
"text": [
|
|
"Valid: 268 baris\n",
|
|
"Rentang: 2026-01-01 sampai 2026-09-25\n",
|
|
"Kolom: ['Tanggal', 'Harga Medium 1', 'Harga Medium 2', 'rata2']\n"
|
|
]
|
|
},
|
|
{
|
|
"data": {
|
|
"text/html": [
|
|
"<div>\n",
|
|
"<style scoped>\n",
|
|
" .dataframe tbody tr th:only-of-type {\n",
|
|
" vertical-align: middle;\n",
|
|
" }\n",
|
|
"\n",
|
|
" .dataframe tbody tr th {\n",
|
|
" vertical-align: top;\n",
|
|
" }\n",
|
|
"\n",
|
|
" .dataframe thead th {\n",
|
|
" text-align: right;\n",
|
|
" }\n",
|
|
"</style>\n",
|
|
"<table border=\"1\" class=\"dataframe\">\n",
|
|
" <thead>\n",
|
|
" <tr style=\"text-align: right;\">\n",
|
|
" <th></th>\n",
|
|
" <th>Tanggal</th>\n",
|
|
" <th>Harga Medium 1</th>\n",
|
|
" <th>Harga Medium 2</th>\n",
|
|
" <th>rata2</th>\n",
|
|
" </tr>\n",
|
|
" </thead>\n",
|
|
" <tbody>\n",
|
|
" <tr>\n",
|
|
" <th>0</th>\n",
|
|
" <td>2026-01-01</td>\n",
|
|
" <td>13500.0</td>\n",
|
|
" <td>12250.0</td>\n",
|
|
" <td>12875.0</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>1</th>\n",
|
|
" <td>2026-01-02</td>\n",
|
|
" <td>13500.0</td>\n",
|
|
" <td>12250.0</td>\n",
|
|
" <td>12875.0</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>2</th>\n",
|
|
" <td>2026-01-03</td>\n",
|
|
" <td>13500.0</td>\n",
|
|
" <td>12250.0</td>\n",
|
|
" <td>12875.0</td>\n",
|
|
" </tr>\n",
|
|
" </tbody>\n",
|
|
"</table>\n",
|
|
"</div>"
|
|
],
|
|
"text/plain": [
|
|
" Tanggal Harga Medium 1 Harga Medium 2 rata2\n",
|
|
"0 2026-01-01 13500.0 12250.0 12875.0\n",
|
|
"1 2026-01-02 13500.0 12250.0 12875.0\n",
|
|
"2 2026-01-03 13500.0 12250.0 12875.0"
|
|
]
|
|
},
|
|
"metadata": {},
|
|
"output_type": "display_data"
|
|
},
|
|
{
|
|
"data": {
|
|
"text/html": [
|
|
"<div>\n",
|
|
"<style scoped>\n",
|
|
" .dataframe tbody tr th:only-of-type {\n",
|
|
" vertical-align: middle;\n",
|
|
" }\n",
|
|
"\n",
|
|
" .dataframe tbody tr th {\n",
|
|
" vertical-align: top;\n",
|
|
" }\n",
|
|
"\n",
|
|
" .dataframe thead th {\n",
|
|
" text-align: right;\n",
|
|
" }\n",
|
|
"</style>\n",
|
|
"<table border=\"1\" class=\"dataframe\">\n",
|
|
" <thead>\n",
|
|
" <tr style=\"text-align: right;\">\n",
|
|
" <th></th>\n",
|
|
" <th>Tanggal</th>\n",
|
|
" <th>Harga Medium 1</th>\n",
|
|
" <th>Harga Medium 2</th>\n",
|
|
" <th>rata2</th>\n",
|
|
" </tr>\n",
|
|
" </thead>\n",
|
|
" <tbody>\n",
|
|
" <tr>\n",
|
|
" <th>265</th>\n",
|
|
" <td>2026-09-23</td>\n",
|
|
" <td>14500.0</td>\n",
|
|
" <td>13150.0</td>\n",
|
|
" <td>13825.0</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>266</th>\n",
|
|
" <td>2026-09-24</td>\n",
|
|
" <td>14500.0</td>\n",
|
|
" <td>13150.0</td>\n",
|
|
" <td>13825.0</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>267</th>\n",
|
|
" <td>2026-09-25</td>\n",
|
|
" <td>14500.0</td>\n",
|
|
" <td>13150.0</td>\n",
|
|
" <td>13825.0</td>\n",
|
|
" </tr>\n",
|
|
" </tbody>\n",
|
|
"</table>\n",
|
|
"</div>"
|
|
],
|
|
"text/plain": [
|
|
" Tanggal Harga Medium 1 Harga Medium 2 rata2\n",
|
|
"265 2026-09-23 14500.0 13150.0 13825.0\n",
|
|
"266 2026-09-24 14500.0 13150.0 13825.0\n",
|
|
"267 2026-09-25 14500.0 13150.0 13825.0"
|
|
]
|
|
},
|
|
"metadata": {},
|
|
"output_type": "display_data"
|
|
}
|
|
],
|
|
"source": [
|
|
"expected_columns = ['Tanggal', 'Harga Medium 1', 'Harga Medium 2', 'rata2']\n",
|
|
"assert result.columns.tolist() == expected_columns\n",
|
|
"assert pd.api.types.is_datetime64_any_dtype(result['Tanggal'])\n",
|
|
"assert result['Tanggal'].is_monotonic_increasing\n",
|
|
"assert result[expected_columns[1:]].notna().all().all()\n",
|
|
"assert result['Tanggal'].duplicated().sum() == 0\n",
|
|
"print(f'Valid: {len(result):,} baris')\n",
|
|
"print('Rentang:', result['Tanggal'].min().date(), 'sampai', result['Tanggal'].max().date())\n",
|
|
"print('Kolom:', result.columns.tolist())\n",
|
|
"display(result.head(3))\n",
|
|
"display(result.tail(3))"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": null,
|
|
"id": "7d9e6fef",
|
|
"metadata": {},
|
|
"outputs": [],
|
|
"source": [
|
|
"ax = result.set_index('Tanggal')[['Harga Medium 1', 'Harga Medium 2', 'rata2']].plot(figsize=(14, 5))\n",
|
|
"ax.set_title('Data deploy setelah manipulasi')\n",
|
|
"ax.set_ylabel('Harga')\n",
|
|
"ax.grid(alpha=0.25)\n",
|
|
"plt.show()"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": null,
|
|
"id": "ba97555c",
|
|
"metadata": {},
|
|
"outputs": [
|
|
{
|
|
"name": "stdout",
|
|
"output_type": "stream",
|
|
"text": [
|
|
"Tersimpan:\n",
|
|
"c:\\Users\\LENOVO\\Documents\\TA\\Nbeats\\data deploy hasil.xlsx\n",
|
|
"c:\\Users\\LENOVO\\Documents\\TA\\Nbeats\\data deploy hasil.csv\n"
|
|
]
|
|
}
|
|
],
|
|
"source": [
|
|
"result.to_excel(OUTPUT_XLSX, index=False)\n",
|
|
"# result.to_csv(OUTPUT_CSV, index=False, date_format='%Y-%m-%d')\n",
|
|
"print('Tersimpan:')\n",
|
|
"print(OUTPUT_XLSX)\n",
|
|
"print(OUTPUT_CSV)"
|
|
]
|
|
}
|
|
],
|
|
"metadata": {
|
|
"kernelspec": {
|
|
"display_name": "Python 3",
|
|
"language": "python",
|
|
"name": "python3"
|
|
},
|
|
"language_info": {
|
|
"codemirror_mode": {
|
|
"name": "ipython",
|
|
"version": 3
|
|
},
|
|
"file_extension": ".py",
|
|
"mimetype": "text/x-python",
|
|
"name": "python",
|
|
"nbconvert_exporter": "python",
|
|
"pygments_lexer": "ipython3",
|
|
"version": "3.10.6"
|
|
}
|
|
},
|
|
"nbformat": 4,
|
|
"nbformat_minor": 5
|
|
}
|