Exercises_1.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![rmotr](https://user-images.githubusercontent.com/7065401/52071918-bda15380-2562-11e9-828c-7f95297e4a82.png)\n",
    "<hr style=\"margin-bottom: 40px;\">\n",
    "\n",
    "<img src=\"https://user-images.githubusercontent.com/7065401/58563302-42466a80-8201-11e9-9948-b3e9f88a5662.jpg\"\n",
    "    style=\"width:400px; float: right; margin: 0 40px 40px 40px;\"></img>\n",
    "\n",
    "# Exercises\n",
    "## Bike store sales"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![purple-divider](https://user-images.githubusercontent.com/7065401/52071927-c1cd7100-2562-11e9-908a-dde91ba14e59.png)\n",
    "\n",
    "## Hands on! "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales = pd.read_csv(\n",
    "    'data/sales_data.csv',\n",
    "    parse_dates=['Date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Date</th>\n",
       "      <th>Day</th>\n",
       "      <th>Month</th>\n",
       "      <th>Year</th>\n",
       "      <th>Customer_Age</th>\n",
       "      <th>Age_Group</th>\n",
       "      <th>Customer_Gender</th>\n",
       "      <th>Country</th>\n",
       "      <th>State</th>\n",
       "      <th>Product_Category</th>\n",
       "      <th>Sub_Category</th>\n",
       "      <th>Product</th>\n",
       "      <th>Order_Quantity</th>\n",
       "      <th>Unit_Cost</th>\n",
       "      <th>Unit_Price</th>\n",
       "      <th>Profit</th>\n",
       "      <th>Cost</th>\n",
       "      <th>Revenue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2013-11-26</td>\n",
       "      <td>26</td>\n",
       "      <td>November</td>\n",
       "      <td>2013</td>\n",
       "      <td>19</td>\n",
       "      <td>Youth (&lt;25)</td>\n",
       "      <td>M</td>\n",
       "      <td>Canada</td>\n",
       "      <td>British Columbia</td>\n",
       "      <td>Accessories</td>\n",
       "      <td>Bike Racks</td>\n",
       "      <td>Hitch Rack - 4-Bike</td>\n",
       "      <td>8</td>\n",
       "      <td>45</td>\n",
       "      <td>120</td>\n",
       "      <td>590</td>\n",
       "      <td>360</td>\n",
       "      <td>950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015-11-26</td>\n",
       "      <td>26</td>\n",
       "      <td>November</td>\n",
       "      <td>2015</td>\n",
       "      <td>19</td>\n",
       "      <td>Youth (&lt;25)</td>\n",
       "      <td>M</td>\n",
       "      <td>Canada</td>\n",
       "      <td>British Columbia</td>\n",
       "      <td>Accessories</td>\n",
       "      <td>Bike Racks</td>\n",
       "      <td>Hitch Rack - 4-Bike</td>\n",
       "      <td>8</td>\n",
       "      <td>45</td>\n",
       "      <td>120</td>\n",
       "      <td>590</td>\n",
       "      <td>360</td>\n",
       "      <td>950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2014-03-23</td>\n",
       "      <td>23</td>\n",
       "      <td>March</td>\n",
       "      <td>2014</td>\n",
       "      <td>49</td>\n",
       "      <td>Adults (35-64)</td>\n",
       "      <td>M</td>\n",
       "      <td>Australia</td>\n",
       "      <td>New South Wales</td>\n",
       "      <td>Accessories</td>\n",
       "      <td>Bike Racks</td>\n",
       "      <td>Hitch Rack - 4-Bike</td>\n",
       "      <td>23</td>\n",
       "      <td>45</td>\n",
       "      <td>120</td>\n",
       "      <td>1366</td>\n",
       "      <td>1035</td>\n",
       "      <td>2401</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016-03-23</td>\n",
       "      <td>23</td>\n",
       "      <td>March</td>\n",
       "      <td>2016</td>\n",
       "      <td>49</td>\n",
       "      <td>Adults (35-64)</td>\n",
       "      <td>M</td>\n",
       "      <td>Australia</td>\n",
       "      <td>New South Wales</td>\n",
       "      <td>Accessories</td>\n",
       "      <td>Bike Racks</td>\n",
       "      <td>Hitch Rack - 4-Bike</td>\n",
       "      <td>20</td>\n",
       "      <td>45</td>\n",
       "      <td>120</td>\n",
       "      <td>1188</td>\n",
       "      <td>900</td>\n",
       "      <td>2088</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2014-05-15</td>\n",
       "      <td>15</td>\n",
       "      <td>May</td>\n",
       "      <td>2014</td>\n",
       "      <td>47</td>\n",
       "      <td>Adults (35-64)</td>\n",
       "      <td>F</td>\n",
       "      <td>Australia</td>\n",
       "      <td>New South Wales</td>\n",
       "      <td>Accessories</td>\n",
       "      <td>Bike Racks</td>\n",
       "      <td>Hitch Rack - 4-Bike</td>\n",
       "      <td>4</td>\n",
       "      <td>45</td>\n",
       "      <td>120</td>\n",
       "      <td>238</td>\n",
       "      <td>180</td>\n",
       "      <td>418</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Date  Day     Month  Year  Customer_Age       Age_Group  \\\n",
       "0 2013-11-26   26  November  2013            19     Youth (<25)   \n",
       "1 2015-11-26   26  November  2015            19     Youth (<25)   \n",
       "2 2014-03-23   23     March  2014            49  Adults (35-64)   \n",
       "3 2016-03-23   23     March  2016            49  Adults (35-64)   \n",
       "4 2014-05-15   15       May  2014            47  Adults (35-64)   \n",
       "\n",
       "  Customer_Gender    Country             State Product_Category Sub_Category  \\\n",
       "0               M     Canada  British Columbia      Accessories   Bike Racks   \n",
       "1               M     Canada  British Columbia      Accessories   Bike Racks   \n",
       "2               M  Australia   New South Wales      Accessories   Bike Racks   \n",
       "3               M  Australia   New South Wales      Accessories   Bike Racks   \n",
       "4               F  Australia   New South Wales      Accessories   Bike Racks   \n",
       "\n",
       "               Product  Order_Quantity  Unit_Cost  Unit_Price  Profit  Cost  \\\n",
       "0  Hitch Rack - 4-Bike               8         45         120     590   360   \n",
       "1  Hitch Rack - 4-Bike               8         45         120     590   360   \n",
       "2  Hitch Rack - 4-Bike              23         45         120    1366  1035   \n",
       "3  Hitch Rack - 4-Bike              20         45         120    1188   900   \n",
       "4  Hitch Rack - 4-Bike               4         45         120     238   180   \n",
       "\n",
       "   Revenue  \n",
       "0      950  \n",
       "1      950  \n",
       "2     2401  \n",
       "3     2088  \n",
       "4      418  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sales.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![green-divider](https://user-images.githubusercontent.com/7065401/52071924-c003ad80-2562-11e9-8297-1c6595f8a7ff.png)\n",
    "\n",
    "### What's the mean of `Customers_Age`?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code goes here\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "cell_type": "hint"
   },
   "source": [
    "Why don't you try with `.mean()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_type": "solution"
   },
   "outputs": [],
   "source": [
    "sales['Customer_Age'].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Go ahead and show a <b>density (KDE)</b> and a <b>box plot</b> with the `Customer_Age` data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code goes here\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_type": "solution"
   },
   "outputs": [],
   "source": [
    "sales['Customer_Age'].plot(kind='kde', figsize=(14,6))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_type": "solution"
   },
   "outputs": [],
   "source": [
    "sales['Customer_Age'].plot(kind='box', vert=False, figsize=(14,6))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![green-divider](https://user-images.githubusercontent.com/7065401/52071924-c003ad80-2562-11e9-8297-1c6595f8a7ff.png)\n",
    "\n",
    "### What's the mean of `Order_Quantity`?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code goes here\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_type": "solution"
   },
   "outputs": [],
   "source": [
    "sales['Order_Quantity'].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Go ahead and show a <b>histogram</b> and a <b>box plot</b> with the `Order_Quantity` data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code goes here\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_type": "solution"
   },
   "outputs": [],
   "source": [
    "sales['Order_Quantity'].plot(kind='hist', bins=30, figsize=(14,6))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_type": "solution"
   },
   "outputs": [],
   "source": [
    "sales['Order_Quantity'].plot(kind='box', vert=False, figsize=(14,6))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![green-divider](https://user-images.githubusercontent.com/7065401/52071924-c003ad80-2562-11e9-8297-1c6595f8a7ff.png)\n",
    "\n",
    "### How many sales per year do we have?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code goes here\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_type": "solution"
   },
   "outputs": [],
   "source": [
    "sales['Year'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Go ahead and show a <b>pie plot</b> with the previous data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code goes here\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_type": "solution"
   },
   "outputs": [],
   "source": [
    "sales['Year'].value_counts().plot(kind='pie', figsize=(6,6))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![green-divider](https://user-images.githubusercontent.com/7065401/52071924-c003ad80-2562-11e9-8297-1c6595f8a7ff.png)\n",
    "\n",
    "### How many sales per month do we have?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code goes here\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_type": "solution"
   },
   "outputs": [],
   "source": [
    "sales['Month'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Go ahead and show a <b>bar plot</b> with the previous data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code goes here\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_type": "solution"
   },
   "outputs": [],
   "source": [
    "sales['Month'].value_counts().plot(kind='bar', figsize=(14,6))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![green-divider](https://user-images.githubusercontent.com/7065401/52071924-c003ad80-2562-11e9-8297-1c6595f8a7ff.png)\n",
    "\n",
    "### Which country has the most sales `quantity of sales`?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code goes here\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_type": "solution"
   },
   "outputs": [],
   "source": [
    "sales['Country'].value_counts().head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_type": "solution"
   },
   "outputs": [],
   "source": [
    "sales['Country'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Go ahead and show a <b>bar plot</b> of the sales per country:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code goes here\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_type": "solution"
   },
   "outputs": [],
   "source": [
    "sales['Country'].value_counts().plot(kind='bar', figsize=(14,6))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![green-divider](https://user-images.githubusercontent.com/7065401/52071924-c003ad80-2562-11e9-8297-1c6595f8a7ff.png)\n",
    "\n",
    "### Create a list of every product sold"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code goes here\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_type": "solution"
   },
   "outputs": [],
   "source": [
    "#sales.loc[:, 'Product'].unique()\n",
    "\n",
    "sales['Product'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create a **bar plot** showing the 10 most sold products (best sellers):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code goes here\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_type": "solution"
   },
   "outputs": [],
   "source": [
    "sales['Product'].value_counts().head(10).plot(kind='bar', figsize=(14,6))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![green-divider](https://user-images.githubusercontent.com/7065401/52071924-c003ad80-2562-11e9-8297-1c6595f8a7ff.png)\n",
    "\n",
    "### Can you see any relationship between `Unit_Cost` and `Unit_Price`?\n",
    "\n",
    "Show a <b>scatter plot</b> between both columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code goes here\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_type": "solution"
   },
   "outputs": [],
   "source": [
    "sales.plot(kind='scatter', x='Unit_Cost', y='Unit_Price', figsize=(6,6))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![green-divider](https://user-images.githubusercontent.com/7065401/52071924-c003ad80-2562-11e9-8297-1c6595f8a7ff.png)\n",
    "\n",
    "### Can you see any relationship between `Order_Quantity` and `Profit`?\n",
    "\n",
    "Show a <b>scatter plot</b> between both columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code goes here\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_type": "solution"
   },
   "outputs": [],
   "source": [
    "sales.plot(kind='scatter', x='Order_Quantity', y='Profit', figsize=(6,6))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![green-divider](https://user-images.githubusercontent.com/7065401/52071924-c003ad80-2562-11e9-8297-1c6595f8a7ff.png)\n",
    "\n",
    "### Can you see any relationship between `Profit` per `Country`?\n",
    "\n",
    "Show a grouped <b>box plot</b> per country with the profit values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code goes here\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_type": "solution"
   },
   "outputs": [],
   "source": [
    "sales[['Profit', 'Country']].boxplot(by='Country', figsize=(10,6))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![green-divider](https://user-images.githubusercontent.com/7065401/52071924-c003ad80-2562-11e9-8297-1c6595f8a7ff.png)\n",
    "\n",
    "### Can you see any relationship between the `Customer_Age` per `Country`?\n",
    "\n",
    "Show a grouped <b>box plot</b> per country with the customer age values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code goes here\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_type": "solution"
   },
   "outputs": [],
   "source": [
    "sales[['Customer_Age', 'Country']].boxplot(by='Country', figsize=(10,6))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![green-divider](https://user-images.githubusercontent.com/7065401/52071924-c003ad80-2562-11e9-8297-1c6595f8a7ff.png)\n",
    "\n",
    "### Add and calculate a new `Calculated_Date` column\n",
    "\n",
    "Use `Day`, `Month`, `Year` to create a `Date` column (`YYYY-MM-DD`)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code goes here\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_type": "solution"
   },
   "outputs": [],
   "source": [
    "sales['Calculated_Date'] = sales[['Year', 'Month', 'Day']].apply(lambda x: '{}-{}-{}'.format(x[0], x[1], x[2]), axis=1)\n",
    "\n",
    "sales['Calculated_Date'].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![green-divider](https://user-images.githubusercontent.com/7065401/52071924-c003ad80-2562-11e9-8297-1c6595f8a7ff.png)\n",
    "\n",
    "### Parse your `Calculated_Date` column into a datetime object"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code goes here\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_type": "solution"
   },
   "outputs": [],
   "source": [
    "sales['Calculated_Date'] = pd.to_datetime(sales['Calculated_Date'])\n",
    "\n",
    "sales['Calculated_Date'].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![green-divider](https://user-images.githubusercontent.com/7065401/52071924-c003ad80-2562-11e9-8297-1c6595f8a7ff.png)\n",
    "\n",
    "### How did sales evolve through the years?\n",
    "\n",
    "Show a <b>line plot</b> using `Calculated_Date` column as the x-axis and the count of sales as the y-axis."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code goes here\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_type": "solution"
   },
   "outputs": [],
   "source": [
    "sales['Calculated_Date'].value_counts().plot(kind='line', figsize=(14,6))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![green-divider](https://user-images.githubusercontent.com/7065401/52071924-c003ad80-2562-11e9-8297-1c6595f8a7ff.png)\n",
    "\n",
    "### Increase 50 U$S revenue to every sale"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code goes here\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_type": "solution"
   },
   "outputs": [],
   "source": [
    "#sales['Revenue'] = sales['Revenue'] + 50\n",
    "\n",
    "sales['Revenue'] += 50"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![green-divider](https://user-images.githubusercontent.com/7065401/52071924-c003ad80-2562-11e9-8297-1c6595f8a7ff.png)\n",
    "\n",
    "### How many orders were made in `Canada` or `France`?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code goes here\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_type": "solution"
   },
   "outputs": [],
   "source": [
    "sales.loc[(sales['Country'] == 'Canada') | (sales['Country'] == 'France')].shape[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![green-divider](https://user-images.githubusercontent.com/7065401/52071924-c003ad80-2562-11e9-8297-1c6595f8a7ff.png)\n",
    "\n",
    "### How many `Bike Racks` orders were made from Canada?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code goes here\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_type": "solution"
   },
   "outputs": [],
   "source": [
    "sales.loc[(sales['Country'] == 'Canada') & (sales['Sub_Category'] == 'Bike Racks')].shape[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![green-divider](https://user-images.githubusercontent.com/7065401/52071924-c003ad80-2562-11e9-8297-1c6595f8a7ff.png)\n",
    "\n",
    "### How many orders were made in each region (state) of France?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code goes here\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_type": "solution"
   },
   "outputs": [],
   "source": [
    "france_states = sales.loc[sales['Country'] == 'France', 'State'].value_counts()\n",
    "\n",
    "france_states"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Go ahead and show a <b>bar plot</b> with the results:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code goes here\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_type": "solution"
   },
   "outputs": [],
   "source": [
    "france_states.plot(kind='bar', figsize=(14,6))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![green-divider](https://user-images.githubusercontent.com/7065401/52071924-c003ad80-2562-11e9-8297-1c6595f8a7ff.png)\n",
    "\n",
    "### How many sales were made per category?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code goes here\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_type": "solution"
   },
   "outputs": [],
   "source": [
    "sales['Product_Category'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Go ahead and show a <b>pie plot</b> with the results:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code goes here\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_type": "solution"
   },
   "outputs": [],
   "source": [
    "sales['Product_Category'].value_counts().plot(kind='pie', figsize=(6,6))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![green-divider](https://user-images.githubusercontent.com/7065401/52071924-c003ad80-2562-11e9-8297-1c6595f8a7ff.png)\n",
    "\n",
    "### How many orders were made per accessory sub-categories?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code goes here\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_type": "solution"
   },
   "outputs": [],
   "source": [
    "accessories = sales.loc[sales['Product_Category'] == 'Accessories', 'Sub_Category'].value_counts()\n",
    "\n",
    "accessories"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Go ahead and show a <b>bar plot</b> with the results:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code goes here\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_type": "solution"
   },
   "outputs": [],
   "source": [
    "accessories.plot(kind='bar', figsize=(14,6))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![green-divider](https://user-images.githubusercontent.com/7065401/52071924-c003ad80-2562-11e9-8297-1c6595f8a7ff.png)\n",
    "\n",
    "### How many orders were made per bike sub-categories?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code goes here\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_type": "solution"
   },
   "outputs": [],
   "source": [
    "bikes = sales.loc[sales['Product_Category'] == 'Bikes', 'Sub_Category'].value_counts()\n",
    "\n",
    "bikes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Go ahead and show a <b>pie plot</b> with the results:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code goes here\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_type": "solution"
   },
   "outputs": [],
   "source": [
    "bikes.plot(kind='pie', figsize=(6,6))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![green-divider](https://user-images.githubusercontent.com/7065401/52071924-c003ad80-2562-11e9-8297-1c6595f8a7ff.png)\n",
    "\n",
    "### Which gender has the most amount of sales?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code goes here\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_type": "solution"
   },
   "outputs": [],
   "source": [
    "sales['Customer_Gender'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_type": "solution"
   },
   "outputs": [],
   "source": [
    "sales['Customer_Gender'].value_counts().plot(kind='bar')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![green-divider](https://user-images.githubusercontent.com/7065401/52071924-c003ad80-2562-11e9-8297-1c6595f8a7ff.png)\n",
    "\n",
    "### How many sales with more than 500 in `Revenue` were made by men?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code goes here\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_type": "solution"
   },
   "outputs": [],
   "source": [
    "sales.loc[(sales['Customer_Gender'] == 'M') & (sales['Revenue'] == 500)].shape[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![green-divider](https://user-images.githubusercontent.com/7065401/52071924-c003ad80-2562-11e9-8297-1c6595f8a7ff.png)\n",
    "\n",
    "### Get the top-5 sales with the highest revenue"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code goes here\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_type": "solution"
   },
   "outputs": [],
   "source": [
    "sales.sort_values(['Revenue'], ascending=False).head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![green-divider](https://user-images.githubusercontent.com/7065401/52071924-c003ad80-2562-11e9-8297-1c6595f8a7ff.png)\n",
    "\n",
    "### Get the sale with the highest revenue"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code goes here\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_type": "solution"
   },
   "outputs": [],
   "source": [
    "#sales.sort_values(['Revenue'], ascending=False).head(1)\n",
    "\n",
    "cond = sales['Revenue'] == sales['Revenue'].max()\n",
    "\n",
    "sales.loc[cond]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![green-divider](https://user-images.githubusercontent.com/7065401/52071924-c003ad80-2562-11e9-8297-1c6595f8a7ff.png)\n",
    "\n",
    "### What is the mean `Order_Quantity` of orders with more than 10K in revenue?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code goes here\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_type": "solution"
   },
   "outputs": [],
   "source": [
    "cond = sales['Revenue'] > 10_000\n",
    "\n",
    "sales.loc[cond, 'Order_Quantity'].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![green-divider](https://user-images.githubusercontent.com/7065401/52071924-c003ad80-2562-11e9-8297-1c6595f8a7ff.png)\n",
    "\n",
    "### What is the mean `Order_Quantity` of orders with less than 10K in revenue?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code goes here\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_type": "solution"
   },
   "outputs": [],
   "source": [
    "cond = sales['Revenue'] < 10_000\n",
    "\n",
    "sales.loc[cond, 'Order_Quantity'].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![green-divider](https://user-images.githubusercontent.com/7065401/52071924-c003ad80-2562-11e9-8297-1c6595f8a7ff.png)\n",
    "\n",
    "### How many orders were made in May of 2016?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code goes here\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_type": "solution"
   },
   "outputs": [],
   "source": [
    "cond = (sales['Year'] == 2016) & (sales['Month'] == 'May')\n",
    "\n",
    "sales.loc[cond].shape[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![green-divider](https://user-images.githubusercontent.com/7065401/52071924-c003ad80-2562-11e9-8297-1c6595f8a7ff.png)\n",
    "\n",
    "### How many orders were made between May and July of 2016?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code goes here\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_type": "solution"
   },
   "outputs": [],
   "source": [
    "cond = (sales['Year'] == 2016) & (sales['Month'].isin(['May', 'June', 'July']))\n",
    "\n",
    "sales.loc[cond].shape[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Show a grouped <b>box plot</b> per month with the profit values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code goes here\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_type": "solution"
   },
   "outputs": [],
   "source": [
    "profit_2016 = sales.loc[sales['Year'] == 2016, ['Profit', 'Month']]\n",
    "\n",
    "profit_2016.boxplot(by='Month', figsize=(14,6))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![green-divider](https://user-images.githubusercontent.com/7065401/52071924-c003ad80-2562-11e9-8297-1c6595f8a7ff.png)\n",
    "\n",
    "### Add 7.2% TAX on every sale `Unit_Price` within United States"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code goes here\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cell_type": "solution"
   },
   "outputs": [],
   "source": [
    "#sales.loc[sales['Country'] == 'United States', 'Unit_Price'] = sales.loc[sales['Country'] == 'United States', 'Unit_Price'] * 1.072\n",
    "\n",
    "sales.loc[sales['Country'] == 'United States', 'Unit_Price'] *= 1.072"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![purple-divider](https://user-images.githubusercontent.com/7065401/52071927-c1cd7100-2562-11e9-908a-dde91ba14e59.png)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.11.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}