<jupyter_start><jupyter_text>Get Origin Block Groups from PatternsWhite Pass Ski area, when you ride up on the lift it is common to discover people next to you hail from Yakima, the Tri-Cities, Portland-Vancouver, Olympia and Tacoma. White Pass is well known for being a very low-key, friendly and extremely unpretentious ski area. With Mt. Hood near Portland and Stephens Pass and Crystal near Seattle getting increasingly crowded, there is a belief more people are beginning to think the drive to White Pass is worth it. Talking to people on the lift, though, is little more than antecdoctal. Where are people _really_ coming from, and are these patterns changing? We can investigate these trends using Safegraph Patterns data.<jupyter_code># this url will be used later, but it's here at the top for easily finding
# the url to block groups on ArcGIS Online may change, so you may need to change this later

bg_url = 'https://services.arcgis.com/P3ePLMYs2RVChkJx/arcgis/rest/services/USA_Block_Groups/FeatureServer/0/'
import importlib
import json
import os
from pathlib import Path
import sys

from arcgis.features import GeoAccessor, FeatureLayer
import pandas as pd
from modeling import Country
dir_prj = Path.cwd().parent

dir_data = dir_prj/'data'

dir_raw = dir_data/'raw'
dir_int = dir_data/'interim'

gdb_raw = dir_raw/'raw.gdb'
gdb_int = dir_int/'interim.gdb'<jupyter_output><empty_output><jupyter_text>Using the previously downloaded patterns data for White Pass, the first step is simply loading the data into a Pandas DataFrame. This DataFrame contains quite a few columns encoded as dictionaries, which can be expanded for more data detail.<jupyter_code>wp_pth = dir_raw/'patterns_white_pass.csv'
wp_df = pd.read_csv(wp_pth, index_col=0)

wp_df.head()<jupyter_output><empty_output><jupyter_text>One of these columns `visitor_home_cbgs` is a dictionary of Census Block Groups with the count of unique devices originating in the block group visiting the point of interest, in this case White Pass Ski Area, in each of the months. Since this column is read in as a string from the CSV, we are going to convert it to a dictionary using `json.loads`.<jupyter_code>wp_df.visitor_home_cbgs = wp_df.visitor_home_cbgs.apply(lambda val: json.loads(val))

wp_df.visitor_home_cbgs<jupyter_output><empty_output><jupyter_text>The ski season generally runs November through March. Although we have data for the entire year, we are only going to focus on the months comprising the ski season. To do this, we need to extract the year and month explicitly from a datetime field. From there, we can filter to just the months we are interested in.<jupyter_code>mnths = [11, 12, 1, 2, 3, 4]

wp_df['year'] = pd.to_datetime(wp_df.date_range_start).apply(lambda dt: dt.year)
wp_df['month'] = pd.to_datetime(wp_df.date_range_start).apply(lambda dt: dt.month)

dict_df = wp_df[wp_df.month.isin(mnths)][['year', 'month', 'visitor_home_cbgs']].sort_values(['year', 'month'])<jupyter_output><empty_output><jupyter_text>Since, at least in this case, we are interested in trends for each ski season as a whole, we are also going to create a label we can later use to bin the data.<jupyter_code>dict_df['season'] = dict_df[['year', 'month']].apply(lambda r: f'{r.year-1}_{r.year}' if r.month < 6 else f'{r.year}_{r.year+1}', axis=1)

dict_df.head()<jupyter_output><empty_output><jupyter_text>Next, although a trival step, we reduce the dataframe down to just the columns we need, the binning season and the dictionary of home block groups and device counts.<jupyter_code>dict_df = dict_df[['season', 'visitor_home_cbgs']]

dict_df.head()<jupyter_output><empty_output><jupyter_text>Next, we explode out the all the values from the dictionary to be columns in the dataframe.<jupyter_code>season_df = dict_df[['season']].join(dict_df.visitor_home_cbgs.apply(pd.Series))

season_df.head()<jupyter_output><empty_output><jupyter_text>Now, we can simply use `groupby` and `max` to get the maximum number of devices per month originating from each block group for each season. Also, while we are at it, we can fill in any missing values with zeros.<jupyter_code>season_df = season_df.groupby('season').max().fillna(0)

season_df.head()<jupyter_output><empty_output><jupyter_text>With all this organized, now we can organize the data by the block group id using `transpose`.<jupyter_code>season_bg_df = season_df.transpose()

season_bg_df
dlta_df = (season_df - season_df.shift()).transpose()
dlta_df.drop(columns=dlta_df.columns[0], inplace=True)
dlta_df.columns = [f'{c}_delta' for c in dlta_df.columns]

dlta_df
season_delta_df = season_bg_df.join(dlta_df)
season_delta_df = season_delta_df[season_delta_df.columns.sort_values()]
season_delta_df.columns = [f'season_{c}' for c in season_delta_df.columns]

season_delta_df
season_delta_df.to_csv(dir_int/'block_group_raw.csv')<jupyter_output><empty_output><jupyter_text>Get Geometry from Block Groups on ArcGIS Online<jupyter_code>bg_lyr = FeatureLayer(bg_url)

bg_lyr
bg_id_lst = dlta_df.index
bg_id_str_lst = [f"'{bgid}'" for bgid in bg_id_lst]
bg_id_str = ','.join(bg_id_str_lst)

where_str = f'FIPS IN ({bg_id_str})'
bg_shp_df = bg_lyr.query(where_str, out_fields=['FIPS']).sdf

bg_shp_df.info()
bg_df = bg_shp_df.join(season_delta_df, on='FIPS').drop(columns='OBJECTID_1')
bg_df.spatial.set_geometry('SHAPE')

bg_df.head()
bg_df.info()
bg_df.spatial.to_featureclass(gdb_raw/'block_group_patterns')
bg_df.SHAPE = bg_df.SHAPE.apply(lambda geom: json.dumps(geom.JSON))

bg_df.info()
bg_df.to_csv(dir_int/'block_group_patterns.csv')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09461634770166338, 'fraction_numerical': 0.0033983187265247718, 'mean_word_length': 6.290743155149935, 'pattern_counts': {'":': 0, '<': 42, '<?xml version=': 0, '>': 41, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '91214', 'license': 'Apache-2.0', 'n_tokens_mistral': 1925, 'n_tokens_neox': 1886, 'n_words': 588, 'path': 'notebooks/00-patterns-preprocessing.ipynb', 'repo_name': 'knu2xs/white-pass-feature-selection', 'size': '5591'}