{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Date Manipulation with Sales Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#importing the basic libraries\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#read the Sales CSV file\n",
    "salesdf = pd.read_csv(\"Sales.Data.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "salesdf.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#print out the basic info\n",
    "print(salesdf.shape)\n",
    "print(salesdf.dtypes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#we need to cast the date to the python datetime format\n",
    "salesdf['Order Date'] = pd.to_datetime(salesdf['Order Date'])\n",
    "salesdf.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Let us look athe dates\n",
    "salesdf['Order Date'].head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#let us extract the different components and put them in different columns (eg for data warehousing)\n",
    "salesdf['Quarter'] = salesdf['Order Date'].apply(lambda x: x.quarter)\n",
    "salesdf['Year'] = salesdf['Order Date'].apply(lambda x: x.year)\n",
    "salesdf['Month'] = salesdf['Order Date'].apply(lambda x: x.month)\n",
    "salesdf['Week'] = salesdf['Order Date'].apply(lambda x: x.week)\n",
    "salesdf['Day'] = salesdf['Order Date'].apply(lambda x: x.day)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#here is a list of other things you can extract\n",
    "#hour, minute, seconds, month in text format"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "salesdf.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "salesdf.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#we want quarters as strings for OLAP analysis\n",
    "salesdf['Quarter'] = salesdf['Quarter'].astype(str)\n",
    "salesdf.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#now replace 1 with Q1, 2 with Q2, 3 with Q3 and 4 with Q4\n",
    "salesdf['Quarter'] = salesdf['Quarter'].str.replace('1',\"Q1\")\n",
    "salesdf['Quarter'] = salesdf['Quarter'].str.replace('2',\"Q2\")\n",
    "salesdf['Quarter'] = salesdf['Quarter'].str.replace('3',\"Q3\")\n",
    "salesdf['Quarter'] = salesdf['Quarter'].str.replace('4',\"Q4\")\n",
    "salesdf['Quarter'].head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Now, let's aggregate like OLAP and plot (we are not using matplotlib so charts will be looking bad)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#aggregations\n",
    "agg_qtr_sales = salesdf.pivot_table(columns=['Quarter'], values='Sales', aggfunc='sum').round(2)\n",
    "print(agg_qtr_sales)\n",
    "agg_qtr_sales.plot.bar()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "agg_qtr_sales.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "agg_year_sales = salesdf.pivot_table(columns=['Year'], values='Sales', aggfunc='sum').round(2)\n",
    "print(agg_year_sales)\n",
    "agg_year_sales.plot.bar()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "agg_month_sales = salesdf.pivot_table(columns=['Month'], values='Sales', aggfunc='sum').round(2)\n",
    "print(agg_month_sales)\n",
    "agg_month_sales.plot.bar()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "agg_week_sales = salesdf.pivot_table(columns=['Week'], values='Sales', aggfunc='sum').round(2)\n",
    "print(agg_week_sales)\n",
    "agg_week_sales.plot.bar()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Date Manipulation with Orders Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#read the Orders CSV file\n",
    "ordersdf = pd.read_csv(\"Orders.Data.csv\")\n",
    "\n",
    "#print out the basic info\n",
    "print(ordersdf.shape)\n",
    "print(ordersdf.dtypes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#there are different dates regarding the ordering and delivery of a product\n",
    "#what if I want to find out the average timespan between order and delivery?\n",
    "ordersdf.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#all dates are objects so we have to convert them to datetime objects\n",
    "#lets change the column names first\n",
    "ordersdf = ordersdf.rename(columns = {\"order_purchase_timestamp\": \"Purchased_At\",\n",
    "                         \"order_approved_at\":\"Approved_At\",\n",
    "                         \"order_delivered_carrier_date\": \"Delivered_to_Carrier_On\",\n",
    "                         \"order_delivered_customer_date\": \"Delivered_to_Customer_On\",\n",
    "                         \"order_estimated_delivery_date\": \"Estimated_Delivery_On\"}) \n",
    "ordersdf.head(10)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#lets have only the relevant columns for the time being\n",
    "ordersdf = ordersdf[['Purchased_At', 'Approved_At', 'Delivered_to_Carrier_On', 'Delivered_to_Customer_On', 'Estimated_Delivery_On']]\n",
    "ordersdf.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#lets typecast to datetime\n",
    "ordersdf['Purchased_At'] = pd.to_datetime(ordersdf['Purchased_At'])\n",
    "ordersdf['Approved_At'] = pd.to_datetime(ordersdf['Approved_At'])\n",
    "ordersdf['Delivered_to_Carrier_On'] = pd.to_datetime(ordersdf['Delivered_to_Carrier_On'])\n",
    "ordersdf['Delivered_to_Customer_On'] = pd.to_datetime(ordersdf['Delivered_to_Customer_On'])\n",
    "ordersdf['Estimated_Delivery_On'] = pd.to_datetime(ordersdf['Estimated_Delivery_On'])\n",
    "ordersdf.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#lets see some rows\n",
    "ordersdf.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#what is the average difference between order time and approval time\n",
    "between_order_approval = ordersdf['Approved_At'] - ordersdf['Purchased_At']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "between_order_approval.apply(lambda x: (x.total_seconds())/3600).mean()\n",
    "\n",
    "#whoa... 10 hours is the average time between order and approval.. this seems a bit too much\n",
    "#we will dig this later"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#what is the average difference between expected delivery time and actual customer delivery time\n",
    "#assuming that the orders are being delivered later than the estimated date....\n",
    "between_estimated_actual = ordersdf['Delivered_to_Customer_On']-ordersdf['Estimated_Delivery_On']\n",
    "between_estimated_actual.apply(lambda x: (x.total_seconds())/3600).mean()\n",
    "\n",
    "#so, -268 hours means that the orders are being delivered 268 hours (or 11 days before, on the average)\n",
    "#this also seems too good; we need to drill down (or maybe taking averages in this way is not the best analysis)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#what is the average difference between approval and actual delivery\n",
    "#assuming that the orders are being delivered later than the estimated date....\n",
    "between_approved_actual = ordersdf['Delivered_to_Customer_On']-ordersdf['Approved_At']\n",
    "between_approved_actual.apply(lambda x: (x.total_seconds())/3600).mean()\n",
    "\n",
    "#so it takes 291 hours or 12 days on the average from approval to delivery. this seems reasonable."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#let's see the figures between order and approval\n",
    "timediffseries = between_order_approval.apply(lambda x: (x.total_seconds())/3600)\n",
    "timediffseries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "newdf = pd.DataFrame(timediffseries,index = np.arange(len(timediffseries)),columns=['TimeDiff'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "newdf.head(100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(newdf.shape)\n",
    "print(newdf.mean())\n",
    "\n",
    "#the following shows that in 30% rows, time difference is greater than 5 minutes\n",
    "print((((newdf.where(newdf['TimeDiff']>5).count())/99441)*100))\n",
    "\n",
    "#the following shows that in 28% rows, time difference is greater than 10 minutes\n",
    "print((((newdf.where(newdf['TimeDiff']>10).count())/99441)*100))\n",
    "\n",
    "#the following shows that in 5% rows, time difference is greater than 50 minutes\n",
    "print((((newdf.where(newdf['TimeDiff']>50).count())/99441)*100))\n",
    "\n",
    "#the following shows that in 1% rows, time difference is greater than 90 minutes\n",
    "print((((newdf.where(newdf['TimeDiff']>90).count())/99441)*100))\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#so there are outliers.... let's plot the histogram\n",
    "newdf['TimeDiff'].hist()\n",
    "\n",
    "#from this histogram, we can sense some outliers..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#to confirm lets plot the first 100 rows histogram\n",
    "newdf['TimeDiff'].head(100).hist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#lets see the boxplot to confirm\n",
    "newdf.boxplot(column='TimeDiff', sym='o', return_type='axes')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#so we have two points, one where TimeDiff>1000 and one >4000\n",
    "#lets see those rows\n",
    "newdf[newdf['TimeDiff']>1000]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#lets skip them and see the boxplot\n",
    "subset = newdf[(newdf.index != 47552) & (newdf.index != 62293)]\n",
    "subset.count()\n",
    "\n",
    "subset.boxplot(column='TimeDiff', sym='o', return_type='axes')\n",
    "#still we see that there are problems...ie some orders are taking too long for approval.."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "subset = newdf[newdf['TimeDiff']<100]\n",
    "print(subset.count())\n",
    "\n",
    "subset.boxplot(column='TimeDiff', sym='o', return_type='axes')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#lets do it once more with 40\n",
    "subset = newdf[newdf['TimeDiff']<40]\n",
    "print(subset.count())\n",
    "\n",
    "subset.boxplot(column='TimeDiff', sym='o', return_type='axes')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#lets do it once more with 40\n",
    "subset = newdf[newdf['TimeDiff']<1]\n",
    "print(subset.count())\n",
    "\n",
    "subset.boxplot(column='TimeDiff', sym='o', return_type='axes')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#let us try to find out how many rows are above 40\n",
    "subset = newdf[newdf['TimeDiff']>40]\n",
    "print(subset.count())\n",
    "\n",
    "#so these all can be considered as sort of outliers.. but the way the orders are being processed needs to be drilled down\n",
    "#i guess this has to do with different products."
   ]
  }
 ],
 "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.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
