<jupyter_start><jupyter_text>Context of DataCompany - UK-based and registered non-store online retailProducts for selling - Mainly all-occasion giftsCustomers - Most are wholesalers (local or international)Transactions Period - **1st Dec 2010 - 9th Dec 2011 (One year)**  Results obtained from Exploratory Data Analysis (EDA)1. The **customer with the highest number of orders comes from the United Kingdom (UK)** 2. The **customer with the highest money spent on purchases comes from Netherlands** 3. The company receives the highest number of orders from customers in the UK (since it is a UK-based company). Therefore, the TOP 5 countries (including UK) that place the highest number of orders are as below:     - United Kingdom 	- Germany 	- France	- Ireland (EIRE)	- Spain4. As the company receives the highest number of orders from customers in the UK (since it is a UK-based company), customers in the UK spend the most on their purchases. Therefore, the TOP 5 countries (including UK) that spend the most money on purchases are as below:     - United Kingdom 	- Netherlands	- Ireland (EIRE)	- Germany	- France5. **November 2011 has the highest sales** 	- The month with the lowest sales is undetermined as the dataset consists of transactions until 9th December 2011 in December 6. There are **no transactions on Saturday** between 1st Dec 2010 - 9th Dec 2011 7. The number of orders received by the company tends to increases from Monday to Thursday and decrese afterward8. The company receives the **highest number of orders at 12:00pm** 	- Possibly most customers made purchases during **lunch hour between 12:00pm - 2:00pm**9. The company tends to **give out FREE items for purchases occasionally each month (Except June 2011)** 	- However, it is not clear what factors contribute to giving out the FREE items to the particular customers<jupyter_code>import pandas as pd 
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns 

import warnings
# current version of seaborn generates a bunch of warnings that we'll ignore
warnings.filterwarnings('ignore')
sns.set_style('whitegrid')

import missingno as msno # missing data visualization module for Python
import pandas_profiling

import gc
import datetime

%matplotlib inline
color = sns.color_palette()
pd.set_option('display.max_rows', 10000)
pd.set_option('display.max_columns', 100)
# specify encoding to deal with different formats
df = pd.read_csv('../input/data.csv', encoding = 'ISO-8859-1')
df.head()
# change the column names
df.rename(index=str, columns={'InvoiceNo': 'invoice_num',
                              'StockCode' : 'stock_code',
                              'Description' : 'description',
                              'Quantity' : 'quantity',
                              'InvoiceDate' : 'invoice_date',
                              'UnitPrice' : 'unit_price',
                              'CustomerID' : 'cust_id',
                              'Country' : 'country'}, inplace=True)
df.head()<jupyter_output><empty_output><jupyter_text>Data Cleaning<jupyter_code>df.info()<jupyter_output><empty_output><jupyter_text>Check missing values for each column<jupyter_code># check missing values for each column 
df.isnull().sum().sort_values(ascending=False)
# check out the rows with missing values
df[df.isnull().any(axis=1)].head()
# change the invoice_date format - String to Timestamp format
df['invoice_date'] = pd.to_datetime(df.invoice_date, format='%m/%d/%Y %H:%M')
# change description - UPPER case to LOWER case
df['description'] = df.description.str.lower()
df.head()<jupyter_output><empty_output><jupyter_text>Remove rows with missing values<jupyter_code># df_new without missing values
df_new = df.dropna()
# check missing values for each column 
df_new.isnull().sum().sort_values(ascending=False)
df_new.info()
# change columns tyoe - String to Int type 
df_new['cust_id'] = df_new['cust_id'].astype('int64')
df_new.head()
df_new.info()
df_new.describe().round(2)<jupyter_output><empty_output><jupyter_text>Remove Quantity with negative values<jupyter_code>df_new = df_new[df_new.quantity > 0]
df_new.describe().round(2)<jupyter_output><empty_output><jupyter_text>Add the column - amount_spent<jupyter_code>df_new['amount_spent'] = df_new['quantity'] * df_new['unit_price']
# rearrange all the columns for easy reference
df_new = df_new[['invoice_num','invoice_date','stock_code','description','quantity','unit_price','amount_spent','cust_id','country']]<jupyter_output><empty_output><jupyter_text>Add the columns - Month, Day and Hour for the invoice<jupyter_code>df_new.insert(loc=2, column='year_month', value=df_new['invoice_date'].map(lambda x: 100*x.year + x.month))
df_new.insert(loc=3, column='month', value=df_new.invoice_date.dt.month)
# +1 to make Monday=1.....until Sunday=7
df_new.insert(loc=4, column='day', value=(df_new.invoice_date.dt.dayofweek)+1)
df_new.insert(loc=5, column='hour', value=df_new.invoice_date.dt.hour)
df_new.head()<jupyter_output><empty_output><jupyter_text>Exploratory Data Analysis (EDA)  How many orders made by the customers?<jupyter_code>df_new.groupby(by=['cust_id','country'], as_index=False)['invoice_num'].count().head()
orders = df_new.groupby(by=['cust_id','country'], as_index=False)['invoice_num'].count()

plt.subplots(figsize=(15,6))
plt.plot(orders.cust_id, orders.invoice_num)
plt.xlabel('Customers ID')
plt.ylabel('Number of Orders')
plt.title('Number of Orders for different Customers')
plt.show()<jupyter_output><empty_output><jupyter_text>Check TOP 5 most number of orders<jupyter_code>print('The TOP 5 customers with most number of orders...')
orders.sort_values(by='invoice_num', ascending=False).head()<jupyter_output><empty_output><jupyter_text>How much money spent by the customers?<jupyter_code>money_spent = df_new.groupby(by=['cust_id','country'], as_index=False)['amount_spent'].sum()

plt.subplots(figsize=(15,6))
plt.plot(money_spent.cust_id, money_spent.amount_spent)
plt.xlabel('Customers ID')
plt.ylabel('Money spent (Dollar)')
plt.title('Money Spent for different Customers')
plt.show()<jupyter_output><empty_output><jupyter_text>Check TOP 5 highest money spent<jupyter_code>print('The TOP 5 customers with highest money spent...')
money_spent.sort_values(by='amount_spent', ascending=False).head()<jupyter_output><empty_output><jupyter_text>Discover patterns for Number of OrdersCredit: https://www.kaggle.com/hendraherviawan/customer-purchasing-patterns  How many orders (per month)?<jupyter_code># color available
sns.palplot(color)
df_new.head()
ax = df_new.groupby('invoice_num')['year_month'].unique().value_counts().sort_index().plot('bar',color=color[0],figsize=(15,6))
ax.set_xlabel('Month',fontsize=15)
ax.set_ylabel('Number of Orders',fontsize=15)
ax.set_title('Number of orders for different Months (1st Dec 2010 - 9th Dec 2011)',fontsize=15)
ax.set_xticklabels(('Dec_10','Jan_11','Feb_11','Mar_11','Apr_11','May_11','Jun_11','July_11','Aug_11','Sep_11','Oct_11','Nov_11','Dec_11'), rotation='horizontal', fontsize=13)
plt.show()<jupyter_output><empty_output><jupyter_text>How many orders (per day)?<jupyter_code>df_new.groupby('invoice_num')['day'].unique().value_counts().sort_index()
ax = df_new.groupby('invoice_num')['day'].unique().value_counts().sort_index().plot('bar',color=color[0],figsize=(15,6))
ax.set_xlabel('Day',fontsize=15)
ax.set_ylabel('Number of Orders',fontsize=15)
ax.set_title('Number of orders for different Days',fontsize=15)
ax.set_xticklabels(('Mon','Tue','Wed','Thur','Fri','Sun'), rotation='horizontal', fontsize=15)
plt.show()<jupyter_output><empty_output><jupyter_text>How many orders (per hour)?<jupyter_code>df_new.groupby('invoice_num')['hour'].unique().value_counts().iloc[:-1].sort_index()
ax = df_new.groupby('invoice_num')['hour'].unique().value_counts().iloc[:-1].sort_index().plot('bar',color=color[0],figsize=(15,6))
ax.set_xlabel('Hour',fontsize=15)
ax.set_ylabel('Number of Orders',fontsize=15)
ax.set_title('Number of orders for different Hours',fontsize=15)
ax.set_xticklabels(range(6,21), rotation='horizontal', fontsize=15)
plt.show()<jupyter_output><empty_output><jupyter_text>Discover patterns for Unit Price<jupyter_code>df_new.unit_price.describe()<jupyter_output><empty_output><jupyter_text>**We see that there are unit price = 0 (FREE items)**There are some free items given to customers from time to time.<jupyter_code># check the distribution of unit price
plt.subplots(figsize=(12,6))
sns.boxplot(df_new.unit_price)
plt.show()
df_free = df_new[df_new.unit_price == 0]
df_free.head()
df_free.year_month.value_counts().sort_index()
ax = df_free.year_month.value_counts().sort_index().plot('bar',figsize=(12,6), color=color[0])
ax.set_xlabel('Month',fontsize=15)
ax.set_ylabel('Frequency',fontsize=15)
ax.set_title('Frequency for different Months (Dec 2010 - Dec 2011)',fontsize=15)
ax.set_xticklabels(('Dec_10','Jan_11','Feb_11','Mar_11','Apr_11','May_11','July_11','Aug_11','Sep_11','Oct_11','Nov_11'), rotation='horizontal', fontsize=13)
plt.show()<jupyter_output><empty_output><jupyter_text>**Not clear why there are FREE items given to certain customers**- On average, the company gave out 2-4 times FREE items to customers each month (Except in June 2011)  Discover patterns for each Country<jupyter_code>df_new.head()<jupyter_output><empty_output><jupyter_text>How many orders for each country?<jupyter_code>group_country_orders = df_new.groupby('country')['invoice_num'].count().sort_values()
# del group_country_orders['United Kingdom']

# plot number of unique customers in each country (with UK)
plt.subplots(figsize=(15,8))
group_country_orders.plot('barh', fontsize=12, color=color[0])
plt.xlabel('Number of Orders', fontsize=12)
plt.ylabel('Country', fontsize=12)
plt.title('Number of Orders for different Countries', fontsize=12)
plt.show()
group_country_orders = df_new.groupby('country')['invoice_num'].count().sort_values()
del group_country_orders['United Kingdom']

# plot number of unique customers in each country (without UK)
plt.subplots(figsize=(15,8))
group_country_orders.plot('barh', fontsize=12, color=color[0])
plt.xlabel('Number of Orders', fontsize=12)
plt.ylabel('Country', fontsize=12)
plt.title('Number of Orders for different Countries', fontsize=12)
plt.show()<jupyter_output><empty_output><jupyter_text>How much money spent by each country?<jupyter_code>group_country_amount_spent = df_new.groupby('country')['amount_spent'].sum().sort_values()
# del group_country_orders['United Kingdom']

# plot total money spent by each country (with UK)
plt.subplots(figsize=(15,8))
group_country_amount_spent.plot('barh', fontsize=12, color=color[0])
plt.xlabel('Money Spent (Dollar)', fontsize=12)
plt.ylabel('Country', fontsize=12)
plt.title('Money Spent by different Countries', fontsize=12)
plt.show()
group_country_amount_spent = df_new.groupby('country')['amount_spent'].sum().sort_values()
del group_country_amount_spent['United Kingdom']

# plot total money spent by each country (without UK)
plt.subplots(figsize=(15,8))
group_country_amount_spent.plot('barh', fontsize=12, color=color[0])
plt.xlabel('Money Spent (Dollar)', fontsize=12)
plt.ylabel('Country', fontsize=12)
plt.title('Money Spent by different Countries', fontsize=12)
plt.show()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13625066571986508, 'fraction_numerical': 0.024232203088940175, 'mean_word_length': 6.781077348066298, 'pattern_counts': {'":': 0, '<': 77, '<?xml version=': 0, '>': 78, 'https://': 1, 'lorem ipsum': 0, 'www.': 1, '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': '93152', 'license': 'MIT', 'n_tokens_mistral': 3954, 'n_tokens_neox': 3673, 'n_words': 871, 'path': 'notebooks/interviews/JL/projects/e-commerce-data-eda.ipynb', 'repo_name': 'dilip-albert-datascientist/traditional-machine-learning', 'size': '11266'}