<jupyter_start><jupyter_text>Data sources+ The RAW_us_confirmed_cases.csv file from the Kaggle repository of John Hopkins University COVID-19 data.https://www.kaggle.com/antgoldbloom/covid19-data-from-john-hopkins-university?select=RAW_us_confirmed_cases.csv+ The CDC dataset of masking mandates by county.https://data.cdc.gov/Policy-Surveillance/U-S-State-and-Territorial-Public-Mask-Mandates-Fro/62d6-pm5i+ The New York Times mask compliance survey data.https://github.com/nytimes/covid-19-data/tree/master/mask-use+ CDC’s COVID Data Tracker provides COVID-19 vaccination data in the United Stateshttps://www.cdc.gov/coronavirus/2019-ncov/vaccines/distributing/reporting-counties.html<jupyter_code># load US confirmed cases data
df_us_confirmed_case = pd.read_csv('RAW_us_confirmed_cases.csv')
df_us_confirmed_case
# load US marks mandates data
df_us_mask_mandate = pd.read_csv('U.S._State_and_Territorial_Public_Mask_Mandates_From_April_10__2020_through_August_15__2021_by_County_by_Day.csv')
df_us_mask_mandate
# Load mark use survey data 
df_mask_use_by_county = pd.read_csv('mask-use-by-county.csv')
df_mask_use_by_county<jupyter_output><empty_output><jupyter_text>Data processing+ Data cleaning and drop unneeded columns+ Melt the confirm case data so each row represent confirmed case each day+ Standardize the FIPS column among the three datasets+ Filter out only Palm Beach,FL data(FIPS == '12099')<jupyter_code>state = 'Florida'
state_abbr = 'FL'
county = 'Palm Beach'
FIPS = 12099

# clean up data
# drop unneeded columns
df_us_confirmed_case_dropped = df_us_confirmed_case.drop(columns=['UID', 'iso2', 'iso3', 'code3', 'Country_Region', 'Lat', 'Long_', 'Combined_Key'])
df_us_confirmed_case_dropped=df_us_confirmed_case_dropped.fillna(0)
df_us_confirmed_case_dropped['FIPS'] =  df_us_confirmed_case_dropped.FIPS.apply(lambda x:  "{:05d}".format(int(float(x))))
df_us_confirmed_case_dropped
# Melt the confirm case data so each row represent confirmed case each day

id_vars = [
    'Province_State',
    'Admin2',
    'FIPS',
]

df_us_confirmed_case_transformed =pd.melt(df_us_confirmed_case_dropped, id_vars = id_vars, var_name ='date', value_name = "cases")
df_us_confirmed_case_transformed['date'] = pd.to_datetime(df_us_confirmed_case_transformed['date'])
df_us_confirmed_case_transformed
# Filter to only palm beach data
df_palm_beach_confirmed_case_transformed = df_us_confirmed_case_transformed[(df_us_confirmed_case_transformed['Admin2'] == county) & (df_us_confirmed_case_transformed['Province_State'] == state)].reset_index(drop=True)
df_palm_beach_confirmed_case_transformed
# create FIPS column for easier join
df_us_mask_mandate['FIPS_State'] =  df_us_mask_mandate.FIPS_State.apply(lambda x:  "{:02d}".format(int(x)))
df_us_mask_mandate['FIPS_County'] =  df_us_mask_mandate.FIPS_County.apply(lambda x:  "{:03d}".format(int(x)))
df_us_mask_mandate['FIPS'] = df_us_mask_mandate['FIPS_State'] +  df_us_mask_mandate['FIPS_County'] 
df_us_mask_mandate

# drop unneed columns
df_us_mask_mandate_dropped = df_us_mask_mandate.drop(columns=['FIPS_State', 'FIPS_County', 'order_code', 'Source_of_Action', 'URL', 'Citation'])
df_us_mask_mandate_dropped
# Filter to get palm beach mandate data
df_palm_beach_mask_mandate = df_us_mask_mandate_dropped[df_us_mask_mandate['County_Name'] == 'Palm Beach County'].reset_index(drop =True)
df_palm_beach_mask_mandate
# Palm beach shows all mandate data as NaN, indicate that there is no mandate happens
df_palm_beach_mask_mandate.Face_Masks_Required_in_Public.unique()
# Filter to get palm beach mark usage survey data
df_mask_use_palm_beach = df_mask_use_by_county[df_mask_use_by_county['COUNTYFP'] == FIPS].reset_index(drop =True)
df_mask_use_palm_beach
# Transform the mask usage data for better visualization
df_mask_use_palm_beach_transformed = df_mask_use_palm_beach.drop(columns=['COUNTYFP'])
df_mask_use_palm_beach_transformed =pd.melt(df_mask_use_palm_beach_transformed, var_name ='Response', value_name = "Proportion")
df_mask_use_palm_beach_transformed
# LOAD vaccinine data
df_vaccine = pd.read_csv('COVID-19_Vaccinations_in_the_United_States_County.csv')
df_vaccine

df_vaccine_palm_beach = df_vaccine[df_vaccine['FIPS'] == '12099'].reset_index(drop =True)
df_vaccine_palm_beach['Date'] = pd.to_datetime(df_vaccine_palm_beach['Date'])
df_vaccine_palm_beach
# Replace NA with zeros
df_vaccine_palm_beach=df_vaccine_palm_beach.fillna(0)
df_vaccine_palm_beach<jupyter_output><empty_output><jupyter_text>Data Visualization+ Estimated Prevalence of Mask Wearing in Palm Beach County, FL+ Time series visualize Accumulate Covid Cases, Palm Beach Country, FL+ Daily New Covid Cases & 7 days rolling average, Palm Beach Country, FL+ 7 days rolling average infection rate(Daily new cases / Population), Palm Beach Country, FL+ 7 days rolling average infection rate Diff, Palm Beach Country, FL<jupyter_code># Visualize the esitmated prevalence of mask wearing in Palm Beach County
fig, ax = plt.subplots(figsize=(15,10))
ax.bar(df_mask_use_palm_beach_transformed['Response'], df_mask_use_palm_beach_transformed['Proportion'], color  ='C0')

ax.set_title("Estimated Prevalence of Mask Wearing in Palm Beach County, FL")
ax.set_ylabel("Proportion")

plt.show()
# accumulated covid cases
fig, ax = plt.subplots(figsize=(15,10))
ax.plot(df_palm_beach_confirmed_case_transformed['date'], df_palm_beach_confirmed_case_transformed['cases'], color='C0')

ax.set_title("Accumulate Covid Cases, Palm Beach Country, FL")
ax.set_xlabel("Date")
ax.set_ylabel("Number of Covid Cases")

plt.show()
# daily new covid cases
df_palm_beach_confirmed_case_transformed['new_cases'] = df_palm_beach_confirmed_case_transformed['cases'].diff()
df_palm_beach_confirmed_case_transformed['new_cases_moving_average_7_days'] = df_palm_beach_confirmed_case_transformed['new_cases'].rolling(window=7).mean().round()
df_palm_beach_confirmed_case_transformed
# daily new covid cases
fig, ax = plt.subplots(figsize=(15,10))
ax.plot(df_palm_beach_confirmed_case_transformed['date'], df_palm_beach_confirmed_case_transformed['new_cases'], color='C0')
ax.plot(df_palm_beach_confirmed_case_transformed['date'], df_palm_beach_confirmed_case_transformed['new_cases_moving_average_7_days'], color='C1')


line0 = lines.Line2D([0], [0], label='Daily New COVID Cases', color='C0')
line1 = lines.Line2D([0], [0], label='7 days rolling average New COVID Cases', color='C1')
plt.legend(handles=[line0,line1])

ax.set_title("Daily New Covid Cases & 7 days rolling average, Palm Beach Country, FL")
ax.set_xlabel("Date")
ax.set_ylabel("Number of Covid Cases")

plt.show()
# infection rate = daily new case / population
# Palm Beach Population is 1492191 
population = 1492191
df_palm_beach_confirmed_case_transformed['daily_infection_rate'] = df_palm_beach_confirmed_case_transformed['new_cases'].apply(lambda x: x * 1.0 / population)
df_palm_beach_confirmed_case_transformed['daily_infection_rate_new_cases_moving_average_7_days'] = df_palm_beach_confirmed_case_transformed['new_cases_moving_average_7_days'].apply(lambda x: x * 1.0 / population)

df_palm_beach_confirmed_case_transformed
# daily new covid cases
fig, ax = plt.subplots(figsize=(15,10))
ax.plot(df_palm_beach_confirmed_case_transformed['date'], df_palm_beach_confirmed_case_transformed['daily_infection_rate_new_cases_moving_average_7_days'], color='C1')



line1 = lines.Line2D([0], [0], label='7 days rolling average infection rate', color='C1')

plt.legend(handles=[line1])

ax.set_title("7 days rolling average infection rate, Palm Beach Country, FL (Daily new cases / Population)")
ax.set_xlabel("Date")
ax.set_ylabel("Infection Rate")

plt.show()
# Create chart of daily infection rate diff

df_palm_beach_confirmed_case_transformed['daily_infection_rate_diff'] =df_palm_beach_confirmed_case_transformed['daily_infection_rate'].diff()
df_palm_beach_confirmed_case_transformed['daily_infection_rate_new_cases_moving_average_7_days_diff'] =df_palm_beach_confirmed_case_transformed['daily_infection_rate_new_cases_moving_average_7_days'].diff()

df_palm_beach_confirmed_case_transformed
fig, ax = plt.subplots(figsize=(15,10))
ax.plot(df_palm_beach_confirmed_case_transformed['date'], df_palm_beach_confirmed_case_transformed['daily_infection_rate_new_cases_moving_average_7_days_diff'], color='C1')



line1 = lines.Line2D([0], [0], label='7 days rolling average infection rate Diff', color='C1')

plt.legend(handles=[line1])

ax.set_title("7 days rolling average infection rate diff, Palm Beach Country, FL")
ax.set_xlabel("Date")
ax.set_ylabel("Infection Rate Diff")

plt.show()<jupyter_output><empty_output><jupyter_text>Data Analysis+ As we can see, Palm Beach's Infection rate has no much change, specially using the 7 days rolling average visualization+ Since Palm Beach county doesn't have mandate mask policy, try to find another county with similar mask wearing data but with have mark mandate policy+ Using Mask-Wearing Survey Data to look for another city with the similar mask wearing %+ Found a similar county(Spotsylvania County, VA) which has similar mask wearing %, specially "ALWAYS == 0.785"<jupyter_code># Spotsylvania County, VA (FIPS_other = 51177)
FIPS_other = 51177
df_mask_use_other = df_mask_use_by_county[df_mask_use_by_county['COUNTYFP'] == FIPS_other].reset_index(drop =True)
df_mask_use_other
# Repeat the same data processing as Palm Beach, FL
df_spotsylvania_mask_mandate = df_us_mask_mandate_dropped[df_us_mask_mandate['FIPS'] == "{:05d}".format(FIPS_other)].reset_index(drop =True)
df_spotsylvania_mask_mandate
df_spotsylvania_mask_mandate.Face_Masks_Required_in_Public.unique()

df_spotsylvania_mask_mandate_yes = df_spotsylvania_mask_mandate[df_spotsylvania_mask_mandate['Face_Masks_Required_in_Public'] == 'Yes'].reset_index(drop =True)
df_spotsylvania_mask_mandate_yes
# Get Spotsylvania confirmed case data
df_spotsylvania_confirmed_case_transformed = df_us_confirmed_case_transformed[(df_us_confirmed_case_transformed['FIPS'] ==   "{:05d}".format(FIPS_other))].reset_index(drop=True)
df_spotsylvania_confirmed_case_transformed
# Spotsylvania County's population 136215
Population_other = 136215
# Get Spotsylvania new case and daily infection rate 7 day moving average metrics
df_spotsylvania_confirmed_case_transformed['new_cases'] = df_spotsylvania_confirmed_case_transformed['cases'].diff()
df_spotsylvania_confirmed_case_transformed['new_cases_moving_average_7_days'] = df_spotsylvania_confirmed_case_transformed['new_cases'].rolling(window=7).mean().round()
df_spotsylvania_confirmed_case_transformed['daily_infection_rate'] = df_spotsylvania_confirmed_case_transformed['new_cases'].apply(lambda x: x * 1.0 / Population_other)
df_spotsylvania_confirmed_case_transformed['daily_infection_rate_new_cases_moving_average_7_days'] = df_spotsylvania_confirmed_case_transformed['new_cases_moving_average_7_days'].apply(lambda x: x * 1.0 / Population_other)
df_spotsylvania_confirmed_case_transformed['daily_infection_rate_diff'] =df_spotsylvania_confirmed_case_transformed['daily_infection_rate'].diff()
df_spotsylvania_confirmed_case_transformed['daily_infection_rate_new_cases_moving_average_7_days_diff'] =df_spotsylvania_confirmed_case_transformed['daily_infection_rate_new_cases_moving_average_7_days'].diff()
df_spotsylvania_confirmed_case_transformed
df_palm_beach_confirmed_case_transformed.columns
# Join the Palm beach data and Spotsylvania data for plot chart to compare
joine_df=df_palm_beach_confirmed_case_transformed.merge(df_spotsylvania_confirmed_case_transformed, left_on='date', right_on='date',
          suffixes=('_palm_beach', '_spotsylvania'))

joine_df.columns
# Chart for 7 days rolling average infection rate diff between Palm beach and Spotsylvania
    
    
fig, ax = plt.subplots(figsize=(15,10))
ax.plot(joine_df['date'], joine_df['daily_infection_rate_new_cases_moving_average_7_days_diff_palm_beach'], color='C0')
ax.plot(joine_df['date'], joine_df['daily_infection_rate_new_cases_moving_average_7_days_diff_spotsylvania'], color='C1')


line0 = lines.Line2D([0], [0], label='Change in 7 days rolling average infection rate Palm Beach,FL', color='C0')
line1 = lines.Line2D([0], [0], label='Change in 7 days rolling average infection rate Spotsylvania, TX', color='C1')
span = ax.axvspan(date2num(datetime(2020,5,29)), date2num(datetime(2021,5,14)),color="C2", label = 'mask mandate in Spotsylvania Country')


plt.legend(handles=[
    line0,
    line1,
span])

ax.set_title("Change in 7 days rolling average infection rate diff, Palm Beach, FL vs Spotsylvania, VA")
ax.set_xlabel("Date")
ax.set_ylabel("Infection Rate Diff")

plt.show()

# Chart of 7 days roliing average infection rate between Palm Beach and Spotsylavania

    
    
fig, ax = plt.subplots(figsize=(15,10))
ax.plot(joine_df['date'], joine_df['daily_infection_rate_new_cases_moving_average_7_days_palm_beach'], color='C0')
ax.plot(joine_df['date'], joine_df['daily_infection_rate_new_cases_moving_average_7_days_spotsylvania'], color='C1')


line0 = lines.Line2D([0], [0], label='7 days rolling average infection rate Palm Beach', color='C0')
line1 = lines.Line2D([0], [0], label='7 days rolling average infection rate Spotsylvania', color='C1')
span = ax.axvspan(date2num(datetime(2020,5,29)), date2num(datetime(2021,5,14)),color="C2", label = 'mask mandate in Spotsylvania Country')


plt.legend(handles=[
    line0,
    line1,
span])

ax.set_title("7 days rolling average infection rate, Palm Beach, FL vs Spotsylvania, VA")
ax.set_xlabel("Date")
ax.set_ylabel("Infection Rate")

plt.show()

df_join_vaccine_data =df_palm_beach_confirmed_case_transformed.merge(df_vaccine_palm_beach, how="left", left_on='date', right_on='Date',
          suffixes=('', '_vaccine'))

df_join_vaccine_data=df_join_vaccine_data.fillna(0)
df_join_vaccine_data
# Chart for 7 days moving average infection rate and vaccine rate at Palm Beach
    
    
fig, ax = plt.subplots(figsize=(15,10))
ax.plot(df_join_vaccine_data['date'], df_join_vaccine_data['daily_infection_rate_new_cases_moving_average_7_days'], color='C0')
ax.set_title("7 days rolling average infection rate and Vaccine rate at Palm Beach, FL")
ax.set_xlabel("Date")
ax.set_ylabel("Infection Rate", color = 'C0')
ax.tick_params(axis='y', labelcolor='C0')

ax2 = ax.twinx()  # instantiate a second axes that shares the same x-axis
ax2.plot(df_join_vaccine_data['date'], df_join_vaccine_data['Series_Complete_Pop_Pct'], color='C1')
ax2.set_ylabel("Vaccine Rate", color = 'C1')
ax2.tick_params(axis='y', labelcolor='C1')

line0 = lines.Line2D([0], [0], label='7 days rolling average infection rate Palm Beach', color='C0')
line1 = lines.Line2D([0], [0], label='Vaccine rate Palm Beach', color='C1')

plt.legend(handles=[
    line0,
    line1])


plt.show()<jupyter_output><empty_output><jupyter_text>Research Questions:Based on the data visualization "7 days rolling average infection rate and Vaccine rate at Palm Beach, FL", we can see when the Cov-19 vaccine started at 2021-01, the infection rate went down until Delta variant came to the picture around 2021-07.NULL Hypotheis: There is no correlation between Vaccine Rate and Daily Infection Rate of 7 days rolling average Considering Delta Variant Impact: + Jul 2021: DELTA variant started https://www.newsweek.com/first-us-covid-delta-variant-cases-how-did-it-mutate-1617871+ Other impact facts: Summer break and Nationwide reopening, etc.Therefore, I splitted the data to 3 time period to find the correlation between Infections Rate and Vaccine Rate.+ Before 2021-01 Vaccine started+ 2021-01 to 2021-06 Vaccine rate increase and infection rate decrease+ 2021-06 Vaccine didn’t help a lot for Delta variant<jupyter_code># check correlation before vacacine available

before_vacacine = df_join_vaccine_data.loc[(df_join_vaccine_data['date'] > '2020-1-21') & (df_join_vaccine_data['date'] < '2020-12-31')]
filterd_df_before_vacacine = before_vacacine[['daily_infection_rate_new_cases_moving_average_7_days','Series_Complete_Pop_Pct']]
filterd_df_before_vacacine

# Find correlation for 7 day moving average infection rate and vaccine rate before Vaccine started
correlations_before_vacacine = filterd_df_before_vacacine.corr()
correlations_before_vacacine
# Use heatmap to visualize
sns.heatmap(correlations_before_vacacine)
plt.show()

# as expected, there is no correclation
# Find correlation for 7 day rolling average infection rate and vaccine rate after vacacine available and before DELTA spike

after_vacacine = df_join_vaccine_data.loc[(df_join_vaccine_data['date'] > '2021-1-1') & (df_join_vaccine_data['date'] < '2021-6-30')]
filterd_df_after_vacacine = after_vacacine[['daily_infection_rate_new_cases_moving_average_7_days','Series_Complete_Pop_Pct']]
filterd_df_after_vacacine

correlations_after_vacacine = filterd_df_after_vacacine.corr()
correlations_after_vacacine


sns.heatmap(correlations_after_vacacine)
plt.show()

# we can see there's very strong negative correlation between Infection Rate and Vaccine Rate
# check correlation after DELTA variant 2021-07-01

after_delta = df_join_vaccine_data.loc[(df_join_vaccine_data['date'] > '2021-7-1') & (df_join_vaccine_data['date'] < '2022-1-1')]
filterd_df_after_delta = after_delta[['daily_infection_rate_new_cases_moving_average_7_days','Series_Complete_Pop_Pct']]
filterd_df_after_delta
# check correlation after delta
correlations_after_delta = filterd_df_after_delta.corr()
correlations_after_delta
sns.heatmap(correlations_after_delta)
plt.show()

# we can see very slight negative correlation between Infection Rate and Vacine Rate after Delta variant
# Use Linear regression to find the correlation bewteen daily infection rate 7 days moving average and vaccine rate based on 
# data between Vaccine available and before DETLA started

# The feature is the vaccine rate, the predicted value is the daily infection rate of new case moving average 7 days
after_vac = df_join_vaccine_data.loc[(df_join_vaccine_data['date'] > '2021-1-1') & (df_join_vaccine_data['date'] < '2021-6-30')]

vaccine_rate = after_vac['Series_Complete_Pop_Pct']
# Transform the 1d array so it become a single feature input
vaccine_rate = vaccine_rate.to_numpy().T.reshape(-1, 1)

infection_rate = after_vac['daily_infection_rate_new_cases_moving_average_7_days']
# Convert the infection rate to percentage and make it an 1D array
infection_rate = infection_rate.to_numpy().T * 100


# Train a linear regression model using the vaccine rate and infection rate and get the model's coefficient 
model = LinearRegression().fit(vaccine_rate, infection_rate)
model.coef_
# negative coefficient means if vaccination rate increase, infection rate will decreased
# Our NULL Hypothesis: There is no correlation between vaccine rate and infection ratet
# Use Ordinary Least Squares by Call SM.OLS to get p_value 
x = vaccine_rate
y = infection_rate
x2 = sm.add_constant(x)
est = sm.OLS(y, x2)
model = est.fit()
print(model.summary())<jupyter_output>OLS Regression Results                            
==============================================================================
Dep. Variable:                      y   R-squared:                       0.851
Model:                            OLS   Adj. R-squared:                  0.850
Method:                 Least Squares   F-statistic:                     1009.
Date:                Sun, 12 Dec 2021   Prob (F-statistic):           4.97e-75
Time:                        21:56:25   Log-Likelihood:                 662.16
No. Observations:                 179   AIC:                            -1320.
Df Residuals:                     177   BIC:                            -1314.
Df Model:                           1                                         
Covariance Type:            nonrobust                                         
==============================================================================
                 coef    std err          t      P>|[...]
{'fraction_non_alphanumeric': 0.10311352547658569, 'fraction_numerical': 0.024043056184296564, 'mean_word_length': 6.76640625, 'pattern_counts': {'":': 0, '<': 24, '<?xml version=': 0, '>': 25, 'https://': 5, 'lorem ipsum': 0, 'www.': 3, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 3, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '245769', 'license': 'MIT', 'n_tokens_mistral': 7145, 'n_tokens_neox': 6440, 'n_words': 1437, 'path': 'hcds-final-project.ipynb', 'repo_name': 'IvyLinMS/data-512-final-project', 'size': '19881'}