# Google Drive file path ROOT = '/datasets/tims-google-drive/' PROJECT = 'Leisure & Travel/Skiing/Snow Monitoring/' # Google Sheets workbooks to create. Separate into multiple workbooks to avoid size constraints. WORKBOOKS = ['Weather', 'Correction Models'] CREDENTIALS = 'GoogleDriveAccess.json'

# NWAC measurement areas. Each area contains one or more weather stations. AREAS = ['crystal', 'snoqualmie-pass', 'stevens-pass', 'hurricane-ridge', 'mt-baker-ski-area', 'washington-pass', 'lake-wenatchee', 'mt-rainier', 'white-pass-ski-area', 'mt-st-helens', 'chinook-pass'] # NWAC measures. Only one measure can be downloaded at a time. MEASURES = ['temperature', 'precipitation', 'snowfall_24_hour', 'snow_depth', 'wind_direction', 'wind_speed_minimum', 'wind_speed_average', 'wind_speed_maximum'] # Relative date range in days. NWAC permits downloads of up to 365 days of data at a time. DATE_RANGE = 30 # Correction count. Number of correction models to run and select the best fit from. CORRECTION_COUNT = 3

# Install packackes !pip install requests !pip install pandas --upgrade !pip install tableauhyperapi==0.0.14265 !pip install pantab==2.0.0 !pip install gspread

# Import installed and stock packages import numpy as np from datetime import date, datetime, timedelta from dateutil.relativedelta import relativedelta import pytz from pytz import timezone import re import requests import io import pandas as pd from tableauhyperapi import TableName import pantab import gspread import os.path

# Return the current time as a datetime in Pacific time def now_pt(): now_utc = datetime.now(tz = pytz.utc) now_pt = now_utc.astimezone(timezone('US/Pacific')) now_pt = now_pt.replace(tzinfo = None) return now_pt # Return date formatted as string def date_str(ts = now_pt()): ts_formatted = ts.strftime('%m/%d/%Y') return ts_formatted # Return time formatted as string def hour_str(ts = now_pt()): ts_formatted = ts.strftime('%-m/%-d/%y %-I%p') return ts_formatted

# Get an upper bound equal to the today's most recent hour, allowing for some amount data lag period_end = datetime.now(pytz.timezone('US/Pacific')) - timedelta(minutes=20) period_end = datetime(period_end.year, period_end.month, period_end.day, period_end.hour) # Get a lower bound equal to some number of days ago from today, starting at midnight period_start = period_end - timedelta(days=DATE_RANGE) period_start = datetime(period_start.year, period_start.month, period_start.day) # Progress report message = 'Will retrieve data from {t} through {e}'.format(t=hour_str(period_start), e=hour_str(period_end)) print(message)

# Create an empty list of measure labels measures = [] # Create an empty dataframe to union each area to nwac_df = pd.DataFrame() # Get season attributes period_start_label = period_start.strftime('%Y-%m-%d') period_end_label = (period_end + timedelta(days=1)).strftime('%Y-%m-%d') for area in AREAS: # Get a friendly name for the area area_label = area.replace('-', ' ').title() # Start a counter to track joins j = 0 # Create lists for measure tracking extant_measures = [] empty_measures = [] # Progress update print('\n--{a} FROM {s} TO {e}--'.format(a=area_label.upper(), s=hour_str(period_start), e=hour_str(period_end))) for measure in MEASURES: # Get a friendly name for the measure measure_label = measure.replace('_', ' ').lower() # Get data from NWAC url = 'https://nwac.us/data-portal/csv/' + area + '/sensortype/' + measure + '/start-date/' + period_start_label + '/end-date/' + period_end_label page = requests.get(url) measure_df = pd.read_csv(io.StringIO(page.text)) # Unpivot stations measure_df = pd.melt( measure_df, id_vars = 'Date/Time (PST)', var_name = 'Station Detail', value_name = 'Measure' ).rename(columns={'Date/Time (PST)': 'Calendar Timestamp'}) # Exclude null records measure_df.dropna(how='any', inplace=True) if measure_df.empty: empty_measures.append(measure_label) else: extant_measures.append(measure_label) # Extract components from station detail measure_df['Unit'] = measure_df['Station Detail'].str.split(pat='(', n=1, expand=True)[0] measure_df['Elevation (ft)'] = measure_df['Station Detail'].str.extract(pat='\((.*)\'\)') measure_df['Location'] = measure_df['Station Detail'].str.extract(pat=' - (.*)') measure_df['Location'] = measure_df['Location'].str.split(pat='(', n=1, expand=True)[0] measure_df['Station'] = measure_df['Station Detail'].str.extract(pat='\)(.*)') # Clean up columns measure_df['Station'] = measure_df['Location'].fillna(measure_df['Station']).str.strip() measure_df['Elevation (ft)'] = measure_df['Elevation (ft)'].str.strip().astype(int) measure_df['Calendar Timestamp'] = pd.to_datetime(measure_df['Calendar Timestamp'].str.strip()) measure_df['Measure'] = pd.to_numeric(measure_df['Measure'], errors='coerce') measure_df['Unit'] = measure_df['Unit'].str.strip() # Put measure units in header unit = measure_df['Unit'].dropna(how='any').reset_index(drop=True).at[1] measure_label = measure_label.title() + ' (' + unit + ')' measure_df.rename(columns={'Measure':measure_label}, inplace=True) # Append measure labels to list and deduplicate measures.append(measure_label) # First measure in list if j == 0: station_df = measure_df[['Station', 'Elevation (ft)', 'Calendar Timestamp', measure_label]].copy() station_df['Area'] = area_label # Subsequent measures in the list else: station_df = station_df.merge( measure_df[['Station', 'Elevation (ft)', 'Calendar Timestamp', measure_label]], how = 'outer', on = ['Station', 'Elevation (ft)', 'Calendar Timestamp'], suffixes = ('', '_m') ) station_df['Area'] = station_df['Area'].fillna(area_label) # Increment join counter j = j + 1 # Union stations nwac_df = pd.concat([nwac_df, station_df]) # Progress report extant_measures = ', '.join(extant_measures) empty_measures = ', '.join(empty_measures) if len(extant_measures) > 0: print('Imported {m}'.format(m=extant_measures)) if len(empty_measures) > 0: print('Missing {e}'.format(e=empty_measures)) # Select and sort columns measures = list(set(measures)) measures.sort() dimensions = ['Area', 'Station', 'Elevation (ft)', 'Calendar Timestamp'] sorting = ['Calendar Timestamp', 'Area', 'Station'] nwac_df = nwac_df[dimensions + measures].sort_values(by=sorting).reset_index(drop=True) # Preview results nwac_df.tail()

# Get unique timestamps in date range calendar_range = pd.date_range(start=period_start, end=period_end, freq='1H') calendar_df = pd.DataFrame(calendar_range, columns=['Calendar Timestamp']) calendar_df['Calendar Date'] = calendar_df['Calendar Timestamp'].dt.date.astype('datetime64') # Progress update message = 'Generated datetime range from {t} to {e}'.format(t=period_start, e=period_end) print(message) # Cross join stations and dates dense_df = pd.merge( nwac_df[['Area', 'Station', 'Elevation (ft)']].dropna(how='any').drop_duplicates().sort_values(by=['Area', 'Station']), calendar_df[['Calendar Date', 'Calendar Timestamp']], how = 'cross' ) # Add station label dense_df['Station Label'] = dense_df['Elevation (ft)'].map('{:,}'.format).astype(str) + "' " + dense_df['Station'] # Add calendar month, day, and hour for matching accross seasons dense_df['Calendar Leap Day'] = np.where((dense_df['Calendar Timestamp'].dt.month==2) & (dense_df['Calendar Timestamp'].dt.day==29), 28, dense_df['Calendar Timestamp'].dt.day) dense_df['Calendar Month Day Hour'] = dense_df['Calendar Timestamp'].dt.hour + dense_df['Calendar Leap Day'] * 100 + dense_df['Calendar Timestamp'].dt.month * 10000 # Select and sort columns dimensions = ['Area', 'Station', 'Station Label', 'Elevation (ft)', 'Calendar Date', 'Calendar Timestamp', 'Calendar Month Day Hour'] dense_df = dense_df[dimensions].sort_values(by=sorting).reset_index(drop=True) # Preview results dense_df.tail()

# Join dense dates with other data nwac_dense_df = pd.merge( dense_df, nwac_df[['Area', 'Station', 'Calendar Timestamp'] + measures], how = 'left', on = ['Area', 'Station', 'Calendar Timestamp'], suffixes = ('', ' Sparse') ) # Select and sort columns nwac_dense_df = nwac_dense_df[dimensions + measures] # Preview results nwac_dense_df.tail()

# Get precipitation precip_df = nwac_df[nwac_df['Precipitation (")'] > 0][[ 'Area', 'Calendar Timestamp']].drop_duplicates().reset_index(drop=True) precip_df['Precipitation Indicator'] = True # Select and sort columns precip_df = precip_df[['Area', 'Calendar Timestamp', 'Precipitation Indicator']] # Preview Results precip_df.tail()

# Join NWAC with precipitation nwac_precip_df = nwac_dense_df.merge( precip_df, how = 'left', on = ['Area', 'Calendar Timestamp'] ) # Fill nulls nwac_precip_df['Precipitation Indicator'] = nwac_precip_df['Precipitation Indicator'].fillna(False) # Select and sort columns dimensions = ['Area', 'Station', 'Station Label', 'Elevation (ft)', 'Calendar Date', 'Calendar Timestamp', 'Calendar Month Day Hour', 'Precipitation Indicator'] nwac_precip_df = nwac_precip_df[dimensions + measures] # Preview results nwac_precip_df.tail()

# Set the change thresholds above which to apply corrections permitted_temperature_change = 10 permitted_depth_change = 10 permitted_snowfall_change = 10 # Set the upper/lower bound increments expected_temperature_change = 5 expected_depth_change = 0.4 expected_snowfall_change = 0.4 # Set the correction rate threshold above which to exclude measurements for the entire station permitted_correction_rate = 0.35

# Initialize a list of anchor dates anchor_dates = [] # Create anchor dates equally spaced throughout the period for d in pd.date_range(start=period_start, end=period_end, periods=CORRECTION_COUNT): # Truncate to the hour hourstamp = datetime(year=d.year, month=d.month, day=d.day, hour=d.hour) if hourstamp not in anchor_dates: anchor = [period_start, period_end, hourstamp] anchor_dates.append(anchor) # Progress update anchor_dates_str = [hour_str(d[2]) for d in anchor_dates] message = 'Will anchor corrections to {d}'.format(d=', '.join(anchor_dates_str)) print(message)

# Create an empty data frame to union to anchor_df = pd.DataFrame() for anchor in anchor_dates: # Get season attributes period_start = anchor[0] period_end = anchor[1] anchor_date = anchor[2] # Create reference dates for filtering inner_past_date = anchor_date inner_future_date = anchor_date outer_past_date = anchor_date - timedelta(hours=1) outer_future_date = anchor_date + timedelta(hours=1) # Progress update message = 'Anchoring corrections to {d}'.format(d=hour_str(anchor_date)) print(message) # Get weather data filtered to the anchor date df = nwac_dense_df[nwac_dense_df['Calendar Timestamp']==anchor_date][[ 'Area', 'Station', 'Calendar Timestamp', 'Temperature (deg F)', 'Snow Depth (")', 'Snowfall 24 Hour (")']].copy() # Get non-null measurements df['Temperature Measurement'] = np.where(df['Temperature (deg F)'].notna(), 1, 0) df['Depth Measurement'] = np.where(df['Snow Depth (")'].notna(), 1, 0) df['Snowfall Measurement'] = np.where(df['Snowfall 24 Hour (")'].notna(), 1, 0) # Correct temperature df['Temperature Change'] = 0 df['Temperature Correction'] = np.where(df['Temperature (deg F)'].notna(), 0, np.NaN) df['Temperature Corrected (deg F)'] = df['Temperature (deg F)'] df['Temperature Lower Bound'] = df['Temperature (deg F)'] df['Temperature Upper Bound'] = df['Temperature (deg F)'] # Correct snow depth df['Depth Change'] = 0 df['Depth Correction'] = np.where(df['Snow Depth (")'].notna(), 0, np.NaN) df['Snow Depth Corrected (")'] = df['Snow Depth (")'] df['Depth Lower Bound'] = np.where(df['Snow Depth (")'] < 0, 0, df['Snow Depth (")']) df['Depth Upper Bound'] = df['Depth Lower Bound'] # Correct snowfall df['Snowfall Change'] = 0 df['Snowfall Correction'] = np.where(df['Snowfall 24 Hour (")'].notna(), 0, np.NaN) df['Snowfall 24 Hour Corrected (")'] = np.where( df['Snowfall 24 Hour (")'] < 0, 0, df['Snowfall 24 Hour (")']) df['Snowfall Lower Bound'] = df['Snowfall 24 Hour Corrected (")'] df['Snowfall Upper Bound'] = df['Snowfall 24 Hour Corrected (")'] # Insert anchor date into data se df['Anchor Date'] = anchor_date # Union to previously anchored segments anchor_df = pd.concat([anchor_df, df]) # Iteratively join moving one hour backward and forward from the anchor date while (period_start <= outer_past_date) | (outer_future_date <= period_end): # Get temperature and snow depth for inner dates inner_df = anchor_df[(anchor_df['Calendar Timestamp'].isin( [inner_past_date, inner_future_date])) & (anchor_df['Anchor Date']==anchor_date)].copy() inner_df['Calendar Timestamp'] = np.where( inner_df['Calendar Timestamp']==inner_past_date, outer_past_date, outer_future_date) # Create two records for first iterations following anchor case if inner_past_date == inner_future_date: inner2_df = inner_df.copy() inner2_df['Calendar Timestamp'] = outer_future_date inner_df = pd.concat([inner_df, inner2_df]) # Filter to within date range inner_df = inner_df[inner_df['Calendar Timestamp'].between(period_start, period_end)] # Get temperature and snow depth for outer dates outer_df = nwac_dense_df[nwac_dense_df['Calendar Timestamp'].isin( [outer_past_date, outer_future_date])][[ 'Area', 'Station', 'Calendar Timestamp', 'Temperature (deg F)', 'Snow Depth (")', 'Snowfall 24 Hour (")']] # Join with existing snow depths df = pd.merge( inner_df[[ 'Area', 'Station', 'Calendar Timestamp', 'Anchor Date', 'Temperature Corrected (deg F)', 'Temperature Correction', 'Temperature Lower Bound', 'Temperature Upper Bound', 'Snow Depth Corrected (")','Depth Correction', 'Depth Lower Bound', 'Depth Upper Bound', 'Snowfall 24 Hour Corrected (")', 'Snowfall Correction', 'Snowfall Lower Bound', 'Snowfall Upper Bound']], outer_df[[ 'Area', 'Station', 'Calendar Timestamp', 'Temperature (deg F)', 'Snow Depth (")', 'Snowfall 24 Hour (")']], how = 'left', on = ['Area', 'Station', 'Calendar Timestamp'], suffixes = (' Corrected', '') ) # Get non-null measurements df['Temperature Measurement'] = np.where(df['Temperature (deg F)'].notna(), 1, 0) df['Depth Measurement'] = np.where(df['Snow Depth (")'].notna(), 1, 0) df['Snowfall Measurement'] = np.where(df['Snowfall 24 Hour (")'].notna(), 1, 0) # Correct temperature df['Temperature Change'] = (df['Temperature (deg F)'] - df['Temperature Corrected (deg F)']).abs() df['Temperature Correction'] = np.where( df['Temperature Change'] > permitted_temperature_change, 1, np.where( df['Temperature Change'].notna(), 0, np.NaN)) df['Temperature Lower Bound'] = np.where( df['Temperature Correction'].fillna(1)==1, df['Temperature Lower Bound'] - expected_temperature_change, df['Temperature (deg F)']) df['Temperature Upper Bound'] = np.where( df['Temperature Correction'].fillna(1)==1, df['Temperature Upper Bound'] + expected_temperature_change, df['Temperature (deg F)']) df['Temperature Corrected (deg F)'] = np.where( df['Temperature (deg F)'].between(df['Temperature Lower Bound'], df['Temperature Upper Bound']), df['Temperature (deg F)'], np.where(df['Temperature (deg F)'].notna(), df['Temperature Corrected (deg F)'], np.NaN)) # Correct snow depth df['Depth Change'] = (df['Snow Depth (")'] - df['Snow Depth Corrected (")']).abs() df['Depth Correction'] = np.where( df['Depth Change'] > permitted_depth_change, 1, np.where( df['Depth Change'].notna(), 0, np.NaN)) df['Depth Lower Bound'] = np.where( df['Depth Correction'].fillna(1)==1, df['Depth Lower Bound'] - expected_depth_change, df['Snow Depth (")']) df['Depth Lower Bound'] = np.where(df['Depth Lower Bound'] < 0, 0, df['Depth Lower Bound']) df['Depth Upper Bound'] = np.where( df['Depth Correction'].fillna(1)==1, df['Depth Upper Bound'] + expected_depth_change, df['Snow Depth (")']) df['Depth Upper Bound'] = np.where(df['Depth Upper Bound'] < 0, 0, df['Depth Upper Bound']) df['Snow Depth Corrected (")'] = np.where( df['Snow Depth (")'].between(df['Depth Lower Bound'], df['Depth Upper Bound']), df['Snow Depth (")'], np.where(df['Snow Depth (")'].notna(), df['Snow Depth Corrected (")'], np.NaN)) # Correct snowfall df['Snowfall Non Negative'] = np.where(df['Snowfall 24 Hour (")'] < 0, 0, df['Snowfall 24 Hour (")']) df['Snowfall Change'] = (df['Snowfall Non Negative'] - df['Snowfall 24 Hour Corrected (")']).abs() df['Snowfall Correction'] = np.where( df['Snowfall Change'] > permitted_depth_change, 1, np.where( df['Snowfall Change'].notna(), 0, np.NaN)) df['Snowfall Lower Bound'] = np.where( df['Snowfall Correction'].fillna(1)==1, df['Snowfall Lower Bound'] - expected_depth_change, df['Snowfall Non Negative']) df['Snowfall Lower Bound'] = np.where(df['Snowfall Lower Bound'] < 0, 0, df['Snowfall Lower Bound']) df['Snowfall Upper Bound'] = np.where( df['Snowfall Correction'].fillna(1)==1, df['Snowfall Upper Bound'] + expected_depth_change, df['Snowfall Non Negative']) df['Snowfall Upper Bound'] = np.where(df['Snowfall Upper Bound'] < 0, 0, df['Snowfall Upper Bound']) df['Snowfall 24 Hour Corrected (")'] = np.where( df['Snowfall Non Negative'].between(df['Snowfall Lower Bound'], df['Snowfall Upper Bound']), df['Snowfall Non Negative'], np.where(df['Snowfall 24 Hour (")'].notna(), df['Snowfall 24 Hour Corrected (")'], np.NaN)) # Select and sort columns df = df[[ 'Area', 'Station', 'Calendar Timestamp', 'Anchor Date', 'Temperature (deg F)', 'Temperature Corrected (deg F)', 'Temperature Measurement', 'Temperature Correction', 'Temperature Lower Bound', 'Temperature Upper Bound', 'Snow Depth (")', 'Snow Depth Corrected (")', 'Depth Measurement', 'Depth Correction', 'Depth Lower Bound', 'Depth Upper Bound', 'Snowfall 24 Hour (")', 'Snowfall 24 Hour Corrected (")', 'Snowfall Measurement', 'Snowfall Correction', 'Snowfall Lower Bound', 'Snowfall Upper Bound']] # Union results anchor_df = pd.concat([anchor_df, df]) # Increment dates inner_past_date = inner_past_date - timedelta(hours=1) inner_future_date = inner_future_date + timedelta(hours=1) outer_past_date = outer_past_date - timedelta(hours=1) outer_future_date = outer_future_date + timedelta(hours=1) # # Add period dates # anchor_df['Period Start Date'] = period_start # anchor_df['Period End Date'] = period_end # Select and sort columns anchor_df = anchor_df.sort_values(by=sorting).reset_index() anchor_df = anchor_df[[ 'Area', 'Station', 'Calendar Timestamp', 'Anchor Date', 'Temperature (deg F)', 'Temperature Corrected (deg F)', 'Temperature Measurement', 'Temperature Correction', 'Temperature Lower Bound', 'Temperature Upper Bound', 'Snow Depth (")', 'Snow Depth Corrected (")', 'Depth Measurement', 'Depth Correction', 'Depth Lower Bound', 'Depth Upper Bound', 'Snowfall 24 Hour (")', 'Snowfall 24 Hour Corrected (")', 'Snowfall Measurement', 'Snowfall Correction', 'Snowfall Lower Bound', 'Snowfall Upper Bound' ]].sort_values(by=['Area', 'Station', 'Anchor Date', 'Calendar Timestamp']).reset_index(drop=True) # Preview results anchor_df.tail()

# Get the correction count for each combination of station and anchor date models_df = anchor_df.groupby(['Area', 'Station', 'Anchor Date'], as_index=False)[[ 'Temperature Correction', 'Temperature Measurement', 'Depth Correction', 'Depth Measurement', 'Snowfall Correction', 'Snowfall Measurement' ]].sum(min_count=1) models_df.rename(columns={ 'Temperature Correction': 'Temperature Correction Count', 'Temperature Measurement': 'Temperature Measurement Count', 'Depth Correction': 'Depth Correction Count', 'Depth Measurement': 'Depth Measurement Count', 'Snowfall Correction': 'Snowfall Correction Count', 'Snowfall Measurement': 'Snowfall Measurement Count' }, inplace=True) # For each station rank the anchor dates by the least number of corrections models_df['Temperature Accuracy'] = models_df.sort_values(by='Anchor Date').groupby( ['Area', 'Station'], as_index=False)['Temperature Correction Count'].rank( method='first', na_option='keep') models_df['Depth Accuracy'] = models_df.sort_values(by='Anchor Date').groupby( ['Area', 'Station'], as_index=False)['Depth Correction Count'].rank( method='first', na_option='keep') models_df['Snowfall Accuracy'] = models_df.sort_values(by='Anchor Date').groupby( ['Area', 'Station'], as_index=False)['Snowfall Correction Count'].rank( method='first', na_option='keep') # Calculate the correction rate models_df['Temperature Correction Rate'] = models_df['Temperature Correction Count'].fillna(0) \ / models_df['Temperature Measurement Count'] models_df['Depth Correction Rate'] = models_df['Depth Correction Count'].fillna(0) \ / models_df['Depth Measurement Count'] models_df['Snowfall Correction Rate'] = models_df['Snowfall Correction Count'].fillna(0) \ / models_df['Snowfall Measurement Count'] # Identify the model to be applied models_df['Temperature Correction Applied'] = (models_df['Temperature Accuracy'] == 1) \ & (models_df['Temperature Correction Rate'] < permitted_correction_rate) models_df['Depth Correction Applied'] = (models_df['Depth Accuracy'] == 1) \ & (models_df['Depth Correction Rate'] < permitted_correction_rate) models_df['Snowfall Correction Applied'] = (models_df['Snowfall Accuracy'] == 1) \ & (models_df['Snowfall Correction Rate'] < permitted_correction_rate) # Select and sort columns models_df = models_df[[ 'Area', 'Station', 'Anchor Date', 'Temperature Correction Count', 'Temperature Correction Rate', 'Temperature Accuracy', 'Temperature Correction Applied', 'Depth Correction Count', 'Depth Correction Rate', 'Depth Accuracy', 'Depth Correction Applied', 'Snowfall Correction Count', 'Snowfall Correction Rate', 'Snowfall Accuracy', 'Snowfall Correction Applied' ]].sort_values(by=['Area', 'Station', 'Anchor Date']).reset_index(drop=True) # Preview results models_df.tail()

# Get hourly correction values associated with model accuracy anchor2_df = pd.merge( anchor_df[[ 'Area', 'Station', 'Calendar Timestamp', 'Anchor Date', 'Temperature Corrected (deg F)', 'Snow Depth Corrected (")', 'Snowfall 24 Hour Corrected (")']], models_df[[ 'Area', 'Station', 'Anchor Date', 'Temperature Accuracy', 'Temperature Correction Applied', 'Depth Accuracy', 'Depth Correction Applied', 'Snowfall Accuracy', 'Snowfall Correction Applied']], how = 'inner', on = ['Area', 'Station', 'Anchor Date'] ) # Filter to best fit model correction_df = pd.merge( anchor2_df[anchor2_df['Temperature Accuracy']==1][[ 'Area', 'Station', 'Calendar Timestamp', 'Temperature Corrected (deg F)', 'Temperature Accuracy', 'Temperature Correction Applied']], anchor2_df[anchor2_df['Depth Accuracy']==1][[ 'Area', 'Station', 'Calendar Timestamp', 'Snow Depth Corrected (")', 'Depth Accuracy', 'Depth Correction Applied']], how = 'outer', on = ['Area', 'Station', 'Calendar Timestamp'] ) correction_df = correction_df.merge( anchor2_df[anchor2_df['Snowfall Accuracy']==1][[ 'Area', 'Station', 'Calendar Timestamp', 'Snowfall 24 Hour Corrected (")', 'Snowfall Accuracy', 'Snowfall Correction Applied']], how = 'outer', on = ['Area', 'Station', 'Calendar Timestamp'] ) # Force nulls if best fit model is still too inaccurate to be applied correction_df['Temperature Corrected (deg F)'] = np.where( correction_df['Temperature Correction Applied']==True, correction_df['Temperature Corrected (deg F)'], np.NaN) correction_df['Snow Depth Corrected (")'] = np.where( correction_df['Depth Correction Applied']==True, correction_df['Snow Depth Corrected (")'], np.NaN) correction_df['Snowfall 24 Hour Corrected (")'] = np.where( correction_df['Snowfall Correction Applied']==True, correction_df['Snowfall 24 Hour Corrected (")'], np.NaN) # Preview results correction_df.tail()

# Join corrected temperatures to rest of data nwac_corr_df = nwac_precip_df.merge( correction_df[correction_df['Temperature Correction Applied']==True][[ 'Area', 'Station', 'Calendar Timestamp', 'Temperature Corrected (deg F)']], how = 'left', on = ['Area', 'Station', 'Calendar Timestamp'], suffixes = ('', ' Corrected Temperature') ) # Join corrected depth to rest of data nwac_corr_df = nwac_corr_df.merge( correction_df[correction_df['Depth Correction Applied']==True][[ 'Area', 'Station', 'Calendar Timestamp', 'Snow Depth Corrected (")']], how = 'left', on = ['Area', 'Station', 'Calendar Timestamp'], suffixes = ('', ' Corrected Depth') ) # Join corrected snowfall to rest of data nwac_corr_df = nwac_corr_df.merge( correction_df[correction_df['Snowfall Correction Applied']==True][[ 'Area', 'Station', 'Calendar Timestamp', 'Snowfall 24 Hour Corrected (")']], how = 'left', on = ['Area', 'Station', 'Calendar Timestamp'], suffixes = ('', ' Corrected Snowfall') ) # Apply floor and ceiling to non-corrected measures for column in ['Wind Speed Average (mph)', 'Wind Speed Maximum (mph)', 'Wind Speed Minimum (mph)']: nwac_corr_df[column] = np.where(nwac_corr_df[column].between(0, 200), nwac_corr_df[column], np.NaN) # Select and sort columns measures_corr = measures measures_corr.extend(['Temperature Corrected (deg F)', 'Snow Depth Corrected (")', 'Snowfall 24 Hour Corrected (")']) measures_corr = list(set(measures_corr)) measures_corr.sort() nwac_corr_df = nwac_corr_df[dimensions + measures_corr].sort_values(by=sorting).reset_index(drop=True) # Preview results nwac_corr_df.tail()

# Create a dataframe of weather metadata weather_metadata_df = pd.DataFrame({ 'Date Range Start': [period_start], 'Date Range End': [period_end], 'Date Range Days': [DATE_RANGE], 'Job Run Date': [now_pt()] }) # Create a dataframe of correction model metadata model_metadata_df = pd.DataFrame({ 'Correction Model Count': [CORRECTION_COUNT], 'Permitted Correction Rate': [permitted_correction_rate], 'Permitted Temperature Change (deg F)': [permitted_temperature_change], 'Expected Temperature Change (deg F)': [expected_temperature_change], 'Permitted Snow Depth Change (")': [permitted_depth_change], 'Expected Snow Depth Change (")': [expected_depth_change], 'Permitted Snowfall 24 Hour Change (")': [permitted_snowfall_change], 'Expected Snowfall 24 Hour Change (")': [expected_snowfall_change] }) # Capture message message = 'Derived metadata' print(message)

# Connect to Google Sheets gsheets = gspread.service_account(filename = ROOT + CREDENTIALS) # Collect tables as lists of workbook, sheet, source tables = [[WORKBOOKS[0], [['Weather', nwac_corr_df], ['Metadata', weather_metadata_df]]], [WORKBOOKS[1], [['Corrections', anchor_df], ['Correction Models', models_df], ['Metadata', model_metadata_df]]]]

for t in tables: workbook = gsheets.open(t[0]) sheets = t[1] print('\n--{w} Workbook--'.format(w=workbook.title)) for s in sheets: table_name = s[0] table_data = s[1] # Convert columns to strings datetime_columns = table_data.select_dtypes(include=['datetime64[ns]']).columns.tolist() for column in datetime_columns: table_data[column] = table_data[column].astype(str) # Fill nulls table_data.fillna('', inplace=True) # Create new worksheet if table_name not in [w.title for w in workbook.worksheets()]: worksheet = workbook.add_worksheet(title=table_name, rows=0, cols=0) print('Created a new sheet named "{t}"'.format(t=table_name)) # Clear existing worksheet else: worksheet = workbook.worksheet(table_name) worksheet.clear() print('Cleared the "{t}" sheet'.format(t=table_name)) # Insert data into worksheet headers = [table_data.columns.values.tolist()] contents = table_data.values.tolist() worksheet.update(headers + contents) print('Inserted {r} records into the "{t}" sheet'.format(r=len(table_data.index), t=table_name)) # Delete extraneous worksheets for worksheet in workbook.worksheets(): if worksheet.title not in [s[0] for s in sheets]: workbook.del_worksheet(worksheet) print('Deleted the "{s}" sheet'.format(s=worksheet.title))