Skip to content
0
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt


data = pd.read_csv("data/orders_and_shipments.csv")

data.columns = data.columns.str.strip()

data["Shipment Date"] = data["Shipment Year"].astype(str) + "-" + data["Shipment Month"].astype(str) + "-" + data["Shipment Day"].astype(str)

data["Shipment Date"] = pd.to_datetime(data["Shipment Date"])

data["Order Date"] = data["Order Year"].astype(str) + "-" + data["Order Month"].astype(str) + "-" + data["Order Day"].astype(str) + "-" + data["Order Time"]

data["Order Date"] = pd.to_datetime(data["Order Date"])

data["Shipment Days - Actual"] = np.ceil(( data["Shipment Date"]  - data["Order Date"]) / np.timedelta64(1, 'D')) # round up

data["Late Shipment"] = (data["Shipment Days - Scheduled"] - data["Shipment Days - Actual"]) < 0

data["Shipment Days - Late"] = data["Shipment Days - Actual"] - data["Shipment Days - Scheduled"]
data["Shipment Days - Late"][data["Late Shipment"] == False] = 0 
# some orders got shipped before order date maybe data entry error in order date or shipment date, this analysis consider these observation as not being late



quantity_demanded = data.groupby(["Order YearMonth", "Product Name"])["Order Quantity"].sum().reset_index()

on_time_rate = data.groupby(["Order YearMonth", "Product Name"])["Late Shipment"].apply(lambda x: 1- (x.sum() / x.count())).reset_index()
on_time_rate.rename({"Late Shipment": "On time shipment rate"}, axis = 1, inplace = True)

inventory = pd.read_csv("data/inventory.csv")
inventory.head()
inventory.columns = inventory.columns.str.strip()

inventory = pd.merge(inventory, quantity_demanded, left_on=["Product Name", "Year Month"], right_on=["Product Name", "Order YearMonth"]).drop(["Order YearMonth"], axis = 1)

inventory = pd.merge(inventory, on_time_rate, left_on=["Product Name", "Year Month"], right_on=["Product Name", "Order YearMonth"]).drop(["Order YearMonth"], axis = 1)

inventory["Under Stock"] = (inventory["Order Quantity"] - inventory["Warehouse Inventory"]) > 0
inventory["Year Month"] = inventory["Year Month"].astype(str)
inventory["Year Month"] = pd.to_datetime(inventory["Year Month"].str[0:4] + "-" + inventory["Year Month"].str[-2:])
fulfillment = pd.read_csv("data/fulfillment.csv")
fulfillment.head()

📷 Dashboard screenshot

Just In Time supply chain analysis

To see why shipments are delayed and how to improve the on-time shipment rate, we can keep track of the following metrics:

1. Order Quantity trend:

Hidden code

Customers in different markets make orders in different seasons that usually last from 3 to 5 months.

-> By predicting the next season with high order quantity, we can optimize our supply chain by increase supply in these timeframe and keep less inventory in the warehouse in low seasons.

Despite most of the orders (57% of the total number of order items) came from the European and Latin American markets, these markets had the lowest on-time shipping rate.

2. Supply and demmand analysis

Taking a closer look at the most demanded products:

‌
‌
‌