Untitled

import pandas as pd

# Load CSV files
sales_forecast = pd.read_csv("monthly_sales_forecast.csv")
products = pd.read_csv("product_table.csv")
categories = pd.read_csv("product_category_table.csv")
brands = pd.read_csv("brand_table.csv")
suppliers = pd.read_csv("supplier_table.csv")

# Select relevant columns
sales_forecast = sales_forecast[["ProductKey", "EstimatedUnitVolume", "PredictedPrice", "EstimatedSales"]]
products = products[["ProductKey", "ProductCategory_Lvl1", "ProductCategory_Lvl2", "BrandKey", "SupplierKey", "Margin", "Elasticity"]]
categories = categories[["ProductCategory_Lvl1", "ProductCategory_Lvl2", "Est_Emission_Int"]]
brands = brands[["BrandKey", "Est_Emission_Int"]]
suppliers = suppliers[["SupplierKey", "Distance", "Est_Emission_Int"]]

# Merge datasets
merged_df = sales_forecast.merge(products, on="ProductKey", how="left")
merged_df = merged_df.merge(categories, on=["ProductCategory_Lvl1", "ProductCategory_Lvl2"], how="left")
merged_df = merged_df.merge(brands, on="BrandKey", how="left", suffixes=("_Category", "_Brand"))
merged_df = merged_df.merge(suppliers, on="SupplierKey", how="left", suffixes=("", "_Supplier"))

# Apply Best Sellers Promotion (10% Discount)
discount = 0.10
merged_df["DiscountApplied"] = discount

# Apply price elasticity formula: New Sales = Old Sales * (1 + Elasticity * Discount)
merged_df["SalesUplift"] = merged_df["EstimatedUnitVolume"] * (1 + merged_df["Elasticity"] * discount)

# Calculate revenue before & after discount
merged_df["RevenueBefore"] = merged_df["EstimatedUnitVolume"] * merged_df["PredictedPrice"]
merged_df["RevenueAfter"] = merged_df["SalesUplift"] * merged_df["PredictedPrice"] * (1 - discount)

# Calculate profit after discount
merged_df["ProfitAfter"] = merged_df["RevenueAfter"] - (merged_df["SalesUplift"] * merged_df["Margin"])

# Calculate emissions and offset costs
merged_df["TotalEmissions"] = merged_df["SalesUplift"] * merged_df["Est_Emission_Int_Brand"]
merged_df["OffsetCost"] = merged_df["TotalEmissions"] * 0.25  # Assumed offset cost per kg CO2

# Save final dataset to CSV
merged_df.to_csv("final_best_sellers_promotion.csv", index=False)

# Display the first few rows of the final dataset
print(merged_df.head())