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.
- The script must not expose passwords, as this would be very unsafe, instead, it should use Streamlit secrets.
- The title must be “Payments in 2021”.
- 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.
- 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.
- 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.
Skeleton Script for non-Python Users:
import streamlit as st import pandas as pd import snowflake.connector # Normally, a secrets file should be saved in C:\Users\<your_user>\.streamlit # as secrets.toml ctx = 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 Snowflake account and executes the query above. If you have no Python experience, I recommend leaving this alone. """ cur = ctx.cursor().execute(query) payments_df = pd.DataFrame.from_records(iter(cur), columns=[x 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_df payments_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:
- 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)
- Post your code to GitHub and make it publicly available (Check out our guide if you don’t know how to here)
- Post the URL in the comments of the challenge.