Part_A_Data_Acquisition.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Exploring correlation between COVID morbidity in the counties of New York state and distances of the counties  from New York city."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reaserch question:\n",
    "\n",
    "### Is there any stastistically significant relation between distance of a New York State county from the city  of New York and the COVID infection rate among the population of the county? "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Part A: Fetching and cleansing Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data sources\n",
    "For this project, necessary data were collected from the following 3 sources:\n",
    "\n",
    "1. Github repositoey (Daiily covid morbidity)(https://github.com/nytimes/covid-19-data)\n",
    "2. NationalBureau of Economic Research (County Distance Database)(https://www.nber.org/research/data/county-distance-database)\n",
    "3. New York Demographics (Population in different counties of New York state)(https://www.newyork-demographics.com/)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Fetching COVID morbidity data from a github repository"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import csv, requests\n",
    "\n",
    "\n",
    "url = 'https://raw.githubusercontent.com/nytimes/covid-19-data/master/us-counties.csv'\n",
    "\n",
    "res = requests.get(url)\n",
    "content = res.content.decode('iso-8859-1')\n",
    "f = open('covid_data.csv', \"w\", newline ='')\n",
    "f.write(\"\")\n",
    "f.close()\n",
    "\n",
    "\n",
    "f = open('covid_data.csv', \"a\", newline ='')\n",
    "writer = csv.writer(f, delimiter = ',', lineterminator ='\\n')\n",
    "for row in csv.reader(content.splitlines()):\n",
    "    writer.writerow(row)\n",
    "f.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Checking contents of the covid_data.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['date', 'county', 'state', 'fips', 'cases', 'deaths']\n",
      "['2020-01-21', 'Snohomish', 'Washington', '53061', '1', '0']\n",
      "['2020-01-22', 'Snohomish', 'Washington', '53061', '1', '0']\n",
      "['2020-01-23', 'Snohomish', 'Washington', '53061', '1', '0']\n",
      "['2020-01-24', 'Cook', 'Illinois', '17031', '1', '0']\n"
     ]
    }
   ],
   "source": [
    "import csv\n",
    "n=0\n",
    "with open('covid_data.csv', 'r') as f:\n",
    "    reader = csv.reader(f)\n",
    "    for line in reader:\n",
    "        print(line)\n",
    "        if n>3:\n",
    "            break\n",
    "        n+=1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Data filtering: Making a csv file with only New York counties from covid_data.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "import csv\n",
    "\n",
    "f2 = open('covid_data_filtered.csv', \"w\", newline ='')\n",
    "f2.write(\"\")\n",
    "f2.close()\n",
    "\n",
    "\n",
    "f2 = open('covid_data_filtered.csv', \"a\", newline ='')\n",
    "\n",
    "writer = csv.writer(f2, delimiter = ',', lineterminator ='\\n')\n",
    "\n",
    "with open ('covid_data.csv', 'r') as f1:\n",
    "    reader = csv.reader(f1)\n",
    "    \n",
    "    for row in reader:\n",
    "        if 'date' in row:\n",
    "            writer.writerow(row)\n",
    "        elif (row[2]) == 'New York':\n",
    "            #print(row)\n",
    "            writer.writerow(row)\n",
    "\n",
    "f2.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Checking the csv with filtered covid data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['date', 'county', 'state', 'fips', 'cases', 'deaths']\n",
      "['2020-03-01', 'New York City', 'New York', '', '1', '0']\n",
      "['2020-03-02', 'New York City', 'New York', '', '1', '0']\n",
      "['2020-03-03', 'New York City', 'New York', '', '2', '0']\n",
      "['2020-03-04', 'New York City', 'New York', '', '2', '0']\n",
      "['2020-03-04', 'Westchester', 'New York', '36119', '9', '0']\n",
      "['2020-03-05', 'Nassau', 'New York', '36059', '1', '0']\n",
      "['2020-03-05', 'New York City', 'New York', '', '4', '0']\n",
      "['2020-03-05', 'Westchester', 'New York', '36119', '17', '0']\n",
      "['2020-03-06', 'Nassau', 'New York', '36059', '4', '0']\n",
      "['2020-03-06', 'New York City', 'New York', '', '5', '0']\n",
      "['2020-03-06', 'Rockland', 'New York', '36087', '2', '0']\n",
      "['2020-03-06', 'Westchester', 'New York', '36119', '33', '0']\n",
      "['2020-03-07', 'Nassau', 'New York', '36059', '4', '0']\n",
      "['2020-03-07', 'New York City', 'New York', '', '12', '0']\n",
      "['2020-03-07', 'Rockland', 'New York', '36087', '2', '0']\n",
      "['2020-03-07', 'Saratoga', 'New York', '36091', '2', '0']\n",
      "['2020-03-07', 'Westchester', 'New York', '36119', '69', '0']\n",
      "['2020-03-08', 'Nassau', 'New York', '36059', '5', '0']\n",
      "['2020-03-08', 'New York City', 'New York', '', '14', '0']\n",
      "['2020-03-08', 'Rockland', 'New York', '36087', '2', '0']\n",
      "['2020-03-08', 'Saratoga', 'New York', '36091', '2', '0']\n"
     ]
    }
   ],
   "source": [
    "import csv\n",
    "n=0\n",
    "with open('covid_data_filtered.csv', 'r') as f:\n",
    "    reader = csv.reader(f)\n",
    "    for line in reader:\n",
    "        print(line)\n",
    "        if n>20:\n",
    "            break\n",
    "        n+=1\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Fetching data on distances among counties from a website\n",
    "\n",
    "A csv file containing the data was simplly downloaded from the NationalBureau of Economic Research (County Distance Database)(https://www.nber.org/research/data/county-distance-database)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Overviewing distance data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\"county1\",\"mi_to_county\",\"county2\"\n",
      "\n",
      "\"01001\",22.4629943022086,\"01021\"\n",
      "\n",
      "\"01001\",26.8446865669988,\"01085\"\n",
      "\n",
      "\"01001\",29.5175849409829,\"01051\"\n",
      "\n",
      "\"01001\",30.7763708418057,\"01047\"\n",
      "\n"
     ]
    }
   ],
   "source": [
    "with open ('distance_data.csv', 'r') as f:\n",
    "    i=0\n",
    "    for line in f:\n",
    "        print (line)\n",
    "        if i>3:\n",
    "            break\n",
    "        i+=1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### # Data filtering: Making a csv file with distances among the counties of only New York State\n",
    "\n",
    "[The fips code of the counties in New York State starts with 36]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "import csv\n",
    "\n",
    "f2 = open('distance_data_filtered.csv', \"w\")\n",
    "f2.write(\"\")\n",
    "f2.close()\n",
    "\n",
    "\n",
    "f2 = open('distance_data_filtered.csv', \"a\")\n",
    "\n",
    "writer = csv.writer(f2, delimiter = ',', lineterminator ='\\n')\n",
    "\n",
    "with open ('distance_data.csv', 'r') as f1:\n",
    "    reader = csv.reader(f1)\n",
    "    \n",
    "    for row in reader:\n",
    "        if row[0][:2] =='36'and row[2][:2]=='36':\n",
    "            writer.writerow(row)\n",
    "\n",
    "f2.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Checking the csv file with filtered distance data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['36001', '16.2301528273665', '36093']\n",
      "['36001', '22.6607026010004', '36039']\n",
      "['36001', '23.6115698154751', '36095']\n",
      "['36001', '24.8616219873773', '36083']\n",
      "['36001', '29.4495615480702', '36021']\n",
      "['36001', '31.8518434578745', '36057']\n",
      "['36001', '36.2820414719704', '36091']\n",
      "['36001', '42.9688789938063', '36035']\n",
      "['36001', '46.7308087340766', '36111']\n",
      "['36001', '53.7163642437726', '36077']\n",
      "['36001', '56.8678824919545', '36115']\n",
      "['36001', '57.5177850856424', '36025']\n"
     ]
    }
   ],
   "source": [
    "import csv\n",
    "n=0\n",
    "with open('distance_data_filtered.csv', 'r') as f:\n",
    "    reader = csv.reader(f)\n",
    "    for line in reader:\n",
    "        print(line)\n",
    "        if n>10:\n",
    "            break\n",
    "        n+=1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###  Fetching NY state population data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Web-scrapping with detective works"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Kings County', 'Queens County', 'New York County', 'Suffolk County', 'Bronx County', 'Nassau County', 'Westchester County', 'Erie County', 'Monroe County', 'Richmond County', 'Onondaga County', 'Orange County', 'Rockland County', 'Albany County', 'Dutchess County', 'Saratoga County', 'Oneida County', 'Niagara County', 'Broome County', 'Ulster County', 'Rensselaer County', 'Schenectady County', 'Chautauqua County', 'Oswego County', 'Jefferson County', 'Ontario County', 'St. Lawrence County', 'Tompkins County', 'Putnam County', 'Steuben County', 'Wayne County', 'Chemung County', 'Clinton County', 'Cayuga County', 'Cattaraugus County', 'Sullivan County', 'Madison County', 'Warren County', 'Livingston County', 'Herkimer County', 'Washington County', 'Otsego County', 'Columbia County', 'Genesee County', 'Fulton County', 'Franklin County', 'Montgomery County', 'Tioga County', 'Cortland County', 'Chenango County', 'Greene County', 'Allegany County', 'Delaware County', 'Orleans County', 'Wyoming County', 'Essex County', 'Seneca County', 'Schoharie County', 'Lewis County', 'Yates County', 'Schuyler County', 'Hamilton County'] \n",
      "\n",
      "['2,559,903', '2,253,858', '1,628,706', '1,476,601', '1,418,207', '1,356,924', '967,506', '918,702', '741,770', '476,143', '460,528', '384,940', '325,789', '305,506', '294,218', '229,863', '228,671', '209,281', '190,488', '177,573', '158,714', '155,299', '126,903', '117,124', '109,834', '109,777', '107,740', '102,180', '98,320', '95,379', '89,918', '83,456', '80,485', '76,576', '76,117', '75,432', '70,941', '63,944', '62,914', '61,319', '61,204', '59,493', '59,461', '57,280', '53,383', '50,022', '49,221', '48,203', '47,581', '47,207', '47,188', '46,091', '44,135', '40,352', '39,859', '36,885', '34,016', '30,999', '26,296', '24,913', '17,807', '4,416']\n"
     ]
    }
   ],
   "source": [
    "import requests\n",
    "url = 'https://www.newyork-demographics.com/counties_by_population'\n",
    "\n",
    "r = requests.get(url)\n",
    "text =r.text\n",
    "county_names=[] # Here I'll append the county names as given in the website\n",
    "county_populations=[] # And here the population of each county will be appended\n",
    "data = text.split(\"\\n\")\n",
    "n=0\n",
    "for line in data:\n",
    "        if '-demographics\">'in line: # This is an example of some 'detective' works needed to fetch required data\n",
    "            county_names.append(line.split('\">')[1].replace('</a>',''))\n",
    "        elif ',' in line:\n",
    "            if '      ' in line:\n",
    "                county_populations.append(line.replace(' ', ''))# Removing all extra spaces \n",
    "                    \n",
    "print(county_names, '\\n')\n",
    "print(county_populations)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Making a dictionary with county names as keys and populations as values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "population_dict = {county_names[i]: county_populations[i] for i in range(len(county_names))} "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### creating a csv file containing population data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "import csv\n",
    "population_dict = {county_names[i]: county_populations[i] for i in range(len(county_names))} \n",
    "header = [\"county\", \"population\"]\n",
    "\n",
    "with open('population_data.csv', 'w', newline='') as f:\n",
    "    writer = csv.writer(f)\n",
    "    writer.writerow(header) \n",
    "    for k,v in population_dict.items():\n",
    "        writer.writerow([k,v])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Checking the csv file containing population data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['county', 'population']\n",
      "['Kings County', '2,559,903']\n",
      "['Queens County', '2,253,858']\n",
      "['New York County', '1,628,706']\n",
      "['Suffolk County', '1,476,601']\n"
     ]
    }
   ],
   "source": [
    "import csv\n",
    "n=0\n",
    "\n",
    "with open ('population_data.csv', 'r') as f:\n",
    "    reader = csv.reader(f)\n",
    "    for line in reader:\n",
    "        print(line)\n",
    "        if n>3:\n",
    "            break\n",
    "        n+=1\n",
    "    \n",
    "   "
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}