Python project.py

# -*- coding: utf-8 -*-
"""Untitled1.ipynb

Automatically generated by Colab.

Original file is located at
    https://colab.research.google.com/drive/1ypascbnd-aK-lGf-edhkXyMe_iijlUgn
"""

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns

sheets= pd.read_excel('/content/Regional Sales Dataset.xlsx',sheet_name=None)

#assign dataframes to each sheets
df_sales= sheets['Sales Orders']
df_regions=  sheets['Regions']
df_customers= sheets['Customers']
df_products= sheets['Products']
df_state_reg= sheets['State Regions']
df_budgets= sheets['2017 Budgets']

df_sales.head(5)

print("df_sales shape:", df_sales.shape)
print("df_regions shape:", df_regions.shape)
print("df_customers:", df_customers.shape)
print("df_products:", df_products.shape)
print("df_state_reg:", df_state_reg.shape)
print("df_budgets:", df_budgets.shape)

df_sales.head(5)

df_regions.head(5)

df_customers.head(5)

df_products.head(5)

new_header= df_state_reg.iloc[0]
df_state_reg.columns=new_header
df_state_reg=df_state_reg[1:].reset_index(drop=True)

df_state_reg.head(5)

df_budgets.head(5)

df_sales.isnull().sum()

df_products.isnull().sum()

df_state_reg.isnull().sum()

df_budgets.isnull().sum()

df_customers.isnull().sum()

df_regions.isnull().sum()

"""#### **Data cleaning and Wrangling**

"""

#Merge with customers

df=df_sales.merge(
    df_customers,
    how='left',
    left_on='Customer Name Index',
    right_on='Customer Index'
)

#Merge with products
df=df.merge(
    df_products,
    how='left',
    left_on='Product Description Index',
    right_on='Index'
)

#Merge with regions
df=df.merge(
    df_regions,
    how='left',
    left_on='Delivery Region Index',
    right_on='id'
)

df.head(5)

#Merge with state_regions
df= df.merge(
    df_state_reg[["State Code", "Region"]],
    how='left',
    left_on='state_code' ,
    right_on='State Code')

#Merge with budgets
df= df.merge(
    df_budgets,
    how='left',
   on ='Product Name')

df.head(5)

#df.to_excel('file.xlsx')

#Customer Index
#Index
#id
#State Code_x
#State Code_y
#Region_x

#To remove the redundant column
cols_to_drop = ['Customer Index', 'Index', 'id', 'State Code']
df= df.drop(columns=cols_to_drop,errors='ignore')
df.head(5)

#Convert all cols to lowercase for consistency and easier access
df.columns=df.columns.str.lower()
df.columns.values

#keep the imporatnt columns and delete the cols that we dont need

cols_to_keep=['ordernumber', 'orderdate', 'customer names','channel', 'product name','order quantity', 'unit price',
       'line total', 'total unit cost', 'state_code', 'county', 'state','region', 'latitude',
       'longitude','2017 budgets' ]

df =df[cols_to_keep]

df.head(1)

#Renaming cols

df=df.rename(columns={
    'ordernumber' : 'order_number',
    'orderdate'  : 'order_date',
    'customer names' : 'customer_name',
    'product name' : 'product_name',
    'order quantity' : 'order_quantity',
    'unit price' : 'unit_price',
    'line total': 'revenue',
    'total unit cost': 'cost',
    'state_code': 'state',
    'state': 'state_name',
    'latitude':'lat',
    'longitude': 'long',
    '2017 budgets':'budget'


})

# blank out budgets for non 2017 orders
df.loc[df['order_date'].dt.year != 2017, 'budget'] = pd.NA

df.head(10)

df_2017= df[df.order_date.dt.year== 2017]

df.isnull().sum()

df_2017.head(5)

"""## Feature engineering"""

df['total_cost']= df['cost']*df['order_quantity']

df['profit']=df['revenue']-df['total_cost']

df['profit margin perc']= df['profit']/df['revenue']*100

df.head(5)

"""#### **EDA**"""

#monthly sales trend over time

#this line gives the month and year like (2022-03)
df['order_month']= df['order_date'].dt.to_period('M')
monthly_sales= df.groupby('order_month')['revenue'].sum()

plt.figure(figsize=(15,4))
monthly_sales.plot(marker='o',color='navy')

from matplotlib.ticker import FuncFormatter

#this line converts the number into millions, for clean ans clear datapoint
formatter = FuncFormatter(lambda x, pos: f'{x / 1e6:.1f}M')
#to get the current axes(here y axis , where we want to convert the no. into million)
plt.gca().yaxis.set_major_formatter(formatter)

plt.title('Monthly Sales Trend')
plt.xlabel('Month')
plt.ylabel('Total Revenue (Millions)')
plt.xticks(rotation=45)
plt.show()

#same code as the above just some more function
df['order_month'] = df['order_date'].dt.to_period('M')
monthly_sales = df.groupby('order_month')['revenue'].sum()

plt.figure(figsize=(15, 4))
plt.plot(monthly_sales.index.astype(str), monthly_sales.values, marker='o', color='navy')

# Add value labels on each point
for x, y in zip(monthly_sales.index.astype(str), monthly_sales.values):
    plt.text(x, y, f'{y/1e6:.1f}M', ha='center', va='bottom', fontsize=8, rotation=45)

# Format Y-axis
formatter = FuncFormatter(lambda x, pos: f'{x / 1e6:.1f}M')
plt.gca().yaxis.set_major_formatter(formatter)

# Titles and labels
plt.title('Monthly Sales Trend')
plt.xlabel('Month')
plt.ylabel('Total Revenue (Millions)')
plt.xticks(rotation=45)
plt.tight_layout()
plt.grid(True)
plt.show()

#Removing 2018 data,bcoz in 2018..we have only the data of jan and feb so it is not helping to find trends rather making it more confusing

# Convert order_date to datetime (if not already)
df['order_date'] = pd.to_datetime(df['order_date'])

# Remove records from January and February 2018
df_new = df[~((df['order_date'].dt.year == 2018) & (df['order_date'].dt.month.isin([1, 2])))]

# Aggregating the revenue for same month in different year (for ex: Total sales in the month of jan till now, and for every month and then analyze which month has got the highest sales and which got the lowest )

# Extract calendar month and month number
df_new['Month'] = df_new['order_date'].dt.month_name()
df_new['Month_num'] = df_new['order_date'].dt.month

# Group by calendar month (all years combined)
monthly_sales = df_new.groupby(['Month_num', 'Month'])['revenue'].sum().reset_index()

# Sort to get months in Jan–Dec order
monthly_sales = monthly_sales.sort_values('Month_num')

# Plot
plt.figure(figsize=(12, 5))
plt.plot(monthly_sales['Month'], monthly_sales['revenue'], marker='o', color='teal')

# Format Y-axis in millions
formatter = FuncFormatter(lambda x, pos: f'{x / 1e6:.1f}M')
plt.gca().yaxis.set_major_formatter(formatter)

# Labels and formatting
plt.title("Seasonal Sales Trend (All Years Combined)")
plt.xlabel("Month")
plt.ylabel("Total Revenue (Millions)")
plt.grid(True)
plt.xticks(rotation=45)
plt.tight_layout()
plt.show()

df.to_excel("final.xlsx")

#Top 10 products by revenue

#Group by product and sum revenue
product_revenue = df_new.groupby('product_name')['revenue'].sum().reset_index()

#Get top 10 products by revenue
top_products = product_revenue.sort_values(by='revenue', ascending=False).head(10)

# Plot
plt.figure(figsize=(12, 6))
ax = sns.barplot(data=top_products, x='product_name', y='revenue', palette='magma')

#Add value labels
for bar in ax.patches:
    height = bar.get_height()
    ax.text(
        bar.get_x() + bar.get_width() / 2,
        height,
        f'{height:,.0f}',  # comma as thousand separator
        ha='center',
        va='bottom',
        fontsize=10,
        fontweight='bold'
    )

# Customize chart
plt.title('Top 10 Products by Revenue')
plt.xlabel('Product Name')
plt.ylabel('Revenue')
plt.xticks(rotation=45)
plt.tight_layout()
plt.show()

# Bottom 10 product sby revenue
product_revenue = df_new.groupby('product_name')['revenue'].sum().reset_index()

# Step 3: Get bottom 10 products by revenue
bottom_products = product_revenue.sort_values(by='revenue', ascending=True).head(10)

# Step 4: Plot the bar chart
plt.figure(figsize=(12, 6))
ax = sns.barplot(data=bottom_products, x='product_name', y='revenue', palette='flare')

# Step 5: Add value labels on each bar
for bar in ax.patches:
    height = bar.get_height()
    ax.text(
        bar.get_x() + bar.get_width() / 2,
        height,
        f'{height:,.0f}',  # comma formatting for large numbers
        ha='center',
        va='bottom',
        fontsize=10,
        fontweight='bold'
    )

# Step 6: Customize chart
plt.title('Bottom 10 Products by Revenue')
plt.xlabel('Product Name')
plt.ylabel('Total Revenue')
plt.xticks(rotation=45)
plt.tight_layout()
plt.show()

#Sales by channel

chan_sales= df_new.groupby("channel")["revenue"].sum().sort_values(ascending=True)
plt.figure(figsize=(5,5))
plt.pie(chan_sales.values,labels=chan_sales.index,colors=sns.color_palette('coolwarm'),autopct='%1.1f%%')
plt.title("Total Sales by channel")
plt.tight_layout
plt.show()

#Average order value

AOV= df_new.groupby('order_number')['revenue'].sum()

plt.figure(figsize=(12,4))
plt.hist(
    AOV,
    bins=50,
    color='lightblue',
    edgecolor='black'
)

plt.title("Distribution of average order value")
plt.xlabel('Order value(USD)')
plt.ylabel('Number of orders')
plt.tight_layout()
plt.show()

#here the chart is right skewed, less customers are spending more and its giving clear information bcoz by calculating avg order_val per customer ,we are getting high amount but thats bcoz of these customer who spent a lot.

# Unit Price Distribution per Product
# Top 10 States by Revenue and Order Count
# Average Profit Marging by Channel
# Top and Bottom 10 Customers by Revenue
# Customer Segmentation: Revenue vs Profit Margin
# Correlation Heatmap

# Unit Price Distribution per Product

plt.figure(figsize=(14, 6))
sns.boxplot(x='product_name', y='unit_price', data=df)

plt.title('Unit Price Distribution per Product', fontsize=16)
plt.xlabel('Product Name', fontsize=12)
plt.ylabel('Unit Price', fontsize=12)
plt.xticks(rotation=45)
plt.tight_layout()
plt.show()

#INSIGHTS
#Which products are consistently priced ->	Look for short boxes
#Which ones have variable prices -> Look for tall boxes
#Which have odd pricing or data errors -> Look for outliers
#What’s the typical price for each product -> Check the median line
#Which products might need price review -> Outliers or wide IQRs signal issues



#TOP 10 states by revenue  and TOP 10 states by order count


# Step 1: Group by state_name and calculate total revenue and order count
state_stats = df.groupby('state_name').agg(
    revenue=('revenue', 'sum'),
    order_count=('order_number', 'count')
).reset_index()

# Step 2: Get top 10 by revenue and by order count
top10_revenue = state_stats.sort_values(by='revenue', ascending=False).head(10)
top10_orders = state_stats.sort_values(by='order_count', ascending=False).head(10)

# Step 3: Create side-by-side plots
fig, (ax1, ax2) = plt.subplots(1, 2, figsize=(15, 6), sharey=False)

# Bar chart for top 10 by revenue
barplot1 = sns.barplot(data=top10_revenue, x='state_name', y='revenue', palette='tab10', ax=ax1)
for p in barplot1.patches:
    barplot1.annotate(f'{int(p.get_height()):,}',
                      (p.get_x() + p.get_width() / 2., p.get_height()),
                      ha='center', va='bottom', fontsize=10, color='black')

ax1.set_title('Top 10 States by Revenue', fontsize=14)
ax1.set_xlabel('State', fontsize=12)
ax1.set_ylabel('Revenue ($)', fontsize=12)
ax1.tick_params(axis='x', rotation=45)
ax1.grid(True, axis='y')

# Bar chart for top 10 by order count
barplot2 = sns.barplot(data=top10_orders, x='state_name', y='order_count', palette='tab10', ax=ax2)
for p in barplot2.patches:
    barplot2.annotate(f'{int(p.get_height()):,}',
                      (p.get_x() + p.get_width() / 2., p.get_height()),
                      ha='center', va='bottom', fontsize=10, color='black')

ax2.set_title('Top 10 States by Order Count', fontsize=14)
ax2.set_xlabel('State', fontsize=12)
ax2.set_ylabel('Order Count', fontsize=12)
ax2.tick_params(axis='x', rotation=45)
ax2.grid(True, axis='y')

plt.tight_layout()
plt.show()

# Step 1: Group by 'channel' and calculate average profit margin
avg_profit_margin = df.groupby('channel')['profit margin perc'].mean().reset_index()

# Step 2: Sort values (optional, for better visualization)
avg_profit_margin = avg_profit_margin.sort_values(by='profit margin perc', ascending=False)

# Step 3: Plot using seaborn
plt.figure(figsize=(10, 6))
ax = sns.barplot(data=avg_profit_margin, x='channel', y='profit margin perc', palette='viridis')

# Add labels on top of each bar
for container in ax.containers:
    ax.bar_label(container, fmt='%.2f%%', label_type='edge', fontsize=10, padding=3)

# Add titles and labels
plt.title('Average Profit Margin by Channel', fontsize=14)
plt.xlabel('Channel')
plt.ylabel('Average Profit Margin (%)')
plt.xticks(rotation=45)
plt.tight_layout()
plt.show()


#Insights

#Invest more in high-margin channels.
#Investigate why some channels perform poorly.

#Customer segmentation: revenue vs profit margin

# Step 1: Group by customer and calculate total revenue and average profit margin
customer_data = df.groupby('customer_name').agg(
    total_revenue=('revenue', 'sum'),
    avg_profit_margin=('profit margin perc', 'mean')  # Make sure this column exists
).reset_index()

# Step 2: Create the scatter plot
plt.figure(figsize=(12, 8))
scatter = sns.scatterplot(
    data=customer_data,
    x='total_revenue',
    y='avg_profit_margin',
    hue='avg_profit_margin',  # Optional: color based on profit margin
    palette='viridis',
    size='total_revenue',     # Optional: size based on revenue
    sizes=(20, 200),
    alpha=0.7,
    legend=False
)

# Step 3: Add title and labels
plt.title('Customer Segmentation: Revenue vs Profit Margin', fontsize=16)
plt.xlabel('Total Revenue ($)', fontsize=12)
plt.ylabel('Average Profit Margin (%)', fontsize=12)
plt.grid(True)
plt.tight_layout()

# Step 4: Show plot
plt.show()


#INSIGHTS
#Top-right corner: Customers with high revenue & high profit margin → Premium, high-value customers

#Bottom-right corner: High revenue but low margin → Maybe negotiated deals or discount seekers

#Top-left: Low revenue, high margin → Niche customers with profitable purchases

#Bottom-left: Low revenue & low profit margin → Least valuable customers

num_cols= ['order_quantity','revenue','total_cost','unit_price','profit']

# Calculate the correlation matrix for these numeric features
corr = df[num_cols].corr()

# Set the figure size for clarity
plt.figure(figsize=(6, 4))

# Plot the heatmap with annotations and a viridis colormap
sns.heatmap(
    corr,             # Data: correlation matrix
    annot=True,       # Display the correlation coefficients on the heatmap
    fmt=".2f",        # Format numbers to two decimal places
    cmap='viridis'    # Color palette for the heatmap
)

# Add title for context
plt.title('Correlation Matrix')

# Adjust layout to prevent clipping
plt.tight_layout()

# Display the heatmap
plt.show()

#monthly revenue cycle:may peaks at
#top 10 produts based on revenue : (Product 26, Product 25, Product 13, Product 14, Product 5, Product 15, Product 2, Product 4, Product 1, Product3)
#bottome 10 products based on revenue: (Product 9, Product 24, Product 29, Product 22, Product 7, Product 10, Product 27, Product 30, Product 23, Product21)
#Total_Sales by channel:The wholesale channel drives the majority of total sales at 54.1%, followed by distributor (31.3%), while export contributes the least at 14.6%.
#channel distribution (avg profit margin vs channels): export- 37.96, distributor- 37.56, wholesale- 37.09
#Order value distribution:The distribution shows that most orders are of low value, with order frequency dropping steeply as value increases—indicating a heavily right-skewed pattern.
#Unit price distribution: written in code
#TOP 10 states by revenue  and TOP 10 states by order count: California has the highest sales and order count and maschuttes has the lowest, apart from that newyork has higher sales than indiana but it has less order count than indiana that concludes that indiana people are buying cheap/discounted prods or maybe newyork people are buying expensive products but less orders
#custoemr segmentation: (revenue vs profit margin)- written in code
#correlation matrix: Correlation shows how strongly two values move together — if one goes up, does the other go up or down?A high correlation means a strong link, while a low or zero correlation means little or no connection.