<jupyter_start><jupyter_text>tables_us_data.ipynbBuild tables of the latest COVID-19 statistics for U.S. counties.Inputs:* `outputs/us_counties_clean.csv`: The contents of `data/us_counties.csv` after data cleaning by [clean_us_data.ipynb](./clean_us_data.ipynb)* `outputs/us_counties_clean_meta.json`: Column type metadata for reading `data/us_counties_clean.csv` with `pd.read_csv()`**Note:** You can redirect these input files by setting the environment variable `COVID_OUTPUTS_DIR` to a replacement for the prefix `outputs` in the above paths.<jupyter_code># Initialization boilerplate
import os
import json
import pandas as pd
import numpy as np
from typing import *

import text_extensions_for_pandas as tp

# Local file of utility functions
import util

# Allow environment variables to override data file locations.
_OUTPUTS_DIR = os.getenv("COVID_OUTPUTS_DIR", "outputs")
util.ensure_dir_exists(_OUTPUTS_DIR)  # create if necessary<jupyter_output><empty_output><jupyter_text>Read and Reformat Input Data<jupyter_code># Read in the CSV file and apply the saved type information
csv_file = os.path.join(_OUTPUTS_DIR, "us_counties_clean.csv")
meta_file = os.path.join(_OUTPUTS_DIR, "us_counties_clean_meta.json")

# Read column type metadata
with open(meta_file) as f:
    cases_meta = json.load(f)

# Pandas does not currently support parsing datetime64 from CSV files.
# As a workaround, read the "Date" column as objects and manually 
# convert after.
cases_meta["Date"] = "object"

cases_vertical = (
    pd
    .read_csv(csv_file, dtype=cases_meta, parse_dates=["Date"])   
    .set_index(["FIPS", "Date"], verify_integrity=True)
)
cases_vertical<jupyter_output><empty_output><jupyter_text>Normalize the Confirmed and Deaths counts by populationThe populations of U.S. counties vary by several orders of magnitude, so it's useful to normalize the case count for each count to the county's population. Compute confirmed casesand deaths per 100 residents.<jupyter_code>cases = cases_vertical.copy()
cases["Confirmed_per_100"] = cases["Confirmed"] / cases["Population"] * 100
cases["Deaths_per_100"] = cases["Deaths"] / cases["Population"] * 100
cases<jupyter_output><empty_output><jupyter_text>Extract the most recent element of each time seriesMost of the tables below focus on the most recent day's data, so we generate a DataFrame withjust the last element of each time series.<jupyter_code>cases_without_index = cases.reset_index()
last_date = cases_without_index["Date"].max()
cases_by_county = (
    cases_without_index[cases_without_index["Date"] == last_date]
    .set_index("FIPS")
    .drop(columns=["Confirmed_Outlier", "Deaths_Outlier", "Recovered_Outlier"]))
cases_by_county<jupyter_output><empty_output><jupyter_text>Generate tablesNow that we have read and formatted the input data, we can use Pandas to generate summary tables of the latest COVID-19 data.  Table: COVID-19 Cases and Deaths by StateAggregate the most recent county-level numbers by state to build up a table of statewide totals.<jupyter_code>cases_by_state = (cases_by_county
 .groupby("State")
 .aggregate({
     "Population": "sum",
     "Confirmed": "sum",
     "Deaths": "sum"
 }))
cases_by_state["Confirmed_per_100"] = cases_by_state["Confirmed"] / cases_by_state["Population"] * 100
cases_by_state["Deaths_per_100"] = cases_by_state["Deaths"] / cases_by_state["Population"] * 100

cases_by_state = cases_by_state[["Population", "Confirmed", "Deaths", \
                                 "Confirmed_per_100", "Deaths_per_100"]]
cases_by_state
# Now our data prep is done and we can start analyzing.

# The latest nationwide totals
cases_by_state[["Confirmed", "Deaths"]].sum()
# Today's nationwide totals, computed from the county-level data
cases_by_county[["Confirmed", "Deaths"]].sum()<jupyter_output><empty_output><jupyter_text>Table: Top 10 states by total confirmed cases<jupyter_code>cases_by_state.sort_values("Confirmed", ascending=False).head(10)<jupyter_output><empty_output><jupyter_text>Table: Top 10 states by confirmed cases per 100 residents<jupyter_code>cases_by_state.sort_values("Confirmed_per_100", ascending=False).head(10)<jupyter_output><empty_output><jupyter_text>Table: Top 10 states by deaths per 100 residents<jupyter_code># Top 10 states by deaths per 100 residents
cases_by_state.sort_values("Deaths_per_100", ascending=False).head(10)<jupyter_output><empty_output><jupyter_text>Table: Top 10 states by confirmed cases<jupyter_code>cases_by_county.sort_values("Confirmed", ascending=False).head(20)<jupyter_output><empty_output><jupyter_text>Table: Top 20 counties by confirmed cases per 100 residents<jupyter_code>cases_by_county.sort_values("Confirmed_per_100", ascending=False).head(20)<jupyter_output><empty_output><jupyter_text>Table: Top 20 counties by deaths per 100 residents<jupyter_code>cases_by_county.sort_values("Deaths_per_100", ascending=False).head(20)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10077204388459976, 'fraction_numerical': 0.01909792767167818, 'mean_word_length': 7.004878048780488, 'pattern_counts': {'":': 3, '<': 45, '<?xml version=': 0, '>': 45, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '11', 'dir': 'jupyter-structured-clean-dedup', 'id': '426606', 'license': 'Apache-2.0', 'n_tokens_mistral': 1681, 'n_tokens_neox': 1549, 'n_words': 410, 'path': 'notebooks/tables_us_data.ipynb', 'repo_name': 'Dpkranjan/covid-notebooks', 'size': '4922'}