<jupyter_start><jupyter_text>HTML code for website tables<jupyter_code>state_table = pd.read_sql_query('select * from state order by id', con=engine).to_html
vaccine_table = pd.read_sql_query('select * from vaccine order by id', con=engine).to_html
allocation_table = pd.read_sql_query('select * from allocation order by id', con=engine).to_html
# state_table()<jupyter_output><empty_output><jupyter_text>Visualizations<jupyter_code># Query all allocations by State
state_allocation_df = pd.read_sql_query("SELECT s.state, s.population\
                                          ,SUM(CASE WHEN v.req_doses = 2 THEN a.dose2\
                                                    WHEN v.req_doses = 1 THEN a.dose1 END) as full_doses\
                                          ,SUM(CASE WHEN v.req_doses = 2 THEN a.dose1-a.dose2\
                                                    WHEN v.req_doses = 1 THEN 0 END) as partial_doses\
                                      FROM allocation a\
                                      INNER JOIN vaccine v ON a.vaccine_id = v.id\
                                      INNER JOIN state s ON a.state_id = s.id\
                                      WHERE state NOT IN ('District of Columbia', 'Puerto Rico')\
                                      GROUP BY s.state, s.population"
                                  , con=engine)

# Query max week from allocations table
as_of_date = pd.read_sql_query('SELECT MAX(week) AS week FROM allocation', con=engine)
as_of_date = as_of_date['week'][0]
as_of_date = as_of_date.strftime('%m/%d/%Y')
as_of_date<jupyter_output><empty_output><jupyter_text>Allocations by State ( of Full Doses)<jupyter_code># Format dataframe for plotting
state_df = state_allocation_df[['state', 'full_doses']]
state_df = state_df.rename(columns={'state':'State','full_doses':'Full Doses Allocated'}).sort_values('State')
state_df.head()
# Plot Bar chart using pyplot
source = state_df
plt.figure(figsize =(12, 4))
x_axis = np.arange(len(source))
x_ticks = list(source['State'])
y_values = source['Full Doses Allocated'].tolist()
plt.bar(x_axis, y_values, width= .5, color='b', align='center')
tick_locations = [value for value in x_axis]
plt.xticks(tick_locations, x_ticks, rotation = 90)
plt.xlabel("States")
plt.ylabel('Vaccines Allocated')
plt.xlim(-0.5, len(x_axis)-0.5)
# plt.ylim(0,100)
plt.title(f'Allocations of Covid Vaccine as of {as_of_date}')
plt.savefig('Images/state_allocations_count.png', bbox_inches="tight", facecolor='white', pad_inches=0.5)
plt.tight_layout()
plt.show()<jupyter_output><empty_output><jupyter_text>Allocations by State (Full Doses as % of Population)<jupyter_code># Format dataframe for plotting
state_pct_df = state_allocation_df

# Calculate percent of pupulation
state_pct_df['pct_full'] = state_pct_df['full_doses']/state_pct_df['population']*100
# Data shows 0 partial doses. Removing column from dataframe.
# state_pct_df['pct_partial'] = state_pct_df['partial_doses']/state_pct_df['population']*100

state_pct_df = state_pct_df[['state', 'pct_full']]
state_pct_df = state_pct_df.rename(columns={'state':'State','pct_full':'Allocation Rate'}).sort_values('State')
state_pct_df.head()
# Plot Bar chart using pyplot
source = state_pct_df
plt.figure(figsize =(12, 4))
x_axis = np.arange(len(source))
x_ticks = list(source['State'])
y_values = source['Allocation Rate'].tolist()
plt.bar(x_axis, y_values, width= .5, color='b', align='center')
tick_locations = [value for value in x_axis]
plt.xticks(tick_locations, x_ticks, rotation = 90)
plt.xlabel("States")
plt.ylabel('Allocations as % of population ')
plt.xlim(-0.5, len(x_axis)-0.5)
plt.ylim(0,100)
plt.title(f'Allocations of Covid Vaccine as % of State Populations as of {as_of_date}')
plt.savefig('Images/state_allocations.png', bbox_inches="tight", facecolor='white', pad_inches=0.5)
plt.tight_layout()
plt.show()<jupyter_output><empty_output><jupyter_text>50 US States Allocations by Vaccine (Full Doses as % of Population)<jupyter_code># Format dataframe for plotting
by_vaccine_df = pd.read_sql_query("SELECT v.vaccine, s.usa_population\
                                                    ,SUM(CASE WHEN v.req_doses = 2 THEN a.dose2\
                                                              WHEN v.req_doses = 1 THEN a.dose1 END) as full_doses\
                                                FROM allocation a\
                                                INNER JOIN vaccine v ON a.vaccine_id = v.id\
                                                CROSS JOIN (SELECT sum(population) as usa_population\
                                                                FROM state\
                                                                WHERE state NOT IN ('District of Columbia', 'Puerto Rico')\
                                                            ) s\
                                                GROUP BY v.vaccine, s.usa_population"
                                  , con=engine)
usa_population = by_vaccine_df['usa_population'][0]
by_vaccine_df['Allocation Rate'] = by_vaccine_df['full_doses']/by_vaccine_df['usa_population']*100
by_vaccine_df = by_vaccine_df[['vaccine','Allocation Rate']]
by_vaccine_df = by_vaccine_df.rename(columns={'vaccine':'Vaccine'})

# Calculate unallocated % of population
total_allocated = by_vaccine_df['Allocation Rate'].sum()
unallocated = 100 - total_allocated
df = pd.DataFrame({'Vaccine':['Not Allocated'],'Allocation Rate':[unallocated]})

by_vaccine_df = pd.concat([by_vaccine_df, df], ignore_index = True)

by_vaccine_df
# Pie Plot of Allocation of Total US Population by Vaccine
plot = by_vaccine_df.set_index('Vaccine').plot.pie(x='Vaccine', y='Allocation Rate', figsize=(8,8), pctdistance=1.1, explode = (0.1, 0.2, 0.1, 0.0), labeldistance=None, autopct='%1.1f%%')
plt.legend(loc='upper left')
plt.ylabel("")
plt.title(f'Percentage of US Pop. Allocated by Vaccine as of {as_of_date}')
plt.savefig('Images/vaccine_allocations.png', bbox_inches="tight", facecolor='white', pad_inches=0.5)
plt.tight_layout()
plt.show()<jupyter_output><empty_output><jupyter_text>Florida Allocations by Vaccine (Full Doses as % of Population)<jupyter_code># Format dataframe for plotting
florida_df = state_pct_df[state_pct_df["State"]=='Florida']
Non_All = float(100 - florida_df['Allocation Rate'])
ALLO = float(florida_df['Allocation Rate'])

#Florida Data Pie Plot
labels = ['Pop % Allocated', 'Pop % Unallocated'] 
numbers = [ALLO, Non_All]
explode=[0.1, 0]
fig1, ax1 = plt.subplots()
ax1.pie(numbers, explode=explode, labels=labels, autopct='%1.1f%%', shadow=True, startangle = 140)
ax1.axis('equal')
plt.title(f'Distribution of Vaccine Allocations against Total FL Population as of {as_of_date}')
plt.tight_layout()
plt.savefig('Images/Allo_pie.png', bbox_inches='tight', facecolor='white', pad_inches=0.5, transparent=True)
plt.show()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12212852573422507, 'fraction_numerical': 0.013812154696132596, 'mean_word_length': 3.146473779385172, 'pattern_counts': {'":': 0, '<': 25, '<?xml version=': 0, '>': 25, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 34, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '424863', 'license': 'MIT', 'n_tokens_mistral': 2409, 'n_tokens_neox': 2211, 'n_words': 482, 'path': 'Analysis.ipynb', 'repo_name': 'millerbrook/ETL-Project', 'size': '6878'}