Build dataset.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "import os\n",
    "import re\n",
    "import pandas as pd\n",
    "import geopandas as gpd\n",
    "from tqdm import tqdm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "files = os.listdir('data')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "stations_files = [file_name for file_name in files if 'stations' in file_name]\n",
    "status_files = [file_name for file_name in files if 'status' in file_name]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stations_data_list = []\n",
    "\n",
    "for file in tqdm(stations_files, desc='file'):\n",
    "    \n",
    "    with open(f'data/{file}', 'r') as file_object:\n",
    "        try:\n",
    "            stations_json_load = json.load(file_object)\n",
    "        except json.JSONDecodeError as e:\n",
    "            print(f'error on {file}: {e}')\n",
    "            continue\n",
    "\n",
    "    time_stations_data = (\n",
    "        pd.json_normalize(\n",
    "            data=stations_json_load,\n",
    "            record_path= [\n",
    "                ['data','stations']\n",
    "            ],\n",
    "            meta='last_updated',\n",
    "        )\n",
    "        .filter(items=[\n",
    "            'last_updated',\n",
    "            'station_id',\n",
    "            'legacy_id',\n",
    "            'name',\n",
    "            'capacity',\n",
    "            'lat',\n",
    "            'lon'\n",
    "        ])\n",
    "    )\n",
    "\n",
    "    file_timestamp = int(re.search('\\d*',file).group())\n",
    "\n",
    "    time_stations_data['status_last_updated_fetched_timestamp'] = file_timestamp \n",
    "\n",
    "\n",
    "    stations_data_list.append(time_stations_data)\n",
    "\n",
    "stations_data = pd.concat(stations_data_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "status_data_list = []\n",
    "\n",
    "for file in tqdm(status_files):\n",
    "\n",
    "    with open(f'data/{file}', 'r') as file_object:\n",
    "        try:\n",
    "            status_json_load = json.load(file_object)\n",
    "        except json.JSONDecodeError as e:\n",
    "            print(f'error on {file}: {e}')\n",
    "            continue\n",
    "\n",
    "    time_status_data = pd.json_normalize(\n",
    "        data=status_json_load,\n",
    "        record_path= [\n",
    "            ['data','stations']\n",
    "        ],\n",
    "        meta='last_updated',\n",
    "    ).filter(items=[\n",
    "        'last_updated',\n",
    "        'station_id',\n",
    "        'station_status',\n",
    "        'is_renting',\n",
    "        'is_returning',\n",
    "        'num_docks_available',\n",
    "        'num_bikes_available',\n",
    "        'num_ebikes_available',\n",
    "        'num_bikes_disabled',\n",
    "        'num_docks_disabled',\n",
    "        'valet.active'\n",
    "    ])\n",
    "\n",
    "    status_data_list.append(time_status_data)\n",
    "\n",
    "\n",
    "status_data = pd.concat(status_data_list)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "localize times"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {},
   "outputs": [],
   "source": [
    "status_data['last_updated'] = (\n",
    "    status_data['last_updated']\n",
    "    .apply(pd.Timestamp, unit='s', tz='US/Eastern')\n",
    ")\n",
    "\n",
    "stations_data['status_last_updated_fetched_timestamp'] = (\n",
    "    stations_data['status_last_updated_fetched_timestamp']\n",
    "    .apply(pd.Timestamp, unit='s', tz='US/Eastern')\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "check that stations data timestamps have matching status data timestamps"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {},
   "outputs": [],
   "source": [
    "assert stations_data['status_last_updated_fetched_timestamp'].isin(status_data['last_updated']).all()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset = (\n",
    "    stations_data\n",
    "    .merge(\n",
    "        status_data, \n",
    "        left_on=['status_last_updated_fetched_timestamp','station_id'],\n",
    "        right_on=['last_updated','station_id'],\n",
    "        how='inner',\n",
    "        suffixes=['_stations',None]\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "check that each station_id is a unique physical location"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "metadata": {},
   "outputs": [],
   "source": [
    "assert dataset.groupby('station_id')['lat'].nunique().max() == 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {},
   "outputs": [],
   "source": [
    "stations_locations = (\n",
    "    dataset\n",
    "    .drop_duplicates(subset='station_id')\n",
    "    .set_index('station_id')\n",
    "    [['lat','lon']]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "metadata": {},
   "outputs": [],
   "source": [
    "stations_geo = gpd.GeoDataFrame(\n",
    "    index=stations_locations.index,\n",
    "    geometry=gpd.points_from_xy(\n",
    "        stations_locations['lon'],\n",
    "        stations_locations['lat'],\n",
    "        crs='epsg:4326'\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset = (\n",
    "    dataset\n",
    "    .set_index(['last_updated','station_id'])\n",
    "    .drop(columns=[\n",
    "        'name',\n",
    "        'lat','lon',\n",
    "        'status_last_updated_fetched_timestamp',\n",
    "        'last_updated_stations',\n",
    "        ])\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>capacity</th>\n",
       "      <th>is_renting</th>\n",
       "      <th>is_returning</th>\n",
       "      <th>num_docks_available</th>\n",
       "      <th>num_bikes_available</th>\n",
       "      <th>num_ebikes_available</th>\n",
       "      <th>num_bikes_disabled</th>\n",
       "      <th>num_docks_disabled</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>last_updated</th>\n",
       "      <th>station_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"10\" valign=\"top\">2024-03-12 17:40:49-04:00</th>\n",
       "      <th>66dc2995-0aca-11e7-82f6-3863bb44ef7c</th>\n",
       "      <td>51</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>06439006-11b6-44f0-8545-c9d39035f32a</th>\n",
       "      <td>48</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19d17911-1e4a-41fa-b62b-719aa0a6182e</th>\n",
       "      <td>39</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1861678548643203686</th>\n",
       "      <td>25</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>15</td>\n",
       "      <td>8</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cd2d9dab-7708-4685-a56f-9412c738de7e</th>\n",
       "      <td>23</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>22</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66db2a71-0aca-11e7-82f6-3863bb44ef7c</th>\n",
       "      <td>45</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>41</td>\n",
       "      <td>16</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>901ad0c4-383e-490a-8b54-0656ce2358d6</th>\n",
       "      <td>19</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>11</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66dc292c-0aca-11e7-82f6-3863bb44ef7c</th>\n",
       "      <td>55</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>50</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4c03fa2d-89da-4f0b-8f19-c7de5edbe256</th>\n",
       "      <td>25</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>21</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9af90faf-0b9b-451b-9cb0-20ff421ca1d9</th>\n",
       "      <td>27</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>18</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                capacity  \\\n",
       "last_updated              station_id                                       \n",
       "2024-03-12 17:40:49-04:00 66dc2995-0aca-11e7-82f6-3863bb44ef7c        51   \n",
       "                          06439006-11b6-44f0-8545-c9d39035f32a        48   \n",
       "                          19d17911-1e4a-41fa-b62b-719aa0a6182e        39   \n",
       "                          1861678548643203686                         25   \n",
       "                          cd2d9dab-7708-4685-a56f-9412c738de7e        23   \n",
       "                          66db2a71-0aca-11e7-82f6-3863bb44ef7c        45   \n",
       "                          901ad0c4-383e-490a-8b54-0656ce2358d6        19   \n",
       "                          66dc292c-0aca-11e7-82f6-3863bb44ef7c        55   \n",
       "                          4c03fa2d-89da-4f0b-8f19-c7de5edbe256        25   \n",
       "                          9af90faf-0b9b-451b-9cb0-20ff421ca1d9        27   \n",
       "\n",
       "                                                                is_renting  \\\n",
       "last_updated              station_id                                         \n",
       "2024-03-12 17:40:49-04:00 66dc2995-0aca-11e7-82f6-3863bb44ef7c           0   \n",
       "                          06439006-11b6-44f0-8545-c9d39035f32a           0   \n",
       "                          19d17911-1e4a-41fa-b62b-719aa0a6182e           0   \n",
       "                          1861678548643203686                            1   \n",
       "                          cd2d9dab-7708-4685-a56f-9412c738de7e           1   \n",
       "                          66db2a71-0aca-11e7-82f6-3863bb44ef7c           1   \n",
       "                          901ad0c4-383e-490a-8b54-0656ce2358d6           1   \n",
       "                          66dc292c-0aca-11e7-82f6-3863bb44ef7c           1   \n",
       "                          4c03fa2d-89da-4f0b-8f19-c7de5edbe256           1   \n",
       "                          9af90faf-0b9b-451b-9cb0-20ff421ca1d9           1   \n",
       "\n",
       "                                                                is_returning  \\\n",
       "last_updated              station_id                                           \n",
       "2024-03-12 17:40:49-04:00 66dc2995-0aca-11e7-82f6-3863bb44ef7c             0   \n",
       "                          06439006-11b6-44f0-8545-c9d39035f32a             0   \n",
       "                          19d17911-1e4a-41fa-b62b-719aa0a6182e             0   \n",
       "                          1861678548643203686                              1   \n",
       "                          cd2d9dab-7708-4685-a56f-9412c738de7e             1   \n",
       "                          66db2a71-0aca-11e7-82f6-3863bb44ef7c             1   \n",
       "                          901ad0c4-383e-490a-8b54-0656ce2358d6             1   \n",
       "                          66dc292c-0aca-11e7-82f6-3863bb44ef7c             1   \n",
       "                          4c03fa2d-89da-4f0b-8f19-c7de5edbe256             1   \n",
       "                          9af90faf-0b9b-451b-9cb0-20ff421ca1d9             1   \n",
       "\n",
       "                                                                num_docks_available  \\\n",
       "last_updated              station_id                                                  \n",
       "2024-03-12 17:40:49-04:00 66dc2995-0aca-11e7-82f6-3863bb44ef7c                    0   \n",
       "                          06439006-11b6-44f0-8545-c9d39035f32a                    0   \n",
       "                          19d17911-1e4a-41fa-b62b-719aa0a6182e                    0   \n",
       "                          1861678548643203686                                     8   \n",
       "                          cd2d9dab-7708-4685-a56f-9412c738de7e                    1   \n",
       "                          66db2a71-0aca-11e7-82f6-3863bb44ef7c                    0   \n",
       "                          901ad0c4-383e-490a-8b54-0656ce2358d6                    3   \n",
       "                          66dc292c-0aca-11e7-82f6-3863bb44ef7c                   50   \n",
       "                          4c03fa2d-89da-4f0b-8f19-c7de5edbe256                    3   \n",
       "                          9af90faf-0b9b-451b-9cb0-20ff421ca1d9                   18   \n",
       "\n",
       "                                                                num_bikes_available  \\\n",
       "last_updated              station_id                                                  \n",
       "2024-03-12 17:40:49-04:00 66dc2995-0aca-11e7-82f6-3863bb44ef7c                    0   \n",
       "                          06439006-11b6-44f0-8545-c9d39035f32a                    0   \n",
       "                          19d17911-1e4a-41fa-b62b-719aa0a6182e                    0   \n",
       "                          1861678548643203686                                    15   \n",
       "                          cd2d9dab-7708-4685-a56f-9412c738de7e                   22   \n",
       "                          66db2a71-0aca-11e7-82f6-3863bb44ef7c                   41   \n",
       "                          901ad0c4-383e-490a-8b54-0656ce2358d6                   11   \n",
       "                          66dc292c-0aca-11e7-82f6-3863bb44ef7c                    3   \n",
       "                          4c03fa2d-89da-4f0b-8f19-c7de5edbe256                   21   \n",
       "                          9af90faf-0b9b-451b-9cb0-20ff421ca1d9                    9   \n",
       "\n",
       "                                                                num_ebikes_available  \\\n",
       "last_updated              station_id                                                   \n",
       "2024-03-12 17:40:49-04:00 66dc2995-0aca-11e7-82f6-3863bb44ef7c                     0   \n",
       "                          06439006-11b6-44f0-8545-c9d39035f32a                     0   \n",
       "                          19d17911-1e4a-41fa-b62b-719aa0a6182e                     0   \n",
       "                          1861678548643203686                                      8   \n",
       "                          cd2d9dab-7708-4685-a56f-9412c738de7e                     1   \n",
       "                          66db2a71-0aca-11e7-82f6-3863bb44ef7c                    16   \n",
       "                          901ad0c4-383e-490a-8b54-0656ce2358d6                     2   \n",
       "                          66dc292c-0aca-11e7-82f6-3863bb44ef7c                     1   \n",
       "                          4c03fa2d-89da-4f0b-8f19-c7de5edbe256                     3   \n",
       "                          9af90faf-0b9b-451b-9cb0-20ff421ca1d9                     1   \n",
       "\n",
       "                                                                num_bikes_disabled  \\\n",
       "last_updated              station_id                                                 \n",
       "2024-03-12 17:40:49-04:00 66dc2995-0aca-11e7-82f6-3863bb44ef7c                   0   \n",
       "                          06439006-11b6-44f0-8545-c9d39035f32a                   0   \n",
       "                          19d17911-1e4a-41fa-b62b-719aa0a6182e                   0   \n",
       "                          1861678548643203686                                    2   \n",
       "                          cd2d9dab-7708-4685-a56f-9412c738de7e                   0   \n",
       "                          66db2a71-0aca-11e7-82f6-3863bb44ef7c                   4   \n",
       "                          901ad0c4-383e-490a-8b54-0656ce2358d6                   5   \n",
       "                          66dc292c-0aca-11e7-82f6-3863bb44ef7c                   2   \n",
       "                          4c03fa2d-89da-4f0b-8f19-c7de5edbe256                   0   \n",
       "                          9af90faf-0b9b-451b-9cb0-20ff421ca1d9                   0   \n",
       "\n",
       "                                                                num_docks_disabled  \n",
       "last_updated              station_id                                                \n",
       "2024-03-12 17:40:49-04:00 66dc2995-0aca-11e7-82f6-3863bb44ef7c                   0  \n",
       "                          06439006-11b6-44f0-8545-c9d39035f32a                   0  \n",
       "                          19d17911-1e4a-41fa-b62b-719aa0a6182e                   0  \n",
       "                          1861678548643203686                                    0  \n",
       "                          cd2d9dab-7708-4685-a56f-9412c738de7e                   0  \n",
       "                          66db2a71-0aca-11e7-82f6-3863bb44ef7c                   0  \n",
       "                          901ad0c4-383e-490a-8b54-0656ce2358d6                   0  \n",
       "                          66dc292c-0aca-11e7-82f6-3863bb44ef7c                   0  \n",
       "                          4c03fa2d-89da-4f0b-8f19-c7de5edbe256                   0  \n",
       "                          9af90faf-0b9b-451b-9cb0-20ff421ca1d9                   0  "
      ]
     },
     "execution_count": 137,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataset.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "save out"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset.to_parquet('dataset.parquet') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stations_geo.to_file('stations_geo.geojson')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "citibike-analysis",
   "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.11.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}