<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Check Homework HW05
Use this notebook to check your solutions. This notebook will not be graded.
Step1:  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.
Step2:  The Employees, Territory, Customers, and Orders tables are the same as those we used in class.
Step3:  Problem 1
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
Step4:  Shape of resulting table
Step5:  Shape of resulting table
Step6:  Shape of resulting table
<ASSISTANT_TASK:>
Python Code:
import pandas as pd
import numpy as np
Explanation: Check Homework HW05
Use this notebook to check your solutions. This notebook will not be graded.
End of explanation
import hw5_answers
reload(hw5_answers)
from hw5_answers import *
Explanation: 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.
End of explanation
Employees = pd.read_excel('/home/data/AdventureWorks/Employees.xls')
Territory = pd.read_excel('/home/data/AdventureWorks/SalesTerritory.xls')
Customers = pd.read_excel('/home/data/AdventureWorks/Customers.xls')
Orders = pd.read_excel('/home/data/AdventureWorks/ItemsOrdered.xls')
Explanation: The Employees, Territory, Customers, and Orders tables are the same as those we used in class.
End of explanation
df1 = get_manager(Employees)
print "Shape of resulting table: ", df1.shape
print "Columns: ", ', '.join(df1.columns)
df1.head()
Explanation: Problem 1
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.
End of explanation
df2 = get_spend_by_order(Orders, Customers)
print "Shape of resulting table: ", df2.shape
print "Columns: ", ', '.join(df2.columns)
df2.head()
Explanation: Shape of resulting table:  (291, 6)
Columns:  EmployeeID, FirstName, MiddleName, LastName, ManagerFirstName, ManagerLastName
| EmployeeID | FirstName |MiddleName | LastName | ManagerFirstName | ManagerLastName
-----------|-----------|-----------|----------|------------------|----------------
0   | 259   | Ben   | T | Miller    |Sheela | Word
1   | 278   | Garrett   | R | Vargas    |Stephen    | Jiang
2   | 204   | Gabe  | B | Mares | Peter | Krebs
3   | 78    | Reuben    | H | D'sa  | Peter | Krebs
4   | 255   | Gordon    | L | Hee   | Sheela    | Word
Problem 2
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).
End of explanation
df3 = get_order_location(Orders, Customers, Territory)
print "Shape of resulting table: ", df3.shape
print "Columns: ", ', '.join(df3.columns)
df3.head()
Explanation: Shape of resulting table:  (32, 4)
Columns:  FirstName, LastName, Item, TotalSpent
|FirstName | LastName | Item | TotalSpent
----------|----------|------|-----------
0 | Anthony | Sanchez   | Umbrella  | 4.5
1 | Conrad  | Giles | Ski Poles | 25.5
2 | Conrad  | Giles | Tent  | 88.0
3 | Donald  | Davids    | Lawnchair | 32.0
4 | Elroy   | Keller    | Inflatable Mattress   | 38.0
Problem 3
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).
End of explanation
df4 = employee_info(Employees)
print "Shape of resulting table: ", df4.shape
print "Columns: ", ', '.join(df4.columns)
df4.head()
Explanation: Shape of resulting table:  (11, 3)
Columns:  CustomerID, Name, TotalItems
| CustomerID | Name | TotalItems
-----------|------|-----------
0   | 10315 | Central   | 1
1   | 10438 | Central   | 3
2   | 10439 | Central   | 2
3   | 10101 | Northwest | 6
4   | 10299 | Northwest | 2
Problem 4
Write a function called employee_info that takes one argument: "Employees", and returns a DataFrame containing the following columns: JobTitle, NumberOfEmployees, and MeanVacationHours, containing all job titles, the number of employees with that job title, and the mean number of vacation days for employees with that job title.
End of explanation
<END_TASK>