# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

# + [markdown] pycharm={"name": "#%% md\n"}
# # Converting ``storebranch.csv``
# This is our final table to convert, and it's the simplest one, we only need to join
# store's lat and lng location to the main table through store_branch_id, let's check it again!
#
# Also, there's no missing data according to the pandas profile, so our process is very
# straight-forward.

# + pycharm={"name": "#%%\n"}
import pandas as pd
import matplotlib.pyplot as plt
from sklearn.cluster import KMeans

# + pycharm={"name": "#%%\n"}
stores = pd.read_csv('./data/storebranch.csv')
stores

# + pycharm={"name": "#%%\n"}
# check the number of unique values for store_branch_id and store_id
stores['store_branch_id'].value_counts()

# + pycharm={"name": "#%%\n"}
stores['store_id'].value_counts()


# + [markdown] pycharm={"name": "#%% md\n"}
# So store_id holds the branch name which is repeated on the data and store_branch_id is the
# unique identifier to join, we will add a new field where we count the number of stores
# a branch has, which could be related to how well the store performs time-wise.

# + pycharm={"name": "#%%\n"}
# add the total count to the dataframe for each store branch
def add_branch_count(df):
    container = []  # stores the field values
    hashtable = dict()  # stores the count for each branch
    # first we count stores and add them to the dict with O(n) complexity
    for store in df['store_id']:
        if store in hashtable:  # we sum it up
            hashtable[store] += 1
        else:  # we need to initialize it
            hashtable[store] = 1
    # now we can use the hashtable to add the count to each field
    for store in df['store_id']:
        container.append(hashtable[store])
    df['branch_size'] = container

    return


# + pycharm={"name": "#%%\n"}
# test the function
add_branch_count(stores)
stores

# + [markdown] pycharm={"name": "#%% md\n"}
# Now all we need to confirm is the extension of the stores, are we in a city or multiple cities?

# + pycharm={"name": "#%%\n"}
plt.scatter(stores['lat'], stores['lng'])
plt.show()

# + [markdown] pycharm={"name": "#%% md\n"}
# It seems obvious that we have 4 cities in the database (for example the biggest cluster is
# definitely Santiago). We could also tag lat, lng as part of a cluster which could make more
# sense for a model. For the model it won't matter if we tag a value as 'Santiago' or '1'.
#
# Because we know there are four clusters we can use a k-means model directly.

# + pycharm={"name": "#%%\n"}
# create a k-means cluster for lat, lng data
kmeans = KMeans(n_clusters=4, random_state=0).fit(stores[['lat', 'lng']])
kmeans

# + pycharm={"name": "#%%\n"}
# test the model with an example
predictions = kmeans.predict(stores[['lat', 'lng']])
predictions[:20]

# + pycharm={"name": "#%%\n"}
# ploy the clustered scatter
palette = ["blue","red","green","yellow"]
colors = [palette[i] for i in predictions]
plt.scatter(stores['lat'], stores['lng'], c=colors)
plt.show()

# + [markdown] pycharm={"name": "#%% md\n"}
# The cluster looks good, let's add it to the table

# + pycharm={"name": "#%%\n"}
stores['city'] = predictions
stores

# + [markdown] pycharm={"name": "#%% md\n"}
# ## Conclusions
# Very straight-forward table, we add city cluster to help a model make decisions based on
# city relations as cities could have different timings according to distances. Also branch size
# of the store could be related on how fast a request can be filled. If this database needs tuning
# in the future we can tokenize stores according to it's size but keeping them separate, as two
# stores having the same branch size are different tokens.
#
# Finally, let's save this table to be used in the final dataset.

# + pycharm={"name": "#%%\n"}
# we won't be needing the store_id, it was transformed into branch_size
#stores.drop('store_id', axis=0, inplace=True)
stores

# + pycharm={"name": "#%%\n"}
stores.to_csv('./data/storebranch_processed.csv')

StarcoderdataJupyterScriptsDedupFiltered