combining_datasets.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import ast\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 183,
   "metadata": {},
   "outputs": [],
   "source": [
    "if os.name == 'posix':\n",
    "    grouped_location = '/Users/ernestvmo/OneDrive - Universiteit Leiden/Q1/Urban Computing/Project/data/grouped_csv/'\n",
    "else:\n",
    "    grouped_location = 'C:/Users/Ernest/OneDrive - Universiteit Leiden/Q1/Urban Computing/Project/data/grouped_csv/'\n",
    "\n",
    "saf_df = pd.read_csv(grouped_location + 'saf_grouped.csv', index_col=[0])\n",
    "nypd_df = pd.read_csv(grouped_location + 'grouped_nypd_complaints.csv', index_col=[0])\n",
    "_311_df = pd.read_csv(grouped_location + 'grouped_311.csv', index_col=[0])\n",
    "# taxi_df = pd.read_csv(grouped_location + 'grouped_taxi.csv', index_col=[0])\n",
    "taxi_pickup = pd.read_csv(grouped_location + 'taxi_pickup.csv', index_col=0)\n",
    "taxi_dropoff = pd.read_csv(grouped_location + 'taxi_dropoff.csv', index_col=0)\n",
    "\n",
    "nypd_df = nypd_df.rename(columns={'nypd_compaints_COUNT':'nypd_complaints_COUNT'})\n",
    "\n",
    "del taxi_pickup['Unnamed: 0.1'], taxi_dropoff['Unnamed: 0.1']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### SAF to NYPD"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 184,
   "metadata": {},
   "outputs": [],
   "source": [
    "saf_merge = nypd_df.merge(saf_df, how='left', on=['datestop','coordinates'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 185,
   "metadata": {},
   "outputs": [],
   "source": [
    "saf_merge.fillna(0, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 186,
   "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>datestop</th>\n",
       "      <th>coordinates</th>\n",
       "      <th>nypd_complaints_COUNT</th>\n",
       "      <th>SAF_COUNT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2012-07-01</td>\n",
       "      <td>(0, 20)</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2012-07-01</td>\n",
       "      <td>(0, 21)</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2012-07-01</td>\n",
       "      <td>(0, 22)</td>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2012-07-01</td>\n",
       "      <td>(1, 20)</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2012-07-01</td>\n",
       "      <td>(2, 20)</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</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>65990</th>\n",
       "      <td>2013-06-30</td>\n",
       "      <td>(21, 17)</td>\n",
       "      <td>5</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65991</th>\n",
       "      <td>2013-06-30</td>\n",
       "      <td>(22, 8)</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65992</th>\n",
       "      <td>2013-06-30</td>\n",
       "      <td>(22, 10)</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65993</th>\n",
       "      <td>2013-06-30</td>\n",
       "      <td>(22, 12)</td>\n",
       "      <td>1</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65994</th>\n",
       "      <td>2013-06-30</td>\n",
       "      <td>(22, 14)</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>65995 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         datestop coordinates  nypd_complaints_COUNT  SAF_COUNT\n",
       "0      2012-07-01     (0, 20)                      1        0.0\n",
       "1      2012-07-01     (0, 21)                      1        0.0\n",
       "2      2012-07-01     (0, 22)                      3        0.0\n",
       "3      2012-07-01     (1, 20)                      1        0.0\n",
       "4      2012-07-01     (2, 20)                      2        0.0\n",
       "...           ...         ...                    ...        ...\n",
       "65990  2013-06-30    (21, 17)                      5        0.0\n",
       "65991  2013-06-30     (22, 8)                      1        0.0\n",
       "65992  2013-06-30    (22, 10)                      1        0.0\n",
       "65993  2013-06-30    (22, 12)                      1        2.0\n",
       "65994  2013-06-30    (22, 14)                      1        0.0\n",
       "\n",
       "[65995 rows x 4 columns]"
      ]
     },
     "execution_count": 186,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "saf_merge"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 311 to NYPD"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 187,
   "metadata": {},
   "outputs": [],
   "source": [
    "_311_merged = nypd_df.merge(_311_df, how='left', on=['datestop', 'coordinates'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 188,
   "metadata": {},
   "outputs": [],
   "source": [
    "_311_merged = _311_merged.replace(np.nan, 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 189,
   "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>datestop</th>\n",
       "      <th>coordinates</th>\n",
       "      <th>nypd_complaints_COUNT</th>\n",
       "      <th>311_complaints_COUNT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2012-07-01</td>\n",
       "      <td>(0, 20)</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2012-07-01</td>\n",
       "      <td>(0, 21)</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2012-07-01</td>\n",
       "      <td>(0, 22)</td>\n",
       "      <td>3</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2012-07-01</td>\n",
       "      <td>(1, 20)</td>\n",
       "      <td>1</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2012-07-01</td>\n",
       "      <td>(2, 20)</td>\n",
       "      <td>2</td>\n",
       "      <td>3.0</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>65990</th>\n",
       "      <td>2013-06-30</td>\n",
       "      <td>(21, 17)</td>\n",
       "      <td>5</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65991</th>\n",
       "      <td>2013-06-30</td>\n",
       "      <td>(22, 8)</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65992</th>\n",
       "      <td>2013-06-30</td>\n",
       "      <td>(22, 10)</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65993</th>\n",
       "      <td>2013-06-30</td>\n",
       "      <td>(22, 12)</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65994</th>\n",
       "      <td>2013-06-30</td>\n",
       "      <td>(22, 14)</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>65995 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         datestop coordinates  nypd_complaints_COUNT  311_complaints_COUNT\n",
       "0      2012-07-01     (0, 20)                      1                   0.0\n",
       "1      2012-07-01     (0, 21)                      1                   1.0\n",
       "2      2012-07-01     (0, 22)                      3                   4.0\n",
       "3      2012-07-01     (1, 20)                      1                   3.0\n",
       "4      2012-07-01     (2, 20)                      2                   3.0\n",
       "...           ...         ...                    ...                   ...\n",
       "65990  2013-06-30    (21, 17)                      5                   1.0\n",
       "65991  2013-06-30     (22, 8)                      1                   0.0\n",
       "65992  2013-06-30    (22, 10)                      1                   0.0\n",
       "65993  2013-06-30    (22, 12)                      1                   0.0\n",
       "65994  2013-06-30    (22, 14)                      1                   0.0\n",
       "\n",
       "[65995 rows x 4 columns]"
      ]
     },
     "execution_count": 189,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "_311_merged"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Taxi"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Pickup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 190,
   "metadata": {},
   "outputs": [],
   "source": [
    "taxi_pickup = taxi_pickup.rename(columns={'pickup_datetime':'datestop'})\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 191,
   "metadata": {},
   "outputs": [],
   "source": [
    "taxi_pickup.sort_values(by=['datestop', 'coordinates'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 192,
   "metadata": {},
   "outputs": [],
   "source": [
    "taxi_pickup_merged = nypd_df.merge(taxi_pickup, how='left', on=['datestop','coordinates'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 193,
   "metadata": {},
   "outputs": [],
   "source": [
    "taxi_pickup_merged = taxi_pickup_merged.fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 194,
   "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>datestop</th>\n",
       "      <th>coordinates</th>\n",
       "      <th>nypd_complaints_COUNT</th>\n",
       "      <th>passenger_count</th>\n",
       "      <th>total_amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2012-07-01</td>\n",
       "      <td>(0, 20)</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2012-07-01</td>\n",
       "      <td>(0, 21)</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2012-07-01</td>\n",
       "      <td>(0, 22)</td>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2012-07-01</td>\n",
       "      <td>(1, 20)</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2012-07-01</td>\n",
       "      <td>(2, 20)</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</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>65990</th>\n",
       "      <td>2013-06-30</td>\n",
       "      <td>(21, 17)</td>\n",
       "      <td>5</td>\n",
       "      <td>2.0</td>\n",
       "      <td>74.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65991</th>\n",
       "      <td>2013-06-30</td>\n",
       "      <td>(22, 8)</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>72.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65992</th>\n",
       "      <td>2013-06-30</td>\n",
       "      <td>(22, 10)</td>\n",
       "      <td>1</td>\n",
       "      <td>7.0</td>\n",
       "      <td>60.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65993</th>\n",
       "      <td>2013-06-30</td>\n",
       "      <td>(22, 12)</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65994</th>\n",
       "      <td>2013-06-30</td>\n",
       "      <td>(22, 14)</td>\n",
       "      <td>1</td>\n",
       "      <td>16.0</td>\n",
       "      <td>312.93</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>65995 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         datestop coordinates  nypd_complaints_COUNT  passenger_count  \\\n",
       "0      2012-07-01     (0, 20)                      1              0.0   \n",
       "1      2012-07-01     (0, 21)                      1              0.0   \n",
       "2      2012-07-01     (0, 22)                      3              0.0   \n",
       "3      2012-07-01     (1, 20)                      1              0.0   \n",
       "4      2012-07-01     (2, 20)                      2              0.0   \n",
       "...           ...         ...                    ...              ...   \n",
       "65990  2013-06-30    (21, 17)                      5              2.0   \n",
       "65991  2013-06-30     (22, 8)                      1              1.0   \n",
       "65992  2013-06-30    (22, 10)                      1              7.0   \n",
       "65993  2013-06-30    (22, 12)                      1              0.0   \n",
       "65994  2013-06-30    (22, 14)                      1             16.0   \n",
       "\n",
       "       total_amount  \n",
       "0              0.00  \n",
       "1              0.00  \n",
       "2              0.00  \n",
       "3              0.00  \n",
       "4              0.00  \n",
       "...             ...  \n",
       "65990         74.50  \n",
       "65991         72.33  \n",
       "65992         60.00  \n",
       "65993          0.00  \n",
       "65994        312.93  \n",
       "\n",
       "[65995 rows x 5 columns]"
      ]
     },
     "execution_count": 194,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "taxi_pickup_merged"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Dropoff"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 195,
   "metadata": {},
   "outputs": [],
   "source": [
    "taxi_dropoff = taxi_dropoff.rename(columns={'dropoff_datetime':'datestop'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 196,
   "metadata": {},
   "outputs": [],
   "source": [
    "taxi_dropoff.sort_values(by=['datestop', 'coordinates'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 197,
   "metadata": {},
   "outputs": [],
   "source": [
    "taxi_dropoff_merged = nypd_df.merge(taxi_dropoff, how='left', on=['datestop','coordinates'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 198,
   "metadata": {},
   "outputs": [],
   "source": [
    "taxi_dropoff_merged = taxi_dropoff_merged.fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 199,
   "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>datestop</th>\n",
       "      <th>coordinates</th>\n",
       "      <th>nypd_complaints_COUNT</th>\n",
       "      <th>passenger_count</th>\n",
       "      <th>total_amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2012-07-01</td>\n",
       "      <td>(0, 20)</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2012-07-01</td>\n",
       "      <td>(0, 21)</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2012-07-01</td>\n",
       "      <td>(0, 22)</td>\n",
       "      <td>3</td>\n",
       "      <td>4.0</td>\n",
       "      <td>165.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2012-07-01</td>\n",
       "      <td>(1, 20)</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2012-07-01</td>\n",
       "      <td>(2, 20)</td>\n",
       "      <td>2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>76.60</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>67406</th>\n",
       "      <td>2013-06-30</td>\n",
       "      <td>(21, 17)</td>\n",
       "      <td>5</td>\n",
       "      <td>7.0</td>\n",
       "      <td>169.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67407</th>\n",
       "      <td>2013-06-30</td>\n",
       "      <td>(22, 8)</td>\n",
       "      <td>1</td>\n",
       "      <td>33.0</td>\n",
       "      <td>848.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67408</th>\n",
       "      <td>2013-06-30</td>\n",
       "      <td>(22, 10)</td>\n",
       "      <td>1</td>\n",
       "      <td>32.0</td>\n",
       "      <td>890.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67409</th>\n",
       "      <td>2013-06-30</td>\n",
       "      <td>(22, 12)</td>\n",
       "      <td>1</td>\n",
       "      <td>17.0</td>\n",
       "      <td>462.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67410</th>\n",
       "      <td>2013-06-30</td>\n",
       "      <td>(22, 14)</td>\n",
       "      <td>1</td>\n",
       "      <td>26.0</td>\n",
       "      <td>579.03</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>67411 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         datestop coordinates  nypd_complaints_COUNT  passenger_count  \\\n",
       "0      2012-07-01     (0, 20)                      1              0.0   \n",
       "1      2012-07-01     (0, 21)                      1              0.0   \n",
       "2      2012-07-01     (0, 22)                      3              4.0   \n",
       "3      2012-07-01     (1, 20)                      1              0.0   \n",
       "4      2012-07-01     (2, 20)                      2              1.0   \n",
       "...           ...         ...                    ...              ...   \n",
       "67406  2013-06-30    (21, 17)                      5              7.0   \n",
       "67407  2013-06-30     (22, 8)                      1             33.0   \n",
       "67408  2013-06-30    (22, 10)                      1             32.0   \n",
       "67409  2013-06-30    (22, 12)                      1             17.0   \n",
       "67410  2013-06-30    (22, 14)                      1             26.0   \n",
       "\n",
       "       total_amount  \n",
       "0              0.00  \n",
       "1              0.00  \n",
       "2            165.40  \n",
       "3              0.00  \n",
       "4             76.60  \n",
       "...             ...  \n",
       "67406        169.50  \n",
       "67407        848.16  \n",
       "67408        890.58  \n",
       "67409        462.56  \n",
       "67410        579.03  \n",
       "\n",
       "[67411 rows x 5 columns]"
      ]
     },
     "execution_count": 199,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "taxi_dropoff_merged"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Combine ALL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 200,
   "metadata": {},
   "outputs": [],
   "source": [
    "taxi_merged = taxi_pickup_merged.merge(taxi_dropoff_merged, on=['datestop', 'coordinates', 'nypd_complaints_COUNT'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 201,
   "metadata": {},
   "outputs": [],
   "source": [
    "taxi_merged = taxi_merged.rename(columns={'passenger_count_x':'passenger_count_pickup', 'passenger_count_y':'passenger_count_dropoff', 'total_amount_x':'total_amount_pickup','total_amount_y':'total_amount_dropoff'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 202,
   "metadata": {},
   "outputs": [],
   "source": [
    "saf_311_merged = saf_merge.merge(_311_merged, how='inner', on=['datestop','coordinates', 'nypd_complaints_COUNT'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 203,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_merged = saf_311_merged.merge(taxi_merged, how='inner', on=['datestop','coordinates', 'nypd_complaints_COUNT'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 204,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_merged = all_merged[['datestop','coordinates','SAF_COUNT','311_complaints_COUNT','passenger_count_pickup','total_amount_pickup','passenger_count_dropoff','total_amount_dropoff','nypd_complaints_COUNT']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 205,
   "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>datestop</th>\n",
       "      <th>coordinates</th>\n",
       "      <th>SAF_COUNT</th>\n",
       "      <th>311_complaints_COUNT</th>\n",
       "      <th>passenger_count_pickup</th>\n",
       "      <th>total_amount_pickup</th>\n",
       "      <th>passenger_count_dropoff</th>\n",
       "      <th>total_amount_dropoff</th>\n",
       "      <th>nypd_complaints_COUNT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2012-07-01</td>\n",
       "      <td>(0, 20)</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2012-07-01</td>\n",
       "      <td>(0, 21)</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2012-07-01</td>\n",
       "      <td>(0, 22)</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>4.0</td>\n",
       "      <td>165.40</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2012-07-01</td>\n",
       "      <td>(1, 20)</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2012-07-01</td>\n",
       "      <td>(2, 20)</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>76.60</td>\n",
       "      <td>2</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67406</th>\n",
       "      <td>2013-06-30</td>\n",
       "      <td>(21, 17)</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>74.50</td>\n",
       "      <td>7.0</td>\n",
       "      <td>169.50</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67407</th>\n",
       "      <td>2013-06-30</td>\n",
       "      <td>(22, 8)</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>72.33</td>\n",
       "      <td>33.0</td>\n",
       "      <td>848.16</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67408</th>\n",
       "      <td>2013-06-30</td>\n",
       "      <td>(22, 10)</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>60.00</td>\n",
       "      <td>32.0</td>\n",
       "      <td>890.58</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67409</th>\n",
       "      <td>2013-06-30</td>\n",
       "      <td>(22, 12)</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>17.0</td>\n",
       "      <td>462.56</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67410</th>\n",
       "      <td>2013-06-30</td>\n",
       "      <td>(22, 14)</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>312.93</td>\n",
       "      <td>26.0</td>\n",
       "      <td>579.03</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>67411 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         datestop coordinates  SAF_COUNT  311_complaints_COUNT  \\\n",
       "0      2012-07-01     (0, 20)        0.0                   0.0   \n",
       "1      2012-07-01     (0, 21)        0.0                   1.0   \n",
       "2      2012-07-01     (0, 22)        0.0                   4.0   \n",
       "3      2012-07-01     (1, 20)        0.0                   3.0   \n",
       "4      2012-07-01     (2, 20)        0.0                   3.0   \n",
       "...           ...         ...        ...                   ...   \n",
       "67406  2013-06-30    (21, 17)        0.0                   1.0   \n",
       "67407  2013-06-30     (22, 8)        0.0                   0.0   \n",
       "67408  2013-06-30    (22, 10)        0.0                   0.0   \n",
       "67409  2013-06-30    (22, 12)        2.0                   0.0   \n",
       "67410  2013-06-30    (22, 14)        0.0                   0.0   \n",
       "\n",
       "       passenger_count_pickup  total_amount_pickup  passenger_count_dropoff  \\\n",
       "0                         0.0                 0.00                      0.0   \n",
       "1                         0.0                 0.00                      0.0   \n",
       "2                         0.0                 0.00                      4.0   \n",
       "3                         0.0                 0.00                      0.0   \n",
       "4                         0.0                 0.00                      1.0   \n",
       "...                       ...                  ...                      ...   \n",
       "67406                     2.0                74.50                      7.0   \n",
       "67407                     1.0                72.33                     33.0   \n",
       "67408                     7.0                60.00                     32.0   \n",
       "67409                     0.0                 0.00                     17.0   \n",
       "67410                    16.0               312.93                     26.0   \n",
       "\n",
       "       total_amount_dropoff  nypd_complaints_COUNT  \n",
       "0                      0.00                      1  \n",
       "1                      0.00                      1  \n",
       "2                    165.40                      3  \n",
       "3                      0.00                      1  \n",
       "4                     76.60                      2  \n",
       "...                     ...                    ...  \n",
       "67406                169.50                      5  \n",
       "67407                848.16                      1  \n",
       "67408                890.58                      1  \n",
       "67409                462.56                      1  \n",
       "67410                579.03                      1  \n",
       "\n",
       "[67411 rows x 9 columns]"
      ]
     },
     "execution_count": 205,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_merged"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 207,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_merged.coordinates = all_merged.coordinates.apply(ast.literal_eval)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 217,
   "metadata": {},
   "outputs": [],
   "source": [
    "if os.name == 'posix':\n",
    "    save_location = '/Users/ernestvmo/OneDrive - Universiteit Leiden/Q1/Urban Computing/Project/data/FINAL DATA/'\n",
    "else:\n",
    "    save_location = 'C:/Users/Ernest/OneDrive - Universiteit Leiden/Q1/Urban Computing/Project/data/FINAL DATA/'\n",
    "\n",
    "all_merged.to_csv(save_location + 'dataset.csv')"
   ]
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "aee8b7b246df8f9039afb4144a1f6fd8d2ca17a180786b69acc140d282b71a49"
  },
  "kernelspec": {
   "display_name": "Python 3.9.4 64-bit",
   "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.9.4"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}