import altair as alt import pandas as pd import numpy as np

def clean_data(df): df['date']=pd.to_datetime(df.date) df['wind_speed']=df['wind_speed'].astype(float).replace(999.9, np.nan) return df

def make_chart(df): df=clean_data(df) brush = alt.selection_interval(encodings=['x']) points = alt.Chart().mark_circle().encode( alt.X('monthdate(date):T', title='Date'), alt.Y('mean_temp:Q', title='Mean Temperature in 2020 (F)'), size=alt.Size('wind_speed:Q', scale=alt.Scale(domain=[1, 20], range=[1,500])), color=alt.Color('mean_temp:Q', scale=alt.Scale(scheme='blueorange', domainMid=32), legend=alt.Legend(title='Mean Temperature')), tooltip=['date', 'name', 'mean_temp', 'wind_speed', 'rain'] ).properties( width=550, height=300 ).add_selection( brush ).transform_filter( 'year(datum.date) == 2020' ) bars = alt.Chart().mark_bar().encode( x=alt.X('mean(mean_temp)', title='Mean Temperature (F)'), y=alt.Y('year(date):N', title='Year'), color=alt.Color('mean(mean_temp):Q', scale=alt.Scale(scheme='blueorange', domainMid=32)) ).transform_filter( brush ).properties( width=550, ) chart=alt.vconcat(points, bars, data=df, title=f"Mean Temperature Dashboard for NY" ) return chart

make_chart(df_1)

station_list=['BUFFALO NIAGARA INTERNATIONAL', 'CENTRAL PARK']

SELECT date, name, temp as mean_temp, wdsp as wind_speed, rain_drizzle as rain FROM fh-bigquery.weather_gsod.all WHERE date > '2015-12-31' and name in {{ station_list | inclause}} ORDER BY date DESC

make_chart(df_2)

SELECT date, name, temp as mean_temp, {{ column_name | sqlsafe }} as wind_speed FROM fh-bigquery.weather_gsod.all WHERE date > '2015-12-31' and name = {{ station_name }} ORDER BY date DESC limit 5

column_names=['temp', 'wdsp', 'rain_drizzle']

SELECT date, name, {% for col in column_names %} {% if not loop.last %} {{ col | sqlsafe }}, {% else %} {{ col | sqlsafe }} {% endif %} {% endfor %} FROM fh-bigquery.weather_gsod.all WHERE date > '2015-12-31' and name = {{ station_name }} ORDER BY date DESC LIMIT 5

SELECT date, name, {% for col in column_names %} {% if not loop.last %} {{ col | sqlsafe }}, {% else %} {{ col | sqlsafe }} {% endif %} {% endfor %} FROM fh-bigquery.weather_gsod.all WHERE date > '2015-12-31' and name = {{ station_name }} ORDER BY date DESC LIMIT 5