openbus_23_splunk_data_prep.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-03-28T21:32:28.535887Z",
     "start_time": "2019-03-28T21:32:23.693270Z"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import partridge as ptg\n",
    "import datetime\n",
    "import numpy as np\n",
    "\n",
    "import gtfs_utils"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-03-28T21:32:28.551511Z",
     "start_time": "2019-03-28T21:32:28.535887Z"
    }
   },
   "outputs": [],
   "source": [
    "from os.path import join"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-03-28T21:32:28.582744Z",
     "start_time": "2019-03-28T21:32:28.551511Z"
    }
   },
   "outputs": [],
   "source": [
    "import re\n",
    "import datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-03-28T21:32:28.645231Z",
     "start_time": "2019-03-28T21:32:28.582744Z"
    }
   },
   "outputs": [],
   "source": [
    "def single_timestr_to_seconds(x, *, inverse=False, mod24=False, only_mins=False):\n",
    "    \"\"\"\n",
    "    Given an HH:MM:SS time string ``x``, return the number of seconds\n",
    "    past midnight that it represents.\n",
    "    In keeping with GTFS standards, the hours entry may be greater than\n",
    "    23.\n",
    "    If ``mod24``, then return the number of seconds modulo ``24*3600``.\n",
    "    If ``inverse``, then do the inverse operation.\n",
    "    In this case, if ``mod24`` also, then first take the number of\n",
    "    seconds modulo ``24*3600``.\n",
    "    \"\"\"\n",
    "    if not inverse:\n",
    "        try:\n",
    "            if not only_mins:\n",
    "                hours, mins, seconds = x.split(\":\")\n",
    "                result = int(hours) * 3600 + int(mins) * 60 + int(seconds)\n",
    "            else:\n",
    "                hours, mins = x.split(\":\")\n",
    "            if mod24:\n",
    "                result %= 24 * 3600\n",
    "        except:\n",
    "            result = np.nan\n",
    "    else:\n",
    "        try:\n",
    "            seconds = int(x)\n",
    "            if mod24:\n",
    "                seconds %= 24 * 3600\n",
    "            hours, remainder = divmod(seconds, 3600)\n",
    "            mins, secs = divmod(remainder, 60)\n",
    "            result = \"{:02d}:{:02d}:{:02d}\".format(hours, mins, secs)\n",
    "        except:\n",
    "            result = np.nan\n",
    "    return result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-03-28T21:32:28.676472Z",
     "start_time": "2019-03-28T21:32:28.645231Z"
    }
   },
   "outputs": [],
   "source": [
    "def timestr_to_seconds(x, *, only_mins=False):\n",
    "    try:\n",
    "        hms = x.str.split(':', expand=True)\n",
    "        if not only_mins:\n",
    "            result = hms.iloc[:,0].astype(int) * 3600 + hms.iloc[:,1].astype(int) * 60 + hms.iloc[:,2].astype(int)\n",
    "        else:\n",
    "            result = hms.iloc[:,0].astype(int) * 3600 + hms.iloc[:,1].astype(int) * 60\n",
    "    except:\n",
    "        result = np.nan\n",
    "\n",
    "    return result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-03-28T21:32:28.692091Z",
     "start_time": "2019-03-28T21:32:28.676472Z"
    }
   },
   "outputs": [],
   "source": [
    "FOLDER = 'data\\\\siri\\\\2018-10'\n",
    "file = 'siri_rt_data.2018-10-08.11.log.gz'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-03-28T21:32:28.723336Z",
     "start_time": "2019-03-28T21:32:28.692091Z"
    }
   },
   "outputs": [],
   "source": [
    "from glob import glob\n",
    "import os\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-03-28T21:32:28.785825Z",
     "start_time": "2019-03-28T21:32:28.723336Z"
    }
   },
   "outputs": [],
   "source": [
    "tf = r\"C:\\dev\\ds\\open-bus-explore\\data\\siri\\2018-11\\siri_rt_data.2018-11-20.0.log.gz\"\n",
    "def create_trip_df(path, drop=['timestamp', 'desc'], \n",
    "                   convert_timestr_to_seconds=True, add_date=True, \n",
    "                   add_trailing_zeros=True):\n",
    "    header = [\"timestamp\", \"desc\", \"agency_id\", \n",
    "              \"route_id\", \"route_short_name\", \"service_id\", \n",
    "              \"planned_start_time\", \"bus_id\", \"predicted_end_time\", \n",
    "              \"time_recorded\", \"lat\", \"lon\"]\n",
    "    date = datetime.datetime.strptime(re.findall('siri_rt_data\\\\.([^\\\\.]+)\\\\.\\\\d+\\\\.log', path)[0], '%Y-%m-%d')\n",
    "    df = pd.read_csv(path, header=None, error_bad_lines=False)\n",
    "    df.columns = header\n",
    "    if drop is not None:\n",
    "        df = df.drop(drop, axis=1)\n",
    "    df = (df.assign(agency_id = lambda x: x.agency_id.astype(int))\n",
    "              .assign(service_id = lambda x: x.service_id.astype(int))\n",
    "              .assign(route_id = lambda x: x.route_id.astype(int))\n",
    "              .assign(lat = lambda x: x.lat.astype(float))\n",
    "              .assign(lon = lambda x: x.lon.astype(float)))\n",
    "    if convert_timestr_to_seconds:\n",
    "        df = (df.assign(planned_start_time = lambda x: timestr_to_seconds(x.planned_start_time, only_mins=True))\n",
    "                .assign(predicted_end_time = lambda x: timestr_to_seconds(x.predicted_end_time, only_mins=True))\n",
    "                .assign(time_recorded = lambda x: timestr_to_seconds(x.time_recorded)))\n",
    "    if add_date:\n",
    "        df = (df.assign(date = date))\n",
    "    if add_trailing_zeros:\n",
    "        df = (df\n",
    "                .assign(planned_start_time = lambda x: x.planned_start_time+':00')\n",
    "                .assign(predicted_end_time = lambda x: x.predicted_end_time+':00'))\n",
    "    \n",
    "    return df\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-03-28T21:32:36.346575Z",
     "start_time": "2019-03-28T21:32:28.785825Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2019-03-26_route_stats\n",
      "(6651, 51)\n",
      "2019-03-27_route_stats\n",
      "(6667, 51)\n",
      "2019-03-28_route_stats\n",
      "(6742, 51)\n"
     ]
    }
   ],
   "source": [
    "from glob import glob\n",
    "import os\n",
    "out_folder = 'data\\gtfs_stats_csv_hack'\n",
    "gz_folder = 'data\\gtfs_stats_csv_gz_hack'\n",
    "#os.mkdir(out_folder)\n",
    "#os.mkdir(gz_folder)\n",
    "\n",
    "for file in glob('data\\gtfs_stats_hack\\*route_stats*'):\n",
    "    base = os.path.basename(file).split('.')[0]\n",
    "    out_path = os.path.join(out_folder, base+'.csv')\n",
    "    gz_out_path = os.path.join(gz_folder, base+'.csv.gz')\n",
    "    if not os.path.exists(gz_out_path):\n",
    "        print (base)\n",
    "        r = pd.read_pickle(file, compression='gzip')\n",
    "        print (r.shape)\n",
    "        #r.to_csv(out_path)\n",
    "        r.to_csv(gz_out_path, compression='gzip')\n",
    "        "
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-03-11T16:07:09.168062Z",
     "start_time": "2019-03-11T16:06:46.876438Z"
    }
   },
   "source": [
    "out_folder = 'data/trip_stats_csv'\n",
    "gz_folder = 'data/trip_stats_csv_gz_hack'\n",
    "#os.mkdir(out_folder)\n",
    "os.mkdir(gz_folder)\n",
    "\n",
    "for file in glob(r'data\\gtfs_stats_hack\\2019*trip_stats*'):\n",
    "    base = os.path.basename(file).split('.')[0]\n",
    "    out_path = os.path.join(out_folder, base+'.csv')\n",
    "    gz_out_path = os.path.join(gz_folder, base+'.csv.gz')\n",
    "    if not os.path.exists(gz_out_path):\n",
    "        print (base)\n",
    "        r = pd.read_pickle(file, compression='gzip')\n",
    "        print (r.shape)\n",
    "        #r.to_csv(out_path)\n",
    "        r.to_csv(gz_out_path, compression='gzip')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-12-17T18:08:52.460379Z",
     "start_time": "2018-12-17T18:08:48.898717Z"
    }
   },
   "outputs": [],
   "source": [
    "t = create_trip_df(tf, drop=['desc'], convert_timestr_to_seconds=False)\n",
    "t.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-12-03T14:51:37.153763Z",
     "start_time": "2018-12-03T13:54:40.519759Z"
    }
   },
   "outputs": [],
   "source": [
    "FOLDER = 'data\\\\siri\\\\2018-11'\n",
    "out_folder = 'data\\siri_csv_v2'\n",
    "if not os.path.exists(out_folder):\n",
    "    os.mkdir(out_folder)\n",
    "\n",
    "for file in glob(FOLDER+'/*.log.gz'):\n",
    "    print(file)\n",
    "    df = create_trip_df(file, drop=['desc'], convert_timestr_to_seconds=False)\n",
    "    base = '.'.join(os.path.basename(file).split('.')[:-2])\n",
    "\n",
    "    #df.to_parquet(bn + '.parq')\n",
    "    #os.remove(file)\n",
    "    out_path = os.path.join(out_folder, base+'.csv.gz')\n",
    "    df.to_csv(out_path, compression='gzip', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-12-04T10:45:32.823097Z",
     "start_time": "2018-12-04T10:45:32.651262Z"
    }
   },
   "outputs": [],
   "source": [
    "import sys\n",
    "sys.path.append('C:/dev/ds/open-bus-explore/open-bus/gtfs/retriever')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-12-04T10:45:34.385231Z",
     "start_time": "2018-12-04T10:45:33.244869Z"
    }
   },
   "outputs": [],
   "source": [
    "import s3_wrapper\n",
    "\n",
    "aki = 'P6OMDOFWYCQNTWE7XEPR'\n",
    "sak = 'glx9UFBOBNQCtYqSWIUW5OKWyhn9CedVb5tn7La5u6I'\n",
    "bucket = 'obus-do1'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-12-04T11:52:47.294360Z",
     "start_time": "2018-12-04T11:52:45.247967Z"
    }
   },
   "outputs": [],
   "source": [
    "crud = s3_wrapper.S3Crud(aki, sak, bucket)\n",
    "s3_wrapper.list_content(crud, regex_argument='(.*\\.2018-11-2[5-9]\\..*)')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-12-04T11:08:38.206158Z",
     "start_time": "2018-12-04T10:46:49.492574Z"
    }
   },
   "outputs": [],
   "source": [
    "FOLDER = 'data\\\\siri\\\\2018-11'\n",
    "#os.mkdir(FOLDER)\n",
    "for k in s3_wrapper.list_content(crud, regex_argument='(.*\\.2018-11-2[5-9]\\..*)'):\n",
    "    file_name = k.split('/')[-1]\n",
    "    output_path = os.path.join(FOLDER, file_name)\n",
    "    if not os.path.exists(output_path):\n",
    "        print (f'Downloading {file_name}')\n",
    "        s3_wrapper.download(crud, output_path, k)\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "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.6.3"
  },
  "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": 2
}