{
 "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
}