<jupyter_start><jupyter_text>Exploratory Data Analysis (EDA): Competitor Concentration<jupyter_code>#joining business and neighborhood tables from SQL database
businesses_neighborhoods = pd.read_sql_query('''SELECT
    Name,
    Rating,
    Review_Count,
    stores_and_services.ZipCode,
    Neighborhood,
    Borough
    FROM stores_and_services
    LEFT JOIN neighborhoods
    ON stores_and_services.ZipCode = neighborhoods.ZipCode''', conn)

businesses_neighborhoods = businesses_neighborhoods.set_index('Name')
businesses_neighborhoods
businesses_neighborhoods.describe()
concentration = businesses_neighborhoods.groupby('Name').size().reset_index(name='Stores')
concentration = concentration.sort_values(by='Stores', ascending=False)
concentration['Share %'] = concentration['Stores'] / concentration['Stores'].sum() * 100
concentration['Concentration Index'] = concentration['Share %'].cumsum()
concentration = concentration.reset_index()
concentration = concentration.drop('index', axis=1)
concentration.index = concentration.index + 1
concentration.head(8)
HHI_industry = sum(concentration['Share %']**2)
HHI_industry
n_concentration = businesses_neighborhoods.groupby('Neighborhood').size().reset_index(name='Stores')
n_concentration = n_concentration.sort_values(by='Stores', ascending=False)
n_concentration['Share %'] = n_concentration['Stores'] / n_concentration['Stores'].sum() * 100
n_concentration['Concentration Index'] = n_concentration['Share %'].cumsum()
n_concentration = n_concentration.reset_index()
n_concentration = n_concentration.drop('index', axis=1)
n_concentration.index = n_concentration.index + 1
n_concentration.head(8)
HHI_neighborhood = sum(n_concentration['Share %']**2)
HHI_neighborhood<jupyter_output><empty_output><jupyter_text>Exploratory Data Analysis (EDA): Market Size<jupyter_code>#initialize dog growth table with unique list of neighborhoods
key = 'Neighborhood'
dog_growth = pd.read_sql_query('SELECT DISTINCT {}, Borough FROM neighborhoods'.format(key), conn)
dog_growth = dog_growth.set_index(key)
print(dog_growth.shape)
dog_growth.head()
#function to select dogs by neighborhoods for a given year
def get_dogs_by_year(year):
    return pd.read_sql_query("""SELECT {}, LicenseIssuedYear AS CY{}
                                FROM dogs
                                LEFT JOIN neighborhoods
                                ON dogs.ZipCode = neighborhoods.ZipCode
                                WHERE LicenseIssuedYear <= {}
                                AND LicenseExpiredYear >= {};""".format(key, year, year, year), conn)

for i in range(2014, 2023):
    dog_growth = pd.merge(dog_growth, get_dogs_by_year(i).groupby(key).count(), on=key, how='left')
    
dog_growth['CY2022'] = dog_growth['CY2022'].fillna(0).astype(int)
dog_growth
total_market = dog_growth.sum()
total_market
dog_growth['5Y CAGR 2014-2019'] = (((dog_growth['CY2019'] / dog_growth['CY2014']) ** (1/5)) - 1) * 100
dog_growth = dog_growth.sort_values(by=['5Y CAGR 2014-2019'], ascending=False)
dog_growth = dog_growth.replace(np.inf, np.nan)
dog_growth = dog_growth.dropna(subset=['5Y CAGR 2014-2019'])
dog_growth
dog_growth['Share 2019'] = dog_growth['CY2019'] / total_market['CY2019'] * 100
dog_growth = dog_growth.sort_values(by=['Share 2019'], ascending=False)
dog_growth
bcg_matrix = pd.merge(dog_growth, n_concentration, on=key, how='left')
bcg_matrix = bcg_matrix.drop(columns=['Share %', 'Concentration Index'])
bcg_matrix['Dogs per Store'] = bcg_matrix['CY2016'] / bcg_matrix['Stores']
bcg_matrix['Dogs per Store'] = bcg_matrix['Dogs per Store'].astype(int)
bcg_matrix['5Y CAGR 2014-2019'] = bcg_matrix['5Y CAGR 2014-2019'].round(2)
bcg_matrix['Share 2019'] = bcg_matrix['Share 2019'].round(2)
bcg_matrix
bcg_matrix.describe()
import matplotlib.pyplot as plt
import seaborn as sns

ax = sns.relplot(x='Share 2019',
            y='5Y CAGR 2014-2019',
            hue='Dogs per Store',
            palette=sns.color_palette("RdYlGn", 38),
            size='Stores',
            sizes=(100,10000),
            data=bcg_matrix,
            legend=False,
            height=10);
sns.set_style('darkgrid')
ax.set(xlim=(0, 10))
ax.set(ylim=(0, 45))
plt.xlabel('Market Share in 2019 [%]')
plt.ylabel('5-Year CAGR from 2014 to 2019 [%]')
#close connection
close_c_conn()
len(bcg_matrix['Dogs per Store'].unique())
import plotly
plotly.tools.set_credentials_file(username=plotly_id, api_key=plotly_key)
import plotly.plotly as py
import plotly.graph_objs as go

hover_text = []
color_range = []
for index, row in bcg_matrix.iterrows():
    hover_text.append(('Borough: {borough}<br>'+
                      'Neighborhood: {neighborhood}<br>'+
                      'Share: {share}%<br>'+
                      'Growth: {growth}%<br>'+
                      'Stores: {stores}<br>'+
                      'Dogs per Store: {pps}').format(borough=row['Borough'],
                                            neighborhood=row['Neighborhood'],
                                            share=row['Share 2019'],
                                            growth=row['5Y CAGR 2014-2019'],
                                            stores=row['Stores'],
                                            pps=row['Dogs per Store']))
    color_range.append(min(row['Dogs per Store'],120))

trace0 = go.Scatter(
    x=bcg_matrix['Share 2019'],
    y=bcg_matrix['5Y CAGR 2014-2019'],
    text=hover_text,
    mode='markers',
    marker=dict(
        size=bcg_matrix['Stores'],
        color=color_range,
        showscale=True,
        reversescale=True,
        colorbar=dict(
                title='Dogs<br>Per<br>Store<br>(2016)'
            ),
        colorscale='RdBu'
    )
)

data = [trace0]
layout = go.Layout(
    title='Growth-Share Matrix of Licensed Dogs in New York by Neighborhood',
    xaxis=dict(
        title='Neighborhood Share of Licensed Dogs, 2019 [%]',
        gridcolor='rgb(255, 255, 255)',
        zerolinewidth=1,
        ticklen=5,
        gridwidth=2,
    ),
    yaxis=dict(
        title='5-Year CAGR of Licensed Dogs, 2014-2019 [%]',
        gridcolor='rgb(255, 255, 255)',
        zerolinewidth=1,
        ticklen=5,
        gridwidth=2,
    ),
    paper_bgcolor='rgb(243, 243, 243)',
    plot_bgcolor='rgb(243, 243, 243)',
)

fig = go.Figure(data=data, layout=layout)
py.iplot(fig, filename='bcg-matrix')<jupyter_output>/anaconda3/lib/python3.7/site-packages/IPython/core/display.py:689: UserWarning:

Consider using IPython.display.IFrame instead
{'fraction_non_alphanumeric': 0.1338060736858332, 'fraction_numerical': 0.04054262370895637, 'mean_word_length': 3.5244072524407253, 'pattern_counts': {'":': 0, '<': 17, '<?xml version=': 0, '>': 17, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 13, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '311740', 'license': 'MIT', 'n_tokens_mistral': 2417, 'n_tokens_neox': 2036, 'n_words': 389, 'path': 'src/04_Exploratory_Data_Analysis.ipynb', 'repo_name': 'acdick/framing_data_with_dataframes', 'size': '6487'}