Data_Analysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Library and Concatenate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import libraries\n",
    "import os\n",
    "import glob\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "sns.set(style='whitegrid')\n",
    "%matplotlib inline\n",
    "\n",
    "from matplotlib import rcParams\n",
    "rcParams['patch.force_edgecolor'] = True\n",
    "rcParams['patch.facecolor'] = 'b'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Concate .csv files\n",
    "os.chdir(\"./Sales Data Examples\")\n",
    "extension = 'csv'\n",
    "all_filenames = [i for i in glob.glob('*.{}'.format(extension), recursive=True)]\n",
    "combined_csv = pd.concat([pd.read_csv(f) for f in all_filenames])\n",
    "combined_csv.to_csv( \"Sales_2019.csv\", index=False, encoding='utf-8-sig')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Read dataframe and Clear outliers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales = pd.read_csv(\"Sales_2019.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "sales.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "nan_sales = sales[sales.isna().any(axis=1)]\n",
    "display(nan_sales.head())\n",
    "\n",
    "sales = sales.dropna(how='all')\n",
    "sales = sales[sales['Order Date'].str[0:4] != 'Null']\n",
    "sales.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Format columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales['Quantity'] = pd.to_numeric(sales['Quantity'])\n",
    "sales['Price'] = pd.to_numeric(sales['Price'])\n",
    "sales['Order Date'] = pd.to_datetime(sales['Order Date'], format='%d/%m/%y %H:%M')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales['Year'] = pd.DatetimeIndex(sales['Order Date']).year\n",
    "sales['Month'] = pd.DatetimeIndex(sales['Order Date']).month\n",
    "sales['Day'] = pd.DatetimeIndex(sales['Order Date']).day\n",
    "sales['Hour'] = pd.DatetimeIndex(sales['Order Date']).hour\n",
    "sales['Minute'] = pd.DatetimeIndex(sales['Order Date']).minute\n",
    "sales.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def district_column(address):\n",
    "    return address.split(\",\")[1].split(\" \")[2]\n",
    "\n",
    "sales['District'] = sales['Purchase Address'].apply(lambda x: f\"{district_column(x)}\")\n",
    "sales.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### EDA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales['Sales'] = sales['Quantity'] * sales['Price']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Sales by Month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "df = sales.groupby('Month').agg(Total_Order_Value = ('Sales', 'sum'), Count_Order = ('Order ID', 'count'))\n",
    "\n",
    "f, ax = plt.subplots(figsize=(9, 6))\n",
    "plt.title('Sales Trend by Month ($)')\n",
    "sns.barplot(x=df.index, y=\"Total_Order_Value\", data=df, color=\"g\")\n",
    "ax2 = plt.twinx()\n",
    "sns.pointplot(x=df.index, y=\"Count_Order\", data=df, color=\"b\", ax=ax2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Sales by Hour of the Day"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = sales.groupby('Hour').agg(Total_Order_Value = ('Sales', 'sum'), Count_Order = ('Order ID', 'count'))\n",
    "\n",
    "f, ax = plt.subplots(figsize=(9, 6))\n",
    "plt.title('Sales Trend by Hour ($)')\n",
    "sns.barplot(x=df.index, y=\"Total_Order_Value\", data=df, color=\"g\")\n",
    "ax2 = plt.twinx()\n",
    "sns.pointplot(x=df.index, y=\"Count_Order\", data=df, color=\"b\", ax=ax2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Sales by Territory"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = sales.groupby('District').agg(Total_Order_Value = ('Sales', 'sum'), Count_Order = ('Order ID', 'count'))\n",
    "\n",
    "f, ax = plt.subplots(figsize=(9, 6))\n",
    "plt.title('Sales Trend by Territory ($)')\n",
    "sns.barplot(x=df.index, y=\"Total_Order_Value\", data=df, color=\"b\")\n",
    "ax2 = plt.twinx()\n",
    "sns.pointplot(x=df.index, y=\"Count_Order\", data=df, color=\"g\", ax=ax2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Sales by Product"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = sales.groupby('Product').agg(Total_Order_Value = ('Sales', 'sum'), Count_Order = ('Order ID', 'count'))\n",
    "\n",
    "f, ax = plt.subplots(figsize=(9, 6))\n",
    "plt.title('Sales trend by Product ($)')\n",
    "sns.barplot(x=\"Total_Order_Value\", y=df.index, data=df, orient='h', color=\"b\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Top 5 triple-bundling package"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = sales[sales['Order ID'].duplicated(keep=False)]\n",
    "df['Bundle'] = df.groupby('Order ID')['Product'].transform(lambda x: ', '.join(x))\n",
    "df_pair = df[['Order ID', 'Bundle']].drop_duplicates()\n",
    "df_pair.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Referenced: https://stackoverflow.com/questions/52195887/counting-unique-pairs-of-numbers-into-a-python-dictionary\n",
    "from itertools import combinations\n",
    "from collections import Counter\n",
    "\n",
    "count = Counter()\n",
    "\n",
    "for row in df_pair['Bundle']:\n",
    "    row_list = row.split(', ')\n",
    "    count.update(Counter(combinations(row_list, 3)))\n",
    "\n",
    "for key, value in count.most_common(5):\n",
    "    print(key, '-', value, 'times')"
   ]
  }
 ],
 "metadata": {
  "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.4-final"
  },
  "orig_nbformat": 2,
  "kernelspec": {
   "name": "python3",
   "display_name": "Python 3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}