#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Created on Wed Nov 13 20:09:52 2019

@author: cameronlee
"""

import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
xl = pd.ExcelFile("SalesData.xlsx")
SalesData = xl.parse("Orders")

#CUSTOMER SEGMENT PURCHASE FREQUENCY
MostFrequent = SalesData["Segment"].value_counts()
print(MostFrequent)

#TOP 10 CUSTOMERS BY SEGMENT FOR PROFITABLE PURCHASES
print("---------------------------------")
segment_profit_Cus = SalesData [["Segment", "Profit", "Customer Name",]]
Segments = SalesData.Segment.unique()

for Segment in Segments:
    segment_profit = segment_profit_Cus.loc[segment_profit_Cus["Segment"] == Segment]
    segment_total_profit = segment_profit.groupby(by = "Customer Name").sum().sort_values(by = "Profit", ascending = False)
    semgnt_total_profit = segment_total_profit.reset_index()
    print("The top 10 Customers with the most profitable purchases in " + Segment + " are: ")
    print(segment_total_profit.head(10))

#TOP 10 CUSTOMERS BY SEGMENTS WITH DISCOUNTS
print("---------------------------------")
segment_discount_Cus = SalesData [["Segment", "Discount", "Customer Name", "Profit"]]
Segments = SalesData.Segment.unique()

for Segment in Segments:
    segment_discount = segment_discount_Cus.loc[segment_discount_Cus["Segment"] == Segment]
    segment_total_discount = segment_discount.groupby(by = "Customer Name").sum().sort_values(by = "Discount" and "Profit", ascending = False)
    segment_total_discount = segment_total_discount.reset_index()
    print("The top 10 Customers purchasing with Discount in  " + Segment + " are: ")
    print(segment_total_discount.head(10))

#TOP 10 CUSTOMERS BY SEGMENTS WITHOUT DISCOUNTS
print("---------------------------------")
segment_no_Cus = SalesData [["Segment", "Discount", "Customer Name", "Profit"]]
segment_no_Cus_no_discount = segment_no_Cus.loc[segment_no_Cus["Discount"] == 0]

Segments = SalesData.Segment.unique()

for Segment in Segments:
    segment_no =segment_no_Cus_no_discount.loc[segment_no_Cus_no_discount["Segment"] == Segment]
    segment_total_no = segment_no.groupby(by = "Customer Name").sum().sort_values(by = "Profit")
    segment_total_no = segment_total_no.reset_index()
    print("The bottom 10 Customers purchasing without discount in " + " are: ")
    print(segment_total_no.head(10))


#BOTTOM 10 CUSTOMERS BY SEGMENTS WITH DISCOUNTS
print("---------------------------------")
segment_discount_Cus = SalesData [["Segment", "Discount", "Customer Name", "Profit"]]
Segments = SalesData.Segment.unique()

for Segment in Segments:
    segment_discount = segment_discount_Cus.loc[segment_discount_Cus["Segment"] == Segment]
    segment_total_discount = segment_discount.groupby(by = "Customer Name").sum().sort_values(by = "Discount" and "Profit", ascending = False)
    segment_total_discount = segment_total_discount.reset_index()
    print("The bottom 10 Customers purchasing with Discount in  " + Segment + " are: ")
    print(segment_total_discount.tail(10))

##BOTTOM 10 CUSTOMERS BY SEGMENTS WITHOUT DISCOUNTS
print("---------------------------------")
segment_no_Cus = SalesData [["Segment", "Discount", "Customer Name", "Profit"]]
segment_no_Cus_no_discount = segment_no_Cus.loc[segment_no_Cus["Discount"] == 0]

Segments = SalesData.Segment.unique()

for Segment in Segments:
    segment_no =segment_no_Cus_no_discount.loc[segment_no_Cus_no_discount["Segment"] == Segment]
    segment_total_no = segment_no.groupby(by = "Customer Name").sum().sort_values(by = "Profit")
    segment_total_no = segment_total_no.reset_index()
    print("The bottom 10 Customers purchasing without discount in " + " are: ")
    print(segment_total_no.tail(10))