bixi_station.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas\n",
    "import json\n",
    "\n",
    "import zipfile\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "from io import BytesIO\n",
    "from zipfile import ZipFile\n",
    "from urllib.request import urlopen\n",
    "\n",
    "import re"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['2017/',\n",
       " '2017/OD_2017-04.csv',\n",
       " '2017/OD_2017-05.csv',\n",
       " '2017/OD_2017-06.csv',\n",
       " '2017/OD_2017-07.csv',\n",
       " '2017/OD_2017-08.csv',\n",
       " '2017/OD_2017-09.csv',\n",
       " '2017/OD_2017-10.csv',\n",
       " '2017/OD_2017-11.csv',\n",
       " '2017/Stations_2017.csv']"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "resp = urlopen('http://montreal.bixi.com/c/bixi/file_db/data_all.file/BixiMontrealRentals2017.zip')\n",
    "zipfiles = ZipFile(BytesIO(resp.read()))\n",
    "zipfiles.namelist()\n",
    "# ['bbc.classes', 'bbc.docs', 'bbc.mtx', 'bbc.terms']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2017/Stations_2017.csv\n"
     ]
    }
   ],
   "source": [
    "data_bixi_stations=[]\n",
    "\n",
    "pattern = re.compile(\"^[0-9]{4}/Stations_[0-9]{4}.csv$\")\n",
    "\n",
    "count=0;\n",
    "for file in (zipfiles.namelist()):\n",
    "    if(pattern.match(file)):\n",
    "        print(file)\n",
    "        if (count == 0):\n",
    "            data_bixi_stations = pd.read_csv(zipfiles.open(file))\n",
    "            count = 1\n",
    "        else:\n",
    "            my_df = pd.read_csv(zipfiles.open(file))\n",
    "            data_bixi_stations = (pd.DataFrame(np.concatenate((data_bixi_stations,my_df),axis=0), columns=list(my_df.columns.values)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "#data_frame = pandas.read_csv(\n",
    "#    './data_in/BixiMontrealRentals/2017/Stations_2017.csv',\n",
    "##    parse_dates=[\n",
    "##        'last_update',\n",
    "##        'input_date',\n",
    "##        'date_photo',\n",
    "##    ],\n",
    "#    infer_datetime_format=True,\n",
    "#    na_values=['']\n",
    "#)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code</th>\n",
       "      <th>name</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7015</td>\n",
       "      <td>LaSalle / 4e avenue</td>\n",
       "      <td>45.430740</td>\n",
       "      <td>-73.591911</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6714</td>\n",
       "      <td>LaSalle / Sénécal</td>\n",
       "      <td>45.434434</td>\n",
       "      <td>-73.586694</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6712</td>\n",
       "      <td>LaSalle / Crawford</td>\n",
       "      <td>45.437914</td>\n",
       "      <td>-73.582740</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>6715</td>\n",
       "      <td>Natatorium (LaSalle / Rolland)</td>\n",
       "      <td>45.444408</td>\n",
       "      <td>-73.575568</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7048</td>\n",
       "      <td>Métro Angrignon</td>\n",
       "      <td>45.446534</td>\n",
       "      <td>-73.603541</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   code                            name   latitude  longitude\n",
       "0  7015             LaSalle / 4e avenue  45.430740 -73.591911\n",
       "1  6714               LaSalle / Sénécal  45.434434 -73.586694\n",
       "2  6712              LaSalle / Crawford  45.437914 -73.582740\n",
       "3  6715  Natatorium (LaSalle / Rolland)  45.444408 -73.575568\n",
       "4  7048                 Métro Angrignon  45.446534 -73.603541"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_bixi_stations[data_bixi_stations.columns[:10]].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "code           int64\n",
       "name          object\n",
       "latitude     float64\n",
       "longitude    float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_bixi_stations.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "json_result_string = data_bixi_stations.to_json(\n",
    "    orient='records', \n",
    "    double_precision=12,\n",
    "    date_format='iso'\n",
    ")\n",
    "json_result = json.loads(json_result_string)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "geojson = {\n",
    "    'type': 'FeatureCollection',\n",
    "    'features': []\n",
    "}\n",
    "for record in json_result:\n",
    "    geojson['features'].append({\n",
    "        'type': 'Feature',\n",
    "        'id': record['code'],\n",
    "        'geometry': {\n",
    "            'type': 'Point',\n",
    "            'coordinates': [record['longitude'], record['latitude']],\n",
    "        },\n",
    "        'properties': record,\n",
    "    })"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "with open('./data_out/Stations_2017.geojson', 'w') as f:\n",
    "    f.write(json.dumps(geojson, indent=2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2017/OD_2017-04.csv\n",
      "2017/OD_2017-05.csv\n",
      "2017/OD_2017-06.csv\n",
      "2017/OD_2017-07.csv\n",
      "2017/OD_2017-08.csv\n",
      "2017/OD_2017-09.csv\n",
      "2017/OD_2017-10.csv\n",
      "2017/OD_2017-11.csv\n"
     ]
    }
   ],
   "source": [
    "data_bixi_concatenate=[]\n",
    "\n",
    "pattern = re.compile(\"^[0-9]{4}/OD_[0-9]{4}-[0-9]{2}.csv$\")\n",
    "\n",
    "count=0;\n",
    "for file in (zipfiles.namelist()):\n",
    "    if(pattern.match(file)):\n",
    "        print(file)\n",
    "        if (count == 0):\n",
    "            data_bixi_concatenate = pd.read_csv(zipfiles.open(file))\n",
    "            count = 1\n",
    "        else:\n",
    "            my_df = pd.read_csv(zipfiles.open(file))\n",
    "            data_bixi_concatenate = (pd.DataFrame(np.concatenate((data_bixi_concatenate,my_df),axis=0), columns=list(my_df.columns.values)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>start_date</th>\n",
       "      <th>start_station_code</th>\n",
       "      <th>end_date</th>\n",
       "      <th>end_station_code</th>\n",
       "      <th>duration_sec</th>\n",
       "      <th>is_member</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017-04-15 00:00</td>\n",
       "      <td>7060</td>\n",
       "      <td>2017-04-15 00:31</td>\n",
       "      <td>7060</td>\n",
       "      <td>1841</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017-04-15 00:01</td>\n",
       "      <td>6173</td>\n",
       "      <td>2017-04-15 00:10</td>\n",
       "      <td>6173</td>\n",
       "      <td>553</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017-04-15 00:01</td>\n",
       "      <td>6203</td>\n",
       "      <td>2017-04-15 00:04</td>\n",
       "      <td>6204</td>\n",
       "      <td>195</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017-04-15 00:01</td>\n",
       "      <td>6104</td>\n",
       "      <td>2017-04-15 00:06</td>\n",
       "      <td>6114</td>\n",
       "      <td>285</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017-04-15 00:01</td>\n",
       "      <td>6174</td>\n",
       "      <td>2017-04-15 00:11</td>\n",
       "      <td>6174</td>\n",
       "      <td>569</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         start_date start_station_code          end_date end_station_code  \\\n",
       "0  2017-04-15 00:00               7060  2017-04-15 00:31             7060   \n",
       "1  2017-04-15 00:01               6173  2017-04-15 00:10             6173   \n",
       "2  2017-04-15 00:01               6203  2017-04-15 00:04             6204   \n",
       "3  2017-04-15 00:01               6104  2017-04-15 00:06             6114   \n",
       "4  2017-04-15 00:01               6174  2017-04-15 00:11             6174   \n",
       "\n",
       "  duration_sec is_member  \n",
       "0         1841         1  \n",
       "1          553         1  \n",
       "2          195         1  \n",
       "3          285         1  \n",
       "4          569         1  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_bixi_concatenate[data_bixi_concatenate.columns[:10]].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>start_date</th>\n",
       "      <th>start_station_code</th>\n",
       "      <th>end_date</th>\n",
       "      <th>end_station_code</th>\n",
       "      <th>duration_sec</th>\n",
       "      <th>is_member</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4740352</th>\n",
       "      <td>2017-11-15 23:57</td>\n",
       "      <td>6182</td>\n",
       "      <td>2017-11-16 00:11</td>\n",
       "      <td>6159</td>\n",
       "      <td>840</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4740353</th>\n",
       "      <td>2017-11-15 23:58</td>\n",
       "      <td>6013</td>\n",
       "      <td>2017-11-16 00:04</td>\n",
       "      <td>6023</td>\n",
       "      <td>363</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4740354</th>\n",
       "      <td>2017-11-15 23:58</td>\n",
       "      <td>6128</td>\n",
       "      <td>2017-11-16 00:15</td>\n",
       "      <td>6411</td>\n",
       "      <td>1014</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4740355</th>\n",
       "      <td>2017-11-15 23:58</td>\n",
       "      <td>6748</td>\n",
       "      <td>2017-11-16 00:27</td>\n",
       "      <td>6349</td>\n",
       "      <td>1752</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4740356</th>\n",
       "      <td>2017-11-15 23:59</td>\n",
       "      <td>6112</td>\n",
       "      <td>2017-11-16 00:06</td>\n",
       "      <td>6173</td>\n",
       "      <td>460</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               start_date start_station_code          end_date  \\\n",
       "4740352  2017-11-15 23:57               6182  2017-11-16 00:11   \n",
       "4740353  2017-11-15 23:58               6013  2017-11-16 00:04   \n",
       "4740354  2017-11-15 23:58               6128  2017-11-16 00:15   \n",
       "4740355  2017-11-15 23:58               6748  2017-11-16 00:27   \n",
       "4740356  2017-11-15 23:59               6112  2017-11-16 00:06   \n",
       "\n",
       "        end_station_code duration_sec is_member  \n",
       "4740352             6159          840         0  \n",
       "4740353             6023          363         1  \n",
       "4740354             6411         1014         1  \n",
       "4740355             6349         1752         1  \n",
       "4740356             6173          460         1  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_bixi_concatenate[data_bixi_concatenate.columns[:10]].tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\chris\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  \n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>start_station_code</th>\n",
       "      <th>end_station_code</th>\n",
       "      <th>flow</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7060</td>\n",
       "      <td>7060</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6173</td>\n",
       "      <td>6173</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6203</td>\n",
       "      <td>6204</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>6104</td>\n",
       "      <td>6114</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6174</td>\n",
       "      <td>6174</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  start_station_code end_station_code  flow\n",
       "0               7060             7060     1\n",
       "1               6173             6173     1\n",
       "2               6203             6204     1\n",
       "3               6104             6114     1\n",
       "4               6174             6174     1"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_bixi_concatenate_flow = data_bixi_concatenate[['start_station_code','end_station_code']]\n",
    "data_bixi_concatenate_flow['flow']=1\n",
    "data_bixi_concatenate_flow[data_bixi_concatenate_flow.columns[:10]].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "col_flow = data_bixi_concatenate_flow.groupby(['start_station_code','end_station_code'])['flow'].sum() #multiple index serie\n",
    "\n",
    "#make dataframe from preceding multiple index serie\n",
    "df_flow=pd.DataFrame(col_flow)\n",
    "df_flow.reset_index(inplace=True) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>start_station_code</th>\n",
       "      <th>end_station_code</th>\n",
       "      <th>flow</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>194173</th>\n",
       "      <td>10002</td>\n",
       "      <td>7076</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>194174</th>\n",
       "      <td>10002</td>\n",
       "      <td>7077</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>194175</th>\n",
       "      <td>10002</td>\n",
       "      <td>7079</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>194176</th>\n",
       "      <td>10002</td>\n",
       "      <td>7080</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>194177</th>\n",
       "      <td>10002</td>\n",
       "      <td>10002</td>\n",
       "      <td>249</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        start_station_code  end_station_code  flow\n",
       "194173               10002              7076    14\n",
       "194174               10002              7077     7\n",
       "194175               10002              7079     4\n",
       "194176               10002              7080    11\n",
       "194177               10002             10002   249"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_flow[df_flow.columns[:10]].tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "4740357\n",
      "4740357\n"
     ]
    }
   ],
   "source": [
    "Total = df_flow['flow'].sum()\n",
    "print(Total)\n",
    "print(len(data_bixi_concatenate))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_flow.to_csv('data_out/bixi_flow.csv', sep=',', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "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.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}