Big Data analytics coursework 2

Greig Dighton

H00296387

Introduction

This report will aim to gain a further knowledge of the Superstore dataset with the goal of understanding how to increase their profits. This task will be completed by combing through the data to understand what is working well and what is not performing to the same standards. By doing so we will be able to identify areas that are in need of improvement when it comes to profitability. Once the issues have been identified we will discuss ways in which adjustments can be made, so that the company is able to become more profitable.

The findings of this report will also be shown in graphical form to allow for an easier viewing of our findings. This will allow us to visually show where the provided company has been losing out on profits and allow us to make suggestions as to what areas of the business they should be aiming to improve in.

Importing data

First and foremost we must upload the dataset to view any form of data. I used the head function to show off the first 5 rows of the dataset to ensure that this had worked properly and that I could see the data was there. Once this had been done I created a summary of the full data set in order to check whether all the features that are expected are correct in accordance with the companies specifications that were provided. I was soon able to check this off through the use of the describe function that listed the features and the numbers within each feature that lay within the dataset. This then allowed me to look at individual features to check what lay within them, and whether this was accurate with what the company provided with us. I first checked the categories feature and soon discovered that the company has 3 separate categories, Office Supplies, furniture, and technology, this fits in line with the specification as we were expecting to find 3 separate categories. I then proceeded to check the sub categories section to understand what lay within it and to check that there was the correct amount as per the specification. This turned out to be correct. I also made sure to look out for any outlier sub categories that may not belong, but they were all satisfactory. Once this was done I then proceeded to check the state category, checking there were no invalid states, none were misspelled, or there was a wrong amount. This all checked out well with 49 correct states, which sits in line with the specifications.

After I was happy that my dataset categories were up to standard and correct in accordance to the specification I began to look for outliers that lay within the data itself. This is where I was able to pick up on a few pieces of data that had appeared after using the describe function on the dataset. What I had found is that there were a few numbers that lay within categories that would skew the data should I choose to leave them in when conducting my analysis. This is why I made the decision to remove these few specific outliers. The first outlier I removed was for any sales number that was below 1. From viewing my describe of the dataset I picked up on this when I looked at the minimum value for sales. Since it is not possible to sell less than 1 of an item I decided to remove any sales from the dataset that were below 1, keeping everything else that was above 1. Furthermore, the second outlier I removed was in the discount column, as I had become aware that in the max row for discount there was a value of 1.3. I made the decision that this would be best suited for analysing the data and it may cause skewed results. I did however take note that this is something the company should be looking at. To remove this outlier I set out to keep only discount amount between 0-1, this then removed the outlying data from the dataset. This then allowed me to create a new cleaned dataset to use for the rest of my analysis.

import pandas as pd SS = pd.read_csv('dataset_Superstore (2).csv') # Importing the dataset

SS.head(5) # Checking dataset has imported by looking at first 5 orders

SS.info() # Looking it information within the dataset

SS.describe()

pd.unique(SS["Category"]) # Checking category information

pd.unique(SS["Sub-Category"]) # Checking sub-category information

pd.unique(SS["Segment"]) # Checking Segment information

pd.unique(SS["State"]) # Checking State information

pd.unique(SS["Customer_no"]) # Checking Customer number information

CleanSales = SS[SS['Sales']>=1] # Cleaning to remove any sales below 1 CleanSales.describe()

CleanSS = CleanSales[(CleanSales['Discount'] >= 0) & (CleanSales['Discount'] <= 1)] # Cleaning to remove discount above 1 CleanSS.describe()

Identifying weak data

When identifying what data we want to look at from the data set we had to identify whether there were any unusual values that we do not want to include within the data analysis. To do this we had to look at whether the gaps between the 3rd quartile and the max value is much larger than the gap between the median and the 3rd quartile. If this was the case then the max is unusual and we may look to remove the values from the analysis. This method was also used to see whether the gaps between the 1st quartile and the min value is much larger than the gap between the 1st quartile and the median then the minimum value is unusual. In the case of the data set this method was used to investigate whether any of Sales, Discount, Quantity, and Profit.

When looking at what the data provided there were obvious values that appeared within all aforementioned categories. When looking at the number of values that were labelled unusual, the number came out to be larger than expected. For this reason I made the decision to leave this data in, as in context to this project I believe that the data that appears 'unusual' may be a cause for the lack of profitability. For this reason I decided to investigate these values more closely.

Firstly I investigated all order that were making a negative profits in the hope that there would be some form of pattern that would become apparent. I thought it would be best to work through the lowest performing orders first, and when I did so I stumbled upon some insights that were leading to the bad performance of these orders. The superstore has been giving out some extremely large discounts on some of their orders, with the worst negative profit order being given a 70% discount. This has led to this order making a -6599 dollar loss, and this pattern has been repeated with the majority of the remaining negative profit orders being given very large discounts. These discounts are also not justified through the customers buying in bulk, as the largest purchase quantity for any of the negative profit orders I could find was 13. It has essentially become apparent that the superstore has been giving out discounts far above what they can afford to be handing out. I investigated this issue further by creating a scatter graph of all the superstores orders for profit and the discount given on the order. The graph very clearly shows that all orders that are given 0 to very little discount are profitable for the superstore, and that when the discount amount is increased the profits drop significantly. There is an area on the scatter graph just after the 40% discount where it becomes apparent that orders are no longer profitable and they are now begining to make losses. At this time I decided to run a linear regression model to fully understand the impact of what is happening between the relationship between profit and discount. Once the linear regression was run there were very clear results showing that the expected value of the intercept was 67.7 when all independent variables are zero, essentially meaning that when there is no discount, the superstore is expected to make around 67.7 dollars in profit. Although the coefficient (in this case the discount) shows a value of -250.36, meaning that with every increase in the units of discount the superstore are expected to lose 250.36 dollars. This has a very negative impact for the business due to the size of the loss with each unit of increase of discount. This ultimately reiterates how the superstore needs to revise their discounts that they are giving out on their products as it is having quite a severe impact on the profitability of their business.

Through a further investigation into the data I was also able to find an anomaly when it came to the largest orders by quantity. The data showed that there were 3 orders which have sold a quantity of 10000 each, this is extremely far away from the next closest large quantity sale of 14. I decided it would be beneficial to look at these 3 individual orders more closely, and I was able to identify the items as: Hunt BOSTON Model 1606 High-Volume Electric Pencil Sharpener, Beige, Laser & Ink Jet Business Envelopes, and Ultra door pull handle. Considering the large amount of quantity of each of these products being sold I expected to see a large amount generated by sales. This although is not the case, which makes me believe that there has been an error from the superstore with their pricing. For 30,000 items sold combined between the 3 orders to only make a revenue of just 1202 it is evident that these items are anomalies, and need fixed asap. The error in pricing may have resulted in customers taking advantage of the mistake, leading to the high quantity sold within the order.

selected_column = 'Sales' # Calculating descriptive statistics median_value = CleanSS[selected_column].median() # Calculating the median value of the selected column min_value = CleanSS[selected_column].min() # Calculating the minimum value of the selected column max_value = CleanSS[selected_column].max() # Calculating the maximum value of the selected column q1_value = CleanSS[selected_column].quantile(0.25) # Calculating the 1st quartile (25th percentile) of the selected column q3_value = CleanSS[selected_column].quantile(0.75) # Calculating the 3rd quartile (75th percentile) of the selected column # Calculating the gaps between different quartiles and extreme values gap_q1_min = q1_value - min_value # Calculating the gap between the 1st quartile (Q1) and the minimum value gap_median_q1 = median_value - q1_value # Calculating the gap between the median and the 1st quartile (Q1) gap_max_q3 = max_value - q3_value # Calculating the gap between the maximum value and the 3rd quartile (Q3) gap_q3_median = q3_value - median_value # Calculating the gap between the 3rd quartile (Q3) and the median # Printing the descriptive statistics and gaps print(f"Median: {median_value}") # Printing the median value print(f"Minimum: {min_value}") # Printing the minimum value print(f"Maximum: {max_value}") # Printing the maximum value print(f"1st Quartile (Q1): {q1_value}") # Printing the 1st quartile (Q1) value print(f"3rd Quartile (Q3): {q3_value}") # Printing the 3rd quartile (Q3) value # Printing the gaps between quartiles and extreme values print(f"Gap between 1st Quartile (Q1) and min value: {gap_q1_min}") # Printing the gap between Q1 and the minimum value print(f"Gap between Median and 1st Quartile(Q1) value: {gap_median_q1}") # Printing the gap between the median and Q1 print(f"Gap between 3rd Quartile (Q3) and Median: {gap_q3_median}") # Printing the gap between Q3 and the median print(f"Gap between Max and 3rd Quartile (Q3): {gap_max_q3}") # Printing the gap between the maximum value and Q3

selected_column = 'Discount' # Calculating descriptive statistics median_value = CleanSS[selected_column].median() # Calculating the median value of the selected column min_value = CleanSS[selected_column].min() # Calculating the minimum value of the selected column max_value = CleanSS[selected_column].max() # Calculating the maximum value of the selected column q1_value = CleanSS[selected_column].quantile(0.25) # Calculating the 1st quartile (25th percentile) of the selected column q3_value = CleanSS[selected_column].quantile(0.75) # Calculating the 3rd quartile (75th percentile) of the selected column # Calculating the gaps between different quartiles and extreme values gap_q1_min = q1_value - min_value # Calculating the gap between the 1st quartile (Q1) and the minimum value gap_median_q1 = median_value - q1_value # Calculating the gap between the median and the 1st quartile (Q1) gap_max_q3 = max_value - q3_value # Calculating the gap between the maximum value and the 3rd quartile (Q3) gap_q3_median = q3_value - median_value # Calculating the gap between the 3rd quartile (Q3) and the median # Printing the descriptive statistics and gaps print(f"Median: {median_value}") # Printing the median value print(f"Minimum: {min_value}") # Printing the minimum value print(f"Maximum: {max_value}") # Printing the maximum value print(f"1st Quartile (Q1): {q1_value}") # Printing the 1st quartile (Q1) value print(f"3rd Quartile (Q3): {q3_value}") # Printing the 3rd quartile (Q3) value # Printing the gaps between quartiles and extreme values print(f"Gap between 1st Quartile (Q1) and min value: {gap_q1_min}") # Printing the gap between Q1 and the minimum value print(f"Gap between Median and 1st Quartile(Q1) value: {gap_median_q1}") # Printing the gap between the median and Q1 print(f"Gap between 3rd Quartile (Q3) and Median: {gap_q3_median}") # Printing the gap between Q3 and the median print(f"Gap between Max and 3rd Quartile (Q3): {gap_max_q3}") # Printing the gap between the maximum value and Q3

selected_column = 'Quantity' # Calculating descriptive statistics median_value = CleanSS[selected_column].median() # Calculating the median value of the selected column min_value = CleanSS[selected_column].min() # Calculating the minimum value of the selected column max_value = CleanSS[selected_column].max() # Calculating the maximum value of the selected column q1_value = CleanSS[selected_column].quantile(0.25) # Calculating the 1st quartile (25th percentile) of the selected column q3_value = CleanSS[selected_column].quantile(0.75) # Calculating the 3rd quartile (75th percentile) of the selected column # Calculating the gaps between different quartiles and extreme values gap_q1_min = q1_value - min_value # Calculating the gap between the 1st quartile (Q1) and the minimum value gap_median_q1 = median_value - q1_value # Calculating the gap between the median and the 1st quartile (Q1) gap_max_q3 = max_value - q3_value # Calculating the gap between the maximum value and the 3rd quartile (Q3) gap_q3_median = q3_value - median_value # Calculating the gap between the 3rd quartile (Q3) and the median # Printing the descriptive statistics and gaps print(f"Median: {median_value}") # Printing the median value print(f"Minimum: {min_value}") # Printing the minimum value print(f"Maximum: {max_value}") # Printing the maximum value print(f"1st Quartile (Q1): {q1_value}") # Printing the 1st quartile (Q1) value print(f"3rd Quartile (Q3): {q3_value}") # Printing the 3rd quartile (Q3) value # Printing the gaps between quartiles and extreme values print(f"Gap between 1st Quartile (Q1) and min value: {gap_q1_min}") # Printing the gap between Q1 and the minimum value print(f"Gap between Median and 1st Quartile(Q1) value: {gap_median_q1}") # Printing the gap between the median and Q1 print(f"Gap between 3rd Quartile (Q3) and Median: {gap_q3_median}") # Printing the gap between Q3 and the median print(f"Gap between Max and 3rd Quartile (Q3): {gap_max_q3}") # Printing the gap between the maximum value and Q3

selected_column = 'Profit' # Calculating descriptive statistics median_value = CleanSS[selected_column].median() # Calculating the median value of the selected column min_value = CleanSS[selected_column].min() # Calculating the minimum value of the selected column max_value = CleanSS[selected_column].max() # Calculating the maximum value of the selected column q1_value = CleanSS[selected_column].quantile(0.25) # Calculating the 1st quartile (25th percentile) of the selected column q3_value = CleanSS[selected_column].quantile(0.75) # Calculating the 3rd quartile (75th percentile) of the selected column # Calculating the gaps between different quartiles and extreme values gap_q1_min = q1_value - min_value # Calculating the gap between the 1st quartile (Q1) and the minimum value gap_median_q1 = median_value - q1_value # Calculating the gap between the median and the 1st quartile (Q1) gap_max_q3 = max_value - q3_value # Calculating the gap between the maximum value and the 3rd quartile (Q3) gap_q3_median = q3_value - median_value # Calculating the gap between the 3rd quartile (Q3) and the median # Printing the descriptive statistics and gaps print(f"Median: {median_value}") # Printing the median value print(f"Minimum: {min_value}") # Printing the minimum value print(f"Maximum: {max_value}") # Printing the maximum value print(f"1st Quartile (Q1): {q1_value}") # Printing the 1st quartile (Q1) value print(f"3rd Quartile (Q3): {q3_value}") # Printing the 3rd quartile (Q3) value # Printing the gaps between quartiles and extreme values print(f"Gap between 1st Quartile (Q1) and min value: {gap_q1_min}") # Printing the gap between Q1 and the minimum value print(f"Gap between Median and 1st Quartile(Q1) value: {gap_median_q1}") # Printing the gap between the median and Q1 print(f"Gap between 3rd Quartile (Q3) and Median: {gap_q3_median}") # Printing the gap between Q3 and the median print(f"Gap between Max and 3rd Quartile (Q3): {gap_max_q3}") # Printing the gap between the maximum value and Q3

CleanSS.describe()

HighProfit = len(CleanSS.loc[CleanSS["Profit"]>=30]) print("Profit",HighProfit) HighSales = len(CleanSS.loc[CleanSS["Sales"]>=210]) print("Sales",HighSales) HighQuantity = len(CleanSS.loc[CleanSS["Quantity"]>=5]) print("Quantity",HighQuantity)

# Selecting specific columns for analysis BusinessFigures = CleanSS[["Order ID","Sales","Quantity","Discount","Profit"]] # Sorting the data by the 'Profit' column in ascending order SortedProfit = BusinessFigures.sort_values(by="Profit", ascending=True) # Displaying the first 50 rows of the sorted DataFrame print(SortedProfit.head(50))

import matplotlib.pyplot as plt # Importing the matplotlib library for creating plots import seaborn as sns # Importing the seaborn library for statistical data visualization

fig, ax = plt.subplots() # Creating a new figure and axis objects using matplotlib # Creating a scatter plot using the 'Discount' column as x-axis and the 'Profit' column as y-axis ax.scatter(CleanSS['Discount'], CleanSS['Profit']) plt.xlabel('Discount') # Setting the label for the x-axis as 'Discount' plt.ylabel('Profit') # Setting the label for the y-axis as 'Profit' plt.title("Discount vs Profit") # Setting the title of the plot as "Discount vs Profit"

!pip install statsmodels # Command to install the statsmodels library if it's not already installed import statsmodels.formula.api as smf # Importing the statsmodels library for statistical modeling and analysis

reg = smf.ols(formula='Profit ~ Discount', data = CleanSS) results = reg.fit() results.params

# Selecting specific columns for analysis BusinessFigures = CleanSS[["Order ID", "Sales", "Quantity", "Discount", "Profit"]] # Sorting the data by the 'Quantity' column in descending order SortedQuantity = BusinessFigures.sort_values(by="Quantity", ascending=False) # Displaying the top 20 rows of the sorted DataFrame print(SortedQuantity.head(20))

# Selecting specific columns for analysis BusinessFigures = CleanSS[["Order ID", "Product Name", "Category", "Sales", "Quantity", "Discount", "Profit"]] # Filtering the data to include only rows where Quantity is greater than or equal to 15 HighQuantity = BusinessFigures.loc[CleanSS["Quantity"] >= 15] # Printing the DataFrame containing rows with high quantities print("High Quantities", HighQuantity)

# Selecting specific columns for analysis BusinessFigures = CleanSS[["Order ID", "Sales", "Quantity", "Discount", "Profit"]] # Sorting the data by the 'Sales' column in descending order SortedSales = BusinessFigures.sort_values(by="Sales", ascending=False) # Displaying the top 20 rows of the sorted DataFrame print(SortedSales.head(20))

Data analysis

To further understand the superstores problems and figure out ways to increase their profits we need to look at some more in depth data. For this reason I have produced a number of graphs that will allow for further data analysis to be conducted. Firstly I created a graph showing the total number of profitabale sales by category to understand which category is performing the best by profitability. To do this I compared two different graphs, one showing the profit by category, and the other showing negative profit (loss) by category in order to understand where work needs to be done. After analysing both graphs it is apparent that the furniture category is struggling to produce as much profit for the superstore as the other two main categories. From interpreting the graphs it is apparent that furniture is only making around 15,000 in profit, in comparison to technology which is making around 140,000 in profit, and office supplies which is making around 120,000 in profit. So now it is clear for the superstore to see that their furniture products are their least profitable. In order to understand why furniture is performing as bad as it is I created another graph to show the average discount on each category, to which I found that furniture had the highest average discount on products. So for the superstore to increase their profits, they need to look at reducing the discounts that they offer on furniture items, as it is costing themselves by handing out higher discounts.

Furthermore, I wanted to gain an understanding of what regions were performing best. For this I created two graphs again, one of profitable sales by region, the second of non-profitable sales by region. This way I will be able to understand if there is a region that needs work done for increasing their profits. By comparing the graphs it is evident that the central region will need to most work done for increasing profits as they have the second lowest number of profitable sales, and the most non-profitable sales by far. To further understand why this issue is as bad as it is I created another average discount graph, and the results showed that the average discount being offered in the central region for each of the 3 categories is significantly higher. So for the central region, and the superstore as a whole to increase profits, the amount of discounts offered need to be reduced significantly otherwise profits will not increase

The final graph produced shows the amount the superstore is losing due to returned items. This graph shows the amount of profit that is being taken away due to the item being returned back to the store. Items within the technology category seem to be the biggest issue with the superstore losing nearly 14,000 dollars in their profits due to their returned items. For this reason the superstore needs to try harder to ensure that all their products are up to the high standard promised, as this should hopefully reduce the number of returned items. With a lower amount of returned items the superstore should be looking at increased profits, as they are able to keep more of the profits, without returning the money to customers

# Grouping the DataFrame by 'Category' and calculating the sum of profits for each category CatProfSum = CleanSS.groupby('Category')['Profit'].sum() # Printing the sum of profits for each category print(CatProfSum)

# Grouping the DataFrame by 'Category' and calculating the sum of sales for each category CatSalesSum = CleanSS.groupby('Category')['Sales'].sum() # Printing the sum of sales for each category print(CatSalesSum)

# Grouping the DataFrame by 'Category' and calculating the sum of discounts for each category CatDiscountSum = CleanSS.groupby('Category')['Discount'].sum() # Printing the sum of discounts for each category print(CatDiscountSum)

# Selecting specific columns for visualization GraphData = CleanSS[["State", "Region", "Category", "Sub-Category", "Sales", "Quantity", "Discount", "Profit"]] # Printing the selected columns for visualization print(GraphData)

# Filtering the data to include only rows where Profit is greater than or equal to 0 PosProfit = GraphData[(GraphData['Profit'] >= 0)] # Printing the first 10 rows of the filtered DataFrame print(PosProfit.head(10))

# Filtering the data to include only rows where Profit is less than 0 NegProfit = GraphData[(GraphData['Profit'] < 0)] # Printing the first 10 rows of the filtered DataFrame print(NegProfit.head(10))

# Filtering the data to include only rows where the Region is 'Central' CentralRegion = CleanSS[CleanSS['Region'] == 'Central'] # Printing the first 10 rows of the filtered DataFrame print(CentralRegion.head(10))

# Grouping the DataFrame containing positive profit by 'Category' without sorting and without setting 'Category' as index categories = PosProfit.groupby('Category', sort=False, as_index=False) # Calculating the sum of profits for each category categorySum = categories['Profit'].sum() # Creating a bar plot to visualize profit by category plt.bar(categorySum['Category'], categorySum['Profit']) # Setting labels and title for the plot plt.xlabel('Category') # Label for x-axis plt.ylabel('Profit') # Label for y-axis plt.title('Profit by Category') # Title of the plot # Rotating x-axis labels for better readability plt.xticks(rotation=45) # Displaying the plot plt.show()

# Grouping the DataFrame containing negative profit by 'Category' without sorting and without setting 'Category' as index categoriesNeg = NegProfit.groupby('Category', sort=False, as_index=False) # Calculating the sum of profits (losses) for each category with negative profit categoryNegSum = categoriesNeg['Profit'].sum() # Creating a bar plot to visualize negative profit (loss) by category plt.bar(categoryNegSum['Category'], categoryNegSum['Profit']) # Setting labels and title for the plot plt.xlabel('Category') # Label for x-axis plt.ylabel('Profit') # Label for y-axis plt.title('Negative Profit (Loss) by Category') # Title of the plot # Rotating x-axis labels for better readability plt.xticks(rotation=45) # Displaying the plot plt.show()

# Grouping the DataFrame by 'Category' and calculating the mean discount for each category average_discount_by_category = CleanSS.groupby('Category')['Discount'].mean() # Creating a new figure with specified size plt.figure(figsize=(10, 6)) # Creating a bar plot to visualize average discount by category average_discount_by_category.plot(kind='bar') # Setting title and labels for the plot plt.title('Average Discount by Category') plt.xlabel('Category') plt.ylabel('Average Discount') # Rotating x-axis labels for better readability plt.xticks(rotation=45, ha='right') # Adjusting layout for better appearance plt.tight_layout() # Displaying the plot plt.show()

# Grouping the DataFrame containing positive profit by 'Region' without sorting and without setting 'Region' as index RegionPos = PosProfit.groupby('Region', sort=False, as_index=False) # Counting the total number of profitable sales for each region RegionPosSum = RegionPos['Sales'].count() # Creating a bar plot to visualize the total number of profitable sales by region plt.bar(RegionPosSum['Region'], RegionPosSum['Sales']) # Setting labels and title for the plot plt.xlabel('Region') # Label for x-axis plt.ylabel('Sales') # Label for y-axis plt.title('Total Number of Profitable Sales by Region') # Title of the plot # Rotating x-axis labels for better readability plt.xticks(rotation=45) # Displaying the plot plt.show()

# Grouping the DataFrame containing negative profit by 'Region' without sorting and without setting 'Region' as index RegionNeg = NegProfit.groupby('Region', sort=False, as_index=False) # Counting the total number of non-profitable sales for each region RegionNegSum = RegionNeg['Sales'].count() # Creating a bar plot to visualize the total number of non-profitable sales by region plt.bar(RegionNegSum['Region'], RegionNegSum['Sales']) # Setting labels and title for the plot plt.xlabel('Region') # Label for x-axis plt.ylabel('Sales') # Label for y-axis plt.title('Total Number of Non-Profitable Sales by Region') # Title of the plot # Rotating x-axis labels for better readability plt.xticks(rotation=45) # Displaying the plot plt.show()

# Grouping the DataFrame containing data from the Central region by 'Category' and calculating the mean discount for each category average_discount_by_central_category = CentralRegion.groupby('Category')['Discount'].mean() # Creating a new figure with specified size plt.figure(figsize=(10, 6)) # Creating a bar plot to visualize average discount by category in the Central region average_discount_by_central_category.plot(kind='bar') # Setting title and labels for the plot plt.title('Average Discount by Category in the Central Region') plt.xlabel('Category') plt.ylabel('Average Discount') # Rotating x-axis labels for better readability plt.xticks(rotation=45, ha='right') # Adjusting layout for better appearance plt.tight_layout() # Displaying the plot plt.show()

# Grouping the DataFrame containing negative profit by 'Region' without sorting and without setting 'Region' as index RegionNeg = NegProfit.groupby('Region', sort=False, as_index=False) # Calculating the total sales for non-profitable transactions for each region RegionNegSum = RegionNeg['Sales'].sum() # Creating a bar plot to visualize the total sales for non-profitable transactions by region plt.bar(RegionNegSum['Region'], RegionNegSum['Sales']) # Setting labels and title for the plot plt.xlabel('Region') # Label for x-axis plt.ylabel('Sales') # Label for y-axis plt.title('Total Sales of Non-Profitable Transactions by Region') # Title of the plot # Rotating x-axis labels for better readability plt.xticks(rotation=45) # Displaying the plot plt.show()

# Filtering the CleanSS DataFrame to include only rows where the 'Returned' column is True Returned = CleanSS.loc[CleanSS["Returned"]==True] # Printing the first 10 rows of the filtered DataFrame print(Returned.head(10))

# Grouping the DataFrame containing returned items by 'Category' and calculating the total profit for each category CatProfitReturned = Returned.groupby('Category')['Profit'].sum() # Creating a bar plot to visualize the total profit lost due to returned items by category plt.bar(CatProfitReturned.index, CatProfitReturned.values) # Setting labels and title for the plot plt.xlabel('Category') # Label for x-axis plt.ylabel('Profit') # Label for y-axis plt.title('Profit Lost due to Items Returned by Category') # Title of the plot # Rotating x-axis labels for better readability plt.xticks(rotation=45) # Adjusting layout for better appearance plt.tight_layout() # Displaying the plot plt.show()

Summary

In summary superstore will be able to increase their profits by sticking to a few very simple objectives. First and foremost they must look at their rate of discounts allowed and come up with some form of solution that stops stores giving out discount on orders that end up creating losses for the company. There needs to be a cap on the discount, and all stores must follow this rule, as there are certain stores within regions that are significantly letting the company down in their efforts to maximise their profits. Secondly the superstore need to work on not having items returned to them, as this causes them to lose out on profit they have already made, due to this having to be returned to the customer. It would be beneficial for the superstore to run more extensive quality checks on their products, especially their technology products, in order for them to receive less returns. This will enable them to keep more of their profits, thus increasing their profit. Lastly, the superstore must look out for out of the ordinary orders, such as the orders of 10,000 quantity, as they may have made errors with their pricing. Mistakes like these can cost the business extensively in the long run if they continue to happen, so it is important that checks are undertaken regularly so that these types of orders don't persist and cost the company in terms of their profitability.