<commit_before>{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Check Homework HW05\n",
    "Use this notebook to check your solutions. This notebook will **not** be graded."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-29T17:28:38.972793",
     "start_time": "2017-09-29T17:28:38.164229"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, import your solutions from `hw5_answers.py`. The following code looks a bit redundant. However, we do this to allow reloading the `hw5_answers.py` in case you made some changes. Normally, Python assumes that modules don't change and therefore does not try to import them again."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-29T17:28:38.980674",
     "start_time": "2017-09-29T17:28:38.975803"
    },
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import hw5_answers\n",
    "reload(hw5_answers)\n",
    "from hw5_answers import *"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The Employees, Territory, Customers, and Orders tables are the same as those we used in class."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-29T17:28:39.157261",
     "start_time": "2017-09-29T17:28:38.983355"
    },
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "Employees = pd.read_excel('/home/data/AdventureWorks/Employees.xls')\n",
    "Territory = pd.read_excel('/home/data/AdventureWorks/SalesTerritory.xls')\n",
    "Customers = pd.read_excel('/home/data/AdventureWorks/Customers.xls')\n",
    "Orders = pd.read_excel('/home/data/AdventureWorks/ItemsOrdered.xls')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Problem 1\n",
    "Write a function called `get_manager` that takes as its one argument the Pandas DataFrame \"Employees\" and returns a DataFrame containing list of all employees (EmployeeID, first name, middle name, last name), and their manager's first and last name. The columns in the output DataFrame should be: EmployeeID, FirstName, MiddleName, LastName, ManagerFirstName, ManagerLastName.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-29T17:28:39.227225",
     "start_time": "2017-09-29T17:28:39.159358"
    },
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df1 = get_manager(Employees)\n",
    "print \"Shape of resulting table: \", df1.shape\n",
    "print \"Columns: \", ', '.join(df1.columns)\n",
    "df1.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "    Shape of resulting table:  (291, 6)\n",
    "    Columns:  EmployeeID, FirstName, MiddleName, LastName, ManagerFirstName, ManagerLastName\n",
    "\n",
    " | EmployeeID | FirstName |MiddleName | LastName | ManagerFirstName | ManagerLastName\n",
    "-----------|-----------|-----------|----------|------------------|----------------\n",
    "0\t| 259\t| Ben\t| T\t| Miller\t|Sheela\t| Word\n",
    "1\t| 278\t| Garrett\t| R\t| Vargas\t|Stephen\t| Jiang\n",
    "2\t| 204\t| Gabe\t| B\t| Mares\t| Peter\t| Krebs\n",
    "3\t| 78\t| Reuben\t| H\t| D'sa\t| Peter\t| Krebs\n",
    "4\t| 255\t| Gordon\t| L\t| Hee\t| Sheela\t| Word\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Problem 2\n",
    "Write a functon called `get_spend_by_order` that takes as its two arguments the Pandas DataFrames \"Orders\" and \"Customers\", and returns a DataFrame with the following columns: \"FirstName\", \"LastName\", \"Item\", \"TotalSpent\", listing all cutomer names, their purchased items, and the total amount spend on that item (remember that the \"Price\" listed in \"Orders\" is the _price per item_).\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-29T17:28:39.282288",
     "start_time": "2017-09-29T17:28:39.229657"
    },
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "df2 = get_spend_by_order(Orders, Customers)\n",
    "print \"Shape of resulting table: \", df2.shape\n",
    "print \"Columns: \", ', '.join(df2.columns)\n",
    "df2.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "    Shape of resulting table:  (32, 4)\n",
    "    Columns:  FirstName, LastName, Item, TotalSpent\n",
    "    \n",
    "  |FirstName | LastName | Item | TotalSpent\n",
    "----------|----------|------|-----------\n",
    "0 |\tAnthony\t| Sanchez\t| Umbrella\t| 4.5\n",
    "1 |\tConrad\t| Giles\t| Ski Poles\t| 25.5\n",
    "2 |\tConrad\t| Giles\t| Tent\t| 88.0\n",
    "3 |\tDonald\t| Davids\t| Lawnchair\t| 32.0\n",
    "4 |\tElroy\t| Keller\t| Inflatable Mattress\t| 38.0"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Problem 3\n",
    "Write a function called `get_order_location` that takes three arguments: \"Orders\", \"Customers\", and \"Territory\", and returns a DataFrame containing the following columns:  \"CustomerID\", \"Name\", and \"TotalItems\", that gives, for each order, the  CustomerID, the name of the territory where the order was placed, and the total number of items ordered (yes, 2 ski poles counts as 2 items). \n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-29T17:28:39.348259",
     "start_time": "2017-09-29T17:28:39.284798"
    },
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],<commit_msg>update: TotalSpent in problem 2 was not calculated properly<commit_after>{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Check Homework HW05\n",
    "Use this notebook to check your solutions. This notebook will **not** be graded."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-10-08T17:16:30.638477",
     "start_time": "2017-10-08T17:16:29.750705"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, import your solutions from `hw5_answers.py`. The following code looks a bit redundant. However, we do this to allow reloading the `hw5_answers.py` in case you made some changes. Normally, Python assumes that modules don't change and therefore does not try to import them again."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-10-08T17:17:11.356229",
     "start_time": "2017-10-08T17:17:11.350772"
    },
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import hw5_answers\n",
    "reload(hw5_answers)\n",
    "from hw5_answers import *"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The Employees, Territory, Customers, and Orders tables are the same as those we used in class."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-10-08T17:16:33.487487",
     "start_time": "2017-10-08T17:16:33.272556"
    },
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "Employees = pd.read_excel('/home/data/AdventureWorks/Employees.xls')\n",
    "Territory = pd.read_excel('/home/data/AdventureWorks/SalesTerritory.xls')\n",
    "Customers = pd.read_excel('/home/data/AdventureWorks/Customers.xls')\n",
    "Orders = pd.read_excel('/home/data/AdventureWorks/ItemsOrdered.xls')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Problem 1\n",
    "Write a function called `get_manager` that takes as its one argument the Pandas DataFrame \"Employees\" and returns a DataFrame containing list of all employees (EmployeeID, first name, middle name, last name), and their manager's first and last name. The columns in the output DataFrame should be: EmployeeID, FirstName, MiddleName, LastName, ManagerFirstName, ManagerLastName.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-29T17:28:39.227225",
     "start_time": "2017-09-29T17:28:39.159358"
    },
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df1 = get_manager(Employees)\n",
    "print \"Shape of resulting table: \", df1.shape\n",
    "print \"Columns: \", ', '.join(df1.columns)\n",
    "df1.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "    Shape of resulting table:  (291, 6)\n",
    "    Columns:  EmployeeID, FirstName, MiddleName, LastName, ManagerFirstName, ManagerLastName\n",
    "\n",
    " | EmployeeID | FirstName |MiddleName | LastName | ManagerFirstName | ManagerLastName\n",
    "-----------|-----------|-----------|----------|------------------|----------------\n",
    "0\t| 259\t| Ben\t| T\t| Miller\t|Sheela\t| Word\n",
    "1\t| 278\t| Garrett\t| R\t| Vargas\t|Stephen\t| Jiang\n",
    "2\t| 204\t| Gabe\t| B\t| Mares\t| Peter\t| Krebs\n",
    "3\t| 78\t| Reuben\t| H\t| D'sa\t| Peter\t| Krebs\n",
    "4\t| 255\t| Gordon\t| L\t| Hee\t| Sheela\t| Word\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Problem 2\n",
    "Write a functon called `get_spend_by_order` that takes as its two arguments the Pandas DataFrames \"Orders\" and \"Customers\", and returns a DataFrame with the following columns: \"FirstName\", \"LastName\", \"Item\", \"TotalSpent\", listing all cutomer names, their purchased items, and the total amount spend on that item (remember that the \"Price\" listed in \"Orders\" is the _price per item_).\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-10-08T19:40:22.983333",
     "start_time": "2017-10-08T19:40:22.896560"
    },
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "df2 = get_spend_by_order(Orders, Customers)\n",
    "print \"Shape of resulting table: \", df2.shape\n",
    "print \"Columns: \", ', '.join(df2.columns)\n",
    "df2.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "    Shape of resulting table:  (32, 4)\n",
    "    Columns:  FirstName, LastName, Item, TotalSpent\n",
    "    \n",
    "  |FirstName | LastName | Item | TotalSpent\n",
    "----------|----------|------|-----------\n",
    "0 |\tAnthony\t| Sanchez\t| Umbrella\t| 4.5\n",
    "1 |\tConrad\t| Giles\t| Ski Poles\t| 51.0\n",
    "2 |\tConrad\t| Giles\t| Tent\t| 88.0\n",
    "3 |\tDonald\t| Davids\t| Lawnchair\t| 128.0\n",
    "4 |\tElroy\t| Keller\t| Inflatable Mattress\t| 38.0"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Problem 3\n",
    "Write a function called `get_order_location` that takes three arguments: \"Orders\", \"Customers\", and \"Territory\", and returns a DataFrame containing the following columns:  \"CustomerID\", \"Name\", and \"TotalItems\", that gives, for each order, the  CustomerID, the name of the territory where the order was placed, and the total number of items ordered (yes, 2 ski poles counts as 2 items). \n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-29T17:28:39.348259",
     "start_time": "2017-09-29T17:28:39.284798"
    },
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
{'fraction_non_alphanumeric': 0.19953844885848512, 'fraction_numerical': 0.05044094617984011, 'mean_word_length': 2.748532591906086, 'pattern_counts': {'":': 162, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, '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}}
{'dir': 'git-commits-cleaned', 'id': '4938016', 'n_tokens_mistral': 4892, 'n_tokens_neox': 4388, 'n_words': 1048}