<jupyter_start><jupyter_text>Data organized    Initialily to organize the data I rewrote the order_products grouping by order_id. Thus, the full data after    the manipulation will be organized by the order_id as variable key.  Load helper functions<jupyter_code>%run ./functions.ipynb<jupyter_output><empty_output><jupyter_text>Read the data   * Dataset order_products<jupyter_code>path0 = os.getcwd()
order_products = pd.read_csv(os.path.join(path0, "order_products.csv"))
order_products.isnull().sum()
order_products.info()<jupyter_output><empty_output><jupyter_text>Create new variables to order_products dataset   Here I use the **order_products_var** function to create new variables:   * quantity_UN: Quantity of products in units.   * quantity_KG: Quantity of products in KG.   After create these variables I agroup the dataset by order_id and create a other new variables:   * UN_plus_KG: Quantity of units plus Quantity of KG   * UN_mult_KG: Quantity of units times Quantity of KG<jupyter_code>order_products = order_products_var(order_products)
order_products.head()
order_products.isnull().sum()<jupyter_output><empty_output><jupyter_text>* Dataset orders<jupyter_code>orders = pd.read_csv(os.path.join(path0, "orders.csv"))<jupyter_output><empty_output><jupyter_text>Here I rename the lat and long variables to indentify the end of the path<jupyter_code>orders = orders.rename({'lat': 'lat_destination', 'lng': 'lng_destination'}, axis=1)  
orders.isnull().sum()
orders.info()
orders.head(3)<jupyter_output><empty_output><jupyter_text>Join the datasets (orders and order_products)<jupyter_code>full = pd.merge(orders, order_products, how='inner', on='order_id')
full.isnull().sum()<jupyter_output><empty_output><jupyter_text>* Dataset shoppers<jupyter_code>shoppers = pd.read_csv(os.path.join(path0, "shoppers.csv"))
shoppers.isnull().sum()<jupyter_output><empty_output><jupyter_text>Treatment of missing in shoppers dataset   The found_rate, accepted_rate and rating variables have missing. To treat this I filter the dataframe taking just qhere found_rate is null then I find the values to picking_speed in the new dataframe. With these values I create a new dataframe making a filter in original dataframe using the picking_speed values founded, **found_rate2** dataframe. After that I fill the found_rate missing using the mode of **found_rate2** dataframe.      The missing value of accepted_rate and rating variabel were filled using the mode of column in original dataset.<jupyter_code>shoppers.info()
found_rate = shoppers[shoppers.found_rate.isnull()]
found_rate.head()
found_rate.picking_speed.unique()
found_rate2 = shoppers[(shoppers.picking_speed == 2.41) | (shoppers.picking_speed == 2.19) | (shoppers.picking_speed == 2.33)]
shoppers.loc[shoppers['found_rate'].isnull(), 'found_rate'] = found_rate2['found_rate'].mode().iat[0]
shoppers.loc[shoppers['accepted_rate'].isnull(), 'accepted_rate'] = shoppers['accepted_rate'].mode().iat[0]
shoppers.loc[shoppers['rating'].isnull(), 'rating'] = shoppers['rating'].mode().iat[0]
shoppers.isnull().sum()<jupyter_output><empty_output><jupyter_text>Join the datasets (full and shoppers)<jupyter_code>full = pd.merge(full, shoppers, how='left', on='shopper_id')
full.isnull().sum()<jupyter_output><empty_output><jupyter_text>* Dataset storebranch<jupyter_code>storebranch = pd.read_csv(os.path.join(path0, "storebranch.csv"))<jupyter_output><empty_output><jupyter_text>Here I rename the lat and long variables to indentify the beginning of the path<jupyter_code>storebranch = storebranch.rename({'lat': 'lat_origin', 'lng': 'lng_origin'}, axis=1) 
storebranch.isnull().sum()
storebranch.info()
storebranch.head(3)<jupyter_output><empty_output><jupyter_text>Join the datasets (full and storebranch)<jupyter_code>full = pd.merge(full, storebranch, how='left', on='store_branch_id')
full.isnull().sum()
full = time_sep(full, col = 'promised_time')
full.columns<jupyter_output><empty_output><jupyter_text>Distance between origin and destiny and car pathThe datasets offered have lat and long to origin and destiny points. Using these information I calculate the Haversine distance:   * The Haversine calculator computes the distance between two points on a spherical model of the Earth along a great circle arc.   I calculate too the path by car between the coordenates using the project-osrm project API.The Open Source Routing Machine or OSRM is a C++ implementation of a high-performance routing engine for shortest paths in road networks. Licensed under the permissive 2-clause BSD license, OSRM is a free network service.It combines sophisticated routing algorithms with the open and free road network data of the OpenStreetMap (OSM) project. Shortest path computation on a continental sized network can take up to several seconds if it is done without a so-called speedup-technique. OSRM uses an implementation of contraction hierarchies and is able to compute and output a shortest path between any origin and destination within a few milliseconds, whereby the pure route computation takes much less time. Most effort is spent in annotating the route and transmitting the geometry over the network.   * **distance**: The distance traveled by the route, in float meters.   * **duration**: The estimated travel time, in float number of seconds.   * **weight**: The calculated weight of the route.<jupyter_code>full['distance_haversine'] = distance(full.lat_origin, full.lng_origin, full.lat_destination, full.lng_destination)
full[['lng_origin', 'lat_origin', 'lng_destination', 'lat_destination']].head(3)
%%time
full['distance_car'], full['weight_car'], full['duration'] = path(full, 'lng_origin', 'lat_origin', 'lng_destination', 'lat_destination')<jupyter_output><empty_output><jupyter_text>As the function is slow, I didn't find a way to improve the performance and create a for. I write the dataframe after each function that uses a API.<jupyter_code>full.to_csv(os.path.join(path0, "full_distance.csv"))<jupyter_output><empty_output><jupyter_text>In the next cells I use the geopy package to find city, state, county and neighbourhood. I do this to the store lat and long and the destination lat and long.<jupyter_code>full['city_origin'], full['state_origin'], full['county_origin'], full['neighbourhood_origin'] = location(full, 'lat_origin', 'lng_origin')
full.to_csv(os.path.join(path0, "full_origin.csv"))
full['city_destiny'], full['state_destiny'], full['county_destiny'], full['neighbourhood_destiny'] = location(full, 'lat_destination', 'lng_destination')
full.to_csv(os.path.join(path0, "full_destiny.csv"))<jupyter_output><empty_output><jupyter_text>In the next cell I create new variables to the path of delivery. These variables indicate if the store and destination are in the same city, state, county or neighbourhood<jupyter_code>full.loc[full['city_origin'] == full['city_destiny'], 'same_city'] = 1 
full.loc[full['city_origin'] != full['city_destiny'], 'same_city'] = 0

full.loc[full['state_origin'] == full['state_destiny'], 'same_state'] = 1 
full.loc[full['state_origin'] != full['state_destiny'], 'same_state'] = 0

full.loc[full['county_origin'] == full['county_destiny'], 'same_county'] = 1 
full.loc[full['county_origin'] != full['county_destiny'], 'same_county'] = 0

full.loc[full['neighbourhood_origin'] == full['neighbourhood_destiny'], 'same_neighbourhood'] = 1 
full.loc[full['neighbourhood_origin'] != full['neighbourhood_destiny'], 'same_neighbourhood'] = 0
full.loc[full['city_origin'].isnull(), 'city_origin'] = 'undefined'
full.loc[full['city_destiny'].isnull(), 'city_destiny'] = 'undefined'<jupyter_output><empty_output><jupyter_text>In the next cell I create a categorical variable with the origin and destintion of order.<jupyter_code>full['path_city'] = full['city_origin'] + '_X_' + full['city_destiny']
full['path_state'] = full['state_origin'] + '_X_' + full['state_destiny']
full['path_county'] = full['county_origin'] + '_X_' + full['county_destiny']<jupyter_output><empty_output><jupyter_text>Here I agroup the store_id and count the shoppers by store<jupyter_code>shop = full.groupby('store_id').aggregate(
                            {'shopper_id': 'count'
                            }).reset_index()
shop = shop.rename({'shopper_id': 'shoppers_number'}, axis=1)  

full2 = pd.merge(full, shop, how='left', on='store_id')<jupyter_output><empty_output><jupyter_text>Here I agroup the store_id and count the store_branch by store<jupyter_code>branch = full2.groupby('store_id').aggregate(
                            {'store_branch_id': 'count'
                            }).reset_index()
branch = branch.rename({'store_branch_id': 'store_branch_number'}, axis=1)  

full2 = pd.merge(full2, branch, how='left', on='store_id')
full2.isnull().sum()
full2.to_csv(os.path.join(path0, "full_new.csv"))
#fim<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1199051597606413, 'fraction_numerical': 0.005306537202212939, 'mean_word_length': 6.763365468886941, 'pattern_counts': {'":': 0, '<': 77, '<?xml version=': 0, '>': 77, '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': '19', 'dir': 'jupyter-structured-clean-dedup', 'id': '256306', 'license': 'MIT', 'n_tokens_mistral': 2819, 'n_tokens_neox': 2776, 'n_words': 755, 'path': 'EDA_Features.ipynb', 'repo_name': 'sanchobuendia/test_cornershop', 'size': '8857'}