Lab 8 - Discover associations between e-commerce purchases.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Week 8 - Discover associations between e-commerce purchases\n",
    "*© 2021 Colin Conrad*\n",
    "\n",
    "Welcome back once again! This week is the first in a two-part series on data mining with Python. Data mining is the process of knowledge discovery from large data sets, and is perhaps one of the defining tasks of a data scientist. Data mining techniques include everything that you have learned so far: data processing, data structures and descriptive analytics and summarization. This week, we will focus on association rule mining, a technique commonly applied to discovery trends in a database.\n",
    "\n",
    "This and the following lab are fairly hard (at least, they are kind of heady). The concepts explored near the end of the course will be a bit simpler, though data mining and machine learning are included in this unit in case there are people who wish to employ these techniques for their final projects. Though we will explore this e-commerce data set for this week, the techniques explored here are widely applicable to various data formats and applications, including libraries, archives, media records or (with some work) natural language documents such as social media.\n",
    "\n",
    "**This week, we will achieve the following objectives:**\n",
    "- Analyze outliers\n",
    "- Detect patterns among e-commerce transactions\n",
    "- Implement association rule mining\n",
    "\n",
    "# Case: The Online Retail Data Set\n",
    "This week's lab is based on the [Online Retail Data Set](https://archive.ics.uci.edu/ml/datasets/online+retail) provided by Dr. Daqing Chen through the UCI Machine Learning Repository. The dataset consists of roughly 500k transactions from a British e-commerce company which occurred between 01/12/2010 and 09/12/2011. The company is a gift and confectionary company and mainly sells to retailers.\n",
    "\n",
    "When conducting data mining and machine learning analysis it is often important to have large datasets. The University of California Machine Learning Repository is a useful resource for finding datasets which are suited to these purposes. To learn more, visit their website: https://archive.ics.uci.edu/ml/index.php"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Objective 1: Analyze Outliers\n",
    "The ultimate goal of data mining is to generate insights from the data. As such, outlier detection is particularly important. Outliers often considerably account for an organization's performance and this e-commerce company is no exception. We will start by importing pandas and matplotlib so that we can do some visual analytics. We will specify a few more details related to encoding and data types so that we don't run into trouble. \n",
    "\n",
    "This dataset consists of 541909 records and 8 columns. This is the largest dataset that we have explored so far, but still pretty far from 'big data'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd #import pandas as an object pd\n",
    "\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "\n",
    "sns.set()\n",
    "\n",
    "ec = pd.read_csv('data/w8_ec.csv', encoding=\"ISO-8859-1\", dtype={'CustomerID': str,'InvoiceNo': str}) #we will name our dataframe ec\n",
    "ec.head() #see the first five entries"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's visualize the `UnitPrice` to look for outliers in that column. Right away we can see that there are a small number of transactions which have over 1000 pounds in price. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ec.plot(x='InvoiceDate', y='UnitPrice', figsize=(12,6), title='Transaction price over time')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's retrieve the subset of transactions which have over 1000 pounds as the UnitPrice. We could also learn that there are 122 transactions by using `.describe()`, which you can try if you would like! "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ec_outliers = ec[\n",
    "                    (ec['UnitPrice'] > 1000) |\n",
    "                    (ec['UnitPrice'] < -1000)\n",
    "                ]\n",
    "\n",
    "ec_outliers.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ec_outliers.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What type of transactions are these? We can use the `.unique()` method to determine the number of unique values in the `Description` column. It is clear that these consist of overhead costs which were recorded as transactions in the data. Let's dig into the differences between these categories."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ec_outliers['Description'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Analyze outliers by description group\n",
    "In the previous lab we learned about the `groupby()` method which can be used to group data by a nominal value. `Description` is one such value. By calculating the sum of unit price for each value, we can compare expenses between the categories. It is clear that `AMAZON FEE` is a considerable source of revenue for this company.\n",
    "\n",
    "One immediate question I would have for the executives at this company is whether this represented a revenue or an expense. It seems strange that they are recorded as revenues; it is possible that they represent sales which were made on Amazon. However, this does not account for the fact that there are other fees represented as revenues (e.g. DOTCOM POSTAGE)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "out_group = ec_outliers.groupby('Description')\n",
    "out_group['UnitPrice'].sum().plot.bar(figsize=(12,6), title='Total fees paid by outlier fee type')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Challenge Question 1 (1.5 points)\n",
    "Though it is clear that Amazon Fees constitute the largest fee segment among the outliers, it is not clear whether any one Amazon fee was responsible for these expenses. Write code that provides the `Invoice Number`, `Description` and `UnitPrice` for the transaction which had the highest UnitPrice value."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# insert your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Objective 2: Detect patterns among e-commerce transactions\n",
    "With a better understanding of the `UnitPrice` outliers, it is time to analyze the main transactional data. We should start by removing the outlier data and analyzing only common transactions. We can create a subset called `ect` (e-commerce transactions) for this analysis. Let's visualize the `Quantity` column instead."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ect = ec[\n",
    "            (ec['Description'] != 'AMAZON FEE') & \n",
    "            (ec['Description'] != 'Manual') &\n",
    "            (ec['Description'] != 'Bank Charges') & \n",
    "            (ec['Description'] != 'Discount') &\n",
    "            (ec['Description'] != 'POSTAGE') & \n",
    "            (ec['Description'] != 'DOTCOM POSTAGE') &\n",
    "            (ec['Description'] != 'Adjust bad debt') & \n",
    "            (ec['Description'] != 'CRUK Commission')\n",
    "        ]\n",
    "\n",
    "ect.plot(x='InvoiceDate', y='Quantity', figsize=(12,6), title='Quantity over time, excluding overhead fees')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "However, once again we realize that there outliers in Quantity as well, albeit far fewer. Let's retrieve orders with over 5000 quantity (or negative 5000 quantity) and investigate what these are."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ect_outliers = ect[\n",
    "                    (ect['Quantity'] > 5000) |\n",
    "                    (ect['Quantity'] < -5000)\n",
    "                ]\n",
    "\n",
    "ect_outliers"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It seems that some of these are attributed to wasted product, while others are probably mistaken orders and their respective return. For instance, it seems highly unlikely that a wholesaler would deliberately purchase 74215 ceramic storage jars, as evidenced by the -74215 value that follows. We can discount these outliers during our analysis of the transactions as well. When we again visualize the results, it seems much cleaner."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ectc = ect[\n",
    "            (ect['Quantity'] < 5000) &\n",
    "            (ect['Quantity'] > -5000)\n",
    "         ]\n",
    "\n",
    "ectc.plot(x='InvoiceDate', y='Quantity', figsize=(12,6), title='Quantity over time, excluding overhead fees and extreme values')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Creating a calculated value\n",
    "While `Quantity` and `UnitPrice` are valuable data, in order to assess the value of each transaction it would be valuable to know the `TransactionValue` which is the `UnitPrice` multiplied by `Quantity`. With this data we would be able to assess the revenue generated for each invoice or customer. However, our dataframe does not currently carry this calculated value.\n",
    "\n",
    "In order to create a calculated value in Pandas we must iterate through each row of the dataframe. Pandas supports a method called `.apply()` which is designed for this task. Using `.apply()` we can apply simple functions to columns or rows of the dataframe to generated a calculated value. \n",
    "\n",
    "There are two types of functions which are supported by `.apply()`. The first are numpy functoins such as `.mean()` or `.sum()`. The second is called a `lambda`, which is sort of like a mini function in Python. In the code below we apply a `lambda` to calculate the `TransactionValue`.\n",
    "\n",
    "**Note:** If you don't understand lambdas, don't worry about it, it is here mostly so that you can see it in action. Also, Jupyter may throw a waning at you for using the code below, this is because we made changes to the dataframe that the lambda references, which is not a best practice."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ectc['TransactionValue'] = ectc.apply(lambda row: row['UnitPrice'] * row['Quantity'], axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This will give us a new row called TransactionValue, which we can use for analysis."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ectc.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Grouping Customers\n",
    "By again using `.groupby()` we can now analyze the sales made to each customer. The code below grabs the `customer_sales`, which is the sum of `TransactionValue` for each customer and orders them from largest to smallest."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cust_group = ectc.groupby('CustomerID')\n",
    "customer_sales = cust_group['TransactionValue'].sum().sort_values(ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can now visualize the data. Using a simple line plot we will see that there is a clear exponential relationship where a handful of customers account for most of the revenue. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "customer_sales.plot(figsize=(12,6), title='Customer sales distribution')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's compare the top 10 customers. The code below visualizes the top 10 customers using a bar plot. It seems that customers 14646, 18102 and 17450 account for a considerable number of the overall sales. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "customer_sales[0:10].plot.bar(figsize=(12,6), title='Top 10 customers')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's conclude by analyzing the percentage of the overall sales these three customers account for. The code below takes the sum of sales from the top 3 customers and divides it by the sum of all of the customer sales. It seems that the top 3 customers accounted for nearly 9\\% of the company's overall revenue."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "percent_revenue = (customer_sales[0:3].sum()/customer_sales.sum()) * 100 # calculate sales conducted by top customers\n",
    "percent_revenue = round(percent_revenue, 2) # round the results to two decimal places\n",
    "\n",
    "print('Our top 3 customers accounted for ' + str(percent_revenue) + '% of our revenue.')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Challenge Question 2 (1.5 points)\n",
    "While insights about the top 3 customers are useful, it is also useful to generate insights about the top 10 customers. Create code that:\n",
    "- Calculates the percent of overall revenue generated by the top 10 customers\n",
    "- Calculates the percent of overall count of transactions accounted for by the top 10 customers\n",
    "\n",
    "This may yield insights into how this business should prioritize its customer relationships."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# insert your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Objective 3: Implement Association Rule Mining\n",
    "\n",
    "The final data mining technique which is worth exploring is association rule mining. The algorithm that we will explore is called the **apriori** algorithm. \n",
    "\n",
    "The apriori association mining algorithm is a simple frequency item set mining algorithm, most commonly used to find associations over a transactional database. In the 1990s and early 2000s, this algorithm was used to discover a lot of data mining \"gems\" -- unexpected associations between purchases and behaviours. Today it is still commonly used by retailers and e-commerce companies to look at purchasing patterns. \n",
    "\n",
    "The algorithm looks at all of the transactions in a dataset and returns the most common associations between the items. It uses three measures: **support**, **confidence** and **lift**. Support describes the number of occurrences in the dataset. Confidence is how often the association is found to be true. Lift is the ratio of observed support to the independent observations of the items in the association. [The Wikipedia page gives a more detailed explanation](https://en.wikipedia.org/wiki/Association_rule_learning).\n",
    "\n",
    "### Installing the Apyori Library\n",
    "\n",
    "Before we can get started, we will need to install the apyroi library. Yes, you read that right... aPYori. Python has no shame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pip install apyori"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Unfortunately, the apyori library is not designed to work with pandas easily. As such, we will go back to good old lists. *Warning* this may make you nostalgic for a better time."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import csv #import the csv library for loading csv type files\n",
    "from apyori import apriori #import the apriori library\n",
    "\n",
    "ec = [] # we will declare a list to store the data\n",
    "\n",
    "with open('data/w8_ec.csv', newline='') as csvfile:\n",
    "    datareader = csv.reader(csvfile, delimiter=',', quotechar='|') \n",
    "    for row in datareader: \n",
    "        ec.append(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As before, it's usually a good practice to see the data we are looking at. Let's see how the data is structured."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(ec[4]) #prints the fifth item"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using the apyori library is simple. We simply call then apriori function on the dataset and it will create a bunch of associations for us. Let's do that, while saving the associations as the \"associations\" list. Let's print it to see what the associations look like."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "associations = list(apriori(ec)) #use the apriori function to create associations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(associations) #print the associations"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Wait! This is <u>not readable at all!</u>. By simply printing the list, we retrieved direct output from the associations. We can't use this on any sort of report! \n",
    "\n",
    "That said, we can sort of make out the details of the output. You can see the associations, support and confidence values. These are saved as lists within the list of associations. One way to manage this data is to only print the elements that we need, one by one. Let's loop through the results and only print the relevant data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for a in associations: #loop through each association directly\n",
    "    #corresponds to the list values for association, the support and the confidence.\n",
    "    print(\"Association: \" + str(list(a[0])) + \", Support: \" + str(a[1]) + \", Confidence: \" + str(a[2][0][2]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "That's better. However, it also revealed another problem. We only have 10 association rules.\n",
    "\n",
    "This was caused by the apriori functions default values. By default, it only collects support above 0.1. This is far too low for our dataset, as there is a lot of variance in the purchases. We can override the default by stating it in the function. Let's try lowering it to 0.002 ... patterns that happen at least 1000 times in our dataset (<u>warning: this may take about a minute to process on some computers</u>)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "associations = list(apriori(ec, min_support=0.002))\n",
    "print(len(associations))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for a in associations:\n",
    "    print(\"Association: \" + str(list(a[0])) + \", Support: \" + str(a[1]) + \", Confidence: \" + str(a[2][0][2]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Better still. If you scroll down, we see some interesting patterns start to emerge, such as:\n",
    "<ul>\n",
    "    <li>'United Kingdom', 'ASSORTED COLOUR BIRD ORNAMENT'</li>\n",
    "    <li>'United Kingdom', 'WHITE HANGING HEART T-LIGHT HOLDER'</li>\n",
    "    <li>'United Kingdom', '4.95', '47566', 'PARTY BUNTING'</li>\n",
    "</ul>\n",
    "Though interesting that some products are purchased from the UK at a certain price, it is clear that there is something missing. Typically, basket analysis is conducted <i>on baskets</i>. We seem to have output that is improperly structured for this task.\n",
    "\n",
    "### Conduct basket analysis\n",
    "One option is to restructure the data. Fortunately, we are using a programming language, so this step is relatively simple. We can create a simple script that creates a new list of baskets for association mining. The script below does this and finishes by reporting the number of basket associations produced."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "i = 1\n",
    "basket = [] #specify a list for basket items\n",
    "baskets = [] #a list of baskets\n",
    "invoice = ec[1][0] #a placeholder for the previous invoice number\n",
    "\n",
    "while i < len(ec): #iterate through each item sequentially\n",
    "    if ec[i][0] != invoice: #if this is a new invoice\n",
    "        baskets.append(basket) #append the baskets\n",
    "        basket = [] #start a new basket\n",
    "    basket.append(ec[i][2]) #add this item to basket\n",
    "    invoice = ec[i][0] #specify this current invoice as the new previous invoice\n",
    "    i += 1 #iterate to the next item\n",
    "\n",
    "len(baskets)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As before, we can use the apriori function to create the associations and the print statement to print the results. Let's only print results that also have a confidence of greater than 50%, meaning that at least half of the time, these things are purchased together."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "basket_associations = list(apriori(baskets, min_support=0.02))\n",
    "\n",
    "for a in basket_associations:\n",
    "    if len(a[0]) > 1: # we are only interested in baskets with more than one item in it\n",
    "        if a[2][1][2] > 0.5: # confidence of at least 0.5\n",
    "            print(\"Association: \" + str(list(a[0])) + \", Support: \" + str(a[1]) + \", Confidence: \" + str(a[2][1][2]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is much more powerful information. The way that apyori is structured is quite confusing however the code above simplifies it by retrieving the confidence of the first association in the list.\n",
    "\n",
    "We can see that, for instance, that the purchase of 'PINK REGENCY TEACUP AND SAUCER' is purchased with 'GREEN REGENCY TEACUP AND SAUCER' 60% of the time. From the perspective of marketing, we could use this information to learn more about those customers and why they are purchasing so many saucers, and what are their motivations for doing so at that point in time.\n",
    "\n",
    "What other associations are out there? Feel free to play with the variables in this code to find new results!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Challenge Question 3 (2 points)\n",
    "Re-analyze the basket associations to analyze baskets with at least 1\\% support and a lift of 15. Do you think these associations would be useful? "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# insert your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## References\n",
    "\n",
    "Chen, D., Sain, S. L., and Guo, Kun (2012). Data mining for the online retail industry: A case study of RFM model-based customer segmentation using data mining. _Journal of Database Marketing and Customer Strategy Management 19_(3) 197-208 doi: 10.1057/dbm.2012.17."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}