Map_2020_EDA.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "b76a0075-a08b-4170-9bca-8dfe17863af5",
   "metadata": {},
   "source": [
    "## PROJECT 5\n",
    "\n",
    "MAP EDA Per Capita using population data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0db8bb78-b496-4f74-a55c-5755d8a71115",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "#Folium for Map \n",
    "from folium import Map\n",
    "from folium.plugins import HeatMap\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "607b9f50-209a-45ac-8397-de6a37dca3f4",
   "metadata": {},
   "outputs": [],
   "source": [
    "## Readining in data\n",
    "\n",
    "# get accident data for all years\n",
    "df_accident = pd.DataFrame()\n",
    "\n",
    "for i in range( 2010, 2021):   \n",
    "    folder_name =  str(i) + '_fars/'\n",
    "    print(i, folder_name)\n",
    "    df_accident = df_accident.append(pd.read_csv(\"./data/\" + folder_name + \"accident.CSV\", encoding='latin-1'))\n",
    "\n",
    "# get person data for all years\n",
    "df_person = pd.DataFrame()\n",
    "\n",
    "for i in range( 2010, 2021):   \n",
    "    folder_name =  str(i) + '_fars/'\n",
    "    print(i, folder_name)\n",
    "    df_person = df_person.append(pd.read_csv(\"./data/\" + folder_name + \"person.CSV\", encoding='latin-1'))\n",
    "\n",
    "    \n",
    "# Make column names lowercase\n",
    "df_accident.columns = df_accident.columns.str.lower()\n",
    "df_person.columns = df_person.columns.str.lower()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ea4fa308-b2cf-41c6-8d1e-4f69b51d9eed",
   "metadata": {},
   "outputs": [],
   "source": [
    "##merging person \n",
    "\n",
    "df_accident.columns.intersection(df_person.columns)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0c084262-ab82-4410-af85-de77ea7a2904",
   "metadata": {},
   "outputs": [],
   "source": [
    "#combining accident and person 2010-2020\n",
    "df_full= pd.merge(df_accident, df_person, on=['state', 'st_case', 've_forms', 'county'], how='inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b16c4361-097c-4e42-850f-d1b0a6526af6",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_full = df_full.drop(columns='statename_y')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "755637bc-fa2a-44c5-92eb-8da913d9b4cf",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_full['state'] = df_full['statename_x']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ebf31ac8-c1b5-48c0-a644-113903785d08",
   "metadata": {},
   "outputs": [],
   "source": [
    "# (df_full['state'] == 'Texas').value_counts()\n",
    "county_pop['state'] = county_pop['state'].str.lstrip()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "35b80624-aac4-4acd-899a-06f142bb2b11",
   "metadata": {},
   "outputs": [],
   "source": [
    "(county_pop['state'] == 'Texas').value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d185a1fc-edc3-40a8-9619-a0f35a7e1067",
   "metadata": {},
   "outputs": [],
   "source": [
    "(df_full['state'] == 'Texas').value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "254829a2-44c2-4195-9617-71fe60392681",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_full[['state', 'countyname','st_case']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "05150f4a-2251-4a9c-bd70-41f9f85662e1",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "df_full['countyname']\n",
    "df_full['countyname'] = df_full['countyname'].str.replace('(\\d+)', '')\n",
    "df_full['countyname'] = df_full['countyname'].str.replace('(','').replace(')','') \n",
    "df_full['countyname'] = df_full['countyname'].str.replace(')','') \n",
    "df_full['countyname'] = df_full['countyname'].str.rstrip()\n",
    "df_full['countyname'] = df_full['countyname'].str.lstrip()\n",
    "df_full['countyname'] = df_full['countyname'].str.lower()\n",
    "# df_full['countyname'] = df_full['countyname'].str.replace(' county', '')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b3dbb129-ee70-4294-8e4f-bb437d26950f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_full['countyname']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7b678956-f035-4640-8ecb-38f115099c87",
   "metadata": {},
   "outputs": [],
   "source": [
    "(df_full['countyname'] == 'los angeles').value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ee75cd62-be5c-4bfb-8308-be3230bcdd28",
   "metadata": {},
   "outputs": [],
   "source": [
    "county_pop['countyname'] = county_pop['countyname'].str.lower()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "613db136-8561-45aa-8dfe-b094ef0f97f9",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_full = df_full[['year', 'st_case', 'countyname', 'state']].dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "88332f68-17f7-4dec-bcfe-4d812e78b647",
   "metadata": {},
   "outputs": [],
   "source": [
    "# data from https://www.census.gov/data/datasets/time-series/demo/popest/2020s-counties-total.html\n",
    "\n",
    "# get population by county data from https://www.census.gov/data/datasets/time-series/demo/popest/2020s-counties-total.html\n",
    "county_pop1 = pd.read_excel('./data/co-est2019-annres.xlsx', skiprows=3)\n",
    "county_pop1 = county_pop1.rename(columns={'Unnamed: 0':'county_name'}, errors='ignore').dropna()\n",
    "\n",
    "county_pop2 = pd.read_excel('./data/co-est2021-pop.xlsx', skiprows=3)\n",
    "\n",
    "county_pop2 = county_pop2.rename(columns={'Unnamed: 0':'county_name', 'Unnamed: 1':'Estimates Base'}, errors='ignore').dropna()\n",
    "\n",
    "\n",
    "# merge 2020-2021 census data with 2010-2019 census data\n",
    "county_pop = pd.merge(county_pop1, county_pop2, on='county_name')\n",
    "\n",
    "# # split county_name column into county and state columns\n",
    "county_pop['countyname'] = county_pop['county_name'].str.split(',', expand=True).rename(columns={0:'countyname', 1:'state'})['countyname'].map(lambda x: x.replace('.', ''))\n",
    "county_pop['state'] = county_pop['county_name'].str.split(',', expand=True).rename(columns={0:'countyname', 1:'state'})['state']\n",
    "\n",
    "# reorder columns\n",
    "county_pop = county_pop.loc[:, ['county_name',\n",
    "                                'Census',\n",
    "                                'countyname',\n",
    "                                'state',\n",
    "                                2010,\n",
    "                                2011,\n",
    "                                2012,\n",
    "                                2013,\n",
    "                                2014,\n",
    "                                2015,\n",
    "                                2016,\n",
    "                                2017,\n",
    "                                2018,\n",
    "                                2019,\n",
    "                                2020,\n",
    "                                2021]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "128bdb83-b5d1-4f86-8e6a-175db67e90c6",
   "metadata": {},
   "outputs": [],
   "source": [
    "county_pop = county_pop.drop(index=0, errors='ignore').drop(columns=['county_name', 'Census'], errors='ignore')\n",
    "county_pop['countyname'] = county_pop['countyname'].str.replace(' county', '')\n",
    "county_pop.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "61f05267-6a69-4496-9d6a-14abcbc801d2",
   "metadata": {},
   "outputs": [],
   "source": [
    "pop_acc = pd.merge(df_full, county_pop, on=['countyname', 'state'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "20abf6e7-8cea-4781-bf30-82c180f6efb6",
   "metadata": {},
   "outputs": [],
   "source": [
    "(county_pop['countyname']).value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "85f017eb-5ad0-4741-9df3-65c178938f7e",
   "metadata": {},
   "outputs": [],
   "source": [
    "pop_acc['countyname'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f702c0bc-14a2-4289-a029-e0b05cba855f",
   "metadata": {},
   "outputs": [],
   "source": [
    "## download latitude, longitude, zip code data from https://www.kaggle.com/datasets/tsnowak/us-geographic-codes\n",
    "county_latlong_zip = pd.read_csv('./data/us_zip_fips_county.csv', encoding='unicode_escape').rename(columns=lambda x: x.lower().replace(' ', '_'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fc878928-c76f-4778-9b41-fd5bd944992d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# county_latlong_zip['countyname'] = county_latlong_zip['county_name']\n",
    "county_latlong_zip['countyname'] = county_latlong_zip['countyname'].str.lower()\n",
    "county_latlong_zip['countyname'] = county_latlong_zip['countyname'].str.replace(' county', '')\n",
    "county_latlong_zip['countyname'] = county_latlong_zip['countyname'].str.replace(' borough', '')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bf211503-dde9-49db-8618-00cf7ac39ef9",
   "metadata": {},
   "outputs": [],
   "source": [
    "county_latlong_zip = county_latlong_zip.drop(columns='county_name')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "87533b83-a6a1-4b78-b905-51f46066ec09",
   "metadata": {},
   "outputs": [],
   "source": [
    "county_latlong_zip['latitude'] = county_latlong_zip['centroid_lat']\n",
    "county_latlong_zip['longitud'] = county_latlong_zip['centroid_long']\n",
    "# county_latlong_zip.drop(columns=['centroid_lat', 'centroid_long'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2e76fd66-3f48-4336-8161-b38ebc3e39a3",
   "metadata": {},
   "outputs": [],
   "source": [
    "county_latlong_zip['countyname'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9b2d3746-bd5c-45ca-a55f-e25c23da0455",
   "metadata": {},
   "outputs": [],
   "source": [
    "county_latlong_zip"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "de441b9d-7632-44b2-9e59-c5a7f9612edd",
   "metadata": {},
   "outputs": [],
   "source": [
    "county_latlong_zip.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "03b42329-8c1e-41a4-bab2-e9d6709d3bcf",
   "metadata": {},
   "outputs": [],
   "source": [
    "county_latlong_zip = county_latlong_zip.drop(columns=['zip_code', 'state_abrv', 'state_code', 'ansi_code', 'centroid_lat', 'centroid_long'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e1f5e7cf-72f5-494d-9ee1-a430ed597b58",
   "metadata": {},
   "outputs": [],
   "source": [
    "county_latlong_zip['state'] = county_latlong_zip['state_name']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b450e6cd-cd1f-42be-81cc-d3c67d7a936f",
   "metadata": {},
   "outputs": [],
   "source": [
    "county_latlong_zip.columns.intersection(pop_acc.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ef59e74e-17f9-4a9f-9888-8bd00f1bcd7c",
   "metadata": {},
   "outputs": [],
   "source": [
    "map_data = pd.merge(county_latlong_zip, pop_acc, on=['countyname', 'state'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "29ef516b-10c7-458c-bd82-92cec62588fb",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "05c045f5-3e67-4128-b1f5-dfbedf114318",
   "metadata": {},
   "outputs": [],
   "source": [
    "map_data.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0ba77dc4-3f9c-4b99-b5aa-fca438efcfb8",
   "metadata": {},
   "outputs": [],
   "source": [
    "map_data[['state_name', 'county_code',   'fips_code',  'countyname',\n",
    "          'latitude','longitud' ,'year','st_case', 2020]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1eee45cc-685b-43c5-ab7f-ed7e42d51ef0",
   "metadata": {},
   "outputs": [],
   "source": [
    "map_data_2020 = map_data.loc[map_data['year'] == 2020]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e7e0355a-1612-46a5-8a89-156db7671fae",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.DataFrame(map_data_2020[['county_code','countyname']].value_counts())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3d526191-6c26-49f2-8234-41f916d221bd",
   "metadata": {},
   "outputs": [],
   "source": [
    "# (map_data_2020[['county_code','countyname']].value_counts())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "efdfcc52-a194-49c0-97d8-8ee845195028",
   "metadata": {},
   "outputs": [],
   "source": [
    "map_data_2020[['county_code','countyname']].value_counts().to_numpy().shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b1324911-6686-4e2f-8e1c-920ec329acab",
   "metadata": {},
   "outputs": [],
   "source": [
    "# x = pd.DataFrame(data=map_data_2020[['county_code','countyname']].value_counts().to_numpy(), columns='accident_count', index=map_data_2020[['county_code','countyname']].value_counts().index )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eec75147-581f-4576-8b9e-448f198719e3",
   "metadata": {},
   "outputs": [],
   "source": [
    "map_data_2020[2020]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c32cc546-1546-45e6-baa4-36898cc85d59",
   "metadata": {},
   "outputs": [],
   "source": [
    "for_map = Map(location=[30.169621, -96.683617], zoom_start=6, control_scale=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f147a92e-d3a6-48c4-a86d-e9a9437992da",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "hm_wide = HeatMap(list(zip(map_data_2020['latitude'],map_data_2020['longitud'])),\n",
    "    min_opacity=0.2,\n",
    "    radius=8, \n",
    "    blur=1, \n",
    "    max_zoom=1,\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "531f8e38-08d8-440e-aee0-2d17ec2dcb55",
   "metadata": {},
   "outputs": [],
   "source": [
    "## Printing map\n",
    "# for_map.add_child(hm_wide)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "94f7af10-a9b0-41b6-82c4-37aed38a4ef2",
   "metadata": {},
   "outputs": [],
   "source": [
    "## Source on folium map https://livecodestream.dev/post/how-to-plot-your-data-on-maps-using-python-and-folium/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "606ff125-233e-48b5-ae0e-1c713ce3cbe3",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}