covid-19-usa.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# IMPORT PACKAGES\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "# 1. IMPORT USA FACTS CONFIRMED CASES DATA \n",
    "# import urllib to pull in the data\n",
    "from urllib import request\n",
    "# read the usa facts confirmed cases data it is in UTF8 BOM then convert to UTF8\n",
    "confirmed_utf8 = request.urlopen(\"https://usafactsstatic.blob.core.windows.net/public/data/covid-19/covid_confirmed_usafacts.csv\").read().decode(\"utf-8-sig\").encode(\"utf-8\")\n",
    "# check the data type - make sure it is bytes - not essential for final code\n",
    "type(confirmed_utf8)\n",
    "# import StringIO\n",
    "from io import StringIO\n",
    "# create dataframe\n",
    "cases = pd.read_csv(StringIO(str(confirmed_utf8,'utf-8')))\n",
    "\n",
    "# CREATE A UNIQUE LIST OF 'states' ALONG WITH its 'stateFIPS'\n",
    "state_fips = cases[['State', 'stateFIPS']].drop_duplicates()\n",
    "\n",
    "# 2. CASES BY STATES\n",
    "# group date columns by state using groupby\n",
    "states_cases = cases.groupby('State', as_index=False).sum()\n",
    "# create data frame with StateFIPS and State before dropping this for melt\n",
    "states_cases_fips = states_cases[['State', 'stateFIPS']]\n",
    "# drop countyFIPS, CountyName and Stat\n",
    "states_cases_drop = states_cases.drop(['countyFIPS','stateFIPS'], axis=1)\n",
    "# melt the dataframe by state\n",
    "states_cases_melt = pd.melt(states_cases_drop, id_vars=['State']).rename(columns={'variable':'date','value':'cases'})\n",
    "states_cases_melt\n",
    "\n",
    "# 3. IMPORT USA FACTS CONFIRMED DEATHS FILE \n",
    "# read the data it is in UTF8 BOM then convert to UTF8\n",
    "deaths_utf8 = request.urlopen(\"https://usafactsstatic.blob.core.windows.net/public/data/covid-19/covid_deaths_usafacts.csv\").read().decode(\"utf-8-sig\").encode(\"utf-8\")\n",
    "# create dataframe\n",
    "deaths = pd.read_csv(StringIO(str(deaths_utf8,'utf-8')))\n",
    "\n",
    "# 4. DEATHS BY STATES\n",
    "# group date columns by state using groupby\n",
    "states_deaths = deaths.groupby('State', as_index=False).sum()\n",
    "# drop countyFIPS, CountyName and Stat\n",
    "states_deaths_drop = states_deaths.drop(['countyFIPS','stateFIPS'], axis=1)\n",
    "# melt the dataframe by state\n",
    "states_deaths_melt = pd.melt(states_deaths_drop, id_vars=['State']).rename(columns={'variable':'date','value':'deaths'})\n",
    "states_deaths_melt\n",
    "\n",
    "# 5. MERGE STATE CASES AND DEATHS DATA\n",
    "merged_cases_deaths = states_cases_melt.merge(states_deaths_melt, on=['State', 'date'])\n",
    "merged_cases_deaths.tail(5)\n",
    "\n",
    "# 6. iIMPORT USA FACTS POPULATION FILE  \n",
    "# read the data it is in UTF8 BOM then convert to UTF8\n",
    "population_utf8 = request.urlopen(\"https://usafactsstatic.blob.core.windows.net/public/data/covid-19/covid_county_population_usafacts.csv\").read().decode(\"utf-8-sig\").encode(\"utf-8\")\n",
    "# create dataframe\n",
    "population = pd.read_csv(StringIO(str(population_utf8,'utf-8')))\n",
    "# 7. POPULATION BY STATE\n",
    "# group by state using groupby\n",
    "states_pop = population.groupby('State', as_index=False).sum()\n",
    "# drop countyFIPS, CountyName and Stat\n",
    "states_pop_drop = states_pop.drop(['countyFIPS'], axis=1)\n",
    "\n",
    "# 8. MERGE CASES INTO DEATHS INTO POPULATION AND THEN MERGE BACK'stateFIPS'\n",
    "merged_cases_deaths_pop = merged_cases_deaths.merge(states_pop_drop, on=['State'])\n",
    "merged_with_pop = merged_cases_deaths_pop.merge(state_fips, on=['State'])\n",
    "\n",
    "#9. INSERT FULL STATE NAME SO HAVE CHOICE ON USING STATE ABBREVIATION\n",
    "state_name = pd.DataFrame({'stateFIPS': [1, 2, 4, 5, 6, 8, 9, 10, 11, 12, 13, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 44, 45, 46, 47, 48, 49, 50, 51, 53, 54, 55, 56], 'state_name': ['Alabama', 'Alaska', 'Arizona', 'Arkansas', 'California', 'Colorado', 'Connecticut', 'Delaware', 'District of Columbia', 'Florida', 'Georgia', 'Hawaii', 'Idaho', 'Illinois', 'Indiana', 'Iowa', 'Kansas', 'Kentucky', 'Louisiana', 'Maine', 'Maryland', 'Massachusetts', 'Michigan', 'Minnesota', 'Mississippi', 'Missouri', 'Montana', 'Nebraska', 'Nevada', 'New Hampshire', 'New Jersey', 'New Mexico', 'New York', 'North Carolina', 'North Dakota', 'Ohio', 'Oklahoma', 'Oregon', 'Pennsylvania', 'Rhode Island', 'South Carolina', 'South Dakota', 'Tennessee', 'Texas', 'Utah', 'Vermont', 'Virginia', 'Washington', 'West Virginia', 'Wisconsin', 'Wyoming']})\n",
    "states = merged_with_pop.merge(state_name, on=['stateFIPS'])\n",
    "\n",
    "#10. CREATE DATE TIME FOR CREATING DATA POINTS FOR USE AS A CALCULATED FIELD INSERT IN A TITLE\n",
    "import datetime\n",
    "states['date_time'] = pd.to_datetime(states['date'])\n",
    "\n",
    "# 11. RENAME AND REORDER FOR EASE OF VIEW\n",
    "states.columns = ['state','date','cases', 'deaths','population','fips', 'state_name', 'date_time']\n",
    "states = states[['date','date_time','state','state_name','fips','population', 'cases', 'deaths']]\n",
    "\n",
    "# calculate incremental cases and deaths \n",
    "states['new_cases'] = (states.groupby('fips')['cases'].diff().fillna(states['cases'], downcast='infer'))\n",
    "states['new_deaths'] = (states.groupby('fips')['deaths'].diff().fillna(states['deaths'], downcast='infer'))\n",
    "\n",
    "# calculate 7 day rolling averages\n",
    "states['new_cases_7_day_roll_av'] = states.groupby('fips')['new_cases'].transform(lambda x: x.rolling(7, 1).mean().round())\n",
    "states['new_deaths_7_day_roll_av'] = states.groupby('fips')['new_deaths'].transform(lambda x: x.rolling(7, 1).mean().round())\n",
    "\n",
    "# 11. CREATE PER 100,000 CALCULATION COLUMNS\n",
    "# create column for cases per 100,000\n",
    "states['cases_per_100,000'] = (states['cases']/(states['population']/100000)).round(0).astype(int)\n",
    "# create column for deaths per 100,000\n",
    "states['deaths_per_100,000'] = (states['deaths']/(states['population']/100000)).round(0).astype(int)\n",
    "# create column for new cases per 100,000\n",
    "states['new_cases_per_100,000'] = (states['new_cases']/(states['population']/100000)).round(0).astype(int)\n",
    "# create column for new deaths per 100,000\n",
    "states['new_deaths_per_100,000'] = (states['new_deaths']/(states['population']/100000)).round(0).astype(int)\n",
    "# create column for new cases seven day rolling average per 100,000\n",
    "states['new_cases_7_day_roll_av_per_100,000'] = (states['new_cases_7_day_roll_av']/(states['population']/100000)).round(0).astype(int)\n",
    "# create column for new deaths seven day rolling average per 100,000\n",
    "states['new_deaths_7_day_roll_av_per_100,000'] = (states['new_deaths_7_day_roll_av']/(states['population']/100000)).round(0).astype(int)\n",
    "\n",
    "# create 14 day prior comparison for new cases\n",
    "states['new_cases_14 day_comp']  = (states.groupby('fips')['new_cases_7_day_roll_av'].shift(periods = 14, fill_value = 0))\n",
    "# calculate the delta for new cases over the prior 14 days \n",
    "states['new_cases_14 day_delta'] = states['new_cases_7_day_roll_av']/states['new_cases_14 day_comp']-1\n",
    "\n",
    "# create columns for ease of inserting data points using calculated fields in Tableau \n",
    "states['nj_cases_most_recent'] = states.loc[(states['state'] == 'NJ') & (states['date_time'] == str(states['date_time'].max())), 'cases'].iloc[0]\n",
    "states['nj_deaths_most_recent'] = states.loc[(states['state'] == 'NJ') & (states['date_time'] == str(states['date_time'].max())), 'deaths'].iloc[0]\n",
    "states['nj_new_cases_14_day_delta_most_recent'] = states.loc[(states['state'] == 'NJ') & (states['date_time'] == str(states['date_time'].max())), 'new_cases_14 day_delta'].iloc[0]\n",
    "\n",
    "# EXPORT TO A TEXT FILE\n",
    "states.to_csv('states.txt', sep='\\t')\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}