In [3]: import pandas as pd

df = pd.read_csv(r"C:\Users\sneha\Downloads\Data_Doyen_Rules_Dataset\blinkit_dataset.c

print([Link]())

product_id product_name category brand \

0 1 Tata Organic Grocery 300 Grocery Tata
1 2 Mother Dairy Lite Dairy 275 Dairy Mother Dairy
2 3 P&G Classic Personal 439 Personal Care P&G
3 4 Dettol Fresh Household 771 Household Dettol
4 5 Minute Maid Daily Beverages 264 Beverages Minute Maid

price discount_pct final_price rating num_reviews delivery_time_min \

0 199.78 25 149.84 4.5 146 37
1 44.32 30 31.02 4.0 264 36
2 501.13 0 501.13 3.7 69 17
3 627.17 0 627.17 3.9 103 23
4 101.69 15 86.44 4.3 422 10

... is_organic packaging_type weight_g shelf_life_days reorder_level \

0 ... True Can 750 212 15
1 ... False Jar 1000 17 24
2 ... True Jar 1000 1463 25
3 ... True Bottle 200 1143 18
4 ... True Can 300 363 30

demand_index date_added expiry_date offer_type delivery_status

0 73 27-11-2023 26-06-2024 None On-Time
1 25 7/8/2024 24-08-2024 None Delayed
2 100 3/3/2024 5/3/2028 FreeDelivery On-Time
3 15 7/8/2024 24-09-2027 None On-Time
4 6 4/7/2024 2/7/2025 None On-Time

[5 rows x 25 columns]

In [4]: df
Out[4]: product_id product_name category brand price discount_pct final_price

Tata Organic
0 1 Grocery Tata 199.78 25
Grocery 300

Mother Dairy Mother

1 2 Dairy 44.32 30
Lite Dairy 275 Dairy

P&G Classic Personal

2 3 P&G 501.13 0
Personal 439 Care

Dettol Fresh
3 4 Household Dettol 627.17 0
Household 771

Minute Maid
Minute
4 5 Daily Beverages 101.69 15
Maid
Beverages 264

... ... ... ... ... ... ...

Minute Maid
Minute
12995 12996 Classic Beverages 219.17 15
Maid
Beverages 396

FreshFarm
Fruits &
12996 12997 Organic Fruits FreshFarm 348.21 5
Vegetables
225

FreshFarm
Fruits &
12997 12998 Original Fruits FreshFarm 178.86 10
Vegetables
349

Lizol Lite
12998 12999 Household Lizol 435.70 5
Household 792

Dettol Organic
12999 13000 Household Dettol 230.42 0
Household 987

13000 rows × 25 columns

In [5]: result = [Link]("category").agg(

total_products=("product_id", "count"),
product_names=("product_name", list)
).reset_index()

print(result)
 category total_products \
0 Bakery 1713
1 Beverages 1561
2 Dairy 1662
3 Fruits & Vegetables 1588
4 Grocery 1700
5 Household 1560
6 Personal Care 1620
7 Snacks 1596

product_names
0 [Britannia Organic Bakery 192, Britannia Premi...
1 [Minute Maid Daily Beverages 264, Coca-Cola Or...
2 [Mother Dairy Lite Dairy 275, Mother Dairy Lit...
3 [FreshFarm Original Fruits 804, DailyGreens Li...
4 [Tata Organic Grocery 300, Aashirvaad Fresh Gr...
5 [Dettol Fresh Household 771, Surf Excel Classi...
6 [P&G Classic Personal 439, Nivea Family Pack P...
7 [Britannia Premium Snacks 284, ITC Premium Sna...

In [6]: result = result.sort_values("total_products", ascending=False)

print(result)

category total_products \
0 Bakery 1713
4 Grocery 1700
2 Dairy 1662
6 Personal Care 1620
7 Snacks 1596
3 Fruits & Vegetables 1588
1 Beverages 1561
5 Household 1560

product_names
0 [Britannia Organic Bakery 192, Britannia Premi...
4 [Tata Organic Grocery 300, Aashirvaad Fresh Gr...
2 [Mother Dairy Lite Dairy 275, Mother Dairy Lit...
6 [P&G Classic Personal 439, Nivea Family Pack P...
7 [Britannia Premium Snacks 284, ITC Premium Sna...
3 [FreshFarm Original Fruits 804, DailyGreens Li...
1 [Minute Maid Daily Beverages 264, Coca-Cola Or...
5 [Dettol Fresh Household 771, Surf Excel Classi...

In [7]: pivot = df.pivot_table(

index="category",
columns="packaging_type",
values="product_id",
aggfunc="count",
fill_value=0
)

print(pivot)
 packaging_type Bottle Box Can Jar Packet Pouch
category
Bakery 282 285 264 291 300 291
Beverages 267 269 244 261 261 259
Dairy 282 249 285 275 285 286
Fruits & Vegetables 275 267 280 248 246 272
Grocery 283 282 308 270 295 262
Household 264 278 255 250 249 264
Personal Care 277 276 275 252 262 278
Snacks 312 257 254 268 259 246

In [8]: result = [Link]("category").agg(

unique_product_types=("product_name", "nunique"),
product_names=("product_name", lambda x: sorted([Link]()))
).reset_index()

print(result)

category unique_product_types \
0 Bakery 1645
1 Beverages 1530
2 Dairy 1625
3 Fruits & Vegetables 1517
4 Grocery 1659
5 Household 1519
6 Personal Care 1572
7 Snacks 1550

product_names
0 [BakeHouse Classic Bakery 122, BakeHouse Class...
1 [Coca-Cola Classic Beverages 120, Coca-Cola Cl...
2 [Aavin Classic Dairy 111, Aavin Classic Dairy ...
3 [DailyGreens Classic Fruits 117, DailyGreens C...
4 [Aashirvaad Classic Grocery 148, Aashirvaad Cl...
5 [Dettol Classic Household 106, Dettol Classic ...
6 [Dove Classic Personal 107, Dove Classic Perso...
7 [Britannia Classic Snacks 100, Britannia Class...

In [9]: delayed = df[df["delivery_status"] == "Delayed"]

delayed
Out[9]: product_id product_name category brand price discount_pct final_pric

Mother Dairy Mother

1 2 Dairy 44.32 30
Lite Dairy 275 Dairy

Britannia
13 14 Family Pack Bakery Britannia 493.11 0
Bakery 876

Tata Lite
14 15 Grocery Tata 63.34 20
Grocery 795

Fortune
21 22 Organic Grocery Fortune 471.42 25
Grocery 168

Amul Classic
23 24 Dairy Amul 69.42 10
Dairy 954

... ... ... ... ... ... ...

DailyGreens
Fruits &
12977 12978 Classic Fruits DailyGreens 324.20 15
Vegetables
242

Pepsi Family
12978 12979 Pack Beverages Beverages Pepsi 300.32 5
388

Dove Daily Personal

12979 12980 Dove 567.40 20
Personal 391 Care

FreshFarm Lite Fruits &

12992 12993 FreshFarm 62.30 0
Fruits 109 Vegetables

Minute Maid
Minute
12993 12994 Fresh Beverages 46.33 10
Maid
Beverages 309

2605 rows × 25 columns

In [10]: delayed_details = delayed[[

"product_id",
"product_name",
"category",
"brand",
"final_price",
"rating",
"num_reviews",
"city",
"seller",
"delivery_time_min",
"stock",
"sold_quantity",
"profit_margin_pct"
]]
 print(delayed_details)

product_id product_name category \

1 2 Mother Dairy Lite Dairy 275 Dairy
13 14 Britannia Family Pack Bakery 876 Bakery
14 15 Tata Lite Grocery 795 Grocery
21 22 Fortune Organic Grocery 168 Grocery
23 24 Amul Classic Dairy 954 Dairy
... ... ... ...
12977 12978 DailyGreens Classic Fruits 242 Fruits & Vegetables
12978 12979 Pepsi Family Pack Beverages 388 Beverages
12979 12980 Dove Daily Personal 391 Personal Care
12992 12993 FreshFarm Lite Fruits 109 Fruits & Vegetables
12993 12994 Minute Maid Fresh Beverages 309 Beverages

brand final_price rating num_reviews city seller

\
1 Mother Dairy 31.02 4.0 264 Jaipur QuickStores
13 Britannia 493.11 4.8 124 Kolkata SellerA
14 Tata 50.67 3.7 196 Mumbai SellerB
21 Fortune 353.56 4.1 476 Mumbai UrbanSeller
23 Amul 62.48 4.4 462 Bengaluru LocalMart
... ... ... ... ... ... ...
12977 DailyGreens 275.57 3.9 149 Delhi DailyNeeds
12978 Pepsi 285.30 3.1 230 Bengaluru LocalMart
12979 Dove 453.92 3.4 225 Hyderabad DailyNeeds
12992 FreshFarm 62.30 4.9 213 Mumbai QuickStores
12993 Minute Maid 41.70 4.4 196 Lucknow QuickStores

delivery_time_min stock sold_quantity profit_margin_pct

1 36 122 28 15.2
13 31 92 410 23.8
14 26 94 201 12.9
21 34 131 132 18.5
23 31 87 77 33.4
... ... ... ... ...
12977 28 122 418 20.3
12978 27 91 124 22.1
12979 30 84 282 38.9
12992 32 141 259 22.9
12993 30 82 77 6.3

[2605 rows x 13 columns]

In [11]: import pandas as pd

delayed = df[df["delivery_status"] == "Delayed"][[

"demand_index",
"reorder_level",
"weight_g",
"packaging_type",
"profit_margin_pct",
"seller",
"city",
 "delivery_time_min",
"rating"
]]

delayed

Out[11]: demand_index reorder_level weight_g packaging_type profit_margin_pct

1 25 24 1000 Jar 15.2

13 100 18 750 Packet 23.8

14 43 18 750 Bottle 12.9

21 45 26 300 Can 18.5

23 23 17 500 Packet 33.4

... ... ... ... ... ...

12977 97 24 1000 Can 20.3

12978 47 18 1500 Packet 22.1

12979 68 16 200 Bottle 38.9

12992 55 28 2000 Pouch 22.9

12993 35 16 500 Pouch 6.3

2605 rows × 9 columns

In [12]: unique_seller_count = df["seller"].nunique()

print("Total unique sellers:", unique_seller_count)

seller_orders = [Link]("seller").size().reset_index(name="order_count")

seller_orders = seller_orders.sort_values("order_count", ascending=False)

print(seller_orders)

Total unique sellers: 6

seller order_count
0 DailyNeeds 2217
3 SellerA 2212
4 SellerB 2175
1 LocalMart 2154
2 QuickStores 2148
5 UrbanSeller 2094

In [13]: delayed_count = [Link]("seller").size().reset_index(name="delay_count"

delayed_count = delayed_count.sort_values("delay_count", ascending=False)

print(delayed_count)
 seller delay_count
0 DailyNeeds 457
4 SellerB 451
2 QuickStores 438
3 SellerA 432
5 UrbanSeller 418
1 LocalMart 409

In [14]: delayed_low_rating = delayed[delayed["rating"] < 3][[

"seller",
"city",
"delivery_time_min",
"demand_index",
"rating"
]]

delayed_low_rating

Out[14]: seller city delivery_time_min demand_index rating

200 SellerB Pune 26 66 2.8

468 DailyNeeds Mumbai 33 12 2.7

1083 LocalMart Hyderabad 25 51 2.5

1818 SellerB Lucknow 43 0 2.7

2324 QuickStores Jaipur 31 11 2.9

2992 SellerA Chennai 24 37 2.7

3894 UrbanSeller Delhi 22 54 2.9

4728 SellerB Kolkata 26 26 2.6

5254 SellerB Chennai 28 10 2.9

5522 SellerB Mumbai 40 49 2.8

5583 SellerA Chennai 29 13 2.9

5677 QuickStores Bengaluru 21 63 2.9

6071 SellerA Lucknow 24 17 2.6

6545 QuickStores Mumbai 30 28 2.9

9164 QuickStores Bengaluru 28 37 2.9

9824 LocalMart Jaipur 38 20 2.5

11288 SellerB Pune 31 56 2.9

12075 SellerB Hyderabad 28 2 2.8

12093 UrbanSeller Jaipur 33 18 2.9

12564 UrbanSeller Jaipur 34 51 2.6

In [15]: avg_rating_city = [Link]("city")["rating"].mean().reset_index(name="avg_rating_cit
print(avg_rating_city)

city avg_rating_city
0 Ahmedabad 4.182662
1 Bengaluru 4.188773
2 Chennai 4.181941
3 Delhi 4.210055
4 Hyderabad 4.212140
5 Jaipur 4.195469
6 Kolkata 4.200964
7 Lucknow 4.171515
8 Mumbai 4.232987
9 Pune 4.192437

In [16]: avg_rating_seller = [Link]("seller")["rating"].mean().reset_index(name="avg_rating

print(avg_rating_seller)

seller avg_rating_seller
0 DailyNeeds 4.200767
1 LocalMart 4.197818
2 QuickStores 4.186965
3 SellerA 4.194620
4 SellerB 4.197149
5 UrbanSeller 4.204394

In [17]: low_rated = df[df["rating"] < 3][["product_name", "seller", "city", "rating"]]

low_rated

Out[17]: product_name seller city rating

200 Britannia Premium Snacks 918 SellerB Pune 2.8

239 Tata Classic Grocery 854 QuickStores Lucknow 2.9

255 ITC Daily Snacks 369 DailyNeeds Mumbai 2.9

353 Parle Daily Snacks 861 DailyNeeds Bengaluru 2.7

468 Surf Excel Classic Household 385 DailyNeeds Mumbai 2.7

... ... ... ... ...

12351 Tata Family Pack Grocery 317 SellerA Jaipur 2.7

12564 BakeHouse Original Bakery 747 UrbanSeller Jaipur 2.6

12590 Dove Fresh Personal 607 SellerB Bengaluru 2.7

12835 Lizol Fresh Household 276 UrbanSeller Kolkata 2.8

12894 Harpic Organic Household 794 LocalMart Kolkata 2.9

90 rows × 4 columns

In [18]: city_breakdown = low_rated["city"].value_counts()

seller_breakdown = low_rated["seller"].value_counts()
 print(city_breakdown)
print(seller_breakdown)

Lucknow 17
Chennai 12
Jaipur 12
Pune 11
Bengaluru 8
Mumbai 7
Delhi 7
Ahmedabad 6
Kolkata 6
Hyderabad 4
Name: city, dtype: int64
LocalMart 20
SellerB 17
QuickStores 15
DailyNeeds 15
SellerA 15
UrbanSeller 8
Name: seller, dtype: int64

In [19]: df = pd.read_csv(r"C:\Users\sneha\Downloads\Data_Doyen_Rules_Dataset\blinkit_dataset.c

avg_delivery = df["delivery_time_min"].mean()
slow_orders = df[df["delivery_time_min"] > avg_delivery][["city", "seller", "delivery_

print(slow_orders)

city seller delivery_time_min

0 Bengaluru UrbanSeller 37
1 Jaipur QuickStores 36
6 Lucknow LocalMart 33
7 Bengaluru UrbanSeller 34
10 Mumbai SellerA 28
... ... ... ...
12979 Hyderabad DailyNeeds 30
12980 Mumbai SellerB 38
12992 Mumbai QuickStores 32
12993 Lucknow QuickStores 30
12995 Bengaluru DailyNeeds 30

[6462 rows x 3 columns]

In [20]: avg_delivery

Out[20]: 27.55876923076923

In [21]: slow_seller_count = slow_orders["seller"].value_counts()

# count by city
slow_city_count = slow_orders["city"].value_counts()
 print("Sellers causing delays:")
print(slow_seller_count)

print("\nCities suffering delays:")

print(slow_city_count)

Sellers causing delays:

DailyNeeds 1132
SellerA 1121
UrbanSeller 1067
LocalMart 1058
QuickStores 1057
SellerB 1027
Name: seller, dtype: int64

Cities suffering delays:

Lucknow 1154
Jaipur 1037
Mumbai 841
Ahmedabad 839
Bengaluru 664
Kolkata 566
Chennai 445
Delhi 377
Hyderabad 335
Pune 204
Name: city, dtype: int64

In [22]: df = pd.read_csv(r"C:\Users\sneha\Downloads\Data_Doyen_Rules_Dataset\blinkit_dataset.c
low_shelf = df[df['shelf_life_days'] <= 30].copy()

low_shelf.loc[:, 'expected_sales'] = (
low_shelf['demand_index'] + low_shelf['sold_quantity']
)

low_shelf.loc[:, 'estimated_waste'] = (
(low_shelf['stock'] - low_shelf['expected_sales']).clip(lower=0)
)

In [23]: low_shelf
Out[23]: product_id product_name category brand price discount_pct final_price

Mother Dairy Mother

1 2 Dairy 44.32 30
Lite Dairy 275 Dairy

Britannia
6 7 Organic Bakery Bakery Britannia 390.21 0
192

Britannia
7 8 Premium Bakery Britannia 370.21 10
Bakery 931

Britannia
13 14 Family Pack Bakery Britannia 493.11 0
Bakery 876

Mother Dairy Mother

16 17 Dairy 211.62 10
Lite Dairy 52 Dairy

... ... ... ... ... ... ...

Gowardhan Lite
12985 12986 Dairy Gowardhan 237.93 0
Dairy 154

Modern
12989 12990 Premium Bakery Modern 312.06 5
Bakery 148

FreshFarm Lite Fruits &

12992 12993 FreshFarm 62.30 0
Fruits 109 Vegetables

FreshFarm
Fruits &
12996 12997 Organic Fruits FreshFarm 348.21 5
Vegetables
225

FreshFarm
Fruits &
12997 12998 Original Fruits FreshFarm 178.86 10
Vegetables
349

4963 rows × 27 columns

In [ ]:

In [24]: import pandas as pd

from dateutil import parser

dates = [
"26-06-2024", "24-08-2024", "5/3/2028", "24-09-2027",
"2/7/2025", "13-02-2026", "15-06-2024", "29-11-2024",
"29-07-2025", "26-09-2025", "22-05-2027", "11/3/2026", "2/4/2026"
]

def parse_mixed_date(d):
if "-" in d:
# Treat as DD-MM-YYYY
return pd.to_datetime(d, dayfirst=True)
 elif "/" in d:
# Treat as MM/DD/YYYY
return pd.to_datetime(d, dayfirst=True)
else:
# Fallback
return [Link](d)

parsed_dates = [parse_mixed_date(d) for d in dates]

df = [Link]({"original": dates, "parsed": parsed_dates})

print(df)

original parsed
0 26-06-2024 2024-06-26
1 24-08-2024 2024-08-24
2 5/3/2028 2028-03-05
3 24-09-2027 2027-09-24
4 2/7/2025 2025-07-02
5 13-02-2026 2026-02-13
6 15-06-2024 2024-06-15
7 29-11-2024 2024-11-29
8 29-07-2025 2025-07-29
9 26-09-2025 2025-09-26
10 22-05-2027 2027-05-22
11 11/3/2026 2026-03-11
12 2/4/2026 2026-04-02

In [25]: import pandas as pd

df = pd.read_csv(r"C:\Users\sneha\Downloads\Data_Doyen_Rules_Dataset\blinkit_dataset.c

import pandas as pd
from dateutil import parser

def parse_mixed_date(d):
if [Link](d):
return [Link]
d = str(d).strip()
if "-" in d:

return pd.to_datetime(d, dayfirst=True, errors='coerce')

elif "/" in d:

return pd.to_datetime(d, dayfirst=True, errors='coerce')

else:

return [Link](d, dayfirst=True)

df['parsed_date_added'] = df['date_added'].apply(parse_mixed_date)
df['parsed_expiry_date'] = df['expiry_date'].apply(parse_mixed_date)

print(df[['date_added', 'parsed_date_added', 'expiry_date', 'parsed_expiry_date'

 date_added parsed_date_added expiry_date parsed_expiry_date
0 27-11-2023 2023-11-27 26-06-2024 2024-06-26
1 7/8/2024 2024-08-07 24-08-2024 2024-08-24
2 3/3/2024 2024-03-03 5/3/2028 2028-03-05
3 7/8/2024 2024-08-07 24-09-2027 2027-09-24
4 4/7/2024 2024-07-04 2/7/2025 2025-07-02
... ... ... ... ...
12995 16-01-2025 2025-01-16 13-05-2026 2026-05-13
12996 11/1/2024 2024-01-11 24-01-2024 2024-01-24
12997 8/8/2025 2025-08-08 11/8/2025 2025-08-11
12998 11/11/2023 2023-11-11 18-12-2024 2024-12-18
12999 7/7/2024 2024-07-07 7/1/2028 2028-01-07

[13000 rows x 4 columns]

In [26]: df['parsed_date_added'] = df['parsed_date_added'].[Link]('%d-%m-%Y')

df['parsed_expiry_date'] = df['parsed_expiry_date'].[Link]('%d-%m-%Y')

df.to_excel(r"C:\Users\sneha\Downloads\Data_Doyen_Rules_Dataset\blinkit_dataset_parsed
index=False)

In [40]: import pandas as pd

import numpy as np
from sklearn.model_selection import train_test_split
from [Link] import LabelEncoder
from [Link] import RandomForestRegressor
from [Link] import mean_absolute_error, r2_score
from [Link] import SimpleImputer

df = pd.read_csv(r"C:\Users\sneha\Downloads\Data_Doyen_Rules_Dataset\[Link]"

df['is_organic'] = df['is_organic'].astype(int)

df['parsed_date_added'] = (
pd.to_datetime(df['parsed_date_added'], errors='coerce', dayfirst=True)
)

df['parsed_expiry_date'] = (
pd.to_datetime(df['parsed_expiry_date'], errors='coerce', dayfirst=True)
)

df['days_to_expiry'] = (df['parsed_expiry_date'] - df['parsed_date_added']).dt.

df = [Link](subset=['sold_quantity'])

cat_cols = [
'product_name', 'category', 'brand', 'city', 'seller',
'packaging_type', 'offer_type', 'delivery_status'
]
encoders = {}
for col in cat_cols:
le = LabelEncoder()
df[col] = le.fit_transform(df[col].astype(str))
encoders[col] = le

target = 'sold_quantity'

features = [
'price', 'discount_pct', 'final_price',
'rating', 'num_reviews',
'delivery_time_min', 'stock',
'profit_margin_pct', 'is_organic',
'weight_g', 'shelf_life_days', 'reorder_level',
'demand_index', 'days_to_expiry'
] + cat_cols

imputer = SimpleImputer(strategy='median')
X = [Link](imputer.fit_transform(df[features]), columns=features)
y = df[target]

X_train, X_test, y_train, y_test = train_test_split(

X, y, test_size=0.2, random_state=42
)

model = RandomForestRegressor(
n_estimators=300,
random_state=42,
max_depth=12,
n_jobs=-1
)

[Link](X_train, y_train)

preds = [Link](X_test)
print("MAE:", mean_absolute_error(y_test, preds))
print("R2:", r2_score(y_test, preds))

future_df = [Link]()

future_imputed = [Link](
[Link](future_df[features]),
columns=features
)

future_predictions = [Link](future_imputed)
 future_df['predicted_future_sales'] = future_predictions

MAE: 35.07946955475486
R2: 0.8829500693406349

In [41]: print(future_df[['product_name', 'predicted_future_sales']])

product_name predicted_future_sales
0 12091 257.939636
1 9324 55.956802
2 9917 540.056363
3 4046 58.563122
4 8275 37.954470
... ... ...
12995 8223 267.534961
12996 5546 81.110589
12997 5657 89.961828
12998 8056 65.662178
12999 4154 70.437965

[13000 rows x 2 columns]

In [42]: grouped = future_df.groupby('product_name')['predicted_future_sales'].mean()

print(grouped)

product_name
0 301.419059
1 299.104814
2 50.920152
3 50.760529
4 180.135363
...
12612 41.064057
12613 191.409058
12614 160.126479
12615 71.814627
12616 477.367399
Name: predicted_future_sales, Length: 12617, dtype: float64

In [43]: future_df['decoded_product_name'] = encoders['product_name'].inverse_transform(

future_df['product_name']
)

print(future_df[['decoded_product_name', 'predicted_future_sales']])
 decoded_product_name predicted_future_sales
0 Tata Organic Grocery 300 257.939636
1 Mother Dairy Lite Dairy 275 55.956802
2 P&G Classic Personal 439 540.056363
3 Dettol Fresh Household 771 58.563122
4 Minute Maid Daily Beverages 264 37.954470
... ... ...
12995 Minute Maid Classic Beverages 396 267.534961
12996 FreshFarm Organic Fruits 225 81.110589
12997 FreshFarm Original Fruits 349 89.961828
12998 Lizol Lite Household 792 65.662178
12999 Dettol Organic Household 987 70.437965

[13000 rows x 2 columns]

In [45]: future_df['decoded_product_name'] = encoders['product_name'].inverse_transform(

future_df['product_name']
)

future_df['expected_sales_before_expiry'] = future_df['predicted_future_sales']

future_df['expected_waste'] = future_df['stock'] - future_df['expected_sales_before_ex

future_df['expected_waste'] = future_df['expected_waste'].clip(lower=0)

print(future_df[['decoded_product_name', 'stock', 'days_to_expiry',

'predicted_future_sales', 'expected_sales_before_expiry',
'expected_waste']])
future_df.to_csv("[Link]", index=False)
 decoded_product_name stock days_to_expiry \
0 Tata Organic Grocery 300 76 212
1 Mother Dairy Lite Dairy 275 122 17
2 P&G Classic Personal 439 126 1463
3 Dettol Fresh Household 771 92 1143
4 Minute Maid Daily Beverages 264 152 363
... ... ... ...
12995 Minute Maid Classic Beverages 396 141 482
12996 FreshFarm Organic Fruits 225 95 13
12997 FreshFarm Original Fruits 349 101 3
12998 Lizol Lite Household 792 123 403
12999 Dettol Organic Household 987 93 1279

predicted_future_sales expected_sales_before_expiry expected_waste

0 257.939636 257.939636 0.000000
1 55.956802 55.956802 66.043198
2 540.056363 540.056363 0.000000
3 58.563122 58.563122 33.436878
4 37.954470 37.954470 114.045530
... ... ... ...
12995 267.534961 267.534961 0.000000
12996 81.110589 81.110589 13.889411
12997 89.961828 89.961828 11.038172
12998 65.662178 65.662178 57.337822
12999 70.437965 70.437965 22.562035

[13000 rows x 6 columns]

In [32]: import pandas as pd

import numpy as np
from sklearn.model_selection import train_test_split
from [Link] import RandomForestRegressor
from [Link] import SimpleImputer
from [Link] import LabelEncoder
from [Link] import mean_absolute_error, r2_score

PATH =r"C:\Users\sneha\Downloads\Data_Doyen_Rules_Dataset\[Link]"

df = pd.read_csv(PATH)

total_orders = len(df)
total_sellers = df['seller'].nunique()
total_cities = df['city'].nunique()
total_skus = df['product_id'].nunique()

delayed = df[df['delivery_status'].[Link]() == 'delayed']

if len(delayed)==0:
raise SystemExit("No delayed rows found. Check delivery_status values.")

seller_delay_counts = delayed['seller'].value_counts()
top5_seller_delays = seller_delay_counts.head(5).sum()
pct_delays_top5_sellers = 100 * top5_seller_delays / len(delayed)

city_delay_counts = delayed['city'].value_counts()
top3_city_delays = city_delay_counts.head(3).sum()
pct_delays_top3_cities = 100 * top3_city_delays / len(delayed)

avg_delivery = df['delivery_time_min'].mean()
orders_above_avg = df[df['delivery_time_min'] > avg_delivery]
pct_above_avg = 100 * len(orders_above_avg) / total_orders

low_shelf = df[df['shelf_life_days'] <= 30].copy()

if len(low_shelf)>0:
low_shelf['expected_sales'] = (low_shelf['demand_index'].fillna(0) + low_shelf
low_shelf['estimated_waste'] = (low_shelf['stock'].fillna(0) - low_shelf['expected
total_perish_stock = low_shelf['stock'].sum()
total_est_waste = low_shelf['estimated_waste'].sum()
pct_waste_perish = 100 * total_est_waste / (total_perish_stock if total_perish_sto
else:
pct_waste_perish = 0.0
total_est_waste = 0
total_perish_stock = 0

low_rated = df[df['rating'] < 3]

if len(low_rated)>0:
low_rate_seller_counts = low_rated['seller'].value_counts()
top4_lowrating_sellers = low_rate_seller_counts.head(4).sum()
pct_lowrating_top4_sellers = 100 * top4_lowrating_sellers / len(low_rated)
else:
pct_lowrating_top4_sellers = 0.0

mae = None
r2 = None
pct_skus_pred_exceed_stock = None
if 'sold_quantity' in [Link] and df['sold_quantity'].notnull().sum() > 10:

use_cols = ['price','discount_pct','final_price','rating','num_reviews',
'delivery_time_min','stock','profit_margin_pct','is_organic',
'weight_g','shelf_life_days','reorder_level','demand_index']

use_cols = [c for c in use_cols if c in [Link]]

df_model = [Link](subset=['sold_quantity'])

cat_cols = [c for c in ['product_name','category','brand','city','seller','packagi

encoders = {}
for c in cat_cols:
le = LabelEncoder()
df_model[c] = le.fit_transform(df_model[c].astype(str))
encoders[c] = le
use_cols.append(c)
X = df_model[use_cols].fillna(df_model[use_cols].median())
y = df_model['sold_quantity']
imputer = SimpleImputer(strategy='median')
X_imp = imputer.fit_transform(X)
X_train, X_test, y_train, y_test = train_test_split(X_imp, y, test_size=0.2
model = RandomForestRegressor(n_estimators=100, random_state=42, n_jobs=-1)
[Link](X_train, y_train)
 preds = [Link](X_test)
mae = mean_absolute_error(y_test, preds)
r2 = r2_score(y_test, preds)

X_all = [Link](df_model[use_cols])
all_preds = [Link](X_all)
df_model['predicted_future_sales'] = all_preds
compare = df_model.groupby('product_id').agg(stock=('stock','first'),
predicted=('predicted_future_sales'
exceed_count = (compare['predicted'] > compare['stock']).sum()
pct_skus_pred_exceed_stock = 100 * exceed_count / len(compare) if len(compare
lines = []
[Link](f"{pct_delays_top5_sellers:.1f}% of all delays come from top 5 sellers (
[Link](f"{pct_delays_top3_cities:.1f}% of delays are concentrated in top 3 citie
[Link](f"Average delivery time = {avg_delivery:.1f} min; {pct_above_avg:.1f
[Link](f"Perishables (shelf<=30d) estimated waste = {total_est_waste:.0f}
[Link](f"{pct_lowrating_top4_sellers:.1f}% of low-rated products come from top 4
if mae is not None:
[Link](f"Model MAE = {mae:.2f} units, R2 = {r2:.2f}.")
if pct_skus_pred_exceed_stock is not None:
[Link](f"{pct_skus_pred_exceed_stock:.1f}% of SKUs predicted demand exceed c

for L in lines:
print(L)

summary = {
'total_orders': total_orders,
'total_sellers': total_sellers,
'total_cities': total_cities,
'total_skus': total_skus,
'delayed_count': len(delayed),
'top5_seller_delays': top5_seller_delays,
'pct_delays_top5_sellers': pct_delays_top5_sellers,
'top3_city_delays': top3_city_delays,
'pct_delays_top3_cities': pct_delays_top3_cities,
'avg_delivery_min': avg_delivery,
'pct_above_avg_delivery': pct_above_avg,
'perish_stock': total_perish_stock,
'est_waste_perish': total_est_waste,
'pct_waste_perish': pct_waste_perish,
'pct_lowrating_top4_sellers': pct_lowrating_top4_sellers,
'mae': mae,
'r2': r2,
'pct_skus_pred_exceed_stock': pct_skus_pred_exceed_stock
}
[Link]([summary]).to_csv("blinkit_slide_summary.csv", index=False)
print("\nSaved blinkit_slide_summary.csv with metrics.")
 84.3% of all delays come from top 5 sellers (2196 delayed orders).
41.8% of delays are concentrated in top 3 cities (1088 delayed orders).
Average delivery time = 27.6 min; 49.7% of orders exceed this.
Perishables (shelf<=30d) estimated waste = 98233 units (18.0% of perish stock).
74.4% of low-rated products come from top 4 sellers.
Model MAE = 35.31 units, R2 = 0.88.
52.3% of SKUs predicted demand exceed current stock (risking stockouts).

Saved blinkit_slide_summary.csv with metrics.

In [ ]:

In [ ]:

In [ ]:

In [ ]:

In [ ]:

In [ ]:

In [ ]:

In [ ]:

In [ ]:

In [ ]:

In [ ]:

In [ ]:

In [ ]:

In [ ]:

In [ ]:

In [ ]:

In [ ]:

In [ ]:

In [ ]:
In [ ]:

In [ ]:

In [ ]:

In [ ]:

In [ ]:

In [ ]:

In [ ]:

In [ ]:

In [ ]:

In [ ]: