coach_2019042702.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from pathlib import Path\n",
    "import collections \n",
    "import numpy as np\n",
    "import json\n",
    "import dask.dataframe as dd\n",
    "import time\n",
    "import zipfile as zfile\n",
    "import threading\n",
    "import datetime as dt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['CC.csv',\n",
       " 'CC.SQL',\n",
       " 'GPS.SQL',\n",
       " 'GPSS1.CSV',\n",
       " 'TT.SQL',\n",
       " 'TTS1.csv',\n",
       " '中華郵政大數據競賽資料欄位規格.xlsx',\n",
       " '中華郵政大數據競賽資料欄位規格_新.xlsx',\n",
       " 'ORACLE相關/',\n",
       " 'ORACLE相關/ACC_Oracle.SQL',\n",
       " 'ORACLE相關/CC_Oracle.SQL',\n",
       " 'ORACLE相關/createDB.txt',\n",
       " 'ORACLE相關/GPS_Oracle.SQL',\n",
       " 'ORACLE相關/sqlldr.exe',\n",
       " 'ORACLE相關/TTS1.CTL',\n",
       " 'ORACLE相關/TT_Oracle.SQL',\n",
       " '培訓暨說明會 - 簡報檔/',\n",
       " '培訓暨說明會 - 簡報檔/1.競賽辦法說明.pdf',\n",
       " '培訓暨說明會 - 簡報檔/2.競賽資料說明-1.pdf',\n",
       " '培訓暨說明會 - 簡報檔/2.競賽資料說明-2(中華郵政大數據競賽資料欄位規格).xlsx',\n",
       " '培訓暨說明會 - 簡報檔/3.郵務知識培訓-1.pdf',\n",
       " '培訓暨說明會 - 簡報檔/3.郵務知識培訓-2.pdf',\n",
       " '培訓暨說明會 - 簡報檔/4.微軟Power BI分析軟體介紹.pdf',\n",
       " '培訓暨說明會 - 簡報檔/5.競賽命題及方向之工研院楊奇達經理建議.pdf',\n",
       " '培訓暨說明會 - 簡報檔/5.競賽命題及方向之高端訓博士建議.pdf',\n",
       " 'ACC.SQL',\n",
       " 'ACCS1.csv']"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 取得壓縮檔內的檔名\n",
    "\n",
    "df_fn = []\n",
    "zf = zfile.ZipFile('../POSTDB.zip')\n",
    "for fname in zfile.ZipFile.namelist(zf):\n",
    "    df_fn.append(fname.encode('cp437').decode('big5'))\n",
    "df_fn"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 由壓縮檔取得所需要資料檔\n",
    "\n",
    "zf = zfile.ZipFile('../POSTDB.zip')\n",
    "date_spec = {'Mail_datetime': [2, 3]}\n",
    "# dateparse = lambda x: pd.datetime.strptime(\"Mail_date\" + \" \" + \"Mail_time\", '%Y-%m-%d %H:%M:%S')\n",
    "# mydateparser = lambda x: pd.datetime.strptime(date_spec, \"%Y-%m-%d %H:%M:%S\")\n",
    "dateparse = lambda x: pd.to_datetime('Mail_datetime', format='%Y-%m-%d %H:%M:%S',errors='ignore')\n",
    "df = pd.read_csv(zf.open('TTS1.csv'), # 檔案位置\n",
    "                 header=None, # 首行是否有欄位名稱\n",
    "                 encoding=\"big5\", # 編碼\n",
    "                 dtype = \"str\", # 資料類型\n",
    "                 nrows = 10000000, #讀取行數\n",
    "                 names = [\"Status_code\", \"Mail_num\", \"Mail_date\", \"Mail_time\", \"OP_office\", \"other\"] # 欄位名稱\n",
    "                )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>Mail_num</th>\n",
       "      <th>counts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00000000000000</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3304399</th>\n",
       "      <td>80077700104470</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2753568</th>\n",
       "      <td>38163710006817222005</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1388756</th>\n",
       "      <td>06668100104770</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>928879</th>\n",
       "      <td>03258850000070</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>928897</th>\n",
       "      <td>03258950000070</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>928915</th>\n",
       "      <td>03259050000070</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3272586</th>\n",
       "      <td>76800700103870</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4051888</th>\n",
       "      <td>95634500103370</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3070546</th>\n",
       "      <td>59033110009118</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155971</th>\n",
       "      <td>00333773400028</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2606041</th>\n",
       "      <td>31025710045130701000</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3103177</th>\n",
       "      <td>60741220403918</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2832965</th>\n",
       "      <td>40779110003718</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4007416</th>\n",
       "      <td>95238632000110505004</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>680888</th>\n",
       "      <td>02037360303716</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4071267</th>\n",
       "      <td>95861373000010231003</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11364</th>\n",
       "      <td>00014132000020</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11304</th>\n",
       "      <td>00014032000020</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11243</th>\n",
       "      <td>00013932000020</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11185</th>\n",
       "      <td>00013832000020</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>178633</th>\n",
       "      <td>00396433000020</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1832829</th>\n",
       "      <td>11962910121818</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1841842</th>\n",
       "      <td>12118810121818</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155943</th>\n",
       "      <td>00333673400028</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2832968</th>\n",
       "      <td>40779210003718</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2978615</th>\n",
       "      <td>51785310009118</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1967075</th>\n",
       "      <td>14163933404416</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1798084</th>\n",
       "      <td>11492040036810</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3031080</th>\n",
       "      <td>55004024105618</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2616397</th>\n",
       "      <td>31510010602718</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2616432</th>\n",
       "      <td>31512410001018</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2616434</th>\n",
       "      <td>31512510001018</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2616436</th>\n",
       "      <td>31512610001018</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2616455</th>\n",
       "      <td>31513910001018</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>434454</th>\n",
       "      <td>01146670602718</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2616475</th>\n",
       "      <td>31514910001018</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2616473</th>\n",
       "      <td>31514810001018</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2616471</th>\n",
       "      <td>31514710001018</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2616469</th>\n",
       "      <td>31514610001018</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2616467</th>\n",
       "      <td>31514510001018</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2616465</th>\n",
       "      <td>31514410001018</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2616463</th>\n",
       "      <td>31514310001018</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2616461</th>\n",
       "      <td>31514210001018</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2616459</th>\n",
       "      <td>31514110001018</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2616457</th>\n",
       "      <td>31514010001018</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2616454</th>\n",
       "      <td>31513840206518</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2616438</th>\n",
       "      <td>31512710001018</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2616452</th>\n",
       "      <td>31513810001018</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2616450</th>\n",
       "      <td>31513710001018</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2616449</th>\n",
       "      <td>31513610001018</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2616448</th>\n",
       "      <td>31513510001018</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2616447</th>\n",
       "      <td>31513410001018</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2616446</th>\n",
       "      <td>31513310001018</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2616445</th>\n",
       "      <td>31513300104170</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2616443</th>\n",
       "      <td>31513110001018</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2616442</th>\n",
       "      <td>31513010001018</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2616441</th>\n",
       "      <td>31512910001018</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2616440</th>\n",
       "      <td>31512810001018</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4419943</th>\n",
       "      <td>99999924104301</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4419944 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     Mail_num  counts\n",
       "0        00000000000000            23\n",
       "3304399  80077700104470            22\n",
       "2753568  38163710006817222005      15\n",
       "1388756  06668100104770            12\n",
       "928879   03258850000070            12\n",
       "928897   03258950000070            12\n",
       "928915   03259050000070            12\n",
       "3272586  76800700103870            12\n",
       "4051888  95634500103370            12\n",
       "3070546  59033110009118            11\n",
       "155971   00333773400028            11\n",
       "2606041  31025710045130701000      11\n",
       "3103177  60741220403918            11\n",
       "2832965  40779110003718            11\n",
       "4007416  95238632000110505004      11\n",
       "680888   02037360303716            11\n",
       "4071267  95861373000010231003      11\n",
       "11364    00014132000020            11\n",
       "11304    00014032000020            11\n",
       "11243    00013932000020            11\n",
       "11185    00013832000020            11\n",
       "178633   00396433000020            11\n",
       "1832829  11962910121818            11\n",
       "1841842  12118810121818            11\n",
       "155943   00333673400028            11\n",
       "2832968  40779210003718            11\n",
       "2978615  51785310009118            11\n",
       "1967075  14163933404416            11\n",
       "1798084  11492040036810            11\n",
       "3031080  55004024105618            10\n",
       "...                       ...     ...\n",
       "2616397  31510010602718             1\n",
       "2616432  31512410001018             1\n",
       "2616434  31512510001018             1\n",
       "2616436  31512610001018             1\n",
       "2616455  31513910001018             1\n",
       "434454   01146670602718             1\n",
       "2616475  31514910001018             1\n",
       "2616473  31514810001018             1\n",
       "2616471  31514710001018             1\n",
       "2616469  31514610001018             1\n",
       "2616467  31514510001018             1\n",
       "2616465  31514410001018             1\n",
       "2616463  31514310001018             1\n",
       "2616461  31514210001018             1\n",
       "2616459  31514110001018             1\n",
       "2616457  31514010001018             1\n",
       "2616454  31513840206518             1\n",
       "2616438  31512710001018             1\n",
       "2616452  31513810001018             1\n",
       "2616450  31513710001018             1\n",
       "2616449  31513610001018             1\n",
       "2616448  31513510001018             1\n",
       "2616447  31513410001018             1\n",
       "2616446  31513310001018             1\n",
       "2616445  31513300104170             1\n",
       "2616443  31513110001018             1\n",
       "2616442  31513010001018             1\n",
       "2616441  31512910001018             1\n",
       "2616440  31512810001018             1\n",
       "4419943  99999924104301             1\n",
       "\n",
       "[4419944 rows x 2 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby(['Mail_num']).size().reset_index(name='counts').sort_values(['counts'], ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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>Status_code</th>\n",
       "      <th>Mail_num</th>\n",
       "      <th>Mail_date</th>\n",
       "      <th>Mail_time</th>\n",
       "      <th>OP_office</th>\n",
       "      <th>other</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Y4</td>\n",
       "      <td>00000000000000</td>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>09:49:04</td>\n",
       "      <td>330031</td>\n",
       "      <td>003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Y4</td>\n",
       "      <td>00000000000000</td>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>09:58:08</td>\n",
       "      <td>330031</td>\n",
       "      <td>002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>I4</td>\n",
       "      <td>00000000000000</td>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>14:11:51</td>\n",
       "      <td>330031</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Y4</td>\n",
       "      <td>00000000000000</td>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>14:23:32</td>\n",
       "      <td>330031</td>\n",
       "      <td>006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>I4</td>\n",
       "      <td>00000000000000</td>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>14:52:09</td>\n",
       "      <td>330031</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Status_code              Mail_num   Mail_date Mail_time OP_office  \\\n",
       "0          Y4  00000000000000        2018-01-01  09:49:04    330031   \n",
       "1          Y4  00000000000000        2018-01-01  09:58:08    330031   \n",
       "2          I4  00000000000000        2018-01-01  14:11:51    330031   \n",
       "3          Y4  00000000000000        2018-01-01  14:23:32    330031   \n",
       "4          I4  00000000000000        2018-01-01  14:52:09    330031   \n",
       "\n",
       "                                        other  \n",
       "0        003                                   \n",
       "1        002                                   \n",
       "2                                              \n",
       "3        006                                   \n",
       "4                                              "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'str'>\n"
     ]
    }
   ],
   "source": [
    "print(type(df.columns[0]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "# TT1 郵件狀態代碼 X(2) | Status_code\n",
    "# TT2 掛號號碼 X(20) | Mail_num\n",
    "# TT3 處理日期 X(10) | Mail_date\n",
    "# TT4 處理時間 X(8) | Mail_time\n",
    "# TT5 處理局號 X(6) | OP_office // operation office\n",
    "# TT6 其它 X(42) | other\n",
    "\n",
    "df.columns = [\"Status_code\", \"Mail_num\", \"Mail_date\", \"Mail_time\", \"OP_office\", \"other\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 定義：狀態碼為 **點**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "nodes = list(df.Status_code.unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "head_nodes = sorted(list(set([ node[0] for node in nodes])))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "dic_nodes  = dict( zip(head_nodes, range(len(head_nodes))) )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "label_node = lambda x: dic_nodes[x[0]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# 只有狀態為節點\n",
    "data_nodes = []\n",
    "for node in nodes:\n",
    "    data = { \"id\": node, \"group\": label_node(node)}\n",
    "    data_nodes.append(data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 定義：**線**，狀態的改變\n",
    "- 要依據狀態、郵件號碼及時間去決定**線** 的連接\n",
    "- 時間的轉換，請參考 [pandas.to_datetime()](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.to_datetime.html)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Mail_datetime'] = df.Mail_date+\" \"+df.Mail_time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.Mail_datetime = pd.to_datetime(df.Mail_datetime)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "all_mail[\"58668700100170\"] = [ (\"Y4\", \"2018-01-01 09:49:04\"),\n",
    "                           (\"I4\", \"2018-01-01 14:11:51\"), ... ] \n",
    "\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Code_OP'] = df.Status_code + \"-\" + df.OP_office.map(str) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_nodes = []\n",
    "for node in df['Code_OP'].unique():\n",
    "    data = { \"id\": node, \"group\": label_node(node)}\n",
    "    data_nodes.append(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "處理  10000000 筆資料，組成  4419944  筆資料，共執行： 765.3743736743927 秒\n"
     ]
    }
   ],
   "source": [
    "start_time = time.time()\n",
    "all_mail = {}\n",
    "all_mail_key = set()\n",
    "for idx, row in df.iterrows():\n",
    "    mail_code = row.Mail_num.strip()\n",
    "    if not mail_code in all_mail_key:\n",
    "        all_mail[mail_code] = []\n",
    "        all_mail_key.add(mail_code)\n",
    "        \n",
    "    all_mail[mail_code].append( (row.Code_OP, row.Mail_datetime) )\n",
    "    \n",
    "\n",
    "time_diff = time.time() - start_time\n",
    "print(\"處理 \",len(df), \"筆資料，組成 \", len(all_mail.keys()), \" 筆資料，共執行：\", time_diff, \"秒\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4419944"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(all_mail.keys())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('Y4-500584', Timestamp('2018-01-02 07:50:39')),\n",
       " ('H4-500584', Timestamp('2018-01-02 14:06:59')),\n",
       " ('Z2-500584', Timestamp('2018-01-02 16:34:28'))]"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_mail['00120000014101061010']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('Y4-540028', 'Y4-540028'),\n",
       " ('Y4-540028', 'I4-540028'),\n",
       " ('I4-540028', 'I4-540028')]"
      ]
     },
     "execution_count": 131,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def convert_2_edge(mail_status):\n",
    "    edges = []\n",
    "    for idx in range(len(mail_status)-1):\n",
    "        edges.append( ( mail_status[idx][0], mail_status[idx+1][0]) )\n",
    "        \n",
    "    return edges\n",
    "# \"Y4\" -> \"H4\", \n",
    "# \"H4\" -> \"Z2\"\n",
    "\n",
    "mail_status = all_mail['96410700000070']\n",
    "convert_2_edge(mail_status)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1133859"
      ]
     },
     "execution_count": 151,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_edges = [] \n",
    "for mail_code in all_mail:\n",
    "    status_num = len(all_mail[mail_code])\n",
    "    \n",
    "    if (status_num) > 1:\n",
    "        mail_status = all_mail[mail_code]\n",
    "        all_edges.extend(convert_2_edge(mail_status))\n",
    "\n",
    "value_list = {}\n",
    "for edge, value in collections.Counter(all_edges).most_common(1000):\n",
    "    value_list[edge] = value\n",
    "\n",
    "list_max = np.max(list(value_list.values()))\n",
    "list_min = np.min(list(value_list.values()))\n",
    "list_diff = float(list_max - list_min)\n",
    "\n",
    "k = 20\n",
    "\n",
    "normal_val = lambda x: int(k/3+k*2*(x-list_min)/list_diff)\n",
    "\n",
    "data_edges = []\n",
    "for edge, value in collections.Counter(all_edges).most_common(1000):\n",
    "    edge = {\"source\": edge[0], \"target\":edge[1], \"value\":normal_val(value)}\n",
    "    data_edges.append(edge)\n",
    "\n",
    "all_data = { \"nodes\":data_nodes, \"links\":data_edges}\n",
    "\n",
    "open(\"data_4_d3.json\", 'w').write(json.dumps(all_data, indent=2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.7.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}