Coursework 2 - C11BD

Leticia Hurtado de Mendoza - H00434517

18/03/2023

Introduction

The following report, based on the 'Superstore.csv' dataset, aims to analyse your data to determine how you can increase your profits. In this document, an initial data import will be carried out, followed by data cleaning to identify possible errors and outliers, then an analysis of the statistical data obtained, and finally a visualisation of the data by plotting the data and carrying out a linear regression analysis.

Import the Data

import pandas as pd import matplotlib.pyplot as plt import numpy as np from sklearn.model_selection import train_test_split from sklearn.preprocessing import StandardScaler from sklearn.linear_model import LinearRegression from sklearn.metrics import mean_squared_error, r2_score, mean_absolute_error Superstore = pd.read_csv("dataset_Superstore.csv") df = Superstore

Run to view results

In this very first step, we are importing all packages that can be useful as we go deep into the analyse of this Data.

When importing the dataset, first the pandas package needs to be imported as well. Later, a table shows all the 'Superstore.csv' dataset.

Re-labeling Columns

Next step is re-labelling columns to get a cleaner and better view of all the Data, showing more specific information about different categories by using 'pd.unique()' method, particularly useful for large datasets. helps to identify unique elements within the Data and will be useful for future Data exploration, cleaning and analysis tasks.

Superstore.columns = ["Row ID","Order ID","Order Date","Ship Date","Ship Mode","Customer ID","Customer Name","Customer Underscore Number","Segment","Segment Number","Country","City","State","State Number","Postal Code","Region","Region Number","Product ID","Category","Category Number","Sub-Category","Sub-Category Number","Product Name","Product Name Number","Sales","Quantity","Discount","Profit","Return"]

Run to view results

Cleaning the Data

The 'Cleaning the Data' step includes removing data entry errors and outliers. During this stage, different techniques and methods can be applied. In this particular case, the methods to be used are, for missing values 'isnull()', to find out outliers, 'describe()', and for data entry errors, 'dtypes'. Lastly, a visual inspection will be carried out to identify possible anomalies within.

missing_values = Superstore.isnull().sum() print("Missing values per column:") print(missing_values)

Run to view results

df.dtypes

Run to view results

df.describe()

Run to view results

Superstore = Superstore.dropna() print("\nShape of the DataFrame after dropping missing values:", Superstore.shape)

Run to view results

for column in Superstore.columns: unique_values = Superstore[column].unique() print(f"\nUnique values in '{column}' column:") print(unique_values)

Run to view results

for column in Superstore.columns: if Superstore[column].dtype == 'object': Superstore[column] = Superstore[column].str.lower()

Run to view results

Now that we have got rid off all possible data entry errors, next step is to identify outliers and handling them.

numerical_columns = df.select_dtypes(include=['float64', 'int64']).columns

Run to view results

After identifying and handling outliers from relevant columns in our Dataset, we ned to verify that they have been removed correctly, and that will be done by using the IQR method. (See below)

Q1 = df['Profit'].quantile(0.25) Q3 = df['Profit'].quantile(0.75) IQR = Q3 - Q1 lower_bound = Q1 - 1.5 * IQR upper_bound = Q3 + 1.5 * IQR df = df[(df['Profit'] >= lower_bound) & (df['Profit'] <= upper_bound)] print(f"\\nSummary statistics for '{column}' column after outlier removal:") print(df['Profit'].describe())

Run to view results

Summary Statistics

Summary statistics is useful because it provides a concise overview and understanding of the dataset characteristics. It offers a quick and easy view of aspects of our data distribution, central tendency, dispersion and shape.The first thing to be done, is to print out all statistical values for all column.

summary_stats = df.describe() print(summary_stats)

Run to view results

Summary statistics for relevant columns show the dataset's central tendency and spread. The mean 'Profit' stands at $8.8, with a standard deviation of $8.25. 'Sales' display considerable variability, with a mean of $41.28 and standard deviation of $42.36. Most 'Discounts' hover around 9%, but a few orders experience none or exceptionally high discounts up to 50%.

Summary_Figures = Superstore[['Order Date', 'Sales', 'Quantity', 'Discount', 'Profit']] NewProfit = Summary_Figures.sort_values(by = 'Profit', ascending = False) print(NewProfit.head(15))

Run to view results

Summary_Figures = Superstore [['Order Date', 'Product Name', 'Category', 'Sales', 'Quantity', 'Discount']] LargeQuantities = Summary_Figures.loc[Superstore['Quantity']>= 15] print('LargeQuantities', LargeQuantities)

Run to view results

Plotting

Plotting is useful to visualise our data and understand it in an easier way. In order to be able to plot, some packages were imported at the beginning of this notebooks, such as 'matplotlib', for example.

plt.figure(figsize=(8, 6)) plt.scatter(df['Sales'], df['Profit'], color='orange', alpha=0.6) plt.title('Sales vs. Profit') plt.xlabel('Sales') plt.ylabel('Profit') plt.grid(True, linestyle='--', alpha=0.5) plt.show()

Run to view results

In this graph, a comparison of Sales v. Profit is shown, including negative values, understood as loss.

plt.figure(figsize=(8, 6)) df['Segment'].value_counts().plot(kind='bar', color='skyblue') plt.title('Distribution of Orders by Segment') plt.xlabel('Segment') plt.ylabel('Number of Orders') plt.xticks(rotation=45) plt.grid(axis='y', linestyle='--', alpha=0.7) plt.show()

Run to view results

The above graph shows who buys and how much, and it can help the business to see where to put the focus on in order to increase their profitability.

plt.figure(figsize=(8, 6)) df['Region'].value_counts().plot(kind='bar', color='lightgreen') plt.title('Distribution of Orders by Region') plt.xlabel('Region') plt.ylabel('Number of Orders') plt.xticks(rotation=45) plt.grid(axis='y', linestyle='--', alpha=0.7) plt.show()

Run to view results

This last graph shows the number of orders per region, and the most orders are made from West and East region, which can help on decision-making for different selling strategies for the company.

Modelling strategy - Linear Regression

Linear regression has been the chosen model strategy to find out what are the features that contribute to profit the most, and the outcome shows that these are 'Profit', 'Row ID', 'Quantity', 'Sub-Category Number' and 'Segment'.

It is useful especially when there is a linear relationship between the independent variables and the dependent one. It can help us because it can be used as a predictive modelling task, or also to find a trend analysis or correlation analysis.

df_encoded = pd.get_dummies(df, columns=['Segment', 'Region', 'Sub-Category'], drop_first=True) X = df_encoded.select_dtypes(include=[np.number]) y = df_encoded['Profit'] X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=42) scaler = StandardScaler() X_train_scaled = scaler.fit_transform(X_train) X_test_scaled = scaler.transform(X_test) model = LinearRegression() model.fit(X_train_scaled, y_train) y_pred = model.predict(X_test_scaled) mse = mean_squared_error(y_test, y_pred) r2 = r2_score(y_test, y_pred) mae = mean_absolute_error(y_test, y_pred) print("Model Evaluation Metrics:") print(f"Mean Squared Error (MSE): {mse}") print(f"R-squared (R2): {r2}") print(f"Mean Absolute Error (MAE): {mae}") coefficients = pd.DataFrame({'Feature': X.columns, 'Coefficient': model.coef_}) coefficients_sorted = coefficients.sort_values(by='Coefficient', ascending=False) print("\\nTop 5 features contributing to profitability:") print(coefficients_sorted.head())

Run to view results

So, based on the analysis conducted on the Supersore.csv datasheet provided, some actionable insights and strategies can be carried out by the company in order to increase its profitability. - Strategies based on segments: the analysis done reveals that the majority of orders are from Consumer insight, so to potentially increase profits, the company has to develop targeted marketing and sales strategies for the Corporate and Home Office segments, This can involve offering tailored promotions, product bundles or loyalty programs to attract these segments. - Regional focus: as seen on the graph, the distribution of orders by region indicates that the East and West regions generate the most orders, so while maintaining efforts in these regions, the business should consider investing resources in regions where fewer orders are placed. This can involve market research to understand different local preferences, adjusting pricing strategies, or increasing advertising and promotion efforts in those regions. - Product mix optimization: by analysing the sales data by product category and sub-category, some insights were revealed into which products are driving profitability and which ones are underperforming. The company has to focus on optimizing its product mix by promoting high-profit margin items or introducing new product categories with higher demands. - Cost-reduction strategies: this can involve negotiating better supplier contracts, reducing waste in the supply chain or implementing technology solutions to streamline processes. - Outlier management: continuously monitoring and managing outliers, especially in the ‘Profit’ column. By removing or implementing strategies to mitigate outliers’ impact, such as adjusting pricing or refining inventory management practices, the company can ensure more consistent and sustainable profitability. - Prediction: by using the linear regression modelling strategy, key factors that contribute to profitability can be identified, and by understanding which variables have the most significant impact on profit, the company can prioritise resources and investments accordingly.

.css-15w88e5{color:var(--chakra-colors-fg-neutral-primary);font-weight:inherit;letter-spacing:-0.09px;}Coursework 2 - C11BD

Introduction

Import the Data

Re-labeling Columns

Cleaning the Data

Summary Statistics

Plotting

Modelling strategy - Linear Regression

Coursework 2 - C11BD