(archive)clean_merged_arcgis_data_and_merge_census_data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dea777a2-3d18-4792-aaee-5fbe5d488c81",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from functools import reduce"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "3cd813ea",
   "metadata": {},
   "source": [
    "# Clean Merged ArcGIS Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "19c23d6e-fdc2-4ae1-9d19-3394f4d7967f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Reading in data\n",
    "df = pd.read_excel(\"Processed Data/ArcGIS_SpatialJoin_MergedData.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5d88c4eb-08cb-496e-b4aa-357d490b430b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Here are the first five lines\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "afe89874-059d-4fb4-9372-c84f9382a6e9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# What is the shape of the data? # rows and # cols\n",
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4df2f9b5-064c-4f99-b0a7-74be514f6bbf",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Here are all the column names\n",
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0e0503e2-2df3-4fcd-934a-561f4f4555ee",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a copy of the dataframe\n",
    "df1 = df.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a51b7b1f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Remove the unncessary words in column names created because of ArcGIS export\n",
    "for col in df1.columns:\n",
    "    df1.rename(columns={col:col.split('.')[1]}, inplace=True)\n",
    "df1.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e419d19a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Convert the GEOID column from float64 (e.g. 3.604700e+10) to int64 (36005000100)\n",
    "df1['CT2020_GEOID'] = df1['CT2020_GEOID'].astype('Int64')\n",
    "df1['CT2020_GEOID']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "182f0085",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Checking -- note: there will still be dozens of records that don't have GEOID, those crashes usually are on bridges or turnpike\n",
    "df1[df1['CT2020_GEOID'].isnull()][['CRASH_DATE','CRASH_TIME','BOROUGH','ZIP_CODE','LATITUDE','LONGITUDE', 'ON_STREET_NAME','CROSS_STREET_NAME']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "16f52f68",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop the columns created because of ArcGIS's \"spatial join\" operation (6 join * 3 col/join = 18 columns)\n",
    "ArcGIS_drop_columns=['OBJECTID', 'Join_Count', 'TARGET_FID']\n",
    "\n",
    "# Drop all columns in census tract dataset except 'CT2020_GEOID'\n",
    "census_tract_drop_columns = [\n",
    "    # 'CT2020_GEOID'\n",
    "    'CT2020_CTLabel', \n",
    "    'CT2020_BoroCode', \n",
    "    'CT2020_BoroName',\n",
    "    'CT2020_CT2020', \n",
    "    'CT2020_BoroCT2020', \n",
    "    'CT2020_CDEligibil', \n",
    "    'CT2020_NTAName', \n",
    "    'CT2020_NTA2020', \n",
    "    'CT2020_CDTA2020',\n",
    "    'CT2020_CDTANAME', \n",
    "    'CT2020_Shape_Leng', \n",
    "    'CT2020_Shape_Area']\n",
    "\n",
    "# Drop unuseful columns from bike route datasets\n",
    "bike_route_drop_columns = [\n",
    "    'BR2021_segmentid',\n",
    "    # 'BR2021_allclasses',\n",
    "    # 'BR2021_facilitycl',\n",
    "    'BR2021_onoffst',\n",
    "    'BR2021_bikedir',\n",
    "    'BR2021_lanecount',\n",
    "    'BR2021_ft_facilit',\n",
    "    'BR2021_tf_facilit',\n",
    "    'BR2021_ft2facilit',\n",
    "    'BR2021_tf2facilit',\n",
    "    'BR2021_comments',\n",
    "    'BR2021_boro',\n",
    "    'BR2021_street',\n",
    "    'BR2021_fromstreet',\n",
    "    'BR2021_tostreet',\n",
    "    'BR2021_shape_leng',\n",
    "    'BR2020_segmentid',\n",
    "    # 'BR2020_allclasses',\n",
    "    # 'BR2020_facilitycl',\n",
    "    'BR2020_onoffst',\n",
    "    'BR2020_bikedir',\n",
    "    'BR2020_lanecount',\n",
    "    'BR2020_ft_facilit',\n",
    "    'BR2020_tf_facilit',\n",
    "    'BR2020_instdate',\n",
    "    'BR2020_moddate',\n",
    "    'BR2020_comments',\n",
    "    'BR2020_boro',\n",
    "    'BR2020_street',\n",
    "    'BR2020_fromstreet',\n",
    "    'BR2020_tostreet',\n",
    "    'BR2019_segmentid',\n",
    "    # 'BR2019_allclasses',\n",
    "    # 'BR2019_facilitycl',\n",
    "    'BR2019_onoffst',\n",
    "    'BR2019_bikedir',\n",
    "    'BR2019_lanecount',\n",
    "    'BR2019_ft_facilit',\n",
    "    'BR2019_tf_facilit',\n",
    "    'BR2019_date_instd',\n",
    "    'BR2019_time_instd',\n",
    "    'BR2019_date_modda',\n",
    "    'BR2019_time_modda',\n",
    "    'BR2019_comments',\n",
    "    'BR2019_boro',\n",
    "    'BR2019_street',\n",
    "    'BR2019_fromstreet',\n",
    "    'BR2019_tostreet',\n",
    "    'BR2018_segmentid',\n",
    "    # 'BR2018_allclasses',\n",
    "    'BR2018_onoffst',\n",
    "    'BR2018_bikedir',\n",
    "    'BR2018_lanecount',\n",
    "    # 'BR2018_facilitycl',\n",
    "    'BR2018_ft_facilit',\n",
    "    'BR2018_tf_facilit',\n",
    "    'BR2018_instdate',\n",
    "    'BR2018_moddate',\n",
    "    'BR2018_comments',\n",
    "    'BR2018_boro',\n",
    "    'BR2018_street',\n",
    "    'BR2018_fromstreet',\n",
    "    'BR2018_tostreet',\n",
    "    'BR2018_segmentmil',\n",
    "    'BR2018_LaneMiles',\n",
    "    'BR2018_BikeID',\n",
    "    'BR2018_Shape_Leng',\n",
    "    'BR2018_OBJECTID',\n",
    "    'BR2018_OBJECTID_1',\n",
    "    'BR2018_lasteditby',\n",
    "    'BR2017_segmentid',\n",
    "    # 'BR2017_allclasses',\n",
    "    'BR2017_onoffst',\n",
    "    'BR2017_bikedir',\n",
    "    'BR2017_lanecount',\n",
    "    'BR2017_ft_facilit',\n",
    "    'BR2017_tf_facilit',\n",
    "    'BR2017_instdate',\n",
    "    'BR2017_moddate',\n",
    "    'BR2017_comments',\n",
    "    'BR2017_boro',\n",
    "    'BR2017_street',\n",
    "    'BR2017_fromstreet',\n",
    "    'BR2017_tostreet',\n",
    "    'BR2017_OBJECTID_1']\n",
    "\n",
    "# Drop unuseful columns from crash datasets\n",
    "crash_drop_columns = [\n",
    "\t# 'CRASH_DATE',\n",
    "    'CRASH_TIME',\n",
    "    'BOROUGH',\n",
    "    # 'ZIP_CODE',\n",
    "    'LATITUDE',\n",
    "    'LONGITUDE',\n",
    "    'LOCATION',\n",
    "    'ON_STREET_NAME',\n",
    "    'CROSS_STREET_NAME',\n",
    "    'OFF_STREET_NAME',\n",
    "    # 'NUMBER_OF_PERSONS_INJURED',\n",
    "    # 'NUMBER_OF_PERSONS_KILLED',\n",
    "    'NUMBER_OF_PEDESTRIANS_INJURED',\n",
    "    'NUMBER_OF_PEDESTRIANS_KILLED',\n",
    "    # 'NUMBER_OF_CYCLIST_INJURED',\n",
    "    # 'NUMBER_OF_CYCLIST_KILLED',\n",
    "    'NUMBER_OF_MOTORIST_INJURED',\n",
    "    'NUMBER_OF_MOTORIST_KILLED',\n",
    "    'CONTRIBUTING_FACTOR_VEHICLE_1',\n",
    "    'CONTRIBUTING_FACTOR_VEHICLE_2',\n",
    "    'CONTRIBUTING_FACTOR_VEHICLE_3',\n",
    "    'CONTRIBUTING_FACTOR_VEHICLE_4',\n",
    "    'CONTRIBUTING_FACTOR_VEHICLE_5',\n",
    "    'COLLISION_ID',\n",
    "    'VEHICLE_TYPE_CODE_1',\n",
    "    'VEHICLE_TYPE_CODE_2',\n",
    "    'VEHICLE_TYPE_CODE_3',\n",
    "    'VEHICLE_TYPE_CODE_4',\n",
    "    'VEHICLE_TYPE_CODE_5',\n",
    "]\n",
    "\n",
    "df2 = df1.drop(columns=ArcGIS_drop_columns+census_tract_drop_columns+bike_route_drop_columns+crash_drop_columns)\n",
    "df1.shape[1] - df2.shape[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2ac17c1f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Here are the first five lines\n",
    "df2.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "77a12cba",
   "metadata": {},
   "source": [
    "### Process bike lane data\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5336c4ef",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Make a copy\n",
    "df3 = df2.copy()\n",
    "df3.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8366c57f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Check all possible values of allclasses for one year\n",
    "df3['BR2021_allclasses'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ef571923",
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Method 1 to get the least protected bikelane type (slower) for one column\n",
    "# def least_protected_bikelane(row):\n",
    "#     '''\n",
    "#     This function takes in a default argument for the apply function.\n",
    "#     The function will process the bikelane data, so that the new variable shows the lowest facility class \n",
    "#     found along a segment. For an example, if a segment has a protected bike lane on one side of the street \n",
    "#     (class I) and a shared lane (class III) on the opposite, then this field will show “III”.\n",
    "#         # I = Protected (most protected bike lane type)\n",
    "#         # II = Conventional\n",
    "#         # III = Signed/ Marked Route (least protected bike lane type)\n",
    "#         # L = Link\n",
    "#     '''\n",
    "#     if pd.isna(row['BR2021_allclasses']) == False:\n",
    "#         bikelane_type_list = row['BR2021_allclasses'].split(',')\n",
    "#         if 'III' in bikelane_type_list:\n",
    "#             return 3\n",
    "#         elif 'II' in bikelane_type_list:\n",
    "#             return 2\n",
    "#         elif 'I' in bikelane_type_list:\n",
    "#             return 1\n",
    "#         else:\n",
    "#             return row['BR2021_allclasses']\n",
    "#     else:\n",
    "#         return row['BR2021_allclasses']\n",
    "\n",
    "# df3['BR2021_bikelane_min'] = df3.apply(least_protected_bikelane, axis=1)\n",
    "\n",
    "# # Checking\n",
    "# df3[df3['BR2021_allclasses'].notnull()][['BR2021_allclasses','BR2021_bikelane_min']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e7f11847",
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Method 2 to get the least protected bikelane type (faster, vectorized)\n",
    "# df3.loc[df3['BR2021_allclasses'].str.contains('L', na=True), 'BR2021_bikelane_min'] = 'L'\n",
    "# df3.loc[df3['BR2021_allclasses'].str.contains('I', na=True), 'BR2021_bikelane_min'] = 'I'\n",
    "# df3.loc[df3['BR2021_allclasses'].str.contains('II', na=True), 'BR2021_bikelane_min'] = 'II'\n",
    "# df3.loc[df3['BR2021_allclasses'].str.contains('III', na=True), 'BR2021_bikelane_min'] = 'III'\n",
    "# df3.loc[df3['BR2021_allclasses'].isnull() == True, 'BR2021_bikelane_min'] = np.nan\n",
    "\n",
    "# # Checking\n",
    "# df3[df3['BR2021_allclasses'].notnull()][['BR2021_allclasses','BR2021_bikelane_min']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a0920a80",
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Use method 2 to get the least protected bikelane type\n",
    "# def get_least_protected_bikelane_vectorized_method(df, old_var_name, new_var_name):\n",
    "#     '''\n",
    "#     This function takes in a dataframe, the name of the variable that contains info about the bikelane, \n",
    "#     which is usually in the format of BRxxxx_allclasses, and the new name of the variable it will output.\n",
    "#         # I = Protected (most protected bike lane type)\n",
    "#         # II = Conventional\n",
    "#         # III = Signed/ Marked Route (least protected bike lane type)\n",
    "#         # L = Link\n",
    "#     The function will process the bikelane data, so that the new variable shows the lowest facility class \n",
    "#     found along a segment. For an example, if a segment has a protected bike lane on one side of the street \n",
    "#     (class I) and a shared lane (class III) on the opposite, then this field will show “III”.\n",
    "#     '''\n",
    "#     df.loc[df[old_var_name].str.contains('L', na=True), new_var_name] = 'L'\n",
    "#     df.loc[df[old_var_name].str.contains('I', na=True), new_var_name] = 'I'\n",
    "#     df.loc[df[old_var_name].str.contains('II', na=True), new_var_name] = 'II'\n",
    "#     df.loc[df[old_var_name].str.contains('III', na=True), new_var_name] = 'III'\n",
    "#     df.loc[df[old_var_name].isnull() == True, new_var_name] = np.nan\n",
    "\n",
    "#     return df\n",
    "\n",
    "# df3 = get_least_protected_bikelane_vectorized_method(df3, 'BR2017_allclasses', 'BR2017_bikelane_min')\n",
    "# df3 = get_least_protected_bikelane_vectorized_method(df3, 'BR2018_allclasses', 'BR2018_bikelane_min')\n",
    "# df3 = get_least_protected_bikelane_vectorized_method(df3, 'BR2019_allclasses', 'BR2019_bikelane_min')\n",
    "# df3 = get_least_protected_bikelane_vectorized_method(df3, 'BR2020_allclasses', 'BR2020_bikelane_min')\n",
    "# df3 = get_least_protected_bikelane_vectorized_method(df3, 'BR2021_allclasses', 'BR2021_bikelane_min')\n",
    "\n",
    "# # Checking\n",
    "# df3[df3['BR2021_allclasses'].notnull()][['BR2017_allclasses', 'BR2017_bikelane_min', 'BR2018_allclasses', 'BR2018_bikelane_min', 'BR2019_allclasses', 'BR2019_bikelane_min', 'BR2020_allclasses', 'BR2020_bikelane_min', 'BR2021_allclasses', 'BR2021_bikelane_min']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "701289e7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Use method 1 to get the least protected bikelane type\n",
    "def get_least_protected_bikelane(row, variable_name):\n",
    "    '''\n",
    "    This function takes in a default argument for the apply function, and an additional argument for \n",
    "    the name of the variable that contains info about the bikelane, which is usually in the format of \n",
    "    BRxxxx_allclasses.\n",
    "        # 1 = I = Protected (most protected bike lane type)\n",
    "        # 2 = II = Conventional\n",
    "        # 3 = III = Signed/ Marked Route (least protected bike lane type)\n",
    "        # NaN = L = Link\n",
    "    The function will process the bikelane data, so that the new variable shows the lowest facility class \n",
    "    found along a segment. For an example, if a segment has a protected bike lane on one side of the street \n",
    "    (class I) and a shared lane (class III) on the opposite, then this field will show “III”.\n",
    "    '''\n",
    "    if pd.isna(row[variable_name]) == False:\n",
    "        bikelane_type_list = row[variable_name].split(',')\n",
    "        if 'III' in bikelane_type_list:\n",
    "            return 3\n",
    "        elif 'II' in bikelane_type_list:\n",
    "            return 2\n",
    "        elif 'I' in bikelane_type_list:\n",
    "            return 1\n",
    "        elif 'L' in bikelane_type_list:\n",
    "            return np.nan\n",
    "    else:\n",
    "        return 0\n",
    "\n",
    "df3['BR2017_bikelane_min'] = df3.apply(get_least_protected_bikelane, variable_name='BR2017_allclasses', axis=1)\n",
    "df3['BR2018_bikelane_min'] = df3.apply(get_least_protected_bikelane, variable_name='BR2018_allclasses', axis=1)\n",
    "df3['BR2019_bikelane_min'] = df3.apply(get_least_protected_bikelane, variable_name='BR2019_allclasses', axis=1)\n",
    "df3['BR2020_bikelane_min'] = df3.apply(get_least_protected_bikelane, variable_name='BR2020_allclasses', axis=1)\n",
    "df3['BR2021_bikelane_min'] = df3.apply(get_least_protected_bikelane, variable_name='BR2021_allclasses', axis=1)\n",
    "\n",
    "# Checking\n",
    "df3[df3['BR2021_allclasses'].notnull()][['BR2017_allclasses', 'BR2017_bikelane_min', 'BR2018_allclasses', 'BR2018_bikelane_min', 'BR2019_allclasses', 'BR2019_bikelane_min', 'BR2020_allclasses', 'BR2020_bikelane_min', 'BR2021_allclasses', 'BR2021_bikelane_min']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8fc8ad13",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Checking for the \"Link\" type\n",
    "df3[df3['BR2021_allclasses'] == 'L'][['BR2017_allclasses', 'BR2017_bikelane_min', 'BR2018_allclasses', 'BR2018_bikelane_min', 'BR2019_allclasses', 'BR2019_bikelane_min', 'BR2020_allclasses', 'BR2020_bikelane_min', 'BR2021_allclasses', 'BR2021_bikelane_min']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6d351ca0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Use method 1 to get the most protected bikelane type\n",
    "def get_most_protected_bikelane(row, variable_name):\n",
    "    '''\n",
    "    This function takes in a default argument for the apply function, and an additional argument for \n",
    "    the name of the variable that contains info about the bikelane, which is usually in the format of \n",
    "    BRxxxx_allclasses.\n",
    "        # 1 = I = Protected (most protected bike lane type)\n",
    "        # 2 = II = Conventional\n",
    "        # 3 = III = Signed/ Marked Route (least protected bike lane type)\n",
    "        # NaN = L = Link\n",
    "        The function will process the bikelane data, so that the new variable shows the highest facility class \n",
    "        found along a segment. For an example, if a segment has a protected bike lane on one side of the street \n",
    "        (class I) and a shared lane (class III) on the opposite, then this field will show “I”.\n",
    "    '''\n",
    "    if pd.isna(row[variable_name]) == False:\n",
    "        bikelane_type_list = row[variable_name].split(',')\n",
    "        if 'I' in bikelane_type_list:\n",
    "            return 1\n",
    "        elif 'II' in bikelane_type_list:\n",
    "            return 2\n",
    "        elif 'III' in bikelane_type_list:\n",
    "            return 3\n",
    "        elif 'L' in bikelane_type_list:\n",
    "            return np.nan\n",
    "    else:\n",
    "        return 0\n",
    "df3['BR2017_bikelane_max'] = df3.apply(get_most_protected_bikelane, variable_name='BR2017_allclasses', axis=1)\n",
    "df3['BR2018_bikelane_max'] = df3.apply(get_most_protected_bikelane, variable_name='BR2018_allclasses', axis=1)\n",
    "df3['BR2019_bikelane_max'] = df3.apply(get_most_protected_bikelane, variable_name='BR2019_allclasses', axis=1)\n",
    "df3['BR2020_bikelane_max'] = df3.apply(get_most_protected_bikelane, variable_name='BR2020_allclasses', axis=1)\n",
    "df3['BR2021_bikelane_max'] = df3.apply(get_most_protected_bikelane, variable_name='BR2021_allclasses', axis=1)\n",
    "\n",
    "# Checking\n",
    "df3[df3['BR2021_allclasses'].notnull()][['BR2017_allclasses', 'BR2017_bikelane_max', 'BR2018_allclasses', 'BR2018_facilitycl', 'BR2018_bikelane_max', 'BR2019_allclasses', 'BR2019_facilitycl', 'BR2019_bikelane_max', 'BR2020_allclasses', 'BR2020_facilitycl', 'BR2020_bikelane_max', 'BR2021_allclasses', 'BR2021_facilitycl', 'BR2021_bikelane_max']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3055010e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Checking\n",
    "df3[(df3['BR2019_bikelane_max'] != df3['BR2019_facilitycl']) & df3['BR2019_bikelane_max'].notnull()][['BR2019_allclasses', 'BR2019_facilitycl', 'BR2019_bikelane_max']]\n",
    "# df3[(df3['BR2019_bikelane_max'] != df3['BR2019_facilitycl']) & df3['BR2019_bikelane_max'].notnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6d861b0f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop the allclasses and facilitycl columns\n",
    "drop_columns = [\n",
    "    'BR2017_allclasses',\n",
    "    'BR2018_allclasses',\n",
    "    'BR2019_allclasses',\n",
    "    'BR2020_allclasses',\n",
    "    'BR2021_allclasses',\n",
    "    'BR2018_facilitycl',\n",
    "    'BR2019_facilitycl',\n",
    "    'BR2020_facilitycl',\n",
    "    'BR2021_facilitycl']\n",
    "\n",
    "df4 = df3.drop(columns=drop_columns)\n",
    "df4.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fcf265cc",
   "metadata": {},
   "source": [
    "### Create time-related variables from CRASH DATE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f074ccf3",
   "metadata": {},
   "outputs": [],
   "source": [
    "df5 = df4.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d76468f6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a numerical variable column to indicate the year of crash accident\n",
    "df5['CRASH_YEAR'] = pd.to_datetime(df5['CRASH_DATE']).dt.year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c4438b74",
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Create a numerical variable column to indicate the month of crash accident\n",
    "# df5['CRASH_MONTH'] = pd.to_datetime(df5['CRASH_DATE']).dt.month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "68db08fe",
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Create a numerical variable column to indicate the year+month of crash accident\n",
    "# df5['CRASH_YEAR-MONTH'] = pd.to_datetime(df5['CRASH_DATE']).dt.to_period('m')\n",
    "# df5['CRASH_YEAR-MONTH']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c98c8598",
   "metadata": {},
   "outputs": [],
   "source": [
    "# How many crash records happen on the bike path in 2021? 2183\n",
    "df5[(df5['BR2020_bikelane_min'].notnull()) & (df5['CRASH_YEAR']==2020)].shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9e59d624",
   "metadata": {},
   "source": [
    "### Combine 5 bikelane columns into 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "888ec78c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Combine bikelane data depending on the year\n",
    "def combine_bikelane_data(row, variable_name):\n",
    "    '''\n",
    "    This function takes in a default argument for the apply function.\n",
    "    The function will combine 5 years of bikelane data and show the data that corresponds to the crash year. \n",
    "    For example, if the crash happens in year 2021, the data of BR_bikelane_max will come from BR2021_bikelane_max.\n",
    "    And the data of BR_bikelane_min will come from BR2021_bikelane_min.\n",
    "    '''\n",
    "    if row['CRASH_YEAR'] == 2017:\n",
    "        return row['BR2017_'+variable_name]\n",
    "    elif row['CRASH_YEAR'] == 2018:\n",
    "        return row['BR2018_'+variable_name]\n",
    "    elif row['CRASH_YEAR'] == 2019:\n",
    "        return row['BR2019_'+variable_name]\n",
    "    elif row['CRASH_YEAR'] == 2020:\n",
    "        return row['BR2020_'+variable_name]\n",
    "    elif row['CRASH_YEAR'] == 2021:\n",
    "        return row['BR2021_'+variable_name]\n",
    "\n",
    "df5['BR_bikelane_max'] = df5.apply(combine_bikelane_data, variable_name='bikelane_max', axis=1)\n",
    "df5['BR_bikelane_min'] = df5.apply(combine_bikelane_data, variable_name='bikelane_min', axis=1)\n",
    "\n",
    "# Checking\n",
    "df5[df5['CRASH_YEAR'].notnull()][['BR_bikelane_max','CRASH_YEAR','BR2021_bikelane_max','BR2020_bikelane_max','BR2020_bikelane_max','BR2019_bikelane_max','BR2018_bikelane_max','BR2017_bikelane_max']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2b485905",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop unneeded columns\n",
    "drop_columns = [\n",
    "    'BR2017_bikelane_min',\n",
    "    'BR2018_bikelane_min',\n",
    "    'BR2019_bikelane_min',\n",
    "    'BR2020_bikelane_min',\n",
    "    'BR2021_bikelane_min',\n",
    "    'BR2017_bikelane_max',\n",
    "    'BR2018_bikelane_max',\n",
    "    'BR2019_bikelane_max',\n",
    "    'BR2020_bikelane_max',\n",
    "    'BR2021_bikelane_max']\n",
    "\n",
    "df6 = df5.drop(columns=drop_columns)\n",
    "df6.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4a884f56",
   "metadata": {},
   "source": [
    "### Export the processed & clean data (bike route + crash + census tract) for recording keeping"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e68beee5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Export data as csv file\n",
    "df6.to_csv('Processed Data/ArcGIS_SpatialJoin_MergedData_Cleaned.csv',index=False)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "730307b9",
   "metadata": {},
   "source": [
    "# Merge All Census Data to the Merged ArcGIS Data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f9000f06",
   "metadata": {},
   "source": [
    "### Merge 1 census data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3c4dfcca",
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Import census data\n",
    "# df_acs2017 = pd.read_csv(\"Raw Data/Raw Data in txt File for American Community Survey (ACS) 5-Year Estimates/ACS_2013-2017.txt\",sep='\\t')\n",
    "# df_acs2018 = pd.read_csv(\"Raw Data/Raw Data in txt File for American Community Survey (ACS) 5-Year Estimates/ACS_2014-2018.txt\",sep='\\t')\n",
    "# df_acs2019 = pd.read_csv(\"Raw Data/Raw Data in txt File for American Community Survey (ACS) 5-Year Estimates/ACS_2015-2019.txt\",sep='\\t')\n",
    "# df_acs2020 = pd.read_csv(\"Raw Data/Raw Data in txt File for American Community Survey (ACS) 5-Year Estimates/ACS_2016-2020.txt\",sep='\\t')\n",
    "# df_acs2021 = pd.read_csv(\"Raw Data/Raw Data in txt File for American Community Survey (ACS) 5-Year Estimates/ACS_2017-2021.txt\",sep='\\t')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8828fa6c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Here are the first five lines\n",
    "# df_acs2017.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cc4371b5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# # What is the shape of the data? # rows and # cols\n",
    "# df_acs2017.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "72dbed58",
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Convert the data type for later join operation & create as a new column under the same name as census tract data\n",
    "# # Census tract uses CT2020_GEOID, ACS uses Geo_FIPS\n",
    "# df_acs2017['CT2020_GEOID'] = df_acs2017['Geo_FIPS'].astype('Int64')\n",
    "# df_acs2017.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2d5f5842",
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Keep the borough column by renaming it before dropping all the others\n",
    "# df_acs2017['borough'] = df_acs2017['Geo_COUNTY']\n",
    "# df_acs2017.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3baf660a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Drop all columns that start with \"Geo_\" (ie. geo data, non-demographic data)\n",
    "# df_acs2017 = df_acs2017.loc[:,~df_acs2017.columns.str.startswith('Geo_')]\n",
    "# df_acs2017.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7669bb54",
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Rename columns\n",
    "# # A00001_001:     Total Population\n",
    "# # A00002_002:     Population Density (Per Sq. Mile)\n",
    "# # B12001_001:     Population 25 Years and Over\n",
    "# # B12001_002:     Population 25 Years and Over: Less than High School\n",
    "# # B12001_003:     Population 25 Years and Over: High School Diploma\n",
    "# # B12001_004:     Population 25 Years and Over: Bachelor's Degree or Better\n",
    "# # A14006_001:     Median Household Income (In 2021 Inflation Adjusted Dollars) [Dollars adjusted for inflation to match value in 2021]\n",
    "# # A09005_001:     Workers 16 Years and Over:\n",
    "# # A09005_002:     Workers 16 Years and Over: Car, Truck, or Van\n",
    "# # A09005_003:     Workers 16 Years and Over: Public Transportation (Includes Taxicab)\n",
    "# # A09005_005:     Workers 16 Years and Over: Bicycle\n",
    "# # A09003_001:     Average Commute to Work (In Min)\n",
    "\n",
    "# df_acs2017 = df_acs2017.rename({\n",
    "#     'SE_A00001_001':'ttl_pop',\n",
    "#     'SE_A00002_002':'pop_density_per_sq_mil',\n",
    "#     'SE_B12001_001':'pop_25_yr_over',\n",
    "#     'SE_B12001_002':'educ_less_hs',\n",
    "#     'SE_B12001_003':'educ_hs',\n",
    "#     'SE_B12001_004':'educ_bs_over',\n",
    "#     'SE_A14006_001':'median_household_inc',\n",
    "#     'SE_A09005_001':'workers_16_yr_over',\n",
    "#     'SE_A09005_002':'tranport_mean_car',\n",
    "#     'SE_A09005_003':'tranport_mean_public',\n",
    "#     'SE_A09005_005':'tranport_mean_bike',\n",
    "#     'SE_A09003_001':'avg_commmute_to_work_min'\n",
    "#     }, axis='columns')\n",
    "# df_acs2017.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6714a62b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Compute the \"population over 25 years and over for education\"\n",
    "# df_acs2017['educ_less_hs_pct'] = df_acs2017['educ_less_hs']/df_acs2017['pop_25_yr_over']\n",
    "# df_acs2017['educ_hs_pct'] = df_acs2017['educ_hs']/df_acs2017['pop_25_yr_over']\n",
    "# df_acs2017['educ_bs_over_pct'] = df_acs2017['educ_bs_over']/df_acs2017['pop_25_yr_over']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "194f62f7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Compute the \"workers over 16 years and over for tranportation mean\"\n",
    "# df_acs2017['tranport_mean_car_pct'] = df_acs2017['tranport_mean_car']/df_acs2017['workers_16_yr_over']\n",
    "# df_acs2017['tranport_mean_public_pct'] = df_acs2017['tranport_mean_public']/df_acs2017['workers_16_yr_over']\n",
    "# df_acs2017['tranport_mean_bike_pct'] = df_acs2017['tranport_mean_bike']/df_acs2017['workers_16_yr_over']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5400cc8d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Drop all remaining columns that start with \"SE\"\n",
    "# df_acs2017 = df_acs2017.loc[:,~df_acs2017.columns.str.startswith('SE_')]\n",
    "# df_acs2017.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6563372e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# df_acs2017.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f4238528",
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Add year to column name\n",
    "# year = 2017\n",
    "# for col in df_acs2017.columns:\n",
    "#     df_acs2017.rename({col:'ACS'+str(year)+'_'+col}, axis='columns', inplace=True)\n",
    "# df_acs2017.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "25c2c18c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Merge data\n",
    "# df_test = pd.merge(df3, df_acs2017, how=\"left\", on=\"CT2020_GEOID\")\n",
    "# df_test.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e43c0eac",
   "metadata": {},
   "source": [
    "### Merge all census data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "391b3dc4",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import census data\n",
    "df_acs2017 = pd.read_csv(\"Raw Data/Raw Data in txt File for American Community Survey (ACS) 5-Year Estimates/ACS_2013-2017.txt\",sep='\\t')\n",
    "df_acs2018 = pd.read_csv(\"Raw Data/Raw Data in txt File for American Community Survey (ACS) 5-Year Estimates/ACS_2014-2018.txt\",sep='\\t')\n",
    "df_acs2019 = pd.read_csv(\"Raw Data/Raw Data in txt File for American Community Survey (ACS) 5-Year Estimates/ACS_2015-2019.txt\",sep='\\t')\n",
    "df_acs2020 = pd.read_csv(\"Raw Data/Raw Data in txt File for American Community Survey (ACS) 5-Year Estimates/ACS_2016-2020.txt\",sep='\\t')\n",
    "df_acs2021 = pd.read_csv(\"Raw Data/Raw Data in txt File for American Community Survey (ACS) 5-Year Estimates/ACS_2017-2021.txt\",sep='\\t')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5d919c99",
   "metadata": {},
   "outputs": [],
   "source": [
    "def process_acs_data(dataframe, year):\n",
    "    '''\n",
    "    This function takes in one single ACS dataframe and its corresponding year, cleans it, and outputs the dataframe.\n",
    "    '''\n",
    "    df = dataframe.copy()\n",
    "\n",
    "    # Convert the data type for later join operation & create as a new column under the same name as census tract data\n",
    "    # Census tract uses CT2020_GEOID, ACS uses Geo_FIPS\n",
    "    df['CT2020_GEOID'] = df['Geo_FIPS'].astype('Int64')\n",
    "\n",
    "    # Keep the borough column by renaming it before dropping all the others\n",
    "    df['borough'] = df['Geo_COUNTY']\n",
    "\n",
    "    # Drop all columns that start with \"Geo_\" (ie. geo data, non-demographic data)\n",
    "    df = df.loc[:,~df.columns.str.startswith('Geo_')]\n",
    "\n",
    "    # Rename columns\n",
    "    # A00001_001:     Total Population\n",
    "    # A00002_002:     Population Density (Per Sq. Mile)\n",
    "    # B12001_001:     Population 25 Years and Over\n",
    "    # B12001_002:     Population 25 Years and Over: Less than High School\n",
    "    # B12001_003:     Population 25 Years and Over: High School Diploma\n",
    "    # B12001_004:     Population 25 Years and Over: Bachelor's Degree or Better\n",
    "    # A14006_001:     Median Household Income (In 2021 Inflation Adjusted Dollars) [Dollars adjusted for inflation to match value in 2021]\n",
    "    # A09005_001:     Workers 16 Years and Over:\n",
    "    # A09005_002:     Workers 16 Years and Over: Car, Truck, or Van\n",
    "    # A09005_003:     Workers 16 Years and Over: Public Transportation (Includes Taxicab)\n",
    "    # A09005_005:     Workers 16 Years and Over: Bicycle\n",
    "    # A09003_001:     Average Commute to Work (In Min)\n",
    "    df = df.rename({\n",
    "        'SE_A00001_001':'ttl_pop',\n",
    "        'SE_A00002_002':'pop_density_per_sq_mil',\n",
    "        'SE_B12001_001':'pop_25_yr_over',\n",
    "        'SE_B12001_002':'educ_less_hs',\n",
    "        'SE_B12001_003':'educ_hs',\n",
    "        'SE_B12001_004':'educ_bs_over',\n",
    "        'SE_A14006_001':'median_household_inc',\n",
    "        'SE_A09005_001':'workers_16_yr_over',\n",
    "        'SE_A09005_002':'tranport_mean_car',\n",
    "        'SE_A09005_003':'tranport_mean_public',\n",
    "        'SE_A09005_005':'tranport_mean_bike',\n",
    "        'SE_A09003_001':'avg_commmute_to_work_min'\n",
    "        }, axis='columns')\n",
    "\n",
    "    # Drop all remaining columns that start with \"SE\"\n",
    "    df = df.loc[:,~df.columns.str.startswith('SE_')]\n",
    "\n",
    "    # Compute the \"population over 25 years and over for education\"\n",
    "    df['educ_less_hs_pct'] = df['educ_less_hs']/df['pop_25_yr_over']\n",
    "    df['educ_hs_pct'] = df['educ_hs']/df['pop_25_yr_over']\n",
    "    df['educ_bs_over_pct'] = df['educ_bs_over']/df['pop_25_yr_over']\n",
    "\n",
    "    # Compute the \"workers over 16 years and over for tranportation mean\"\n",
    "    df['tranport_mean_car_pct'] = df['tranport_mean_car']/df['workers_16_yr_over']\n",
    "    df['tranport_mean_public_pct'] = df['tranport_mean_public']/df['workers_16_yr_over']\n",
    "    df['tranport_mean_bike_pct'] = df['tranport_mean_bike']/df['workers_16_yr_over']\n",
    "\n",
    "    # Drop the columns after we finished the computation\n",
    "    drop_columns = [\n",
    "        'pop_25_yr_over',\n",
    "        'educ_less_hs',\n",
    "        'educ_hs',\n",
    "        'educ_bs_over',\n",
    "        'workers_16_yr_over',\n",
    "        'tranport_mean_car',\n",
    "        'tranport_mean_public',\n",
    "        'tranport_mean_bike'\n",
    "        ]\n",
    "    df = df.drop(columns=drop_columns)\n",
    "\n",
    "    # Add year to column name\n",
    "    for col in df.columns:\n",
    "        if col != 'CT2020_GEOID':\n",
    "            df.rename({col:'ACS'+str(year)+'_'+col}, axis='columns', inplace=True)\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bf093007",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Process and create new dataframe for ACS data\n",
    "df_acs2017_processed = process_acs_data(df_acs2017, 2017)\n",
    "df_acs2018_processed = process_acs_data(df_acs2018, 2018)\n",
    "df_acs2019_processed = process_acs_data(df_acs2019, 2019)\n",
    "df_acs2020_processed = process_acs_data(df_acs2020, 2020)\n",
    "df_acs2021_processed = process_acs_data(df_acs2021, 2021)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "81e408b7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Merge data\n",
    "dfs_to_merge = [df6, df_acs2017_processed, df_acs2018_processed, df_acs2019_processed, df_acs2020_processed, df_acs2021_processed]\n",
    "df7 = reduce(lambda left, right: pd.merge(left, right, how='inner', on='CT2020_GEOID'), dfs_to_merge)\n",
    "df7.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3ab65882",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Combine ACS data depending on the year\n",
    "def combine_ACS_data(row, variable_name):\n",
    "    '''\n",
    "    This function takes in a default argument for the apply function.\n",
    "    The function will combine 5 years of ACS data into 1 and show the data that corresponds to the crash year. \n",
    "    For example, if the crash happens in year 2021, ACS_ttl_pop will be the data from ACS2021_ttl_pop.\n",
    "    '''\n",
    "    if row['CRASH_YEAR'] == 2017:\n",
    "        return row['ACS2017_'+variable_name]\n",
    "    elif row['CRASH_YEAR'] == 2018:\n",
    "        return row['ACS2018_'+variable_name]\n",
    "    elif row['CRASH_YEAR'] == 2019:\n",
    "        return row['ACS2019_'+variable_name]\n",
    "    elif row['CRASH_YEAR'] == 2020:\n",
    "        return row['ACS2020_'+variable_name]\n",
    "    elif row['CRASH_YEAR'] == 2021:\n",
    "        return row['ACS2021_'+variable_name]\n",
    "\n",
    "df7['ACS_ttl_pop'] = df7.apply(combine_ACS_data, variable_name='ttl_pop', axis=1)\n",
    "df7['ACS_pop_density_per_sq_mil'] = df7.apply(combine_ACS_data, variable_name='pop_density_per_sq_mil', axis=1)\n",
    "df7['ACS_educ_less_hs_pct'] = df7.apply(combine_ACS_data, variable_name='educ_less_hs_pct', axis=1)\n",
    "df7['ACS_educ_hs_pct'] = df7.apply(combine_ACS_data, variable_name='educ_less_hs_pct', axis=1)\n",
    "df7['ACS_educ_bs_over_pct'] = df7.apply(combine_ACS_data, variable_name='educ_bs_over_pct', axis=1)\n",
    "df7['ACS_median_household_inc'] = df7.apply(combine_ACS_data, variable_name='median_household_inc', axis=1)\n",
    "df7['ACS_tranport_mean_car_pct'] = df7.apply(combine_ACS_data, variable_name='tranport_mean_car_pct', axis=1)\n",
    "df7['ACS_tranport_mean_public_pct'] = df7.apply(combine_ACS_data, variable_name='tranport_mean_public_pct', axis=1)\n",
    "df7['ACS_tranport_mean_bike_pct'] = df7.apply(combine_ACS_data, variable_name='tranport_mean_bike_pct', axis=1)\n",
    "df7['ACS_avg_commmute_to_work_min'] = df7.apply(combine_ACS_data, variable_name='avg_commmute_to_work_min', axis=1)\n",
    "df7['ACS_borough'] = df7.apply(combine_ACS_data, variable_name='borough', axis=1)\n",
    "\n",
    "# Checking\n",
    "df7[df7['CRASH_YEAR'].notnull()][['CRASH_YEAR','ACS_ttl_pop','ACS_pop_density_per_sq_mil','ACS_educ_less_hs_pct']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a848e0a2",
   "metadata": {},
   "outputs": [],
   "source": [
    "df7.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "81432db8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop unneeded columns\n",
    "drop_columns = [\n",
    "    'ACS2017_ttl_pop', \n",
    "    'ACS2017_pop_density_per_sq_mil', \n",
    "    'ACS2017_educ_less_hs_pct', \n",
    "    'ACS2017_educ_hs_pct', \n",
    "    'ACS2017_educ_bs_over_pct', \n",
    "    'ACS2017_median_household_inc', \n",
    "    'ACS2017_tranport_mean_car_pct', \n",
    "    'ACS2017_tranport_mean_public_pct', \n",
    "    'ACS2017_tranport_mean_bike_pct', \n",
    "    'ACS2017_avg_commmute_to_work_min', \n",
    "    'ACS2017_borough', \n",
    "    'ACS2018_ttl_pop', \n",
    "    'ACS2018_pop_density_per_sq_mil', \n",
    "    'ACS2018_educ_less_hs_pct', \n",
    "    'ACS2018_educ_hs_pct', \n",
    "    'ACS2018_educ_bs_over_pct', \n",
    "    'ACS2018_median_household_inc', \n",
    "    'ACS2018_tranport_mean_car_pct', \n",
    "    'ACS2018_tranport_mean_public_pct', \n",
    "    'ACS2018_tranport_mean_bike_pct', \n",
    "    'ACS2018_avg_commmute_to_work_min', \n",
    "    'ACS2018_borough', \n",
    "    'ACS2019_ttl_pop', \n",
    "    'ACS2019_pop_density_per_sq_mil', \n",
    "    'ACS2019_educ_less_hs_pct', \n",
    "    'ACS2019_educ_hs_pct', \n",
    "    'ACS2019_educ_bs_over_pct', \n",
    "    'ACS2019_median_household_inc', \n",
    "    'ACS2019_tranport_mean_car_pct', \n",
    "    'ACS2019_tranport_mean_public_pct', \n",
    "    'ACS2019_tranport_mean_bike_pct', \n",
    "    'ACS2019_avg_commmute_to_work_min', \n",
    "    'ACS2019_borough', \n",
    "    'ACS2020_ttl_pop', \n",
    "    'ACS2020_pop_density_per_sq_mil', \n",
    "    'ACS2020_educ_less_hs_pct', \n",
    "    'ACS2020_educ_hs_pct', \n",
    "    'ACS2020_educ_bs_over_pct', \n",
    "    'ACS2020_median_household_inc', \n",
    "    'ACS2020_tranport_mean_car_pct', \n",
    "    'ACS2020_tranport_mean_public_pct', \n",
    "    'ACS2020_tranport_mean_bike_pct', \n",
    "    'ACS2020_avg_commmute_to_work_min', \n",
    "    'ACS2020_borough', \n",
    "    'ACS2021_ttl_pop', \n",
    "    'ACS2021_pop_density_per_sq_mil', \n",
    "    'ACS2021_educ_less_hs_pct', \n",
    "    'ACS2021_educ_hs_pct', \n",
    "    'ACS2021_educ_bs_over_pct', \n",
    "    'ACS2021_median_household_inc', \n",
    "    'ACS2021_tranport_mean_car_pct', \n",
    "    'ACS2021_tranport_mean_public_pct', \n",
    "    'ACS2021_tranport_mean_bike_pct', \n",
    "    'ACS2021_avg_commmute_to_work_min', \n",
    "    'ACS2021_borough']\n",
    "df8 = df7.drop(columns=drop_columns)\n",
    "df8.columns"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "ad605a83",
   "metadata": {},
   "source": [
    "### Check how many GEO_ID (ie. census tracts) were lost from the inner join"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a9e203e2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# geo_spatialjoin = list(df3['CT2020_GEOID'].unique())\n",
    "# geo2017 = list(df_acs2017_processed['CT2020_GEOID'].unique())\n",
    "# geo2018 = list(df_acs2018_processed['CT2020_GEOID'].unique())\n",
    "# geo2019 = list(df_acs2019_processed['CT2020_GEOID'].unique())\n",
    "# geo2020 = list(df_acs2020_processed['CT2020_GEOID'].unique())\n",
    "# geo2021 = list(df_acs2021_processed['CT2020_GEOID'].unique())\n",
    "\n",
    "# inter = set(geo_spatialjoin).intersection(geo2017, geo2018, geo2019, geo2020, geo2021)\n",
    "# diff = set(geo_spatialjoin).difference(inter)\n",
    "# len(inter)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "073dd62d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# print(\"unique GEOID values before =\", len(df3['CT2020_GEOID'].unique()))\n",
    "# print(\"unique GEOID values after =\", len(df_final['CT2020_GEOID'].unique()))\n",
    "# print(\"unique zip code values after =\", len(df_final['ZIP_CODE'].unique()))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7bf233d9",
   "metadata": {},
   "source": [
    "# Export data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a13ea6c7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Export data as csv file\n",
    "df8.to_csv('Final Data/final_data.csv',index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.10.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.10.4"
  },
  "vscode": {
   "interpreter": {
    "hash": "26de051ba29f2982a8de78e945f0abaf191376122a1563185a90213a26c5da77"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}