<jupyter_start><jupyter_text>Description:This notebook is working with a creative-commons licensed dataset provided Olist, a Brazilian e-commerce marketplace integrator.The e-commerce website enables independent sellers to sell their products through the ecommerce Store and ship them directly to the customers.After a customer purchases a product, a seller gets notified to fulfill that order. Once the customer receives the product, or the estimated delivery date is due, the customer gets a satisfaction survey by email where she can rate and review the purchase experience.  Dataset description:The dataset has information of 100k orders from 2016 to 2018. The dataset contains order status, price, payment and delivery time to the customer location, product attributes, and reviews written by customers.The geolocation dataset that relates Brazilian zip codes to lat/lng coordinates.<jupyter_code># pip install spacy, nltk, google-trans-new
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
%matplotlib inline

from glob import glob
from hops import hdfs

# hide warnings
import warnings
warnings.filterwarnings('ignore')

pd.set_option('display.max_columns', None)
pd.set_option('display.max_rows', None)
import time

start = time.time()

customers_df = pd.read_csv(hdfs.project_path() + "ecommerce/customer.csv")
geolocation_df = pd.read_csv(hdfs.project_path() + "ecommerce/geolocation.csv")
orders_df = pd.read_csv(hdfs.project_path() + "ecommerce/order.csv")
order_items_df = pd.read_csv(hdfs.project_path() + "ecommerce/order_item.csv")
order_payments_df = pd.read_csv(hdfs.project_path() + "ecommerce/order_payment.csv")
order_reviews_df = pd.read_csv(hdfs.project_path() + "ecommerce/order_review.csv")
products_df = pd.read_csv(hdfs.project_path() + "ecommerce/product.csv")
sellers_df = pd.read_csv(hdfs.project_path() + "ecommerce/seller.csv")
category_transalations_df = pd.read_csv(hdfs.project_path() + "ecommerce/product_category_name_translation.csv")
end = time.time()
print(end - start)
# Lets check the size of each df:
df_names = ['customers_df','geolocation_df', 'orders_df', 'order_items_df','order_payments_df',
            'order_reviews_df','products_df','sellers_df', 'category_transalations_df' ]
for df in df_names:
    print("Dataset {} has shape {}".format(df, eval(df).shape))<jupyter_output>Dataset customers_df has shape (99441, 5)
Dataset geolocation_df has shape (1000163, 5)
Dataset orders_df has shape (99441, 9)
Dataset order_items_df has shape (112650, 7)
Dataset order_payments_df has shape (103886, 5)
Dataset order_reviews_df has shape (99224, 7)
Dataset products_df has shape (32340, 10)
Dataset sellers_df has shape (3095, 4)
Dataset category_transalations_df has shape (71, 2)<jupyter_text>Data Schema![schema.png](attachment:schema.png)  Description of various columns in different csv files![order.png](attachment:order.png) ![what.png](attachment:what.png)<jupyter_code>df = pd.merge(orders_df,order_payments_df, on="order_id")
df = pd.merge(df,customers_df, on="customer_id")
df = pd.merge(df,order_items_df, on="order_id")
df = pd.merge(df,sellers_df, on="seller_id")
df = pd.merge(df,order_reviews_df, on="order_id")
df = pd.merge(df,products_df, on="product_id")
#df = pd.merge(df,geolocation_df, left_on="" right_on="geolocation_zip_code_prefix")
df = pd.merge(df,category_transalations_df, on="product_category_name")
df.shape
df.head()
df.isnull().sum()
df.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 115608 entries, 0 to 115607
Data columns (total 42 columns):
 #   Column                         Non-Null Count   Dtype  
---  ------                         --------------   -----  
 0   Unnamed: 0_x                   115608 non-null  int64  
 1   order_id                       115608 non-null  object 
 2   customer_id                    115608 non-null  object 
 3   order_status                   115608 non-null  object 
 4   order_purchase_timestamp       115608 non-null  object 
 5   order_approved_at              115608 non-null  object 
 6   order_delivered_carrier_date   115608 non-null  object 
 7   order_delivered_customer_date  115608 non-null  object 
 8   order_estimated_delivery_date  115608 non-null  object 
 9   payment_sequential             115608 non-null  int64  
 10  payment_type                   115608 non-null  object 
 11  payment_installments           115608 non-null  int64  
 12  payment_value                 [...]<jupyter_text>Products Analysis Top 25 Product catgeories<jupyter_code>print("Number of unique categories: ", len(products_df.product_category_name.unique()))
plt.figure(figsize=(10,6))
top_25_prod_categories = products_df.groupby('product_category_name')['product_id'].count().sort_values(ascending=False).head(25)
sns.barplot(x=top_25_prod_categories.index, y=top_25_prod_categories.values)
plt.xticks(rotation=80)
plt.xlabel('Product Category')
plt.title('Top 25 Most Common Categories');
plt.show()<jupyter_output><empty_output><jupyter_text>Maximum number of products fall under these top 25 categories  Lets now do an RFM(Recency, Frequncy, Monetary) Analysis for Behavioural Segmentaion of Customers<jupyter_code>from datetime import datetime

import sklearn
from sklearn.preprocessing import StandardScaler
from sklearn.cluster import KMeans
from sklearn.metrics import silhouette_score

from scipy.cluster.hierarchy import linkage
from scipy.cluster.hierarchy import dendrogram
from scipy.cluster.hierarchy import cut_tree<jupyter_output><empty_output><jupyter_text>Monetary<jupyter_code>df.head()
df.count()
# Remove duplicate entries
df= df.drop_duplicates(subset={'order_id','customer_id','order_purchase_timestamp','order_delivered_customer_date'}, keep='first')
df=df.reindex()
df['total_payment'] = df['payment_value'] * df['payment_installments']
# monetary
grouped_df = df.groupby('customer_unique_id')['total_payment'].sum()
grouped_df = grouped_df.reset_index()
grouped_df.columns = ['customer_unique_id', 'monetary']
grouped_df.head()
# frequency
frequency = df.groupby('customer_unique_id')['order_id'].count()
frequency = frequency.reset_index()
frequency.columns = ['customer_unique_id', 'frequency']
frequency.sort_values("frequency",ascending=False).head()
# merge the two dfs
grouped_df = pd.merge(grouped_df, frequency, on='customer_unique_id', how='inner')
grouped_df.sort_values("monetary",ascending=False).head()
df['order_purchase_timestamp'] = pd.to_datetime(df['order_purchase_timestamp'], infer_datetime_format=True, errors='ignore')

max_date = max(df['order_purchase_timestamp'])
df['diff_days'] =  (max_date-df['order_purchase_timestamp']).dt.days
# Recency
recency = df.groupby('customer_unique_id')['diff_days'].min()
recency = recency.reset_index()
recency.columns = ['customer_unique_id', 'recency']
recency.head()
# merge the grouped_df to recency df
rfm_df = pd.merge(grouped_df, recency, on='customer_unique_id', how='inner')
rfm_df.sort_values("monetary",ascending=False).head()
# Plot RFM distributions
plt.figure(figsize=(12,10))
# Plot distribution of R
plt.subplot(3, 1, 1); sns.distplot(rfm_df['recency'])
# Plot distribution of F
plt.subplot(3, 1, 2); sns.distplot(rfm_df['frequency'])
# Plot distribution of M
plt.subplot(3, 1, 3); sns.distplot(rfm_df['monetary'])
# Show the plot
plt.show()<jupyter_output><empty_output><jupyter_text>Lets check for Outliers<jupyter_code>sns.boxplot(rfm_df['recency'])
sns.boxplot(rfm_df['frequency'])
sns.boxplot(rfm_df['monetary'])<jupyter_output><empty_output><jupyter_text>Monetary and Frequency have outliers<jupyter_code># removing (statistical) outliers for monetary
Q1 = rfm_df.monetary.quantile(0.05)
Q3 = rfm_df.monetary.quantile(0.95)
IQR = Q3 - Q1
rfm_df = rfm_df[(rfm_df.monetary >= Q1 - 1.5*IQR) & (rfm_df.monetary <= Q3 + 1.5*IQR)]

# outlier treatment for frequency
Q1 = rfm_df.frequency.quantile(0.05)
Q3 = rfm_df.frequency.quantile(0.95)
IQR = Q3 - Q1
rfm_df = rfm_df[(rfm_df.frequency >= Q1 - 1.5*IQR) & (rfm_df.frequency <= Q3 + 1.5*IQR)]
sns.boxplot(rfm_df['monetary'])
sns.boxplot(rfm_df['frequency'])<jupyter_output><empty_output><jupyter_text>Scaling<jupyter_code>rfm_df_scaled = rfm_df[['monetary', 'frequency', 'recency']]

# instantiate
scaler = StandardScaler()

# fit_transform
rfm_df_scaled = scaler.fit_transform(rfm_df_scaled)
rfm_df_scaled.shape
rfm_df_scaled = pd.DataFrame(rfm_df_scaled)
rfm_df_scaled.columns = ['monetary', 'frequency', 'recency']
rfm_df_scaled.head()<jupyter_output><empty_output><jupyter_text>Lets do the RFM Analysis for the Sellers We can use the same code above just replacing the groupby column<jupyter_code>df.head()
# frequency
frequency = df.groupby('seller_id')['order_item_id'].count()
frequency = frequency.reset_index()
frequency.columns = ['seller_id', 'frequency']
frequency.head()
# monetary
monetary = df.groupby('seller_id')['total_payment'].sum()
monetary = monetary.reset_index()
monetary.columns = ['seller_id', 'monetary']
monetary.head()
# monetary
recency = df.groupby('seller_id')['diff_days'].min()
recency = recency.reset_index()
recency.columns = ['seller_id', 'recency']
recency.head()
rfm_seller_df = pd.merge(frequency, monetary, on='seller_id', how='inner')
rfm_seller_df = pd.merge(rfm_seller_df, recency, on='seller_id', how='inner')
rfm_seller_df.head()
# Plot RFM distributions
plt.figure(figsize=(12,10))
# Plot distribution of R
plt.subplot(3, 1, 1); sns.distplot(rfm_seller_df['recency'])
# Plot distribution of F
plt.subplot(3, 1, 2); sns.distplot(rfm_seller_df['frequency'])
# Plot distribution of M
plt.subplot(3, 1, 3); sns.distplot(rfm_seller_df['monetary'])
# Show the plot
plt.show()<jupyter_output><empty_output><jupyter_text>Outlier detection<jupyter_code>sns.boxplot(rfm_seller_df['recency'])
sns.boxplot(rfm_seller_df['frequency'])
sns.boxplot(rfm_seller_df['monetary'])<jupyter_output><empty_output><jupyter_text>Outlier treatment<jupyter_code># removing (statistical) outliers for monetary
Q1 = rfm_seller_df.monetary.quantile(0.05)
Q3 = rfm_seller_df.monetary.quantile(0.95)
IQR = Q3 - Q1
rfm_seller_df = rfm_seller_df[(rfm_seller_df.monetary >= Q1 - 1.5*IQR) & (rfm_seller_df.monetary <= Q3 + 1.5*IQR)]

# outlier treatment for frequency
Q1 = rfm_seller_df.frequency.quantile(0.05)
Q3 = rfm_seller_df.frequency.quantile(0.95)
IQR = Q3 - Q1
rfm_seller_df = rfm_seller_df[(rfm_seller_df.frequency >= Q1 - 1.5*IQR) & (rfm_seller_df.frequency <= Q3 + 1.5*IQR)]

# outlier treatment for recency
Q1 = rfm_seller_df.recency.quantile(0.05)
Q3 = rfm_seller_df.recency.quantile(0.95)
IQR = Q3 - Q1
rfm_seller_df = rfm_seller_df[(rfm_seller_df.recency >= Q1 - 1.5*IQR) & (rfm_seller_df.recency <= Q3 + 1.5*IQR)]
sns.boxplot(rfm_seller_df['recency'])
sns.boxplot(rfm_seller_df['frequency'])
sns.boxplot(rfm_seller_df['monetary'])<jupyter_output><empty_output><jupyter_text>Scaling<jupyter_code>rfm_seller_df_scaled = rfm_seller_df[['monetary', 'frequency', 'recency']]

# instantiate
scaler = StandardScaler()

# fit_transform
rfm_seller_df_scaled = scaler.fit_transform(rfm_seller_df_scaled)
rfm_seller_df_scaled.shape
rfm_seller_df_scaled = pd.DataFrame(rfm_seller_df_scaled)
rfm_seller_df_scaled.columns = ['monetary', 'frequency', 'recency']
rfm_seller_df_scaled.head()<jupyter_output><empty_output><jupyter_text>Lets do some Customer Sentiment Analysis using the review comments<jupyter_code>order_reviews_df.head()
len(order_reviews_df)<jupyter_output><empty_output><jupyter_text>We need real review messages so lets drop the NONE values<jupyter_code>reviews_df = order_reviews_df[~(order_reviews_df['review_comment_message'] == 'NONE')]
reviews_df.head()<jupyter_output><empty_output><jupyter_text>Lets only keep the review messages and the review score to see what the customers are liking and disliking<jupyter_code>reviews_df = reviews_df[['review_score','review_comment_message']]
reviews_df.head()<jupyter_output><empty_output><jupyter_text>Lets do some cleaning of the review messages<jupyter_code>!python -m spacy download pt_core_news_sm
import re, nltk, spacy, string
import pt_core_news_sm
nlp = pt_core_news_sm.load()
from sklearn.feature_extraction.text import CountVectorizer, TfidfVectorizer<jupyter_output><empty_output><jupyter_text>Cleaning the messages<jupyter_code>def clean_text(text):
    text = text.lower()  # Make the text lowercase
    text = re.sub('\[.*\]','', text).strip() # Remove text in square brackets if any
    text = text.translate(str.maketrans('', '', string.punctuation)) # Remove punctuation
    text = re.sub('\S*\d\S*\s*','', text).strip()  # Remove words containing numbers
    return text.strip()
reviews_df.review_comment_message = reviews_df.review_comment_message.apply(lambda x: clean_text(x))<jupyter_output><empty_output><jupyter_text>Lemmatizing the words<jupyter_code># portugese stopwords
stopwords = nlp.Defaults.stop_words

# lemmatizer function
def lemmatizer(text):
    doc = nlp(text)
    sent = [token.lemma_ for token in doc if not token.text in set(stopwords)]
    return ' '.join(sent)
reviews_df['lemma'] = reviews_df.review_comment_message.apply(lambda x: lemmatizer(x))
reviews_df.head(10)<jupyter_output><empty_output><jupyter_text>Unigrams, Bigram, Trigrams Frequency Analysis<jupyter_code>#top 30 bigram frequency among the reviews

def get_top_n_bigram(text, ngram=1, top=None):
    vec = CountVectorizer(ngram_range=(ngram, ngram), stop_words=stopwords).fit(text)
    bag_of_words = vec.transform(text)

    sum_words = bag_of_words.sum(axis=0) 
    words_freq = [(word, sum_words[0, idx]) for word, idx in vec.vocabulary_.items()]

    words_freq =sorted(words_freq, key = lambda x: x[1], reverse=True)
    return words_freq[:top]
top_30_unigrams = get_top_n_bigram(reviews_df.lemma,ngram=1, top=30)
top_30_bigrams = get_top_n_bigram(reviews_df.lemma,ngram=2, top=30)
top_30_trigrams = get_top_n_bigram(reviews_df.lemma,ngram=3, top=30)
df1 = pd.DataFrame(top_30_unigrams, columns = ['unigram' , 'count'])
plt.figure(figsize=(12,6))
fig = sns.barplot(x=df1['unigram'], y=df1['count'])
plt.xticks(rotation = 80)
plt.show()
df2 = pd.DataFrame(top_30_bigrams, columns = ['bigram' , 'count'])
plt.figure(figsize=(12,6))
fig = sns.barplot(x=df2['bigram'], y=df2['count'])
plt.xticks(rotation = 80)
plt.show()
df3 = pd.DataFrame(top_30_trigrams, columns = ['trigram' , 'count'])
plt.figure(figsize=(12,6))
fig = sns.barplot(x=df3['trigram'], y=df3['count'])
plt.xticks(rotation = 80)
plt.show()<jupyter_output><empty_output><jupyter_text>TfidfVectorizer<jupyter_code>tfidf = TfidfVectorizer(min_df=2, max_df=0.95, stop_words=stopwords)
dtm = tfidf.fit_transform(reviews_df.lemma)
tfidf.get_feature_names()[:10]
len(tfidf.get_feature_names())
reviews_df.review_score.value_counts()<jupyter_output><empty_output><jupyter_text>Using NMF for Topic modelling for the review messages Lets pick 5 has the n_components as the review_score is also of 5 point grades<jupyter_code>from sklearn.decomposition import NMF

#Load nmf_model with the n_components 
num_topics =  5 

#keep the random_state =40
nmf_model = NMF(n_components=num_topics, random_state=40)

W1 = nmf_model.fit_transform(dtm)
H1 = nmf_model.components_
colnames = ["Topic" + str(i) for i in range(nmf_model.n_components)]
docnames = ["Doc" + str(i) for i in range(len(reviews_df.lemma))]
df_doc_topic = pd.DataFrame(np.round(W1, 2), columns=colnames, index=docnames)
significant_topic = np.argmax(df_doc_topic.values, axis=1)
df_doc_topic['dominant_topic'] = significant_topic
reviews_df['topic'] = significant_topic
pd.set_option('display.max_colwidth', -1)
reviews_df[['review_comment_message','lemma','review_score','topic']][reviews_df.topic==0].head(20)
temp = reviews_df[['review_comment_message','lemma','review_score','topic']].groupby('topic').head(20)
temp.sort_values('topic')<jupyter_output><empty_output><jupyter_text>Translating Portugese to English using Google Translator Attempting to provide better names to the topic indexes thus obtained above<jupyter_code>!pip install google_trans_new
#  google translate from portuguese to english
from google_trans_new import google_translator
translator = google_translator()

def translate_pt_to_eng(sent):
    translated_sent = translator.translate(sent,lang_tgt='en',lang_src='pt')
    return translated_sent
!pip install --user googletrans
from googletrans import Translator
translator = Translator()

translator.translate('veritas lux mea', src='la', dest='en')
reviews_df['lemma'].head()
print(translate_pt_to_eng('receber prazo estipular'))
reviews_df['lemma'] = reviews_df['lemma'].apply(lambda x : translate_pt_to_eng(x))<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12408232037549645, 'fraction_numerical': 0.02286677097123601, 'mean_word_length': 6.708256029684601, 'pattern_counts': {'":': 0, '<': 93, '<?xml version=': 0, '>': 93, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '22', 'dir': 'jupyter-structured-clean-dedup', 'id': '150964', 'license': 'Apache-2.0', 'n_tokens_mistral': 6254, 'n_tokens_neox': 5900, 'n_words': 1162, 'path': 'ecommerce/eda-rfm-ecommerce.ipynb', 'repo_name': 'feature-stores-for-ml/fs-notebooks', 'size': '16618'}