{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# COVID-19 Statistical Worksheet"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<U>Notes if you are using Jupyter Notebook</U>:  to call <B>exit()</B> from a notebook, please use <B>sys.exit()</B> (requires <B>import sys</B>); if a strange error occurs, it may be because Jupyter retains variables from all executed cells.  To reset the notebook's variables, click 'Restart Kernel' (the circular arrow) -- this will not undo any text changes.  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<B>Please do as many of these tasks as you can.</B>  I don't know whether these tasks will be overly time consuming, so please spend a minimum of 2-3 hours and if you have not completed all credit questions after that time and are unable to continue due to time constraints, simply let me know you were not able to finish and any notes you have about the problems.  Thank you!  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<B>please note</B> the usual caveats:\n",
    "<UL>\n",
    "    <LI>Hints and discussion are in the <B>Discussion Document</B> for this session.</LI>\n",
    "    <LI>Some tasks may require the use of <B>new features</B>.  These will be mentioned in the question and discussed in more depth in the Discussion.</LI>\n",
    "    <LI><B>My answers</B> and results <B>may not always be the best</B>, or even correct!  I am also a student of this library, so there may be better or possibly even more accurate answers than the ones I supply.  <U>If you find a discrepancy between your answer and mine, but yours looks correct</U>, the proper response is to investigate by cross-checking, not to try to fit your answer to my results!  Let me know if you see differences and we'll have a look.  </LI>\n",
    "</UL>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1: import the data\n",
    "<UL>\n",
    "    <LI>Import the data file <B>covid_event.csv</B> from this week's files.</LI>\n",
    "    <LI>Set the date column as the index, as some of the charting and grouping will be done with the date as a reference.</LI>\n",
    "    <LI>Once it has been set to the index, set the type of the index to <B>datetime64[ns]</B></LI>\n",
    "(It is also possible for the index and data type to be set in the <B>.read_csv()</B> function when you first ingest the data.)\n",
    "    <LI>Check the index's <B>dtype</B> to confirm that it has changed.</LI>\n",
    "</UL>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2: explore the DataFrame\n",
    "\n",
    "Once imported, make sure to check on the import by reviewing your DataFrame's first few rows, its structure and size, with all of the following tools (these are not required for your solution, but try each one of them to get a sense of it):\n",
    "<B><UL>\n",
    "    <LI>len()</LI>\n",
    "    <LI>.head() and .tail()</LI>\n",
    "    <LI>.columns</LI>\n",
    "    <LI>.dtypes</LI>\n",
    "    <LI>.describe()</LI>\n",
    "    </UL></B></LI>\n",
    "    <LI><U>You may also need</U> to see more of the DataFrame than Notebook displays (default maximum of 10 rows).  You can modify the pandas defaults thusly:\n",
    "\n",
    "<PRE>\n",
    "pd.set_option('display.max_rows', 500)\n",
    "pd.set_option('display.max_columns', 20)\n",
    "        </PRE></LI>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<U>Expected Output</U>:<br>\n",
    "<PRE>424611 rows\n",
    "\n",
    "first 5 lines:\n",
    "               county       state     fips  cases  deaths                  new\n",
    "date                                                                          \n",
    "2020-01-21  Snohomish  Washington  53061.0      1       0  SnohomishWashington\n",
    "2020-01-22  Snohomish  Washington  53061.0      0       0  SnohomishWashington\n",
    "2020-01-23  Snohomish  Washington  53061.0      0       0  SnohomishWashington\n",
    "2020-01-24       Cook    Illinois  17031.0      1       0         CookIllinois\n",
    "2020-01-24  Snohomish  Washington  53061.0      0       0  SnohomishWashington\n",
    "\n",
    "last 5 lines:\n",
    "                county    state     fips  cases  deaths                new\n",
    "date                                                                      \n",
    "2020-08-11  Sweetwater  Wyoming  56037.0      7       0  SweetwaterWyoming\n",
    "2020-08-11       Teton  Wyoming  56039.0      2       0       TetonWyoming\n",
    "2020-08-11       Uinta  Wyoming  56041.0      2       1       UintaWyoming\n",
    "2020-08-11    Washakie  Wyoming  56043.0      2       0    WashakieWyoming\n",
    "2020-08-11      Weston  Wyoming  56045.0     -1       0      WestonWyoming\n",
    "\n",
    "columns:\n",
    "Index(['county', 'state', 'fips', 'cases', 'deaths', 'new'], dtype='object')\n",
    "\n",
    "column dtypes:\n",
    "county     object\n",
    "state      object\n",
    "fips      float64\n",
    "cases       int64\n",
    "deaths      int64\n",
    "new        object\n",
    "dtype: object\n",
    "\n",
    "cases.describe:\n",
    "                fips          cases         deaths\n",
    "count  420389.000000  424611.000000  424611.000000\n",
    "mean    31024.987469      12.142074       0.389493\n",
    "std     16212.075849      83.494348       6.264726\n",
    "min      1001.000000   -1742.000000    -512.000000\n",
    "25%     18147.000000       0.000000       0.000000\n",
    "50%     29165.000000       1.000000       0.000000\n",
    "75%     46073.000000       5.000000       0.000000\n",
    "max     78030.000000    8021.000000    1221.000000"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3: count of unique states represented in the data\n",
    "Please show a count of unique states in the data.  I found <B>55</B> unique areas in the 'state' column.\n",
    "\n",
    "(<U>Hint</U>:  the <B>.unique()</B> method discards duplicates.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<U>Expected Output</U>:<br>\n",
    "<PRE>55</PRE>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4. Show those 'states' not in a 50-state list.\n",
    "If there are <B>55</B> unique names in the <B>state</B> column, what are the 5 non-state names?  \n",
    "<UL>\n",
    "    <LI>Read into a DataFrame the reference file <B>state_name_abbrev.csv</B> to <I>programmatically</I> identify those names that are not part of the 50 state list.</LI>\n",
    "    <LI>Use the column of state names to identify which statements in your file are not found in the 50 state list.</LI>\n",
    "    <LI>This should be possible in one or two statements; it should not be done with looping.  (<U>Hint</U>:  consider the <B>.isin()</B> membership test in a conditional slice, and the <B>.unique()</B> method of the <B>Series</B> type)</LI>\n",
    "</UL>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<U>Expected Result</U> will show the following states, but may come as an array, Series or set -- the below is not meant to represent a specific output:<br>\n",
    "<PRE>District of Columbia\n",
    "Puerto Rico\n",
    "Virgin Islands\n",
    "Guam\n",
    "Northern Mariana Islands</PRE>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5:  count of unique counties represented in the data\n",
    "Find a unique count of counties in the data set.  Since some county names are not unique among states, a unique count of counties will look for <B>state</B> and <B>county</B> in combination.  I found <B>3250</B> unique state + county combinations.  \n",
    "\n",
    "<U>Hint</U>:  a groupby object has an <B>.ngroups</B> attribute which returns the number of groups.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<U>Expected Output</U>:<br>\n",
    "<PRE>3250</PRE>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 6: cases by state, sorted\n",
    "Please sum the number of cases grouped by state.  Then, use <B>.sort_values()</B> with the parameter argument <B>ascending=False</B> to sort the states by cases, from highest to lowest."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<U>Expected Output</U>:<br>\n",
    "<PRE>state\n",
    "California                  586078\n",
    "Florida                     542784\n",
    "Texas                       522626\n",
    "New York                    426713\n",
    "Georgia                     205920\n",
    "Illinois                    198975\n",
    "Arizona                     188780\n",
    "New Jersey                  187328\n",
    "North Carolina              138124\n",
    "Louisiana                   133243\n",
    "Pennsylvania                125079\n",
    "Tennessee                   122089\n",
    "Massachusetts               121707\n",
    "Alabama                     103851\n",
    "Ohio                        102827\n",
    "South Carolina              102130\n",
    "Virginia                    101924\n",
    "Michigan                     98361\n",
    "Maryland                     97403\n",
    "Indiana                      77627\n",
    "Mississippi                  68479\n",
    "Washington                   66620\n",
    "Wisconsin                    66146\n",
    "Missouri                     61932\n",
    "Minnesota                    61880\n",
    "Nevada                       57608\n",
    "Colorado                     51578\n",
    "Connecticut                  50684\n",
    "Arkansas                     50411\n",
    "Iowa                         49521\n",
    "Utah                         44836\n",
    "Oklahoma                     44726\n",
    "Kentucky                     37503\n",
    "Kansas                       32174\n",
    "Nebraska                     29030\n",
    "Idaho                        25961\n",
    "Puerto Rico                  23403\n",
    "New Mexico                   22650\n",
    "Oregon                       21782\n",
    "Rhode Island                 20053\n",
    "Delaware                     15699\n",
    "District of Columbia         12896\n",
    "South Dakota                  9713\n",
    "North Dakota                  7889\n",
    "West Virginia                 7877\n",
    "New Hampshire                 6863\n",
    "Montana                       5125\n",
    "Alaska                        4595\n",
    "Maine                         4052\n",
    "Hawaii                        3733\n",
    "Wyoming                       3074\n",
    "Vermont                       1472\n",
    "Guam                          1403\n",
    "Virgin Islands                 639\n",
    "Northern Mariana Islands        82\n",
    "Name: cases, dtype: int64</PRE>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 7: bar chart:  cases by state, 10 highest\n",
    "Working from the data built above, show the first 10 states and number of cases in a bar chart. \n",
    "\n",
    "<U>Note</U> that a semicolon placed at the end of any <B>.plot()</B> statements will hide the <B>&lt;AxesSubplot&gt;</B> object from display."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<U>Expected Output</U>:<br>\n",
    "<IMG SRC=\"images/hw7.png\" align=\"left\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 8: show cases by county for a given state.\n",
    "Take user input for a state name (or you can hard-code the state name).  Show the cases by county within the state, sorted from highest to lowest."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<U>Expected Output</U>:<BR>\n",
    "<PRE>please enter a state name: Florida\n",
    "total 68\n",
    "county\n",
    "Miami-Dade      135129\n",
    "Broward          63605\n",
    "Palm Beach       37639\n",
    "Hillsborough     32996\n",
    "Orange           32041\n",
    "Duval            23741\n",
    "Pinellas         18103\n",
    "Lee              16717\n",
    "Polk             14645\n",
    "Collier          10487\n",
    "Osceola           9858\n",
    "Escambia          9807\n",
    "Manatee           9395\n",
    "Volusia           8040\n",
    "Seminole          7219\n",
    "Pasco             7172\n",
    "Marion            6668\n",
    "Sarasota          6314\n",
    "Brevard           6190\n",
    "St. Lucie         5920\n",
    "Lake              5229\n",
    "Leon              5115\n",
    "Bay               4523\n",
    "Alachua           4246\n",
    "Santa Rosa        4013\n",
    "Martin            3852\n",
    "St. Johns         3738\n",
    "Okaloosa          3586\n",
    "Clay              3327\n",
    "Columbia          2917\n",
    "Indian River      2555\n",
    "Charlotte         2281\n",
    "Hernando          2069\n",
    "Jackson           1936\n",
    "Gadsden           1927\n",
    "Hendry            1805\n",
    "Citrus            1594\n",
    "Monroe            1548\n",
    "Putnam            1539\n",
    "Suwannee          1507\n",
    "Highlands         1474\n",
    "Walton            1428\n",
    "DeSoto            1377\n",
    "Sumter            1344\n",
    "Nassau            1253\n",
    "Flagler           1095\n",
    "Okeechobee        1077\n",
    "Hardee             986\n",
    "Taylor             981\n",
    "Baker              964\n",
    "Washington         877\n",
    "Wakulla            726\n",
    "Unknown            721\n",
    "Madison            710\n",
    "Levy               698\n",
    "Gulf               685\n",
    "Hamilton           623\n",
    "Dixie              562\n",
    "Bradford           519\n",
    "Holmes             509\n",
    "Calhoun            481\n",
    "Jefferson          462\n",
    "Union              435\n",
    "Franklin           433\n",
    "Liberty            407\n",
    "Glades             406\n",
    "Gilchrist          369\n",
    "Lafayette          189\n",
    "Name: cases, dtype: int64</PRE>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 9: bar chart:  cases by county for a given state, 10 highest\n",
    "Working from the above data, show the first 10 counties in a bar chart."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<U>Expected Output</U> (image may be cut off at bottom):<BR>\n",
    "<PRE>please enter a state name: New York\n",
    "total 59</PRE>\n",
    "<IMG SRC=\"images/hw9.png\" align=\"left\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 10: pie chart showing all 50 states and share of cases\n",
    "Show the number of cases by state in a pie chart.\n",
    "<UL>\n",
    "    <LI>The plot() arguments I used were <B>kind='pie'</B>, <B>figsize=(5,5)</B>,  <B>labels=None</B> </LI>\n",
    "<LI>I set a legend by using the <B>plt.legend()</B> method with <B>loc=(.95,.35)</B> (for positioning) and <B>labels=</B> equal to the first 10 names in the index.</LI>\n",
    "    </UL>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<U>Expected Output</U>:<BR>\n",
    "<IMG SRC=\"images/hw10.png\" align=\"left\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 11: pie chart showing top 10 states and share of cases among them\n",
    "perform the same aggregation and charting as above, but chart a slice of just the first 10 states"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<U>Expected Output</U>:<BR>\n",
    "<IMG SRC=\"images/hw11.png\" align=\"left\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 12: cases over time for a selected state\n",
    "Take <B>input()</B> for a state name (or hard code a state name if you prefer), then build a line chart showing the increase in number of cases over time for that state.  \n",
    "\n",
    "The data shows new cases for each day, so a line chart of just new cases will not show the increase.  In order to calculate the increase, after grouping cases by date in one state, use <B>.cumsum()</B> to calculate the cumulative sum.  Here is an example of <B>.cumsum()</B>:\n",
    "<PRE>\n",
    "df = pd.DataFrame({'a': [1, 2, 3], 'b': [9, 30, 200]})\n",
    "df['c'] = df.b.cumsum()\n",
    "print(df)\n",
    "   a    b    c        # 'c' column adds up 'b' column values\n",
    "0  1    9    9        #   9 =  0 +   9\n",
    "1  2   30   39        #  39 =  9 +  30\n",
    "2  3  200  239        # 239 = 39 + 200\n",
    "</PRE>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<U>Expected Output</U>:<BR>\n",
    "<PRE>please enter a state name: New York</PRE>\n",
    "<IMG SRC=\"images/hw12.png\" align=\"left\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 13: (extra credit) cases over time:  selected states comparison\n",
    "Repeat the above exercise, but this time allow the user to input any number of states; build a line chart comparing the rates of new cases.  You can plot multiple lines from a DataFrame by calling <B>.plot()</B> on a DataFrame that has the columns you want to plot.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<U>Expected Output</U>:<BR>\n",
    "<PRE>please enter states separated by commas: New York, Florida</PRE>\n",
    "<IMG SRC=\"images/hw13.png\" align=\"left\">    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 14: (extra credit) scatter plot showing cases by state population\n",
    "This chart shows the correlation of two factors:  the <B>y</B> (vertical) axis will show state population, and the <B>x</B> axis will show number of cases.  The placement of the dot within the 2D area correlates the two numers, which can visually indicate cases per capita  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<U>Expected Output</U>:<BR>\n",
    "<IMG SRC=\"images/hw14.png\" align=\"left\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 15: (extra credit) bar chart showing daily increase in cases for a state\n",
    "Use <B>input()</B> for a state name.  You can use the <B>.shift()</B> method to compare count of a day's cases to the prior days, calculating the increase.  See discussion for more details.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<U>Expected Output</U>:<BR>\n",
    "<PRE>please enter a state name: New York</PRE>\n",
    "<IMG SRC=\"images/hw16.png\" align=\"left\">"
   ]
  }
 ],
 "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.12.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
