Using historical sales data from ORDERS and ORDER_ITEMS, current stock levels from PRODUCT_INVENTORY, and supplier lead times (assume an average lead time of 14 days if not explicitly available per product), calculate the reorder point (ROP) and optimal stock level for the top 20 products by sales volume. The output should be a CSV file or table listing each product with: 1) Current stock. 2) Calculated ROP. 3) Calculated optimal stock level. 4) Calculated safety stock. 5) Estimated days of stock remaining. Briefly explain the formulas used (e.g., Economic Order Quantity - EOQ, if applicable).

May 19, 02:15 PM
Vega

I'll help you calculate reorder points and optimal stock levels for your top-selling products. Let me first check what data sources are available to us.

Database Tables
nova_glow
0 tables
Database Tables
Finance & Economics
0 tables

Now, let's examine the structure of the relevant tables in the nova_glow database to understand what data we have available.

Database: 4 tables analyzed
orders10 rows
ColumnTypeNullablePrimary
ORDER_IDVARCHAR(16777216)NoNo
CUSTOMER_IDVARCHAR(16777216)NoNo
ORDER_DATETIMESTAMP_NTZ(9)NoNo
ORDER_STATUSVARCHAR(16777216)NoNo
SHIPPING_STREET_ADDRESSVARCHAR(16777216)NoNo
SHIPPING_CITYVARCHAR(16777216)NoNo
SHIPPING_STATEVARCHAR(16777216)NoNo
SHIPPING_ZIP_CODEVARCHAR(16777216)NoNo
SHIPPING_METHODVARCHAR(16777216)NoNo
PAYMENT_METHOD_USEDVARCHAR(16777216)NoNo
PROMO_CODE_USEDVARCHAR(16777216)NoNo
SUBTOTAL_AMOUNTFLOATNoNo
DISCOUNT_AMOUNTFLOATNoNo
SHIPPING_COSTFLOATNoNo
TAX_AMOUNTFLOATNoNo
TOTAL_ORDER_AMOUNTFLOATNoNo
order_items10 rows
ColumnTypeNullablePrimary
ORDER_ITEM_IDVARCHAR(16777216)NoNo
ORDER_IDVARCHAR(16777216)NoNo
PRODUCT_IDVARCHAR(16777216)NoNo
QUANTITYNUMBER(38,0)NoNo
PRICE_PER_UNIT_AT_PURCHASEFLOATNoNo
TOTAL_ITEM_PRICEFLOATNoNo
product_inventory10 rows
ColumnTypeNullablePrimary
INVENTORY_IDVARCHAR(16777216)NoNo
PRODUCT_IDVARCHAR(16777216)NoNo
CURRENT_STOCK_LEVELNUMBER(38,0)NoNo
REORDER_POINT_THRESHOLDNUMBER(38,0)NoNo
TARGET_STOCK_LEVELNUMBER(38,0)NoNo
SUPPLIER_LEAD_TIME_DAYSNUMBER(38,0)NoNo
LAST_RESTOCK_DATEDATENoNo
LAST_STOCK_CHECK_DATETIMESTAMP_NTZ(9)NoNo
products10 rows
ColumnTypeNullablePrimary
PRODUCT_IDVARCHAR(16777216)NoNo
PRODUCT_NAMEVARCHAR(16777216)NoNo
CATEGORYVARCHAR(16777216)NoNo
SUPPLIER_NAMEVARCHAR(16777216)NoNo
COST_PRICEFLOATNoNo
SELLING_PRICEFLOATNoNo
DESCRIPTIONVARCHAR(16777216)NoNo
DATE_ADDEDDATENoNo

Great! I have all the necessary tables to complete this task. Let's create a notebook to calculate the reorder points and optimal stock levels for the top 20 products by sales volume.

Now, let's start building our analysis. First, I'll add a markdown cell to explain the purpose of this notebook.

Added Markdown cell
# Inventory Reorder Point and Optimal Stock Level Analysis

This notebook calculates the reorder ...

Now, let's add a Python cell to import the necessary libraries and set up our environment.

Added Python cell
Success
# Import necessary libraries
import pandas as pd
import numpy as np
import math
from datetime imp...
Analysis date: 2025-05-19

Now, let's retrieve the data we need from the database. First, let's get the historical sales data to identify the top 20 products by sales volume.

Added Python cell
Success
# Query to get historical sales data for the past year
sql_sales_data = """
SELECT 
    oi.PRODUC...
Retrieved 0 sales records
Empty DataFrame Columns: [product_id, product_name, order_date, quantity_sold, avg_price] Index: []

It seems we don't have any sales data for the past year. Let's modify our query to get all available sales data without the date restriction.

Updated Python cell
Success
# Query to get all historical sales data
sql_sales_data = """
SELECT 
    oi.PRODUCT_ID,
    p.PR...
product_id product_name order_date quantity_sold avg_price 0 NGG-AUR-16C StellarMini Z9000 2021-02-14T08:10:34 2 717.97 1 NGG-ECO-DUS ChronoDrive Light 2021-02-21T05:19:55 1 1078.54 2 NGG-AUR-16C StellarMini Z9000 2021-02-21T05:19:55 2 717.97 3 NGG-ECO-F48 PulseWave AI 2021-02-21T05:19:55 1 360.87 4 NGG-AUR-16C StellarMini Z9000 2021-03-03T08:57:28 2 717.97 ... ... ... ... ... ... 10700 NGG-ECO-DUS ChronoDrive Light 2023-12-31T09:02:21 1 1078.54 10701 NGG-NOV-JTH PulseMax AI 2023-12-31T09:18:20 2 1182.49 10702 NGG-CHR-AAC NovaBot Z9000 2023-12-31T09:18:20 1 202.77 10703 NGG-CHR-825 ChronoPro Stealth 2023-12-31T14:18:01 1 177.72 10704 NGG-ECO-L6C NovaShift Stealth 2023-12-31T14:18:01 1 846.66 [10705 rows x 5 columns]
Retrieved 10705 sales records
product_id product_name order_date quantity_sold avg_price 0 NGG-AUR-16C StellarMini Z9000 2021-02-14T08:10:34 2 717.97 1 NGG-ECO-DUS ChronoDrive Light 2021-02-21T05:19:55 1 1078.54 2 NGG-AUR-16C StellarMini Z9000 2021-02-21T05:19:55 2 717.97 3 NGG-ECO-F48 PulseWave AI 2021-02-21T05:19:55 1 360.87 4 NGG-AUR-16C StellarMini Z9000 2021-03-03T08:57:28 2 717.97

Great! Now we have sales data. Let's also get the current inventory data.

Added Python cell
Success
# Query to get current inventory data
sql_inventory_data = """
SELECT 
    pi.PRODUCT_ID,
    p.P...
product_id product_name category cost_price selling_price current_stock_level reorder_point_threshold target_stock_level supplier_lead_time_days last_restock_date last_stock_check_date 0 NGG-AUR-5WW CyberPro S2 Gaming Accessories 70.75 190.60 162 20 70 19 2023-09-30 2023-12-15T02:56:19 1 NGG-STE-49B AuraGlow Z9000 VR/AR 364.97 678.25 27 20 70 18 2023-10-01 2023-12-16T13:53:44 2 NGG-ECO-Q9A EcoLens S2 VR/AR 263.06 574.42 92 20 70 10 2023-04-11 2023-12-29T17:38:07 3 NGG-AUR-U9T EcoMini S2 Smart Home 306.82 510.95 70 20 70 17 2023-12-01 2023-12-30T23:38:25 4 NGG-STE-TXP QuantumGlow Light Smart Home 320.52 859.04 242 20 70 11 2022-08-11 2023-12-27T04:33:15 .. ... ... ... ... ... ... ... ... ... ... ... 65 NGG-NOV-UWH EcoGlow AI Gaming Accessories 396.40 1035.88 45 20 70 13 2023-11-01 2023-12-08T04:39:07 66 NGG-CHR-VW4 EcoShift AI Drones 324.90 702.63 65 20 70 18 2021-03-20 2023-12-24T06:40:32 67 NGG-CYB-UEL QuantumShift Stealth Office Tech 284.31 480.11 70 20 70 9 2023-12-01 2023-12-04T01:18:45 68 NGG-NOV-NV9 CyberWave Stealth Gaming Accessories 55.41 105.52 70 20 70 8 2023-12-01 2023-12-23T13:44:02 69 NGG-ECO-W7J AuraLens X1 Smart Home 253.99 480.58 47 20 70 18 2023-11-01 2023-12-24T06:50:50 [70 rows x 11 columns]
Retrieved 70 inventory records
product_id product_name category cost_price selling_price current_stock_level reorder_point_threshold target_stock_level supplier_lead_time_days last_restock_date last_stock_check_date 0 NGG-AUR-5WW CyberPro S2 Gaming Accessories 70.75 190.60 162 20 70 19 2023-09-30 2023-12-15T02:56:19 1 NGG-STE-49B AuraGlow Z9000 VR/AR 364.97 678.25 27 20 70 18 2023-10-01 2023-12-16T13:53:44 2 NGG-ECO-Q9A EcoLens S2 VR/AR 263.06 574.42 92 20 70 10 2023-04-11 2023-12-29T17:38:07 3 NGG-AUR-U9T EcoMini S2 Smart Home 306.82 510.95 70 20 70 17 2023-12-01 2023-12-30T23:38:25 4 NGG-STE-TXP QuantumGlow Light Smart Home 320.52 859.04 242 20 70 11 2022-08-11 2023-12-27T04:33:15

Now, let's process the sales data to identify the top 20 products by sales volume and calculate the average daily demand and its standard deviation.

Added Python cell
Success
# Convert order_date to datetime
sales_data['order_date'] = pd.to_datetime(sales_data['order_date...
Sales data spans from 2021-02-14 to 2023-12-31 (1051 days) Top 20 Products by Sales Volume:
product_id product_name quantity_sold 33 NGG-ECO-F48 PulseWave AI 461 25 NGG-CYB-RLW NovaGlow AI 452 65 NGG-STE-ALA AuraGlow P400 450 9 NGG-CHR-55E CyberBot Connect 440 6 NGG-AUR-U9T EcoMini S2 436 30 NGG-ECO-99U PulseMax X1 425 35 NGG-ECO-IFA ChronoBuds AI 414 2 NGG-AUR-16C StellarMini Z9000 408 50 NGG-NOV-UWH EcoGlow AI 408 12 NGG-CHR-AAC NovaBot Z9000 406 47 NGG-NOV-E80 ChronoGlow P400 405 11 NGG-CHR-825 ChronoPro Stealth 403 32 NGG-ECO-DUS ChronoDrive Light 403 60 NGG-STE-0DG CyberMini P400 396 59 NGG-QUA-UG0 AuraCore Light 394 44 NGG-NOV-3XC AuraBot Stealth 386 40 NGG-ECO-TJG CyberCore Connect 385 62 NGG-STE-6QV PulseWave AI 381 22 NGG-CYB-KY7 NovaGlow Stealth 371 38 NGG-ECO-Q9A EcoLens S2 370

Now, let's calculate the daily demand statistics for these top 20 products.

Added Python cell
Success
# Calculate daily demand statistics for the top 20 products
daily_demand_stats = []

for _, produ...
Daily Demand Statistics for Top 20 Products:
product_id product_name total_quantity avg_daily_demand std_daily_demand 0 NGG-ECO-F48 PulseWave AI 461 0.44 0.00 1 NGG-CYB-RLW NovaGlow AI 452 0.43 0.00 2 NGG-STE-ALA AuraGlow P400 450 0.43 0.00 3 NGG-CHR-55E CyberBot Connect 440 0.42 0.00 4 NGG-AUR-U9T EcoMini S2 436 0.41 0.00 5 NGG-ECO-99U PulseMax X1 425 0.40 0.00 6 NGG-ECO-IFA ChronoBuds AI 414 0.39 0.00 7 NGG-AUR-16C StellarMini Z9000 408 0.39 0.00 8 NGG-NOV-UWH EcoGlow AI 408 0.39 0.00 9 NGG-CHR-AAC NovaBot Z9000 406 0.39 0.00 10 NGG-NOV-E80 ChronoGlow P400 405 0.39 0.00 11 NGG-CHR-825 ChronoPro Stealth 403 0.38 0.00 12 NGG-ECO-DUS ChronoDrive Light 403 0.38 0.00 13 NGG-STE-0DG CyberMini P400 396 0.38 0.00 14 NGG-QUA-UG0 AuraCore Light 394 0.37 0.00 15 NGG-NOV-3XC AuraBot Stealth 386 0.37 0.00 16 NGG-ECO-TJG CyberCore Connect 385 0.37 0.00 17 NGG-STE-6QV PulseWave AI 381 0.36 0.00 18 NGG-CYB-KY7 NovaGlow Stealth 371 0.35 0.00 19 NGG-ECO-Q9A EcoLens S2 370 0.35 0.00

I notice that the standard deviation of daily demand is showing as 0.00 for all products. This is likely because the sales data is aggregated at a daily level and doesn't show much variation. Let's try a different approach to calculate the standard deviation by looking at weekly or monthly sales patterns.

Added Python cell
Success
# Calculate weekly demand statistics for the top 20 products
weekly_demand_stats = []

for _, pro...
Demand Statistics for Top 20 Products (using weekly variation):
/var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:18: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['week'] = product_sales_data['order_date'].dt.isocalendar().week /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:19: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['year'] = product_sales_data['order_date'].dt.isocalendar().year /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:20: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['year_week'] = product_sales_data['year'].astype(str) + '-' + product_sales_data['week'].astype(str) /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:18: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['week'] = product_sales_data['order_date'].dt.isocalendar().week /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:19: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['year'] = product_sales_data['order_date'].dt.isocalendar().year /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:20: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['year_week'] = product_sales_data['year'].astype(str) + '-' + product_sales_data['week'].astype(str) /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:18: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['week'] = product_sales_data['order_date'].dt.isocalendar().week /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:19: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['year'] = product_sales_data['order_date'].dt.isocalendar().year /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:20: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['year_week'] = product_sales_data['year'].astype(str) + '-' + product_sales_data['week'].astype(str) /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:18: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['week'] = product_sales_data['order_date'].dt.isocalendar().week /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:19: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['year'] = product_sales_data['order_date'].dt.isocalendar().year /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:20: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['year_week'] = product_sales_data['year'].astype(str) + '-' + product_sales_data['week'].astype(str) /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:18: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['week'] = product_sales_data['order_date'].dt.isocalendar().week /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:19: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['year'] = product_sales_data['order_date'].dt.isocalendar().year /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:20: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['year_week'] = product_sales_data['year'].astype(str) + '-' + product_sales_data['week'].astype(str) /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:18: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['week'] = product_sales_data['order_date'].dt.isocalendar().week /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:19: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['year'] = product_sales_data['order_date'].dt.isocalendar().year /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:20: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['year_week'] = product_sales_data['year'].astype(str) + '-' + product_sales_data['week'].astype(str) /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:18: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['week'] = product_sales_data['order_date'].dt.isocalendar().week /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:19: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['year'] = product_sales_data['order_date'].dt.isocalendar().year /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:20: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['year_week'] = product_sales_data['year'].astype(str) + '-' + product_sales_data['week'].astype(str) /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:18: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['week'] = product_sales_data['order_date'].dt.isocalendar().week /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:19: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['year'] = product_sales_data['order_date'].dt.isocalendar().year /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:20: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['year_week'] = product_sales_data['year'].astype(str) + '-' + product_sales_data['week'].astype(str) /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:18: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['week'] = product_sales_data['order_date'].dt.isocalendar().week /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:19: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['year'] = product_sales_data['order_date'].dt.isocalendar().year /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:20: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['year_week'] = product_sales_data['year'].astype(str) + '-' + product_sales_data['week'].astype(str) /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:18: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['week'] = product_sales_data['order_date'].dt.isocalendar().week /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:19: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['year'] = product_sales_data['order_date'].dt.isocalendar().year /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:20: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['year_week'] = product_sales_data['year'].astype(str) + '-' + product_sales_data['week'].astype(str) /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:18: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['week'] = product_sales_data['order_date'].dt.isocalendar().week /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:19: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['year'] = product_sales_data['order_date'].dt.isocalendar().year /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:20: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['year_week'] = product_sales_data['year'].astype(str) + '-' + product_sales_data['week'].astype(str) /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:18: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['week'] = product_sales_data['order_date'].dt.isocalendar().week /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:19: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['year'] = product_sales_data['order_date'].dt.isocalendar().year /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:20: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['year_week'] = product_sales_data['year'].astype(str) + '-' + product_sales_data['week'].astype(str) /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:18: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['week'] = product_sales_data['order_date'].dt.isocalendar().week /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:19: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['year'] = product_sales_data['order_date'].dt.isocalendar().year /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:20: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['year_week'] = product_sales_data['year'].astype(str) + '-' + product_sales_data['week'].astype(str) /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:18: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['week'] = product_sales_data['order_date'].dt.isocalendar().week /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:19: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['year'] = product_sales_data['order_date'].dt.isocalendar().year /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:20: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['year_week'] = product_sales_data['year'].astype(str) + '-' + product_sales_data['week'].astype(str) /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:18: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['week'] = product_sales_data['order_date'].dt.isocalendar().week /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:19: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['year'] = product_sales_data['order_date'].dt.isocalendar().year /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:20: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['year_week'] = product_sales_data['year'].astype(str) + '-' + product_sales_data['week'].astype(str) /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:18: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['week'] = product_sales_data['order_date'].dt.isocalendar().week /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:19: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['year'] = product_sales_data['order_date'].dt.isocalendar().year /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:20: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['year_week'] = product_sales_data['year'].astype(str) + '-' + product_sales_data['week'].astype(str) /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:18: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['week'] = product_sales_data['order_date'].dt.isocalendar().week /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:19: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['year'] = product_sales_data['order_date'].dt.isocalendar().year /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:20: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['year_week'] = product_sales_data['year'].astype(str) + '-' + product_sales_data['week'].astype(str) /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:18: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['week'] = product_sales_data['order_date'].dt.isocalendar().week /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:19: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['year'] = product_sales_data['order_date'].dt.isocalendar().year /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:20: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['year_week'] = product_sales_data['year'].astype(str) + '-' + product_sales_data['week'].astype(str) /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:18: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['week'] = product_sales_data['order_date'].dt.isocalendar().week /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:19: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['year'] = product_sales_data['order_date'].dt.isocalendar().year /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:20: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['year_week'] = product_sales_data['year'].astype(str) + '-' + product_sales_data['week'].astype(str) /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:18: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['week'] = product_sales_data['order_date'].dt.isocalendar().week /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:19: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['year'] = product_sales_data['order_date'].dt.isocalendar().year /var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/1989296877.py:20: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy product_sales_data['year_week'] = product_sales_data['year'].astype(str) + '-' + product_sales_data['week'].astype(str)
product_id product_name total_quantity avg_daily_demand std_daily_demand 0 NGG-ECO-F48 PulseWave AI 461 0.44 0.43 1 NGG-CYB-RLW NovaGlow AI 452 0.43 0.43 2 NGG-STE-ALA AuraGlow P400 450 0.43 0.35 3 NGG-CHR-55E CyberBot Connect 440 0.42 0.42 4 NGG-AUR-U9T EcoMini S2 436 0.41 0.31 5 NGG-ECO-99U PulseMax X1 425 0.40 0.40 6 NGG-ECO-IFA ChronoBuds AI 414 0.39 0.37 7 NGG-AUR-16C StellarMini Z9000 408 0.39 0.33 8 NGG-NOV-UWH EcoGlow AI 408 0.39 0.35 9 NGG-CHR-AAC NovaBot Z9000 406 0.39 0.36 10 NGG-NOV-E80 ChronoGlow P400 405 0.39 0.36 11 NGG-CHR-825 ChronoPro Stealth 403 0.38 0.46 12 NGG-ECO-DUS ChronoDrive Light 403 0.38 0.35 13 NGG-STE-0DG CyberMini P400 396 0.38 0.35 14 NGG-QUA-UG0 AuraCore Light 394 0.37 0.34 15 NGG-NOV-3XC AuraBot Stealth 386 0.37 0.35 16 NGG-ECO-TJG CyberCore Connect 385 0.37 0.39 17 NGG-STE-6QV PulseWave AI 381 0.36 0.39 18 NGG-CYB-KY7 NovaGlow Stealth 371 0.35 0.37 19 NGG-ECO-Q9A EcoLens S2 370 0.35 0.41

Now, let's merge the demand statistics with the inventory data to calculate the reorder points and optimal stock levels.

Added Python cell
Success
# Merge demand statistics with inventory data
inventory_analysis = pd.merge(demand_stats_df, inve...
Inventory Analysis for Top 20 Products:
product_id product_name current_stock_level reorder_point optimal_stock_level safety_stock days_of_stock_remaining 0 NGG-ECO-F48 PulseWave AI 0 9.00 28.00 3.00 0.00 1 NGG-CYB-RLW NovaGlow AI 70 8.00 20.00 3.00 163.00 2 NGG-STE-ALA AuraGlow P400 33 6.00 26.00 2.00 77.00 3 NGG-CHR-55E CyberBot Connect 28 12.00 29.00 4.00 67.00 4 NGG-AUR-U9T EcoMini S2 70 10.00 24.00 3.00 169.00 5 NGG-ECO-99U PulseMax X1 70 11.00 84.00 3.00 173.00 6 NGG-ECO-IFA ChronoBuds AI 70 7.00 27.00 3.00 178.00 7 NGG-AUR-16C StellarMini Z9000 24 6.00 19.00 2.00 62.00 8 NGG-NOV-UWH EcoGlow AI 45 8.00 19.00 2.00 116.00 9 NGG-CHR-AAC NovaBot Z9000 70 9.00 30.00 3.00 181.00 10 NGG-NOV-E80 ChronoGlow P400 24 8.00 23.00 3.00 62.00 11 NGG-CHR-825 ChronoPro Stealth 70 8.00 36.00 3.00 183.00 12 NGG-ECO-DUS ChronoDrive Light 38 7.00 17.00 2.00 99.00 13 NGG-STE-0DG CyberMini P400 70 7.00 31.00 2.00 186.00 14 NGG-QUA-UG0 AuraCore Light 70 7.00 24.00 2.00 187.00 15 NGG-NOV-3XC AuraBot Stealth 70 11.00 24.00 3.00 191.00 16 NGG-ECO-TJG CyberCore Connect 47 8.00 24.00 3.00 128.00 17 NGG-STE-6QV PulseWave AI 70 7.00 29.00 3.00 193.00 18 NGG-CYB-KY7 NovaGlow Stealth 63 9.00 32.00 3.00 178.00 19 NGG-ECO-Q9A EcoLens S2 92 6.00 20.00 3.00 261.00

Let's save this data to the store and create a visualization to better understand the inventory status.

Added Python cell
Success
# Save the inventory analysis to the store
store.set("inventory_analysis", inventory_analysis)

#...
2025-05-19 07:17:54,408 - app.kernel_configuration.asset_store - WARNING - basic to_dict() resulted in referenced data for chart stock_vs_reorder_chart: data-ebda224f4a19befa987ada85c6a2b7c9
{'key': 'stock_vs_reorder_chart', 'content': alt.LayerChart(...), 'content_type': 'application/vnd.vega.v5+json', 'metadata': {'created': '2025-05-19T07:17:54.388684', 'data_handling': 'referenced_basic_dict'}, 'cell_id': '80826bf6-06f3-4284-ab1b-418b9c25fd5b', 'cell_version': '2025-05-19T14:17:53.792Z'}
2 asset references available in notebook

Now, let's create another visualization to show the days of stock remaining for each product.

Added Python cell
Error
# Create a visualization for days of stock remaining
days_chart = alt.Chart(inventory_analysis).m...
TypeError: altair.vegalite.v5.api._Conditional() got multiple values for keyword argument 'condition'
---------------------------------------------------------------------------
TypeError Traceback (most recent call last)
Cell In[22], line 5  1 # Create a visualization for days of stock remaining  2 days_chart = alt.Chart(inventory_analysis).mark_bar().encode(  3 x=alt.X('product_name:N', sort='-y', title='Product Name'),  4 y=alt.Y('days_of_stock_remaining:Q', title='Days of Stock Remaining'), ----> 5 color=alt.condition(  6  alt.datum.days_of_stock_remaining < 30,  7  alt.value('red'), # less than 30 days  8  alt.condition(  9  alt.datum.days_of_stock_remaining < 60,  10  alt.value('orange'), # between 30 and 60 days  11  alt.value('green') # more than 60 days  12  )  13  ),  14 tooltip=['product_name', 'days_of_stock_remaining', 'current_stock_level', 'avg_daily_demand']  15 ).properties(  16 title='Days of Stock Remaining by Product',  17 width=600,  18 height=400  19 )  21 # Add reference lines for 30 and 60 days  22 thirty_day_rule = alt.Chart(pd.DataFrame({'y': [30]})).mark_rule(  23 color='orange',  24 strokeDash=[4, 4]  25 ).encode(y='y:Q')
File ~/Library/Caches/pypoetry/virtualenvs/jupyter-kernel-server-yp2e7biK-py3.12/lib/python3.12/site-packages/altair/vegalite/v5/api.py:1889, in condition(predicate, if_true, if_false, empty, **kwargs)  1861 """  1862 A conditional attribute or encoding.  1863  (...)  1886  the spec that describes the condition  1887 """  1888 condition = _predicate_to_condition(predicate, empty=empty) -> 1889 return _condition_to_selection(condition, if_true, if_false, **kwargs)
File ~/Library/Caches/pypoetry/virtualenvs/jupyter-kernel-server-yp2e7biK-py3.12/lib/python3.12/site-packages/altair/vegalite/v5/api.py:629, in _condition_to_selection(condition, if_true, if_false, **kwargs)  627 if_false = utils.parse_shorthand(if_false)  628 if_false.update(kwargs) --> 629 selection = _Conditional(condition=cond_mutable, **if_false) # type: ignore[typeddict-item]  630 else:  631 raise TypeError(if_false)
TypeError: altair.vegalite.v5.api._Conditional() got multiple values for keyword argument 'condition'
TypeError: Execution failed
---------------------------------------------------------------------------
TypeError Traceback (most recent call last)
Cell In[22], line 5 1 # Create a visualization for days of stock remaining 2 days_chart = alt.Chart(inventory_analysis).mark_bar().encode( 3 x=alt.X('product_name:N', sort='-y', title='Product Name'), 4 y=alt.Y('days_of_stock_remaining:Q', title='Days of Stock Remaining'), ----> 5 color=alt.condition( 6 alt.datum.days_of_stock_remaining < 30, 7 alt.value('red'), # less than 30 days 8 alt.condition( 9 alt.datum.days_of_stock_remaining < 60, 10 alt.value('orange'), # between 30 and 60 days 11 alt.value('green') # more than 60 days 12 ) 13 ), 14 tooltip=['product_name', 'days_of_stock_remaining', 'current_stock_level', 'avg_daily_demand'] 15 ).properties( 16 title='Days of Stock Remaining by Product', 17 width=600, 18 height=400 19 ) 21 # Add reference lines for 30 and 60 days 22 thirty_day_rule = alt.Chart(pd.DataFrame({'y': [30]})).mark_rule( 23 color='orange', 24 strokeDash=[4, 4] 25 ).encode(y='y:Q')
File ~/Library/Caches/pypoetry/virtualenvs/jupyter-kernel-server-yp2e7biK-py3.12/lib/python3.12/site-packages/altair/vegalite/v5/api.py:1889, in condition(predicate, if_true, if_false, empty, **kwargs) 1861 """ 1862 A conditional attribute or encoding. 1863 (...) 1886 the spec that describes the condition 1887 """ 1888 condition = _predicate_to_condition(predicate, empty=empty) -> 1889 return _condition_to_selection(condition, if_true, if_false, **kwargs)
File ~/Library/Caches/pypoetry/virtualenvs/jupyter-kernel-server-yp2e7biK-py3.12/lib/python3.12/site-packages/altair/vegalite/v5/api.py:629, in _condition_to_selection(condition, if_true, if_false, **kwargs) 627 if_false = utils.parse_shorthand(if_false) 628 if_false.update(kwargs) --> 629 selection = _Conditional(condition=cond_mutable, **if_false) # type: ignore[typeddict-item] 630 else: 631 raise TypeError(if_false)
TypeError: altair.vegalite.v5.api._Conditional() got multiple values for keyword argument 'condition'

Let's fix the visualization for days of stock remaining:

Updated Python cell
Success
# Create a visualization for days of stock remaining
# Add a column for color category based on d...
{'key': 'days_of_stock_chart', 'content': alt.LayerChart(...), 'content_type': 'application/vnd.vega.v5+json', 'metadata': {'created': '2025-05-19T07:18:23.641415', 'data_handling': 'no_data'}, 'cell_id': '04c99c34-127c-4c7a-a499-29827bff68df', 'cell_version': '2025-05-19T14:18:23.422Z'}
1 asset references available in notebook

Now, let's create a final table with all the key metrics for the top 20 products:

Added Python cell
Success
# Create a final table with all the key metrics
final_table = inventory_analysis[['product_id', '...
Product ID Product Name Current Stock Reorder Point (ROP) Optimal Stock Level Safety Stock Days of Stock Remaining Lead Time (days) Avg Daily Demand Cost Price ($) Selling Price ($) Reorder Status 7 NGG-AUR-16C StellarMini Z9000 24 6.00 19.00 2.00 62.00 11 0.39 370.76 717.97 OK 10 NGG-NOV-E80 ChronoGlow P400 24 8.00 23.00 3.00 62.00 14 0.39 243.35 631.25 OK 3 NGG-CHR-55E CyberBot Connect 28 12.00 29.00 4.00 67.00 19 0.42 190.43 364.14 OK 2 NGG-STE-ALA AuraGlow P400 33 6.00 26.00 2.00 77.00 9 0.43 155.43 375.77 OK 12 NGG-ECO-DUS ChronoDrive Light 38 7.00 17.00 2.00 99.00 11 0.38 495.16 1078.54 OK 8 NGG-NOV-UWH EcoGlow AI 45 8.00 19.00 2.00 116.00 13 0.39 396.40 1035.88 OK 16 NGG-ECO-TJG CyberCore Connect 47 8.00 24.00 3.00 128.00 15 0.37 212.48 621.04 OK 1 NGG-CYB-RLW NovaGlow AI 70 8.00 20.00 3.00 163.00 11 0.43 374.69 854.97 OK 4 NGG-AUR-U9T EcoMini S2 70 10.00 24.00 3.00 169.00 17 0.41 306.82 510.95 OK 5 NGG-ECO-99U PulseMax X1 70 11.00 84.00 3.00 173.00 18 0.40 10.90 26.92 OK 6 NGG-ECO-IFA ChronoBuds AI 70 7.00 27.00 3.00 178.00 12 0.39 140.98 303.43 OK 18 NGG-CYB-KY7 NovaGlow Stealth 63 9.00 32.00 3.00 178.00 17 0.35 93.43 244.91 OK 9 NGG-CHR-AAC NovaBot Z9000 70 9.00 30.00 3.00 181.00 15 0.39 117.73 202.77 OK 11 NGG-CHR-825 ChronoPro Stealth 70 8.00 36.00 3.00 183.00 13 0.38 72.48 177.72 OK 13 NGG-STE-0DG CyberMini P400 70 7.00 31.00 2.00 186.00 13 0.38 100.62 240.07 OK 14 NGG-QUA-UG0 AuraCore Light 70 7.00 24.00 2.00 187.00 12 0.37 194.15 560.71 OK 15 NGG-NOV-3XC AuraBot Stealth 70 11.00 24.00 3.00 191.00 21 0.37 335.25 543.06 OK 17 NGG-STE-6QV PulseWave AI 70 7.00 29.00 3.00 193.00 11 0.36 100.83 157.63 OK 19 NGG-ECO-Q9A EcoLens S2 92 6.00 20.00 3.00 261.00 10 0.35 263.06 574.42 OK 0 NGG-ECO-F48 PulseWave AI 0 9.00 28.00 3.00 0.00 13 0.44 168.67 360.87 REORDER NOW
1 asset references available in notebook

Let's export the final table to CSV:

Added Python cell
Success
# Export the final table to CSV
csv_data = final_table.to_csv(index=False)

# Display the first f...
['Product ID,Product Name,Current Stock,Reorder Point (ROP),Optimal Stock Level,Safety Stock,Days of Stock Remaining,Lead Time (days),Avg Daily Demand,Cost Price ($),Selling Price ($),Reorder Status', 'NGG-AUR-16C,StellarMini Z9000,24,6.0,19.0,2.0,62.0,11,0.3882017126546147,370.76,717.97,OK', 'NGG-NOV-E80,ChronoGlow P400,24,8.0,23.0,3.0,62.0,14,0.38534728829686016,243.35,631.25,OK', 'NGG-CHR-55E,CyberBot Connect,28,12.0,29.0,4.0,67.0,19,0.4186489058039962,190.43,364.14,OK', 'NGG-STE-ALA,AuraGlow P400,33,6.0,26.0,2.0,77.0,9,0.42816365366317793,155.43,375.77,OK']
{'key': 'inventory_reorder_csv', 'content': 'Product ID,Product Name,Current Stock,Reorder Point (ROP),Optimal Stock Level,Safety Stock,Days of Stock Remaining,Lead Time (days),Avg Daily Demand,Cost Price ($),Selling Price ($),Reorder Status\nNGG-AUR-16C,StellarMini Z9000,24,6.0,19.0,2.0,62.0,11,0.3882017126546147,370.76,717.97,OK\nNGG-NOV-E80,ChronoGlow P400,24,8.0,23.0,3.0,62.0,14,0.38534728829686016,243.35,631.25,OK\nNGG-CHR-55E,CyberBot Connect,28,12.0,29.0,4.0,67.0,19,0.4186489058039962,190.43,364.14,OK\nNGG-STE-ALA,AuraGlow P400,33,6.0,26.0,2.0,77.0,9,0.42816365366317793,155.43,375.77,OK\nNGG-ECO-DUS,ChronoDrive Light,38,7.0,17.0,2.0,99.0,11,0.3834443387250238,495.16,1078.54,OK\nNGG-NOV-UWH,EcoGlow AI,45,8.0,19.0,2.0,116.0,13,0.3882017126546147,396.4,1035.88,OK\nNGG-ECO-TJG,CyberCore Connect,47,8.0,24.0,3.0,128.0,15,0.36631779257849667,212.48,621.04,OK\nNGG-CYB-RLW,NovaGlow AI,70,8.0,20.0,3.0,163.0,11,0.4300666032350143,374.69,854.97,OK\nNGG-AUR-U9T,EcoMini S2,70,10.0,24.0,3.0,169.0,17,0.4148430066603235,306.82,510.95,OK\nNGG-ECO-99U,PulseMax X1,70,11.0,84.0,3.0,173.0,18,0.4043767840152236,10.9,26.92,OK\nNGG-ECO-IFA,ChronoBuds AI,70,7.0,27.0,3.0,178.0,12,0.3939105613701237,140.98,303.43,OK\nNGG-CYB-KY7,NovaGlow Stealth,63,9.0,32.0,3.0,178.0,17,0.35299714557564227,93.43,244.91,OK\nNGG-CHR-AAC,NovaBot Z9000,70,9.0,30.0,3.0,181.0,15,0.38629876308277833,117.73,202.77,OK\nNGG-CHR-825,ChronoPro Stealth,70,8.0,36.0,3.0,183.0,13,0.3834443387250238,72.48,177.72,OK\nNGG-STE-0DG,CyberMini P400,70,7.0,31.0,2.0,186.0,13,0.37678401522359656,100.62,240.07,OK\nNGG-QUA-UG0,AuraCore Light,70,7.0,24.0,2.0,187.0,12,0.3748810656517602,194.15,560.71,OK\nNGG-NOV-3XC,AuraBot Stealth,70,11.0,24.0,3.0,191.0,21,0.36726926736441484,335.25,543.06,OK\nNGG-STE-6QV,PulseWave AI,70,7.0,29.0,3.0,193.0,11,0.362511893434824,100.83,157.63,OK\nNGG-ECO-Q9A,EcoLens S2,92,6.0,20.0,3.0,261.0,10,0.3520456707897241,263.06,574.42,OK\nNGG-ECO-F48,PulseWave AI,0,9.0,28.0,3.0,0.0,13,0.4386298763082778,168.67,360.87,REORDER NOW\n', 'content_type': 'text/plain', 'metadata': {'created': '2025-05-19T07:18:46.564925'}, 'cell_id': '8b87fb8b-07ca-4a02-ba47-cf8bc9207cee', 'cell_version': '2025-05-19T14:18:46.365Z'}
1 asset references available in notebook

Finally, let's add a summary and conclusion to our analysis:

Added Python cell
Success
# Summary of the inventory analysis
print("INVENTORY ANALYSIS SUMMARY")
print("=" * 50)

# Count ...
INVENTORY ANALYSIS SUMMARY ================================================== Products requiring immediate reorder: 1 out of 20 Products with less than 60 days of stock: 1 out of 20 Total value of current inventory: $224739.62 Average days of stock remaining: 142.7 days Average supplier lead time: 13.8 days TOP PRIORITY PRODUCTS FOR REORDERING: -------------------------------------------------- URGENT: PulseWave AI (ID: NGG-ECO-F48) Current Stock: 0 units Reorder Point: 9.0 units Optimal Order Quantity: 28 units Lead Time: 13 days

Let's add a final markdown cell to summarize our findings and explain the formulas used:

Added Markdown cell
# Summary and Conclusions

## Key Findings

1. **Immediate Reordering Required**: 
   - **PulseWa...

I'll now create a final visualization to compare the current reorder point thresholds in the system with our calculated reorder points:

Added Python cell
Success
# Compare current reorder point thresholds with calculated reorder points
comparison_df = invento...
2025-05-19 07:19:38,953 - app.kernel_configuration.asset_store - WARNING - basic to_dict() resulted in referenced data for chart rop_comparison_chart: data-e1abe8c5ae9494c9c7198653ebc4106c
{'key': 'rop_comparison_chart', 'content': alt.Chart(...), 'content_type': 'application/vnd.vega.v5+json', 'metadata': {'created': '2025-05-19T07:19:38.949587', 'data_handling': 'referenced_basic_dict'}, 'cell_id': '35391b36-d377-47c0-ae1f-d0f6be022306', 'cell_version': '2025-05-19T14:19:38.695Z'}
1 asset references available in notebook

Let's add one final markdown cell to explain how to use the results of this analysis:

Added Markdown cell
# How to Use This Analysis

## Implementing the Results

1. **CSV Export**: 
   - The complete an...
May 19, 02:20 PM

can you make an cool/informational data visualuzation of the products

May 19, 02:21 PM
Vega

I'll create a more informative and visually appealing data visualization for the products. Let's add a new visualization that combines multiple metrics in an interesting way.

Added Python cell
Success
# Create an enhanced visualization that combines multiple metrics
# First, prepare the data with ...
2025-05-19 07:22:13,757 - app.kernel_configuration.asset_store - WARNING - basic to_dict() resulted in referenced data for chart product_quadrant_analysis: data-61760c1c92db069e5368403accfd1a36
{'key': 'product_quadrant_analysis', 'content': alt.LayerChart(...), 'content_type': 'application/vnd.vega.v5+json', 'metadata': {'created': '2025-05-19T07:22:13.744497', 'data_handling': 'referenced_basic_dict'}, 'cell_id': '4bc61397-2173-4782-af4c-2290519b5cc3', 'cell_version': '2025-05-19T14:22:12.482Z'}
2 asset references available in notebook

Let's create one more visualization that shows the inventory status in a more creative way:

Added Python cell
Success
# Create a radial visualization of inventory status
# First, prepare the data
radial_data = inven...
/var/folders/xm/3wltsxcd0wvf5c5fnw64z6dh0000gn/T/ipykernel_82895/305764824.py:135: FutureWarning: The default of observed=False is deprecated and will be changed to True in a future version of pandas. Pass observed=False to retain current behavior or observed=True to adopt the future default and silence this warning. category_status = heatmap_data.groupby(['category', 'status_category']).size().reset_index(name='count') 2025-05-19 07:22:49,297 - app.kernel_configuration.asset_store - WARNING - basic to_dict() resulted in referenced data for chart category_status_heatmap: data-b76be9925269c6588bdfa08ee80b6e64
{'key': 'category_status_heatmap', 'content': alt.LayerChart(...), 'content_type': 'application/vnd.vega.v5+json', 'metadata': {'created': '2025-05-19T07:22:49.294021', 'data_handling': 'referenced_basic_dict'}, 'cell_id': 'af1de240-12f4-4cde-abe5-bf6f8f581de0', 'cell_version': '2025-05-19T14:22:48.357Z'}
2 asset references available in notebook

Let's add a markdown cell to explain these new visualizations:

Added Markdown cell
# Advanced Product Inventory Visualizations

I've created several advanced visualizations to prov...
May 19, 02:23 PM