{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"2024/12月份開灌之資料DataDate年分會歸到2025，修正為2024\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>WorkStationId</th>\n",
       "      <th>WorkStationGrpId</th>\n",
       "      <th>DataYear</th>\n",
       "      <th>PeriodNo</th>\n",
       "      <th>CropType</th>\n",
       "      <th>DataDate</th>\n",
       "      <th>CalcArea</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>07002</td>\n",
       "      <td>0700203</td>\n",
       "      <td>2025</td>\n",
       "      <td>1</td>\n",
       "      <td>TR</td>\n",
       "      <td>2025-01-29</td>\n",
       "      <td>16296.17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>07002</td>\n",
       "      <td>0700204</td>\n",
       "      <td>2025</td>\n",
       "      <td>1</td>\n",
       "      <td>TR</td>\n",
       "      <td>2025-01-29</td>\n",
       "      <td>21953.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>07002</td>\n",
       "      <td>0700205</td>\n",
       "      <td>2025</td>\n",
       "      <td>1</td>\n",
       "      <td>TR</td>\n",
       "      <td>2025-01-29</td>\n",
       "      <td>14863.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>07003</td>\n",
       "      <td>0700301</td>\n",
       "      <td>2025</td>\n",
       "      <td>1</td>\n",
       "      <td>TR</td>\n",
       "      <td>2025-01-29</td>\n",
       "      <td>3409.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>07003</td>\n",
       "      <td>0700302</td>\n",
       "      <td>2025</td>\n",
       "      <td>1</td>\n",
       "      <td>TR</td>\n",
       "      <td>2025-01-29</td>\n",
       "      <td>60775.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2494</th>\n",
       "      <td>14009</td>\n",
       "      <td>1400911</td>\n",
       "      <td>2025</td>\n",
       "      <td>1</td>\n",
       "      <td>TR</td>\n",
       "      <td>2025-01-04</td>\n",
       "      <td>11819.93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2495</th>\n",
       "      <td>14009</td>\n",
       "      <td>1400912</td>\n",
       "      <td>2025</td>\n",
       "      <td>1</td>\n",
       "      <td>TR</td>\n",
       "      <td>2025-01-04</td>\n",
       "      <td>7549.19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2496</th>\n",
       "      <td>14009</td>\n",
       "      <td>1400913</td>\n",
       "      <td>2025</td>\n",
       "      <td>1</td>\n",
       "      <td>TR</td>\n",
       "      <td>2025-01-04</td>\n",
       "      <td>3632.57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2497</th>\n",
       "      <td>14009</td>\n",
       "      <td>1400916</td>\n",
       "      <td>2025</td>\n",
       "      <td>1</td>\n",
       "      <td>TR</td>\n",
       "      <td>2025-01-04</td>\n",
       "      <td>5135.79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2498</th>\n",
       "      <td>14009</td>\n",
       "      <td>1400918</td>\n",
       "      <td>2025</td>\n",
       "      <td>1</td>\n",
       "      <td>TR</td>\n",
       "      <td>2025-01-04</td>\n",
       "      <td>4454.13</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2499 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     WorkStationId WorkStationGrpId  DataYear  PeriodNo CropType   DataDate  \\\n",
       "0            07002          0700203      2025         1       TR 2025-01-29   \n",
       "1            07002          0700204      2025         1       TR 2025-01-29   \n",
       "2            07002          0700205      2025         1       TR 2025-01-29   \n",
       "3            07003          0700301      2025         1       TR 2025-01-29   \n",
       "4            07003          0700302      2025         1       TR 2025-01-29   \n",
       "...            ...              ...       ...       ...      ...        ...   \n",
       "2494         14009          1400911      2025         1       TR 2025-01-04   \n",
       "2495         14009          1400912      2025         1       TR 2025-01-04   \n",
       "2496         14009          1400913      2025         1       TR 2025-01-04   \n",
       "2497         14009          1400916      2025         1       TR 2025-01-04   \n",
       "2498         14009          1400918      2025         1       TR 2025-01-04   \n",
       "\n",
       "      CalcArea  \n",
       "0     16296.17  \n",
       "1     21953.26  \n",
       "2     14863.00  \n",
       "3      3409.74  \n",
       "4     60775.90  \n",
       "...        ...  \n",
       "2494  11819.93  \n",
       "2495   7549.19  \n",
       "2496   3632.57  \n",
       "2497   5135.79  \n",
       "2498   4454.13  \n",
       "\n",
       "[2499 rows x 7 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel('tbl_CropAreaData_RealTime2025_1140218.xlsx', dtype={'WorkStationId': str, 'WorkStationGrpId': str})\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "WorkStationId               object\n",
       "WorkStationGrpId            object\n",
       "DataYear                     int64\n",
       "PeriodNo                     int64\n",
       "CropType                    object\n",
       "DataDate            datetime64[ns]\n",
       "CalcArea                   float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['DataDate'] = pd.to_datetime(df['DataDate'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['DataDate'] = df['DataDate'].apply(lambda x: x.replace(year = x.year - 1) if x.month == 12 else x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>WorkStationId</th>\n",
       "      <th>WorkStationGrpId</th>\n",
       "      <th>DataYear</th>\n",
       "      <th>PeriodNo</th>\n",
       "      <th>CropType</th>\n",
       "      <th>DataDate</th>\n",
       "      <th>CalcArea</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>07002</td>\n",
       "      <td>0700203</td>\n",
       "      <td>2025</td>\n",
       "      <td>1</td>\n",
       "      <td>TR</td>\n",
       "      <td>2025-01-29</td>\n",
       "      <td>16296.17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>07002</td>\n",
       "      <td>0700204</td>\n",
       "      <td>2025</td>\n",
       "      <td>1</td>\n",
       "      <td>TR</td>\n",
       "      <td>2025-01-29</td>\n",
       "      <td>21953.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>07002</td>\n",
       "      <td>0700205</td>\n",
       "      <td>2025</td>\n",
       "      <td>1</td>\n",
       "      <td>TR</td>\n",
       "      <td>2025-01-29</td>\n",
       "      <td>14863.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>07003</td>\n",
       "      <td>0700301</td>\n",
       "      <td>2025</td>\n",
       "      <td>1</td>\n",
       "      <td>TR</td>\n",
       "      <td>2025-01-29</td>\n",
       "      <td>3409.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>07003</td>\n",
       "      <td>0700302</td>\n",
       "      <td>2025</td>\n",
       "      <td>1</td>\n",
       "      <td>TR</td>\n",
       "      <td>2025-01-29</td>\n",
       "      <td>60775.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2494</th>\n",
       "      <td>14009</td>\n",
       "      <td>1400911</td>\n",
       "      <td>2025</td>\n",
       "      <td>1</td>\n",
       "      <td>TR</td>\n",
       "      <td>2025-01-04</td>\n",
       "      <td>11819.93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2495</th>\n",
       "      <td>14009</td>\n",
       "      <td>1400912</td>\n",
       "      <td>2025</td>\n",
       "      <td>1</td>\n",
       "      <td>TR</td>\n",
       "      <td>2025-01-04</td>\n",
       "      <td>7549.19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2496</th>\n",
       "      <td>14009</td>\n",
       "      <td>1400913</td>\n",
       "      <td>2025</td>\n",
       "      <td>1</td>\n",
       "      <td>TR</td>\n",
       "      <td>2025-01-04</td>\n",
       "      <td>3632.57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2497</th>\n",
       "      <td>14009</td>\n",
       "      <td>1400916</td>\n",
       "      <td>2025</td>\n",
       "      <td>1</td>\n",
       "      <td>TR</td>\n",
       "      <td>2025-01-04</td>\n",
       "      <td>5135.79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2498</th>\n",
       "      <td>14009</td>\n",
       "      <td>1400918</td>\n",
       "      <td>2025</td>\n",
       "      <td>1</td>\n",
       "      <td>TR</td>\n",
       "      <td>2025-01-04</td>\n",
       "      <td>4454.13</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2499 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     WorkStationId WorkStationGrpId  DataYear  PeriodNo CropType   DataDate  \\\n",
       "0            07002          0700203      2025         1       TR 2025-01-29   \n",
       "1            07002          0700204      2025         1       TR 2025-01-29   \n",
       "2            07002          0700205      2025         1       TR 2025-01-29   \n",
       "3            07003          0700301      2025         1       TR 2025-01-29   \n",
       "4            07003          0700302      2025         1       TR 2025-01-29   \n",
       "...            ...              ...       ...       ...      ...        ...   \n",
       "2494         14009          1400911      2025         1       TR 2025-01-04   \n",
       "2495         14009          1400912      2025         1       TR 2025-01-04   \n",
       "2496         14009          1400913      2025         1       TR 2025-01-04   \n",
       "2497         14009          1400916      2025         1       TR 2025-01-04   \n",
       "2498         14009          1400918      2025         1       TR 2025-01-04   \n",
       "\n",
       "      CalcArea  \n",
       "0     16296.17  \n",
       "1     21953.26  \n",
       "2     14863.00  \n",
       "3      3409.74  \n",
       "4     60775.90  \n",
       "...        ...  \n",
       "2494  11819.93  \n",
       "2495   7549.19  \n",
       "2496   3632.57  \n",
       "2497   5135.79  \n",
       "2498   4454.13  \n",
       "\n",
       "[2499 rows x 7 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_excel('tbl_CropAreaData_RealTime2025_1140218_yearFixed.xlsx', index=False)"
   ]
  }
 ],
 "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.12.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}