build-dataset.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import pickle as pickle\n",
    "import pathlib"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = pd.read_csv('/home/bh_parijat/data/main-dataset.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "\n",
    "def remaining_pts(path = None):\n",
    "\n",
    "    indices, zipcodes,latitudes,longitudes = [],[],[],[]\n",
    "    files = [file for file in path.iterdir()]\n",
    "    \n",
    "    \n",
    "    for file in files:\n",
    "\n",
    "        if file.suffix == \".pkl\":\n",
    "            continue\n",
    "\n",
    "        #print(file.name)\n",
    "        with open(file,'rb') as handle:\n",
    "            result = pickle.load(handle)\n",
    "\n",
    "        #print(file)  \n",
    "        a,b,c,d = zip(*result)\n",
    "\n",
    "        indices.extend(a)\n",
    "        zipcodes.extend(b)\n",
    "        latitudes.extend(c)\n",
    "        longitudes.extend(d)\n",
    "        \n",
    "        \n",
    "    print(len(indices),len(zipcodes),len(longitudes),len(latitudes))\n",
    "    \n",
    "    return pd.DataFrame(data={'org_index':indices,'zipcodes':zipcodes,'Latitude':latitudes,'Longitude':longitudes})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df = pd.DataFrame(data={\"org_index\":indices,\"zipcode\":zipcodes})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df = df.sort_values(by=['org_index']).reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df.to_csv('~/data/zipcode_index.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "#pd.read_csv('/home/bh_parijat/data/zipcode_index.csv').head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "not_present = data[data['zipcode'].isnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(464464, 21)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "not_present.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "not_present = not_present.drop(columns=['org_index','zipcode'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(4798339, 21)"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "#z_dataset.to_csv('~/data/main-dataset.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "#z_dataset.shape[0] -  df.shape[0]#"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_remaining_pts = pd.read_csv('/home/bh_parijat/NYPD-crime-analysis/reverse-data-map/not_present/remaining_points.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "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>org_indices</th>\n",
       "      <th>zipcode</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>180000</td>\n",
       "      <td>11220</td>\n",
       "      <td>40.644274</td>\n",
       "      <td>-74.011194</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>180001</td>\n",
       "      <td>10013</td>\n",
       "      <td>40.715957</td>\n",
       "      <td>-73.997354</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>180002</td>\n",
       "      <td>11229</td>\n",
       "      <td>40.595930</td>\n",
       "      <td>-73.954908</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>180003</td>\n",
       "      <td>10002</td>\n",
       "      <td>40.715833</td>\n",
       "      <td>-73.986297</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>180004</td>\n",
       "      <td>11428</td>\n",
       "      <td>40.726293</td>\n",
       "      <td>-73.734761</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   org_indices  zipcode   Latitude  Longitude\n",
       "0       180000    11220  40.644274 -74.011194\n",
       "1       180001    10013  40.715957 -73.997354\n",
       "2       180002    11229  40.595930 -73.954908\n",
       "3       180003    10002  40.715833 -73.986297\n",
       "4       180004    11428  40.726293 -73.734761"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_remaining_pts.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_remaining_pts = df_remaining_pts.rename(columns={'org_indices':'org_index'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(457984, 4)"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_remaining_pts.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['org_index', 'zipcode', 'Latitude', 'Longitude'], dtype='object')"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_remaining_pts.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "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>org_index</th>\n",
       "      <th>zipcode</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>180000</td>\n",
       "      <td>11220</td>\n",
       "      <td>40.644274</td>\n",
       "      <td>-74.011194</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>180001</td>\n",
       "      <td>10013</td>\n",
       "      <td>40.715957</td>\n",
       "      <td>-73.997354</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>180002</td>\n",
       "      <td>11229</td>\n",
       "      <td>40.595930</td>\n",
       "      <td>-73.954908</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>180003</td>\n",
       "      <td>10002</td>\n",
       "      <td>40.715833</td>\n",
       "      <td>-73.986297</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>180004</td>\n",
       "      <td>11428</td>\n",
       "      <td>40.726293</td>\n",
       "      <td>-73.734761</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   org_index  zipcode   Latitude  Longitude\n",
       "0     180000    11220  40.644274 -74.011194\n",
       "1     180001    10013  40.715957 -73.997354\n",
       "2     180002    11229  40.595930 -73.954908\n",
       "3     180003    10002  40.715833 -73.986297\n",
       "4     180004    11428  40.726293 -73.734761"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_remaining_pts.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Int64Index([ 180000,  180001,  180002,  180003,  180004,  180005,  180006,\n",
       "             180007,  180008,  180009,\n",
       "            ...\n",
       "            4798329, 4798330, 4798331, 4798332, 4798333, 4798334, 4798335,\n",
       "            4798336, 4798337, 4798338],\n",
       "           dtype='int64', length=464464)"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "not_present.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "remaining_merged = pd.merge(not_present, df_remaining_pts,left_on=['index'],right_on=['org_index'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "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>ARREST_KEY</th>\n",
       "      <th>ARREST_DATE</th>\n",
       "      <th>PD_CD</th>\n",
       "      <th>PD_DESC</th>\n",
       "      <th>KY_CD</th>\n",
       "      <th>OFNS_DESC</th>\n",
       "      <th>LAW_CODE</th>\n",
       "      <th>LAW_CAT_CD</th>\n",
       "      <th>ARREST_BORO</th>\n",
       "      <th>...</th>\n",
       "      <th>PERP_SEX</th>\n",
       "      <th>PERP_RACE</th>\n",
       "      <th>X_COORD_CD</th>\n",
       "      <th>Y_COORD_CD</th>\n",
       "      <th>Latitude_x</th>\n",
       "      <th>Longitude_x</th>\n",
       "      <th>org_index</th>\n",
       "      <th>zipcode</th>\n",
       "      <th>Latitude_y</th>\n",
       "      <th>Longitude_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>180000</td>\n",
       "      <td>164571494</td>\n",
       "      <td>05/08/2017</td>\n",
       "      <td>339.0</td>\n",
       "      <td>LARCENY,PETIT FROM OPEN AREAS,UNCLASSIFIED</td>\n",
       "      <td>341.0</td>\n",
       "      <td>PETIT LARCENY</td>\n",
       "      <td>PL 1552500</td>\n",
       "      <td>M</td>\n",
       "      <td>K</td>\n",
       "      <td>...</td>\n",
       "      <td>M</td>\n",
       "      <td>WHITE HISPANIC</td>\n",
       "      <td>981144.0</td>\n",
       "      <td>174000.0</td>\n",
       "      <td>40.644274</td>\n",
       "      <td>-74.011194</td>\n",
       "      <td>180000</td>\n",
       "      <td>11220</td>\n",
       "      <td>40.644274</td>\n",
       "      <td>-74.011194</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>180001</td>\n",
       "      <td>164558531</td>\n",
       "      <td>05/08/2017</td>\n",
       "      <td>439.0</td>\n",
       "      <td>LARCENY,GRAND FROM OPEN AREAS,UNCLASSIFIED</td>\n",
       "      <td>109.0</td>\n",
       "      <td>GRAND LARCENY</td>\n",
       "      <td>PL 1553004</td>\n",
       "      <td>F</td>\n",
       "      <td>M</td>\n",
       "      <td>...</td>\n",
       "      <td>M</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>984984.0</td>\n",
       "      <td>200116.0</td>\n",
       "      <td>40.715957</td>\n",
       "      <td>-73.997354</td>\n",
       "      <td>180001</td>\n",
       "      <td>10013</td>\n",
       "      <td>40.715957</td>\n",
       "      <td>-73.997354</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>180002</td>\n",
       "      <td>164571491</td>\n",
       "      <td>05/08/2017</td>\n",
       "      <td>779.0</td>\n",
       "      <td>PUBLIC ADMINISTRATION,UNCLASSIFIED FELONY</td>\n",
       "      <td>126.0</td>\n",
       "      <td>MISCELLANEOUS PENAL LAW</td>\n",
       "      <td>PL 2155100</td>\n",
       "      <td>F</td>\n",
       "      <td>K</td>\n",
       "      <td>...</td>\n",
       "      <td>M</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>996773.0</td>\n",
       "      <td>156390.0</td>\n",
       "      <td>40.595930</td>\n",
       "      <td>-73.954908</td>\n",
       "      <td>180002</td>\n",
       "      <td>11229</td>\n",
       "      <td>40.595930</td>\n",
       "      <td>-73.954908</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>180003</td>\n",
       "      <td>164570089</td>\n",
       "      <td>05/08/2017</td>\n",
       "      <td>494.0</td>\n",
       "      <td>STOLEN PROPERTY 2,1,POSSESSION,UNCLASSIFIED</td>\n",
       "      <td>111.0</td>\n",
       "      <td>POSSESSION OF STOLEN PROPERTY</td>\n",
       "      <td>PL 1654501</td>\n",
       "      <td>F</td>\n",
       "      <td>M</td>\n",
       "      <td>...</td>\n",
       "      <td>M</td>\n",
       "      <td>WHITE HISPANIC</td>\n",
       "      <td>988049.0</td>\n",
       "      <td>200071.0</td>\n",
       "      <td>40.715833</td>\n",
       "      <td>-73.986297</td>\n",
       "      <td>180003</td>\n",
       "      <td>10002</td>\n",
       "      <td>40.715833</td>\n",
       "      <td>-73.986297</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>180004</td>\n",
       "      <td>164558610</td>\n",
       "      <td>05/08/2017</td>\n",
       "      <td>779.0</td>\n",
       "      <td>PUBLIC ADMINISTRATION,UNCLASSIFIED FELONY</td>\n",
       "      <td>126.0</td>\n",
       "      <td>MISCELLANEOUS PENAL LAW</td>\n",
       "      <td>PL 215510B</td>\n",
       "      <td>F</td>\n",
       "      <td>Q</td>\n",
       "      <td>...</td>\n",
       "      <td>M</td>\n",
       "      <td>ASIAN / PACIFIC ISLANDER</td>\n",
       "      <td>1057767.0</td>\n",
       "      <td>203993.0</td>\n",
       "      <td>40.726293</td>\n",
       "      <td>-73.734761</td>\n",
       "      <td>180004</td>\n",
       "      <td>11428</td>\n",
       "      <td>40.726293</td>\n",
       "      <td>-73.734761</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    index  ARREST_KEY ARREST_DATE  PD_CD  \\\n",
       "0  180000   164571494  05/08/2017  339.0   \n",
       "1  180001   164558531  05/08/2017  439.0   \n",
       "2  180002   164571491  05/08/2017  779.0   \n",
       "3  180003   164570089  05/08/2017  494.0   \n",
       "4  180004   164558610  05/08/2017  779.0   \n",
       "\n",
       "                                       PD_DESC  KY_CD  \\\n",
       "0   LARCENY,PETIT FROM OPEN AREAS,UNCLASSIFIED  341.0   \n",
       "1   LARCENY,GRAND FROM OPEN AREAS,UNCLASSIFIED  109.0   \n",
       "2    PUBLIC ADMINISTRATION,UNCLASSIFIED FELONY  126.0   \n",
       "3  STOLEN PROPERTY 2,1,POSSESSION,UNCLASSIFIED  111.0   \n",
       "4    PUBLIC ADMINISTRATION,UNCLASSIFIED FELONY  126.0   \n",
       "\n",
       "                       OFNS_DESC    LAW_CODE LAW_CAT_CD ARREST_BORO  ...  \\\n",
       "0                  PETIT LARCENY  PL 1552500          M           K  ...   \n",
       "1                  GRAND LARCENY  PL 1553004          F           M  ...   \n",
       "2        MISCELLANEOUS PENAL LAW  PL 2155100          F           K  ...   \n",
       "3  POSSESSION OF STOLEN PROPERTY  PL 1654501          F           M  ...   \n",
       "4        MISCELLANEOUS PENAL LAW  PL 215510B          F           Q  ...   \n",
       "\n",
       "   PERP_SEX                 PERP_RACE X_COORD_CD Y_COORD_CD Latitude_x  \\\n",
       "0         M            WHITE HISPANIC   981144.0   174000.0  40.644274   \n",
       "1         M                     WHITE   984984.0   200116.0  40.715957   \n",
       "2         M                     WHITE   996773.0   156390.0  40.595930   \n",
       "3         M            WHITE HISPANIC   988049.0   200071.0  40.715833   \n",
       "4         M  ASIAN / PACIFIC ISLANDER  1057767.0   203993.0  40.726293   \n",
       "\n",
       "   Longitude_x  org_index  zipcode  Latitude_y  Longitude_y  \n",
       "0   -74.011194     180000    11220   40.644274   -74.011194  \n",
       "1   -73.997354     180001    10013   40.715957   -73.997354  \n",
       "2   -73.954908     180002    11229   40.595930   -73.954908  \n",
       "3   -73.986297     180003    10002   40.715833   -73.986297  \n",
       "4   -73.734761     180004    11428   40.726293   -73.734761  \n",
       "\n",
       "[5 rows x 23 columns]"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "remaining_merged.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(457984, 23)"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "remaining_merged.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['index', 'ARREST_KEY', 'ARREST_DATE', 'PD_CD', 'PD_DESC', 'KY_CD',\n",
       "       'OFNS_DESC', 'LAW_CODE', 'LAW_CAT_CD', 'ARREST_BORO', 'ARREST_PRECINCT',\n",
       "       'JURISDICTION_CODE', 'AGE_GROUP', 'PERP_SEX', 'PERP_RACE', 'X_COORD_CD',\n",
       "       'Y_COORD_CD', 'Latitude_x', 'Longitude_x', 'org_index', 'zipcode',\n",
       "       'Latitude_y', 'Longitude_y'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "remaining_merged.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "#column = {'org_index_x':'org_index','zipcode_x':'zipcode','Latitude_x':'Latitude','Longitude_x':'Longitude'}\n",
    "columns = {'Latitude_x':'Latitude','Longitude_x':'Longitude'}\n",
    "remaining_merged = remaining_merged.rename(columns=column)\n",
    "\n",
    "remaining_merged = remaining_merged[data.columns]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(457984, 21)"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "remaining_merged.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "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>ARREST_KEY</th>\n",
       "      <th>ARREST_DATE</th>\n",
       "      <th>PD_CD</th>\n",
       "      <th>PD_DESC</th>\n",
       "      <th>KY_CD</th>\n",
       "      <th>OFNS_DESC</th>\n",
       "      <th>LAW_CODE</th>\n",
       "      <th>LAW_CAT_CD</th>\n",
       "      <th>ARREST_BORO</th>\n",
       "      <th>...</th>\n",
       "      <th>JURISDICTION_CODE</th>\n",
       "      <th>AGE_GROUP</th>\n",
       "      <th>PERP_SEX</th>\n",
       "      <th>PERP_RACE</th>\n",
       "      <th>X_COORD_CD</th>\n",
       "      <th>Y_COORD_CD</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>org_index</th>\n",
       "      <th>zipcode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>180000</td>\n",
       "      <td>164571494</td>\n",
       "      <td>05/08/2017</td>\n",
       "      <td>339.0</td>\n",
       "      <td>LARCENY,PETIT FROM OPEN AREAS,UNCLASSIFIED</td>\n",
       "      <td>341.0</td>\n",
       "      <td>PETIT LARCENY</td>\n",
       "      <td>PL 1552500</td>\n",
       "      <td>M</td>\n",
       "      <td>K</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>45-64</td>\n",
       "      <td>M</td>\n",
       "      <td>WHITE HISPANIC</td>\n",
       "      <td>981144.0</td>\n",
       "      <td>174000.0</td>\n",
       "      <td>40.644274</td>\n",
       "      <td>-74.011194</td>\n",
       "      <td>180000</td>\n",
       "      <td>11220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>180001</td>\n",
       "      <td>164558531</td>\n",
       "      <td>05/08/2017</td>\n",
       "      <td>439.0</td>\n",
       "      <td>LARCENY,GRAND FROM OPEN AREAS,UNCLASSIFIED</td>\n",
       "      <td>109.0</td>\n",
       "      <td>GRAND LARCENY</td>\n",
       "      <td>PL 1553004</td>\n",
       "      <td>F</td>\n",
       "      <td>M</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>45-64</td>\n",
       "      <td>M</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>984984.0</td>\n",
       "      <td>200116.0</td>\n",
       "      <td>40.715957</td>\n",
       "      <td>-73.997354</td>\n",
       "      <td>180001</td>\n",
       "      <td>10013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>180002</td>\n",
       "      <td>164571491</td>\n",
       "      <td>05/08/2017</td>\n",
       "      <td>779.0</td>\n",
       "      <td>PUBLIC ADMINISTRATION,UNCLASSIFIED FELONY</td>\n",
       "      <td>126.0</td>\n",
       "      <td>MISCELLANEOUS PENAL LAW</td>\n",
       "      <td>PL 2155100</td>\n",
       "      <td>F</td>\n",
       "      <td>K</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>25-44</td>\n",
       "      <td>M</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>996773.0</td>\n",
       "      <td>156390.0</td>\n",
       "      <td>40.595930</td>\n",
       "      <td>-73.954908</td>\n",
       "      <td>180002</td>\n",
       "      <td>11229</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>180003</td>\n",
       "      <td>164570089</td>\n",
       "      <td>05/08/2017</td>\n",
       "      <td>494.0</td>\n",
       "      <td>STOLEN PROPERTY 2,1,POSSESSION,UNCLASSIFIED</td>\n",
       "      <td>111.0</td>\n",
       "      <td>POSSESSION OF STOLEN PROPERTY</td>\n",
       "      <td>PL 1654501</td>\n",
       "      <td>F</td>\n",
       "      <td>M</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>45-64</td>\n",
       "      <td>M</td>\n",
       "      <td>WHITE HISPANIC</td>\n",
       "      <td>988049.0</td>\n",
       "      <td>200071.0</td>\n",
       "      <td>40.715833</td>\n",
       "      <td>-73.986297</td>\n",
       "      <td>180003</td>\n",
       "      <td>10002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>180004</td>\n",
       "      <td>164558610</td>\n",
       "      <td>05/08/2017</td>\n",
       "      <td>779.0</td>\n",
       "      <td>PUBLIC ADMINISTRATION,UNCLASSIFIED FELONY</td>\n",
       "      <td>126.0</td>\n",
       "      <td>MISCELLANEOUS PENAL LAW</td>\n",
       "      <td>PL 215510B</td>\n",
       "      <td>F</td>\n",
       "      <td>Q</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>25-44</td>\n",
       "      <td>M</td>\n",
       "      <td>ASIAN / PACIFIC ISLANDER</td>\n",
       "      <td>1057767.0</td>\n",
       "      <td>203993.0</td>\n",
       "      <td>40.726293</td>\n",
       "      <td>-73.734761</td>\n",
       "      <td>180004</td>\n",
       "      <td>11428</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    index  ARREST_KEY ARREST_DATE  PD_CD  \\\n",
       "0  180000   164571494  05/08/2017  339.0   \n",
       "1  180001   164558531  05/08/2017  439.0   \n",
       "2  180002   164571491  05/08/2017  779.0   \n",
       "3  180003   164570089  05/08/2017  494.0   \n",
       "4  180004   164558610  05/08/2017  779.0   \n",
       "\n",
       "                                       PD_DESC  KY_CD  \\\n",
       "0   LARCENY,PETIT FROM OPEN AREAS,UNCLASSIFIED  341.0   \n",
       "1   LARCENY,GRAND FROM OPEN AREAS,UNCLASSIFIED  109.0   \n",
       "2    PUBLIC ADMINISTRATION,UNCLASSIFIED FELONY  126.0   \n",
       "3  STOLEN PROPERTY 2,1,POSSESSION,UNCLASSIFIED  111.0   \n",
       "4    PUBLIC ADMINISTRATION,UNCLASSIFIED FELONY  126.0   \n",
       "\n",
       "                       OFNS_DESC    LAW_CODE LAW_CAT_CD ARREST_BORO  ...  \\\n",
       "0                  PETIT LARCENY  PL 1552500          M           K  ...   \n",
       "1                  GRAND LARCENY  PL 1553004          F           M  ...   \n",
       "2        MISCELLANEOUS PENAL LAW  PL 2155100          F           K  ...   \n",
       "3  POSSESSION OF STOLEN PROPERTY  PL 1654501          F           M  ...   \n",
       "4        MISCELLANEOUS PENAL LAW  PL 215510B          F           Q  ...   \n",
       "\n",
       "   JURISDICTION_CODE  AGE_GROUP PERP_SEX                 PERP_RACE X_COORD_CD  \\\n",
       "0                0.0      45-64        M            WHITE HISPANIC   981144.0   \n",
       "1                0.0      45-64        M                     WHITE   984984.0   \n",
       "2                0.0      25-44        M                     WHITE   996773.0   \n",
       "3                0.0      45-64        M            WHITE HISPANIC   988049.0   \n",
       "4                0.0      25-44        M  ASIAN / PACIFIC ISLANDER  1057767.0   \n",
       "\n",
       "   Y_COORD_CD   Latitude  Longitude  org_index  zipcode  \n",
       "0    174000.0  40.644274 -74.011194     180000    11220  \n",
       "1    200116.0  40.715957 -73.997354     180001    10013  \n",
       "2    156390.0  40.595930 -73.954908     180002    11229  \n",
       "3    200071.0  40.715833 -73.986297     180003    10002  \n",
       "4    203993.0  40.726293 -73.734761     180004    11428  \n",
       "\n",
       "[5 rows x 21 columns]"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "remaining_merged.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(0, 21)"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "remaining_merged[remaining_merged['zipcode'].isnull()].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "present = data[~data['zipcode'].isnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(4333875, 21)"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "present.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6480"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "not_present.shape[0]-remaining_merged.shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "#d1.shape[0]+remaining_merged.shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df = pd.concat([present,remaining_merged])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(4791859, 21)"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6480"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.shape[0]-final_df.shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [],
   "source": [
    "#final_df[final_df['zipcode'].isnull()].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df.to_csv('~/data/main_dataset_update1.2.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "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>ARREST_KEY</th>\n",
       "      <th>ARREST_DATE</th>\n",
       "      <th>PD_CD</th>\n",
       "      <th>PD_DESC</th>\n",
       "      <th>KY_CD</th>\n",
       "      <th>OFNS_DESC</th>\n",
       "      <th>LAW_CODE</th>\n",
       "      <th>LAW_CAT_CD</th>\n",
       "      <th>ARREST_BORO</th>\n",
       "      <th>...</th>\n",
       "      <th>JURISDICTION_CODE</th>\n",
       "      <th>AGE_GROUP</th>\n",
       "      <th>PERP_SEX</th>\n",
       "      <th>PERP_RACE</th>\n",
       "      <th>X_COORD_CD</th>\n",
       "      <th>Y_COORD_CD</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>org_index</th>\n",
       "      <th>zipcode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>173130602</td>\n",
       "      <td>12/31/2017</td>\n",
       "      <td>566.0</td>\n",
       "      <td>MARIJUANA, POSSESSION</td>\n",
       "      <td>678.0</td>\n",
       "      <td>MISCELLANEOUS PENAL LAW</td>\n",
       "      <td>PL 2210500</td>\n",
       "      <td>V</td>\n",
       "      <td>Q</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>25-44</td>\n",
       "      <td>M</td>\n",
       "      <td>BLACK</td>\n",
       "      <td>1063056.0</td>\n",
       "      <td>207463.0</td>\n",
       "      <td>40.735772</td>\n",
       "      <td>-73.715638</td>\n",
       "      <td>0.0</td>\n",
       "      <td>11426.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>173114463</td>\n",
       "      <td>12/31/2017</td>\n",
       "      <td>478.0</td>\n",
       "      <td>THEFT OF SERVICES, UNCLASSIFIED</td>\n",
       "      <td>343.0</td>\n",
       "      <td>OTHER OFFENSES RELATED TO THEFT</td>\n",
       "      <td>PL 1651503</td>\n",
       "      <td>M</td>\n",
       "      <td>Q</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>25-44</td>\n",
       "      <td>M</td>\n",
       "      <td>ASIAN / PACIFIC ISLANDER</td>\n",
       "      <td>1009113.0</td>\n",
       "      <td>219613.0</td>\n",
       "      <td>40.769437</td>\n",
       "      <td>-73.910241</td>\n",
       "      <td>1.0</td>\n",
       "      <td>11105.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>173113513</td>\n",
       "      <td>12/31/2017</td>\n",
       "      <td>849.0</td>\n",
       "      <td>NY STATE LAWS,UNCLASSIFIED VIOLATION</td>\n",
       "      <td>677.0</td>\n",
       "      <td>OTHER STATE LAWS</td>\n",
       "      <td>LOC000000V</td>\n",
       "      <td>V</td>\n",
       "      <td>K</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>18-24</td>\n",
       "      <td>M</td>\n",
       "      <td>BLACK</td>\n",
       "      <td>1010719.0</td>\n",
       "      <td>186857.0</td>\n",
       "      <td>40.679525</td>\n",
       "      <td>-73.904572</td>\n",
       "      <td>2.0</td>\n",
       "      <td>11207.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>173113423</td>\n",
       "      <td>12/31/2017</td>\n",
       "      <td>101.0</td>\n",
       "      <td>ASSAULT 3</td>\n",
       "      <td>344.0</td>\n",
       "      <td>ASSAULT 3 &amp; RELATED OFFENSES</td>\n",
       "      <td>PL 1200001</td>\n",
       "      <td>M</td>\n",
       "      <td>M</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>25-44</td>\n",
       "      <td>M</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>987831.0</td>\n",
       "      <td>217446.0</td>\n",
       "      <td>40.763523</td>\n",
       "      <td>-73.987074</td>\n",
       "      <td>3.0</td>\n",
       "      <td>10019.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>173113421</td>\n",
       "      <td>12/31/2017</td>\n",
       "      <td>101.0</td>\n",
       "      <td>ASSAULT 3</td>\n",
       "      <td>344.0</td>\n",
       "      <td>ASSAULT 3 &amp; RELATED OFFENSES</td>\n",
       "      <td>PL 1200001</td>\n",
       "      <td>M</td>\n",
       "      <td>M</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>45-64</td>\n",
       "      <td>M</td>\n",
       "      <td>BLACK</td>\n",
       "      <td>987073.0</td>\n",
       "      <td>216078.0</td>\n",
       "      <td>40.759768</td>\n",
       "      <td>-73.989811</td>\n",
       "      <td>4.0</td>\n",
       "      <td>10036.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   index  ARREST_KEY ARREST_DATE  PD_CD                               PD_DESC  \\\n",
       "0      0   173130602  12/31/2017  566.0                 MARIJUANA, POSSESSION   \n",
       "1      1   173114463  12/31/2017  478.0       THEFT OF SERVICES, UNCLASSIFIED   \n",
       "2      2   173113513  12/31/2017  849.0  NY STATE LAWS,UNCLASSIFIED VIOLATION   \n",
       "3      3   173113423  12/31/2017  101.0                             ASSAULT 3   \n",
       "4      4   173113421  12/31/2017  101.0                             ASSAULT 3   \n",
       "\n",
       "   KY_CD                        OFNS_DESC    LAW_CODE LAW_CAT_CD ARREST_BORO  \\\n",
       "0  678.0          MISCELLANEOUS PENAL LAW  PL 2210500          V           Q   \n",
       "1  343.0  OTHER OFFENSES RELATED TO THEFT  PL 1651503          M           Q   \n",
       "2  677.0                 OTHER STATE LAWS  LOC000000V          V           K   \n",
       "3  344.0     ASSAULT 3 & RELATED OFFENSES  PL 1200001          M           M   \n",
       "4  344.0     ASSAULT 3 & RELATED OFFENSES  PL 1200001          M           M   \n",
       "\n",
       "   ...  JURISDICTION_CODE  AGE_GROUP PERP_SEX                 PERP_RACE  \\\n",
       "0  ...                0.0      25-44        M                     BLACK   \n",
       "1  ...                0.0      25-44        M  ASIAN / PACIFIC ISLANDER   \n",
       "2  ...                1.0      18-24        M                     BLACK   \n",
       "3  ...                0.0      25-44        M                     WHITE   \n",
       "4  ...                0.0      45-64        M                     BLACK   \n",
       "\n",
       "  X_COORD_CD  Y_COORD_CD   Latitude  Longitude  org_index  zipcode  \n",
       "0  1063056.0    207463.0  40.735772 -73.715638        0.0  11426.0  \n",
       "1  1009113.0    219613.0  40.769437 -73.910241        1.0  11105.0  \n",
       "2  1010719.0    186857.0  40.679525 -73.904572        2.0  11207.0  \n",
       "3   987831.0    217446.0  40.763523 -73.987074        3.0  10019.0  \n",
       "4   987073.0    216078.0  40.759768 -73.989811        4.0  10036.0  \n",
       "\n",
       "[5 rows x 21 columns]"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [],
   "source": [
    "path = pathlib.Path().absolute()/'reverse-data-map'/'not_present'\n",
    "\n",
    "dataframe = remaining_pts(path=path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#main_dataset.shape[0]-(d1.shape[0]+dataframe.shape[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "#d1.shape[0]-dataframe.shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "#dataframe.shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(464464, 21)"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[data['zipcode'].isnull()].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "arrest_keys = list(final_df['ARREST_KEY'])\n",
    "Left2 = data[~data['ARREST_KEY'].isin(arrest_keys)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(6480, 21)"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Left2.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "Left2.to_csv('~/data/remaining6480.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}