import pandas as pd
import numpy as np

"""
Explanation: Advanced Tables
Why are databases so complex?

Data stored in a database may be split into multiple tables, each containing multiple columns. A column stores a single attribute of the data; a table stores a collection of related attributes.
The database also keeps track of the relationships between different tables. 

Databases are designed to minimize redundancy and maintain data integrity, particularly when data is added, changed, or deleted.

Consistency when updating: no duplicate places for information https://en.wikipedia.org/wiki/Database_normalization
Performance https://en.wikipedia.org/wiki/Star_schema



Side note: you may also have to think about isolation level when working with a database where someone may be updating data as you're trying to read it. The isolation level determines the database read behavior in this situation. See https://en.wikipedia.org/wiki/Isolation_(database_systems).


Working with multiple tables

Two tables can be joined at a time. 'Join' is a binary operator. See https://en.wikipedia.org/wiki/Join_(SQL).
Tables must have key values that can be matched. Usually one table has a primary key and the other table has a foreign key.

Pandas

Pandas allows "merge", "join", and "concatenate" operations. See http://pandas.pydata.org/pandas-docs/version/0.18.1/merging.html#merge-join-and-concatenate for additional reading.
Pandas also allows reshaping and pivoting data tables, see http://pandas.pydata.org/pandas-docs/version/0.18.1/reshaping.html.

In this class, we will cover table joining, merging and concatenation. We will also go over using some of the time-series handling capabilities in Pandas.
End of explanation
"""



    print(x)

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: Concatenating tables in Pandas
To introduce join operations, we will be working with the AdventureWorks dataset, a standard dataset from Microsoft SLQ Server for learing to work with databases. It contains data for the fictitious bicycle manufacturer (Adventure Works Cycles).
Let's starts by importing some tables from AdventureWorks in /home/data/AdventureWorks. These tables contain data on AdventureWorks employees, sales territories, customers, and orders placed by the customers.
End of explanation
"""


Employees.head()

Territory.head()

Customers.head()

Orders.head()

"""
Explanation: Let's take a look at the data we'll be working with:
End of explanation
"""


help(pd.concat)

# constructing the territory tables... as noted, this is an artificial example
TerritoryUSA = Territory[Territory.CountryCode=='US']; TerritoryUSA['RepID'] = np.random.randint(1,1000,5)
TerritoryWorld = Territory[Territory.CountryCode!='US']

TerritoryUSA

TerritoryWorld

# we'll concatenate the databases, but keep separate keys so that we can keep track of which entries came from AdventuresUSA and 
# which from AdventuresWorld.
# We'll use "join='inner'" to only keep colunms that are common to both tables; 
# that is, we will drop the no-longer needed RepID in AdventuresUSA. 
Territory2 = pd.concat([TerritoryUSA, TerritoryWorld], keys=['usa', 'world'], join='inner')

Territory2

"""
Explanation: Let's construct a slightly artificial example. Suppose that AdventureWorks was formed by merging two companies, AdventuresUSA which operated in the US and AdventuresWorld, which operated in other countries. Now we want information on their combined sales territories. 
The Pandas "concat" function is good for stacking tables on top of each other. We will use it to combine the AdventuresUSA and AdventuresWorld territories data tables.
End of explanation
"""


help(pd.DataFrame.append)

Territory3 = TerritoryUSA.append(TerritoryWorld)

Territory3

"""
Explanation: Pandas "append" behaves just like "concat" with axis=0 and join='outer' (i.e., keep all column names). Missing values are set to NaN.
End of explanation
"""


help(pd.merge)

Ans = pd.merge(Employees.loc[:,["EmployeeID","FirstName","MiddleName","LastName","JobTitle","TerritoryID"]], 
               Territory, 
               how='left', on='TerritoryID')
Ans.head()

X = Ans[["FirstName","MiddleName","LastName"]]

Ans['EmployeeName'] = X.apply(lambda x: x.LastName+", "+x.FirstName+" "+str(x.MiddleName), axis=1)

Ans

# Overachiever answer:
Ans['EmployeeName'] = Ans[["FirstName","MiddleName","LastName"]].apply(lambda x: x.LastName+", "+x.FirstName+" "+str(x.MiddleName), axis=1)
Ans = Ans[['EmployeeName', 'EmployeeID', 'JobTitle', 'TerritoryID', 'Name', 'CountryCode', 'Region', 'SalesYTD', 'SalesLastYear']]
Ans

"""
Explanation: Joining and merging tables in Pandas
Join and merge are powerful tools for working with multiple tables. We will use them to answer some questions about the
AdventureWorks dataset that you might encounter in real-life situations.
Join does fast table joining on a shared index. 
Merge does the same thing, but gives you the option to specify columns to join on. 
The idea of joining on a column will become clearer with some examples.
Example 1. "I want a list of all employees, and if any are salespeople then show me the details about their sales territory"
From AdventureWorks, we have a table "Employees" that gives a lot of information about AdventureWorks employees, like 'EmployeeID', 'ManagerID', 'TerritoryID', 'Title', 'FirstName','MiddleName', 'LastName', 'Suffix', 'JobTitle', 'NationalIDNumber', 'BirthDate', 'MaritalStatus', 'Gender', 'HireDate', 'SalariedFlag', 'VacationHours', 'SickLeaveHours', 'PhoneNumber', 'PhoneNumberType', 'EmailAddress', 'AddressLine1', 'AddressLine2', 'City', 'StateProvinceName', 'PostalCode', 'CountryName'. \
Since we're just being asked for a list of employees, we'll give the EmployeeID and their first, middle, and last names, and their role in the company (since additional information is requested for salespeople only). Then, for the salespeople, we must attach information about their sales territories, which is contained in the Territories table. 
Notice that the Employees table has a column 'TerritoryID', which corresponds to the primary key in the 'Territory' table (in 'Territory', each territory has a unique 'TerritoryID'). We'll do a join on TerritoryID.
End of explanation
"""


Ans2 = Ans[Ans.JobTitle=='Sales Representative']
Ans2

# Overachiever: What about *all* employees associated with sales?
Ans2 = Ans[Ans["JobTitle"].apply(lambda x: 'Sales' in x)]
Ans2

"""
Explanation: "For the list above, limit the results to just salespeople"
End of explanation
"""


Ans3 = pd.merge(Customers[["CustomerID","FirstName","LastName","SalesTerritoryID"]], 
                Territory[["TerritoryID","Name"]], 
                how='left', 
                left_on='SalesTerritoryID', right_on='TerritoryID', )
Ans3

"""
Explanation: "Give me a list of our customers, and also tell me which sales territory they fall in."
This looks like another question for "merge"! We have a list of customers with their addresses, and we have a list of territories, but they are in separate tables. 
Let's recover a list of customer names and IDs, together with corresponding sales territory names.
This time, we have to be careful, because "TerritoryID" in the Territory table matches "SalesTerritoryID" in the table Customers. So, we'll have to specify different columns names to merge on for the two tables.
End of explanation
"""


Ans = pd.merge(Territory, Customers, how="inner", left_on="TerritoryID", right_on="SalesTerritoryID")

Ans

"""
Explanation: "Give me a list of all sales territories, also show what customers fall under them"
End of explanation
"""


# In-class exercise! :)

ListNC = Customers[Customers.StateName=='North Carolina']
ListNC

ListNC.CustomerID.count()

Customers = Customers.set_index("StateName")
Customers.loc['North Carolina']

"""
Explanation: "Give me a list of the customers we have in North Carolina, and tell me how many there are."
End of explanation
"""


# We'll use the Orders table for this! In-class exercise :)

Orders["TotalItemPrice"] = Orders.Quantity * Orders.Price
Orders.head()

Orders.groupby(["CustomerID","OrderDate"]).TotalItemPrice.sum()

"""
Explanation: "For each of the items ordered, show the total price (sometimes they ordered more than 1 item)"
End of explanation
"""


# In-class exercise! :)

Ans_1 = pd.merge(Customers[["CustomerID","FirstName","LastName"]], Orders[["CustomerID","TotalItemPrice"]], how="inner", on="CustomerID" )

Ans_1

pd.DataFrame(Ans_1.groupby("CustomerID").TotalItemPrice.sum())

Ans_1a = Ans_1[["FirstName","LastName","CustomerID"]].set_index("CustomerID")
Ans_1b = Ans_1a[Ans_1a.duplicated()==False]

Ans_2 = pd.DataFrame(Ans_1.groupby("CustomerID").TotalItemPrice.sum()).join(Ans_1b)

Ans_2.sort_values("TotalItemPrice", ascending=False)

Ans_2.sort_values(ascending=False)

Territory.apply(max, axis=0)

"""
Explanation: "Show a list of customers, and the total amount of money they have spent with AdventureWorks. I want the highest spenders to appear first!"
End of explanation
"""


help(pd.DataFrame.combine_first)

help(pd.DataFrame.update)


Customers

"""
Explanation: Another side note:
End of explanation
"""


