<jupyter_start><jupyter_text>Download Covid Data for Chapter XX from Sources, Downselect, and CombineThis notebook will download the data used in the Covid example in Chapter XX from the sources (with one exception), do the data down-selection, rename columns, combine into one dataframe, and write that dataframe out as a single CSV file. *(For those who are more familiar with Python and Pandas, a Pickle file would be easier to work with, but I want the reader of the book to gain experience in reading a CSV file into a Pandas dataframe and working with that)*  Cumulative Number of Covid Cases by Date from New York Times<jupyter_code># Download the Covid Data from the NYT github. Use the Raw URL to directly retrieve the data:
df=pd.read_csv("https://raw.githubusercontent.com/nytimes/covid-19-data/master/us-states.csv")
# Now restrict to just April 30, 2020
df=df[df["date"]=="2020-04-30"]
# Index by state so that we can easily merge with other data sets
df=df.set_index("state")
# Drop the data we won't need and limit to states
df.drop(["date", "fips","deaths"], axis=1, inplace=True)
df.drop(["District of Columbia", "Guam", "Northern Mariana Islands", "Puerto Rico", "Virgin Islands"],
       inplace=True)<jupyter_output><empty_output><jupyter_text>Population Data from the US Census Bureau<jupyter_code># Download population data from the US Census Bureau
# You will have to have the 'opynpyxl' library installed because this is an XLSX file
population = pd.read_excel(
     "https://www2.census.gov/programs-surveys/popest/tables/2010-2019/state/totals/nst-est2019-01.xlsx",
     skiprows=[0,1,2,4,5,6,7,8,60,61,62,63,64,65,66,67,68],
    engine='openpyxl'
)
# Rename the columns we care about
population.rename(columns={'Unnamed: 0':'state'}, inplace=True)
# Drop the columns we won't need
cols=population.columns
cols=cols.drop('state')
cols=cols.drop(2019)
print(cols)
population.drop(cols, axis=1, inplace=True)
# Remove the stray periods in the state names and make state the index
population["state"]=population["state"].str.replace(".","")
population.set_index("state", inplace=True)
# Combine the population data into our main data frame
df["population"]=population[2019]
## GDP Data from the US Department of Commerce Bureau of Economic Analysis
# The GDP data cannot be easily directly downloaded because it relies on JavaScript.
# Instead, I am going to provide a file, and you can substitute the CSV file from
# downloading from
gdp_url="https://apps.bea.gov/iTable/iTable.cfm?reqid=70&step=30&isuri=1&year_end=-1&download=csv&classification=non-industry&state=0&yearbegin=-1&unit_of_measure=levels&major_area=0&area=xx&year=2019&tableid=532&category=5532&area_type=0&statistic=3"
gdp=pd.read_csv("https://raw.githubusercontent.com/jmshea/Foundations-of-Data-Science-with-Python/main/03-first-data/state-gdp-bea.csv",
                skiprows=[0, 1, 2, 3, 5, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66])
# Drop columns we don't care about
gdp.drop(["GeoFips", "2019:Q1", "2019:Q2", "2019:Q3"], axis=1, inplace=True)
# Now rename the state column and set the index
gdp.rename(columns={"GeoName":"state"}, inplace=True)
gdp.set_index("state", inplace=True)

# Add the GDP data to our main data frame:
df["gdp"]=gdp["2019:Q4"]<jupyter_output><empty_output><jupyter_text>Urban/Rural Data from the US Census Bureau<jupyter_code># Download the data about urban populations from the US Census Bureau
urban=pd.read_excel("https://www2.census.gov/geo/docs/reference/ua/PctUrbanRural_State.xls")
# Drop the columns we don't care about
cols=urban.columns
cols=cols.drop('STATENAME')
cols=cols.drop('POPPCT_URBAN')
urban.drop(cols, axis=1, inplace=True)
# Rename the state columns to match our main dataframe and make it the index:
urban.rename(columns={"STATENAME":"state"}, inplace=True)
urban.set_index("state", inplace=True)
# Add the % urban data to our main data frame:
df["urban"]=urban["POPPCT_URBAN"]
# Output the main data frame to the screen to make sure it looks right
df<jupyter_output><empty_output><jupyter_text>Write the Data Back to CSV<jupyter_code># Here I write the data out to a CSV file so that the reader gains experience
# reading in and working with data frames created from CSV file
df.to_csv("covid-merged.csv")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.116795141322121, 'fraction_numerical': 0.03270263957019388, 'mean_word_length': 5.9288025889967635, 'pattern_counts': {'":': 2, '<': 17, '<?xml version=': 0, '>': 17, 'https://': 5, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 11, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '192705', 'license': 'FSFAP', 'n_tokens_mistral': 1510, 'n_tokens_neox': 1373, 'n_words': 455, 'path': '03-first-data/Download Covid Data.ipynb', 'repo_name': 'jmshea/intro-data-science-for-engineers', 'size': '4281'}