query = """
SELECT name 
FROM sqlite_master 
WHERE type='table';
"""
df = pd.read_sql_query(query, conn)
df

for table in ['beers','breweries']:
    
    query = f"""
    PRAGMA table_info({table});
    """
    df = pd.read_sql_query(query, conn)
    print(df[['name','type']])
    print('='*30)

metadata_query = """
SELECT *
FROM sqlite_master;
"""
metadata_result = pd.read_sql_query(metadata_query, conn)
metadata_result

five_rows_beers_query = """
SELECT abv, ibu, name, style
FROM beers
LIMIT 5
"""
five_rows_beers_result = pd.read_sql_query(five_rows_beers_query, conn)
five_rows_beers_result

five_rows_breweries_query = """
SELECT brewery_id, name, city, state
FROM breweries
LIMIT 5;
"""
five_rows_breweries_result = pd.read_sql_query(five_rows_breweries_query, conn)
five_rows_breweries_result

beers_total_rows_query = """
SELECT COUNT(*) AS row_count
FROM beers;
"""
beers_total_rows_result = pd.read_sql_query(beers_total_rows_query, conn)
beers_total_rows_result

distinct_styles_query = """
SELECT COUNT (DISTINCT(style)) AS distinct_styles
FROM beers;
"""
distinct_styles_result = pd.read_sql_query(distinct_styles_query, conn)
distinct_styles_result

alcoholic_content_query = """
SELECT MIN(abv) AS min, AVG(abv) AS avg, MAX(abv) AS max
FROM beers;
"""
alcoholic_content_result = pd.read_sql_query(alcoholic_content_query, conn)
alcoholic_content_result

abv_distribution_query = """
SELECT abv
FROM beers;
"""
abv_distribution_result = pd.read_sql_query(abv_distribution_query, conn)
abv_distribution_result

import matplotlib.pyplot as plt
ax = abv_distribution_result.hist(bins=30)[0][0]
ax.set_title("Alcohol by Volume")
plt.show()

ipa_styles_query = """
SELECT DISTINCT(style) AS ipa_styles
FROM beers
WHERE style LIKE "%IPA%"
ORDER BY style ASC;
"""
ipa_styles_result = pd.read_sql_query(ipa_styles_query, conn)
ipa_styles_result

ale_bitterness_query = """
SELECT AVG(ibu)
FROM beers
WHERE style LIKE '%lager%';
"""
ale_bitterness_result = pd.read_sql_query(ale_bitterness_query, conn)
print(ale_bitterness_result)

lager_bitterness_query = """
SELECT AVG(ibu)
FROM beers
WHERE style LIKE "%IPA%" OR "%APA%" OR "%Pale Ale%";
"""
lager_bitterness_result = pd.read_sql_query(lager_bitterness_query, conn)
print(lager_bitterness_result)

style_count_query = """
SELECT DISTINCT(style),
COUNT(name) OVER(PARTITION BY style) AS number
FROM beers
ORDER BY number DESC
LIMIT 10;
"""
style_count_result = pd.read_sql_query(style_count_query, conn)
style_count_result

beers_and_brewery_query = """
SELECT breweries.name AS brewery, beers.name AS beer
FROM beers
INNER JOIN breweries
ON beers.brewery_id = breweries.brewery_id
WHERE breweries.city = 'San Francisco'
ORDER BY brewery ASC, beer ASC;
"""
beers_and_brewery_result = pd.read_sql_query(beers_and_brewery_query, conn)
beers_and_brewery_result

largest_breweries_query = """
SELECT breweries.name AS name, COUNT(*) AS "number of beers"
FROM beers
INNER JOIN breweries
ON beers.brewery_id = breweries.brewery_id
GROUP BY breweries.brewery_id, breweries.name 
ORDER BY "number of beers" DESC, name ASC
LIMIT 10;
"""
largest_breweries_result = pd.read_sql_query(largest_breweries_query, conn)
largest_breweries_result

 top_10_states_query = """
     SELECT state, count(*) AS "breweries", sum(b.beers) "beers" FROM breweries
     JOIN 
         (SELECT brewery_id, count(*) AS beers
          FROM beers
          GROUP BY brewery_id) b 
     ON b.brewery_id = breweries.brewery_id
     GROUP BY state
     ORDER BY breweries DESC, state ASC
     LIMIT 10
 """

top_10_states_result = pd.read_sql_query(top_10_states_query, conn)
top_10_states_result

import seaborn as sns

abv_ibu_query = """
SELECT abv AS ABV, ibu AS IBU
FROM beers;
"""
abv_ibu_df = pd.read_sql_query(abv_ibu_query, conn)

correlation_data = abv_ibu_df.dropna()
sns.heatmap(correlation_data.corr(), annot=True, cmap='coolwarm')
plt.title("Correlation between ABV and IBU")

plt.show()

import sqlite3
import matplotlib.pyplot as plt

avg_abv_by_state_query = """
SELECT state, AVG(abv) as average_abv
FROM beers
JOIN breweries ON beers.brewery_id = breweries.brewery_id
GROUP BY state
ORDER BY average_abv DESC;
"""
avg_abv_by_state = pd.read_sql_query(avg_abv_by_state_query, conn)

avg_abv_by_state.plot(kind='bar', x='state', y='average_abv', figsize=(10, 6))
plt.title("Average ABV by State")
plt.ylabel("Average ABV")
plt.xlabel('State')

plt.show()

import geopandas as gpd

avg_abv_by_state['state'] = avg_abv_by_state['state'].str.upper().str.strip()

state_abbrev_to_full = {
    'AL': 'Alabama', 'AK': 'Alaska', 'AZ': 'Arizona', 'AR': 'Arkansas', 'CA': 'California',
    'CO': 'Colorado', 'CT': 'Connecticut', 'DE': 'Delaware', 'FL': 'Florida', 'GA': 'Georgia',
    'HI': 'Hawaii', 'ID': 'Idaho', 'IL': 'Illinois', 'IN': 'Indiana', 'IA': 'Iowa',
    'KS': 'Kansas', 'KY': 'Kentucky', 'LA': 'Louisiana', 'ME': 'Maine', 'MD': 'Maryland',
    'MA': 'Massachusetts', 'MI': 'Michigan', 'MN': 'Minnesota', 'MS': 'Mississippi', 'MO': 'Missouri',
    'MT': 'Montana', 'NE': 'Nebraska', 'NV': 'Nevada', 'NH': 'New Hampshire', 'NJ': 'New Jersey',
    'NM': 'New Mexico', 'NY': 'New York', 'NC': 'North Carolina', 'ND': 'North Dakota', 'OH': 'Ohio',
    'OK': 'Oklahoma', 'OR': 'Oregon', 'PA': 'Pennsylvania', 'RI': 'Rhode Island', 'SC': 'South Carolina',
    'SD': 'South Dakota', 'TN': 'Tennessee', 'TX': 'Texas', 'UT': 'Utah', 'VT': 'Vermont',
    'VA': 'Virginia', 'WA': 'Washington', 'WV': 'West Virginia', 'WI': 'Wisconsin', 'WY': 'Wyoming',
    'DC': 'District of Columbia'
}

avg_abv_by_state['state'] = avg_abv_by_state['state'].map(state_abbrev_to_full)

gdf = gpd.read_file('Pathname')

gdf = gdf.merge(avg_abv_by_state, left_on='NAME', right_on='state')
gdf.set_index(gdf.state, inplace= True)
gdf.drop(['GEO_ID', 'STATE', 'LSAD', 'NAME'], inplace= True, axis= 1)

gdf.head()

from bokeh.io import show, output_notebook
from bokeh.models import GeoJSONDataSource, LinearColorMapper, ColorBar, HoverTool
from bokeh.palettes import brewer
from bokeh.plotting import figure

# Add a formatted ABV field for hover display
gdf['formatted_abv'] = gdf['average_abv'].apply(lambda x: f'{x:.3f}')

# Convert GeoDataFrame to GeoJSON format
geo_json = gdf.to_json()

# Create a GeoJSONDataSource
geo_source = GeoJSONDataSource(geojson=geo_json)

# Define a color mapper: palette can be adjusted
palette = brewer['YlGnBu'][8]
color_mapper = LinearColorMapper(palette=palette, low=gdf.average_abv.min(), high=gdf.average_abv.max())

# Create figure object with adjusted dimensions
p = figure(title='Average Alcohol by Volume (ABV) across States',
           height=600, width=950,
           toolbar_location='below',
           tools="pan, wheel_zoom, reset")

# Set the x and y range bounds
p.x_range.bounds = (-130, -65)
p.y_range.bounds = (24, 50)

# Remove axis labels and grid lines
p.axis.visible = False
p.xgrid.grid_line_color = None
p.ygrid.grid_line_color = None

# Add patch renderer to figure
p.patches('xs', 'ys', source=geo_source,
          fill_color={'field': 'average_abv', 'transform': color_mapper},
          line_color='black', line_width=0.5, fill_alpha=1)

# Configure hover tool
hover = HoverTool()
hover.tooltips = [
    ("State", "@state"),
    ("Average ABV", "@formatted_abv")
]
p.add_tools(hover)

# Add color bar
color_bar = ColorBar(color_mapper=color_mapper, label_standoff=12, width=500, height=20,
                     location=(0,0), orientation='horizontal')
p.add_layout(color_bar, 'below')

output_notebook()
show(p)

from collections import Counter
import re

query_beers = """
SELECT *
FROM beers;
"""
beers = pd.read_sql_query(query_beers, conn)

words = Counter(re.findall(r'\w+', ' '.join(beers['name']).lower()))
most_common_words = words.most_common(10)

words_df = pd.DataFrame(most_common_words, columns=['Word', 'Frequency'])
words_df.plot(kind='bar', x='Word', y='Frequency')
plt.title("Most Common Words in Beer Names")

plt.show()