final_list_price_estimation.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "8307b314-a95a-4cf9-8d85-831529d9d018",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from sklearn.model_selection import train_test_split\n",
    "from sklearn.preprocessing import OneHotEncoder\n",
    "from sklearn.impute import SimpleImputer\n",
    "from sklearn.compose import ColumnTransformer\n",
    "from sklearn.pipeline import Pipeline\n",
    "from sklearn.ensemble import RandomForestRegressor\n",
    "from sklearn.model_selection import GridSearchCV\n",
    "from sklearn.metrics import mean_squared_error\n",
    "from xgboost import XGBRegressor\n",
    "import matplotlib.pyplot as plt\n",
    "from sklearn.tree import DecisionTreeClassifier\n",
    "from sklearn.model_selection import train_test_split\n",
    "from sklearn.metrics import accuracy_score\n",
    "from sklearn.metrics import mean_absolute_error,  r2_score\n",
    "from sklearn.metrics import mean_absolute_error, mean_squared_error\n",
    "import numpy as np\n",
    "import joblib"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "a6b20e5e-4322-4273-89fb-b7d4f01bf117",
   "metadata": {},
   "outputs": [],
   "source": [
    "test_data = pd.read_csv('cvs_wg_pns_train.csv')\n",
    "predict_data = pd.read_csv('cvs_wg_pns_predict_on.csv')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "204ce675-5761-4993-a61d-67166ae9156e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "___________________________________________________________________________________________________________\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 18067 entries, 0 to 18066\n",
      "Data columns (total 10 columns):\n",
      " #   Column        Non-Null Count  Dtype  \n",
      "---  ------        --------------  -----  \n",
      " 0   Description   18067 non-null  object \n",
      " 1   UPC           18067 non-null  float64\n",
      " 2   Promo_Price   3022 non-null   float64\n",
      " 3   Retail_Price  18067 non-null  float64\n",
      " 4   List_Price    18067 non-null  float64\n",
      " 5   Manufacturer  18067 non-null  object \n",
      " 6   Category      17969 non-null  object \n",
      " 7   Count         17969 non-null  float64\n",
      " 8   Multiplier    17968 non-null  float64\n",
      " 9   Retailer      18067 non-null  object \n",
      "dtypes: float64(6), object(4)\n",
      "memory usage: 1.4+ MB\n",
      "None\n"
     ]
    }
   ],
   "source": [
    "# print(\"___________________________________________________________________________________________________________\")\n",
    "# print(test_data.head())\n",
    "print(\"___________________________________________________________________________________________________________\")\n",
    "print(test_data.info())\n",
    "# print(\"___________________________________________________________________________________________________________\")\n",
    "# print(test_data.describe())\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "497cf46d-3411-4bf7-b1b6-0282578b2a1b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Promo_Price    15045\n",
      "Category          98\n",
      "Count             98\n",
      "Multiplier        99\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# Identify columns with missing values\n",
    "missing_values_summary = test_data.isnull().sum()\n",
    "\n",
    "# Filter out columns with no missing values\n",
    "columns_with_missing_values = missing_values_summary[missing_values_summary > 0]\n",
    "\n",
    "# Print the columns with missing values and their counts\n",
    "print(columns_with_missing_values)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "id": "fe1deaef-d3e3-4521-8257-f879e1d0e908",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Count_Binned\n",
      "0-1            988\n",
      "1-10          2327\n",
      "10-50          982\n",
      "50-100           7\n",
      "100-500          5\n",
      "500-1000         0\n",
      "1000-5000        0\n",
      "5000-10000       0\n",
      "Name: count, dtype: int64\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\balaj\\AppData\\Local\\Temp\\ipykernel_8368\\4064914552.py:9: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  non_whole_counts['Count_Binned'] = pd.cut(non_whole_counts['Count'], bins=bins, labels=labels, include_lowest=True)\n"
     ]
    }
   ],
   "source": [
    "\n",
    "# Creating bins for granular distribution\n",
    "bins = [0, 1, 10, 50, 100, 500, 1000, 5000, 10000]\n",
    "labels = ['0-1', '1-10', '10-50', '50-100', '100-500', '500-1000', '1000-5000', '5000-10000']\n",
    "\n",
    "# Filtering non-whole number 'Count' values\n",
    "non_whole_counts = test_data[~test_data['Count'].isnull() & ~test_data['Count'].apply(float.is_integer)]\n",
    "\n",
    "# Binning the non-whole number 'Count' values\n",
    "non_whole_counts['Count_Binned'] = pd.cut(non_whole_counts['Count'], bins=bins, labels=labels, include_lowest=True)\n",
    "\n",
    "# Getting the distribution of non-whole number 'Count' values\n",
    "non_whole_count_distribution = non_whole_counts['Count_Binned'].value_counts().sort_index()\n",
    "\n",
    "# Display the distribution\n",
    "print(non_whole_count_distribution)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "c830d786-804d-468b-ada4-bd36ad579cbb",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total number of records with missing Count values: 98\n",
      "Number of records with missing Count and missing Category values: 98\n",
      "Number of records with missing Count and missing Multiplier values: 98\n",
      "Hypothesis is validated: All records with missing 'Count' also have missing 'Category' and 'Multiplier' values.\n"
     ]
    }
   ],
   "source": [
    "# Filter records where 'Count' is missing\n",
    "missing_count_records = test_data[test_data['Count'].isnull()]\n",
    "\n",
    "# Check for missing 'Category' and 'Multiplier' in the filtered records\n",
    "missing_category_count = missing_count_records['Category'].isnull().sum()\n",
    "missing_multiplier_count = missing_count_records['Multiplier'].isnull().sum()\n",
    "\n",
    "# Get the total number of records with missing 'Count' value\n",
    "total_missing_count_records = len(missing_count_records)\n",
    "\n",
    "print(f'Total number of records with missing Count values: {total_missing_count_records}')\n",
    "print(f'Number of records with missing Count and missing Category values: {missing_category_count}')\n",
    "print(f'Number of records with missing Count and missing Multiplier values: {missing_multiplier_count}')\n",
    "\n",
    "# Validate the hypothesis\n",
    "if missing_category_count == total_missing_count_records and missing_multiplier_count == total_missing_count_records:\n",
    "    print(\"Hypothesis is validated: All records with missing 'Count' also have missing 'Category' and 'Multiplier' values.\")\n",
    "else:\n",
    "    print(\"Hypothesis is not validated: Some records with missing 'Count' have 'Category' or 'Multiplier' values.\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "c2b0e3ff-8a14-45e4-bbfe-3b7e14593bb9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of records with missing Count values after imputation: 0\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\balaj\\AppData\\Local\\Temp\\ipykernel_14328\\2295471164.py:2: FutureWarning: A value is trying to be set on a copy of a DataFrame or Series through chained assignment using an inplace method.\n",
      "The behavior will change in pandas 3.0. This inplace method will never work because the intermediate object on which we are setting values always behaves as a copy.\n",
      "\n",
      "For example, when doing 'df[col].method(value, inplace=True)', try using 'df.method({col: value}, inplace=True)' or df[col] = df[col].method(value) instead, to perform the operation inplace on the original object.\n",
      "\n",
      "\n",
      "  train = test_data['Count'].fillna(0, inplace=True)\n"
     ]
    }
   ],
   "source": [
    "# Impute missing values in 'Count' with 0\n",
    "train = test_data['Count'].fillna(0, inplace=True)\n",
    "\n",
    "# Verify the imputation\n",
    "missing_count_abs = test_data['Count'].isnull().sum()\n",
    "print(f'Number of records with missing Count values after imputation: {missing_count_abs}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "b1290d5e-ae0d-4541-b5c9-3aa1c41587c6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Impute 'Count' values: set to zero where 'Count' has decimal values\n",
    "test_data['Count'] = test_data['Count'].apply(lambda x: 0 if x % 1 != 0 else x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "id": "91f358c7-2baf-476a-a630-dba862d453ba",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0     1.0\n",
      "1     8.0\n",
      "2    20.0\n",
      "3     8.0\n",
      "4    12.0\n",
      "Name: Count, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "# # Round off the 'Count' column values to the nearest whole number\n",
    "# test_data['Count'] = test_data['Count'].round()\n",
    "\n",
    "# # Verify the changes\n",
    "# print(test_data['Count'].head())\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "078de137-e57b-459d-8dcc-41945adbe5c4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Accuracy of the Category prediction model: 0.64\n",
      "Number of records with missing Category values after imputation: 0\n"
     ]
    }
   ],
   "source": [
    "# Separate data into records with and without 'Category'\n",
    "data_with_category = test_data[test_data['Category'].notnull()]\n",
    "data_without_category = test_data[test_data['Category'].isnull()]\n",
    "\n",
    "# Features and target for the prediction model\n",
    "features = ['Retail_Price', 'Promo_Price', 'Count', 'Manufacturer']\n",
    "target = 'Category'\n",
    "\n",
    "# One-hot encode categorical features\n",
    "data_with_category_encoded = pd.get_dummies(data_with_category[features])\n",
    "data_without_category_encoded = pd.get_dummies(data_without_category[features])\n",
    "\n",
    "# Ensure both datasets have the same dummy variables\n",
    "data_with_category_encoded, data_without_category_encoded = data_with_category_encoded.align(data_without_category_encoded, join='left', axis=1, fill_value=0)\n",
    "\n",
    "# Train a Decision Tree Classifier to predict 'Category'\n",
    "X = data_with_category_encoded\n",
    "y = data_with_category[target]\n",
    "X_train, X_val, y_train, y_val = train_test_split(X, y, test_size=0.2, random_state=42)\n",
    "\n",
    "clf = DecisionTreeClassifier(random_state=42)\n",
    "clf.fit(X_train, y_train)\n",
    "\n",
    "# Predict missing 'Category' values\n",
    "predicted_categories = clf.predict(data_without_category_encoded)\n",
    "\n",
    "# Predict and evaluate on the validation set\n",
    "y_pred_val = clf.predict(X_val)\n",
    "accuracy = accuracy_score(y_val, y_pred_val)\n",
    "print(f'Accuracy of the Category prediction model: {accuracy:.2f}')\n",
    "\n",
    "# Impute the missing values\n",
    "test_data.loc[test_data['Category'].isnull(), 'Category'] = predicted_categories\n",
    "\n",
    "# # Save the imputed dataset to a new CSV file (optional)\n",
    "# test_data.to_csv('cvs_wg_pns_train_category_imputed_final.csv', index=False)\n",
    "\n",
    "# Verify the imputation\n",
    "missing_category_count = test_data['Category'].isnull().sum()\n",
    "print(f'Number of records with missing Category values after imputation: {missing_category_count}')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "73c4d7c1-8fa4-4ab9-acc0-a6ada5d82704",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of records with empty Count values: 0\n",
      "Number of records with zero Count values: 4408\n",
      "Total number of records with empty or zero Count values: 4408\n"
     ]
    }
   ],
   "source": [
    "\n",
    "# Calculate the number of records with empty or zero 'Count' values\n",
    "empty_count = test_data['Count'].isnull().sum()\n",
    "zero_count = (test_data['Count'] == 0).sum()\n",
    "total_empty_or_zero = empty_count + zero_count\n",
    "\n",
    "print(f'Number of records with empty Count values: {empty_count}')\n",
    "print(f'Number of records with zero Count values: {zero_count}')\n",
    "print(f'Total number of records with empty or zero Count values: {total_empty_or_zero}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "c80773fa-542b-4cf8-ac94-b670aa74d10e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of records with empty Count values after imputation: 0\n",
      "Number of records with zero Count values after imputation: 4408\n",
      "Total number of records with empty or zero Count values after imputation: 4408\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\balaj\\AppData\\Local\\Temp\\ipykernel_14328\\3095401499.py:2: FutureWarning: A value is trying to be set on a copy of a DataFrame or Series through chained assignment using an inplace method.\n",
      "The behavior will change in pandas 3.0. This inplace method will never work because the intermediate object on which we are setting values always behaves as a copy.\n",
      "\n",
      "For example, when doing 'df[col].method(value, inplace=True)', try using 'df.method({col: value}, inplace=True)' or df[col] = df[col].method(value) instead, to perform the operation inplace on the original object.\n",
      "\n",
      "\n",
      "  test_data['Count'].fillna(0, inplace=True)\n"
     ]
    }
   ],
   "source": [
    "\n",
    "# # Impute missing (NaN) 'Count' values with 0\n",
    "# test_data['Count'].fillna(0, inplace=True)\n",
    "\n",
    "# # Ensure zero 'Count' values remain as 0 (already handled by the fillna step)\n",
    "# test_data.loc[test_data['Count'] == 0, 'Count'] = 0\n",
    "\n",
    "# # # Save the imputed dataset to a new CSV file (optional)\n",
    "# # test_data.to_csv('path_to_your_data/cvs_wg_pns_train_imputed.csv', index=False)\n",
    "\n",
    "# # Verify the imputation\n",
    "# empty_count = test_data['Count'].isnull().sum()\n",
    "# zero_count = (test_data['Count'] == 0).sum()\n",
    "# total_empty_or_zero = empty_count + zero_count\n",
    "\n",
    "# print(f'Number of records with empty Count values after imputation: {empty_count}')\n",
    "# print(f'Number of records with zero Count values after imputation: {zero_count}')\n",
    "# print(f'Total number of records with empty or zero Count values after imputation: {total_empty_or_zero}')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "id": "9ca58ef3-f823-47ba-bfe3-67fe4c77ac98",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   Retail_Price  Count  Multiplier\n",
      "0         17.99    1.0      17.990\n",
      "1          8.29    8.0      66.320\n",
      "2          4.90   20.0      98.000\n",
      "3          5.99    8.0      50.915\n",
      "4         18.99   12.0     227.880\n",
      "Missing Multiplier values before imputation: 99\n",
      "Missing Multiplier values after imputation: 0\n"
     ]
    }
   ],
   "source": [
    "\n",
    "# # Calculate the number of missing values for the Multiplier column before imputation\n",
    "# missing_multiplier_before = test_data['Multiplier'].isnull().sum()\n",
    "\n",
    "# # Calculate the Multiplier for records with missing Multiplier values\n",
    "# test_data['Multiplier'] = np.where(test_data['Multiplier'].isnull(), \n",
    "#                                    test_data['Retail_Price'] * test_data['Count'], \n",
    "#                                    test_data['Multiplier'])\n",
    "\n",
    "# # Calculate the number of missing values for the Multiplier column after imputation\n",
    "# missing_multiplier_after = test_data['Multiplier'].isnull().sum()\n",
    "\n",
    "\n",
    "\n",
    "# # # Save the modified dataset to a new CSV file (optional)\n",
    "# # test_data.to_csv('path_to_your_data/cvs_wg_pns_train_with_interactions.csv', index=False)\n",
    "\n",
    "# # Verify the new features\n",
    "# print(test_data[['Retail_Price', 'Count', 'Multiplier']].head())\n",
    "\n",
    "# # Output the number of missing values before and after imputation\n",
    "# print(f'Missing Multiplier values before imputation: {missing_multiplier_before}')\n",
    "# print(f'Missing Multiplier values after imputation: {missing_multiplier_after}')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "3ca55e54-7d0e-4c96-a108-90e7f95b4783",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Mean Absolute Error (MAE): 0.74\n",
      "Mean Squared Error (MSE): 2.30\n",
      "Root Mean Squared Error (RMSE): 1.52\n",
      "Number of records with missing Promo_Price values after imputation: 0\n"
     ]
    }
   ],
   "source": [
    "\n",
    "\n",
    "# # Load the training data\n",
    "# test_data = pd.read_csv('path_to_your_data/cvs_wg_pns_train.csv')\n",
    "\n",
    "# Separate data into records with and without 'Promo_Price'\n",
    "data_with_promo_price = test_data[test_data['Promo_Price'].notnull()]\n",
    "data_without_promo_price = test_data[test_data['Promo_Price'].isnull()]\n",
    "\n",
    "# Features and target for the prediction model\n",
    "features = ['Retail_Price', 'Count', 'Manufacturer', 'Category']\n",
    "target = 'Promo_Price'\n",
    "\n",
    "# One-hot encode categorical features\n",
    "data_with_promo_price_encoded = pd.get_dummies(data_with_promo_price[features])\n",
    "\n",
    "# Train a Random Forest Regressor to predict 'Promo_Price'\n",
    "X = data_with_promo_price_encoded\n",
    "y = data_with_promo_price[target]\n",
    "X_train, X_val, y_train, y_val = train_test_split(X, y, test_size=0.2, random_state=42)\n",
    "\n",
    "regressor = RandomForestRegressor(random_state=42)\n",
    "regressor.fit(X_train, y_train)\n",
    "\n",
    "# Predict on the validation set\n",
    "y_pred_val = regressor.predict(X_val)\n",
    "\n",
    "# Calculate evaluation metrics\n",
    "mae = mean_absolute_error(y_val, y_pred_val)\n",
    "mse = mean_squared_error(y_val, y_pred_val)\n",
    "rmse = np.sqrt(mse)\n",
    "\n",
    "print(f'Mean Absolute Error (MAE): {mae:.2f}')\n",
    "print(f'Mean Squared Error (MSE): {mse:.2f}')\n",
    "print(f'Root Mean Squared Error (RMSE): {rmse:.2f}')\n",
    "\n",
    "# Predict missing 'Promo_Price' values\n",
    "data_without_promo_price_encoded = pd.get_dummies(data_without_promo_price[features])\n",
    "data_without_promo_price_encoded = data_without_promo_price_encoded.reindex(columns=data_with_promo_price_encoded.columns, fill_value=0)\n",
    "predicted_promo_prices = regressor.predict(data_without_promo_price_encoded)\n",
    "\n",
    "# Impute the missing values\n",
    "test_data.loc[test_data['Promo_Price'].isnull(), 'Promo_Price'] = predicted_promo_prices\n",
    "\n",
    "# # Save the imputed dataset to a new CSV file (optional)\n",
    "# test_data.to_csv('path_to_your_data/cvs_wg_pns_train_promo_price_imputed_model.csv', index=False)\n",
    "\n",
    "# Verify the imputation\n",
    "missing_promo_price_count = test_data['Promo_Price'].isnull().sum()\n",
    "print(f'Number of records with missing Promo_Price values after imputation: {missing_promo_price_count}')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "195ed776-e30e-4a41-bca0-c60f56193021",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "___________________________________________________________________________________________________________\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 18067 entries, 0 to 18066\n",
      "Data columns (total 10 columns):\n",
      " #   Column        Non-Null Count  Dtype  \n",
      "---  ------        --------------  -----  \n",
      " 0   Description   18067 non-null  object \n",
      " 1   UPC           18067 non-null  float64\n",
      " 2   Promo_Price   18067 non-null  float64\n",
      " 3   Retail_Price  18067 non-null  float64\n",
      " 4   List_Price    18067 non-null  float64\n",
      " 5   Manufacturer  18067 non-null  object \n",
      " 6   Category      18067 non-null  object \n",
      " 7   Count         18067 non-null  float64\n",
      " 8   Multiplier    17968 non-null  float64\n",
      " 9   Retailer      18067 non-null  object \n",
      "dtypes: float64(6), object(4)\n",
      "memory usage: 1.4+ MB\n",
      "None\n"
     ]
    }
   ],
   "source": [
    "# print(\"___________________________________________________________________________________________________________\")\n",
    "# print(test_data.head())\n",
    "print(\"___________________________________________________________________________________________________________\")\n",
    "print(test_data.info())\n",
    "# print(\"___________________________________________________________________________________________________________\")\n",
    "# print(test_data.describe())\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "d20b9f8d-0bcd-4aef-9f69-3f6c5cac60ae",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of records with missing Category values after imputation: 0\n"
     ]
    }
   ],
   "source": [
    "# Verify the imputation\n",
    "missing_category_count = test_data['Category'].isnull().sum()\n",
    "print(f'Number of records with missing Category values after imputation: {missing_category_count}')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "69e751a9-da99-4253-899a-b2149ce5f545",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of records with missing Category values: 0\n",
      "No missing Category values to impute.\n"
     ]
    }
   ],
   "source": [
    "# # import pandas as pd\n",
    "# # from sklearn.model_selection import train_test_split\n",
    "# # from sklearn.tree import DecisionTreeClassifier\n",
    "# # from sklearn.metrics import accuracy_score\n",
    "\n",
    "# # # Load the training data\n",
    "# # test_data = pd.read_csv('path_to_your_data/cvs_wg_pns_train.csv')\n",
    "\n",
    "# # Check for missing values in 'Category'\n",
    "# missing_category_count = test_data['Category'].isnull().sum()\n",
    "# print(f'Number of records with missing Category values: {missing_category_count}')\n",
    "\n",
    "# if missing_category_count == 0:\n",
    "#     print(\"No missing Category values to impute.\")\n",
    "# else:\n",
    "#     # Separate data into records with and without 'Category'\n",
    "#     data_with_category = test_data[test_data['Category'].notnull()]\n",
    "#     data_without_category = test_data[test_data['Category'].isnull()]\n",
    "\n",
    "#     # Features and target for the prediction model\n",
    "#     features = ['Retail_Price', 'Promo_Price', 'Count', 'Manufacturer']\n",
    "#     target = 'Category'\n",
    "\n",
    "#     # One-hot encode categorical features\n",
    "#     data_with_category_encoded = pd.get_dummies(data_with_category[features])\n",
    "#     data_without_category_encoded = pd.get_dummies(data_without_category[features])\n",
    "\n",
    "#     # Ensure both datasets have the same dummy variables\n",
    "#     data_with_category_encoded, data_without_category_encoded = data_with_category_encoded.align(data_without_category_encoded, join='left', axis=1, fill_value=0)\n",
    "\n",
    "#     # Debugging: Check the shapes of the encoded data\n",
    "#     print(f'Shape of data_with_category_encoded: {data_with_category_encoded.shape}')\n",
    "#     print(f'Shape of data_without_category_encoded: {data_without_category_encoded.shape}')\n",
    "\n",
    "#     # Check if data_without_category_encoded is empty\n",
    "#     if data_without_category_encoded.shape[0] == 0:\n",
    "#         print(\"No data to predict. Exiting.\")\n",
    "#     else:\n",
    "#         # Train a Decision Tree Classifier to predict 'Category'\n",
    "#         X = data_with_category_encoded\n",
    "#         y = data_with_category[target]\n",
    "#         X_train, X_val, y_train, y_val = train_test_split(X, y, test_size=0.2, random_state=42)\n",
    "\n",
    "#         clf = DecisionTreeClassifier(random_state=42)\n",
    "#         clf.fit(X_train, y_train)\n",
    "\n",
    "#         # Predict missing 'Category' values\n",
    "#         predicted_categories = clf.predict(data_without_category_encoded)\n",
    "\n",
    "#         # Predict and evaluate on the validation set\n",
    "#         y_pred_val = clf.predict(X_val)\n",
    "#         accuracy = accuracy_score(y_val, y_pred_val)\n",
    "#         print(f'Accuracy of the Category prediction model: {accuracy:.2f}')\n",
    "\n",
    "#         # Impute the missing values\n",
    "#         test_data.loc[test_data['Category'].isnull(), 'Category'] = predicted_categories\n",
    "\n",
    "#         # Verify the imputation\n",
    "#         missing_category_count_after = test_data['Category'].isnull().sum()\n",
    "#         print(f'Number of records with missing Category values after imputation: {missing_category_count_after}')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "2b74a4a4-3ca5-4371-a2ce-374e5055c98d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The mode of the Category column is: HAIR CARE - SHAMPOO/CONDITIONER\n",
      "Number of records with missing Category values after imputation: 0\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\balaj\\AppData\\Local\\Temp\\ipykernel_8368\\1710511053.py:6: FutureWarning: A value is trying to be set on a copy of a DataFrame or Series through chained assignment using an inplace method.\n",
      "The behavior will change in pandas 3.0. This inplace method will never work because the intermediate object on which we are setting values always behaves as a copy.\n",
      "\n",
      "For example, when doing 'df[col].method(value, inplace=True)', try using 'df.method({col: value}, inplace=True)' or df[col] = df[col].method(value) instead, to perform the operation inplace on the original object.\n",
      "\n",
      "\n",
      "  train_data['Category'].fillna(mode_category, inplace=True)\n"
     ]
    }
   ],
   "source": [
    "# # Calculate the mode of the 'Category' column\n",
    "# mode_category = test_data['Category'].mode()[0]\n",
    "# print(f'The mode of the Category column is: {mode_category}')\n",
    "\n",
    "# # Impute missing values in 'Category' with the mode\n",
    "# test_data['Category'].fillna(mode_category, inplace=True)\n",
    "\n",
    "# # Verify the imputation\n",
    "# missing_category_count = test_data['Category'].isnull().sum()\n",
    "# print(f'Number of records with missing Category values after imputation: {missing_category_count}')\n",
    "\n",
    "# # # Save the imputed dataset to a new CSV file (optional)\n",
    "# # test_data.to_csv('path_to_your_data/cvs_wg_pns_train_category_imputed_mode.csv', index=False)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "id": "37d6c7ee-11d0-4a10-8fce-a467fbf0d775",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "___________________________________________________________________________________________________________\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 18067 entries, 0 to 18066\n",
      "Data columns (total 10 columns):\n",
      " #   Column        Non-Null Count  Dtype  \n",
      "---  ------        --------------  -----  \n",
      " 0   Description   18067 non-null  object \n",
      " 1   UPC           18067 non-null  float64\n",
      " 2   Promo_Price   18067 non-null  float64\n",
      " 3   Retail_Price  18067 non-null  float64\n",
      " 4   List_Price    18067 non-null  float64\n",
      " 5   Manufacturer  18067 non-null  object \n",
      " 6   Category      18067 non-null  object \n",
      " 7   Count         18067 non-null  float64\n",
      " 8   Multiplier    18067 non-null  float64\n",
      " 9   Retailer      18067 non-null  object \n",
      "dtypes: float64(6), object(4)\n",
      "memory usage: 1.4+ MB\n",
      "None\n"
     ]
    }
   ],
   "source": [
    "# print(\"___________________________________________________________________________________________________________\")\n",
    "# print(test_data.head())\n",
    "print(\"___________________________________________________________________________________________________________\")\n",
    "print(test_data.info())\n",
    "# print(\"___________________________________________________________________________________________________________\")\n",
    "# print(test_data.describe())\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "89645066-909f-4ed3-a316-53c52a5baa87",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Mean Absolute Error (MAE): 0.92\n",
      "Mean Squared Error (MSE): 3.30\n",
      "Root Mean Squared Error (RMSE): 1.82\n",
      "R-squared (R2): 0.96\n"
     ]
    }
   ],
   "source": [
    "# import pandas as pd\n",
    "# from sklearn.model_selection import train_test_split\n",
    "# from sklearn.ensemble import RandomForestRegressor\n",
    "# from sklearn.metrics import mean_absolute_error, mean_squared_error, r2_score\n",
    "# import numpy as np\n",
    "# import joblib\n",
    "\n",
    "# # Load the prepared data\n",
    "# test_data = pd.read_csv('path_to_your_data/cvs_wg_pns_train.csv')\n",
    "\n",
    "# Prepare the data\n",
    "features = ['Retail_Price', 'Promo_Price', 'Count', 'Manufacturer', 'Category']\n",
    "target = 'List_Price'\n",
    "\n",
    "# One-hot encode categorical features\n",
    "test_data_encoded = pd.get_dummies(test_data[features])\n",
    "\n",
    "# Separate features and target variable\n",
    "X = test_data_encoded\n",
    "y = test_data[target]\n",
    "\n",
    "# Split the data into training and validation sets\n",
    "X_train, X_val, y_train, y_val = train_test_split(X, y, test_size=0.2, random_state=42)\n",
    "\n",
    "# Train a Random Forest Regressor\n",
    "regressor = RandomForestRegressor(random_state=42)\n",
    "regressor.fit(X_train, y_train)\n",
    "\n",
    "# Predict on the validation set\n",
    "y_pred_val = regressor.predict(X_val)\n",
    "\n",
    "# Calculate evaluation metrics\n",
    "mae = mean_absolute_error(y_val, y_pred_val)\n",
    "mse = mean_squared_error(y_val, y_pred_val)\n",
    "rmse = np.sqrt(mse)\n",
    "r2 = r2_score(y_val, y_pred_val)\n",
    "\n",
    "print(f'Mean Absolute Error (MAE): {mae:.2f}')\n",
    "print(f'Mean Squared Error (MSE): {mse:.2f}')\n",
    "print(f'Root Mean Squared Error (RMSE): {rmse:.2f}')\n",
    "print(f'R-squared (R2): {r2:.2f}')\n",
    "\n",
    "# Optionally, save the trained model\n",
    "joblib.dump(regressor, 'random_forest_model.joblib')\n",
    "\n",
    "# Save the predictions along with validation data to a new CSV file (optional)\n",
    "validation_results = X_val.copy()\n",
    "validation_results['Actual_List_Price'] = y_val\n",
    "validation_results['Predicted_List_Price'] = y_pred_val\n",
    "# validation_results.to_csv('path_to_your_data/validation_results.csv', index=False)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "65f11554-d586-4479-a596-2fcc755c91a4",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "f52956c4-29aa-4970-8477-83fbcc2bbfe8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 18067 entries, 0 to 18066\n",
      "Data columns (total 10 columns):\n",
      " #   Column                Non-Null Count  Dtype  \n",
      "---  ------                --------------  -----  \n",
      " 0   Description           18067 non-null  object \n",
      " 1   UPC                   18067 non-null  float64\n",
      " 2   Promo_Price           3022 non-null   float64\n",
      " 3   Retail_Price          18067 non-null  float64\n",
      " 4   Manufacturer          18067 non-null  object \n",
      " 5   Category              17969 non-null  object \n",
      " 6   Count                 18067 non-null  float64\n",
      " 7   Multiplier            17968 non-null  float64\n",
      " 8   Retailer              18067 non-null  object \n",
      " 9   Predicted_List_Price  18067 non-null  float64\n",
      "dtypes: float64(6), object(4)\n",
      "memory usage: 1.4+ MB\n",
      "None\n"
     ]
    }
   ],
   "source": [
    "print(predict_data.info())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "01c7ca49-1bbe-4099-aae7-28840abc38c9",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}