<jupyter_start><jupyter_text>Plot Visitation Distributions Primary Author<NAME> Description:This notebook plots the distribution of food location visitations for various categories among various groupings of census block groups (CBGs). Inputs:```data/cbg_attr_and_cluster_1021.csvexports/home_weekly.csvexports/poi_health_recategorized.csv``` Visualizations:> Stacked time-series plots of % of food location visitations by POI category\> Stacked time-series plots of % of food location visitations by POI category across income percentiles\> Scatter plots of % of food location visitations by POI category across income percentiles\<jupyter_code>import datetime
import geopandas as gpd
import matplotlib.pyplot as plt
import matplotlib.ticker as mtick
import numpy as np
import pandas as pd
import pytz

import warnings
warnings.filterwarnings('ignore')
def peek(df):
    display(df.iloc[0:3, :])
    print(len(df))
def repair_dates(df):
    df['date_range_start'] = pd.to_datetime(df['date_range_start'], utc=True)
    df['date_range_start'] = df['date_range_start'].dt.tz_convert('US/Eastern')
def smoothen(df, columns=[], N=2):
    """Returns a copy of the given dataframe with a rolling-day average of the
    given size applied to the given columns."""
    rolling_groupby = df.copy().reset_index(level=1).groupby(by=['cluster'])
    rolling_df = rolling_groupby[columns].rolling(N + 1, center=True).mean()
    rolling_df = rolling_df.reset_index()
    rolling_df = rolling_df.sort_values(by=['date_range_start', 'cluster'])
    rolling_df = rolling_df.set_index(['date_range_start', 'cluster'])
    return rolling_df

def smoothen_within(df, columns=[], by=None, N=6):
    """Returns a copy of the given dataframe with a rolling-day average of the
    given size applied to the given columns within each attribute class
    determined by the given "by" attribute."""
    attr_classes = set(df[by])
    rolling_dfs = []
    for attr_class in attr_classes:
        attr_class_df = df[df[by] == attr_class]
        rolling_dfs.append(smoothen(attr_class_df, columns, N=N))
    rolling_df = pd.concat(rolling_dfs)
    rolling_df = rolling_df.sort_values(by=['date_range_start', by])
    return rolling_df<jupyter_output><empty_output><jupyter_text>Segment Supermarkets and Grocery Stores by CBG Income<jupyter_code>poi_df = pd.read_csv(f'./exports/poi_health_recategorized.csv')
peek(poi_df)
cbg_df = pd.read_csv('./data/cbg_attr_and_cluster_1021.csv')
cbg_df = cbg_df.rename(columns={
    'census_block_group': 'cbg',
    'Median Household Income': 'income',
    'Total Population': 'population',
})
cbg_df = cbg_df[['cbg', 'income', 'population']]
peek(cbg_df)
def add_percentiles(df, column):
    cdf = df.copy()
    percents = list(range(10, 110, 10))
    values = [np.percentile(cdf[column].dropna(), p) for p in percents]
    
    cdf[f'{column}_percentile'] = None
    for i, value in enumerate(values):
        cdf.loc[(cdf[f'{column}_percentile'].isna()) & (cdf[column] <= value), f'{column}_percentile'] = percents[i] 
    return cdf
sgs_df = poi_df.merge(cbg_df, on='cbg', how='inner')
sgs_df = sgs_df[sgs_df['category'] == 'Delis and Convenience Stores']
sgs_df = add_percentiles(sgs_df, 'income')
sgs_df = add_percentiles(sgs_df, 'area_square_feet')
peek(sgs_df)
sgs_count_df = sgs_df.groupby(by=['income_percentile', 'area_square_feet_percentile']).agg({'placekey': 'count'}).reset_index()
sgs_count_df = sgs_count_df.rename(columns={'placekey': 'count'})
peek(sgs_count_df)
home_weekly_df = pd.read_csv(f'./exports/home_weekly.csv')
repair_dates(home_weekly_df)
home_weekly_df = home_weekly_df[home_weekly_df['date_range_start'].dt.year.isin([2019, 2020])]
home_weekly_df = home_weekly_df.rename(columns={'home_cbg': 'cbg'})
peek(home_weekly_df)
sgs_weekly_df = sgs_df.merge(home_weekly_df, on='placekey', how='inner')
sgs_weekly_df = sgs_weekly_df.rename(columns={
    'cbg_x': 'poi_cbg',
    'cbg_y': 'home_cbg',
    'income': 'poi_income',
    'income_percentile': 'poi_income_percentile',
})
sgs_weekly_df = sgs_weekly_df[[
    'placekey',
    'date_range_start',
    'estimated_visitor_count',
    'area_square_feet_percentile',
    'poi_cbg',
    'poi_income',
    'poi_income_percentile',
    'home_cbg',
]]
peek(sgs_weekly_df)
sgs_income_df = sgs_weekly_df.merge(cbg_df, left_on='home_cbg', right_on='cbg')
sgs_income_df = add_percentiles(sgs_income_df, 'income')
sgs_income_df = sgs_income_df.rename(columns={
    'income': 'home_income',
    'income_percentile': 'home_income_percentile',
})
sgs_income_df = sgs_income_df[[
    'placekey',
    'date_range_start',
    'estimated_visitor_count',
    'area_square_feet_percentile',
    'poi_cbg',
    'poi_income',
    'poi_income_percentile',
    'home_cbg',
    'home_income',
    'home_income_percentile',
]]
peek(sgs_income_df)
sgs_income_df['income_direction'] = np.where(sgs_income_df['home_income'] > sgs_income_df['poi_income'], 1, 0)
sgs_income_df['year'] = sgs_income_df['date_range_start'].dt.year
sgs_income_df['month'] = sgs_income_df['date_range_start'].dt.month
sgs_income_df = sgs_income_df[(sgs_income_df['month'] >= 2) & (sgs_income_df['month'] <=5)]
peek(sgs_income_df)
poi_weekly_df = pd.read_csv('./exports/poi_weekly.csv')
poi_weekly_df = poi_weekly_df[['placekey', 'date_range_start', 'median_dwell', 'contact_density']]
repair_dates(poi_weekly_df)
peek(poi_weekly_df)
sgs_dwell_df = sgs_income_df.merge(poi_weekly_df, on=['placekey', 'date_range_start'], how='inner')
sgs_id_df = sgs_dwell_df.groupby(by=['year', 'poi_income_percentile', 'area_square_feet_percentile']).agg({'estimated_visitor_count': 'sum', 'median_dwell': 'mean', 'contact_density': 'median'}).reset_index()
peek(sgs_id_df)
from sklearn.preprocessing import StandardScaler
sgs_id_df[['std_estimated_visitor_count']] = StandardScaler().fit_transform(sgs_id_df[['estimated_visitor_count']]) * 40 + 20
peek(sgs_id_df)
sgs_id_df.plot(kind='scatter', x='poi_income_percentile', y='area_square_feet_percentile', s='std_estimated_visitor_count')
p_2019_df = sgs_id_df[sgs_id_df['year'] == 2019]
p_2019_df.plot(kind='scatter', x='poi_income_percentile', y='area_square_feet_percentile', s='std_estimated_visitor_count')
p_2020_df = sgs_id_df[sgs_id_df['year'] == 2020]
p_2020_df.plot(kind='scatter', x='poi_income_percentile', y='area_square_feet_percentile', s='std_estimated_visitor_count')
p_2019_df = sgs_id_df[sgs_id_df['year'] == 2019].groupby(by=['area_square_feet_percentile']).agg({'estimated_visitor_count': 'sum', 'contact_density': 'median'}).reset_index()
p_2020_df = sgs_id_df[sgs_id_df['year'] == 2020].groupby(by=['area_square_feet_percentile']).agg({'estimated_visitor_count': 'sum', 'contact_density': 'median'}).reset_index()
pdf = p_2020_df.copy()
pdf['estimated_visitor_count'] = (p_2020_df['estimated_visitor_count'] - p_2019_df['estimated_visitor_count']) / p_2019_df['estimated_visitor_count']
pdf['contact_density'] = (p_2020_df['contact_density'] - p_2019_df['contact_density']) / p_2019_df['contact_density']
pdf.plot(kind='scatter', x='area_square_feet_percentile', y='estimated_visitor_count')
pdf.plot(kind='scatter', x='area_square_feet_percentile', y='contact_density')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14212209706577666, 'fraction_numerical': 0.014601585314977055, 'mean_word_length': 7.324074074074074, 'pattern_counts': {'":': 0, '<': 12, '<?xml version=': 0, '>': 15, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '593107', 'license': 'MIT', 'n_tokens_mistral': 2884, 'n_tokens_neox': 2713, 'n_words': 434, 'path': 'notebooks/archive/cc0105_plot_income_assessment.ipynb', 'repo_name': 'cpcarey/nyu-cusp-usi', 'size': '7196'}