"""
Created on Thu Nov 14 15:33:30 2019

@author: Gai nho Kiki
"""

import pandas as pd
xl = pd.ExcelFile("SalesData.xlsx")
SalesDatae = xl.parse("Orders")
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
xl = pd.ExcelFile("SalesData.xlsx")
SalesData = xl.parse("Orders")
print("------------------------------------")
print("The total profit, sales, and discount that sold per year")
SalesYear = SalesData
SalesYear["Year"] = SalesYear["Order Date"].dt.year
YearlySales = SalesYear [["Year", "Sales", "Discount", "Profit"]]
YearlyTotalSales = YearlySales.groupby(by= "Year").sum()
YearlyTotalSales = YearlyTotalSales.reset_index()
print(YearlyTotalSales)


print("------------------------------------")
print("Top profitable products that sold with discount")
print("---------------------------------")
year_profit_discount = SalesData [[ "Sales","Sub-Category","Year", "Discount", "Profit"]]
years = SalesData.Year.unique()
for SalesYear in years:
        year_profit = year_profit_discount.loc[year_profit_discount["Discount"] !=  0]
        year_profit = year_profit_discount.loc[year_profit_discount["Year"] == SalesYear]
        year_total_profit = year_profit.groupby(by = ["Sub-Category","Year"]).sum().sort_values(by = ["Profit", "Sales"], ascending=False)
        print("The top 10 profitable products with discount that sold the most in " + str(SalesYear) + " are: ")
        year_total_profit = year_total_profit.reset_index()
        print(year_total_profit.head(10))

print("---------------------------------")
year_profit_discount = SalesData [["Year", "Sub-Category", "Sales", "Discount", "Profit"]]
years = SalesData.Year.unique()
for SalesYear in years:
    year_profit = year_profit_discount.loc[year_profit_discount["Discount"] !=  0]
    year_profit = year_profit_discount.loc[year_profit_discount["Year"] == SalesYear]
    year_total_profit = year_profit.groupby(by = ["Sub-Category", "Year"]).sum().sort_values(by = "Profit", ascending = True)
    year_total_profit = year_profit.groupby(by = ["Sub-Category", "Year"]).sum().sort_values(by = "Sales")
    print("The top 10 profitable products with discount that sold the least in " + str(SalesYear) + " are: ")
    year_total_profit = year_total_profit.reset_index()
    print(year_total_profit.tail(10))



print("Least profitable products that sold Without Discount")
print("---------------------------------")
year_profit_wdiscount = SalesData [["Year", "Sub-Category", "Sales", "Discount", "Profit"]]
years = SalesData.Year.unique()
for SalesYear in years:
    year_profit = year_profit_discount.loc[year_profit_discount["Discount"] ==  0]
    year_profit = year_profit_wdiscount.loc[year_profit_wdiscount["Year"] == SalesYear]
    year_total_profit = year_profit.groupby(by = ["Sub-Category", "Year"]).sum().sort_values(by = ["Profit", "Sales"], ascending = True)
    print("The least 10 profitable products without discount that sold the most in " + str(SalesYear) + " are: ")
    year_total_profit = year_total_profit.reset_index()
    print(year_total_profit.head(10))

print("---------------------------------")
year_profit_wdiscount = SalesData [["Year", "Sub-Category", "Sales", "Discount", "Profit"]]
years = SalesData.Year.unique()
for SalesYear in years:
    year_profit = year_profit_discount.loc[year_profit_discount["Discount"] ==  0]
    year_profit = year_profit_wdiscount.loc[year_profit_wdiscount["Year"] == SalesYear]
    year_total_profit = year_profit.groupby(by = ["Sub-Category", "Year"]).sum().sort_values(by = ["Profit", "Sales"], ascending = False)
    print("The least 10 profitable products without discount that sold the least in " + str(SalesYear) + " are: ")
    year_total_profit = year_total_profit.reset_index()