<jupyter_start><jupyter_text>**Tech_savvy** IIT (ISM), Dhanbad CrossSell_Upsell Problem Statement In this project, we have built a hybrid recommender system based on user based collaborative filtering and context aware filtering technique to recommend products to the customer for cross selling and upselling. Unsupervised learning algorithm K-Means Clustering is used to determine the location cluster to which the customer belongs based on their geographical coordinates. The insights gained from this are used to suggest products based on user-user based cosine similarity. This recommender system will thus help to increase its profit margins.  Importing data and libraries In order to upload the excel data in the colab file, first the excel file is uploaded in the google drive which is then mounted in this colab file.<jupyter_code>from google.colab import drive
drive.mount('/content/gdrive')
import numpy as np
import pandas as pd
from math import ceil, isnan

import matplotlib.pyplot as plt
import seaborn as sns
from pickle import dump, load

import folium
import geopy

from sklearn import preprocessing
from sklearn.cluster import KMeans
from sklearn.metrics import silhouette_score
from sklearn.metrics.pairwise import cosine_similarity<jupyter_output><empty_output><jupyter_text>Loading the data from google drive.<jupyter_code>df = pd.read_excel('/content/gdrive/MyDrive/Maverick Hackathon/Data.xlsx')<jupyter_output><empty_output><jupyter_text>EDAHere we have carried out exploratory data analysis in order to understand the data which will help us in deciding the route forward.<jupyter_code>df.head(10)
df.info()
print('Material wise distribution of dataset:\n')
print(df.groupby('Material').size())
print('Wholesaler wise distribution of dataset:\n')
print(df.groupby('Ship-to nu').size())
print('\nStatistics of dataset: ')
df.describe()
print('Null values of in respective columns are:\n')
df.isnull().sum()<jupyter_output>Null values of in respective columns are:<jupyter_text>Data Pre-Processing and Data Visualization As we can see that multiple fields of latitude, longitude and MACO/HL which are NULL, we will have to remove the fields which have NULL values in these fields. Also as the dataset contains nearly 73000 data points, removing some of them will not affect our results significantly.<jupyter_code>df.dropna(subset = ['MACO/HL ', 'Latitude', 'Longitude'], inplace = True)
df.reset_index(drop = True, inplace = True)
df.isnull().sum()<jupyter_output><empty_output><jupyter_text>Here we are adding an extra column of total product sold in HL corresponding to each row, in our dataframe.<jupyter_code>tot = []
print(df.shape[0])

for i in range(df.shape[0]):
  tot.append(int(ceil(df._get_value(i, 'Order qty')*df._get_value(i, 'HL delivered'))))

df.insert(7, "Total_HL", tot)<jupyter_output>65222<jupyter_text>In order to segregate data on the basis of geographical coordinates of the wholesaler, we define two dictionaries loc_wise_sales which stores the total sales of product in HL and loc_wise_maco which stores the MACO/HL, both corresponding to a pair of latitude and longitude values.<jupyter_code>loc_wise_sales = {}
loc_wise_maco = {}
loc_wise_user_id = {}
wholesaler_loc = {}

for i in range(df.shape[0]):
  if(isnan(df._get_value(i, "Latitude")) or isnan(df._get_value(i, "Longitude")) or isnan(df._get_value(i, "MACO/HL "))):
    continue
  temp = str(df._get_value(i, "Latitude")) + ", " + str(df._get_value(i, "Longitude"))
  if(temp in loc_wise_sales.keys()):
    loc_wise_sales[temp]+=df._get_value(i, "Total_HL")
  else:
    loc_wise_sales[temp]=df._get_value(i, "Total_HL")
  if(temp in loc_wise_maco.keys()):
    loc_wise_maco[temp]+=df._get_value(i, "MACO/HL ")
  else:
    loc_wise_maco[temp]=df._get_value(i, "MACO/HL ")
  if(temp not in loc_wise_user_id.keys()):
    loc_wise_user_id[temp]=str(df._get_value(i, "Ship-to nu"))
  wholesaler_loc[str(df._get_value(i, "Ship-to nu"))] = temp

dump(wholesaler_loc, open("wholesaler_loc.pkl", "wb"))<jupyter_output><empty_output><jupyter_text>Now, a dataframe consisting of data segregated based on geographical coordinates has been created.<jupyter_code>modified_data = []
for key, value in loc_wise_sales.items():
  temp = key.split(", ")
  values_list = {}
  values_list["Latitude"] = float(temp[0])
  values_list["Longitude"] = float(temp[1])
  values_list["Total_HL"] = value
  values_list["MACO"] = loc_wise_maco[key]*value
  values_list["ID"] = loc_wise_user_id[key]

  modified_data.append(values_list)

data_frame = pd.DataFrame(modified_data)
print(data_frame.shape)
data_frame.head(20)<jupyter_output>(164, 5)<jupyter_text>Testing whether map is being displayed or not and determining an accurate position for base location of the map such that all the data points are clearly visible on the map.<jupyter_code>map_ = folium.Map(location = (47, 3), tiles = "cartodbpositron", zoom_start = 5)
map_<jupyter_output><empty_output><jupyter_text>In order to visualize the data and the sales history corresponding to geographical position of the wholesaler, a map is plotted as can be seen below. The total sales done in HL was scaled down to a range between 2 and 20 and the total MACO points were scaled down to a range between 0 to 100. The size of circle at each location is a measure of the total sales made in HL. On clicking a particular location, a popup appears which gives information about total MACO points at that location on a scale of 0 to 100.<jupyter_code>x, y = "Latitude", "Longitude"
#color = "Brand"
size = "Total_HL"
popup = "MACO"
data = data_frame.copy()

"""
colors = ["red", "orange", "yellow", "blue", "green", "violet", "maroon", "black", "indigo", "white"]
elements = list(df[color].unique())
print(elements)
data["color"] = data[color].apply(lambda x: colors[elements.index(x)])
"""

scaler = preprocessing.MinMaxScaler(feature_range = (2, 20))
data["size"] = scaler.fit_transform(data[size].values.reshape(-1,1)).reshape(-1)

scaler = preprocessing.MinMaxScaler(feature_range = (0, 100))
data["popup"] = scaler.fit_transform(data[popup].values.reshape(-1,1)).reshape(-1)

_map = folium.Map(location = (47, 3), tiles = "cartodbpositron", zoom_start = 5)

data.apply(lambda row: folium.CircleMarker(location = [row[x], row[y]], popup = str("{:.2f}".format(row["popup"])), fill = True, 
                                          radius = row["size"]).add_to(_map), axis = 1)
_map<jupyter_output><empty_output><jupyter_text>Recommender System In order to divide the wholesalers into clusters corresponding to their locations, unsupervised learning is used. K-Means Clustering divides the wholesalers into clusters of locations. In order to determine the appropriate number of clusters we make use of elbow method.<jupyter_code>coordinates = data_frame[['Longitude','Latitude']]

inertia = []
clusters = range(1,15)
for k in clusters:
    kmeansModel = KMeans(n_clusters=k)
    kmeansModel = kmeansModel.fit(coordinates)
    inertia.append(kmeansModel.inertia_)

fig, ax = plt.subplots(figsize=(12, 8))
plt.plot(clusters, inertia, marker='o')
plt.xlabel('No. of Clusters')
plt.ylabel('Inertia')
plt.title('Elbow Method For Optimal k')
plt.savefig('elbow.png')
plt.show()<jupyter_output><empty_output><jupyter_text>Silhouette score is a metric used to calculate the goodness of a clustering technique and helps us to decide the number of clusters to be formulated from the data.<jupyter_code>sil = []
kmax = 15

for k in range(2, kmax+1):
  kmeans = KMeans(n_clusters = k).fit(coordinates)
  labels = kmeans.labels_
  sil.append(silhouette_score(coordinates, labels, metric = 'euclidean'))

sil<jupyter_output><empty_output><jupyter_text>From the elbow method and silhouette score, we can conclude that ideal number of clusters is a value approximately between 6 to 10. In order to decide the final number of clusters, we plot map of clustered locations corresponding to 6, 8 and 12 as number of clusters.<jupyter_code>model = KMeans(n_clusters = 12) #init = k-Means++ (by default)
data_frame["Cluster"] = model.fit_predict(coordinates)
print(data_frame.head(10))

fig, ax = plt.subplots()

plt.grid()
plot = sns.scatterplot(x = "Latitude", y = "Longitude", data = data_frame, 
                palette = sns.color_palette("bright", 12), hue = 'Cluster',
                legend = "brief", ax = ax)
plot.set_title("Cluster Formation")
plot.legend(loc = 'right')
model = KMeans(n_clusters = 8) #init = k-Means++ (by default)
data_frame["Cluster"] = model.fit_predict(coordinates)
print(data_frame.head(10))

fig, ax = plt.subplots()

plt.grid()
plot = sns.scatterplot(x = "Latitude", y = "Longitude", data = data_frame, 
                palette = sns.color_palette("bright", 8), hue = 'Cluster',
                legend = "brief", ax = ax)
plot.set_title("Cluster Formation")
plot.legend(loc = 'right')
model = KMeans(n_clusters = 6, random_state = 0) #init = k-Means++ (by default)
data_frame["Cluster"] = model.fit_predict(coordinates)
print(data_frame.head(10))
data_frame.to_csv('data_frame.csv')

fig, ax = plt.subplots()

plt.grid()
plot = sns.scatterplot(x = "Latitude", y = "Longitude", data = data_frame, 
                palette = sns.color_palette("bright", 6), hue = 'Cluster',
                legend = "brief", ax = ax)
plot.set_title("Cluster Formation")
plot.legend(loc = 'right')
#Final number of clusters = 6<jupyter_output><empty_output><jupyter_text>Finally, as observed from the plots above, 6 seems to be the ideal value for number of clusters in our K-Means Clustering model for the data present uptill now.<jupyter_code>df.keys()<jupyter_output><empty_output><jupyter_text>Now, in order to understand user-user similarity, we segregate the data corresponding the the wholesaler ID's provided.<jupyter_code>material_index = {}
index_to_material = {}
user_purchase_history = {}
cnt = 0
material = list(df.Material.unique())

for i in material:
  material_index[i] = cnt
  index_to_material[cnt] = i
  cnt += 1

for i in range(df.shape[0]):
  user_id = df._get_value(i, "Ship-to nu")
  material_id = df._get_value(i, "Material")
  total_hl = df._get_value(i, "Total_HL")
  if(user_id not in user_purchase_history.keys()): 
    temp = []
    for j in range(len(material_index)):
      temp.append(0)
    user_purchase_history[user_id] = temp
  user_purchase_history[user_id][material_index[material_id]] += total_hl

purchase_df = pd.DataFrame.from_dict(user_purchase_history, orient = 'index', columns = material)
purchase_df.head(20)

dump(index_to_material, open("index_to_material.pkl", "wb"))
purchase_df.to_csv("purchase_df.csv")<jupyter_output><empty_output><jupyter_text>Here, we calculate mean of the amount of purchase done by each wholesaler in HL and add it to the dataframe.<jupyter_code>purchase_df['Mean'] = purchase_df.mean(axis = 1)
purchase_df.head(5)<jupyter_output><empty_output><jupyter_text>Each value in a row has been subtracted by their corresponding mean values, which will be used to determine the cosine similarity between the wholesalers.<jupyter_code>coorelation_df = purchase_df.copy()
index = list(coorelation_df.index)

for i in range(purchase_df.shape[0]):
  for j in material:
    if(coorelation_df._get_value(index[i], j) != 0):
      coorelation_df.iloc[i, coorelation_df.columns.get_loc(j)] -= coorelation_df._get_value(index[i], "Mean")

coorelation_df.head(10)
coorelation_df.to_csv("coorelation_df.csv")<jupyter_output><empty_output><jupyter_text>Here, we calculate the cosine similarity between the wholesalers.<jupyter_code>sim = cosine_similarity(coorelation_df)
sim<jupyter_output><empty_output><jupyter_text>Creating a dataframe which contains information about cosine similarity index of each wholesaler with every other wholesaler.<jupyter_code>sim_df = pd.DataFrame(sim, columns = coorelation_df.index.values, index = coorelation_df.index.values).reset_index()
sim_df.to_csv("sim_df.csv")
sim_df.head(5)<jupyter_output><empty_output><jupyter_text>Prediction Creating a dictionary which stores list of all the wholesalers which belong to a particular cluster.<jupyter_code>cluster_members = {}

for i in range(data_frame.shape[0]):
  temp = data_frame._get_value(i, "Cluster")
  user_id = df._get_value(i, "Ship-to nu")
  if(temp not in cluster_members.keys()):
    cluster_members[temp] = []
  cluster_members[temp].append(user_id)

#As we get duplicate values of wholesalers in the dictionary we need to make them unique
for i in cluster_members.keys():
  cluster_members[i] = np.unique(cluster_members[i])

dump(cluster_members, open("cluster_members.pkl", "wb"))<jupyter_output><empty_output><jupyter_text>Defining a function which returns a list of all the products bought by a particular wholesaler.<jupyter_code>def product_list(user_id):
  product_bought = []
  for i in range(purchase_df.shape[1]-1):
    #print(index_to_material[i])
    val = purchase_df._get_value(user_id, index_to_material[i])
    if(val != 0):
      product_bought.append(index_to_material[i])
  return product_bought<jupyter_output><empty_output><jupyter_text>Creating a dictionary which stores the MACO/HL corresponding to each material id.<jupyter_code>maco_per_hl = {}

for i in range(df.shape[0]):
  id = df._get_value(i, "Material")
  maco_per_hl[id] = df._get_value(i, "MACO/HL ")

dump(maco_per_hl, open("maco_per_hl.pkl", "wb"))<jupyter_output><empty_output><jupyter_text>Defining a function to display the all the wholesalers that belong to the cluster in which the given user is present. This will be used to narrate a story to the user that explains that the location is one of the reasons why he is being recommended such products.<jupyter_code>def story_display(cluster_pred):
  x, y = "Latitude", "Longitude"
  size = "Total_HL"
  popup = "MACO"
  data = data_frame.copy()
  
  scaler = preprocessing.MinMaxScaler(feature_range = (2, 20))
  data["size"] = scaler.fit_transform(data[size].values.reshape(-1,1)).reshape(-1)

  scaler = preprocessing.MinMaxScaler(feature_range = (0, 100))
  data["popup"] = scaler.fit_transform(data[popup].values.reshape(-1,1)).reshape(-1)

  map = folium.Map(location = (47, 3), tiles = "cartodbpositron", zoom_start = 5)

  for i, row in data.iterrows():
    if(row["Cluster"] == cluster_pred):
      #print(row['Cluster'], row[x], row[y])
      folium.CircleMarker(location = [row[x], row[y]], popup = str("{:.2f}".format(row["popup"])), 
                          fill = True, radius = row["size"]).add_to(map)
                          
  return map<jupyter_output><empty_output><jupyter_text>Defining a predict function which takes in the user id as a parameter and returns a list of products to be recommended to the user.<jupyter_code>def predict(user_id):
  temp = wholesaler_loc[str(user_id)].split(", ")
  #x = float(temp[0])
  #y = float(temp[1])
  print(len(data_frame.index[data_frame['ID']==str(user_id)].to_list()))
  cluster_pred = data_frame._get_value(data_frame.index[data_frame['ID']==str(user_id)].to_list()[0], "Cluster")     #model.predict([[x, y]])
  map_ = story_display(cluster_pred)
  possible_users = []
  for i in cluster_members[int(cluster_pred)]:
    if(i == user_id):
      continue
    similarity = sim_df.iloc[sim_df.columns.get_loc(user_id)-1, sim_df.columns.get_loc(i)]
    possible_users.append([similarity, i])

  possible_users = sorted(possible_users, reverse = True)
  
  product_bought = product_list(user_id)
  possible_recomm = []
  
  for i in range(5):
    temp = product_list(possible_users[i][1])
    for j in temp:
      if(j not in set(product_bought) and j not in set(item[1] for item in possible_recomm)):
        possible_recomm.append([maco_per_hl[j], j])

  possible_recomm = sorted(possible_recomm, reverse = True)
  #print(possible_recomm)

  recommend = []

  for i in range(15):
    recommend.append(possible_recomm[i][1])
  
  return [recommend, map_]

recommendations, map_ = predict(29606863)
print(recommendations)
map_<jupyter_output>1
[53566, 64665, 3530, 3450, 72328, 54632, 70601, 60387, 74582, 65660, 30822, 3411, 78355, 69265, 11766]
{'fraction_non_alphanumeric': 0.11780121045735321, 'fraction_numerical': 0.013664441255381543, 'mean_word_length': 5.457695406929895, 'pattern_counts': {'":': 0, '<': 89, '<?xml version=': 0, '>': 89, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '23', 'dir': 'jupyter-structured-clean-dedup', 'id': '161681', 'license': 'MIT', 'n_tokens_mistral': 5556, 'n_tokens_neox': 5277, 'n_words': 1458, 'path': 'Maverick_2.ipynb', 'repo_name': 'bafnayash/tech_savvy_Maverick', 'size': '16027'}