<jupyter_start><jupyter_text>etl_us_data.ipynbAcquire a copy of the latest COVID-19 time series data and write thatdata out into local CSV files in a format amenable to analysis with Pandas dataframes.Input data sources:* Primary data source: [2019 Novel Coronavirus COVID-19 (2019-nCoV) Data Repository by Johns Hopkins CSSE](https://github.com/CSSEGISandData/COVID-19).* Secondary data source (for values missing from primary source): [New York Times "Coronavirus (Covid-19) Data in the United States" repository](https://github.com/nytimes/covid-19-data).* Secondary data source (for values missing from primary source): [USAFacts Coronavirus Stats & Data](https://usafacts.org/issues/coronavirus/)Output files produced:* `outputs/us_counties.csv`: County-level time series data for the United States* `outputs/us_counties_meta.json`: Column type metadata for reading `data/us_counties.csv` with `pd.read_csv()`**Note:** You can redirect these output files by setting the environment variable `COVID_OUTPUTS_DIR` to a replacement for the prefix `outputs` in the above paths.To read these files back in, use `pd.read_csv()`:```pythonwith open("outputs/us_counties_meta.json") as f:    cases_meta = json.load(f)cases_meta["Date"] = "object"   Workaround for pd.read_csv() not supporting parsing datetime64cases_raw = pd.read_csv("../data/us_counties.csv", dtype=cases_meta, parse_dates=["Date"])cases = cases_raw.set_index(["FIPS", "Date"], verify_integrity=True)```<jupyter_code># Initialization boilerplate

# Import Python packages that this notebook uses.
import os
import numpy as np
import pandas as pd
from urllib.request import urlopen
import json
from datetime import datetime, date

# 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

# URLs for downloading the time series data directly from Github
_JH_BASE_URL = "https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/" + \
               "csse_covid_19_data/csse_covid_19_time_series/"
_JH_CONFIRMED_URL = _JH_BASE_URL + "time_series_covid19_confirmed_US.csv"
_JH_DEATHS_URL = _JH_BASE_URL + "time_series_covid19_deaths_US.csv"

# Currently there are no data on recovered patients for the US.
# _JH_RECOVERED_URL = _JH_BASE_URL + "time_series_covid19_recovered_US.csv"

_NYT_BASE_URL = "https://raw.githubusercontent.com/nytimes/covid-19-data/master/"
_NYT_CSV_URL = _NYT_BASE_URL + "us-counties.csv"

_USAFACTS_BASE_URL = "https://usafactsstatic.blob.core.windows.net/public/data/covid-19"
_USAFACTS_CONFIRMED_URL = f"{_USAFACTS_BASE_URL}/covid_confirmed_usafacts.csv"
_USAFACTS_DEATHS_URL = f"{_USAFACTS_BASE_URL}/covid_deaths_usafacts.csv"

# First date present in the data set, and the format of these dates. 
# Hopefully this won't change as new data are added.
# NOTE: One file uses "01/22/20" and the other uses "1/22/20"
# so you need to filter with endswith() to find matches.
_FIRST_DATE_SUFFIX = "1/22/20"
_DATE_FORMAT = "%m/%d/%y"<jupyter_output><empty_output><jupyter_text>Johns Hopkins dataWe pull the latest Johns Hopkins data from Github.<jupyter_code>raw_confirmed = pd.read_csv(_JH_CONFIRMED_URL)
raw_deaths = pd.read_csv(_JH_DEATHS_URL)

# No "recovered" time series at the moment. Generate an empty
# time series the schema from the deaths
raw_recovered = raw_deaths.copy(deep=True)
for i in range(len(raw_recovered.columns)):
    if str(raw_recovered.columns[i]).endswith(_FIRST_DATE_SUFFIX):
        ts_start_index = i
        break
for c in raw_recovered.columns[ts_start_index:]:
    raw_recovered[c] = 0

raw_confirmed
# Some of the FIPS codes contain NA's:
raw_confirmed[raw_confirmed["FIPS"].isna()]<jupyter_output><empty_output><jupyter_text>Filter the Johns Hopkins Data down to U.S. county-level statistics only. Filter out the locations without county FIPS codes, since they don'talign properly with the county-level metadata this data set will bejoined with, and the number of cases involved is relatively small.<jupyter_code>raw_confirmed = raw_confirmed[~raw_confirmed["FIPS"].isna()].copy()
raw_deaths = raw_deaths[~raw_deaths["FIPS"].isna()].copy()
raw_recovered = raw_recovered[~raw_recovered["FIPS"].isna()].copy()


# Probably due to the presence of NaNs, the remaining FIPS codes end
# up encoded as floating point numbers. Fix that.
for df in [raw_confirmed, raw_deaths, raw_recovered]:
    df["FIPS"] = df["FIPS"].astype("Int64")
    
raw_confirmed<jupyter_output><empty_output><jupyter_text>Remove locations that have FIPS codes but are not U.S. counties.<jupyter_code>def counties_df(df):
    return df[(df["FIPS"] >= 1000)  # Territories have FIPS codes < 1000
              & (~df["Admin2"].isna())  # Countries don't have the "Admin2" field set
              & (df["Admin2"] != "Unassigned")  # States have Admin2 set to "Unassigned"
              & (df["FIPS"] <= 60000)  # Expatriates are coded by state in values > 80k
              ].copy()

county_confirmed = counties_df(raw_confirmed)
county_deaths = counties_df(raw_deaths)
county_recovered = counties_df(raw_recovered)

county_confirmed<jupyter_output><empty_output><jupyter_text>Rearrange the Johns Hopkins data into vertical time seriesThe time series in the raw data are spread across multiple columns. Rotate them by 90 degrees so that they are spread across rows.<jupyter_code>def shred_time_series(df: pd.DataFrame, colname: str):
    """
    Turn a time series encoded as a range of columns into a time series
    encoded as a range of rows.
    
    This function hard-codes the column name mapping for Johns Hopkins 
    data, so it will only work on that data.
    
    :param df: Dataframe with a time series across the columns of each row,
     with an additional outer join indicator column at the very end.
    :param colname: Name of the new column where the time series should go
    
    :returns: A dataframe with one time series element per row.
     The returned dataframe will have a column called "Date" with the date
     of each time series element, and a column with the name `colname` with
     the associated value for each date.
    """
    for i in range(len(df.columns)):
        if str(df.columns[i]).endswith(_FIRST_DATE_SUFFIX):
            ts_start_index = i
            break
    
    ts_matrix = df[df.columns[ts_start_index:]].values
    ts_lists = ts_matrix.tolist()

    date_list = [datetime.strptime(s, _DATE_FORMAT) for s in df.columns[ts_start_index:]]

    # Create a new dataframe where the time series is a list
    nested_df = df[df.columns[:ts_start_index]].copy()
    nested_df[colname] = ts_lists

    # Expand out the list and add the dates back.
    flat_df = nested_df.explode(colname)
    flat_df["Date"] = date_list * len(nested_df.index)
    return flat_df

shredded_confirmed = shred_time_series(county_confirmed, "Confirmed")
shredded_deaths = shred_time_series(county_deaths, "Deaths")
shredded_recovered = shred_time_series(county_recovered, "Recovered")
shredded_confirmed<jupyter_output><empty_output><jupyter_text>Merge the filtered and reformatted Johns Hopkins data into a single DataFrame<jupyter_code># Sort by FIPS code and Date and clean up some columns that don't match
# perfectly.
sorted_deaths = shredded_deaths.sort_values(["FIPS", "Date"])
sorted_confirmed = shredded_confirmed.sort_values(["FIPS", "Date"])
sorted_recovered = shredded_recovered.sort_values(["FIPS", "Date"])

# The "confirmed" time series is missing the "population" column that is
# present in the "deaths" and "recovered" time series.
# Add it back in.
sorted_confirmed["Population"] = sorted_deaths["Population"]

# The floating point numbers in the "Lat" and "Long_" fields also have
# some discrepancies due to rounding error. Use the values in the 
# "confirmed" time series as the gold standard.
sorted_deaths["Lat"] = sorted_confirmed["Lat"]
sorted_deaths["Long_"] = sorted_confirmed["Long_"]
sorted_recovered["Lat"] = sorted_confirmed["Lat"]
sorted_recovered["Long_"] = sorted_confirmed["Long_"]

# Now we can combine the three time series into a single table
combined = (
    sorted_confirmed
    .merge(sorted_deaths, how="outer")
    .merge(sorted_recovered, how="outer"))

# Check for missing data
missing_rows = combined[combined["Confirmed"].isna()]
if len(missing_rows.index) > 0:
    raise ValueError(f"Missing 'Confirmed' time series data for the following rows:\n{missing_rows}")

combined<jupyter_output><empty_output><jupyter_text>Adjust data types and names of the columns in the merged DataFrame<jupyter_code># The outer joins in the previous cell convert some of the integer
# columns to object types. Fix that.

# Data types before:
combined.dtypes
combined["iso2"] = combined["iso2"].astype("string")
combined["iso3"] = combined["iso3"].astype("string")
combined["Admin2"] = combined["Admin2"].astype("string")
combined["Province_State"] = combined["Province_State"].astype("string")
combined["Country_Region"] = combined["Country_Region"].astype("string")
combined["Combined_Key"] = combined["Combined_Key"].astype("string")

combined["Confirmed"] = combined["Confirmed"].astype(np.int64)
combined["Deaths"] = combined["Deaths"].astype(np.int64)
combined["Recovered"] = combined["Recovered"].astype(np.int64)

# Data types after:
combined.dtypes
# Massage the column names a bit and drop unnecessary columns
to_retain = combined[["Date", "FIPS", "Province_State", "Admin2", 
                      "Population",
                      "Confirmed", "Deaths", "Recovered"]]
renamed = to_retain.rename(columns={
    "Province_State": "State",
    "Admin2": "County"
})
renamed<jupyter_output><empty_output><jupyter_text>New York Times dataPull in additional data from the New York Times' data repository to usefor filling in holes in the primary JHU data set.<jupyter_code>raw_nyt = pd.read_csv(_NYT_CSV_URL, parse_dates=["date"])
raw_nyt<jupyter_output><empty_output><jupyter_text>Filter the New York Times data down to U.S. county-level statistics only.<jupyter_code># NY Times data also has some locations with FIPS codes of NaN:
raw_nyt[raw_nyt["fips"].isna()][["county", "state"]].drop_duplicates()
# For now, drop the NaN FIPS codes like we did up above with the
# JHU data set.
raw_nyt = raw_nyt[~raw_nyt["fips"].isna()].copy()
raw_nyt["fips"] = raw_nyt["fips"].astype("int64")

# Also cast int-valued columns to nullable int.
raw_nyt["cases"] = raw_nyt["cases"].astype("Int64")
raw_nyt["deaths"] = raw_nyt["deaths"].astype("Int64")

# Rename the columns in preparation for joining with the primary
# data set.
nyt = raw_nyt.copy().rename(columns={
    "date": "Date",
    "county": "County",
    "state": "State",
    "fips": "FIPS",
    "cases": "Confirmed_NYT",
    "deaths": "Deaths_NYT"
})
nyt
# Compare the number of distinct FIPS codes and dates in primary and
# secondary data sets.
print(f"Primary data set has {len(renamed['FIPS'].unique())} counties "
      f"and {len(renamed['Date'].unique())} dates.")
print(f"Secondary data set has {len(nyt['FIPS'].unique())} counties "
      f"and {len(nyt['Date'].unique())} dates.")<jupyter_output>Primary data set has 3142 counties and 307 dates.
Secondary data set has 3217 counties and 308 dates.<jupyter_text>USAFacts dataWe pull the latest data from the USAFacts CDN. The format of this data is very similar to that of the Johns Hopkins data, so the processing here is also similar.<jupyter_code>raw_confirmed_usafacts = pd.read_csv(_USAFACTS_CONFIRMED_URL)
raw_deaths_usafacts = pd.read_csv(_USAFACTS_DEATHS_URL)

raw_confirmed_usafacts<jupyter_output><empty_output><jupyter_text>Filter the USAFacts Data down to U.S. county-level statistics only.Filter out the locations without FIPS codes, since they don'talign properly with the county-level metadata this data set will bejoined with, and the number of cases involved is relatively small.<jupyter_code>def counties_df(df):
    return df[(df["countyFIPS"] >= 1000)  # Territories and statewide remainders have FIPS codes < 1000
              ].copy()

county_confirmed_usafacts = counties_df(raw_confirmed_usafacts)
county_deaths_usafacts = counties_df(raw_deaths_usafacts)

county_confirmed_usafacts<jupyter_output><empty_output><jupyter_text>Rearrange the USAFacts data into vertical time seriesThe time series in the raw data are spread across multiple columns. Rotate them by 90 degrees so that they are spread across rows.<jupyter_code>shredded_confirmed_usafacts = shred_time_series(county_confirmed_usafacts, "Confirmed")
shredded_deaths_usafacts = shred_time_series(county_deaths_usafacts, "Deaths")
shredded_confirmed_usafacts
shredded_deaths_usafacts[shredded_deaths_usafacts["countyFIPS"] == 8014]
shredded_confirmed_usafacts[shredded_confirmed_usafacts["countyFIPS"] == 8014]<jupyter_output><empty_output><jupyter_text>Merge the filtered and reformatted USAFacts data into a single DataFrame<jupyter_code># Sort by FIPS code and Date prior to joining
sorted_deaths_usafacts = shredded_deaths_usafacts.sort_values(["countyFIPS", "Date"])
sorted_confirmed_usafacts = shredded_confirmed_usafacts.sort_values(["countyFIPS", "Date"])

# Now we can combine the time series into a single table
combined_usafacts = sorted_confirmed_usafacts.merge(
    sorted_deaths_usafacts[["countyFIPS", "Date", "Deaths"]], 
    how="outer", on=["countyFIPS", "Date"])

combined_usafacts<jupyter_output><empty_output><jupyter_text>Adjust data types and names of the columns in the merged DataFrame<jupyter_code># Data types before:
combined_usafacts.dtypes
# Encode strings with the Pandas string type
combined_usafacts["County Name"] = combined_usafacts["County Name"].astype("string")
combined_usafacts["State"] = combined_usafacts["State"].astype("string")

# Encode integer fields containing NaNs using the Pandas nullable int type
combined_usafacts["Confirmed"] = combined_usafacts["Confirmed"].astype("Int64")
combined_usafacts["Deaths"] = combined_usafacts["Deaths"].astype("Int64")

# Data types after:
combined_usafacts.dtypes
# Massage the column names a bit and drop unnecessary columns
to_retain_usafacts = combined_usafacts[
    ["Date", "countyFIPS", "County Name", 
     "State", "Confirmed", "Deaths"]]
renamed_usafacts = to_retain_usafacts.rename(columns={
    "countyFIPS": "FIPS",
    "County Name": "County",
    "Confirmed": "Confirmed_USAFacts",
    "Deaths": "Deaths_USAFacts",
})
renamed_usafacts<jupyter_output><empty_output><jupyter_text>Merge the three data sets into a single DataFrame<jupyter_code># Outer-join the three data sets
to_write = (
    renamed
    # Don't use the county names, because they differ across data sets
    .merge(nyt[["Date", "FIPS", "Confirmed_NYT", "Deaths_NYT"]], how="left")
    .merge(renamed_usafacts[["Date", "FIPS", "Confirmed_USAFacts", "Deaths_USAFacts"]], 
           on=["Date", "FIPS"], how="left")
)
to_write<jupyter_output><empty_output><jupyter_text>Write out the merged data to a CSV file<jupyter_code># Write the data out to a CSV file + a JSON file of type info.
output_csv_data_file = os.path.join(_OUTPUTS_DIR,"us_counties.csv")
print(f"Writing data to {output_csv_data_file}")
to_write.to_csv(output_csv_data_file, index=False)
col_type_mapping = {
    key: str(value) for key, value in to_write.dtypes.iteritems()
}
output_json_data_file = os.path.join(_OUTPUTS_DIR,"us_counties_meta.json")
print(f"Writing metadata to {output_json_data_file}")
with open(output_json_data_file, "w") as f:
    json.dump(col_type_mapping, f)<jupyter_output>Writing data to outputs/us_counties.csv
{'fraction_non_alphanumeric': 0.10614130783546172, 'fraction_numerical': 0.00853494192389142, 'mean_word_length': 5.676949443016281, 'pattern_counts': {'":': 12, '<': 66, '<?xml version=': 0, '>': 67, 'https://': 6, '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': '16', 'dir': 'jupyter-structured-clean-dedup', 'id': '167407', 'license': 'Apache-2.0', 'n_tokens_mistral': 5331, 'n_tokens_neox': 5031, 'n_words': 1409, 'path': 'notebooks/etl_us_data.ipynb', 'repo_name': 'itsrawlinz-jeff/COVID19_VISUALIZATION-R_JEFF', 'size': '15583'}