<jupyter_start><jupyter_text>Covid data for the counties I care aboutThe Washington Post has convenient data by state. I care about Washington DC, where I live, and how certain other locations are doing. The state-level data is not fine-grained enough for what I watn to see. MSA and county level covid data are available online, but overwelming and not easily filterable to what I want. I'm creating this tool to provide information at the county level.  Plan:Turn these into GitHub issues.- Use plotly for interactive visualizations - Serve the website via FastAPI or put into Streamlit. If use FastAPI, use MyPy to check that county/state option selected is a dict key/value- Use GitHub actions to get data from NYT repo into a forked repo. I forked, updated, and release a GitHub action.- Use Prefect to run my script where I pull the data from NYT website and process it daily.- Use Great Expectations for data quality checking.- Use PyTest for code checking. - May use DVC to version data.- Could push the data to a database for fun/speed.- Make an app that allows other users to choose which counties they want to include. Have to make some design decisions about how to show users that information.  Imports and config<jupyter_code>import pandas as pd
import plotly.express as px

pd.options.display.max_rows = 100<jupyter_output><empty_output><jupyter_text>Read in data<jupyter_code>def read_data(oldest_year:int = 2020, newest_year:int = 2022): 
    """Read in csv files of data from the nytimes and concatenate into a single pandas DataFrame. 
    
    Args:
      oldest_year: first year of data to use
      newest_year: most recent year of data to use
    """

    df_dicts = {}   # dictionary to hold the data for each year before concatenation

    for year in range(oldest_year, newest_year+1):
        df_dicts[f'df_{year}'] = pd.read_csv(
    f'https://raw.githubusercontent.com/nytimes/covid-19-data/master/rolling-averages/us-counties-{year}.csv', index_col='date'
    )
    
    return pd.concat(df_dicts.values())
    
df = read_data(2020, 2022)
df.head(2)
df.shape
df.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Index: 2329329 entries, 2020-01-21 to 2022-03-18
Data columns (total 9 columns):
 #   Column               Dtype  
---  ------               -----  
 0   geoid                object 
 1   county               object 
 2   state                object 
 3   cases                int64  
 4   cases_avg            float64
 5   cases_avg_per_100k   float64
 6   deaths               int64  
 7   deaths_avg           float64
 8   deaths_avg_per_100k  float64
dtypes: float64(4), int64(2), object(3)
memory usage: 177.7+ MB<jupyter_text>Write out the file. Use parquet format for size and some speed. Alternatively, could store in a db.<jupyter_code>df.to_parquet(f'../data/2020-2022-all-covid-data-through-{df.tail(1).index.values[0]}.parquet') #20mb mar 20222<jupyter_output><empty_output><jupyter_text>Have to not have an index for feather format.<jupyter_code>df_21_22.reset_index().to_feather(f'../data/2021-2022-all-covid-data-through-{df_21_22.tail(1).index.values[0]}.feather')  #48.8mb feb. 21 
df = pd.read_feather(f"../data/2021-2022-all-covid-data-through-{df_21_22.tail(1).index.values[0]}.feather")
   
df["date"] = pd.to_datetime(
        df["date"]
    )  # negating some of the speed benefit of feather
df.set_index("date", inplace=True) 

# 1.7s
df = pd.read_parquet("../data/2021-2022-all-covid-data-through-2022-02-20.parquet")

# .8s
df.info()
# df_21_22.to_csv(f'2021-2022-all-covid-data-through{df_21_22.tail(1).index.values[0]}.csv') # 92.9mb feb. 21<jupyter_output><empty_output><jupyter_text>Filter to counties of interest<jupyter_code>counties = [
    "District of Columbia",
    "Wood",
    "Putnam",
    "Montgomery",
    "Prince George's",
    "Arlington",
    "Alexandria city",
    "New York City",  # README at NYT mentions some NE are city, not county!
    "Allegheny",
    "Cook",
    "Baltimore",
    "Franklin",
    "Clermont",
    "Somerset",
    "Philadelphia",
    "Denver",
    "Boulder",
    "San Francisco",
    "Los Angeles",
    "Pima",
    "Manatee",
    "Fairfax"]

cols = ["county", "state", "geoid", "cases_avg_per_100k"]<jupyter_output><empty_output><jupyter_text>See each state/county of selected counties once.<jupyter_code>df_once = df_21_22.loc[df_21_22["county"].isin(counties), cols]
df_once

df_21_22.drop_duplicates(subset=["county", "state"])<jupyter_output><empty_output><jupyter_text>Convert geoid to FIPS code for plotting<jupyter_code>df_21_22["fips"] = df_21_22["geoid"].str[-5:]
df_21_22<jupyter_output><empty_output><jupyter_text>Filter to fips codes of counties I want. If ever make into an app, will change to have folks choose State and then County from drop downs.<jupyter_code>fips_counties = [
    "11001",
    "24033",
    "24031",
    "17031",
    "39173",
    "39137",
    "39113",
    "39049",
    "51013",
    "42101",
    "42003",
    "39025",
    "08031",
    "08013",
    "04019",
    "24005",
    "06037",
    "06075",
    "36998",
    # "36061",
    "12081",
    "51510",
    "51059",
    "55083"
]

cols = ["county", "state", "fips", "cases_avg_per_100k"]


df_21_22_counties = df_21_22.loc[df_21_22["fips"].isin(fips_counties), cols]
df_21_22_counties

px.line(
    df_21_22_counties, x=df_21_22_counties.index, y="cases_avg_per_100k", color="county"
)
df_21_22_counties.index = pd.to_datetime(df_21_22_counties.index)
df_21_22_counties.info()
df_22_counties = df_21_22_counties.loc['2022']
px.line(
    df_22_counties, x=df_22_counties.index, y="cases_avg_per_100k", color="county"
)<jupyter_output><empty_output><jupyter_text>MapMost recent 7 day moving average.Scatter geo. Future direction: could make an animation over time.  Could show the DataFrame for just the most recent day in an app, too.<jupyter_code>most_recent_date = df_21_22_counties.index.max()
int('012'.zfill(5))
df_newest = df_21_22_counties[df_21_22_counties.index == most_recent_date].sort_values(
    by="cases_avg_per_100k"
)
df_newest

# could add % change from week earlier

# Need a shape file or to  get lat lon for each of the counties
# harder to find then expected. Found a mapping here: https://simplemaps.com/data/us-counties.

df_mapping = pd.read_csv('../uscounties.csv')
df_mapping<jupyter_output><empty_output><jupyter_text>Need to add a 0 in front for the mapping county fips.<jupyter_code>df_mapping['county_fips_str'] = df_mapping['county_fips'].astype(str).str.zfill(5)
df_mapping
df_ll = pd.merge(left=df_newest, right=df_mapping, how='left', left_on='fips', right_on='county_fips_str')
df_ll

df_ll.set_index('county_x', inplace=True)
df_ll<jupyter_output><empty_output><jupyter_text>Get New York's lat/lon manually - fips for Manhattan is 36998, NYT aggregates data for the city!<jupyter_code>df_mapping.head(30)

df_mapping[df_mapping['county_fips'] == 36061]

df_mapping.loc[df_mapping['county_fips'] == 36061, 'lng']
df_ll.loc['New York City', 'lat'] = 40.7781
df_ll.loc['New York City', 'lng'] = -73.9675
df_ll
px.scatter_geo(
    df_ll,
    lat="lat",
    lon="lng",
    size="cases_avg_per_100k",
    scope="usa",
    color="cases_avg_per_100k",
    color_continuous_scale='Temps',
    hover_name='county_y'
)<jupyter_output><empty_output><jupyter_text>Scatter geo circle sizes are often population, I don't think I want to do that here. Maybe if this was MSA data. Things could get tricky in places where the NYT data isn't broken out by county (e.g. NYC). Let's change the labels. Could display guages/indicators/KPI-type information, but might just do directly in Streamlit if decide to use Streamlit. Could explore sparklines.Subplots are possible. Probably need to drop down to vanilla plotting.<jupyter_code>df = px.data.gapminder().query("continent == 'Oceania'")
fig = px.line(df, x='year', y='gdpPercap', facet_row='country')
fig.update_layout(yaxis_title=None) # only removes final y title
fig.update_xaxes(showgrid=False)
fig.update_yaxes(showgrid=False)


fig.show()

# example with lower level api example on https://community.plotly.com/t/sparklines-from-dataframe/38174

# maybe make a template for subplots if possible

# move the country label to the left and make it just the name<jupyter_output><empty_output><jupyter_text>The following are adapted from answers [here](https://stackoverflow.com/questions/64462790/how-to-plot-plotly-gauge-charts-next-to-each-other-with-python). The method of making traces seems to work better in terms of spacing between the plots.<jupyter_code>import plotly.graph_objs as go
from plotly.subplots import make_subplots

trace1 = go.Indicator(mode="gauge+number",    value=400,    domain={'row' : 1, 'column' : 1}, title={'text': "Speed 1"})
trace2 = go.Indicator(mode="gauge+number",    value=250,    domain={'row' : 1, 'column' : 2}, title={'text': "Speed 2"})

fig = make_subplots(
    rows=1,
    cols=2,
    specs=[[{'type' : 'indicator'}, {'type' : 'indicator'}]],
    )

fig.append_trace(trace1, row=1, col=1)
fig.append_trace(trace2, row=1, col=2)

fig
import plotly.graph_objs as go

# traces with separate domains to form a subplot
trace1 = go.Indicator(mode="gauge+number",    value=400,    domain={'x': [0.0, 0.4], 'y': [0.0, 1]},    title={'text': "Speed 1"})

trace2 = go.Indicator(mode="gauge+number",    value=250,    domain={'x': [0.6, 1.0], 'y': [0., 1.00]},    title={'text': "Speed 2"})

# layout and figure production
layout = go.Layout(height = 600,
                   width = 600,
                   autosize = False,
                   title = 'Side by side gauge diagrams')
fig = go.Figure(data = [trace1, trace2], layout = layout)
fig<jupyter_output><empty_output><jupyter_text>Geopandas with plotly with Jupyter with conda with Python 3.10 is currently an impossibility. 3.9 seems like probably an impossibility, too. At least with conda, via conda-forge of conda channels or pip. Tried several fresh environments.<jupyter_code># px.choropleth(df_newest,  locations='fips', color='cases_avg_per_100k', scope='usa'  )
# %pip install plotly-geo pyshp
# %pip install shapely
# import plotly.figure_factory as ff

# ff.create_choropleth(fips=df_newest['fips'], values=df_newest['cases_avg_per_100k'])
# %conda install plotly -c conda-forge
# %conda install -c plotly plotly-geo
# %conda install geopandas -c conda-forge<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12108922363847045, 'fraction_numerical': 0.05059868675164156, 'mean_word_length': 4.212380473074988, 'pattern_counts': {'":': 0, '<': 57, '<?xml version=': 0, '>': 57, 'https://': 4, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '14', 'dir': 'jupyter-structured-clean-dedup', 'id': '629975', 'license': 'MIT', 'n_tokens_mistral': 4008, 'n_tokens_neox': 3623, 'n_words': 965, 'path': 'notebooks/explore.ipynb', 'repo_name': 'discdiver/covid-cities', 'size': '10356'}