nextbike_Bikes_convert_json_to_df.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from pprint import pprint\n",
    "import os\n",
    "from datetime import datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "def filenames(path):\n",
    "    \"\"\"\n",
    "    get file names from json folder to derive with data and timestamp\n",
    "    \"\"\"\n",
    "    files = os.listdir(path)\n",
    "    files_lst = []\n",
    "    for f in files:\n",
    "        dt = (f[12:20])\n",
    "        tm = (f[21:27])\n",
    "        dat = (f,dt,tm)\n",
    "        files_lst.append(dat)\n",
    "    return(files_lst)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "def parse_json(file):\n",
    "    with open(r'C:\\Users\\steff\\Documents\\DataScience Bootcamp\\Bike\\json\\\\'+file[0]) as f:\n",
    "        json_data = json.load(f)\n",
    "    return json_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "def json_extract(json_data,i,col1,col2):\n",
    "    parsed1 = json_data['countries'][0]['cities'][0]['places'][i][col1]\n",
    "    parsed2 = json_data['countries'][0]['cities'][0]['places'][i][col2]\n",
    "    return parsed1,parsed2    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "def unpacking_bike_numbers(column):\n",
    "    \"\"\" \n",
    "    getting unique list of bikes\n",
    "    \"\"\"\n",
    "    bike_unpack = pd.DataFrame(df[column].tolist(), index=df.index)\n",
    "    colnames = list(bike_unpack.columns.values)\n",
    "    all_bikes = []\n",
    "    all_bikes = bike_unpack[0]\n",
    "    \n",
    "    for c in colnames:\n",
    "        data= bike_unpack[c]\n",
    "        pd.concat([all_bikes,data])\n",
    "    all_bikes = all_bikes.unique()\n",
    "    return all_bikes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "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>day</th>\n",
       "      <th>0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>20190306</td>\n",
       "      <td>660</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20190307</td>\n",
       "      <td>203</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>20190311</td>\n",
       "      <td>915</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>20190312</td>\n",
       "      <td>1432</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>20190313</td>\n",
       "      <td>1431</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>20190314</td>\n",
       "      <td>1166</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>20190315</td>\n",
       "      <td>1431</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>20190316</td>\n",
       "      <td>1431</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>20190317</td>\n",
       "      <td>1431</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>20190318</td>\n",
       "      <td>1430</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>20190319</td>\n",
       "      <td>997</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>20190320</td>\n",
       "      <td>1431</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>20190321</td>\n",
       "      <td>1405</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>20190322</td>\n",
       "      <td>1057</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>20190323</td>\n",
       "      <td>1315</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>20190324</td>\n",
       "      <td>1289</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>20190325</td>\n",
       "      <td>1263</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>20190326</td>\n",
       "      <td>1268</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>20190327</td>\n",
       "      <td>1294</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         day     0\n",
       "0   20190306   660\n",
       "1   20190307   203\n",
       "2   20190311   915\n",
       "3   20190312  1432\n",
       "4   20190313  1431\n",
       "5   20190314  1166\n",
       "6   20190315  1431\n",
       "7   20190316  1431\n",
       "8   20190317  1431\n",
       "9   20190318  1430\n",
       "10  20190319   997\n",
       "11  20190320  1431\n",
       "12  20190321  1405\n",
       "13  20190322  1057\n",
       "14  20190323  1315\n",
       "15  20190324  1289\n",
       "16  20190325  1263\n",
       "17  20190326  1268\n",
       "18  20190327  1294"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bike_lst = []\n",
    "df_files = pd.DataFrame(filenames(r'C:\\Users\\steff\\Documents\\DataScience Bootcamp\\Bike\\json\\\\'),\n",
    "                   columns=('file','day','time'))\n",
    "day = df_files.groupby(by=('day')).size()\n",
    "day.reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "singleday = df_files[(df_files['day'] == '20190327') ]\n",
    "singleday = singleday.values.tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "ename": "KeyboardInterrupt",
     "evalue": "",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mKeyboardInterrupt\u001b[0m                         Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-38-1483247090bb>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[1;32mfor\u001b[0m \u001b[0mf\u001b[0m \u001b[1;32min\u001b[0m \u001b[0msingleday\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 2\u001b[1;33m     \u001b[0mjson_data\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mparse_json\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mf\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      3\u001b[0m     \u001b[1;32mfor\u001b[0m \u001b[0mi\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mrange\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;36m3000\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m         \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      5\u001b[0m             \u001b[0mavail_bikes\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mjson_data\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'countries'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'cities'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'available_bikes'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m<ipython-input-33-bbf4a7f98276>\u001b[0m in \u001b[0;36mparse_json\u001b[1;34m(file)\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[1;32mdef\u001b[0m \u001b[0mparse_json\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mfile\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      2\u001b[0m     \u001b[1;32mwith\u001b[0m \u001b[0mopen\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34mr'C:\\Users\\steff\\Documents\\DataScience Bootcamp\\Bike\\json\\\\'\u001b[0m\u001b[1;33m+\u001b[0m\u001b[0mfile\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;32mas\u001b[0m \u001b[0mf\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 3\u001b[1;33m         \u001b[0mjson_data\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mjson\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mload\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mf\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      4\u001b[0m     \u001b[1;32mreturn\u001b[0m \u001b[0mjson_data\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\users\\steff\\anaconda3\\envs\\tf\\lib\\json\\__init__.py\u001b[0m in \u001b[0;36mload\u001b[1;34m(fp, cls, object_hook, parse_float, parse_int, parse_constant, object_pairs_hook, **kw)\u001b[0m\n\u001b[0;32m    297\u001b[0m         \u001b[0mcls\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mcls\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mobject_hook\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mobject_hook\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    298\u001b[0m         \u001b[0mparse_float\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mparse_float\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mparse_int\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mparse_int\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 299\u001b[1;33m         parse_constant=parse_constant, object_pairs_hook=object_pairs_hook, **kw)\n\u001b[0m\u001b[0;32m    300\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    301\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\users\\steff\\anaconda3\\envs\\tf\\lib\\json\\__init__.py\u001b[0m in \u001b[0;36mloads\u001b[1;34m(s, encoding, cls, object_hook, parse_float, parse_int, parse_constant, object_pairs_hook, **kw)\u001b[0m\n\u001b[0;32m    352\u001b[0m             \u001b[0mparse_int\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mNone\u001b[0m \u001b[1;32mand\u001b[0m \u001b[0mparse_float\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mNone\u001b[0m \u001b[1;32mand\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    353\u001b[0m             parse_constant is None and object_pairs_hook is None and not kw):\n\u001b[1;32m--> 354\u001b[1;33m         \u001b[1;32mreturn\u001b[0m \u001b[0m_default_decoder\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mdecode\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0ms\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    355\u001b[0m     \u001b[1;32mif\u001b[0m \u001b[0mcls\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    356\u001b[0m         \u001b[0mcls\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mJSONDecoder\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\users\\steff\\anaconda3\\envs\\tf\\lib\\json\\decoder.py\u001b[0m in \u001b[0;36mdecode\u001b[1;34m(self, s, _w)\u001b[0m\n\u001b[0;32m    337\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    338\u001b[0m         \"\"\"\n\u001b[1;32m--> 339\u001b[1;33m         \u001b[0mobj\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mend\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mraw_decode\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0ms\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0midx\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0m_w\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0ms\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;36m0\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mend\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    340\u001b[0m         \u001b[0mend\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0m_w\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0ms\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mend\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mend\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    341\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mend\u001b[0m \u001b[1;33m!=\u001b[0m \u001b[0mlen\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0ms\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\users\\steff\\anaconda3\\envs\\tf\\lib\\json\\decoder.py\u001b[0m in \u001b[0;36mraw_decode\u001b[1;34m(self, s, idx)\u001b[0m\n\u001b[0;32m    353\u001b[0m         \"\"\"\n\u001b[0;32m    354\u001b[0m         \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 355\u001b[1;33m             \u001b[0mobj\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mend\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mscan_once\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0ms\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0midx\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    356\u001b[0m         \u001b[1;32mexcept\u001b[0m \u001b[0mStopIteration\u001b[0m \u001b[1;32mas\u001b[0m \u001b[0merr\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    357\u001b[0m             \u001b[1;32mraise\u001b[0m \u001b[0mJSONDecodeError\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"Expecting value\"\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0ms\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0merr\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mvalue\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;32mfrom\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mKeyboardInterrupt\u001b[0m: "
     ]
    }
   ],
   "source": [
    "for f in singleday:\n",
    "    json_data = parse_json(f)\n",
    "    for i in range(0,3000):\n",
    "        try: \n",
    "            avail_bikes = json_data['countries'][0]['cities'][0]['available_bikes']\n",
    "            num_places = json_data['countries'][0]['cities'][0]['num_places']\n",
    "            refresh_rate = json_data['countries'][0]['cities'][0]['refresh_rate']\n",
    "            uid, name = json_extract(json_data,i,'uid','name')\n",
    "            lat, lng = json_extract(json_data,i,'lat','lng')\n",
    "            bikes, booked_bikes = json_extract(json_data,i,'bikes','booked_bikes')\n",
    "            free_racks, bike_racks = json_extract(json_data,i,'free_racks','bike_racks') \n",
    "            terminal_type, spot = json_extract(json_data,i,'terminal_type','spot') \n",
    "            if spot==True:\n",
    "                spot='station'\n",
    "            else:\n",
    "                spot='floating'           \n",
    "            bike_numbers, number = json_extract(json_data,i,'bike_numbers','number') \n",
    "\n",
    "            bike_data = (datetime.strptime((f[1] +' '+ f[2]), \"%Y%m%d %H%M%S\"),\n",
    "                         refresh_rate,num_places,avail_bikes,uid,lat,lng,name,\n",
    "                         number,bikes,booked_bikes,free_racks,bike_racks,terminal_type,\n",
    "                         spot,bike_numbers)\n",
    "            bike_lst.append(bike_data)\n",
    "        except:\n",
    "            continue"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#len(bike_lst)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>date_time</th>\n",
       "      <th>refresh_rate</th>\n",
       "      <th>num_places</th>\n",
       "      <th>total_avail_bikes</th>\n",
       "      <th>uid</th>\n",
       "      <th>from_lat</th>\n",
       "      <th>from_long</th>\n",
       "      <th>from_station</th>\n",
       "      <th>from_station_id</th>\n",
       "      <th>bikes</th>\n",
       "      <th>booked_bikes</th>\n",
       "      <th>free_racks</th>\n",
       "      <th>bike_racks</th>\n",
       "      <th>terminal_type</th>\n",
       "      <th>from_station_mode</th>\n",
       "      <th>bike_numbers</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1375776</th>\n",
       "      <td>2019-03-27 23:59:17</td>\n",
       "      <td>10120</td>\n",
       "      <td>1901</td>\n",
       "      <td>2778</td>\n",
       "      <td>13768193</td>\n",
       "      <td>52.533343</td>\n",
       "      <td>13.398669</td>\n",
       "      <td>BIKE 13063</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td></td>\n",
       "      <td>floating</td>\n",
       "      <td>[13063]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1375777</th>\n",
       "      <td>2019-03-27 23:59:17</td>\n",
       "      <td>10120</td>\n",
       "      <td>1901</td>\n",
       "      <td>2778</td>\n",
       "      <td>13768252</td>\n",
       "      <td>52.475567</td>\n",
       "      <td>13.439722</td>\n",
       "      <td>BIKE 14738</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td></td>\n",
       "      <td>floating</td>\n",
       "      <td>[14738]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1375778</th>\n",
       "      <td>2019-03-27 23:59:17</td>\n",
       "      <td>10120</td>\n",
       "      <td>1901</td>\n",
       "      <td>2778</td>\n",
       "      <td>13768253</td>\n",
       "      <td>52.504000</td>\n",
       "      <td>13.434309</td>\n",
       "      <td>BIKE 15340</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td></td>\n",
       "      <td>floating</td>\n",
       "      <td>[15340]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1375779</th>\n",
       "      <td>2019-03-27 23:59:17</td>\n",
       "      <td>10120</td>\n",
       "      <td>1901</td>\n",
       "      <td>2778</td>\n",
       "      <td>13768323</td>\n",
       "      <td>52.480231</td>\n",
       "      <td>13.445378</td>\n",
       "      <td>BIKE 14678</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td></td>\n",
       "      <td>floating</td>\n",
       "      <td>[14678]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1375780</th>\n",
       "      <td>2019-03-27 23:59:17</td>\n",
       "      <td>10120</td>\n",
       "      <td>1901</td>\n",
       "      <td>2778</td>\n",
       "      <td>13768426</td>\n",
       "      <td>52.483974</td>\n",
       "      <td>13.429674</td>\n",
       "      <td>BIKE 13586</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td></td>\n",
       "      <td>floating</td>\n",
       "      <td>[13586]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  date_time refresh_rate  num_places  total_avail_bikes  \\\n",
       "1375776 2019-03-27 23:59:17        10120        1901               2778   \n",
       "1375777 2019-03-27 23:59:17        10120        1901               2778   \n",
       "1375778 2019-03-27 23:59:17        10120        1901               2778   \n",
       "1375779 2019-03-27 23:59:17        10120        1901               2778   \n",
       "1375780 2019-03-27 23:59:17        10120        1901               2778   \n",
       "\n",
       "              uid   from_lat  from_long from_station  from_station_id  bikes  \\\n",
       "1375776  13768193  52.533343  13.398669   BIKE 13063                0      1   \n",
       "1375777  13768252  52.475567  13.439722   BIKE 14738                0      1   \n",
       "1375778  13768253  52.504000  13.434309   BIKE 15340                0      1   \n",
       "1375779  13768323  52.480231  13.445378   BIKE 14678                0      1   \n",
       "1375780  13768426  52.483974  13.429674   BIKE 13586                0      1   \n",
       "\n",
       "         booked_bikes  free_racks  bike_racks terminal_type from_station_mode  \\\n",
       "1375776             0           0           0                        floating   \n",
       "1375777             0           0           0                        floating   \n",
       "1375778             0           0           0                        floating   \n",
       "1375779             0           0           0                        floating   \n",
       "1375780             0           0           0                        floating   \n",
       "\n",
       "        bike_numbers  \n",
       "1375776      [13063]  \n",
       "1375777      [14738]  \n",
       "1375778      [15340]  \n",
       "1375779      [14678]  \n",
       "1375780      [13586]  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "colnames =('date_time','refresh_rate','num_places','total_avail_bikes','uid','from_lat',\n",
    "           'from_long','from_station','from_station_id','bikes','booked_bikes','free_racks',\n",
    "           'bike_racks','terminal_type','from_station_mode','bike_numbers')\n",
    "df = pd.DataFrame(bike_lst, columns =colnames)\n",
    "df.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_bikes = unpacking_bike_numbers('bike_numbers')\n",
    "#all_bikes = all_bikes[0:100] # testing\n",
    "#all_bikes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "def trips_by_bike(df):\n",
    "    \"\"\" generating state for each bike\"\"\" \n",
    "    pd.options.mode.chained_assignment = None  # default='warn'\n",
    "    appended_data = []\n",
    "    for b in all_bikes:\n",
    "        data = df[df[\"bike_numbers\"].apply(lambda x: True if b in x else False)]\n",
    "        data.groupby(['from_station']).size()\n",
    "        data['bike_id'] = b\n",
    "        # min and max time for this bike on one station\n",
    "        data['dt_end'] = data.groupby('from_station')['date_time'].transform('max')\n",
    "        data['dt_start'] = data.groupby('from_station')['date_time'].transform('min')\n",
    "        data= data[['bike_id','from_station','from_lat','from_long','from_station_id',\n",
    "                    'from_station_mode','dt_start','dt_end']].copy()\n",
    "        appended_data.append(data)\n",
    "    return appended_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "def generating_duration(df):\n",
    "    df = df.sort_values(['bike_id','dt_start'], ascending=True)\n",
    "    df['bike_next_row'] = df['bike_id'].shift(-1)\n",
    "    df['dt_min_next_row'] = df['dt_start'].shift(-1)\n",
    "    df['station_next_row'] = df['from_station'].shift(-1)\n",
    "    df['station_id_next_row'] = df['from_station_id'].shift(-1)\n",
    "    df['trip_duration'] = np.nan\n",
    "    df['trip_end_time'] = np.nan\n",
    "    df['trip_end_time'] = df['trip_end_time'].astype('datetime64[ns]')\n",
    "    df['diff'] = (df['dt_min_next_row']-df['dt_end']).astype('timedelta64[m]')\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "def generating_next_station(df):\n",
    "    df['station_mode_next_row'] = df['from_station_mode'].shift(-1)\n",
    "    df['lat_next_row'] = df['from_lat'].shift(-1)\n",
    "    df['long_next_row'] = df['from_long'].shift(-1)\n",
    "    df['to_station'] = np.nan\n",
    "    df['to_station_id'] = np.nan\n",
    "    df['to_station_mode'] = np.nan\n",
    "    df['to_lat'] = np.nan\n",
    "    df['to_long'] = np.nan\n",
    "    trips = df.drop_duplicates(subset=['bike_id','from_station'], keep='last')\n",
    "    return trips"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "def generating_destination(trips):\n",
    "    trips.loc[((trips['bike_id'] == trips['bike_next_row'])\n",
    "              & (trips['dt_min_next_row'] > trips['dt_start'])), \n",
    "             'trip_end_time'] = trips['dt_min_next_row']\n",
    "    trips.loc[((trips['bike_id'] == trips['bike_next_row'])\n",
    "              & (trips['dt_min_next_row'] > trips['dt_start'])), \n",
    "             'to_station'] = trips['station_next_row']\n",
    "    trips.loc[((trips['bike_id'] == trips['bike_next_row'])\n",
    "              & (trips['dt_min_next_row'] > trips['dt_start'])), \n",
    "             'to_station_id'] = trips['station_id_next_row']\n",
    "    trips.loc[((trips['bike_id'] == trips['bike_next_row'])\n",
    "              & (trips['dt_min_next_row'] > trips['dt_start'])), \n",
    "             'to_station_mode'] = trips['station_mode_next_row']\n",
    "    trips.loc[((trips['bike_id'] == trips['bike_next_row'])\n",
    "              & (trips['dt_min_next_row'] > trips['dt_start'])), \n",
    "             'to_lat'] = trips['lat_next_row']\n",
    "    trips.loc[((trips['bike_id'] == trips['bike_next_row'])\n",
    "              & (trips['dt_min_next_row'] > trips['dt_start'])), \n",
    "             'to_long'] = trips['long_next_row']\n",
    "    trips.loc[((trips['bike_id'] == trips['bike_next_row'])\n",
    "              & (trips['dt_min_next_row'] > trips['dt_start'])), \n",
    "             'trip_duration'] = trips['diff']\n",
    "    return trips"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "def trip_ids(df, day):\n",
    "    newindex = np.arange(int(day)*1000, int(day)*1000+len(df.index), 1)\n",
    "    df['trip_id'] = newindex\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "# generating trips data\n",
    "appended_data = trips_by_bike(df)\n",
    "trips = pd.concat(appended_data,ignore_index=True)\n",
    "trips = generating_duration(trips)\n",
    "trips = generating_next_station(trips)\n",
    "trips = generating_destination(trips)\n",
    "\n",
    "trips = trip_ids(trips, singleday[0][1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "#trips.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2087, 24)"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trips.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_trip = trips[['trip_id','bike_id','trip_duration','dt_end','trip_end_time',\n",
    "         'from_station','from_station_id','from_station_mode','from_lat','from_long',\n",
    "         'to_station','to_station_id','to_station_mode','to_lat','to_long'\n",
    "         ]]\n",
    "df_trip = df_trip.rename(columns = {'dt_end':'trip_start_time'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_trip.to_csv(r'trips\\\\trips_2019-03-27.csv')"
   ]
  },
  {
   "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.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}