import pandas as pd
import numpy as np

%%time
Empoyees = pd.read_excel('/home/data/AdventureWorks/Employees.xls')

%%time
Territory = pd.read_excel('/home/data/AdventureWorks/SalesTerritory.xls')

%%time
Customers = pd.read_excel('/home/data/AdventureWorks/Customers.xls')

%%time
Orders = pd.read_excel('/home/data/AdventureWorks/ItemsOrdered.xls')

"""
Explanation: <div style="float: right; color:red; font-weight:bold;">Rename this file before you work on it!</div>
Pandas Exercise
Use the Adventure Works dataset to create the following reports. The dataset is availablt for download in it's original format at https://msdn.microsoft.com/en-us/library/hh403424.aspx (follow instructions to download).
Task

write the Python Pandas expression to produce a table as described in the problem statements.
The SQL expression may give you a hint. It also allows you to see both systems side-by-side.
If you don't know SQL just ignore the SQL code.
End of explanation
"""


SELECT e.EmployeeID, e.Title, e.FirstName, e.LastName, e.VacationHours, e.SickLeaveHours
	FROM dbo.Employees AS e
	WHERE e.VacationHours < 60
	;

"""
Explanation: Filtering (with)
1. Provide a list of employees that are married.
2a. Show me a list of employees that have a lastname that begins with "R".
2b. Show me a list of employees that have a lastname that ends with "r"
2c. Provide a list of employees that have a hyphenated lastname.
3a. Provide a list of employees that are on salary and have more than 35 vacation hours left.
3b. Show the same as above but limit it to American employees.
3c. Show the same as above but limit it to non-American employees.
4a. List the married employees with more than 35 vacation hours, only ones living in Washington state.
4b. Change the logic to include anyone who meets any of the 3 conditions (i.e., people who are either married, live in Washington state, or have more than 35 vacation hours left)
4c. Show the same as above, but only for Production Technicians
5a. List all employees living in Redmond, Seattle, and Bellevue, showing EmployeeID, FirstName, LastName, and City. Sort the list alphabetically by city.
5b. For the list above, make sure these are only in the state of Washington, just to be careful.
6. Provide a list of employees who have no title, whether it's a NULL or empty string.
7a. Provide a list of employees who have at least 60 vacation hours left.
7b. Provide a list of employees who have less than 60 vacation hours left.
End of explanation
"""


