{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import plotly.graph_objects as go\n",
    "\n",
    "# 讀取數據\n",
    "file_path = 'TDCS_M06A_20240523_090000.csv'\n",
    "data = pd.read_csv(file_path)#, nrows=200)\n",
    "\n",
    "# 確認欄位名稱\n",
    "data.columns = ['VehicleType', 'DetectionTime_O', 'GantryID_O', 'DetectionTime_D', 'GantryID_D', 'TripLength', 'TripEnd', 'TripInformation']\n",
    "\n",
    "# 轉換時間格式\n",
    "data['DetectionTime_O'] = pd.to_datetime(data['DetectionTime_O'])\n",
    "data['DetectionTime_D'] = pd.to_datetime(data['DetectionTime_D'])\n",
    "\n",
    "# 只取GantryID_O開頭是01結尾是S的資料\n",
    "# data = data[data['GantryID_O'].str.startswith('01')]\n",
    "data = data[data['GantryID_O'].str.endswith('S')]\n",
    "data = data[data['GantryID_D'].str.endswith('S')]\n",
    "\n",
    "# 移除重複記錄\n",
    "data_cleaned = data.drop_duplicates()\n",
    "\n",
    "\n",
    "gantry_filepath = 'gantryID.csv'\n",
    "gantryList = pd.read_csv(gantry_filepath)\n",
    "\n",
    "gantryList.columns = ['line','direction','gantryNo','gantryID','src','dest','mille','carPrice','trunkPrice','largeTruckPrice','lat','lng']\n",
    "# 新增name並將名稱轉換為[src]_[dest]\n",
    "gantryList['name'] = gantryList['src'] + '_' + gantryList['dest']\n",
    "\n",
    "# 提取站點流動信息\n",
    "flows = []\n",
    "for trip_info in data_cleaned['TripInformation']:\n",
    "    stations = trip_info.split('; ')\n",
    "    for i in range(len(stations) - 1):\n",
    "        source = stations[i].split('+')[1]\n",
    "        target = stations[i + 1].split('+')[1]\n",
    "        flows.append((source, target))\n",
    "\n",
    "# 構建 Sankey 圖所需數據\n",
    "sources = []\n",
    "targets = []\n",
    "values = []\n",
    "\n",
    "flow_dict = {}\n",
    "for source, target in flows:\n",
    "    if (source, target) in flow_dict:\n",
    "        flow_dict[(source, target)] += 1\n",
    "    else:\n",
    "        flow_dict[(source, target)] = 1\n",
    "\n",
    "for key, value in flow_dict.items():\n",
    "    sources.append(key[0])\n",
    "    targets.append(key[1])\n",
    "    values.append(value)\n",
    "\n",
    "# 創建節點列表\n",
    "all_nodes = list(set(sources + targets))\n",
    "node_indices = {node: idx for idx, node in enumerate(all_nodes)}\n",
    "\n",
    "# 創建 Sankey 圖\n",
    "fig = go.Figure(data=[go.Sankey(\n",
    "    node=dict(\n",
    "        pad=15,\n",
    "        thickness=20,\n",
    "        line=dict(color=\"black\", width=0.5),\n",
    "        label=all_nodes,\n",
    "    ),\n",
    "    link=dict(\n",
    "        source=[node_indices[source] for source in sources],\n",
    "        target=[node_indices[target] for target in targets],\n",
    "        value=values,\n",
    "    ))])\n",
    "\n",
    "fig.update_layout(title_text=\"車輛在不同站點之間的流動 Sankey 圖\", font_size=10)\n",
    "fig.write_image(\"sankey.png\")\n",
    "fig.write_html(\"sankey.html\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   VehicleType  DetectionTime_O GantryID_O  DetectionTime_D GantryID_D  \\\n",
      "0           31  2024/5/23 09:35   01F3535S  2024/5/23 09:35   01F3535S   \n",
      "1           31  2024/5/23 09:00   01F0532S  2024/5/23 09:03   01F0557S   \n",
      "2           32  2024/5/23 09:02   03F0447S  2024/5/23 09:02   03F0447S   \n",
      "3           32  2024/5/23 09:15   01F0633S  2024/5/23 09:20   01F0681S   \n",
      "4           31  2024/5/23 09:39   01F0153N  2024/5/23 09:47   01F0061N   \n",
      "\n",
      "   TripLength TripEnd                                    TripInformation  \n",
      "0         6.8       Y                       2024-05-23 09:35:09+01F3535S  \n",
      "1         4.5       Y  2024-05-23 09:00:53+01F0532S; 2024-05-23 09:03...  \n",
      "2         3.5       Y                       2024-05-23 09:02:51+03F0447S  \n",
      "3         6.8       Y  2024-05-23 09:15:58+01F0633S; 2024-05-23 09:18...  \n",
      "4        11.8       Y  2024-05-23 09:39:09+01F0153N; 2024-05-23 09:39...  \n",
      "    VehicleType  DetectionTime_O GantryID_O  DetectionTime_D GantryID_D  \\\n",
      "2            32  2024/5/23 09:02   03F0447S  2024/5/23 09:02   03F0447S   \n",
      "5            32  2024/5/23 09:01   03F2260N  2024/5/23 09:08   03F2125N   \n",
      "6            32  2024/5/23 09:57   03F2129S  2024/5/23 10:04   03F2261S   \n",
      "7            31  2024/5/23 09:41   03F1128N  2024/5/23 09:41   03F1128N   \n",
      "12           31  2024/5/23 09:34   03F4232N  2024/5/23 09:37   03F4168N   \n",
      "\n",
      "    TripLength TripEnd                                    TripInformation  \n",
      "2          3.5       Y                       2024-05-23 09:02:51+03F0447S  \n",
      "5         17.3       Y  2024-05-23 09:01:01+03F2260N; 2024-05-23 09:02...  \n",
      "6         17.3       Y  2024-05-23 09:57:03+03F2129S; 2024-05-23 09:58...  \n",
      "7          5.4       Y                       2024-05-23 09:41:38+03F1128N  \n",
      "12         9.3       Y  2024-05-23 09:34:34+03F4232N; 2024-05-23 09:37...  \n"
     ]
    }
   ],
   "source": [
    "file_path = 'TDCS_M06A_20240523_090000.csv'\n",
    "\n",
    "# 讀取CSV文件的前100行\n",
    "df = pd.read_csv(file_path, nrows=100)\n",
    "\n",
    "# 打印前5行數據進行檢查\n",
    "print(df.head())\n",
    "\n",
    "f03_df = df[df['GantryID_O'].astype(str).str.startswith('03')]\n",
    "\n",
    "print(f03_df.head())\n"
   ]
  },
  {
   "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>VehicleType</th>\n",
       "      <th>DetectionTime_O</th>\n",
       "      <th>GantryID_O</th>\n",
       "      <th>DetectionTime_D</th>\n",
       "      <th>GantryID_D</th>\n",
       "      <th>TripLength</th>\n",
       "      <th>TripEnd</th>\n",
       "      <th>TripInformation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>32</td>\n",
       "      <td>2024/5/23 09:02</td>\n",
       "      <td>03F0447S</td>\n",
       "      <td>2024/5/23 09:02</td>\n",
       "      <td>03F0447S</td>\n",
       "      <td>3.5</td>\n",
       "      <td>Y</td>\n",
       "      <td>2024-05-23 09:02:51+03F0447S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>32</td>\n",
       "      <td>2024/5/23 09:01</td>\n",
       "      <td>03F2260N</td>\n",
       "      <td>2024/5/23 09:08</td>\n",
       "      <td>03F2125N</td>\n",
       "      <td>17.3</td>\n",
       "      <td>Y</td>\n",
       "      <td>2024-05-23 09:01:01+03F2260N; 2024-05-23 09:02...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>32</td>\n",
       "      <td>2024/5/23 09:57</td>\n",
       "      <td>03F2129S</td>\n",
       "      <td>2024/5/23 10:04</td>\n",
       "      <td>03F2261S</td>\n",
       "      <td>17.3</td>\n",
       "      <td>Y</td>\n",
       "      <td>2024-05-23 09:57:03+03F2129S; 2024-05-23 09:58...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>31</td>\n",
       "      <td>2024/5/23 09:41</td>\n",
       "      <td>03F1128N</td>\n",
       "      <td>2024/5/23 09:41</td>\n",
       "      <td>03F1128N</td>\n",
       "      <td>5.4</td>\n",
       "      <td>Y</td>\n",
       "      <td>2024-05-23 09:41:38+03F1128N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>31</td>\n",
       "      <td>2024/5/23 09:34</td>\n",
       "      <td>03F4232N</td>\n",
       "      <td>2024/5/23 09:37</td>\n",
       "      <td>03F4168N</td>\n",
       "      <td>9.3</td>\n",
       "      <td>Y</td>\n",
       "      <td>2024-05-23 09:34:34+03F4232N; 2024-05-23 09:37...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    VehicleType  DetectionTime_O GantryID_O  DetectionTime_D GantryID_D  \\\n",
       "2            32  2024/5/23 09:02   03F0447S  2024/5/23 09:02   03F0447S   \n",
       "5            32  2024/5/23 09:01   03F2260N  2024/5/23 09:08   03F2125N   \n",
       "6            32  2024/5/23 09:57   03F2129S  2024/5/23 10:04   03F2261S   \n",
       "7            31  2024/5/23 09:41   03F1128N  2024/5/23 09:41   03F1128N   \n",
       "12           31  2024/5/23 09:34   03F4232N  2024/5/23 09:37   03F4168N   \n",
       "\n",
       "    TripLength TripEnd                                    TripInformation  \n",
       "2          3.5       Y                       2024-05-23 09:02:51+03F0447S  \n",
       "5         17.3       Y  2024-05-23 09:01:01+03F2260N; 2024-05-23 09:02...  \n",
       "6         17.3       Y  2024-05-23 09:57:03+03F2129S; 2024-05-23 09:58...  \n",
       "7          5.4       Y                       2024-05-23 09:41:38+03F1128N  \n",
       "12         9.3       Y  2024-05-23 09:34:34+03F4232N; 2024-05-23 09:37...  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "f03_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 將拆分後的列名重新命名\n",
    "f03_df.loc[:, 'TripInformation'] = f03_df['TripInformation'].apply(lambda x: x.split('; '))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>VehicleType</th>\n",
       "      <th>DetectionTime_O</th>\n",
       "      <th>GantryID_O</th>\n",
       "      <th>DetectionTime_D</th>\n",
       "      <th>GantryID_D</th>\n",
       "      <th>TripLength</th>\n",
       "      <th>TripEnd</th>\n",
       "      <th>TripInformation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>32</td>\n",
       "      <td>2024/5/23 09:02</td>\n",
       "      <td>03F0447S</td>\n",
       "      <td>2024/5/23 09:02</td>\n",
       "      <td>03F0447S</td>\n",
       "      <td>3.5</td>\n",
       "      <td>Y</td>\n",
       "      <td>[2024-05-23 09:02:51+03F0447S]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>32</td>\n",
       "      <td>2024/5/23 09:01</td>\n",
       "      <td>03F2260N</td>\n",
       "      <td>2024/5/23 09:08</td>\n",
       "      <td>03F2125N</td>\n",
       "      <td>17.3</td>\n",
       "      <td>Y</td>\n",
       "      <td>[2024-05-23 09:01:01+03F2260N, 2024-05-23 09:0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>32</td>\n",
       "      <td>2024/5/23 09:57</td>\n",
       "      <td>03F2129S</td>\n",
       "      <td>2024/5/23 10:04</td>\n",
       "      <td>03F2261S</td>\n",
       "      <td>17.3</td>\n",
       "      <td>Y</td>\n",
       "      <td>[2024-05-23 09:57:03+03F2129S, 2024-05-23 09:5...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>31</td>\n",
       "      <td>2024/5/23 09:41</td>\n",
       "      <td>03F1128N</td>\n",
       "      <td>2024/5/23 09:41</td>\n",
       "      <td>03F1128N</td>\n",
       "      <td>5.4</td>\n",
       "      <td>Y</td>\n",
       "      <td>[2024-05-23 09:41:38+03F1128N]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>31</td>\n",
       "      <td>2024/5/23 09:34</td>\n",
       "      <td>03F4232N</td>\n",
       "      <td>2024/5/23 09:37</td>\n",
       "      <td>03F4168N</td>\n",
       "      <td>9.3</td>\n",
       "      <td>Y</td>\n",
       "      <td>[2024-05-23 09:34:34+03F4232N, 2024-05-23 09:3...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    VehicleType  DetectionTime_O GantryID_O  DetectionTime_D GantryID_D  \\\n",
       "2            32  2024/5/23 09:02   03F0447S  2024/5/23 09:02   03F0447S   \n",
       "5            32  2024/5/23 09:01   03F2260N  2024/5/23 09:08   03F2125N   \n",
       "6            32  2024/5/23 09:57   03F2129S  2024/5/23 10:04   03F2261S   \n",
       "7            31  2024/5/23 09:41   03F1128N  2024/5/23 09:41   03F1128N   \n",
       "12           31  2024/5/23 09:34   03F4232N  2024/5/23 09:37   03F4168N   \n",
       "\n",
       "    TripLength TripEnd                                    TripInformation  \n",
       "2          3.5       Y                     [2024-05-23 09:02:51+03F0447S]  \n",
       "5         17.3       Y  [2024-05-23 09:01:01+03F2260N, 2024-05-23 09:0...  \n",
       "6         17.3       Y  [2024-05-23 09:57:03+03F2129S, 2024-05-23 09:5...  \n",
       "7          5.4       Y                     [2024-05-23 09:41:38+03F1128N]  \n",
       "12         9.3       Y  [2024-05-23 09:34:34+03F4232N, 2024-05-23 09:3...  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "f03_df.head()"
   ]
  }
 ],
 "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.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}