Comparison Engine.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "bff7b6b0",
   "metadata": {},
   "source": [
    "# Data Cleaning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "24a34d48",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-07-11T23:13:27.050028Z",
     "start_time": "2023-07-11T23:13:26.942286Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['notebooks-master',\n",
       " 'ProjectData',\n",
       " 'nyct2020_23a.zip',\n",
       " '.DS_Store',\n",
       " 'Community Districts',\n",
       " 'Synthetic Data.xlsx',\n",
       " 'archive',\n",
       " 'Census Tract Data.ipynb',\n",
       " 'Untitled.ipynb',\n",
       " 'Data.zip',\n",
       " 'Community Districts.zip',\n",
       " 'Part1.ipynb',\n",
       " 'output.csv',\n",
       " '~$Synthetic Data.xlsx',\n",
       " 'nycb2020.shp',\n",
       " 'LIC Data.ipynb',\n",
       " 'Comparison Engine.ipynb',\n",
       " 'LIC Age&Sex_2021_zipcode.xlsx',\n",
       " 'New_code.ipynb',\n",
       " 'notebooks-master.zip',\n",
       " '.ipynb_checkpoints',\n",
       " 'nyct2020_23a',\n",
       " 'Data']"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "import scipy.stats as stats\n",
    "from geopy.geocoders import GoogleV3\n",
    "import geopandas as gpd\n",
    "from shapely.geometry import Point\n",
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\") \n",
    "os.listdir()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "ed29cea9",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-07-03T18:32:53.951531Z",
     "start_time": "2023-07-03T18:32:53.938860Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['cd_name', 'cd_number', 'borough', 'pop_2000', 'pop_2010',\n",
       "       'pop_estimate_acs', 'pop_change_00_10', 'pop_pct_female_under_5',\n",
       "       'pop_pct_female_5_9', 'pop_pct_female_10_14', 'pop_pct_female_15_19',\n",
       "       'pop_pct_female_20_24', 'pop_pct_female_25_29', 'pop_pct_female_30_34',\n",
       "       'pop_pct_female_35_39', 'pop_pct_female_40_44', 'pop_pct_female_45_49',\n",
       "       'pop_pct_female_50_54', 'pop_pct_female_55_59', 'pop_pct_female_60_64',\n",
       "       'pop_pct_female_65_69', 'pop_pct_female_70_74', 'pop_pct_female_75_79',\n",
       "       'pop_pct_female_80_84', 'pop_pct_female_85_over',\n",
       "       'pop_pct_male_under_5', 'pop_pct_male_5_9', 'pop_pct_male_10_14',\n",
       "       'pop_pct_male_15_19', 'pop_pct_male_20_24', 'pop_pct_male_25_29',\n",
       "       'pop_pct_male_30_34', 'pop_pct_male_35_39', 'pop_pct_male_40_44',\n",
       "       'pop_pct_male_45_49', 'pop_pct_male_50_54', 'pop_pct_male_55_59',\n",
       "       'pop_pct_male_60_64', 'pop_pct_male_65_69', 'pop_pct_male_70_74',\n",
       "       'pop_pct_male_75_79', 'pop_pct_male_80_84', 'pop_pct_male_85_over',\n",
       "       'under18_rate', 'moe_under18_rate', 'over65_rate', 'moe_over65_rate'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "age_gender = pd.read_csv(\"Data/Prefer city - cd_demo_age_gender.csv\")\n",
    "age_gender.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "daffac6c",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-07-03T04:07:58.201904Z",
     "start_time": "2023-07-03T04:07:58.175490Z"
    }
   },
   "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>cd_name</th>\n",
       "      <th>cd_number</th>\n",
       "      <th>borough</th>\n",
       "      <th>pop_2000</th>\n",
       "      <th>pop_2010</th>\n",
       "      <th>pop_estimate_acs</th>\n",
       "      <th>pop_change_00_10</th>\n",
       "      <th>pop_pct_female_under_5</th>\n",
       "      <th>pop_pct_female_5_9</th>\n",
       "      <th>pop_pct_female_10_14</th>\n",
       "      <th>...</th>\n",
       "      <th>pop_pct_male_60_64</th>\n",
       "      <th>pop_pct_male_65_69</th>\n",
       "      <th>pop_pct_male_70_74</th>\n",
       "      <th>pop_pct_male_75_79</th>\n",
       "      <th>pop_pct_male_80_84</th>\n",
       "      <th>pop_pct_male_85_over</th>\n",
       "      <th>under18_rate</th>\n",
       "      <th>moe_under18_rate</th>\n",
       "      <th>over65_rate</th>\n",
       "      <th>moe_over65_rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bronx Community District 1</td>\n",
       "      <td>201</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>82159</td>\n",
       "      <td>91497</td>\n",
       "      <td>101542</td>\n",
       "      <td>0.11</td>\n",
       "      <td>3.6</td>\n",
       "      <td>4.4</td>\n",
       "      <td>3.5</td>\n",
       "      <td>...</td>\n",
       "      <td>1.6</td>\n",
       "      <td>0.9</td>\n",
       "      <td>1.1</td>\n",
       "      <td>0.8</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.1</td>\n",
       "      <td>29.7</td>\n",
       "      <td>1.3</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bronx Community District 2</td>\n",
       "      <td>202</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>46824</td>\n",
       "      <td>52246</td>\n",
       "      <td>70144</td>\n",
       "      <td>0.12</td>\n",
       "      <td>3.3</td>\n",
       "      <td>3.5</td>\n",
       "      <td>3.4</td>\n",
       "      <td>...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.1</td>\n",
       "      <td>0.8</td>\n",
       "      <td>0.8</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.3</td>\n",
       "      <td>27.5</td>\n",
       "      <td>1.2</td>\n",
       "      <td>9.2</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Bronx Community District 3</td>\n",
       "      <td>203</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>68574</td>\n",
       "      <td>79762</td>\n",
       "      <td>110290</td>\n",
       "      <td>0.16</td>\n",
       "      <td>4.4</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2.1</td>\n",
       "      <td>1.4</td>\n",
       "      <td>0.8</td>\n",
       "      <td>0.8</td>\n",
       "      <td>0.4</td>\n",
       "      <td>0.2</td>\n",
       "      <td>29.1</td>\n",
       "      <td>1.1</td>\n",
       "      <td>9.2</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Bronx Community District 6</td>\n",
       "      <td>206</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>75688</td>\n",
       "      <td>83268</td>\n",
       "      <td>108893</td>\n",
       "      <td>0.10</td>\n",
       "      <td>4.8</td>\n",
       "      <td>3.5</td>\n",
       "      <td>3.5</td>\n",
       "      <td>...</td>\n",
       "      <td>1.9</td>\n",
       "      <td>1.4</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.9</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.3</td>\n",
       "      <td>28.3</td>\n",
       "      <td>1.2</td>\n",
       "      <td>9.6</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Bronx Community District 4</td>\n",
       "      <td>204</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>139563</td>\n",
       "      <td>146441</td>\n",
       "      <td>161994</td>\n",
       "      <td>0.05</td>\n",
       "      <td>3.5</td>\n",
       "      <td>4.1</td>\n",
       "      <td>4.3</td>\n",
       "      <td>...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.3</td>\n",
       "      <td>0.9</td>\n",
       "      <td>0.6</td>\n",
       "      <td>0.4</td>\n",
       "      <td>0.3</td>\n",
       "      <td>27.7</td>\n",
       "      <td>1.0</td>\n",
       "      <td>9.7</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 47 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                      cd_name  cd_number borough  pop_2000  pop_2010  \\\n",
       "0  Bronx Community District 1        201   Bronx     82159     91497   \n",
       "1  Bronx Community District 2        202   Bronx     46824     52246   \n",
       "2  Bronx Community District 3        203   Bronx     68574     79762   \n",
       "3  Bronx Community District 6        206   Bronx     75688     83268   \n",
       "4  Bronx Community District 4        204   Bronx    139563    146441   \n",
       "\n",
       "   pop_estimate_acs  pop_change_00_10  pop_pct_female_under_5  \\\n",
       "0            101542              0.11                     3.6   \n",
       "1             70144              0.12                     3.3   \n",
       "2            110290              0.16                     4.4   \n",
       "3            108893              0.10                     4.8   \n",
       "4            161994              0.05                     3.5   \n",
       "\n",
       "   pop_pct_female_5_9  pop_pct_female_10_14  ...  pop_pct_male_60_64  \\\n",
       "0                 4.4                   3.5  ...                 1.6   \n",
       "1                 3.5                   3.4  ...                 2.0   \n",
       "2                 4.0                   4.0  ...                 2.1   \n",
       "3                 3.5                   3.5  ...                 1.9   \n",
       "4                 4.1                   4.3  ...                 2.0   \n",
       "\n",
       "   pop_pct_male_65_69  pop_pct_male_70_74  pop_pct_male_75_79  \\\n",
       "0                 0.9                 1.1                 0.8   \n",
       "1                 1.1                 0.8                 0.8   \n",
       "2                 1.4                 0.8                 0.8   \n",
       "3                 1.4                 1.0                 0.9   \n",
       "4                 1.3                 0.9                 0.6   \n",
       "\n",
       "   pop_pct_male_80_84  pop_pct_male_85_over  under18_rate  moe_under18_rate  \\\n",
       "0                 0.3                   0.1          29.7               1.3   \n",
       "1                 0.3                   0.3          27.5               1.2   \n",
       "2                 0.4                   0.2          29.1               1.1   \n",
       "3                 0.3                   0.3          28.3               1.2   \n",
       "4                 0.4                   0.3          27.7               1.0   \n",
       "\n",
       "   over65_rate  moe_over65_rate  \n",
       "0          9.0              0.5  \n",
       "1          9.2              0.6  \n",
       "2          9.2              0.5  \n",
       "3          9.6              0.5  \n",
       "4          9.7              0.5  \n",
       "\n",
       "[5 rows x 47 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "age_gender.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "a847d4bd",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-07-11T23:13:55.115470Z",
     "start_time": "2023-07-11T23:13:54.945967Z"
    },
    "id": "a847d4bd",
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "population_data=pd.read_csv(\"Data/Prefer city - cd_demo_race_economics.csv\")\n",
    "sample_input=pd.read_csv(\"Data/Synthetic Data Latest - Synthetic Data.csv\", skiprows=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "ee38c85f",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-07-11T23:13:55.260094Z",
     "start_time": "2023-07-11T23:13:55.248826Z"
    },
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 423
    },
    "id": "ee38c85f",
    "outputId": "b00b664c-54e2-47c2-db5b-9172a964d6be"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['ID', 'Age', 'Annual Income', 'Gender', 'Ethnicity', 'CrossStreet1',\n",
       "       'CrossStreet2', 'ZipCode'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample_input.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "b4839090",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-07-12T13:31:32.252092Z",
     "start_time": "2023-07-12T13:31:32.225223Z"
    }
   },
   "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>cd_name</th>\n",
       "      <th>cd_number</th>\n",
       "      <th>borough</th>\n",
       "      <th>pct_foreign_born</th>\n",
       "      <th>moe_foreign_born</th>\n",
       "      <th>lep_rate</th>\n",
       "      <th>moe_lep_rate</th>\n",
       "      <th>pct_white_nh</th>\n",
       "      <th>pct_black_nh</th>\n",
       "      <th>pct_asian_nh</th>\n",
       "      <th>...</th>\n",
       "      <th>pct_hh_rent_burd</th>\n",
       "      <th>moe_hh_rent_burd</th>\n",
       "      <th>pct_bach_deg</th>\n",
       "      <th>moe_bach_deg</th>\n",
       "      <th>unemployment</th>\n",
       "      <th>moe_unemployment</th>\n",
       "      <th>mean_commute</th>\n",
       "      <th>moe_mean_commute</th>\n",
       "      <th>crime_count</th>\n",
       "      <th>crime_per_1000</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bronx Community District 1</td>\n",
       "      <td>201</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>29.3</td>\n",
       "      <td>1.6</td>\n",
       "      <td>29.5</td>\n",
       "      <td>1.6</td>\n",
       "      <td>2.1</td>\n",
       "      <td>28.8</td>\n",
       "      <td>0.5</td>\n",
       "      <td>...</td>\n",
       "      <td>49.4</td>\n",
       "      <td>2.1</td>\n",
       "      <td>11.3</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6.4</td>\n",
       "      <td>0.8</td>\n",
       "      <td>42.5</td>\n",
       "      <td>2.5</td>\n",
       "      <td>2205</td>\n",
       "      <td>21.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bronx Community District 2</td>\n",
       "      <td>202</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>34.6</td>\n",
       "      <td>1.8</td>\n",
       "      <td>30.6</td>\n",
       "      <td>1.8</td>\n",
       "      <td>1.9</td>\n",
       "      <td>28.4</td>\n",
       "      <td>1.1</td>\n",
       "      <td>...</td>\n",
       "      <td>53.8</td>\n",
       "      <td>2.5</td>\n",
       "      <td>14.2</td>\n",
       "      <td>1.2</td>\n",
       "      <td>6.4</td>\n",
       "      <td>0.8</td>\n",
       "      <td>44.9</td>\n",
       "      <td>2.9</td>\n",
       "      <td>864</td>\n",
       "      <td>12.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Bronx Community District 3</td>\n",
       "      <td>203</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>31.7</td>\n",
       "      <td>1.3</td>\n",
       "      <td>25.9</td>\n",
       "      <td>1.3</td>\n",
       "      <td>1.2</td>\n",
       "      <td>35.8</td>\n",
       "      <td>0.8</td>\n",
       "      <td>...</td>\n",
       "      <td>48.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>14.1</td>\n",
       "      <td>1.1</td>\n",
       "      <td>6.6</td>\n",
       "      <td>0.7</td>\n",
       "      <td>43.9</td>\n",
       "      <td>2.6</td>\n",
       "      <td>1495</td>\n",
       "      <td>13.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Bronx Community District 6</td>\n",
       "      <td>206</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>32.6</td>\n",
       "      <td>1.5</td>\n",
       "      <td>27.5</td>\n",
       "      <td>1.5</td>\n",
       "      <td>6.1</td>\n",
       "      <td>27.0</td>\n",
       "      <td>1.3</td>\n",
       "      <td>...</td>\n",
       "      <td>55.1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>12.6</td>\n",
       "      <td>1.1</td>\n",
       "      <td>8.4</td>\n",
       "      <td>0.8</td>\n",
       "      <td>44.4</td>\n",
       "      <td>2.4</td>\n",
       "      <td>1312</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Bronx Community District 4</td>\n",
       "      <td>204</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>42.7</td>\n",
       "      <td>1.3</td>\n",
       "      <td>35.8</td>\n",
       "      <td>1.4</td>\n",
       "      <td>1.9</td>\n",
       "      <td>28.7</td>\n",
       "      <td>1.3</td>\n",
       "      <td>...</td>\n",
       "      <td>54.0</td>\n",
       "      <td>1.7</td>\n",
       "      <td>15.8</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6.4</td>\n",
       "      <td>0.6</td>\n",
       "      <td>41.9</td>\n",
       "      <td>1.8</td>\n",
       "      <td>2359</td>\n",
       "      <td>14.6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                      cd_name  cd_number borough  pct_foreign_born  \\\n",
       "0  Bronx Community District 1        201   Bronx              29.3   \n",
       "1  Bronx Community District 2        202   Bronx              34.6   \n",
       "2  Bronx Community District 3        203   Bronx              31.7   \n",
       "3  Bronx Community District 6        206   Bronx              32.6   \n",
       "4  Bronx Community District 4        204   Bronx              42.7   \n",
       "\n",
       "   moe_foreign_born  lep_rate  moe_lep_rate  pct_white_nh  pct_black_nh  \\\n",
       "0               1.6      29.5           1.6           2.1          28.8   \n",
       "1               1.8      30.6           1.8           1.9          28.4   \n",
       "2               1.3      25.9           1.3           1.2          35.8   \n",
       "3               1.5      27.5           1.5           6.1          27.0   \n",
       "4               1.3      35.8           1.4           1.9          28.7   \n",
       "\n",
       "   pct_asian_nh  ...  pct_hh_rent_burd  moe_hh_rent_burd  pct_bach_deg  \\\n",
       "0           0.5  ...              49.4               2.1          11.3   \n",
       "1           1.1  ...              53.8               2.5          14.2   \n",
       "2           0.8  ...              48.0               2.0          14.1   \n",
       "3           1.3  ...              55.1               2.0          12.6   \n",
       "4           1.3  ...              54.0               1.7          15.8   \n",
       "\n",
       "   moe_bach_deg  unemployment  moe_unemployment  mean_commute  \\\n",
       "0           1.0           6.4               0.8          42.5   \n",
       "1           1.2           6.4               0.8          44.9   \n",
       "2           1.1           6.6               0.7          43.9   \n",
       "3           1.1           8.4               0.8          44.4   \n",
       "4           1.0           6.4               0.6          41.9   \n",
       "\n",
       "   moe_mean_commute  crime_count  crime_per_1000  \n",
       "0               2.5         2205            21.7  \n",
       "1               2.9          864            12.3  \n",
       "2               2.6         1495            13.6  \n",
       "3               2.4         1312            12.0  \n",
       "4               1.8         2359            14.6  \n",
       "\n",
       "[5 rows x 24 columns]"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "population_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "d35df669",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-07-11T23:13:57.466012Z",
     "start_time": "2023-07-11T23:13:57.421769Z"
    },
    "id": "d35df669"
   },
   "outputs": [],
   "source": [
    "#change datatype from Object to category\n",
    "sample_input.dropna(subset=['ID'], inplace=True)\n",
    "sample_input.drop(sample_input.index[-1], inplace=True)\n",
    "sample_input['ID'] = sample_input['ID'].astype(int)\n",
    "sample_input['ZipCode'] = sample_input['ZipCode'].astype(int)\n",
    "sample_input['Age'] = sample_input['Age'].astype('category')\n",
    "sample_input['Annual Income'] = sample_input['Annual Income'].astype('category')\n",
    "sample_input['Ethnicity'] = sample_input['Ethnicity'].astype('category')\n",
    "sample_input['CrossStreet1'] = sample_input['CrossStreet1'].astype('category')\n",
    "sample_input['CrossStreet2'] = sample_input['CrossStreet2'].astype('category')\n",
    "# sample_input['Feedback Option'] = sample_input['Feedback Option'].astype('category')\n",
    "# sample_input['Feedback Details'] = sample_input['Feedback Details'].astype('object')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "43e0be78",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-07-11T23:13:58.269268Z",
     "start_time": "2023-07-11T23:13:58.240016Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>Age</th>\n",
       "      <th>Annual Income</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Ethnicity</th>\n",
       "      <th>CrossStreet1</th>\n",
       "      <th>CrossStreet2</th>\n",
       "      <th>ZipCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Under 5</td>\n",
       "      <td>20000-30000</td>\n",
       "      <td>Male</td>\n",
       "      <td>White</td>\n",
       "      <td>Austin St</td>\n",
       "      <td>72nd Rd</td>\n",
       "      <td>11375</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>5-9</td>\n",
       "      <td>50000-60000</td>\n",
       "      <td>Female</td>\n",
       "      <td>Black</td>\n",
       "      <td>Broadway</td>\n",
       "      <td>Houston Street</td>\n",
       "      <td>10012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>10-14</td>\n",
       "      <td>30000-40000</td>\n",
       "      <td>Male</td>\n",
       "      <td>Hispanic</td>\n",
       "      <td>Lexington Avenue</td>\n",
       "      <td>42nd St</td>\n",
       "      <td>10017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>15-19</td>\n",
       "      <td>20000-30000</td>\n",
       "      <td>Male</td>\n",
       "      <td>Asian</td>\n",
       "      <td>5th Avenue</td>\n",
       "      <td>23rd St</td>\n",
       "      <td>10010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>20-24</td>\n",
       "      <td>80000-90000</td>\n",
       "      <td>Female</td>\n",
       "      <td>White</td>\n",
       "      <td>Park Avenue</td>\n",
       "      <td>57th St</td>\n",
       "      <td>10022</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>148</th>\n",
       "      <td>149</td>\n",
       "      <td>20-24</td>\n",
       "      <td>30000-40000</td>\n",
       "      <td>Female</td>\n",
       "      <td>Asian</td>\n",
       "      <td>Northern Blvd</td>\n",
       "      <td>82nd St</td>\n",
       "      <td>11372</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149</th>\n",
       "      <td>150</td>\n",
       "      <td>25-29</td>\n",
       "      <td>40000-50000</td>\n",
       "      <td>Male</td>\n",
       "      <td>African American</td>\n",
       "      <td>Astoria Blvd</td>\n",
       "      <td>82nd St</td>\n",
       "      <td>11370</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>150</th>\n",
       "      <td>151</td>\n",
       "      <td>30-34</td>\n",
       "      <td>20000-30000</td>\n",
       "      <td>Female</td>\n",
       "      <td>Hispanic or Latino</td>\n",
       "      <td>Woodside Ave</td>\n",
       "      <td>61st St</td>\n",
       "      <td>11377</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>151</th>\n",
       "      <td>152</td>\n",
       "      <td>35-39</td>\n",
       "      <td>60000-70000</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>White</td>\n",
       "      <td>Grand Ave</td>\n",
       "      <td>Queens Blvd</td>\n",
       "      <td>11378</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>152</th>\n",
       "      <td>153</td>\n",
       "      <td>40-44</td>\n",
       "      <td>30000-40000</td>\n",
       "      <td>Male</td>\n",
       "      <td>Asian</td>\n",
       "      <td>Roosevelt Ave</td>\n",
       "      <td>74th St</td>\n",
       "      <td>11372</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>153 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      ID      Age Annual Income   Gender           Ethnicity  \\\n",
       "0      1  Under 5   20000-30000     Male               White   \n",
       "1      2      5-9   50000-60000   Female               Black   \n",
       "2      3    10-14   30000-40000     Male            Hispanic   \n",
       "3      4    15-19   20000-30000     Male               Asian   \n",
       "4      5    20-24   80000-90000   Female               White   \n",
       "..   ...      ...           ...      ...                 ...   \n",
       "148  149    20-24   30000-40000   Female               Asian   \n",
       "149  150    25-29   40000-50000     Male    African American   \n",
       "150  151    30-34   20000-30000   Female  Hispanic or Latino   \n",
       "151  152    35-39   60000-70000  Unknown               White   \n",
       "152  153    40-44   30000-40000     Male               Asian   \n",
       "\n",
       "         CrossStreet1    CrossStreet2  ZipCode  \n",
       "0           Austin St         72nd Rd    11375  \n",
       "1            Broadway  Houston Street    10012  \n",
       "2    Lexington Avenue         42nd St    10017  \n",
       "3          5th Avenue         23rd St    10010  \n",
       "4         Park Avenue         57th St    10022  \n",
       "..                ...             ...      ...  \n",
       "148     Northern Blvd         82nd St    11372  \n",
       "149      Astoria Blvd         82nd St    11370  \n",
       "150      Woodside Ave         61st St    11377  \n",
       "151         Grand Ave     Queens Blvd    11378  \n",
       "152     Roosevelt Ave         74th St    11372  \n",
       "\n",
       "[153 rows x 8 columns]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample_input"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "907d3f26",
   "metadata": {},
   "source": [
    "### Use Google Map API to extract the community district number"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "3bb5f283",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-07-03T18:35:09.010215Z",
     "start_time": "2023-07-03T18:35:09.000959Z"
    }
   },
   "outputs": [],
   "source": [
    "def get_community_district(street1, street2):\n",
    "    # Geocode the intersection\n",
    "    geolocator = GoogleV3(api_key='AIzaSyDfPS9UWjKvOH7mJC8aeUMZOkfwI9gq1B4')  # Replace with your Google Map API key\n",
    "    location = geolocator.geocode(f\"{street1} and {street2}\")\n",
    "\n",
    "    if location is not None:\n",
    "        # Load the community district shapefile data\n",
    "        shapefile_path = 'Community Districts/geo_export_aa538420-175f-425e-8f95-754156ef82a9.shp'\n",
    "        districts = gpd.read_file(shapefile_path)\n",
    "        # Create a GeoDataFrame with the geocoded point\n",
    "        point = Point(location.longitude, location.latitude)\n",
    "        gdf_point = gpd.GeoDataFrame(geometry=[point])\n",
    "        # Find the community district containing the geocoded point\n",
    "        containing_districts = gpd.sjoin(gdf_point, districts, op='within')\n",
    "        if len(containing_districts) > 0:\n",
    "            # Return the first community district found\n",
    "            return containing_districts.iloc[0]['boro_cd']\n",
    "\n",
    "    return None\n",
    "\n",
    "\n",
    "def find_cd_name(cd_number):\n",
    "    filtered_df = population_data.loc[population_data['cd_number'] == cd_number, 'cd_name']\n",
    "    if filtered_df.empty:\n",
    "        return None\n",
    "    else:\n",
    "        return filtered_df.values[0]\n",
    "        \n",
    "    return cd_name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "7ab9283b",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-07-11T23:14:47.894373Z",
     "start_time": "2023-07-11T23:14:47.577368Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The intersection of Austin St and 72nd Rd belongs to Community District 406.0\n",
      "Queens Community District 6\n"
     ]
    }
   ],
   "source": [
    "# Example for get_community_district function\n",
    "street1 = \"Austin St\"\n",
    "street2 = \"72nd Rd\"\n",
    "district = get_community_district(street1, street2)\n",
    "if district is not None:\n",
    "    print(f\"The intersection of {street1} and {street2} belongs to Community District {district}\")\n",
    "else:\n",
    "    print(\"No community district found for the intersection.\")\n",
    "#The intersection of Austin St and 72nd Rd belongs to Community District 406.0\n",
    "# Example for find_cd_name function\n",
    "cd_number = 201\n",
    "cd_name = find_cd_name(district)\n",
    "print(cd_name)\n",
    "#Queens Community District 6"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "90e9a6ba",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-07-11T23:16:04.864233Z",
     "start_time": "2023-07-11T23:15:26.703979Z"
    }
   },
   "outputs": [],
   "source": [
    "# Extract community district number and name for each sample\n",
    "sample_input['CommunityDistrict'] = sample_input.apply(lambda row: get_community_district(row['CrossStreet1'], row['CrossStreet2']), axis=1)\n",
    "sample_input['CD_Name'] = sample_input['CommunityDistrict'].apply(find_cd_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "0fd81cba",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-07-12T13:32:07.680090Z",
     "start_time": "2023-07-12T13:32:07.668354Z"
    }
   },
   "outputs": [],
   "source": [
    "# #Export to a csv file\n",
    "\n",
    "# sample_input.to_csv('output.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "f854dfe0",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-07-11T23:16:04.886844Z",
     "start_time": "2023-07-11T23:16:04.870319Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>Age</th>\n",
       "      <th>Annual Income</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Ethnicity</th>\n",
       "      <th>CrossStreet1</th>\n",
       "      <th>CrossStreet2</th>\n",
       "      <th>ZipCode</th>\n",
       "      <th>CommunityDistrict</th>\n",
       "      <th>CD_Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Under 5</td>\n",
       "      <td>20000-30000</td>\n",
       "      <td>Male</td>\n",
       "      <td>White</td>\n",
       "      <td>Austin St</td>\n",
       "      <td>72nd Rd</td>\n",
       "      <td>11375</td>\n",
       "      <td>406.0</td>\n",
       "      <td>Queens Community District 6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>5-9</td>\n",
       "      <td>50000-60000</td>\n",
       "      <td>Female</td>\n",
       "      <td>Black</td>\n",
       "      <td>Broadway</td>\n",
       "      <td>Houston Street</td>\n",
       "      <td>10012</td>\n",
       "      <td>102.0</td>\n",
       "      <td>Manhattan Community District 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>10-14</td>\n",
       "      <td>30000-40000</td>\n",
       "      <td>Male</td>\n",
       "      <td>Hispanic</td>\n",
       "      <td>Lexington Avenue</td>\n",
       "      <td>42nd St</td>\n",
       "      <td>10017</td>\n",
       "      <td>108.0</td>\n",
       "      <td>Manhattan Community District 8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>15-19</td>\n",
       "      <td>20000-30000</td>\n",
       "      <td>Male</td>\n",
       "      <td>Asian</td>\n",
       "      <td>5th Avenue</td>\n",
       "      <td>23rd St</td>\n",
       "      <td>10010</td>\n",
       "      <td>105.0</td>\n",
       "      <td>Manhattan Community District 5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>20-24</td>\n",
       "      <td>80000-90000</td>\n",
       "      <td>Female</td>\n",
       "      <td>White</td>\n",
       "      <td>Park Avenue</td>\n",
       "      <td>57th St</td>\n",
       "      <td>10022</td>\n",
       "      <td>105.0</td>\n",
       "      <td>Manhattan Community District 5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>148</th>\n",
       "      <td>149</td>\n",
       "      <td>20-24</td>\n",
       "      <td>30000-40000</td>\n",
       "      <td>Female</td>\n",
       "      <td>Asian</td>\n",
       "      <td>Northern Blvd</td>\n",
       "      <td>82nd St</td>\n",
       "      <td>11372</td>\n",
       "      <td>403.0</td>\n",
       "      <td>Queens Community District 3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149</th>\n",
       "      <td>150</td>\n",
       "      <td>25-29</td>\n",
       "      <td>40000-50000</td>\n",
       "      <td>Male</td>\n",
       "      <td>African American</td>\n",
       "      <td>Astoria Blvd</td>\n",
       "      <td>82nd St</td>\n",
       "      <td>11370</td>\n",
       "      <td>403.0</td>\n",
       "      <td>Queens Community District 3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>150</th>\n",
       "      <td>151</td>\n",
       "      <td>30-34</td>\n",
       "      <td>20000-30000</td>\n",
       "      <td>Female</td>\n",
       "      <td>Hispanic or Latino</td>\n",
       "      <td>Woodside Ave</td>\n",
       "      <td>61st St</td>\n",
       "      <td>11377</td>\n",
       "      <td>402.0</td>\n",
       "      <td>Queens Community District 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>151</th>\n",
       "      <td>152</td>\n",
       "      <td>35-39</td>\n",
       "      <td>60000-70000</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>White</td>\n",
       "      <td>Grand Ave</td>\n",
       "      <td>Queens Blvd</td>\n",
       "      <td>11378</td>\n",
       "      <td>404.0</td>\n",
       "      <td>Queens Community District 4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>152</th>\n",
       "      <td>153</td>\n",
       "      <td>40-44</td>\n",
       "      <td>30000-40000</td>\n",
       "      <td>Male</td>\n",
       "      <td>Asian</td>\n",
       "      <td>Roosevelt Ave</td>\n",
       "      <td>74th St</td>\n",
       "      <td>11372</td>\n",
       "      <td>404.0</td>\n",
       "      <td>Queens Community District 4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>153 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      ID      Age Annual Income   Gender           Ethnicity  \\\n",
       "0      1  Under 5   20000-30000     Male               White   \n",
       "1      2      5-9   50000-60000   Female               Black   \n",
       "2      3    10-14   30000-40000     Male            Hispanic   \n",
       "3      4    15-19   20000-30000     Male               Asian   \n",
       "4      5    20-24   80000-90000   Female               White   \n",
       "..   ...      ...           ...      ...                 ...   \n",
       "148  149    20-24   30000-40000   Female               Asian   \n",
       "149  150    25-29   40000-50000     Male    African American   \n",
       "150  151    30-34   20000-30000   Female  Hispanic or Latino   \n",
       "151  152    35-39   60000-70000  Unknown               White   \n",
       "152  153    40-44   30000-40000     Male               Asian   \n",
       "\n",
       "         CrossStreet1    CrossStreet2  ZipCode  CommunityDistrict  \\\n",
       "0           Austin St         72nd Rd    11375              406.0   \n",
       "1            Broadway  Houston Street    10012              102.0   \n",
       "2    Lexington Avenue         42nd St    10017              108.0   \n",
       "3          5th Avenue         23rd St    10010              105.0   \n",
       "4         Park Avenue         57th St    10022              105.0   \n",
       "..                ...             ...      ...                ...   \n",
       "148     Northern Blvd         82nd St    11372              403.0   \n",
       "149      Astoria Blvd         82nd St    11370              403.0   \n",
       "150      Woodside Ave         61st St    11377              402.0   \n",
       "151         Grand Ave     Queens Blvd    11378              404.0   \n",
       "152     Roosevelt Ave         74th St    11372              404.0   \n",
       "\n",
       "                            CD_Name  \n",
       "0       Queens Community District 6  \n",
       "1    Manhattan Community District 2  \n",
       "2    Manhattan Community District 8  \n",
       "3    Manhattan Community District 5  \n",
       "4    Manhattan Community District 5  \n",
       "..                              ...  \n",
       "148     Queens Community District 3  \n",
       "149     Queens Community District 3  \n",
       "150     Queens Community District 2  \n",
       "151     Queens Community District 4  \n",
       "152     Queens Community District 4  \n",
       "\n",
       "[153 rows x 10 columns]"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample_input"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "43626028",
   "metadata": {},
   "source": [
    "# Comparsion Engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "6d926e95",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-07-03T18:36:53.446154Z",
     "start_time": "2023-07-03T18:36:53.421914Z"
    }
   },
   "outputs": [],
   "source": [
    "def comparsion_engine(sample_df, population_df_1, population_df_2,community_district, demographic_feature, alpha):\n",
    "#sample_df is our sample input data\n",
    "#community districts can be Queens Community District 1, Manhattan Community District 2, and etc..\n",
    "#demographic features are only limited to \"Ethnicity, Gender, and Age\"\n",
    "#alpha is significance level\n",
    "    filtered_sample_data = sample_df[sample_df['CD_Name'] == community_district]\n",
    "    filtered_pop_data = population_df_1[population_df_1['cd_name'] == community_district]\n",
    "    filtered_age_data = population_df_2[population_df_2['cd_name'] == community_district]\n",
    "    if demographic_feature == \"Ethnicity\":\n",
    "    \n",
    "        race_proportions = filtered_sample_data['Ethnicity'].value_counts(normalize=True)\n",
    "        race_N = filtered_sample_data.shape[0]\n",
    "        \n",
    "        poplution_df=filtered_pop_data[['pct_white_nh','pct_black_nh','pct_asian_nh','pct_other_nh','pct_hispanic']]\n",
    "        population_race_pop  = (poplution_df.iloc[0]/100).to_dict()\n",
    "        \n",
    "        race_pop = {}\n",
    "        race_pop['pct_white_nh'] = race_proportions['White']\n",
    "        race_pop['pct_black_nh'] = race_proportions['Black']\n",
    "        race_pop['pct_asian_nh'] = race_proportions['Asian']\n",
    "        race_pop['pct_hispanic'] = race_proportions['Hispanic']\n",
    "        race_pop['pct_other_nh'] = 1 - sum(race_pop.values())\n",
    "        \n",
    "        for race in race_pop.keys():\n",
    "            print(f\"For race {race.replace('pct_', '')}:\")\n",
    "\n",
    "\n",
    "            # Define hypotheses\n",
    "            print(\"sample proportion is \", round(race_pop[race], 4), \n",
    "                  \", population proportion is \", round(population_race_pop[race], 4))\n",
    "            if race_pop[race] > population_race_pop[race]:\n",
    "                print(f\"H0: The sample proportion is equal to the population proportion\")\n",
    "                print(f\"H1: The sample proportion is greater than the population proportion\")\n",
    "            else:\n",
    "                print(f\"H0: The sample proportion is equal to the population proportion\")\n",
    "                print(f\"H1: The sample proportion is less than the population proportion\")\n",
    "\n",
    "            # Calculate test statistic\n",
    "            z = (race_pop[race] - population_race_pop[race]) / ((population_race_pop[race] * (1 - population_race_pop[race]) / race_N) ** 0.5)\n",
    "            print(f\"Test statistic (Z): {z}\")\n",
    "\n",
    "            if race_pop[race] > population_race_pop[race]:\n",
    "                # Calculate p-value for the test that sample proportion > population proportion\n",
    "                p_value = 1 - stats.norm.cdf(abs(z))  # One-tailed test\n",
    "            else:\n",
    "                # Calculate p-value for the test that sample proportion < population proportion\n",
    "                p_value = stats.norm.cdf(-abs(z))  # One-tailed test\n",
    "\n",
    "            print(f\"P-value: {p_value}\")\n",
    "\n",
    "            # Conclusion\n",
    "            if p_value < alpha:\n",
    "                if race_pop[race] > population_race_pop[race]:\n",
    "                    print(f\"Conclusion: We reject the null hypothesis. The sample proportion is significantly larger than the population proportion.\\n\")\n",
    "                else:\n",
    "                    print(f\"Conclusion: We reject the null hypothesis. The sample proportion is significantly smaller than the population proportion.\\n\")\n",
    "            else:\n",
    "                print(f\"Conclusion: We fail to reject the null hypothesis. The sample proportion is not significantly different from the population proportion.\\n\")\n",
    "    elif demographic_feature == \"Age\":\n",
    "        age_groups = ['Under 5', '5-9', '10-14', '15-19', '20-24', '25-29', '30-34', '35-39', '40-44', '45-49', '50-54', '55-59', '60-64', '65-69', '70-74', '75-79', '80-84', '85+']\n",
    "\n",
    "        # Create a new dataframe to store the counts\n",
    "        age_group_counts = pd.DataFrame(columns=['Age Group', 'Count'])\n",
    "\n",
    "        # Count the occurrences of each age group\n",
    "        for age_group in age_groups:\n",
    "            count = filtered_sample_data['Age'].str.contains(age_group).sum()\n",
    "            percentage = count / len(filtered_sample_data) * 100\n",
    "            age_group_counts = age_group_counts.append({'Age Group': age_group, 'Count': count, 'Percentage': percentage}, ignore_index=True)\n",
    "     \n",
    "        age_groups = ['under_5', '5_9', '10_14', '15_19', '20_24', '25_29', '30_34', '35_39', '40_44', '45_49', '50_54', '55_59', '60_64', '65_69', '70_74', '75_79', '80_84', '85_over']\n",
    "        # Create a new dataframe to store the summed values\n",
    "        summed_age_groups = pd.DataFrame(columns=['Age Group', 'Sum'])\n",
    "\n",
    "        # Iterate over the age groups and sum the corresponding columns\n",
    "        for age_group in age_groups:\n",
    "            female_col = f'pop_pct_female_{age_group}'\n",
    "            male_col = f'pop_pct_male_{age_group}'\n",
    "            total_sum = filtered_age_data[female_col].sum() + filtered_age_data[male_col].sum()\n",
    "            summed_age_groups = summed_age_groups.append({'Age Group': age_group.replace('_', '-'), 'Sum': total_sum}, ignore_index=True)\n",
    "        for i in range(len(age_group_counts)):\n",
    "            age_group = age_group_counts.loc[i, 'Age Group']\n",
    "            sample_pct = age_group_counts.loc[i, 'Percentage'] / 100\n",
    "            pop_pct = summed_age_groups.loc[i, 'Sum'] / summed_age_groups['Sum'].sum()\n",
    "\n",
    "            print(f\"For {age_group}:\")\n",
    "            print(f\"Sample proportion: {sample_pct}\")\n",
    "            print(f\"Population proportion: {pop_pct}\")\n",
    "            print(f\"H0: The sample proportion is equal to the population proportion\")\n",
    "            print(f\"H1: The sample proportion is greater than the population proportion\")\n",
    "\n",
    "            # Calculate test statistic\n",
    "            z = (sample_pct - pop_pct) / ((pop_pct * (1 - pop_pct) / age_group_counts.shape[0]) ** 0.5)\n",
    "            print(f\"Test statistic (Z): {z}\")\n",
    "\n",
    "            # Calculate p-value\n",
    "            p_value = 1 - stats.norm.cdf(z)  # One-tailed test (greater than)\n",
    "            print(f\"P-value: {p_value}\")\n",
    "\n",
    "            # Conclusion\n",
    "            if p_value < alpha:\n",
    "                print(\"Conclusion: We reject the null hypothesis. The sample proportion is significantly greater than the population proportion.\")\n",
    "            else:\n",
    "                print(\"Conclusion: We fail to reject the null hypothesis. The sample proportion is not significantly greater than the population proportion.\")\n",
    "\n",
    "            # Add an extra line break\n",
    "            print()\n",
    "    elif demographic_feature == \"Gender\":\n",
    "        male_columns = ['pop_pct_male_under_5', 'pop_pct_male_5_9', 'pop_pct_male_10_14',\n",
    "       'pop_pct_male_15_19', 'pop_pct_male_20_24', 'pop_pct_male_25_29',\n",
    "       'pop_pct_male_30_34', 'pop_pct_male_35_39', 'pop_pct_male_40_44',\n",
    "       'pop_pct_male_45_49', 'pop_pct_male_50_54', 'pop_pct_male_55_59',\n",
    "       'pop_pct_male_60_64', 'pop_pct_male_65_69', 'pop_pct_male_70_74',\n",
    "       'pop_pct_male_75_79', 'pop_pct_male_80_84', 'pop_pct_male_85_over']  # Add all columns containing male data\n",
    "        female_columns = ['pop_pct_female_under_5',\n",
    "               'pop_pct_female_5_9', 'pop_pct_female_10_14', 'pop_pct_female_15_19',\n",
    "               'pop_pct_female_20_24', 'pop_pct_female_25_29', 'pop_pct_female_30_34',\n",
    "               'pop_pct_female_35_39', 'pop_pct_female_40_44', 'pop_pct_female_45_49',\n",
    "               'pop_pct_female_50_54', 'pop_pct_female_55_59', 'pop_pct_female_60_64',\n",
    "               'pop_pct_female_65_69', 'pop_pct_female_70_74', 'pop_pct_female_75_79',\n",
    "               'pop_pct_female_80_84', 'pop_pct_female_85_over']  # Add all columns containing female data\n",
    "\n",
    "        male_percentage = filtered_age_data[male_columns].sum(axis=1)\n",
    "        female_percentage = filtered_age_data[female_columns].sum(axis=1)\n",
    "        sample_pct_female = filtered_sample_data[filtered_sample_data['Gender'] == 'Female'].shape[0] / filtered_sample_data.shape[0]\n",
    "        sample_pct_male = filtered_sample_data[filtered_sample_data['Gender'] == 'Male'].shape[0] / filtered_sample_data.shape[0]\n",
    "        sample_pct_gender  =  sample_pct_female + sample_pct_male\n",
    "        sample_pct_female = sample_pct_female / sample_pct_gender\n",
    "        sample_pct_male =  sample_pct_male / sample_pct_gender\n",
    "        # Perform the test for each gender\n",
    "        for gender, sample_pct, pop_pct in [('Female', sample_pct_female, female_percentage.values[0]/100), ('Male', sample_pct_male, male_percentage.values[0]/100)]:\n",
    "            print(f\"For {gender}:\")\n",
    "            print(f\"Sample proportion: {sample_pct}\")\n",
    "            print(f\"Population proportion: {pop_pct}\")\n",
    "            print(f\"H0: The sample proportion is equal to the population proportion\")\n",
    "            print(f\"H1: The sample proportion is not equal to the population proportion\")\n",
    "\n",
    "            # Calculate test statistic\n",
    "            z = (sample_pct - pop_pct) / ((pop_pct * (1 - pop_pct) / filtered_sample_data.shape[0]) ** 0.5)\n",
    "            print(f\"Test statistic (Z): {z}\")\n",
    "\n",
    "            # Calculate p-value\n",
    "            p_value = 2 * stats.norm.cdf(-abs(z))  # Two-tailed test\n",
    "            print(f\"P-value: {p_value}\")\n",
    "\n",
    "            # Conclusion\n",
    "            if p_value < alpha:\n",
    "                print(\"Conclusion: We reject the null hypothesis. The sample proportion is significantly different from the population proportion.\\n\")\n",
    "            else:\n",
    "                print(\"Conclusion: We fail to reject the null hypothesis. The sample proportion is not significantly different from the population proportion.\\n\")\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c7d35861",
   "metadata": {},
   "outputs": [],
   "source": [
    "#If we reject the null hypothesis, it indicates that our survey is not representative in certain groups, \n",
    "#such as different age groups, race groups, or gender groups. In other words, \n",
    "#the survey results may not accurately reflect the opinions or characteristics of these specific groups. \n",
    "#On the other hand, if we fail to reject the null hypothesis, \n",
    "#it suggests that the survey we conducted is well distributed across these groups. \n",
    "#This means that the survey results are more likely to be representative and statistically significant, \n",
    "#indicating that any observed patterns or relationships in the data are not due to chance alone."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "0567c70d",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-07-11T23:30:53.413480Z",
     "start_time": "2023-07-11T23:30:53.394097Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "For race white_nh:\n",
      "sample proportion is  0.2857 , population proportion is  0.267\n",
      "H0: The sample proportion is equal to the population proportion\n",
      "H1: The sample proportion is greater than the population proportion\n",
      "Test statistic (Z): 0.1582813170968757\n",
      "P-value: 0.43711756467778107\n",
      "Conclusion: We fail to reject the null hypothesis. The sample proportion is not significantly different from the population proportion.\n",
      "\n",
      "For race black_nh:\n",
      "sample proportion is  0.0714 , population proportion is  0.038\n",
      "H0: The sample proportion is equal to the population proportion\n",
      "H1: The sample proportion is greater than the population proportion\n",
      "Test statistic (Z): 0.6541878907338846\n",
      "P-value: 0.25649538106882985\n",
      "Conclusion: We fail to reject the null hypothesis. The sample proportion is not significantly different from the population proportion.\n",
      "\n",
      "For race asian_nh:\n",
      "sample proportion is  0.2143 , population proportion is  0.355\n",
      "H0: The sample proportion is equal to the population proportion\n",
      "H1: The sample proportion is less than the population proportion\n",
      "Test statistic (Z): -1.1002925552799432\n",
      "P-value: 0.13560233739666716\n",
      "Conclusion: We fail to reject the null hypothesis. The sample proportion is not significantly different from the population proportion.\n",
      "\n",
      "For race hispanic:\n",
      "sample proportion is  0.2857 , population proportion is  0.314\n",
      "H0: The sample proportion is equal to the population proportion\n",
      "H1: The sample proportion is less than the population proportion\n",
      "Test statistic (Z): -0.2280364577564366\n",
      "P-value: 0.4098089489503344\n",
      "Conclusion: We fail to reject the null hypothesis. The sample proportion is not significantly different from the population proportion.\n",
      "\n",
      "For race other_nh:\n",
      "sample proportion is  0.1429 , population proportion is  0.026\n",
      "H0: The sample proportion is equal to the population proportion\n",
      "H1: The sample proportion is greater than the population proportion\n",
      "Test statistic (Z): 2.747597604548477\n",
      "P-value: 0.003001681871167561\n",
      "Conclusion: We reject the null hypothesis. The sample proportion is significantly larger than the population proportion.\n",
      "\n"
     ]
    }
   ],
   "source": [
    "comparsion_engine(sample_input, population_data, age_gender, 'Queens Community District 2', 'Ethnicity',0.05)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "efe9460d",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-07-11T23:31:23.696551Z",
     "start_time": "2023-07-11T23:31:23.685533Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "For race white_nh:\n",
      "sample proportion is  0.3333 , population proportion is  0.648\n",
      "H0: The sample proportion is equal to the population proportion\n",
      "H1: The sample proportion is less than the population proportion\n",
      "Test statistic (Z): -1.1411757445984638\n",
      "P-value: 0.12689839792384172\n",
      "Conclusion: We fail to reject the null hypothesis. The sample proportion is not significantly different from the population proportion.\n",
      "\n",
      "For race black_nh:\n",
      "sample proportion is  0.0 , population proportion is  0.032\n",
      "H0: The sample proportion is equal to the population proportion\n",
      "H1: The sample proportion is less than the population proportion\n",
      "Test statistic (Z): -0.3149183286488868\n",
      "P-value: 0.37641183244810245\n",
      "Conclusion: We fail to reject the null hypothesis. The sample proportion is not significantly different from the population proportion.\n",
      "\n",
      "For race asian_nh:\n",
      "sample proportion is  0.3333 , population proportion is  0.209\n",
      "H0: The sample proportion is equal to the population proportion\n",
      "H1: The sample proportion is greater than the population proportion\n",
      "Test statistic (Z): 0.5296473694379232\n",
      "P-value: 0.2981782224499825\n",
      "Conclusion: We fail to reject the null hypothesis. The sample proportion is not significantly different from the population proportion.\n",
      "\n",
      "For race hispanic:\n",
      "sample proportion is  0.3333 , population proportion is  0.07\n",
      "H0: The sample proportion is equal to the population proportion\n",
      "H1: The sample proportion is greater than the population proportion\n",
      "Test statistic (Z): 1.7876231476956106\n",
      "P-value: 0.03691841416358088\n",
      "Conclusion: We reject the null hypothesis. The sample proportion is significantly larger than the population proportion.\n",
      "\n",
      "For race other_nh:\n",
      "sample proportion is  0.0 , population proportion is  0.041\n",
      "H0: The sample proportion is equal to the population proportion\n",
      "H1: The sample proportion is less than the population proportion\n",
      "Test statistic (Z): -0.35813210231862413\n",
      "P-value: 0.36012222800727517\n",
      "Conclusion: We fail to reject the null hypothesis. The sample proportion is not significantly different from the population proportion.\n",
      "\n"
     ]
    }
   ],
   "source": [
    "comparsion_engine(sample_input, population_data, age_gender, 'Manhattan Community District 1', 'Ethnicity',0.05)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "c0fdb5f6",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-07-11T23:31:51.319750Z",
     "start_time": "2023-07-11T23:31:51.309252Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "For Female:\n",
      "Sample proportion: 0.5\n",
      "Population proportion: 0.48999999999999994\n",
      "H0: The sample proportion is equal to the population proportion\n",
      "H1: The sample proportion is not equal to the population proportion\n",
      "Test statistic (Z): 0.07484811885651246\n",
      "P-value: 0.9403355558513942\n",
      "Conclusion: We fail to reject the null hypothesis. The sample proportion is not significantly different from the population proportion.\n",
      "\n",
      "For Male:\n",
      "Sample proportion: 0.5\n",
      "Population proportion: 0.511\n",
      "H0: The sample proportion is equal to the population proportion\n",
      "H1: The sample proportion is not equal to the population proportion\n",
      "Test statistic (Z): -0.08233639032704375\n",
      "P-value: 0.9343792173510008\n",
      "Conclusion: We fail to reject the null hypothesis. The sample proportion is not significantly different from the population proportion.\n",
      "\n"
     ]
    }
   ],
   "source": [
    "comparsion_engine(sample_input, population_data, age_gender, 'Queens Community District 2', 'Gender',0.01)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "3056d2aa",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-07-11T23:35:21.998069Z",
     "start_time": "2023-07-11T23:35:21.986912Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "For Female:\n",
      "Sample proportion: 1.0\n",
      "Population proportion: 0.503\n",
      "H0: The sample proportion is equal to the population proportion\n",
      "H1: The sample proportion is not equal to the population proportion\n",
      "Test statistic (Z): 1.405753584791119\n",
      "P-value: 0.1597973091381053\n",
      "Conclusion: We reject the null hypothesis. The sample proportion is significantly different from the population proportion.\n",
      "\n",
      "For Male:\n",
      "Sample proportion: 0.0\n",
      "Population proportion: 0.499\n",
      "H0: The sample proportion is equal to the population proportion\n",
      "H1: The sample proportion is not equal to the population proportion\n",
      "Test statistic (Z): -1.4113879580270878\n",
      "P-value: 0.15813025170802875\n",
      "Conclusion: We reject the null hypothesis. The sample proportion is significantly different from the population proportion.\n",
      "\n"
     ]
    }
   ],
   "source": [
    "comparsion_engine(sample_input, population_data, age_gender, 'Queens Community District 9', 'Gender',0.2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b1115de1",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "colab": {
   "provenance": []
  },
  "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.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}