arcgis_project_nyc_traffic_part2_data_cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "## ok\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import os\n",
    "import seaborn as sns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "## ok\n",
    "def make_OHE(orig_df,df_column_name):\n",
    "    ohe_df = pd.get_dummies(orig_df[df_column_name].astype(str))\n",
    "    return ohe_df\n",
    "\n",
    "def standardize_OHE(orig_df, df_column_names):\n",
    "    row_wise_sum = (orig_df.loc[:,df_column_names]).sum(axis=1)\n",
    "    orig_df.loc[:,df_column_names] = (orig_df.loc[:,df_column_names]).div(row_wise_sum, axis=0)\n",
    "    return orig_df.loc[:,df_column_names]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "c:\\users\\benson\\appdata\\local\\programs\\python\\python37\\lib\\site-packages\\IPython\\core\\interactiveshell.py:3058: DtypeWarning: Columns (28,29,32,33,34,35,36,38,40,42,44,46,48,50,52,54,56,58,60,62,64,65,66,67,69,70,93,98,99,101,108,110,112) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n",
      "c:\\users\\benson\\appdata\\local\\programs\\python\\python37\\lib\\site-packages\\IPython\\core\\interactiveshell.py:3058: DtypeWarning: Columns (8,9,10,17,18,47,57,59,60,76,82,123,124,127,128,129,130,131,133,135,137,139,141,143,145,147,149,151,153,155,157,159,160,161,162,164,165,188,193,194,196,203,205,207) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    }
   ],
   "source": [
    "## ok\n",
    "FOLDER_PATH = os.getcwd()\n",
    "FILE_NAME = \"\\\\gdb_join.txt\"\n",
    "FILE_PATH = FOLDER_PATH + \"\\\\\" + FILE_NAME\n",
    "gdb_join_orig = pd.read_csv(FILE_PATH)\n",
    "\n",
    "FILE_NAME = \"\\\\StreetSegment_LandUse_Subsets.txt\"\n",
    "FILE_PATH = FOLDER_PATH + \"\\\\\" + FILE_NAME\n",
    "StreetSegment_LandUse_Subsets_orig = pd.read_csv(FILE_PATH)\n",
    "\n",
    "FILE_NAME = \"\\\\Subway_Distances.txt\"\n",
    "FILE_PATH = FOLDER_PATH + \"\\\\\" + FILE_NAME\n",
    "Subway_Distances_orig = pd.read_csv(FILE_PATH)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "## ok\n",
    "gdb_join = gdb_join_orig.copy()\n",
    "StreetSegment_LandUse_Subsets = StreetSegment_LandUse_Subsets_orig.copy()\n",
    "Subway_Distances = Subway_Distances_orig.copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "By now, there should be 2 new tables created after the ArcGIS part:\n",
    "\n",
    "* **gdb_join** is a table for the street segments, which each street segment having many (usually 8) rows describing its traffic volume at a given 3-hour subdivision of the day\n",
    "* **StreetSegment_LandUse_Subsets** is a table for the street segments, which each street segment having a variable number of rows that each represent a building or lot within the 500-foot radius of the street segment. We will decide to collapse StreetSegment_LandUse_Subsets so that each street segment has 1 row, essentially aggregating all the info about the street segment's nearby surroundings into 1 compact row."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Both tables have more than 100 columns, so there's a lot of information we don't really need. This next part is about the data columns and selection of which columns are important, and the explained thought process behind their selection."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1463\n",
      "1463\n"
     ]
    }
   ],
   "source": [
    "## ok\n",
    "print(len(pd.unique(gdb_join[\"Segment_ID\"])))\n",
    "print(len(pd.unique(StreetSegment_LandUse_Subsets[\"lion_Segment_ID\"])))\n",
    "# In the previous nyc_traffic_3HrInterval table, we had 1586 unique street segments. But now, there are only 1463.\n",
    "# 123 street segments were removed, but which ones?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1739\n",
      "1739\n"
     ]
    }
   ],
   "source": [
    "## ok\n",
    "\n",
    "print(len(pd.unique(StreetSegment_LandUse_Subsets[\"ORIG_FID\"])))\n",
    "print(len(pd.unique(Subway_Distances[\"IN_FID\"])))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1.0"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "temp = StreetSegment_LandUse_Subsets[[\"lion_Segment_ID\",\"ORIG_FID\"]]\n",
    "temp = temp.drop_duplicates()\n",
    "(temp.groupby([\"ORIG_FID\"])[\"lion_Segment_ID\"].count() == 1).mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data Cleaning StreetSegment_LandUse_Subsets\n",
    "We'll just data clean the table for StreetSegment_LandUse_Subsets. It has a lot of unneeded columns."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Column Transformation Outline\n",
    "* ['ORIG_FID'] => ['ORIG_FID']\n",
    "* F(['lion_Segment_ID']) => ['Segment_ID']\n",
    "* F(['lion_StreetWidt']) => ['StreetWidth']\n",
    "* ['NumFloors'] => ['NumFloors']\n",
    "* F(['BoroCode']) => ['BoroCode1', 'BoroCode2', 'BoroCode3', 'BoroCode4', 'BoroCode5']\n",
    "* F([LandUse]) => [LandUse_t* for * in range(?)]\n",
    "* F([NEAR_DIST]) => ['SubwayProximity'] -Note that this will come from another table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "## ok\n",
    "\n",
    "SELECTED_SL_COLUMNS_NUMERICAL = []\n",
    "SELECTED_SL_COLUMNS_CATEGORICAL = []\n",
    "SELECTED_SL_COLUMNS_OTHERS = []\n",
    "\n",
    "SELECTED_SL_COLUMNS_NUMERICAL += ['lion_StreetWidt']\n",
    "SELECTED_SL_COLUMNS_NUMERICAL += ['NumFloors']\n",
    "SELECTED_SL_COLUMNS_NUMERICAL += ['UnitsRes', 'UnitsTotal']\n",
    "\n",
    "\n",
    "SELECTED_SL_COLUMNS_CATEGORICAL += ['BoroCode']\n",
    "SELECTED_SL_COLUMNS_CATEGORICAL += ['LandUse']\n",
    "\n",
    "\n",
    "SELECTED_SL_COLUMNS_OTHERS += ['ORIG_FID']\n",
    "SELECTED_SL_COLUMNS_OTHERS += ['lion_Segment_ID']\n",
    "\n",
    "SELECTED_SL_COLUMNS = (SELECTED_SL_COLUMNS_NUMERICAL + \n",
    "                       SELECTED_SL_COLUMNS_CATEGORICAL +\n",
    "                       SELECTED_SL_COLUMNS_OTHERS)\n",
    "\n",
    "StreetSegment_LandUse_Subsets = StreetSegment_LandUse_Subsets[SELECTED_SL_COLUMNS]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Finding Nan's and Imputation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "lion_StreetWidt    False\n",
       "NumFloors          False\n",
       "UnitsRes           False\n",
       "UnitsTotal         False\n",
       "BoroCode           False\n",
       "LandUse             True\n",
       "ORIG_FID           False\n",
       "lion_Segment_ID    False\n",
       "dtype: bool"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## ok\n",
    "\n",
    "StreetSegment_LandUse_Subsets.isnull().any().any()\n",
    "(StreetSegment_LandUse_Subsets == \"\").any().any()\n",
    "(StreetSegment_LandUse_Subsets == \" \").any()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "lion_StreetWidt    0.000000\n",
       "NumFloors          0.000000\n",
       "UnitsRes           0.000000\n",
       "UnitsTotal         0.000000\n",
       "BoroCode           0.000000\n",
       "LandUse            0.005384\n",
       "ORIG_FID           0.000000\n",
       "lion_Segment_ID    0.000000\n",
       "dtype: float64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## ok\n",
    "\n",
    "StreetSegment_LandUse_Subsets = StreetSegment_LandUse_Subsets.replace(\" \", np.nan)\n",
    "StreetSegment_LandUse_Subsets = StreetSegment_LandUse_Subsets.replace(\"\", np.nan)\n",
    "\n",
    "StreetSegment_LandUse_Subsets.isnull().mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "lion_StreetWidt    0.0\n",
       "NumFloors          0.0\n",
       "UnitsRes           0.0\n",
       "UnitsTotal         0.0\n",
       "BoroCode           0.0\n",
       "LandUse            0.0\n",
       "ORIG_FID           0.0\n",
       "lion_Segment_ID    0.0\n",
       "dtype: float64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## ok\n",
    "\n",
    "# Too small to impute, we can discard away. Or IS it?\n",
    "\n",
    "#StreetSegment_LandUse_Subsets=StreetSegment_LandUse_Subsets.dropna(subset=[\"LandUse\"])\n",
    "StreetSegment_LandUse_Subsets=StreetSegment_LandUse_Subsets.dropna()\n",
    "# StreetSegment_LandUse_Subsets=StreetSegment_LandUse_Subsets.fillna(1)\n",
    "\n",
    "StreetSegment_LandUse_Subsets.isnull().mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Joining with Subway_Distances"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "#assert (len(pd.unique(StreetSegment_LandUse_Subsets[\"ORIG_FID\"]))) == (len(pd.unique(Subway_Distances[\"IN_FID\"])))\n",
    "## ok\n",
    "Subway_Distances[\"SubwayProximity\"] = 1/Subway_Distances[\"NEAR_DIST\"]\n",
    "\n",
    "StreetSegment_Subway_Distances = Subway_Distances.groupby([\"IN_FID\"], as_index=False)[\"SubwayProximity\"].sum()\n",
    "\n",
    "StreetSegment_Subway_Distances = StreetSegment_Subway_Distances.rename(columns={\"IN_FID\": \"ORIG_FID\"})\n",
    "\n",
    "StreetSegment_LandUse_Subsets = StreetSegment_LandUse_Subsets.merge(StreetSegment_Subway_Distances, on=\"ORIG_FID\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Column Transformations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "StreetSegment_LandUse_Subsets[\"LandUse\"].isnull().any()\n",
    "\n",
    "OVERLAP_THRESHOLD = 0.5\n",
    "\n",
    "LANDUSE_COLUMN_VALUES = []\n",
    "\n",
    "LANDUSE_COLUMN_VALUES += [1*(\n",
    "    StreetSegment_LandUse_Subsets[\"LandUse\"].isin([\"01\", \"02\", \"03\"])\n",
    " + OVERLAP_THRESHOLD*(StreetSegment_LandUse_Subsets[\"LandUse\"].isin([\"04\"])))]\n",
    "\n",
    "LANDUSE_COLUMN_VALUES += [1*(\n",
    "    StreetSegment_LandUse_Subsets[\"LandUse\"].isin([\"05\"])\n",
    " + OVERLAP_THRESHOLD*(StreetSegment_LandUse_Subsets[\"LandUse\"].isin([\"04\"])))]\n",
    "\n",
    "LANDUSE_COLUMN_VALUES += [1*(StreetSegment_LandUse_Subsets[\"LandUse\"].isin([\"06\", \"07\", \"10\"]))]\n",
    "\n",
    "LANDUSE_COLUMN_VALUES += [1*(StreetSegment_LandUse_Subsets[\"LandUse\"].isin([\"08\"]))]\n",
    "## ok\n",
    "LANDUSE_COLUMN_VALUES += [1*(StreetSegment_LandUse_Subsets[\"LandUse\"].isin([\"09\", \"11\"]))]\n",
    "\n",
    "## ok\n",
    "LANDUSE_COLUMNS = [\"LandUse_t{0}\".format(i+1) for i in range(len(LANDUSE_COLUMN_VALUES))]\n",
    "\n",
    "## ok\n",
    "for i in range(len(LANDUSE_COLUMNS)):\n",
    "    column_name = LANDUSE_COLUMNS[i]\n",
    "    column = LANDUSE_COLUMN_VALUES[i]\n",
    "    StreetSegment_LandUse_Subsets[column_name] = column "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "## ok\n",
    "\n",
    "StreetSegment_LandUse_Subsets[\"BoroCode\"] = \"BoroCode\" + StreetSegment_LandUse_Subsets[\"BoroCode\"].astype(str)\n",
    "SSLU_Borough_OHE = make_OHE(StreetSegment_LandUse_Subsets, \"BoroCode\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Exclusive\n",
    "SSLU_LandUse = StreetSegment_LandUse_Subsets.loc[:,LANDUSE_COLUMNS]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "## ok\n",
    "\n",
    "StreetSegment_LandUse_Subsets[\"StreetWidth\"] = StreetSegment_LandUse_Subsets[\"lion_StreetWidt\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "## ok\n",
    "\n",
    "StreetSegment_LandUse_Subsets[\"StreetWidth_z\"] = (\n",
    "    (StreetSegment_LandUse_Subsets[\"StreetWidth\"] - StreetSegment_LandUse_Subsets[\"StreetWidth\"].mean())/\n",
    "    StreetSegment_LandUse_Subsets[\"StreetWidth\"].std()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "## ok\n",
    "\n",
    "StreetSegment_LandUse_Subsets[\"Segment_ID\"] = StreetSegment_LandUse_Subsets[\"lion_Segment_ID\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Exclusive\n",
    "StreetSegment_LandUse_Subsets[\"NumFloors\"] = StreetSegment_LandUse_Subsets[\"NumFloors\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Exclusive\n",
    "\n",
    "StreetSegment_LandUse_Subsets[\"UnitsRes_Prop\"] = (\n",
    "    StreetSegment_LandUse_Subsets[\"UnitsRes\"]/StreetSegment_LandUse_Subsets[\"UnitsTotal\"]\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Final Aggregation Preparation "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "## ok\n",
    "\n",
    "Concat_inputs = [\n",
    "    StreetSegment_LandUse_Subsets[\"StreetWidth\"],\n",
    "    StreetSegment_LandUse_Subsets[\"StreetWidth_z\"],\n",
    "    StreetSegment_LandUse_Subsets[\"NumFloors\"],\n",
    "    StreetSegment_LandUse_Subsets[\"UnitsRes_Prop\"],\n",
    "    StreetSegment_LandUse_Subsets[\"SubwayProximity\"],\n",
    "    SSLU_LandUse,\n",
    "    SSLU_Borough_OHE,\n",
    "    StreetSegment_LandUse_Subsets[\"ORIG_FID\"],\n",
    "    StreetSegment_LandUse_Subsets[\"Segment_ID\"]\n",
    "]\n",
    "\n",
    "StreetSegment_LandUse_Subsets_Grouping = pd.concat(Concat_inputs, axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Final Aggregation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "## ok\n",
    "\n",
    "\n",
    "StreetSegment_LandUse_Subsets_Aggregation = dict()\n",
    "\n",
    "StreetSegment_LandUse_Subsets_Aggregation.update(\n",
    "    {\n",
    "    \"NumFloors\": np.mean,\n",
    "    \"StreetWidth\": np.mean,\n",
    "    \"StreetWidth_z\": np.mean,\n",
    "    \"UnitsRes_Prop\": np.mean,\n",
    "    \"SubwayProximity\": np.mean,\n",
    "    }\n",
    ")\n",
    "\n",
    "StreetSegment_LandUse_Subsets_Aggregation.update(\n",
    "    dict([(col, np.sum) for col in SSLU_Borough_OHE.columns.values])\n",
    ")\n",
    "\n",
    "StreetSegment_LandUse_Subsets_Aggregation.update(\n",
    "    dict([(col, np.sum) for col in SSLU_LandUse.columns.values])\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "## ok\n",
    "\n",
    "Unique_StreetSegment_LandUse_Subsets = StreetSegment_LandUse_Subsets_Grouping.groupby([\"Segment_ID\"], as_index=False).agg(\n",
    "    StreetSegment_LandUse_Subsets_Aggregation\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "## ok\n",
    "\n",
    "\n",
    "OHE_columns = []\n",
    "\n",
    "OHE_columns += list(SSLU_Borough_OHE.columns.values)\n",
    "OHE_columns += LANDUSE_COLUMNS\n",
    "\n",
    "non_OHE_columns = (\n",
    "    set(Unique_StreetSegment_LandUse_Subsets.columns.values) - set(OHE_columns))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "## ok\n",
    "\n",
    "Concat_inputs = [\n",
    "    Unique_StreetSegment_LandUse_Subsets[list(non_OHE_columns)],\n",
    "    standardize_OHE(Unique_StreetSegment_LandUse_Subsets, list(SSLU_Borough_OHE.columns.values)),\n",
    "    standardize_OHE(Unique_StreetSegment_LandUse_Subsets, LANDUSE_COLUMNS) # added 2021/12/10\n",
    "]\n",
    "\n",
    "Unique_StreetSegment_LandUse_Subsets = pd.concat(Concat_inputs, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "## ok\n",
    "\n",
    "Unique_StreetSegment_LandUse_Subsets.isin([np.inf, -np.inf]).any().any()\n",
    "Unique_StreetSegment_LandUse_Subsets = Unique_StreetSegment_LandUse_Subsets.fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "#plt.hist(Unique_StreetSegment_LandUse_Subsets[\"LandUse_t1\"], 50)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Reformatting for EDA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "## ok\n",
    "\n",
    "Unique_StreetSegment_LandUse_Subsets_EDA = Unique_StreetSegment_LandUse_Subsets.copy()\n",
    "\n",
    "BOROCODE_NAMES = [\"Manhattan\", \"Bronx\", \"Brooklyn\", \"Queens\", \"Staten Island\"]\n",
    "\n",
    "Unique_StreetSegment_LandUse_Subsets_EDA[\"BoroCode\"] = np.argmax(Unique_StreetSegment_LandUse_Subsets_EDA[[\"BoroCode1\", \"BoroCode2\", \"BoroCode3\", \"BoroCode4\", \"BoroCode5\"]].to_numpy(), axis=1)+1\n",
    "Unique_StreetSegment_LandUse_Subsets_EDA = Unique_StreetSegment_LandUse_Subsets_EDA.drop(\n",
    "    columns = [\"BoroCode{0}\".format(i+1) for i in range(len(pd.unique(Unique_StreetSegment_LandUse_Subsets_EDA[\"BoroCode\"])))]\n",
    "                                                                                        )\n",
    "\n",
    "Unique_StreetSegment_LandUse_Subsets_EDA[\"BoroCode\"] = Unique_StreetSegment_LandUse_Subsets_EDA[\"BoroCode\"].apply(\n",
    "    lambda x: BOROCODE_NAMES[x - 1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "FILE_NAME = \"\\\\Unique_StreetSegment_LandUse_Subsets_EDA.csv\"\n",
    "FILE_PATH = FOLDER_PATH + FILE_NAME\n",
    "Unique_StreetSegment_LandUse_Subsets_EDA.to_csv(FILE_PATH, index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Reformatting for Modelling"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "Unique_StreetSegment_LandUse_Subsets_ml = Unique_StreetSegment_LandUse_Subsets.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "\n",
    "Unique_StreetSegment_LandUse_Subsets_ml = Unique_StreetSegment_LandUse_Subsets_ml.drop(columns=[\"StreetWidth\"])\n",
    "Unique_StreetSegment_LandUse_Subsets_ml = Unique_StreetSegment_LandUse_Subsets_ml.rename(\n",
    "    columns={\"StreetWidth_z\": \"StreetWidth\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "Concat_inputs = [\n",
    "    Unique_StreetSegment_LandUse_Subsets_ml,\n",
    "]\n",
    "Unique_StreetSegment_LandUse_Subsets_ml = pd.concat(Concat_inputs, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "FILE_NAME = \"\\\\Unique_StreetSegment_LandUse_Subsets_ml.csv\"\n",
    "FILE_PATH = FOLDER_PATH + FILE_NAME\n",
    "Unique_StreetSegment_LandUse_Subsets_ml.to_csv(FILE_PATH, index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data Cleaning gdb_join"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Column Transformation\n",
    "* F(['Is_Weekend']) => ['Is_Weekend']\n",
    "* F(['Traffic_Volume']) => ['Traffic_Volume']\n",
    "* F(['Season']) => ['Season1', 'Season2', 'Season4']\n",
    "* F(['Segment_ID']) => ['Segment_ID']\n",
    "* F(['F3_Hr_Intvl']) => ['3_Hr_Intvl']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECTED_GJ_COLUMNS_NUMERICAL = []\n",
    "SELECTED_GJ_COLUMNS_CATEGORICAL = []\n",
    "SELECTED_GJ_COLUMNS_OTHERS = []\n",
    "\n",
    "SELECTED_GJ_COLUMNS_NUMERICAL += ['Traffic_Volume']\n",
    "\n",
    "SELECTED_GJ_COLUMNS_CATEGORICAL += ['Is_Weekend']\n",
    "SELECTED_GJ_COLUMNS_CATEGORICAL += ['F3_Hr_Intvl']\n",
    "SELECTED_GJ_COLUMNS_CATEGORICAL += ['Season']\n",
    "\n",
    "SELECTED_GJ_COLUMNS_OTHERS += ['Segment_ID']\n",
    "\n",
    "SELECTED_GJ_COLUMNS = (SELECTED_GJ_COLUMNS_NUMERICAL + \n",
    "                       SELECTED_GJ_COLUMNS_CATEGORICAL +\n",
    "                       SELECTED_GJ_COLUMNS_OTHERS)\n",
    "\n",
    "gdb_join = gdb_join[SELECTED_GJ_COLUMNS]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Reformatting for EDA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "gdb_join_EDA = gdb_join.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "gdb_join_EDA[\"Is_Weekend\"] = gdb_join[\"Is_Weekend\"]\n",
    "gdb_join_EDA[\"Season\"] = gdb_join[\"Season\"]\n",
    "gdb_join_EDA[\"Segment_ID\"] = gdb_join[\"Segment_ID\"]\n",
    "gdb_join_EDA[\"3_Hr_Intvl\"] = gdb_join[\"F3_Hr_Intvl\"]\n",
    "gdb_join_EDA[\"Traffic_Volume\"] = gdb_join[\"Traffic_Volume\"] \n",
    "\n",
    "SEASON_NAMES = [\"Winter\", \"Spring\", \"Summer\", \"Autumn\"]\n",
    "\n",
    "gdb_join_EDA[\"Season\"] = gdb_join_EDA[\"Season\"].apply(\n",
    "    lambda x: SEASON_NAMES[x-1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "Concat_inputs = [\n",
    "    gdb_join_EDA[[\"Segment_ID\"]],\n",
    "    gdb_join_EDA[[\"Is_Weekend\"]],\n",
    "    gdb_join_EDA[[\"3_Hr_Intvl\"]],\n",
    "    gdb_join_EDA[[\"Season\"]],\n",
    "    gdb_join_EDA[[\"Traffic_Volume\"]],\n",
    "]\n",
    "gdb_join_EDA = pd.concat(Concat_inputs, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "FILE_NAME = \"\\\\gdb_join_EDA.csv\"\n",
    "FILE_PATH = FOLDER_PATH + FILE_NAME\n",
    "gdb_join_EDA.to_csv(FILE_PATH, index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Reformatting for Modelling"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "gdb_join_ml = gdb_join.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "def day_night_transform(x):\n",
    "    # flattens the range 0 to 24 to be between 0 and 1 as a bellcurve\n",
    "    return 2 * (1 + np.exp(((x - 12)/4)**2))**(-1)\n",
    "\n",
    "gdb_join_ml[\"Is_Weekend\"] = gdb_join[\"Is_Weekend\"].astype(int)\n",
    "\n",
    "gdb_join_ml[\"Season\"] = \"Season\"+gdb_join[\"Season\"].astype(int).astype(str)\n",
    "gj_season_OHE = make_OHE(gdb_join_ml, \"Season\")\n",
    "\n",
    "gdb_join_ml[\"Daylight\"] = gdb_join[\"F3_Hr_Intvl\"].apply(day_night_transform)\n",
    "\n",
    "gdb_join_ml[\"F3_Hr_Intvl\"] = \"Hr\"+gdb_join[\"F3_Hr_Intvl\"].astype(int).astype(str)\n",
    "gj_hour_OHE = make_OHE(gdb_join_ml, \"F3_Hr_Intvl\")\n",
    "gj_hour_OHE = gj_hour_OHE[[\"Hr\"+str(i) for i in range(3,27,3)]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "Concat_inputs = [\n",
    "    gdb_join_ml[[\"Segment_ID\"]],\n",
    "    gdb_join_ml[[\"Is_Weekend\"]],\n",
    "    gdb_join_ml[[\"Daylight\"]],\n",
    "    gj_season_OHE,\n",
    "    gj_hour_OHE,\n",
    "    gdb_join_ml[[\"Traffic_Volume\"]],\n",
    "]\n",
    "\n",
    "gdb_join_ml = pd.concat(Concat_inputs, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "FILE_NAME = \"\\\\gdb_join_ml.csv\"\n",
    "FILE_PATH = FOLDER_PATH + FILE_NAME\n",
    "gdb_join_ml.to_csv(FILE_PATH, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "#|"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "FOLDER_PATH = os.getcwd()\n",
    "FILE_NAME = \"\\\\gdb_join_EDA.csv\"\n",
    "FILE_PATH = FOLDER_PATH + \"\\\\\" + FILE_NAME\n",
    "gdb_join_EDA_orig = pd.read_csv(FILE_PATH)\n",
    "\n",
    "FILE_NAME = \"\\\\Unique_StreetSegment_LandUse_Subsets_EDA.csv\"\n",
    "FILE_PATH = FOLDER_PATH + \"\\\\\" + FILE_NAME\n",
    "Unique_StreetSegment_LandUse_Subsets_EDA_orig = pd.read_csv(FILE_PATH)\n",
    "\n",
    "gdb_join_EDA = gdb_join_EDA_orig.copy()\n",
    "Unique_StreetSegment_LandUse_Subsets_EDA = Unique_StreetSegment_LandUse_Subsets_EDA_orig.copy()\n",
    "\n",
    "JOINER_COLUMN = [\"Segment_ID\"]\n",
    "nyc_traffic_EDA_orig = gdb_join_EDA.merge(Unique_StreetSegment_LandUse_Subsets_EDA, on=JOINER_COLUMN)\n",
    "\n",
    "FILE_NAME = \"\\\\nyc_traffic_EDA_orig.csv\"\n",
    "FILE_PATH = FOLDER_PATH + FILE_NAME\n",
    "nyc_traffic_EDA_orig.to_csv(FILE_PATH, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "FOLDER_PATH = os.getcwd()\n",
    "FILE_NAME = \"\\\\gdb_join_ml.csv\"\n",
    "FILE_PATH = FOLDER_PATH + \"\\\\\" + FILE_NAME\n",
    "gdb_join_ml_orig = pd.read_csv(FILE_PATH)\n",
    "\n",
    "FILE_NAME = \"\\\\Unique_StreetSegment_LandUse_Subsets_ml.csv\"\n",
    "FILE_PATH = FOLDER_PATH + \"\\\\\" + FILE_NAME\n",
    "Unique_StreetSegment_LandUse_Subsets_ml_orig = pd.read_csv(FILE_PATH)\n",
    "\n",
    "gdb_join_ml = gdb_join_ml_orig.copy()\n",
    "Unique_StreetSegment_LandUse_Subsets_ml = Unique_StreetSegment_LandUse_Subsets_ml_orig.copy()\n",
    "\n",
    "JOINER_COLUMN = [\"Segment_ID\"]\n",
    "nyc_traffic_ml_orig = gdb_join_ml.merge(Unique_StreetSegment_LandUse_Subsets_ml, on=JOINER_COLUMN)\n",
    "\n",
    "FILE_NAME = \"\\\\nyc_traffic_ml_orig.csv\"\n",
    "FILE_PATH = FOLDER_PATH + FILE_NAME\n",
    "nyc_traffic_ml_orig.to_csv(FILE_PATH, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "#/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}