Prompt for AI Data Analyst: The Excellent Store Challenge
1. The Business Context

You are a Data Analyst for "The Excellent Store," a major retail chain in Nigeria. The CEO plans to expand in 2025 and needs to understand which products and stores are most profitable. Your task is to analyze transactional data and build a predictive model to forecast the profit returns for any given product in a specific store.

2. Your Primary Objective

Your goal is to build a machine learning model that accurately predicts the Item_Store_Returns.

Evaluation Metric: The model's performance will be judged based on the Root Mean Squared Error (RMSE). A lower RMSE is better.
3. Dataset Description

You will be working with three datasets: train.csv, test.csv, and SampleSubmission.csv.

A. train.csv - Training Data
This file contains historical data with the following columns:

Item_ID: (Text) A unique identifier for each product (e.g., 'DRA12').
Item_Weight: (Numeric) The weight of the product.
Item_Sugar_Content: (Text) The level of sugar in the product (e.g., 'Low Sugar', 'Normal Sugar').
Item_Visibility: (Numeric) The percentage of display area in the store allocated to this specific product.
Item_Type: (Text) The category of the product (e.g., 'Soft Drinks', 'Baking Goods').
Item_Price: (Numeric) The retail price of the product.
Store_ID: (Text) A unique identifier for each store (e.g., 'BABATUNJI010').
Store_Start_Year: (Numeric) The year the store was established (e.g., 2005).
Store_Size: (Text) The size of the store, categorized as 'Small', 'Medium', or 'High'.
Store_Location_Type: (Text) The type of area where the store is located (e.g., 'Cluster 1', 'Cluster 2').
Store_Type: (Text) A more detailed description of the store (e.g., 'Grocery Store', 'Supermarket Type1').
Item_Store_ID: (Text) A unique key combining the Item_ID and Store_ID (e.g., 'DRA12_BABATUNJI010').
Item_Store_Returns: (Numeric) The total profit returns for the item in that store. This is the target variable you need to predict.
B. test.csv - Testing Data
This file has the exact same structure and columns as train.csv, except it does not contain the Item_Store_Returns column. You will predict the values for this dataset.

C. SampleSubmission.csv - Submission Format
The final output file must have two columns:

Item_Store_ID: The unique identifier from the test set.
Item_Store_Returns: Your model's predicted profit returns for that ID.
4. Important Data Characteristics and Known Issues

My initial analysis revealed several key issues you must handle:

Missing Item_Weight: A significant number of products have missing weight values. Hint: A product's weight should be the same regardless of the store it's in.
Missing Store_Size: Certain stores are missing a size classification. Hint: The size might be related to the Store_Type.
Inconsistent Item_Sugar_Content: This categorical feature has inconsistent values like 'Low Sugar' and 'Ultra Low Sugar'. These should be standardized.
Zero Item_Visibility: Some products have a visibility of 0, which is unrealistic for a product on display. Treat these as missing values that need to be filled. Hint: The visibility for the same product might be similar across different stores.
5. Step-by-Step Instructions

Please perform the following tasks in order:

Data Cleaning and Preprocessing:
Load the training and testing datasets.
Address the known issues listed in Section 4 by imputing missing values and standardizing categories.
Feature Engineering:
Create a new feature called Store_Age from the Store_Start_Year column (assume the current year is 2025).
Create a new, broader Item_Category feature by extracting the first two letters from the Item_ID (e.g., 'DR' for Drinks, 'FD' for Food, 'NC' for Non-Consumable).
Encode all categorical features into a numerical format suitable for a machine learning model (e.g., using One-Hot Encoding).
Model Development:
Separate the features (X) from the target variable (y) in the training data.
Select a robust regression model. A GradientBoostingRegressor or RandomForestRegressor from the Scikit-learn library are excellent choices.
Train your chosen model on the fully preprocessed training data.
Prediction and Submission:
Make predictions on the preprocessed test data.
Generate a submission.csv file in the format specified by SampleSubmission.csv.
Please provide the complete Python code to accomplish these tasks.