PrivacyScanData/BI/macro_analysis.py

import pandas as pd
from bokeh.plotting import figure, save, show,output_file, ColumnDataSource
from bokeh.models import HoverTool
import matplotlib.pyplot as plt

class DataFrameAnalysis:
    """Arms Macro-Analysis capability to a dataframe"""
    def __init__(self, frame):
        self.df = frame  # dataframe object

    def avg_discount_rate(self):
        """Calculates average discount rate of all orders."""
        # You should calculate the average and gross discount rate.
        pd.to_numeric(self.df['Discount_Amount'])
        pd.to_numeric(self.df['Order_Total_Amount'])
        total_sales_amount = self.df['Order_Total_Amount'].sum()
        total_discount_amount = self.df['Discount_Amount'].sum()
        total_discount_avg = int((total_discount_amount / (total_discount_amount+total_sales_amount))*100)
        return print(f'Customer Discount Avg: {total_discount_avg}%')


    def customer_role_breakdown(self):
        """Calculates proportion of retail/wholesale as a function of sales."""
        retail = 0
        wholesale = 0
        sum_count =int(len(self.df.index))
        sum_sales = self.df['Order_Total_Amount'].sum()
        retail_customer_count = round((len(self.df.loc[self.df['Customer_Role']=='Customer'].index)/sum_count)*100)
        wholesale_customer_count = round((len(self.df.loc[self.df['Customer_Role']=='Wholesale Customer'].index)/sum_count)*100)
        retail_sales = round((self.df['Order_Total_Amount'].loc[self.df['Customer_Role']=='Customer'].sum()/sum_sales)*100)
        wholesale_sales = round((self.df['Order_Total_Amount'].loc[self.df['Customer_Role']=='Wholesale Customer'].sum()/sum_sales)*100)
        grid = [[retail_customer_count,wholesale_customer_count],[retail_sales,wholesale_sales]]
        crb_df = pd.DataFrame(data=grid, columns=['Retail','Wholesale'], index=['Proportional Order Counts', 'Proportional Sales'])
        plt.style.use('seaborn-deep')
        fig, ax = plt.subplots(figsize=(10, 10))
        crb_df.plot.bar(title='Customer Role Breakdown', xlabel='Customer Role', ylabel='Proportion (%)',
                        cmap='winter', ax=ax)
        plt.figsave('Customer_Role_Breakdown.png')
        print(crb_df.head(3))

    def geographical_breakdown(self):
        """ Displays a scatterplot of Sales/Revenue weights for different States."""
        self.df = self.df[self.df.Country_Name_Shipping== 'United States (US)']
        counts = self.df["State_Name_Shipping"].value_counts().to_dict()
        States = list(counts.keys())
        Count = list(counts.values())
        geo = pd.DataFrame({'States': States, 'Counts': Count})
        geo_dataframe = pd.DataFrame(geo)
        geo_dataframe.insert(loc=2, column="Sales_Total", value=0)
        geo_dataframe.insert(loc=3, column="Avg_Purchase_Revenue", value=0)
        for i, row in self.df.iterrows():
            state = row.loc['State_Name_Shipping']
            total = row.loc['Order_Total_Amount']
            idx = geo_dataframe[geo_dataframe["States"] == state].index.item()
            av = int(geo_dataframe.at[idx, 'Sales_Total']) / int(geo_dataframe.at[idx, 'Counts'])
            geo_dataframe.at[idx, 'Sales_Total'] += total
            geo_dataframe.at[idx, 'Avg_Purchase_Revenue'] = av
        # data visualization
        cds = ColumnDataSource(geo_dataframe)
        cds.data.keys()
        visual = figure(tools='box_zoom, pan, reset',
                        width=700, height=700,
                        title='Geographical Sales Breakdown',
                        y_axis_label='Order Quantity', x_axis_label='Revenue')
        visual.circle('Sales_Total', 'Counts', size=7, source=cds, name= 'States')
        visual.add_tools(HoverTool(tooltips=[("State", "@States"),
                                             ("Average Purchase Revenue", "@Avg_Purchase_Revenue")
                                             ]))
        output_file('geographical_breakdown.html')
        save(visual)
        show(visual)
        return print(geo_dataframe)


class ProductAnalysis:
    """Arms product analysis capability to a dataframe"""

    def __init__(self, frame):
        self.df = frame  # dataframe object
        self.analysis_frame = self.monthly_product_frame()
        self.time_span = self.serve_time_span()  # list of tuples: x[0] == year, x[1] == month for x in self.time_span

    def monthly_product_frame(self):
        """Analyzes the order lines in the CSV_Files folder and
        Returns a pandas Dataframe with monthly product statistics."""
        from datetime import datetime
        import information_repository as ir
        frame = self.df
        frame = frame[['Order_Date', 'Product_Name', 'Quantity', 'Item_Cost']]
        dict_list = []
        for i, row in frame.iterrows():
            row_date = row['Order_Date']
            row_date = datetime.strptime(row_date, "%Y-%m-%d %H:%M")
            row_date_month = row_date.month
            row_date_year = row_date.year
            raw_products = row['Product_Name'].replace('\r', '').split('\n')
            raw_quantities = row['Quantity'].replace('\r', '').split('\n')
            raw_cost = row['Item_Cost'].replace('\r', '').split('\n')
            for key in range(len(raw_products)):
                product = [i for i in ir.p_list if i in raw_products[key]][0]
                quantity = int(raw_quantities[key])
                revenue = float(raw_cost[key])
                dict_object = [product, quantity, revenue, row_date_month, row_date_year]
                matched_dictionary = [i for i in dict_list if
                                      i['name'] == dict_object[0] and i['month'] == dict_object[3]
                                      and i['year'] == dict_object[4]]
                if len(matched_dictionary) == 1:
                    matched_dictionary[0]['count'] += dict_object[1]
                    matched_dictionary[0]['revenue'] += dict_object[2]
                else:
                    dict_list.append({'name': dict_object[0], 'count': dict_object[1],
                                      'revenue': dict_object[2], 'month': dict_object[3], 'year': dict_object[4]})
        self.analysis_frame = pd.DataFrame(columns=['year', 'month', 'count', 'revenue', 'change_over_month', 'product'])
        time_span = []
        for product in ir.p_list:
            product_dictionaries = sorted(
                sorted([i for i in dict_list if i['name'] == product], key=lambda x: x['month']
                       ), key=lambda x: x['year'])
            data_list = []
            year_list = []
            month_list = []
            for key in range(len(product_dictionaries)):
                if key > 0:
                    try:
                        change_over_month = (100 - round(
                            ((product_dictionaries[key]['revenue'] / product_dictionaries[key]['count'])
                             / (product_dictionaries[key - 1]['revenue'] / product_dictionaries[key - 1][
                                        'count'])) * 100))

                    except IndexError:
                        print('change_list calls need to be refined')
                else:
                    change_over_month = 0

                row_list = [product_dictionaries[key]['year'], product_dictionaries[key]['month'],
                            product_dictionaries[key]['count'], product_dictionaries[key]['revenue'], change_over_month,
                            product_dictionaries[key]['name']]
                data_list.append(row_list)
                if product == 'Blue Moon':
                    month_list.append(product_dictionaries[key]['month'])
                    year_list.append(product_dictionaries[key]['year'])

            if product == 'Blue Moon':
                time_span = [*zip(year_list, month_list)]
            append_frame = pd.DataFrame(data=data_list,
                                        columns=['year', 'month', 'count', 'revenue', 'change_over_month', 'product'])
            self.analysis_frame = pd.concat([self.analysis_frame, append_frame], ignore_index=True)
        self.time_span = time_span
        return self.analysis_frame

    def highest_positive_product_change_over_month_analysis(self):
        """Analyzes the monthly_product_frame and returns the 5 products whose sales level increased the most"""
        year = int(input('Type the year you would like to query in yyyy format:  '))
        month = int(input('Type the month you would like to query:  '))
        data_slice = self.analysis_frame.loc[self.analysis_frame['month'] == month].loc[self.analysis_frame['year'] == year].loc[self.analysis_frame['revenue']>500]
        data_slice.sort_values(by='change_over_month', inplace=True, ascending=False)
        return print(data_slice.head(5))

    def highest_negative_product_change_over_month_analysis(self):
        """Analyzes the monthly_product_frame and returns the 5 products whose sales level decreased the most"""
        year = int(input('Type the year you would like to query in yyyy format:  '))
        month = int(input('Type the month you would like to query:  '))
        data_slice = self.analysis_frame.loc[self.analysis_frame['month'] == month].loc[self.analysis_frame['year'] == year].loc[self.analysis_frame['revenue']>500]
        data_slice.sort_values(by='change_over_month', inplace=True, ascending=True)
        return data_slice

    def product_line_change_over_month_analysis(self, year, month):
        """Analyzes the monthly_product_frame by product line and returns a dataframe with
        product line change over month data."""
        import information_repository as ir
        #year = int(input('Type the year you would like to query in yyyy format:  '))
        #month = int(input('Type the month you would like to query:  '))
        product_line_list_of_lists = [ir.tea_product_list, ir.capsule_product_list, ir.smokeable_product_list,
                             ir.skincare_product_list, ir.superfood_product_list, ir.honey_product_list,
                             ir.tincture_product_list]
        product_line_strings = ['Tea', 'Capsules', 'Smokeables', 'Skincare', 'Superfood', 'Honey', 'Tinctures']
        product_line_append_list = []
        line_index_counter = 0
        for product_line in product_line_list_of_lists:
            line_list = []
            line_list.append(year)
            line_list.append(month)
            data_slice = self.analysis_frame.loc[self.analysis_frame['month'] == month].loc[
                self.analysis_frame['year'] == year].loc[self.analysis_frame['product'].isin(product_line)]
            if month > 1:
                last_month_frame = self.analysis_frame.loc[self.analysis_frame['month'] == (month - 1)].loc[
                    self.analysis_frame['year'] == year].loc[self.analysis_frame['product'].isin(product_line)]
            else:
                last_month_frame = self.analysis_frame.loc[self.analysis_frame['month'] == 12].loc[
                    self.analysis_frame['year'] == (year - 1)].loc[self.analysis_frame['product'].isin(product_line)]
            last_month_revenue = last_month_frame['revenue'].sum()
            this_month_revenue = data_slice['revenue'].sum()
            avg_change_over_month = (this_month_revenue / last_month_revenue) * 100
            line_list.append(avg_change_over_month)
            product_line = product_line_strings[line_index_counter]
            line_index_counter += 1
            line_list.append(product_line)
            product_line_append_list.append(line_list)
        product_line_analysis_frame = pd.DataFrame(data=product_line_append_list,
                                                   columns=['year', 'month', 'avg_change_over_month',
                                                            'product_line'])
        product_line_analysis_frame.to_csv('product_line_csv_2021.csv')
        return product_line_analysis_frame

    def serve_time_span(self):
        """Returns a list of tuples of unique (year, month) pairs in chronological order based on the
         monthly_product_frame."""
        return sorted(sorted(list(set([*zip(self.analysis_frame['year'],self.analysis_frame['month'])])),
                            key=lambda x:x[1]), key=lambda x:x[0])

    def product_line_change_over_month_graph(self):
        """Using the product_line_change_over_month_analysis frame, it outputs a graph of the changes over time for
        the top product lines."""
        line_change_frame_data = []
        for i in self.time_span:
            month_frame = self.product_line_change_over_month_analysis(i[0], i[1])
            change_list = month_frame['avg_change_over_month']
            line_change_frame_data.append(change_list)
        treated_line_change_frame_data = []
        for i in range(len(line_change_frame_data)): #index of time period/segment
            if i ==0:
                treated_line_change_frame_data.append([self.time_span[i][0], self.time_span[i][1],
                                                       0,0,0,0,0,0,0]) #insert base amounts for the first month
            else: #function as intended
                month_cumulative_change_list = []
                month_cumulative_change_list.append(self.time_span[i][0])
                month_cumulative_change_list.append(self.time_span[i][1])# append year and month
                for x in range(len(line_change_frame_data[0])):
                    prior_change_list = [i[x] for i in line_change_frame_data]
                    product_cumulative_change = (100+treated_line_change_frame_data[i-1][x+2]) * ((prior_change_list[i]/100))-100
                    #i-1 for previous time period and x+2 for offset due to year and month category
                    month_cumulative_change_list.append(product_cumulative_change)
                treated_line_change_frame_data.append(month_cumulative_change_list)
        graph_frame = pd.DataFrame(data=treated_line_change_frame_data, columns=['Year', 'Month', 'Tea', 'Capsules', 'Smokeables','Skincare',
                                                                           'Superfood', 'Honey', 'Tinctures'])
        print(graph_frame.head(7))
        x = [str(i) for i in graph_frame['Month']]
        y1 = graph_frame['Tea']
        y2 = graph_frame['Capsules']
        y3 = graph_frame['Superfood']
        y4 = graph_frame['Honey']
        y5 = graph_frame['Smokeables']
        graph = figure(x_range=x,title='Cumulative Percentage Change of Product Lines',x_axis_label='Month', y_axis_label='Percentage Change')
        graph.line(x, y1, legend_label ='Tea', color='red', line_width=3)
        graph.line(x, y2, legend_label ='Capsules', color='blue', line_width=3)
        graph.line(x, y3, legend_label ='Superfood', color='orange', line_width=3)
        graph.line(x, y4, legend_label ='Honey', color='yellow', line_width=3)
        graph.line(x, y5, legend_label ='Smokeables', color='green', line_width=3)
        output_file('product_line_change_over_month.html')
        save(graph)
        return show(graph)


class InventoryPredictor:
    """Inventory volume prediction using a product sales csv as the raw data."""
    def __init__(self):
        import information_repository as ir
        self.unit_counts = self.sales_unit_count_dictionaries()
        self.ingredients = self.ingredient_dictionary()
        self.recipes = ir.unit_recipes

        print('initiating')
        pass

    def sales_unit_count_dictionaries(self):
        """Creates a set of dictionaries for each product and the cumulative quantity of units across all SKUs."""
        import information_repository as ir
        product_sales_frame = pd.read_csv('product_sales.csv')
        product_sales_frame = product_sales_frame.where(pd.notnull(product_sales_frame), 'None')
        product_unit_amounts = []
        for i in ir.p_list:
            product_dict = dict(name=i, quantity=0)
            for x, row in product_sales_frame.iterrows():
                if i in row['Product Name']:
                    if i in ir.tea_product_list:
                        if '1' in row['Variation Attributes']:
                            product_dict['quantity'] += row['Quantity Sold']
                        elif '3' in row['Variation Attributes']:
                            product_dict['quantity'] += row['Quantity Sold'] * 3
                        elif '20' in row['Variation Attributes']:
                            product_dict['quantity'] += row['Quantity Sold'] * 20
                        else:
                            pass
                            # print('Something unexpected occured', row['Product Name'], row['Variation Attributes'])
                    elif i in ir.superfood_product_list:
                        if '3' in row['Variation Attributes']:
                            product_dict['quantity'] += row['Quantity Sold']
                        elif '9' in row['Variation Attributes']:
                            product_dict['quantity'] += row['Quantity Sold'] * 3
                        else:
                            product_dict['quantity'] += 1
                    elif i in ir.capsule_product_list:
                        if '1' in row['Variation Attributes']:
                            product_dict['quantity'] += row['Quantity Sold']
                        if '4' in row['Variation Attributes']:
                            product_dict['quantity'] += row['Quantity Sold'] * 4
                    elif i in ir.smokeable_product_list:
                        if '7' in row['Variation Attributes']:
                            product_dict['quantity'] += row['Quantity Sold'] * 7
                        elif 'prerolls' in row['Variation Attributes']:
                            product_dict['quantity'] += row['Quantity Sold'] * 2
                        else:
                            product_dict['quantity'] += row['Quantity Sold'] * 4
                    elif i in ir.honey_product_list:
                        if '3' in row['Variation Attributes']:
                            product_dict['quantity'] += row['Quantity Sold'] * 3
                        elif '5' in row['Variation Attributes']:
                            product_dict['quantity'] += row['Quantity Sold'] * 5
                        elif '2' in row['Variation Attributes']:
                            pass
                            # print('Reminder that packet honeys and jars need to separate')
                    else:
                        product_dict['quantity'] += row['Quantity Sold']
            product_unit_amounts.append(product_dict)
        return product_unit_amounts

    def ingredient_dictionary(self):
        """Creates a ingredient dictionary with all ingredients as keys and the cumulative volume across all
        products as values."""
        inventory = pd.read_csv('craftybase-export-material.csv')
        ingredient_dictionary = {}
        for i in list(inventory['name']):
            ingredient_dictionary[i]=0
        return ingredient_dictionary

    def ingredient_volume_table(self):
        """Creates a csv with ingredients and the cumulative volume used across a time span."""
        for x in self.unit_counts:
            for y in self.recipes:
                if x['name'] == y['name']:
                    for k, v in y.items():
                        if k != 'name':
                            self.ingredients[k] += v * x['quantity']
        sorted_ingredient_volumes = sorted(self.ingredients.items(), key=lambda x: x[1], reverse=True)
        output_frame = pd.DataFrame(data = sorted_ingredient_volumes, columns= ['Ingredient', 'Volume (gram or oz)'])
        output_frame = output_frame[output_frame['Volume (gram or oz)'] !=0]
        output_frame.to_csv('ingredient_volume_table.csv')