Week 8 - Streamlit & Apps

Whilst, as of the time of writing, the Snowflake-Streamlit integration isn’t here yet, FrostyFriday sees that as only more reason to get ahead of the curve and start developing those Streamlit skills.

While Streamlit is Python-based, and we encourage you to learn Python, this challenge is Python-optional. The skeleton script below should mean you can do this challenge without any Python knowledge.

For a guide on getting started, head over here.

So…what’s the challenge?

Well, a company has a nice and simple payments fact table that you can find here. They want FrostyData to help them by ingesting the data and creating the below line chart.

Result:

1. The script must not expose passwords, as this would be very unsafe, instead, it should use Streamlit secrets.
2. The title must be “Payments in 2021”.
3. It must have a ‘min date’ filter which specifies the earliest date a user can select, by default this should be set to the earliest date possible.
4. It must have a ‘max date’ filter which specifies the latest date a user can select, by default this should be set to the latest date possible.
5. It should have a line chart with dates on the X axis, and amount on the Y axis. The data should be aggregated at the weekly level.

import streamlit as stimport pandas as pdimport snowflake.connector# Normally, a secrets file should be saved in C:\Users\<your_user>\.streamlit# as secrets.tomlctx = snowflake.connector.connect(user="""<enter username here using a secrets.toml file>""",password="""<enter password here using a secrets.toml file>""",account="""<enter account here using a secrets.toml file>""")cs = ctx.cursor()# WARNING - When aggregating columns in this query, keep the column names the same.query = """<enter SQL here>"""@st.cache # This keeps a cache in place so the query isn't constantly re-run.def load_data():"""In Python, def() creates a function. This particular function connects to your Snowflakeaccount and executes the query above. If you have no Python experience, I recommend leavingthis alone."""cur = ctx.cursor().execute(query)payments_df = pd.DataFrame.from_records(iter(cur), columns=[x[0] for x in cur.description])payments_df['PAYMENT_DATE'] = pd.to_datetime(payments_df['PAYMENT_DATE'])payments_df = payments_df.set_index('PAYMENT_DATE')return payments_dfpayments_df = load_data() # This creates what we call a 'dataframe' called payments_df, think of this as# a table. To create the table, we use the above function. So, basically,# every time your write 'payments_df' in your code, you're referencing# the result of your query.def get_min_date():"""This function returns the earliest date present in the dataset.When you want to use this value, just write get_min_date()."""return min(payments_df.index.to_list()).date()def get_max_date():"""This function returns the latest date present in the dataset.When you want to use this value, just write get_max_date()."""return max(payments_df.index.to_list()).date()def app_creation():"""This function is the one your need to edit."""# <Create a title here>min_filter = # <Create a slider for the min date>max_filter = # <Create a slider for the max date>mask = (payments_df.index >= pd.to_datetime("""<your minimum filter should go here>""")) \& (payments_df.index <= pd.to_datetime("""<your maximum filter should go here>"""))payments_df_filtered = payments_df.loc[mask] #This line creates a new dataframe (table) that filters# your results to between the range of your min# slider, and your max slider.# Create a line chart using the new payments_df_filtered dataframe.app_creation() # The function above is now invoked.

Remember if you want to participate:

1. Sign up as a member of Frosty Friday. You can do this by clicking on the sidebar, and then going to ‘REGISTER‘ (note joining our mailing list does not give you a Frosty Friday account)
2. Post your code to GitHub and make it publicly available (Check out our guide if you don't know how to here)
3. Post the URL in the comments of the challenge.