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
| Column | Type | Nullable | Primary |
|---|---|---|---|
| ORDER_ID | VARCHAR(16777216) | No | No |
| CUSTOMER_ID | VARCHAR(16777216) | No | No |
| ORDER_DATE | TIMESTAMP_NTZ(9) | No | No |
| ORDER_STATUS | VARCHAR(16777216) | No | No |
| SHIPPING_STREET_ADDRESS | VARCHAR(16777216) | No | No |
| SHIPPING_CITY | VARCHAR(16777216) | No | No |
| SHIPPING_STATE | VARCHAR(16777216) | No | No |
| SHIPPING_ZIP_CODE | VARCHAR(16777216) | No | No |
| SHIPPING_METHOD | VARCHAR(16777216) | No | No |
| PAYMENT_METHOD_USED | VARCHAR(16777216) | No | No |
| PROMO_CODE_USED | VARCHAR(16777216) | No | No |
| SUBTOTAL_AMOUNT | FLOAT | No | No |
| DISCOUNT_AMOUNT | FLOAT | No | No |
| SHIPPING_COST | FLOAT | No | No |
| TAX_AMOUNT | FLOAT | No | No |
| TOTAL_ORDER_AMOUNT | FLOAT | No | No |
order_items10 rows
| Column | Type | Nullable | Primary |
|---|---|---|---|
| ORDER_ITEM_ID | VARCHAR(16777216) | No | No |
| ORDER_ID | VARCHAR(16777216) | No | No |
| PRODUCT_ID | VARCHAR(16777216) | No | No |
| QUANTITY | NUMBER(38,0) | No | No |
| PRICE_PER_UNIT_AT_PURCHASE | FLOAT | No | No |
| TOTAL_ITEM_PRICE | FLOAT | No | No |
product_inventory10 rows
| Column | Type | Nullable | Primary |
|---|---|---|---|
| INVENTORY_ID | VARCHAR(16777216) | No | No |
| PRODUCT_ID | VARCHAR(16777216) | No | No |
| CURRENT_STOCK_LEVEL | NUMBER(38,0) | No | No |
| REORDER_POINT_THRESHOLD | NUMBER(38,0) | No | No |
| TARGET_STOCK_LEVEL | NUMBER(38,0) | No | No |
| SUPPLIER_LEAD_TIME_DAYS | NUMBER(38,0) | No | No |
| LAST_RESTOCK_DATE | DATE | No | No |
| LAST_STOCK_CHECK_DATE | TIMESTAMP_NTZ(9) | No | No |
products10 rows
| Column | Type | Nullable | Primary |
|---|---|---|---|
| PRODUCT_ID | VARCHAR(16777216) | No | No |
| PRODUCT_NAME | VARCHAR(16777216) | No | No |
| CATEGORY | VARCHAR(16777216) | No | No |
| SUPPLIER_NAME | VARCHAR(16777216) | No | No |
| COST_PRICE | FLOAT | No | No |
| SELLING_PRICE | FLOAT | No | No |
| DESCRIPTION | VARCHAR(16777216) | No | No |
| DATE_ADDED | DATE | No | No |
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...OUTPUT:
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...OUTPUT:
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...OUTPUT:
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...OUTPUT:
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...OUTPUT:
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...OUTPUT:
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...OUTPUT:
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...OUTPUT:
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)
#...OUTPUT:
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...OUTPUT:
TypeError: altair.vegalite.v5.api._Conditional() got multiple values for keyword argument 'condition'
[0;31m---------------------------------------------------------------------------[0m
[0;31mTypeError[0m Traceback (most recent call last)
Cell [0;32mIn[22], line 5[0m
[1;32m 1[0m [38;5;66;03m# Create a visualization for days of stock remaining[39;00m
[1;32m 2[0m days_chart [38;5;241m=[39m alt[38;5;241m.[39mChart(inventory_analysis)[38;5;241m.[39mmark_bar()[38;5;241m.[39mencode(
[1;32m 3[0m x[38;5;241m=[39malt[38;5;241m.[39mX([38;5;124m'[39m[38;5;124mproduct_name:N[39m[38;5;124m'[39m, sort[38;5;241m=[39m[38;5;124m'[39m[38;5;124m-y[39m[38;5;124m'[39m, title[38;5;241m=[39m[38;5;124m'[39m[38;5;124mProduct Name[39m[38;5;124m'[39m),
[1;32m 4[0m y[38;5;241m=[39malt[38;5;241m.[39mY([38;5;124m'[39m[38;5;124mdays_of_stock_remaining:Q[39m[38;5;124m'[39m, title[38;5;241m=[39m[38;5;124m'[39m[38;5;124mDays of Stock Remaining[39m[38;5;124m'[39m),
[0;32m----> 5[0m color[38;5;241m=[39m[43malt[49m[38;5;241;43m.[39;49m[43mcondition[49m[43m([49m
[1;32m 6[0m [43m [49m[43malt[49m[38;5;241;43m.[39;49m[43mdatum[49m[38;5;241;43m.[39;49m[43mdays_of_stock_remaining[49m[43m [49m[38;5;241;43m<[39;49m[43m [49m[38;5;241;43m30[39;49m[43m,[49m
[1;32m 7[0m [43m [49m[43malt[49m[38;5;241;43m.[39;49m[43mvalue[49m[43m([49m[38;5;124;43m'[39;49m[38;5;124;43mred[39;49m[38;5;124;43m'[39;49m[43m)[49m[43m,[49m[43m [49m[38;5;66;43;03m# less than 30 days[39;49;00m
[1;32m 8[0m [43m [49m[43malt[49m[38;5;241;43m.[39;49m[43mcondition[49m[43m([49m
[1;32m 9[0m [43m [49m[43malt[49m[38;5;241;43m.[39;49m[43mdatum[49m[38;5;241;43m.[39;49m[43mdays_of_stock_remaining[49m[43m [49m[38;5;241;43m<[39;49m[43m [49m[38;5;241;43m60[39;49m[43m,[49m
[1;32m 10[0m [43m [49m[43malt[49m[38;5;241;43m.[39;49m[43mvalue[49m[43m([49m[38;5;124;43m'[39;49m[38;5;124;43morange[39;49m[38;5;124;43m'[39;49m[43m)[49m[43m,[49m[43m [49m[38;5;66;43;03m# between 30 and 60 days[39;49;00m
[1;32m 11[0m [43m [49m[43malt[49m[38;5;241;43m.[39;49m[43mvalue[49m[43m([49m[38;5;124;43m'[39;49m[38;5;124;43mgreen[39;49m[38;5;124;43m'[39;49m[43m)[49m[43m [49m[38;5;66;43;03m# more than 60 days[39;49;00m
[1;32m 12[0m [43m [49m[43m)[49m
[1;32m 13[0m [43m [49m[43m)[49m,
[1;32m 14[0m tooltip[38;5;241m=[39m[[38;5;124m'[39m[38;5;124mproduct_name[39m[38;5;124m'[39m, [38;5;124m'[39m[38;5;124mdays_of_stock_remaining[39m[38;5;124m'[39m, [38;5;124m'[39m[38;5;124mcurrent_stock_level[39m[38;5;124m'[39m, [38;5;124m'[39m[38;5;124mavg_daily_demand[39m[38;5;124m'[39m]
[1;32m 15[0m )[38;5;241m.[39mproperties(
[1;32m 16[0m title[38;5;241m=[39m[38;5;124m'[39m[38;5;124mDays of Stock Remaining by Product[39m[38;5;124m'[39m,
[1;32m 17[0m width[38;5;241m=[39m[38;5;241m600[39m,
[1;32m 18[0m height[38;5;241m=[39m[38;5;241m400[39m
[1;32m 19[0m )
[1;32m 21[0m [38;5;66;03m# Add reference lines for 30 and 60 days[39;00m
[1;32m 22[0m thirty_day_rule [38;5;241m=[39m alt[38;5;241m.[39mChart(pd[38;5;241m.[39mDataFrame({[38;5;124m'[39m[38;5;124my[39m[38;5;124m'[39m: [[38;5;241m30[39m]}))[38;5;241m.[39mmark_rule(
[1;32m 23[0m color[38;5;241m=[39m[38;5;124m'[39m[38;5;124morange[39m[38;5;124m'[39m,
[1;32m 24[0m strokeDash[38;5;241m=[39m[[38;5;241m4[39m, [38;5;241m4[39m]
[1;32m 25[0m )[38;5;241m.[39mencode(y[38;5;241m=[39m[38;5;124m'[39m[38;5;124my:Q[39m[38;5;124m'[39m)
File [0;32m~/Library/Caches/pypoetry/virtualenvs/jupyter-kernel-server-yp2e7biK-py3.12/lib/python3.12/site-packages/altair/vegalite/v5/api.py:1889[0m, in [0;36mcondition[0;34m(predicate, if_true, if_false, empty, **kwargs)[0m
[1;32m 1861[0m [38;5;250m[39m[38;5;124;03m"""[39;00m
[1;32m 1862[0m [38;5;124;03mA conditional attribute or encoding.[39;00m
[1;32m 1863[0m
[0;32m (...)[0m
[1;32m 1886[0m [38;5;124;03m the spec that describes the condition[39;00m
[1;32m 1887[0m [38;5;124;03m"""[39;00m
[1;32m 1888[0m condition [38;5;241m=[39m _predicate_to_condition(predicate, empty[38;5;241m=[39mempty)
[0;32m-> 1889[0m [38;5;28;01mreturn[39;00m [43m_condition_to_selection[49m[43m([49m[43mcondition[49m[43m,[49m[43m [49m[43mif_true[49m[43m,[49m[43m [49m[43mif_false[49m[43m,[49m[43m [49m[38;5;241;43m*[39;49m[38;5;241;43m*[39;49m[43mkwargs[49m[43m)[49m
File [0;32m~/Library/Caches/pypoetry/virtualenvs/jupyter-kernel-server-yp2e7biK-py3.12/lib/python3.12/site-packages/altair/vegalite/v5/api.py:629[0m, in [0;36m_condition_to_selection[0;34m(condition, if_true, if_false, **kwargs)[0m
[1;32m 627[0m if_false [38;5;241m=[39m utils[38;5;241m.[39mparse_shorthand(if_false)
[1;32m 628[0m if_false[38;5;241m.[39mupdate(kwargs)
[0;32m--> 629[0m selection [38;5;241m=[39m _Conditional(condition[38;5;241m=[39mcond_mutable, [38;5;241m*[39m[38;5;241m*[39mif_false) [38;5;66;03m# type: ignore[typeddict-item][39;00m
[1;32m 630[0m [38;5;28;01melse[39;00m:
[1;32m 631[0m [38;5;28;01mraise[39;00m [38;5;167;01mTypeError[39;00m(if_false)
[0;31mTypeError[0m: 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...OUTPUT:
{'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', '...OUTPUT:
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...OUTPUT:
['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 ...OUTPUT:
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...OUTPUT:
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...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 ...OUTPUT:
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...OUTPUT:
/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...