<jupyter_start><jupyter_text>Get Data on the PDF<jupyter_code>#date
# [top,left,bottom,width]
box = [2.3,1.5,3.3,18]
fc = 28.28
for i in range(0, len(box)):
   box[i] *= fc

deaths = tabula.read_pdf(pdf_path,pages=1,area=[box],multiple_tables=False)
today=deaths[0].columns[0]
today=today[26:]
today=pd.to_datetime(today,format='%m/%d/%Y')
today=today.date()
print(today)<jupyter_output>2020-12-08<jupyter_text>Get Total Deaths and Total Cases<jupyter_code># [top,left,bottom,width]
box1 = [3.5,1.5,9,11]
fc = 28.28
for i in range(0, len(box1)):
   box1[i] *= fc

total = tabula.read_pdf(pdf_path,pages=1,area=[box1],multiple_tables=False)
total[0].iloc[5]=total[0].columns
total[0].columns = [today,"Totals"]
total[0].set_index(today,inplace=True)


total[0].columns.name = total[0].index.name
total[0].index.name = None
totals=total[0]
totals['Totals'] = totals['Totals'].str.replace(',', '').astype(int)
totals<jupyter_output><empty_output><jupyter_text>Race of Cases Where Race is Known<jupyter_code># [top,left,bottom,width]
box2 = [11,1.6,16.5,11.5]
fc = 28.28
for i in range(0, len(box2)):
   box2[i] *= fc

data = tabula.read_pdf(pdf_path,pages=2,area=[box2],multiple_tables=False)
race_cases=data[0]

#find the percentage of total known
start = '('
end = '%'
s = data[0].columns[0]
perc=float((s.split(start))[1].split(end)[0])/100

race_cases.rename(columns={race_cases.columns[0]:'Race'}, inplace=True)

race_cases.set_index("Race",inplace=True)
race_cases.columns=["Percent"]
race_cases.columns
race_cases['Percent'] = race_cases['Percent'].str.replace('%', '').astype(float)
race_cases['Cases'] = np.floor(race_cases.Percent * totals.loc['Total Cases*','Totals']*perc/100)

race_cases.columns.name = race_cases.index.name
race_cases.index.name = None

race_cases.loc["Unknown","Cases"]= totals.loc['Total Cases*','Totals']-race_cases['Cases'].sum()
race_cases<jupyter_output><empty_output><jupyter_text>Ethnicity of Cases Where Ethnicity is Known<jupyter_code># [top,left,bottom,width]
box3 = [11,12,16,20]
fc = 28.28
for i in range(0, len(box3)):
   box3[i] *= fc

data_eth= tabula.read_pdf(pdf_path,pages=2,area=[box3],multiple_tables=False)
ethnicity_cases=data_eth[0]

#find the percentage of total known
start = '('
end = '%'
s = data_eth[0].columns[0]
perc=float((s.split(start))[1].split(end)[0])/100

ethnicity_cases.rename(columns={ethnicity_cases.columns[0]:'Ethnicity'}, inplace=True)
ethnicity_cases
ethnicity_cases.set_index("Ethnicity",inplace=True)
ethnicity_cases.columns=["Percent"]
ethnicity_cases['Percent'] = ethnicity_cases['Percent'].str.replace('%', '').astype(float)
ethnicity_cases['Cases'] = np.floor(ethnicity_cases.Percent * totals.loc['Total Cases*','Totals']*perc/100)

ethnicity_cases.columns.name = ethnicity_cases.index.name
ethnicity_cases.index.name = None

ethnicity_cases.loc["Unknown","Cases"]= totals.loc['Total Cases*','Totals']-ethnicity_cases['Cases'].sum()
ethnicity_cases<jupyter_output><empty_output><jupyter_text>Race of Deaths Where Race is Known<jupyter_code># [top,left,bottom,width]
box4 = [16.5,1.6,23,11]
fc = 28.28
for i in range(0, len(box4)):
   box4[i] *= fc

deaths = tabula.read_pdf(pdf_path,pages=2,area=[box4],multiple_tables=False)
deaths_race=deaths[0]

#find the percentage of total known
start = '('
end = '%'
s = deaths[0].columns[0]
perc=float((s.split(start))[1].split(end)[0])/100

deaths_race.rename(columns={deaths_race.columns[0]:'Race'}, inplace=True)

deaths_race.set_index("Race",inplace=True)
deaths_race.columns=["Percent"]
deaths_race['Percent'] = deaths_race['Percent'].str.replace('%', '').astype(float)
deaths_race['Deaths'] = np.floor(deaths_race.Percent * totals.loc['Total Deaths*','Totals']*perc/100)

deaths_race.columns.name = deaths_race.index.name
deaths_race.index.name = None

deaths_race.loc["Unknown","Deaths"]= totals.loc['Total Deaths*','Totals']-deaths_race['Deaths'].sum()
deaths_race<jupyter_output><empty_output><jupyter_text>Ethnicity of Deaths Where Ethnicity is Known<jupyter_code>from tabula import read_pdf
# [top,left,bottom,width]
box5 = [16,12,20,20]
fc = 28.28
for i in range(0, len(box5)):
   box5[i] *= fc

deaths_e = tabula.read_pdf(pdf_path,pages=2,area=[box5],multiple_tables=False)
deaths_ethnicity=deaths_e[0]

#find the percentage of total known
start = '('
end = '%'
s = deaths_e[0].columns[0]
perc=float((s.split(start))[1].split(end)[0])/100

deaths_ethnicity.rename(columns={deaths_ethnicity.columns[0]:"Ethnicity"}, inplace=True)

deaths_ethnicity.set_index("Ethnicity",inplace=True)
deaths_ethnicity.columns=["Percent"]
deaths_ethnicity['Percent'] = deaths_ethnicity['Percent'].str.replace('%', '').astype(float)
deaths_ethnicity['Deaths'] = np.floor(deaths_ethnicity.Percent * totals.loc['Total Deaths*','Totals']*perc/100)

deaths_ethnicity.columns.name = deaths_ethnicity.index.name
deaths_ethnicity.index.name = None

deaths_ethnicity.loc["Unknown","Deaths"]= totals.loc['Total Deaths*','Totals']-deaths_ethnicity['Deaths'].sum()
deaths_ethnicity<jupyter_output><empty_output><jupyter_text>Converting New Scraped Covid Data into Same Format as CRDT Table<jupyter_code>column_names = ["Date","State","Cases_Total","Cases_White","Cases_Black","Cases_LatinX","Cases_Asian",
                "Cases_AIAN","Cases_NHPI","Cases_Multiracial","Cases_Other","Cases_Unknown",
                "Cases_Ethnicity_Hispanic","Cases_Ethnicity_NonHispanic","Cases_Ethnicity_Unknown",
                "Deaths_Total","Deaths_White","Deaths_Black","Deaths_LatinX","Deaths_Asian",
               "Deaths_AIAN","Deaths_NHPI","Deaths_Multiracial","Deaths_Other","Deaths_Unknown",
               "Deaths_Ethnicity_Hispanic","Deaths_Ethnicity_NonHispanic","Deaths_Ethnicity_Unknown"]

#new_data is the dataframe for the new data which you just scraped converted into data format of Georgia_Historical
new_data = pd.DataFrame(columns = column_names)

new_data["Date"]=[today]
new_data["State"]=["KY"]
new_data["Cases_Total"]=[totals.loc["Total Cases*","Totals"]]
new_data["Cases_White"]=[race_cases.loc["White","Cases"]]
new_data["Cases_Black"]=[race_cases.loc["Black","Cases"]]
new_data["Cases_Asian"]=[race_cases.loc["Asian","Cases"]]
new_data["Cases_AIAN"]=[race_cases.loc["American Indian or Alaska Native","Cases"]]
new_data["Cases_NHPI"]=[race_cases.loc["Native Hawaiian or Other Pacific","Cases"]]
new_data["Cases_Unknown"]=[race_cases.loc["Unknown","Cases"]]
new_data["Cases_Ethnicity_Hispanic"]=[ethnicity_cases.loc["Hispanic","Cases"]]
new_data["Cases_Ethnicity_NonHispanic"]=[ethnicity_cases.loc["Non-Hispanic","Cases"]]
new_data["Cases_Ethnicity_Unknown"]=[ethnicity_cases.loc["Unknown","Cases"]]

new_data["Deaths_Total"]=[totals.loc["Total Deaths*","Totals"]]
new_data["Deaths_White"]=[deaths_race.loc["White","Deaths"]]
new_data["Deaths_Black"]=[deaths_race.loc["Black","Deaths"]]
new_data["Deaths_Asian"]=[deaths_race.loc["Asian","Deaths"]]
new_data["Deaths_AIAN"]=[deaths_race.loc["American Indian or Alaska Native","Deaths"]]
#new_data["Deaths_NHPI"]=[deaths_race.loc["Native Hawaiian or Other Pacific","Deaths"]]
new_data["Deaths_Multiracial"]=[deaths_race.loc["Multiracial","Deaths"]]
new_data["Deaths_Unknown"]=[deaths_race.loc["Unknown","Deaths"]]

new_data["Deaths_Ethnicity_Hispanic"]=[deaths_ethnicity.loc["Hispanic","Deaths"]]
new_data["Deaths_Ethnicity_NonHispanic"]=[deaths_ethnicity.loc["Non-Hispanic","Deaths"]]
new_data["Deaths_Ethnicity_Unknown"]=[deaths_ethnicity.loc["Unknown","Deaths"]]
new_data<jupyter_output><empty_output><jupyter_text>Data Validation<jupyter_code>#"Difference in sum of cases by race v. Total confirmed cases"
race_v_cases=race_cases['Percent'].sum()

#"Difference in sum of cases by ethnicity v. Total confirmed cases"
race_v_deaths=ethnicity_cases['Percent'].sum()

#"Difference in sum of deaths by race v. Total confirmed deaths"
ethnicity_v_cases=deaths_race['Percent'].sum()

#"Difference in sum of deaths by ethnicity v. Total confirmed deaths"
ethnicity_v_deaths=deaths_ethnicity['Percent'].sum()

data_validation= pd.DataFrame({"Difference in sum of cases by race v. Total confirmed cases":[race_v_cases], 
    "Difference in sum of deaths by race v. Total confirmed deaths":[race_v_deaths],
    "Difference in sum of cases by ethnicity v. Total confirmed cases":[ethnicity_v_cases],
    "Difference in sum of deaths by ethnicity v. Total confirmed deaths":[ethnicity_v_deaths]},index=['Data Validation']).T

#Red if values are not zero
data_validation.style.applymap(lambda x: 'background-color : red' if x>100.0 or x<=99.9 else '')<jupyter_output><empty_output><jupyter_text>Create a csv for today's scrpaer data<jupyter_code>#to make the CSV based for just this scraper
new_data.to_csv(r"C:\Users\wjg39\Documents\Desktop\CRDT\KY\\"+str(new_data["Date"].loc[0])+"_race-ethnicity_Kentucky.csv", index = False)<jupyter_output><empty_output><jupyter_text>Add today's scraper data to historical data<jupyter_code>#to append new data to the csv of all historical
new_data.to_csv(r'C:\Users\wjg39\Documents\Desktop\CRDT\KY\historical_race-ethnicity_Kentucky.csv', mode='a', header=False,index=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.16992745658386457, 'fraction_numerical': 0.020553967905034075, 'mean_word_length': 8.933406113537117, 'pattern_counts': {'":': 4, '<': 41, '<?xml version=': 0, '>': 41, 'https://': 0, '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': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '502823', 'license': 'MIT', 'n_tokens_mistral': 3738, 'n_tokens_neox': 3435, 'n_words': 489, 'path': 'Scraper - XLSX:Google Sheets:CSV/Kentucky_PDF_Scraper.ipynb', 'repo_name': 'BU-Spark/CARR-x-Spark-COVID-Racial-Data-Tracker', 'size': '9098'}