generate-parquet.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "0e045ec6",
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "import re\n",
    "import pandas as pd\n",
    "import configparser\n",
    "import os\n",
    "\n",
    "import ssl\n",
    "ssl._create_default_https_context = ssl._create_unverified_context   "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a7cad3f5",
   "metadata": {},
   "source": [
    "# Traffic Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "12840453",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_mrt_traffic_data_link():\n",
    "    \n",
    "    url = 'https://data.taipei/api/dataset/' \\\n",
    "      '63f31c7e-7fc3-418b-bd82-b95158755b4d' \\\n",
    "      '/resource/eb481f58-1238-4cff-8caa-fa7bb20cb4f4/download'\n",
    "\n",
    "    # 設定每個cell可顯示字串長度\n",
    "    # default = 50\n",
    "    pd.options.display.max_colwidth = 400\n",
    "    return pd.read_csv(url)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "1b763911",
   "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>年月</th>\n",
       "      <th>資料路徑</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>202204</td>\n",
       "      <td>http://tcgmetro.blob.core.windows.net/stationod/%E8%87%BA%E5%8C%97%E6%8D%B7%E9%81%8B%E6%AF%8F%E6%97%A5%E5%88%86%E6%99%82%E5%90%84%E7%AB%99OD%E6%B5%81%E9%87%8F%E7%B5%B1%E8%A8%88%E8%B3%87%E6%96%99_202204.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>202205</td>\n",
       "      <td>http://tcgmetro.blob.core.windows.net/stationod/%E8%87%BA%E5%8C%97%E6%8D%B7%E9%81%8B%E6%AF%8F%E6%97%A5%E5%88%86%E6%99%82%E5%90%84%E7%AB%99OD%E6%B5%81%E9%87%8F%E7%B5%B1%E8%A8%88%E8%B3%87%E6%96%99_202205.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>202206</td>\n",
       "      <td>http://tcgmetro.blob.core.windows.net/stationod/%E8%87%BA%E5%8C%97%E6%8D%B7%E9%81%8B%E6%AF%8F%E6%97%A5%E5%88%86%E6%99%82%E5%90%84%E7%AB%99OD%E6%B5%81%E9%87%8F%E7%B5%B1%E8%A8%88%E8%B3%87%E6%96%99_202206.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>202207</td>\n",
       "      <td>http://tcgmetro.blob.core.windows.net/stationod/%E8%87%BA%E5%8C%97%E6%8D%B7%E9%81%8B%E6%AF%8F%E6%97%A5%E5%88%86%E6%99%82%E5%90%84%E7%AB%99OD%E6%B5%81%E9%87%8F%E7%B5%B1%E8%A8%88%E8%B3%87%E6%96%99_202207.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>202208</td>\n",
       "      <td>http://tcgmetro.blob.core.windows.net/stationod/%E8%87%BA%E5%8C%97%E6%8D%B7%E9%81%8B%E6%AF%8F%E6%97%A5%E5%88%86%E6%99%82%E5%90%84%E7%AB%99OD%E6%B5%81%E9%87%8F%E7%B5%B1%E8%A8%88%E8%B3%87%E6%96%99_202208.csv</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        年月  \\\n",
       "63  202204   \n",
       "64  202205   \n",
       "65  202206   \n",
       "66  202207   \n",
       "67  202208   \n",
       "\n",
       "                                                                                                                                                                                                             資料路徑  \n",
       "63  http://tcgmetro.blob.core.windows.net/stationod/%E8%87%BA%E5%8C%97%E6%8D%B7%E9%81%8B%E6%AF%8F%E6%97%A5%E5%88%86%E6%99%82%E5%90%84%E7%AB%99OD%E6%B5%81%E9%87%8F%E7%B5%B1%E8%A8%88%E8%B3%87%E6%96%99_202204.csv  \n",
       "64  http://tcgmetro.blob.core.windows.net/stationod/%E8%87%BA%E5%8C%97%E6%8D%B7%E9%81%8B%E6%AF%8F%E6%97%A5%E5%88%86%E6%99%82%E5%90%84%E7%AB%99OD%E6%B5%81%E9%87%8F%E7%B5%B1%E8%A8%88%E8%B3%87%E6%96%99_202205.csv  \n",
       "65  http://tcgmetro.blob.core.windows.net/stationod/%E8%87%BA%E5%8C%97%E6%8D%B7%E9%81%8B%E6%AF%8F%E6%97%A5%E5%88%86%E6%99%82%E5%90%84%E7%AB%99OD%E6%B5%81%E9%87%8F%E7%B5%B1%E8%A8%88%E8%B3%87%E6%96%99_202206.csv  \n",
       "66  http://tcgmetro.blob.core.windows.net/stationod/%E8%87%BA%E5%8C%97%E6%8D%B7%E9%81%8B%E6%AF%8F%E6%97%A5%E5%88%86%E6%99%82%E5%90%84%E7%AB%99OD%E6%B5%81%E9%87%8F%E7%B5%B1%E8%A8%88%E8%B3%87%E6%96%99_202207.csv  \n",
       "67  http://tcgmetro.blob.core.windows.net/stationod/%E8%87%BA%E5%8C%97%E6%8D%B7%E9%81%8B%E6%AF%8F%E6%97%A5%E5%88%86%E6%99%82%E5%90%84%E7%AB%99OD%E6%B5%81%E9%87%8F%E7%B5%B1%E8%A8%88%E8%B3%87%E6%96%99_202208.csv  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_source = get_mrt_traffic_data_link()\n",
    "df_source.tail(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4988c482",
   "metadata": {},
   "source": [
    "## Create Data directory"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "a23f8b85",
   "metadata": {},
   "outputs": [],
   "source": [
    "directory = 'traffic_data'\n",
    "\n",
    "if not os.path.exists(directory):\n",
    "    os.makedirs(directory)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "18c17cb4",
   "metadata": {},
   "source": [
    "## Dowload Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "8698087c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "processing mrt traffic data 201701...\n",
      "CPU times: user 20.6 s, sys: 4.6 s, total: 25.2 s\n",
      "Wall time: 1min 59s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "for idx, row in df_source.iterrows():\n",
    "    print(f\"processing mrt traffic data {row[0]}...\")\n",
    "    df_traffic = pd.read_csv(row[1])\n",
    "\n",
    "    # get traffic of first n days only\n",
    "    df_traffic.to_csv(f'{directory}/臺北捷運每日分時各站OD流量統計資料_{row[0]}.csv', index=False)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a8b421ab",
   "metadata": {},
   "source": [
    "## transform from csv to parquet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "9efc5a5b",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "processing file 臺北捷運每日分時各站OD流量統計資料_201701.csv...\n"
     ]
    }
   ],
   "source": [
    "files = os.listdir(directory)\n",
    "\n",
    "for file in files:\n",
    "    if '臺北捷運每日分時各站OD流量統計資料' in file:\n",
    "        print(f'processing file {file}...')\n",
    "        file_date = file.split('_')[1].replace('.csv','')\n",
    "        cols = ['日期','時段','進站','出站','人次']\n",
    "        \n",
    "        df = pd.read_csv(f'{directory}/{file}', usecols=cols)\n",
    "        df = df.rename(columns={'日期': 'dt', \n",
    "                               '時段': 'hour',\n",
    "                               '進站': 'entrance',\n",
    "                               '出站': 'exit',\n",
    "                               '人次': 'traffic'})\n",
    "        df['dt'] = pd.to_datetime(df['dt'])\n",
    "        df.to_parquet(f'{directory}/mrt_q_{file_date}.parquet', index=False)\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d4122526",
   "metadata": {},
   "source": [
    "## create testing data (opt.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ce56c5b2",
   "metadata": {},
   "outputs": [],
   "source": [
    "for date in ['202201', '202202', '202203']:\n",
    "    print(f\"processing data of {date}...\")\n",
    "    df = pd.read_csv(f'{directory}/臺北捷運每日分時各站OD流量統計資料_{date}.csv')\n",
    "    df_traffic_test = df[df[\"日期\"].str[-2:]<=\"03\"].copy()\n",
    "    df_traffic_test['日期'].unique()\n",
    "    df = None\n",
    "    df_traffic_test.to_csv(f'{directory}/mrt_traffic_{date}.csv', 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.6.8"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}