Created
March 31, 2023 17:45
-
-
Save octavian-zarzu/3c8161401d4664f83220c9f5ef3d1052 to your computer and use it in GitHub Desktop.
Streamlit app code - db: Firebolt
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
import os | |
from dotenv import load_dotenv | |
from firebolt.client.auth import UsernamePassword | |
from firebolt.db import connect | |
import streamlit as st | |
import altair as alt | |
import pandas as pd | |
import matplotlib.pyplot as plt | |
load_dotenv() | |
def get_firebolt_connection(): | |
engine_name = os.getenv("FIREBOLT_ENGINE") | |
database_name = os.getenv("FIREBOLT_DATABASE") | |
username = os.getenv("FIREBOLT_USER") | |
password = os.getenv("FIREBOLT_PASSWORD") | |
# create a connection based on provided credentials | |
connection = connect( | |
auth=UsernamePassword(username, password), | |
engine_name=engine_name, | |
database=database_name, | |
) | |
return connection | |
connection = get_firebolt_connection() | |
cursor = connection.cursor() | |
## Title | |
st.set_page_config(layout="wide") | |
st.title('NY Philharmonic Performance History') | |
## Filter slider | |
cursor.execute(""" | |
SELECT | |
min(concert_date), | |
max(concert_date) | |
FROM nyc_phil | |
""") | |
(ts_min, ts_max) = cursor.fetchone() | |
(slider_min, slider_max) = st.slider( | |
"Concert Date", | |
min_value = ts_min, | |
max_value = ts_max, | |
value = (ts_min, ts_max), | |
format="DD/MM/YYYY - hh:mm") | |
cursor.execute(""" | |
SELECT | |
COUNT(DISTINCT program_id) | |
FROM nyc_phil | |
WHERE concert_date > ? | |
AND concert_date < ? | |
""", [slider_min, slider_max]) | |
t = cursor.fetchone() | |
st.write('Total number of distinct programs in the interval: ', int(t[0])) | |
## Chart - Number of programs per season | |
st.write('Number of programs per season') | |
cursor.execute(""" | |
SELECT | |
season, | |
COUNT(DISTINCT program_id) | |
FROM nyc_phil | |
WHERE concert_date > ? | |
AND concert_date < ? | |
GROUP BY ALL | |
ORDER BY season | |
""", [slider_min, slider_max]) | |
program_by_season = cursor.fetchall() | |
program_by_season_df = pd.DataFrame(program_by_season, columns=['season','no_of_programs']) | |
st.bar_chart( | |
data=program_by_season_df, | |
x = 'season', | |
y = 'no_of_programs' | |
) | |
## Chart - Popular composers per season | |
st.write('Popular composers per season') | |
cursor.execute(""" | |
SELECT | |
season, | |
composer_name, | |
COUNT(DISTINCT work_id) | |
FROM nyc_phil | |
WHERE concert_date > ? | |
AND concert_date < ? | |
GROUP BY ALL | |
HAVING COUNT(DISTINCT work_id)>25 | |
ORDER BY season | |
""", [slider_min, slider_max]) | |
program_by_season_and_composer_name = cursor.fetchall() | |
program_by_season_and_composer_name_df = pd.DataFrame(program_by_season_and_composer_name, columns=['season','composer_name','count']) | |
chart = alt.Chart(program_by_season_and_composer_name_df).mark_bar().encode( | |
x = 'season', | |
y = 'count', | |
color = 'composer_name' | |
).interactive() | |
st.altair_chart(chart, theme="streamlit", use_container_width=True) | |
## Chart - Popular venues | |
st.write('Popular venues') | |
cursor.execute(""" | |
SELECT | |
concert_venue, | |
COUNT(DISTINCT program_id) | |
FROM nyc_phil | |
WHERE concert_date > ? | |
AND concert_date < ? | |
GROUP BY ALL | |
HAVING COUNT(DISTINCT program_id) > 100 | |
""", [slider_min, slider_max]) | |
programs_by_venue = cursor.fetchall() | |
programs_by_venue_df = pd.DataFrame(programs_by_venue, columns=['venue','no_of_programs']) | |
fig1, ax1 = plt.subplots() | |
ax1.pie(programs_by_venue_df['no_of_programs'], labels=programs_by_venue_df['venue']) | |
st.pyplot(fig1, width = 0.1) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment