data_wrangling.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "5388dc8e-71bc-476e-b2fb-a7910efb546c",
   "metadata": {},
   "source": [
    "# Data wrangling"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5448d876-355a-4ab9-92d5-dc32f0b62a51",
   "metadata": {},
   "source": [
    "## Libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "67787b64-bb4e-4fc4-8ebf-ec12c8da3896",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "14a603a9-c046-4bf0-bee5-46bb97e8d19c",
   "metadata": {},
   "source": [
    "## Read data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "a5c7479d-05b8-4dd5-8611-8831e65661c0",
   "metadata": {},
   "outputs": [],
   "source": [
    "stations = pd.read_json(\"data/stations.json\")\n",
    "crimes = pd.read_json(\"data/crimes.json\")\n",
    "distances = pd.read_json(\"data/distances.json\")\n",
    "community = pd.read_json(\"data/community_areas.json\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "142f1f09-6133-47e9-b669-862ee02b34c5",
   "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>station_name</th>\n",
       "      <th>community_area</th>\n",
       "      <th>longitude</th>\n",
       "      <th>latitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3510 S Michigan Ave</td>\n",
       "      <td>DOUGLAS</td>\n",
       "      <td>-87.623395</td>\n",
       "      <td>41.830702</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1160 N Larrabee St</td>\n",
       "      <td>NEAR NORTH SIDE</td>\n",
       "      <td>-87.643352</td>\n",
       "      <td>41.903242</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>850 W Addison St</td>\n",
       "      <td>LAKE VIEW</td>\n",
       "      <td>-87.651512</td>\n",
       "      <td>41.947400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5400 N Lincoln Ave</td>\n",
       "      <td>LINCOLN SQUARE</td>\n",
       "      <td>-87.692845</td>\n",
       "      <td>41.979550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1900 W Monterey Ave</td>\n",
       "      <td>MORGAN PARK</td>\n",
       "      <td>-87.668520</td>\n",
       "      <td>41.691435</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6464 N Clark St</td>\n",
       "      <td>ROGERS PARK</td>\n",
       "      <td>-87.671324</td>\n",
       "      <td>41.999763</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>5555 W Grand Ave</td>\n",
       "      <td>BELMONT CRAGIN</td>\n",
       "      <td>-87.765574</td>\n",
       "      <td>41.918609</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1718 S State St</td>\n",
       "      <td>NEAR SOUTH SIDE</td>\n",
       "      <td>-87.627356</td>\n",
       "      <td>41.858373</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>5101 S Wentworth Ave</td>\n",
       "      <td>FULLER PARK</td>\n",
       "      <td>-87.630560</td>\n",
       "      <td>41.801811</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>7040 S Cottage Grove Ave</td>\n",
       "      <td>WOODLAWN</td>\n",
       "      <td>-87.605748</td>\n",
       "      <td>41.766431</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2255 E 103rd St</td>\n",
       "      <td>SOUTH DEERING</td>\n",
       "      <td>-87.568349</td>\n",
       "      <td>41.707933</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>727 E 111th St</td>\n",
       "      <td>PULLMAN</td>\n",
       "      <td>-87.604506</td>\n",
       "      <td>41.692723</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>7808 S Halsted St</td>\n",
       "      <td>AUBURN GRESHAM</td>\n",
       "      <td>-87.644229</td>\n",
       "      <td>41.752137</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>1438 W 63rd St</td>\n",
       "      <td>WEST ENGLEWOOD</td>\n",
       "      <td>-87.660887</td>\n",
       "      <td>41.779632</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>3420 W 63rd St</td>\n",
       "      <td>CHICAGO LAWN</td>\n",
       "      <td>-87.708864</td>\n",
       "      <td>41.778987</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>3120 S Halsted St</td>\n",
       "      <td>BRIDGEPORT</td>\n",
       "      <td>-87.646408</td>\n",
       "      <td>41.837394</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>3315 W Ogden Ave</td>\n",
       "      <td>NORTH LAWNDALE</td>\n",
       "      <td>-87.708382</td>\n",
       "      <td>41.856685</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>3151 W Harrison St</td>\n",
       "      <td>EAST GARFIELD PARK</td>\n",
       "      <td>-87.705488</td>\n",
       "      <td>41.873582</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>1412 S Blue Island Ave\\n</td>\n",
       "      <td>NEAR WEST SIDE</td>\n",
       "      <td>-87.656973</td>\n",
       "      <td>41.862977</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2150 N California Ave</td>\n",
       "      <td>LOGAN SQUARE</td>\n",
       "      <td>-87.697452</td>\n",
       "      <td>41.921103</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>5701 W Madison St</td>\n",
       "      <td>AUSTIN</td>\n",
       "      <td>-87.768200</td>\n",
       "      <td>41.880083</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>5151 N Milwaukee Ave</td>\n",
       "      <td>JEFFERSON PARK</td>\n",
       "      <td>-87.766149</td>\n",
       "      <td>41.974094</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>4650 N Pulaski Rd</td>\n",
       "      <td>ALBANY PARK</td>\n",
       "      <td>-87.728115</td>\n",
       "      <td>41.966053</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                station_name      community_area  longitude   latitude\n",
       "0        3510 S Michigan Ave             DOUGLAS -87.623395  41.830702\n",
       "1         1160 N Larrabee St     NEAR NORTH SIDE -87.643352  41.903242\n",
       "2           850 W Addison St           LAKE VIEW -87.651512  41.947400\n",
       "3         5400 N Lincoln Ave      LINCOLN SQUARE -87.692845  41.979550\n",
       "4        1900 W Monterey Ave         MORGAN PARK -87.668520  41.691435\n",
       "5            6464 N Clark St         ROGERS PARK -87.671324  41.999763\n",
       "6           5555 W Grand Ave      BELMONT CRAGIN -87.765574  41.918609\n",
       "7            1718 S State St     NEAR SOUTH SIDE -87.627356  41.858373\n",
       "8       5101 S Wentworth Ave         FULLER PARK -87.630560  41.801811\n",
       "9   7040 S Cottage Grove Ave            WOODLAWN -87.605748  41.766431\n",
       "10           2255 E 103rd St       SOUTH DEERING -87.568349  41.707933\n",
       "11            727 E 111th St             PULLMAN -87.604506  41.692723\n",
       "12         7808 S Halsted St      AUBURN GRESHAM -87.644229  41.752137\n",
       "13            1438 W 63rd St      WEST ENGLEWOOD -87.660887  41.779632\n",
       "14            3420 W 63rd St        CHICAGO LAWN -87.708864  41.778987\n",
       "15         3120 S Halsted St          BRIDGEPORT -87.646408  41.837394\n",
       "16          3315 W Ogden Ave      NORTH LAWNDALE -87.708382  41.856685\n",
       "17        3151 W Harrison St  EAST GARFIELD PARK -87.705488  41.873582\n",
       "18  1412 S Blue Island Ave\\n      NEAR WEST SIDE -87.656973  41.862977\n",
       "19     2150 N California Ave        LOGAN SQUARE -87.697452  41.921103\n",
       "20         5701 W Madison St              AUSTIN -87.768200  41.880083\n",
       "21      5151 N Milwaukee Ave      JEFFERSON PARK -87.766149  41.974094\n",
       "22         4650 N Pulaski Rd         ALBANY PARK -87.728115  41.966053"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stations"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "79a122c4-f27d-47e8-abd2-c24c7a64bb9d",
   "metadata": {},
   "source": [
    "## New data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "90a93850-8690-4bfb-bd59-ae3f1fcddc1d",
   "metadata": {},
   "source": [
    "### New stations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "56308207-64ac-420b-aebc-2ef52e550ea4",
   "metadata": {},
   "outputs": [],
   "source": [
    "new_stations = stations.copy()\n",
    "new_stations['ID_PS'] = [i for i in range(1,24)]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "da8111f1-bff8-4a84-8ce4-ad3d0e48fc5f",
   "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>station_name</th>\n",
       "      <th>community_area</th>\n",
       "      <th>longitude</th>\n",
       "      <th>latitude</th>\n",
       "      <th>ID_PS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3510 S Michigan Ave</td>\n",
       "      <td>DOUGLAS</td>\n",
       "      <td>-87.623395</td>\n",
       "      <td>41.830702</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1160 N Larrabee St</td>\n",
       "      <td>NEAR NORTH SIDE</td>\n",
       "      <td>-87.643352</td>\n",
       "      <td>41.903242</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>850 W Addison St</td>\n",
       "      <td>LAKE VIEW</td>\n",
       "      <td>-87.651512</td>\n",
       "      <td>41.947400</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5400 N Lincoln Ave</td>\n",
       "      <td>LINCOLN SQUARE</td>\n",
       "      <td>-87.692845</td>\n",
       "      <td>41.979550</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1900 W Monterey Ave</td>\n",
       "      <td>MORGAN PARK</td>\n",
       "      <td>-87.668520</td>\n",
       "      <td>41.691435</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6464 N Clark St</td>\n",
       "      <td>ROGERS PARK</td>\n",
       "      <td>-87.671324</td>\n",
       "      <td>41.999763</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>5555 W Grand Ave</td>\n",
       "      <td>BELMONT CRAGIN</td>\n",
       "      <td>-87.765574</td>\n",
       "      <td>41.918609</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1718 S State St</td>\n",
       "      <td>NEAR SOUTH SIDE</td>\n",
       "      <td>-87.627356</td>\n",
       "      <td>41.858373</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>5101 S Wentworth Ave</td>\n",
       "      <td>FULLER PARK</td>\n",
       "      <td>-87.630560</td>\n",
       "      <td>41.801811</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>7040 S Cottage Grove Ave</td>\n",
       "      <td>WOODLAWN</td>\n",
       "      <td>-87.605748</td>\n",
       "      <td>41.766431</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2255 E 103rd St</td>\n",
       "      <td>SOUTH DEERING</td>\n",
       "      <td>-87.568349</td>\n",
       "      <td>41.707933</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>727 E 111th St</td>\n",
       "      <td>PULLMAN</td>\n",
       "      <td>-87.604506</td>\n",
       "      <td>41.692723</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>7808 S Halsted St</td>\n",
       "      <td>AUBURN GRESHAM</td>\n",
       "      <td>-87.644229</td>\n",
       "      <td>41.752137</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>1438 W 63rd St</td>\n",
       "      <td>WEST ENGLEWOOD</td>\n",
       "      <td>-87.660887</td>\n",
       "      <td>41.779632</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>3420 W 63rd St</td>\n",
       "      <td>CHICAGO LAWN</td>\n",
       "      <td>-87.708864</td>\n",
       "      <td>41.778987</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>3120 S Halsted St</td>\n",
       "      <td>BRIDGEPORT</td>\n",
       "      <td>-87.646408</td>\n",
       "      <td>41.837394</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>3315 W Ogden Ave</td>\n",
       "      <td>NORTH LAWNDALE</td>\n",
       "      <td>-87.708382</td>\n",
       "      <td>41.856685</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>3151 W Harrison St</td>\n",
       "      <td>EAST GARFIELD PARK</td>\n",
       "      <td>-87.705488</td>\n",
       "      <td>41.873582</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>1412 S Blue Island Ave\\n</td>\n",
       "      <td>NEAR WEST SIDE</td>\n",
       "      <td>-87.656973</td>\n",
       "      <td>41.862977</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2150 N California Ave</td>\n",
       "      <td>LOGAN SQUARE</td>\n",
       "      <td>-87.697452</td>\n",
       "      <td>41.921103</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>5701 W Madison St</td>\n",
       "      <td>AUSTIN</td>\n",
       "      <td>-87.768200</td>\n",
       "      <td>41.880083</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>5151 N Milwaukee Ave</td>\n",
       "      <td>JEFFERSON PARK</td>\n",
       "      <td>-87.766149</td>\n",
       "      <td>41.974094</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>4650 N Pulaski Rd</td>\n",
       "      <td>ALBANY PARK</td>\n",
       "      <td>-87.728115</td>\n",
       "      <td>41.966053</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                station_name      community_area  longitude   latitude  ID_PS\n",
       "0        3510 S Michigan Ave             DOUGLAS -87.623395  41.830702      1\n",
       "1         1160 N Larrabee St     NEAR NORTH SIDE -87.643352  41.903242      2\n",
       "2           850 W Addison St           LAKE VIEW -87.651512  41.947400      3\n",
       "3         5400 N Lincoln Ave      LINCOLN SQUARE -87.692845  41.979550      4\n",
       "4        1900 W Monterey Ave         MORGAN PARK -87.668520  41.691435      5\n",
       "5            6464 N Clark St         ROGERS PARK -87.671324  41.999763      6\n",
       "6           5555 W Grand Ave      BELMONT CRAGIN -87.765574  41.918609      7\n",
       "7            1718 S State St     NEAR SOUTH SIDE -87.627356  41.858373      8\n",
       "8       5101 S Wentworth Ave         FULLER PARK -87.630560  41.801811      9\n",
       "9   7040 S Cottage Grove Ave            WOODLAWN -87.605748  41.766431     10\n",
       "10           2255 E 103rd St       SOUTH DEERING -87.568349  41.707933     11\n",
       "11            727 E 111th St             PULLMAN -87.604506  41.692723     12\n",
       "12         7808 S Halsted St      AUBURN GRESHAM -87.644229  41.752137     13\n",
       "13            1438 W 63rd St      WEST ENGLEWOOD -87.660887  41.779632     14\n",
       "14            3420 W 63rd St        CHICAGO LAWN -87.708864  41.778987     15\n",
       "15         3120 S Halsted St          BRIDGEPORT -87.646408  41.837394     16\n",
       "16          3315 W Ogden Ave      NORTH LAWNDALE -87.708382  41.856685     17\n",
       "17        3151 W Harrison St  EAST GARFIELD PARK -87.705488  41.873582     18\n",
       "18  1412 S Blue Island Ave\\n      NEAR WEST SIDE -87.656973  41.862977     19\n",
       "19     2150 N California Ave        LOGAN SQUARE -87.697452  41.921103     20\n",
       "20         5701 W Madison St              AUSTIN -87.768200  41.880083     21\n",
       "21      5151 N Milwaukee Ave      JEFFERSON PARK -87.766149  41.974094     22\n",
       "22         4650 N Pulaski Rd         ALBANY PARK -87.728115  41.966053     23"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_stations"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "92a77667-2b01-47cc-bdcc-b7e206c8e7c3",
   "metadata": {},
   "source": [
    "### New Community areas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "e7b46a37-67f7-4a91-a155-6ed120d01e7e",
   "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>ID_CA</th>\n",
       "      <th>community_area</th>\n",
       "      <th>population</th>\n",
       "      <th>area</th>\n",
       "      <th>neighbours</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>ROGERS PARK</td>\n",
       "      <td>55628</td>\n",
       "      <td>4.77</td>\n",
       "      <td>[1, 2, 77]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>WEST RIDGE</td>\n",
       "      <td>77122</td>\n",
       "      <td>9.14</td>\n",
       "      <td>[1, 2, 4, 13, 77]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>UPTOWN</td>\n",
       "      <td>57182</td>\n",
       "      <td>6.01</td>\n",
       "      <td>[3, 4, 5, 6, 77]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>LINCOLN SQUARE</td>\n",
       "      <td>40494</td>\n",
       "      <td>6.63</td>\n",
       "      <td>[2, 3, 4, 5, 6, 13, 14, 16, 77]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>NORTH CENTER</td>\n",
       "      <td>35114</td>\n",
       "      <td>5.31</td>\n",
       "      <td>[3, 4, 5, 6, 7, 14, 16, 21, 22]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>73</td>\n",
       "      <td>WASHINGTON HEIGHTS</td>\n",
       "      <td>25065</td>\n",
       "      <td>7.41</td>\n",
       "      <td>[49, 71, 72, 73, 75]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>74</td>\n",
       "      <td>MOUNT GREENWOOD</td>\n",
       "      <td>18628</td>\n",
       "      <td>7.02</td>\n",
       "      <td>[72, 74, 75]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>75</td>\n",
       "      <td>MORGAN PARK</td>\n",
       "      <td>21186</td>\n",
       "      <td>8.55</td>\n",
       "      <td>[49, 53, 72, 73, 74, 75]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>76</td>\n",
       "      <td>OHARE</td>\n",
       "      <td>13418</td>\n",
       "      <td>34.55</td>\n",
       "      <td>[10, 17, 76]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>77</td>\n",
       "      <td>EDGEWATER</td>\n",
       "      <td>56296</td>\n",
       "      <td>4.51</td>\n",
       "      <td>[1, 2, 3, 4, 77]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>77 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    ID_CA      community_area  population   area  \\\n",
       "0       1         ROGERS PARK       55628   4.77   \n",
       "1       2          WEST RIDGE       77122   9.14   \n",
       "2       3              UPTOWN       57182   6.01   \n",
       "3       4      LINCOLN SQUARE       40494   6.63   \n",
       "4       5        NORTH CENTER       35114   5.31   \n",
       "..    ...                 ...         ...    ...   \n",
       "72     73  WASHINGTON HEIGHTS       25065   7.41   \n",
       "73     74     MOUNT GREENWOOD       18628   7.02   \n",
       "74     75         MORGAN PARK       21186   8.55   \n",
       "75     76               OHARE       13418  34.55   \n",
       "76     77           EDGEWATER       56296   4.51   \n",
       "\n",
       "                         neighbours  \n",
       "0                        [1, 2, 77]  \n",
       "1                 [1, 2, 4, 13, 77]  \n",
       "2                  [3, 4, 5, 6, 77]  \n",
       "3   [2, 3, 4, 5, 6, 13, 14, 16, 77]  \n",
       "4   [3, 4, 5, 6, 7, 14, 16, 21, 22]  \n",
       "..                              ...  \n",
       "72             [49, 71, 72, 73, 75]  \n",
       "73                     [72, 74, 75]  \n",
       "74         [49, 53, 72, 73, 74, 75]  \n",
       "75                     [10, 17, 76]  \n",
       "76                 [1, 2, 3, 4, 77]  \n",
       "\n",
       "[77 rows x 5 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_community = community.copy()\n",
    "new_community.rename(columns={'ID':'ID_CA'}, inplace=True)\n",
    "\n",
    "new_community"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "30bc8bc7-c323-4d62-bbd0-297833b67803",
   "metadata": {},
   "source": [
    "### Distances D: distances between police stations and community areas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "7e0f7a5f-2348-471c-8097-50a70468ca40",
   "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>station_name</th>\n",
       "      <th>distances</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3510 S Michigan Ave</td>\n",
       "      <td>[2092.19, 2103.08, 1560.8, 1750.1, 1439.87, 13...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1160 N Larrabee St</td>\n",
       "      <td>[1217.56, 1283.21, 686.17, 930.23, 662.57, 467...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>850 W Addison St</td>\n",
       "      <td>[784.26, 885.85, 252.87, 540.93, 298.23, 106.0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5400 N Lincoln Ave</td>\n",
       "      <td>[478.94, 346.67, 482.46, 132.04, 511.79, 642.4...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1900 W Monterey Ave</td>\n",
       "      <td>[3025.14, 3036.02, 2493.75, 2683.05, 2372.81, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6464 N Clark St</td>\n",
       "      <td>[125.89, 311.05, 511.7, 415.38, 711.59, 740.61...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>5555 W Grand Ave</td>\n",
       "      <td>[1597.02, 1399.75, 1335.54, 1179.09, 1029.81, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1718 S State St</td>\n",
       "      <td>[1833.29, 1941.22, 1377.79, 1588.24, 1278.01, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>5101 S Wentworth Ave</td>\n",
       "      <td>[2229.84, 2240.73, 1698.46, 1887.75, 1577.52, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>7040 S Cottage Grove Ave</td>\n",
       "      <td>[2596.72, 2607.61, 2065.33, 2254.63, 1944.4, 1...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2255 E 103rd St</td>\n",
       "      <td>[3001.86, 3012.75, 2470.48, 2659.77, 2349.54, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>727 E 111th St</td>\n",
       "      <td>[2944.69, 2955.58, 2413.3, 2602.6, 2292.37, 21...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>7808 S Halsted St</td>\n",
       "      <td>[2634.12, 2645.01, 2102.74, 2292.03, 1981.8, 1...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>1438 W 63rd St</td>\n",
       "      <td>[2596.68, 2607.57, 2065.29, 2254.59, 1944.36, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>3420 W 63rd St</td>\n",
       "      <td>[2688.95, 2699.84, 2157.56, 2346.86, 2036.63, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>3120 S Halsted St</td>\n",
       "      <td>[2073.43, 2084.32, 1542.05, 1731.34, 1421.11, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>3315 W Ogden Ave</td>\n",
       "      <td>[2020.02, 1822.75, 1620.34, 1602.09, 1289.65, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>3151 W Harrison St</td>\n",
       "      <td>[1848.82, 1659.79, 1425.9, 1439.13, 1099.01, 1...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>1412 S Blue Island Ave\\n</td>\n",
       "      <td>[1816.02, 1826.9, 1284.63, 1473.92, 1163.69, 1...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2150 N California Ave</td>\n",
       "      <td>[1220.91, 1054.28, 854.33, 828.24, 471.1, 649....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>5701 W Madison St</td>\n",
       "      <td>[2032.47, 1835.2, 1734.15, 1614.54, 1377.65, 1...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>5151 N Milwaukee Ave</td>\n",
       "      <td>[1138.76, 941.5, 1055.75, 794.08, 882.92, 1120...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>4650 N Pulaski Rd</td>\n",
       "      <td>[867.43, 670.16, 627.24, 449.49, 583.81, 723.9...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                station_name  \\\n",
       "0        3510 S Michigan Ave   \n",
       "1         1160 N Larrabee St   \n",
       "2           850 W Addison St   \n",
       "3         5400 N Lincoln Ave   \n",
       "4        1900 W Monterey Ave   \n",
       "5            6464 N Clark St   \n",
       "6           5555 W Grand Ave   \n",
       "7            1718 S State St   \n",
       "8       5101 S Wentworth Ave   \n",
       "9   7040 S Cottage Grove Ave   \n",
       "10           2255 E 103rd St   \n",
       "11            727 E 111th St   \n",
       "12         7808 S Halsted St   \n",
       "13            1438 W 63rd St   \n",
       "14            3420 W 63rd St   \n",
       "15         3120 S Halsted St   \n",
       "16          3315 W Ogden Ave   \n",
       "17        3151 W Harrison St   \n",
       "18  1412 S Blue Island Ave\\n   \n",
       "19     2150 N California Ave   \n",
       "20         5701 W Madison St   \n",
       "21      5151 N Milwaukee Ave   \n",
       "22         4650 N Pulaski Rd   \n",
       "\n",
       "                                            distances  \n",
       "0   [2092.19, 2103.08, 1560.8, 1750.1, 1439.87, 13...  \n",
       "1   [1217.56, 1283.21, 686.17, 930.23, 662.57, 467...  \n",
       "2   [784.26, 885.85, 252.87, 540.93, 298.23, 106.0...  \n",
       "3   [478.94, 346.67, 482.46, 132.04, 511.79, 642.4...  \n",
       "4   [3025.14, 3036.02, 2493.75, 2683.05, 2372.81, ...  \n",
       "5   [125.89, 311.05, 511.7, 415.38, 711.59, 740.61...  \n",
       "6   [1597.02, 1399.75, 1335.54, 1179.09, 1029.81, ...  \n",
       "7   [1833.29, 1941.22, 1377.79, 1588.24, 1278.01, ...  \n",
       "8   [2229.84, 2240.73, 1698.46, 1887.75, 1577.52, ...  \n",
       "9   [2596.72, 2607.61, 2065.33, 2254.63, 1944.4, 1...  \n",
       "10  [3001.86, 3012.75, 2470.48, 2659.77, 2349.54, ...  \n",
       "11  [2944.69, 2955.58, 2413.3, 2602.6, 2292.37, 21...  \n",
       "12  [2634.12, 2645.01, 2102.74, 2292.03, 1981.8, 1...  \n",
       "13  [2596.68, 2607.57, 2065.29, 2254.59, 1944.36, ...  \n",
       "14  [2688.95, 2699.84, 2157.56, 2346.86, 2036.63, ...  \n",
       "15  [2073.43, 2084.32, 1542.05, 1731.34, 1421.11, ...  \n",
       "16  [2020.02, 1822.75, 1620.34, 1602.09, 1289.65, ...  \n",
       "17  [1848.82, 1659.79, 1425.9, 1439.13, 1099.01, 1...  \n",
       "18  [1816.02, 1826.9, 1284.63, 1473.92, 1163.69, 1...  \n",
       "19  [1220.91, 1054.28, 854.33, 828.24, 471.1, 649....  \n",
       "20  [2032.47, 1835.2, 1734.15, 1614.54, 1377.65, 1...  \n",
       "21  [1138.76, 941.5, 1055.75, 794.08, 882.92, 1120...  \n",
       "22  [867.43, 670.16, 627.24, 449.49, 583.81, 723.9...  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "D_distances = stations.merge(distances, on = \"station_name\")\n",
    "D_distances.drop(columns=['community_area', 'longitude', 'latitude'], inplace=True)\n",
    "D_distances\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2e005db6-ae18-4c53-ab09-d22efe9db85b",
   "metadata": {},
   "source": [
    "### Distances d: distances between police stations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "e09b45ae-c032-4143-8329-9473aa711387",
   "metadata": {},
   "outputs": [],
   "source": [
    "# !pip install openrouteservice"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "f00afc91-e396-4a6a-995c-dcec79c7ae62",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/aitana/.config/jupyterlab-desktop/jlab_server/lib/python3.12/site-packages/openrouteservice/client.py:211: UserWarning: Rate limit exceeded. Retrying for the 1st time.\n",
      "  warnings.warn('Rate limit exceeded. Retrying for the {0}{1} time.'.format(retry_counter + 1,\n",
      "/home/aitana/.config/jupyterlab-desktop/jlab_server/lib/python3.12/site-packages/openrouteservice/client.py:211: UserWarning: Rate limit exceeded. Retrying for the 2nd time.\n",
      "  warnings.warn('Rate limit exceeded. Retrying for the {0}{1} time.'.format(retry_counter + 1,\n",
      "/home/aitana/.config/jupyterlab-desktop/jlab_server/lib/python3.12/site-packages/openrouteservice/client.py:211: UserWarning: Rate limit exceeded. Retrying for the 3rd time.\n",
      "  warnings.warn('Rate limit exceeded. Retrying for the {0}{1} time.'.format(retry_counter + 1,\n",
      "/home/aitana/.config/jupyterlab-desktop/jlab_server/lib/python3.12/site-packages/openrouteservice/client.py:211: UserWarning: Rate limit exceeded. Retrying for the 4th time.\n",
      "  warnings.warn('Rate limit exceeded. Retrying for the {0}{1} time.'.format(retry_counter + 1,\n",
      "/home/aitana/.config/jupyterlab-desktop/jlab_server/lib/python3.12/site-packages/openrouteservice/client.py:211: UserWarning: Rate limit exceeded. Retrying for the 5th time.\n",
      "  warnings.warn('Rate limit exceeded. Retrying for the {0}{1} time.'.format(retry_counter + 1,\n",
      "/home/aitana/.config/jupyterlab-desktop/jlab_server/lib/python3.12/site-packages/openrouteservice/client.py:211: UserWarning: Rate limit exceeded. Retrying for the 6th time.\n",
      "  warnings.warn('Rate limit exceeded. Retrying for the {0}{1} time.'.format(retry_counter + 1,\n"
     ]
    }
   ],
   "source": [
    "import openrouteservice\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "import time\n",
    "\n",
    "client = openrouteservice.Client(key='5b3ce3597851110001cf6248a37fc158156a4ae3aa37ff6255cb6a41')\n",
    "\n",
    "# Función para calcular el tiempo de viaje entre dos coordenadas\n",
    "def travel_time(coord1, coord2):\n",
    "    try:\n",
    "        route = client.directions(\n",
    "            coordinates=[coord1, coord2],\n",
    "            profile='driving-car',\n",
    "            format='geojson')\n",
    "        \n",
    "        return route['features'][0]['properties']['segments'][0]['duration']\n",
    "        \n",
    "    except Exception as e:\n",
    "        print(f\"Error calculando tiempo entre {coord1} y {coord2}: {e}\")\n",
    "        if cord1 (-87.64422891, 41.75213684) and cord2 == (-87.70548813, 41.87358229):\n",
    "            print(\"SOLUCIONADO A MANO\")\n",
    "            return 1200\n",
    "        return np.nan\n",
    "\n",
    "# Extraer las coordenadas y nombres de estaciones\n",
    "locations = list(zip(new_stations['latitude'], new_stations['longitude']))\n",
    "station_names = new_stations['station_name']\n",
    "station_ids = new_stations['ID_PS']\n",
    "\n",
    "travel_times = pd.DataFrame(index=station_names, columns=station_names)\n",
    "\n",
    "\n",
    "# Rellenar la matriz, calculando solo las distancias que faltan\n",
    "for i, station1 in enumerate(station_names):\n",
    "    coord1 = (new_stations.loc[i, 'longitude'], new_stations.loc[i, 'latitude'])\n",
    "    \n",
    "    for j in range(i + 1, len(station_names)):\n",
    "        station2 = station_names[j]\n",
    "        \n",
    "        # Solo calcula si no existe el valor en la cache\n",
    "        if pd.isna(travel_times.loc[station1, station2]):\n",
    "            coord2 = (new_stations.loc[j, 'longitude'], new_stations.loc[j, 'latitude'])\n",
    "            travel_time_value = travel_time(coord1, coord2)\n",
    "            \n",
    "            # Almacenar el tiempo en ambas direcciones\n",
    "            travel_times.loc[station1, station2] = travel_time_value\n",
    "            travel_times.loc[station2, station1] = travel_time_value\n",
    "            \n",
    "            # Pausa para evitar límite de solicitudes\n",
    "            time.sleep(1)\n",
    "\n",
    "# Asignar 0 para el tiempo de viaje de cada estación a sí misma\n",
    "for station in station_names:\n",
    "    travel_times.loc[station, station] = 0\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "2b5d1e15-990d-4325-a311-bf7170a4a220",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Generar el DataFrame\n",
    "d_distances = {\n",
    "    \"station_name\": station_names,\n",
    "    \"ID_PS\": station_ids,\n",
    "    \"d_distances\": [travel_times.loc[station].tolist() for station in station_names]}\n",
    "\n",
    "d_distances = pd.DataFrame(d_distances)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "ebb02642-b0f8-4399-ab8c-7ca7a312340f",
   "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>station_name</th>\n",
       "      <th>ID_PS</th>\n",
       "      <th>d_distances</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3510 S Michigan Ave</td>\n",
       "      <td>1</td>\n",
       "      <td>[0, 989.7, 1360.4, 1809.6, 1227.2, 2021.8, 161...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1160 N Larrabee St</td>\n",
       "      <td>2</td>\n",
       "      <td>[989.7, 0, 487.2, 992.2, 1827.8, 1154.4, 1160....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>850 W Addison St</td>\n",
       "      <td>3</td>\n",
       "      <td>[1360.4, 487.2, 0, 615.9, 2216.9, 709.2, 1220....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5400 N Lincoln Ave</td>\n",
       "      <td>4</td>\n",
       "      <td>[1809.6, 992.2, 615.9, 0, 2637.8, 373.8, 1185....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1900 W Monterey Ave</td>\n",
       "      <td>5</td>\n",
       "      <td>[1227.2, 1827.8, 2216.9, 2637.8, 0, 2951.4, 25...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6464 N Clark St</td>\n",
       "      <td>6</td>\n",
       "      <td>[2021.8, 1154.4, 709.2, 373.8, 2951.4, 0, 1514...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>5555 W Grand Ave</td>\n",
       "      <td>7</td>\n",
       "      <td>[1618.0, 1160.6, 1220.3, 1185.4, 2547.6, 1514....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1718 S State St</td>\n",
       "      <td>8</td>\n",
       "      <td>[410.2, 756.2, 1195.2, 1685.7, 1370.4, 1705.7,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>5101 S Wentworth Ave</td>\n",
       "      <td>9</td>\n",
       "      <td>[341.5, 1103.8, 1492.9, 1913.8, 1015.8, 2117.3...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>7040 S Cottage Grove Ave</td>\n",
       "      <td>10</td>\n",
       "      <td>[722.6, 1383.8, 1772.8, 2193.8, 878.0, 2397.3,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2255 E 103rd St</td>\n",
       "      <td>11</td>\n",
       "      <td>[1305.0, 1914.3, 2303.3, 2724.3, 787.7, 2927.7...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>727 E 111th St</td>\n",
       "      <td>12</td>\n",
       "      <td>[1232.9, 1833.5, 2222.5, 2643.5, 489.4, 2846.9...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>7808 S Halsted St</td>\n",
       "      <td>13</td>\n",
       "      <td>[825.3, 1425.9, 1815.0, 2235.9, 805.0, 2439.4,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>1438 W 63rd St</td>\n",
       "      <td>14</td>\n",
       "      <td>[766.6, 1389.2, 1778.3, 2199.2, 956.2, 2402.7,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>3420 W 63rd St</td>\n",
       "      <td>15</td>\n",
       "      <td>[1066.6, 1680.8, 2069.8, 2490.8, 1252.8, 2694....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>3120 S Halsted St</td>\n",
       "      <td>16</td>\n",
       "      <td>[315.0, 904.9, 1293.9, 1714.9, 1364.9, 1918.4,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>3315 W Ogden Ave</td>\n",
       "      <td>17</td>\n",
       "      <td>[1016.1, 1046.3, 1379.0, 1568.6, 1945.7, 1898....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>3151 W Harrison St</td>\n",
       "      <td>18</td>\n",
       "      <td>[909.7, 853.4, 1192.6, 1412.7, 1839.3, 1733.3,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>1412 S Blue Island Ave\\n</td>\n",
       "      <td>19</td>\n",
       "      <td>[584.8, 747.4, 1138.7, 1559.7, 1514.4, 1763.1,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2150 N California Ave</td>\n",
       "      <td>20</td>\n",
       "      <td>[1246.0, 617.1, 664.3, 810.7, 2175.6, 1108.8, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>5701 W Madison St</td>\n",
       "      <td>21</td>\n",
       "      <td>[1252.6, 1189.9, 1520.7, 1603.7, 2182.2, 1933....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>5151 N Milwaukee Ave</td>\n",
       "      <td>22</td>\n",
       "      <td>[1815.1, 1186.2, 1058.9, 768.7, 2744.7, 1031.2...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>4650 N Pulaski Rd</td>\n",
       "      <td>23</td>\n",
       "      <td>[1626.6, 997.7, 707.0, 424.1, 2556.2, 753.5, 7...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                station_name  ID_PS  \\\n",
       "0        3510 S Michigan Ave      1   \n",
       "1         1160 N Larrabee St      2   \n",
       "2           850 W Addison St      3   \n",
       "3         5400 N Lincoln Ave      4   \n",
       "4        1900 W Monterey Ave      5   \n",
       "5            6464 N Clark St      6   \n",
       "6           5555 W Grand Ave      7   \n",
       "7            1718 S State St      8   \n",
       "8       5101 S Wentworth Ave      9   \n",
       "9   7040 S Cottage Grove Ave     10   \n",
       "10           2255 E 103rd St     11   \n",
       "11            727 E 111th St     12   \n",
       "12         7808 S Halsted St     13   \n",
       "13            1438 W 63rd St     14   \n",
       "14            3420 W 63rd St     15   \n",
       "15         3120 S Halsted St     16   \n",
       "16          3315 W Ogden Ave     17   \n",
       "17        3151 W Harrison St     18   \n",
       "18  1412 S Blue Island Ave\\n     19   \n",
       "19     2150 N California Ave     20   \n",
       "20         5701 W Madison St     21   \n",
       "21      5151 N Milwaukee Ave     22   \n",
       "22         4650 N Pulaski Rd     23   \n",
       "\n",
       "                                          d_distances  \n",
       "0   [0, 989.7, 1360.4, 1809.6, 1227.2, 2021.8, 161...  \n",
       "1   [989.7, 0, 487.2, 992.2, 1827.8, 1154.4, 1160....  \n",
       "2   [1360.4, 487.2, 0, 615.9, 2216.9, 709.2, 1220....  \n",
       "3   [1809.6, 992.2, 615.9, 0, 2637.8, 373.8, 1185....  \n",
       "4   [1227.2, 1827.8, 2216.9, 2637.8, 0, 2951.4, 25...  \n",
       "5   [2021.8, 1154.4, 709.2, 373.8, 2951.4, 0, 1514...  \n",
       "6   [1618.0, 1160.6, 1220.3, 1185.4, 2547.6, 1514....  \n",
       "7   [410.2, 756.2, 1195.2, 1685.7, 1370.4, 1705.7,...  \n",
       "8   [341.5, 1103.8, 1492.9, 1913.8, 1015.8, 2117.3...  \n",
       "9   [722.6, 1383.8, 1772.8, 2193.8, 878.0, 2397.3,...  \n",
       "10  [1305.0, 1914.3, 2303.3, 2724.3, 787.7, 2927.7...  \n",
       "11  [1232.9, 1833.5, 2222.5, 2643.5, 489.4, 2846.9...  \n",
       "12  [825.3, 1425.9, 1815.0, 2235.9, 805.0, 2439.4,...  \n",
       "13  [766.6, 1389.2, 1778.3, 2199.2, 956.2, 2402.7,...  \n",
       "14  [1066.6, 1680.8, 2069.8, 2490.8, 1252.8, 2694....  \n",
       "15  [315.0, 904.9, 1293.9, 1714.9, 1364.9, 1918.4,...  \n",
       "16  [1016.1, 1046.3, 1379.0, 1568.6, 1945.7, 1898....  \n",
       "17  [909.7, 853.4, 1192.6, 1412.7, 1839.3, 1733.3,...  \n",
       "18  [584.8, 747.4, 1138.7, 1559.7, 1514.4, 1763.1,...  \n",
       "19  [1246.0, 617.1, 664.3, 810.7, 2175.6, 1108.8, ...  \n",
       "20  [1252.6, 1189.9, 1520.7, 1603.7, 2182.2, 1933....  \n",
       "21  [1815.1, 1186.2, 1058.9, 768.7, 2744.7, 1031.2...  \n",
       "22  [1626.6, 997.7, 707.0, 424.1, 2556.2, 753.5, 7...  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d_distances"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "40860b5f-9e23-4b32-b643-adb7832f4ece",
   "metadata": {},
   "source": [
    "### Criminality index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "bce07f45-edc6-4c9c-82e5-ca90bde711b4",
   "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>ID_CA</th>\n",
       "      <th>community_area</th>\n",
       "      <th>population</th>\n",
       "      <th>area</th>\n",
       "      <th>neighbours</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>ROGERS PARK</td>\n",
       "      <td>55628</td>\n",
       "      <td>4.77</td>\n",
       "      <td>[1, 2, 77]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>WEST RIDGE</td>\n",
       "      <td>77122</td>\n",
       "      <td>9.14</td>\n",
       "      <td>[1, 2, 4, 13, 77]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>UPTOWN</td>\n",
       "      <td>57182</td>\n",
       "      <td>6.01</td>\n",
       "      <td>[3, 4, 5, 6, 77]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>LINCOLN SQUARE</td>\n",
       "      <td>40494</td>\n",
       "      <td>6.63</td>\n",
       "      <td>[2, 3, 4, 5, 6, 13, 14, 16, 77]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>NORTH CENTER</td>\n",
       "      <td>35114</td>\n",
       "      <td>5.31</td>\n",
       "      <td>[3, 4, 5, 6, 7, 14, 16, 21, 22]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ID_CA  community_area  population  area                       neighbours\n",
       "0      1     ROGERS PARK       55628  4.77                       [1, 2, 77]\n",
       "1      2      WEST RIDGE       77122  9.14                [1, 2, 4, 13, 77]\n",
       "2      3          UPTOWN       57182  6.01                 [3, 4, 5, 6, 77]\n",
       "3      4  LINCOLN SQUARE       40494  6.63  [2, 3, 4, 5, 6, 13, 14, 16, 77]\n",
       "4      5    NORTH CENTER       35114  5.31  [3, 4, 5, 6, 7, 14, 16, 21, 22]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_community.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "e6acb357-16cd-4af5-be5f-16b4cfef4a7c",
   "metadata": {},
   "outputs": [],
   "source": [
    "crimes_per_community = crimes.groupby(\"community_area\").size()\n",
    "crimes_per_community = crimes_per_community.reset_index()\n",
    "crimes_per_community.columns = ['community_area', 'criminality_index']\n",
    "crimes_per_community['criminality_index'] = crimes_per_community['criminality_index']/sum(crimes_per_community['criminality_index'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "cc74ede9-f7e8-4ae3-9aef-9a73bd382f8d",
   "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>index</th>\n",
       "      <th>community_area</th>\n",
       "      <th>criminality_index</th>\n",
       "      <th>ID_CA</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>60</td>\n",
       "      <td>ROGERS PARK</td>\n",
       "      <td>0.017029</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>74</td>\n",
       "      <td>WEST RIDGE</td>\n",
       "      <td>0.016114</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>66</td>\n",
       "      <td>UPTOWN</td>\n",
       "      <td>0.016228</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>39</td>\n",
       "      <td>LINCOLN SQUARE</td>\n",
       "      <td>0.008787</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>51</td>\n",
       "      <td>NORTH CENTER</td>\n",
       "      <td>0.005632</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>67</td>\n",
       "      <td>WASHINGTON HEIGHTS</td>\n",
       "      <td>0.012218</td>\n",
       "      <td>73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>46</td>\n",
       "      <td>MOUNT GREENWOOD</td>\n",
       "      <td>0.002248</td>\n",
       "      <td>74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>45</td>\n",
       "      <td>MORGAN PARK</td>\n",
       "      <td>0.007599</td>\n",
       "      <td>75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>56</td>\n",
       "      <td>OHARE</td>\n",
       "      <td>0.006995</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>21</td>\n",
       "      <td>EDGEWATER</td>\n",
       "      <td>0.012048</td>\n",
       "      <td>77</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>77 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    index      community_area  criminality_index  ID_CA\n",
       "0      60         ROGERS PARK           0.017029      1\n",
       "1      74          WEST RIDGE           0.016114      2\n",
       "2      66              UPTOWN           0.016228      3\n",
       "3      39      LINCOLN SQUARE           0.008787      4\n",
       "4      51        NORTH CENTER           0.005632      5\n",
       "..    ...                 ...                ...    ...\n",
       "72     67  WASHINGTON HEIGHTS           0.012218     73\n",
       "73     46     MOUNT GREENWOOD           0.002248     74\n",
       "74     45         MORGAN PARK           0.007599     75\n",
       "75     56               OHARE           0.006995     76\n",
       "76     21           EDGEWATER           0.012048     77\n",
       "\n",
       "[77 rows x 4 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "R_criminalities = crimes_per_community.merge(new_community, on=\"community_area\")\n",
    "R_criminalities.drop(columns=[\"population\", \"area\", \"neighbours\"], inplace=True)\n",
    "R_criminalities = R_criminalities.sort_values(\"ID_CA\").reset_index()\n",
    "R_criminalities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "34ad9adf-3298-43ca-ad40-d850bc94e232",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1.0"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sum(R_criminalities['criminality_index'])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fe0335ac-fe68-4e3c-9c96-0b541eba8f17",
   "metadata": {},
   "source": [
    "## Organizing new data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7b7ba1a2-9dc3-4cf8-b01b-c7ef8ad40e47",
   "metadata": {},
   "source": [
    "### Police stations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "d38381a1-4ec7-45d5-84e0-8436c9de5099",
   "metadata": {},
   "outputs": [],
   "source": [
    "police_stations = new_stations.merge(D_distances, on=\"station_name\")\n",
    "police_stations = police_stations.merge(d_distances, on=\"station_name\")\n",
    "police_stations = police_stations.rename(columns={\"distances\":\"D_distances\", 'ID_PS_x': 'ID_PS'})\n",
    "police_stations.drop(columns=['ID_PS_y'], inplace= True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "ffaf743f-178c-4a6b-9a4e-6ea0727259f3",
   "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>station_name</th>\n",
       "      <th>community_area</th>\n",
       "      <th>longitude</th>\n",
       "      <th>latitude</th>\n",
       "      <th>ID_PS</th>\n",
       "      <th>D_distances</th>\n",
       "      <th>d_distances</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3510 S Michigan Ave</td>\n",
       "      <td>DOUGLAS</td>\n",
       "      <td>-87.623395</td>\n",
       "      <td>41.830702</td>\n",
       "      <td>1</td>\n",
       "      <td>[2092.19, 2103.08, 1560.8, 1750.1, 1439.87, 13...</td>\n",
       "      <td>[0, 989.7, 1360.4, 1809.6, 1227.2, 2021.8, 161...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1160 N Larrabee St</td>\n",
       "      <td>NEAR NORTH SIDE</td>\n",
       "      <td>-87.643352</td>\n",
       "      <td>41.903242</td>\n",
       "      <td>2</td>\n",
       "      <td>[1217.56, 1283.21, 686.17, 930.23, 662.57, 467...</td>\n",
       "      <td>[989.7, 0, 487.2, 992.2, 1827.8, 1154.4, 1160....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>850 W Addison St</td>\n",
       "      <td>LAKE VIEW</td>\n",
       "      <td>-87.651512</td>\n",
       "      <td>41.947400</td>\n",
       "      <td>3</td>\n",
       "      <td>[784.26, 885.85, 252.87, 540.93, 298.23, 106.0...</td>\n",
       "      <td>[1360.4, 487.2, 0, 615.9, 2216.9, 709.2, 1220....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5400 N Lincoln Ave</td>\n",
       "      <td>LINCOLN SQUARE</td>\n",
       "      <td>-87.692845</td>\n",
       "      <td>41.979550</td>\n",
       "      <td>4</td>\n",
       "      <td>[478.94, 346.67, 482.46, 132.04, 511.79, 642.4...</td>\n",
       "      <td>[1809.6, 992.2, 615.9, 0, 2637.8, 373.8, 1185....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1900 W Monterey Ave</td>\n",
       "      <td>MORGAN PARK</td>\n",
       "      <td>-87.668520</td>\n",
       "      <td>41.691435</td>\n",
       "      <td>5</td>\n",
       "      <td>[3025.14, 3036.02, 2493.75, 2683.05, 2372.81, ...</td>\n",
       "      <td>[1227.2, 1827.8, 2216.9, 2637.8, 0, 2951.4, 25...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6464 N Clark St</td>\n",
       "      <td>ROGERS PARK</td>\n",
       "      <td>-87.671324</td>\n",
       "      <td>41.999763</td>\n",
       "      <td>6</td>\n",
       "      <td>[125.89, 311.05, 511.7, 415.38, 711.59, 740.61...</td>\n",
       "      <td>[2021.8, 1154.4, 709.2, 373.8, 2951.4, 0, 1514...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>5555 W Grand Ave</td>\n",
       "      <td>BELMONT CRAGIN</td>\n",
       "      <td>-87.765574</td>\n",
       "      <td>41.918609</td>\n",
       "      <td>7</td>\n",
       "      <td>[1597.02, 1399.75, 1335.54, 1179.09, 1029.81, ...</td>\n",
       "      <td>[1618.0, 1160.6, 1220.3, 1185.4, 2547.6, 1514....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1718 S State St</td>\n",
       "      <td>NEAR SOUTH SIDE</td>\n",
       "      <td>-87.627356</td>\n",
       "      <td>41.858373</td>\n",
       "      <td>8</td>\n",
       "      <td>[1833.29, 1941.22, 1377.79, 1588.24, 1278.01, ...</td>\n",
       "      <td>[410.2, 756.2, 1195.2, 1685.7, 1370.4, 1705.7,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>5101 S Wentworth Ave</td>\n",
       "      <td>FULLER PARK</td>\n",
       "      <td>-87.630560</td>\n",
       "      <td>41.801811</td>\n",
       "      <td>9</td>\n",
       "      <td>[2229.84, 2240.73, 1698.46, 1887.75, 1577.52, ...</td>\n",
       "      <td>[341.5, 1103.8, 1492.9, 1913.8, 1015.8, 2117.3...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>7040 S Cottage Grove Ave</td>\n",
       "      <td>WOODLAWN</td>\n",
       "      <td>-87.605748</td>\n",
       "      <td>41.766431</td>\n",
       "      <td>10</td>\n",
       "      <td>[2596.72, 2607.61, 2065.33, 2254.63, 1944.4, 1...</td>\n",
       "      <td>[722.6, 1383.8, 1772.8, 2193.8, 878.0, 2397.3,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2255 E 103rd St</td>\n",
       "      <td>SOUTH DEERING</td>\n",
       "      <td>-87.568349</td>\n",
       "      <td>41.707933</td>\n",
       "      <td>11</td>\n",
       "      <td>[3001.86, 3012.75, 2470.48, 2659.77, 2349.54, ...</td>\n",
       "      <td>[1305.0, 1914.3, 2303.3, 2724.3, 787.7, 2927.7...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>727 E 111th St</td>\n",
       "      <td>PULLMAN</td>\n",
       "      <td>-87.604506</td>\n",
       "      <td>41.692723</td>\n",
       "      <td>12</td>\n",
       "      <td>[2944.69, 2955.58, 2413.3, 2602.6, 2292.37, 21...</td>\n",
       "      <td>[1232.9, 1833.5, 2222.5, 2643.5, 489.4, 2846.9...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>7808 S Halsted St</td>\n",
       "      <td>AUBURN GRESHAM</td>\n",
       "      <td>-87.644229</td>\n",
       "      <td>41.752137</td>\n",
       "      <td>13</td>\n",
       "      <td>[2634.12, 2645.01, 2102.74, 2292.03, 1981.8, 1...</td>\n",
       "      <td>[825.3, 1425.9, 1815.0, 2235.9, 805.0, 2439.4,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>1438 W 63rd St</td>\n",
       "      <td>WEST ENGLEWOOD</td>\n",
       "      <td>-87.660887</td>\n",
       "      <td>41.779632</td>\n",
       "      <td>14</td>\n",
       "      <td>[2596.68, 2607.57, 2065.29, 2254.59, 1944.36, ...</td>\n",
       "      <td>[766.6, 1389.2, 1778.3, 2199.2, 956.2, 2402.7,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>3420 W 63rd St</td>\n",
       "      <td>CHICAGO LAWN</td>\n",
       "      <td>-87.708864</td>\n",
       "      <td>41.778987</td>\n",
       "      <td>15</td>\n",
       "      <td>[2688.95, 2699.84, 2157.56, 2346.86, 2036.63, ...</td>\n",
       "      <td>[1066.6, 1680.8, 2069.8, 2490.8, 1252.8, 2694....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>3120 S Halsted St</td>\n",
       "      <td>BRIDGEPORT</td>\n",
       "      <td>-87.646408</td>\n",
       "      <td>41.837394</td>\n",
       "      <td>16</td>\n",
       "      <td>[2073.43, 2084.32, 1542.05, 1731.34, 1421.11, ...</td>\n",
       "      <td>[315.0, 904.9, 1293.9, 1714.9, 1364.9, 1918.4,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>3315 W Ogden Ave</td>\n",
       "      <td>NORTH LAWNDALE</td>\n",
       "      <td>-87.708382</td>\n",
       "      <td>41.856685</td>\n",
       "      <td>17</td>\n",
       "      <td>[2020.02, 1822.75, 1620.34, 1602.09, 1289.65, ...</td>\n",
       "      <td>[1016.1, 1046.3, 1379.0, 1568.6, 1945.7, 1898....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>3151 W Harrison St</td>\n",
       "      <td>EAST GARFIELD PARK</td>\n",
       "      <td>-87.705488</td>\n",
       "      <td>41.873582</td>\n",
       "      <td>18</td>\n",
       "      <td>[1848.82, 1659.79, 1425.9, 1439.13, 1099.01, 1...</td>\n",
       "      <td>[909.7, 853.4, 1192.6, 1412.7, 1839.3, 1733.3,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>1412 S Blue Island Ave\\n</td>\n",
       "      <td>NEAR WEST SIDE</td>\n",
       "      <td>-87.656973</td>\n",
       "      <td>41.862977</td>\n",
       "      <td>19</td>\n",
       "      <td>[1816.02, 1826.9, 1284.63, 1473.92, 1163.69, 1...</td>\n",
       "      <td>[584.8, 747.4, 1138.7, 1559.7, 1514.4, 1763.1,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2150 N California Ave</td>\n",
       "      <td>LOGAN SQUARE</td>\n",
       "      <td>-87.697452</td>\n",
       "      <td>41.921103</td>\n",
       "      <td>20</td>\n",
       "      <td>[1220.91, 1054.28, 854.33, 828.24, 471.1, 649....</td>\n",
       "      <td>[1246.0, 617.1, 664.3, 810.7, 2175.6, 1108.8, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>5701 W Madison St</td>\n",
       "      <td>AUSTIN</td>\n",
       "      <td>-87.768200</td>\n",
       "      <td>41.880083</td>\n",
       "      <td>21</td>\n",
       "      <td>[2032.47, 1835.2, 1734.15, 1614.54, 1377.65, 1...</td>\n",
       "      <td>[1252.6, 1189.9, 1520.7, 1603.7, 2182.2, 1933....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>5151 N Milwaukee Ave</td>\n",
       "      <td>JEFFERSON PARK</td>\n",
       "      <td>-87.766149</td>\n",
       "      <td>41.974094</td>\n",
       "      <td>22</td>\n",
       "      <td>[1138.76, 941.5, 1055.75, 794.08, 882.92, 1120...</td>\n",
       "      <td>[1815.1, 1186.2, 1058.9, 768.7, 2744.7, 1031.2...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>4650 N Pulaski Rd</td>\n",
       "      <td>ALBANY PARK</td>\n",
       "      <td>-87.728115</td>\n",
       "      <td>41.966053</td>\n",
       "      <td>23</td>\n",
       "      <td>[867.43, 670.16, 627.24, 449.49, 583.81, 723.9...</td>\n",
       "      <td>[1626.6, 997.7, 707.0, 424.1, 2556.2, 753.5, 7...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                station_name      community_area  longitude   latitude  ID_PS  \\\n",
       "0        3510 S Michigan Ave             DOUGLAS -87.623395  41.830702      1   \n",
       "1         1160 N Larrabee St     NEAR NORTH SIDE -87.643352  41.903242      2   \n",
       "2           850 W Addison St           LAKE VIEW -87.651512  41.947400      3   \n",
       "3         5400 N Lincoln Ave      LINCOLN SQUARE -87.692845  41.979550      4   \n",
       "4        1900 W Monterey Ave         MORGAN PARK -87.668520  41.691435      5   \n",
       "5            6464 N Clark St         ROGERS PARK -87.671324  41.999763      6   \n",
       "6           5555 W Grand Ave      BELMONT CRAGIN -87.765574  41.918609      7   \n",
       "7            1718 S State St     NEAR SOUTH SIDE -87.627356  41.858373      8   \n",
       "8       5101 S Wentworth Ave         FULLER PARK -87.630560  41.801811      9   \n",
       "9   7040 S Cottage Grove Ave            WOODLAWN -87.605748  41.766431     10   \n",
       "10           2255 E 103rd St       SOUTH DEERING -87.568349  41.707933     11   \n",
       "11            727 E 111th St             PULLMAN -87.604506  41.692723     12   \n",
       "12         7808 S Halsted St      AUBURN GRESHAM -87.644229  41.752137     13   \n",
       "13            1438 W 63rd St      WEST ENGLEWOOD -87.660887  41.779632     14   \n",
       "14            3420 W 63rd St        CHICAGO LAWN -87.708864  41.778987     15   \n",
       "15         3120 S Halsted St          BRIDGEPORT -87.646408  41.837394     16   \n",
       "16          3315 W Ogden Ave      NORTH LAWNDALE -87.708382  41.856685     17   \n",
       "17        3151 W Harrison St  EAST GARFIELD PARK -87.705488  41.873582     18   \n",
       "18  1412 S Blue Island Ave\\n      NEAR WEST SIDE -87.656973  41.862977     19   \n",
       "19     2150 N California Ave        LOGAN SQUARE -87.697452  41.921103     20   \n",
       "20         5701 W Madison St              AUSTIN -87.768200  41.880083     21   \n",
       "21      5151 N Milwaukee Ave      JEFFERSON PARK -87.766149  41.974094     22   \n",
       "22         4650 N Pulaski Rd         ALBANY PARK -87.728115  41.966053     23   \n",
       "\n",
       "                                          D_distances  \\\n",
       "0   [2092.19, 2103.08, 1560.8, 1750.1, 1439.87, 13...   \n",
       "1   [1217.56, 1283.21, 686.17, 930.23, 662.57, 467...   \n",
       "2   [784.26, 885.85, 252.87, 540.93, 298.23, 106.0...   \n",
       "3   [478.94, 346.67, 482.46, 132.04, 511.79, 642.4...   \n",
       "4   [3025.14, 3036.02, 2493.75, 2683.05, 2372.81, ...   \n",
       "5   [125.89, 311.05, 511.7, 415.38, 711.59, 740.61...   \n",
       "6   [1597.02, 1399.75, 1335.54, 1179.09, 1029.81, ...   \n",
       "7   [1833.29, 1941.22, 1377.79, 1588.24, 1278.01, ...   \n",
       "8   [2229.84, 2240.73, 1698.46, 1887.75, 1577.52, ...   \n",
       "9   [2596.72, 2607.61, 2065.33, 2254.63, 1944.4, 1...   \n",
       "10  [3001.86, 3012.75, 2470.48, 2659.77, 2349.54, ...   \n",
       "11  [2944.69, 2955.58, 2413.3, 2602.6, 2292.37, 21...   \n",
       "12  [2634.12, 2645.01, 2102.74, 2292.03, 1981.8, 1...   \n",
       "13  [2596.68, 2607.57, 2065.29, 2254.59, 1944.36, ...   \n",
       "14  [2688.95, 2699.84, 2157.56, 2346.86, 2036.63, ...   \n",
       "15  [2073.43, 2084.32, 1542.05, 1731.34, 1421.11, ...   \n",
       "16  [2020.02, 1822.75, 1620.34, 1602.09, 1289.65, ...   \n",
       "17  [1848.82, 1659.79, 1425.9, 1439.13, 1099.01, 1...   \n",
       "18  [1816.02, 1826.9, 1284.63, 1473.92, 1163.69, 1...   \n",
       "19  [1220.91, 1054.28, 854.33, 828.24, 471.1, 649....   \n",
       "20  [2032.47, 1835.2, 1734.15, 1614.54, 1377.65, 1...   \n",
       "21  [1138.76, 941.5, 1055.75, 794.08, 882.92, 1120...   \n",
       "22  [867.43, 670.16, 627.24, 449.49, 583.81, 723.9...   \n",
       "\n",
       "                                          d_distances  \n",
       "0   [0, 989.7, 1360.4, 1809.6, 1227.2, 2021.8, 161...  \n",
       "1   [989.7, 0, 487.2, 992.2, 1827.8, 1154.4, 1160....  \n",
       "2   [1360.4, 487.2, 0, 615.9, 2216.9, 709.2, 1220....  \n",
       "3   [1809.6, 992.2, 615.9, 0, 2637.8, 373.8, 1185....  \n",
       "4   [1227.2, 1827.8, 2216.9, 2637.8, 0, 2951.4, 25...  \n",
       "5   [2021.8, 1154.4, 709.2, 373.8, 2951.4, 0, 1514...  \n",
       "6   [1618.0, 1160.6, 1220.3, 1185.4, 2547.6, 1514....  \n",
       "7   [410.2, 756.2, 1195.2, 1685.7, 1370.4, 1705.7,...  \n",
       "8   [341.5, 1103.8, 1492.9, 1913.8, 1015.8, 2117.3...  \n",
       "9   [722.6, 1383.8, 1772.8, 2193.8, 878.0, 2397.3,...  \n",
       "10  [1305.0, 1914.3, 2303.3, 2724.3, 787.7, 2927.7...  \n",
       "11  [1232.9, 1833.5, 2222.5, 2643.5, 489.4, 2846.9...  \n",
       "12  [825.3, 1425.9, 1815.0, 2235.9, 805.0, 2439.4,...  \n",
       "13  [766.6, 1389.2, 1778.3, 2199.2, 956.2, 2402.7,...  \n",
       "14  [1066.6, 1680.8, 2069.8, 2490.8, 1252.8, 2694....  \n",
       "15  [315.0, 904.9, 1293.9, 1714.9, 1364.9, 1918.4,...  \n",
       "16  [1016.1, 1046.3, 1379.0, 1568.6, 1945.7, 1898....  \n",
       "17  [909.7, 853.4, 1192.6, 1412.7, 1839.3, 1733.3,...  \n",
       "18  [584.8, 747.4, 1138.7, 1559.7, 1514.4, 1763.1,...  \n",
       "19  [1246.0, 617.1, 664.3, 810.7, 2175.6, 1108.8, ...  \n",
       "20  [1252.6, 1189.9, 1520.7, 1603.7, 2182.2, 1933....  \n",
       "21  [1815.1, 1186.2, 1058.9, 768.7, 2744.7, 1031.2...  \n",
       "22  [1626.6, 997.7, 707.0, 424.1, 2556.2, 753.5, 7...  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "police_stations"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "70339e57-3cb6-4f0c-a216-f9488792ea71",
   "metadata": {},
   "source": [
    "### Community areas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "b46d5b53-9595-41a8-bcde-427baffb4469",
   "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>ID_CA</th>\n",
       "      <th>community_area</th>\n",
       "      <th>population</th>\n",
       "      <th>area</th>\n",
       "      <th>neighbours</th>\n",
       "      <th>criminality_index</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>ROGERS PARK</td>\n",
       "      <td>55628</td>\n",
       "      <td>4.77</td>\n",
       "      <td>[1, 2, 77]</td>\n",
       "      <td>0.017029</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>WEST RIDGE</td>\n",
       "      <td>77122</td>\n",
       "      <td>9.14</td>\n",
       "      <td>[1, 2, 4, 13, 77]</td>\n",
       "      <td>0.016114</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>UPTOWN</td>\n",
       "      <td>57182</td>\n",
       "      <td>6.01</td>\n",
       "      <td>[3, 4, 5, 6, 77]</td>\n",
       "      <td>0.016228</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>LINCOLN SQUARE</td>\n",
       "      <td>40494</td>\n",
       "      <td>6.63</td>\n",
       "      <td>[2, 3, 4, 5, 6, 13, 14, 16, 77]</td>\n",
       "      <td>0.008787</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>NORTH CENTER</td>\n",
       "      <td>35114</td>\n",
       "      <td>5.31</td>\n",
       "      <td>[3, 4, 5, 6, 7, 14, 16, 21, 22]</td>\n",
       "      <td>0.005632</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>73</td>\n",
       "      <td>WASHINGTON HEIGHTS</td>\n",
       "      <td>25065</td>\n",
       "      <td>7.41</td>\n",
       "      <td>[49, 71, 72, 73, 75]</td>\n",
       "      <td>0.012218</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>74</td>\n",
       "      <td>MOUNT GREENWOOD</td>\n",
       "      <td>18628</td>\n",
       "      <td>7.02</td>\n",
       "      <td>[72, 74, 75]</td>\n",
       "      <td>0.002248</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>75</td>\n",
       "      <td>MORGAN PARK</td>\n",
       "      <td>21186</td>\n",
       "      <td>8.55</td>\n",
       "      <td>[49, 53, 72, 73, 74, 75]</td>\n",
       "      <td>0.007599</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>76</td>\n",
       "      <td>OHARE</td>\n",
       "      <td>13418</td>\n",
       "      <td>34.55</td>\n",
       "      <td>[10, 17, 76]</td>\n",
       "      <td>0.006995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>77</td>\n",
       "      <td>EDGEWATER</td>\n",
       "      <td>56296</td>\n",
       "      <td>4.51</td>\n",
       "      <td>[1, 2, 3, 4, 77]</td>\n",
       "      <td>0.012048</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>77 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    ID_CA      community_area  population   area  \\\n",
       "0       1         ROGERS PARK       55628   4.77   \n",
       "1       2          WEST RIDGE       77122   9.14   \n",
       "2       3              UPTOWN       57182   6.01   \n",
       "3       4      LINCOLN SQUARE       40494   6.63   \n",
       "4       5        NORTH CENTER       35114   5.31   \n",
       "..    ...                 ...         ...    ...   \n",
       "72     73  WASHINGTON HEIGHTS       25065   7.41   \n",
       "73     74     MOUNT GREENWOOD       18628   7.02   \n",
       "74     75         MORGAN PARK       21186   8.55   \n",
       "75     76               OHARE       13418  34.55   \n",
       "76     77           EDGEWATER       56296   4.51   \n",
       "\n",
       "                         neighbours  criminality_index  \n",
       "0                        [1, 2, 77]           0.017029  \n",
       "1                 [1, 2, 4, 13, 77]           0.016114  \n",
       "2                  [3, 4, 5, 6, 77]           0.016228  \n",
       "3   [2, 3, 4, 5, 6, 13, 14, 16, 77]           0.008787  \n",
       "4   [3, 4, 5, 6, 7, 14, 16, 21, 22]           0.005632  \n",
       "..                              ...                ...  \n",
       "72             [49, 71, 72, 73, 75]           0.012218  \n",
       "73                     [72, 74, 75]           0.002248  \n",
       "74         [49, 53, 72, 73, 74, 75]           0.007599  \n",
       "75                     [10, 17, 76]           0.006995  \n",
       "76                 [1, 2, 3, 4, 77]           0.012048  \n",
       "\n",
       "[77 rows x 6 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "community_areas = new_community.merge(R_criminalities[[\"ID_CA\", \"criminality_index\"]], on=\"ID_CA\")\n",
    "community_areas"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "26ef6fb2-320e-4149-bb64-4ba483eeee37",
   "metadata": {},
   "source": [
    "### Police areas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "32af202d-630c-4237-9a84-e8069e404564",
   "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>police_area</th>\n",
       "      <th>max_workload</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   police_area  max_workload\n",
       "0            1             0\n",
       "1            2             0\n",
       "2            3             0\n",
       "3            4             0\n",
       "4            5             0"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "police_areas = {'police_area': [1, 2, 3, 4, 5], 'max_workload': [0, 0, 0, 0, 0]}\n",
    "\n",
    "pd.DataFrame(police_areas)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e534b390-fe14-4923-b9a1-919a17c781aa",
   "metadata": {},
   "source": [
    "## Export and save"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "a574dcf2-3a9d-46c3-b3dc-84a2523a2af8",
   "metadata": {},
   "outputs": [],
   "source": [
    "police_stations.to_csv(\"./new_data/police_stations.csv\")\n",
    "police_stations.to_json(\"./new_data/police_stations.json\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "1e9d1ddc-a39d-4215-9c68-4e3ed70b2bc6",
   "metadata": {},
   "outputs": [],
   "source": [
    "community_areas.to_csv(\"./new_data/community_areas.csv\")\n",
    "community_areas.to_json(\"./new_data/community_areas.json\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "ef3274f1-49f1-48d6-a8ba-d06b23daf7d2",
   "metadata": {},
   "outputs": [],
   "source": [
    "d_distances.to_csv(\"./new_data/d_distances.csv\")\n",
    "d_distances.to_json(\"./new_data/d_distances.json\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "032f947c-3060-4828-b41f-53b3bd270b80",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "--- 1.0003259181976318 seconds ---\n"
     ]
    }
   ],
   "source": [
    "import time\n",
    "start_time = time.time()\n",
    "time.sleep(1)\n",
    "print(\"--- %s seconds ---\" % (time.time() - start_time))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d7cfd09a-2b42-46b2-9715-1929d3ad03a4",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.12.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}