<jupyter_start><jupyter_text>Defining Variables Needed For Data Quality Checks<jupyter_code>#Current Data Checks for MA

# - Difference in sum of cases by race v. Total confirmed cases (should be equal)

# - Difference in sum of deaths by race v. Total deaths (should be equal)

# - Difference in sum of hopsital by hospital by race versus Total hospital (should be equal)


#Variables needed
# 1) Sum of cases by Race
# 2) Sum of Deaths by Race
# 3) Sum of hospital by Race
# 4) Total Hospital
# 5) Total Cases
# 6) Total Deaths<jupyter_output><empty_output><jupyter_text>Downloading the Public CSV from CRDT<jupyter_code>public_csv = pd.read_csv("https://docs.google.com/spreadsheets/d/e/2PACX-1vS8SzaERcKJOD_EzrtCDK1dX1zkoMochlA9iHoHg_RSw3V8bkpfk1mpw4pfL5RdtSOyx_oScsUtyXyk/pub?gid=43720681&single=true&output=csv")<jupyter_output><empty_output><jupyter_text>Defining MA Covid Summary from the Public CSV<jupyter_code>MA_covid_summary = public_csv[public_csv['State']== 'MA']
MA_covid_summary<jupyter_output><empty_output><jupyter_text>Finding Columns with NaN Values<jupyter_code>#Decided not drop Columns with NaN values because feel that the knowledge that the data is missing is pertentient

nan_values = MA_covid_summary.isna()
nan_columns = nan_values.any()

columns_with_nan = MA_covid_summary.columns[nan_columns].tolist()
print(columns_with_nan)

#Seems that MA has just started reporting hospital data with ethnicity, race and unknown<jupyter_output>['Cases_AIAN', 'Cases_NHPI', 'Cases_Multiracial', 'Deaths_AIAN', 'Deaths_NHPI', 'Deaths_Multiracial', 'Hosp_Total', 'Hosp_White', 'Hosp_Black', 'Hosp_LatinX', 'Hosp_Asian', 'Hosp_AIAN', 'Hosp_NHPI', 'Hosp_Multiracial', 'Hosp_Other', 'Hosp_Unknown', 'Hosp_Ethnicity_Hispanic', 'Hosp_Ethnicity_NonHispanic', 'Hosp_Ethnicity_Unknown', 'Tests_Total', 'Tests_White', 'Tests_Black', 'Tests_LatinX', 'Tests_Asian', 'Tests_AIAN', 'Tests_NHPI', 'Tests_Multiracial', 'Tests_Other', 'Tests_Unknown', 'Tests_Ethnicity_Hispanic', 'Tests_Ethnicity_NonHispanic', 'Tests_Ethnicity_Unknown']<jupyter_text>Creating Total Cases,  Total Cases by Race and Total Cases by Ethnicity variables<jupyter_code>#Most recent Total Cases Reported in Maine
total_cases = MA_covid_summary['Cases_Total'].iloc[0:1]

#Have to convert into a float in order to perform operations on total
total_cases = total_cases.astype(float)

print("Total Cases in Massachusetts as of Today")
print(total_cases)


#Need to find case totals for each race (including other and unknown)

cases_white = MA_covid_summary['Cases_White'].iloc[0:1]
cases_white = cases_white.astype(float)


cases_latinx = MA_covid_summary['Cases_LatinX'].iloc[0:1]
cases_latinx = cases_latinx.astype(float)

cases_asian = MA_covid_summary['Cases_Asian'].iloc[0:1]
cases_asian = cases_asian.astype(float)

cases_other = MA_covid_summary['Cases_Other'].iloc[0:1]
cases_other = cases_other.astype(float)

cases_black = MA_covid_summary['Cases_Black'].iloc[0:1]
cases_black = cases_black.astype(float)

cases_unknown = MA_covid_summary['Cases_Unknown'].iloc[0:1]
cases_unknown = cases_unknown.astype(float)


total_cases_race = cases_white + cases_black + cases_latinx + cases_asian + cases_other + cases_unknown
print("Total Cases with Race in Massachusetts")
print(total_cases_race)

#Need to find case totals for each ethnicity (including other and unknown)

cases_ethnicity_hispanic = MA_covid_summary['Cases_Ethnicity_Hispanic'].iloc[0:1]
cases_ethnicity_hispanic = cases_ethnicity_hispanic.astype(float)

cases_ethnicity_nonhispanic = MA_covid_summary['Cases_Ethnicity_NonHispanic'].iloc[0:1]
cases_ethnicity_nonhispanic = cases_ethnicity_nonhispanic.astype(float)

cases_ethnicity_unknown = MA_covid_summary['Cases_Ethnicity_Unknown'].iloc[0:1]
cases_ethnicity_unknown = cases_ethnicity_unknown.astype(float)

total_cases_ethnicity = cases_ethnicity_hispanic + cases_ethnicity_nonhispanic + cases_ethnicity_unknown
print("Total Cases with Ehtnicity in Massachusetts")
print(total_cases_ethnicity)<jupyter_output>Total Cases in Massachusetts as of Today
21    323531.0
Name: Cases_Total, dtype: float64
Total Cases with Race in Massachusetts
21    323531.0
dtype: float64
Total Cases with Ehtnicity in Massachusetts
21    323531.0
dtype: float64<jupyter_text>Creating Total Deaths,  Total Deaths by Race and Total Deaths by Ethnicity variables<jupyter_code>#Most recent Total Cases Reported in Maine
total_deaths = MA_covid_summary['Deaths_Total'].iloc[0:1]

#Have to convert into a float in order to perform operations on total
total_deaths = total_deaths.astype(float)

#Printing out Total Deaths
print("Total Deaths in Massachusetts as of Today")
print(total_deaths)


#Need to find death totals for each race (including other and unknown)

deaths_white = MA_covid_summary['Deaths_White'].iloc[0:1]
deaths_white = deaths_white.astype(float)

deaths_black = MA_covid_summary['Deaths_Black'].iloc[0:1]
deaths_black = deaths_black.astype(float)

deaths_latinx = MA_covid_summary['Deaths_LatinX'].iloc[0:1]
deaths_latinx = deaths_latinx.astype(float)

deaths_asian = MA_covid_summary['Deaths_Asian'].iloc[0:1]
deaths_asian = deaths_asian.astype(float)

deaths_other = MA_covid_summary['Deaths_Other'].iloc[0:1]
deaths_other = deaths_other.astype(float)

deaths_unknown = MA_covid_summary['Deaths_Unknown'].iloc[0:1]
deaths_unknown = deaths_unknown.astype(float)


total_deaths_race = deaths_white + deaths_black + deaths_latinx + deaths_asian + deaths_other + deaths_unknown

print("Total Deaths with Race in Massachusetts")
print(total_deaths_race)


#Need to find death totals for each ethnicity (including other and unknown)

deaths_ethnicity_hispanic = MA_covid_summary['Deaths_Ethnicity_Hispanic'].iloc[0:1]
deaths_ethnicity_hispanic = deaths_ethnicity_hispanic.astype(float)

deaths_ethnicity_nonhispanic = MA_covid_summary['Deaths_Ethnicity_NonHispanic'].iloc[0:1]
deaths_ethnicity_nonhispanic = deaths_ethnicity_nonhispanic.astype(float)

deaths_ethnicity_unknown = MA_covid_summary['Deaths_Ethnicity_Unknown'].iloc[0:1]
deaths_ethnicity_unknown = deaths_ethnicity_unknown.astype(float)

total_deaths_ethnicity = deaths_ethnicity_hispanic + deaths_ethnicity_nonhispanic + deaths_ethnicity_unknown

print("Total Deaths with Ethnicity in Massachusetts")
print(total_deaths_ethnicity)<jupyter_output>Total Deaths in Massachusetts as of Today
21    11717.0
Name: Deaths_Total, dtype: float64
Total Deaths with Race in Massachusetts
21    11717.0
dtype: float64
Total Deaths with Ethnicity in Massachusetts
21    11717.0
dtype: float64<jupyter_text>Creating Total Hospitalizations,  Total Hospitalizations by Race and Total Hospitalizations by Ethnicity variables<jupyter_code>#Most recent Total Cases Reported in Maine
total_hosp = MA_covid_summary['Hosp_Total'].iloc[0:1]

#Have to convert into a float in order to perform operations on total
#total_hosp = total_hosp.astype(float)

#Printing out Total Hospitalizations
print("Total Hospitalizations in Massachusetts as of Today")
print(total_hosp)


#Need to find Hospitalizations totals for each race (including other and unknown)

hosp_white = MA_covid_summary['Hosp_White'].iloc[0:1]
hosp_white = hosp_white.astype(float)

hosp_black = MA_covid_summary['Hosp_Black'].iloc[0:1]
hosp_black = hosp_black.astype(float)

hosp_latinx = MA_covid_summary['Hosp_LatinX'].iloc[0:1]
hosp_latinx = hosp_latinx.astype(float)

hosp_asian = MA_covid_summary['Hosp_Asian'].iloc[0:1]
hosp_asian = hosp_asian.astype(float)

hosp_other = MA_covid_summary['Hosp_Other'].iloc[0:1]
hosp_other = hosp_other.astype(float)

hosp_unknown = MA_covid_summary['Hosp_Unknown'].iloc[0:1]
hosp_unknown = hosp_unknown.astype(float)


total_hosp_race = hosp_white + hosp_black + hosp_latinx + hosp_asian + hosp_other + hosp_unknown

print("Total Hospitalizations with Race in Massachusetts")
print(total_hosp_race)


#Need to find Hospitalizations totals for each ethnicity (including other and unknown)

hosp_ethnicity_hispanic = MA_covid_summary['Hosp_Ethnicity_Hispanic'].iloc[0:1]
hosp_ethnicity_hispanic = hosp_ethnicity_hispanic.astype(float)

hosp_ethnicity_nonhispanic = MA_covid_summary['Hosp_Ethnicity_NonHispanic'].iloc[0:1]
hosp_ethnicity_nonhispanic = hosp_ethnicity_nonhispanic.astype(float)

hosp_ethnicity_unknown = MA_covid_summary['Hosp_Ethnicity_Unknown'].iloc[0:1]
hosp_ethnicity_unknown = hosp_ethnicity_unknown.astype(float)

total_hosp_ethnicity = hosp_ethnicity_hispanic + hosp_ethnicity_nonhispanic + hosp_ethnicity_unknown

print("Total Hospitalizations with Ethnicity in Massachusetts")
print(total_hosp_ethnicity)<jupyter_output>Total Hospitalizations in Massachusetts as of Today
21    15372.0
Name: Hosp_Total, dtype: float64
Total Hospitalizations with Race in Massachusetts
21    15372.0
dtype: float64
Total Hospitalizations with Ethnicity in Massachusetts
21    15372.0
dtype: float64<jupyter_text>Data Quality Checking Clause<jupyter_code>#Checking if Total Cases and Cases with Race are equal.
#If not equal will print an error statment.
if ((total_cases - total_cases_race) != 0).bool():
    print("Data Quality Check Needed for Cases with Race.")

    
#Checking if Total Cases and Cases with Ethnicity are equal.  
#If not equal will print an error statment.   
if ((total_cases - total_cases_ethnicity) != 0).bool():
    print("Data Quality Check Needed for Cases with Ethnicity.")
    
#Checking if Total Deaths and Deaths with Race are equal.
#If not equal will print an error statment.
if ((total_deaths - total_deaths_race) != 0).bool():
    print("Data Quality Check Needed for Deaths with Race.")
    
#Checking if Total Deaths and Deaths with Ethnicity are equal.
#If not equal will print an error statment.
if ((total_deaths - total_deaths_ethnicity) != 0).bool():
    print("Data Quality Check Needed for Deaths with Ethnicity.")
    
#Checking if Total Hospitalizations and Hospitalizations with Race are equal.
#If not equal will print an error statment.
if ((total_hosp - total_hosp_race) != 0).bool():
    print("Data Quality Check Needed for Hospitalizations with Race.")

    
#Checking if Total Hospitalizations and Hospitalizations with Ethnicity are equal.  
#If not equal will print an error statment.   
if ((total_hosp - total_hosp_ethnicity) != 0).bool():
    print("Data Quality Check Needed for Hospitalizations with Ethnicity.")<jupyter_output><empty_output><jupyter_text>Creating a Case Each Day Column to use in Time Series Plots<jupyter_code>#In order to find Total Cases Each Day need to subtract next row from previous row
#However first need to convert column type to float
MA_covid_summary['Cases_Total'] = MA_covid_summary['Cases_Total'].astype(float)
cases_each_day = MA_covid_summary['Cases_Total'].diff(periods=-1)

#Adding Cases Each Day as a column
MA_covid_summary['Cases Each Day'] = cases_each_day

#Maybe change cases each day to cases each week<jupyter_output><ipython-input-13-90b3d464feab>:3: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  MA_covid_summary['Cases_Total'] = MA_covid_summary['Cases_Total'].astype(float)
<ipython-input-13-90b3d464feab>:7: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  MA_covid_summary['Cases Each Day'] = cases_each_day<jupyter_text>Creating a Date Time Column to use In Time Series Plot<jupyter_code>#Structuring the Date to be in YYYY/MM/DD format
date_strings = []

for dates in MA_covid_summary['Date'].astype(str):
    date_formatted = dates[0:4] + "/" + dates[4:6] + "/" + dates[6:8]
    date_strings.append(date_formatted)
    
MA_covid_summary["Date Time"] = date_strings

MA_covid_summary['Date Time'] =  pd.to_datetime(MA_covid_summary['Date Time'], format='%Y/%m/%d')<jupyter_output><ipython-input-14-9a23d22c2f67>:8: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  MA_covid_summary["Date Time"] = date_strings
<ipython-input-14-9a23d22c2f67>:10: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  MA_covid_summary['Date Time'] =  pd.to_datetime(MA_covid_summary['Date Time'], format='%Y/%m/%d')<jupyter_text>Creating a Deaths Each Day Column to use in Time Series Plots<jupyter_code>#Apply the same method
MA_covid_summary['Deaths_Total'] = MA_covid_summary['Deaths_Total'].astype(float)
deaths_each_day = MA_covid_summary['Deaths_Total'].diff(periods=-1)


#Adding Cases Each Day as a column
MA_covid_summary['Deaths Each Day'] = deaths_each_day<jupyter_output><ipython-input-19-636c878b7db4>:2: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  MA_covid_summary['Hosp_Total'] = MA_covid_summary['Hosp_Total'].astype(float)
<ipython-input-19-636c878b7db4>:7: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  MA_covid_summary['Hosp Each Day'] = hosp_each_day<jupyter_text>Creating a Hospitalizations Each Day Column to use in Time Series Plots<jupyter_code>#Apply the same method
MA_covid_summary['Hosp_Total'] = MA_covid_summary['Hosp_Total'].astype(float)
hosp_each_day = MA_covid_summary['Hosp_Total'].diff(periods=-1)


#Adding Cases Each Day as a column
MA_covid_summary['Hosp Each Day'] = hosp_each_day<jupyter_output><ipython-input-20-636c878b7db4>:2: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  MA_covid_summary['Hosp_Total'] = MA_covid_summary['Hosp_Total'].astype(float)
<ipython-input-20-636c878b7db4>:7: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  MA_covid_summary['Hosp Each Day'] = hosp_each_day<jupyter_text>Time Series Plots<jupyter_code>MA_covid_summary.set_index('Date Time')['Cases Each Day'].plot(figsize=(12, 10), title='Cases Each Day: Massachusetts'
                                                                        ,linewidth=2.5, color='green')


MA_covid_summary.set_index('Date Time')['Deaths Each Day'].plot(figsize=(12, 10), title='Deaths Each Day: Massachusetts'
                                                                        ,linewidth=2.5, color='red')



MA_covid_summary.set_index('Date Time')['Hosp Each Day'].plot(figsize=(12, 10), title='Hospitalizations Each Day: Massachusetts'
                                                                        ,linewidth=2.5, color='blue')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09297805642633229, 'fraction_numerical': 0.018557993730407524, 'mean_word_length': 6.29355281207133, 'pattern_counts': {'":': 0, '<': 53, '<?xml version=': 0, '>': 53, 'https://': 9, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 10, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '13', 'dir': 'jupyter-structured-clean-dedup', 'id': '306502', 'license': 'MIT', 'n_tokens_mistral': 5720, 'n_tokens_neox': 5435, 'n_words': 1290, 'path': 'Scraper - XLSX:Google Sheets:CSV/Data_Quality_Checks_MA.ipynb', 'repo_name': 'BU-Spark/CARR-x-Spark-COVID-Racial-Data-Tracker', 'size': '15950'}