Untitled
unknown
plain_text
a month ago
2.3 kB
5
Indexable
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())
Editor is loading...
Leave a Comment