2_merge_preprocess.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# Load item datasets\n",
    "df1 = pd.read_csv('Items/Items/CDs_and_Vinyls_items_processed.csv')\n",
    "df2 = pd.read_csv('Items/Items/Digital_Music_items_processed.csv')\n",
    "df3 = pd.read_csv('Items/Items/Magazine_Subscriptions_items_processed.csv')\n",
    "df4 = pd.read_csv('Items/Items/Movies_and_TV_items_processed.csv')\n",
    "df5 = pd.read_csv('Items/Items/Musical_Instruments_items_processed.csv')\n",
    "df6 = pd.read_csv('Items/Items/Sports_and_Outdoors_items_processed.csv', low_memory=False)\n",
    "df7 = pd.read_csv('Items/Items/Toys_and_Games_items_processed.csv')\n",
    "df8 = pd.read_csv('Items/Items/Video_Games_items_processed.csv')\n",
    "\n",
    "# Update 'Main Category' column\n",
    "df1['main_category'] = 'CDs and Vinyls'\n",
    "df2['main_category'] = 'Digital Music'\n",
    "df3['main_category'] = 'Magazine Subscriptions'\n",
    "df4['main_category'] = 'Movies and TV'\n",
    "df5['main_category'] = 'Musical Instruments'\n",
    "df6['main_category'] = 'Sports and Outdoors'\n",
    "df7['main_category'] = 'Toys and Games'\n",
    "df8['main_category'] = 'Video Games'\n",
    "\n",
    "# Combine item datasets\n",
    "items_df = pd.concat([df1, df2, df3, df4, df5, df6, df7, df8], ignore_index=True)\n",
    "\n",
    "# Handle missing values\n",
    "items_df = items_df.dropna()\n",
    "\n",
    "# Drop duplicates\n",
    "items_df = items_df.drop_duplicates()\n",
    "\n",
    "# Ensure correct data types\n",
    "items_df['item_average_rating'] = items_df['item_average_rating'].astype(float)\n",
    "items_df['item_rating_number'] = items_df['item_rating_number'].astype(int)\n",
    "items_df['price'] = pd.to_numeric(items_df['price'], errors='coerce')\n",
    "items_df['price'] = items_df['price'].astype(float)\n",
    "\n",
    "# Drop unnecessary columns\n",
    "items_df = items_df.drop(columns=['description', 'store', 'details', 'features','item_average_rating', 'item_rating_number'])\n",
    "\n",
    "# Check validity of data\n",
    "items_df = items_df[items_df['price'] > 0]\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Missing values:\n",
      " rating                 0\n",
      "review_title         285\n",
      "review_text          357\n",
      "asin                   0\n",
      "parent_asin            0\n",
      "user_id                0\n",
      "date                   0\n",
      "helpful_vote           0\n",
      "verified_purchase      0\n",
      "main_category          0\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# Load review datasets in a similar manner\n",
    "reviews_df1 = pd.read_csv('Reviews/Reviews/CDs_and_Vinyls_reviews_processed.csv')\n",
    "reviews_df2 = pd.read_csv('Reviews/Reviews/Digital_Music_reviews_processed.csv')\n",
    "reviews_df3 = pd.read_csv('Reviews/Reviews/Magazine_Subscriptions_reviews_processed.csv')\n",
    "reviews_df4 = pd.read_csv('Reviews/Reviews/Movies_and_TV__reviews_processed.csv')\n",
    "reviews_df5 = pd.read_csv('Reviews/Reviews/Musical_Instruments_reviews_processed.csv')\n",
    "reviews_df6 = pd.read_csv('Reviews/Reviews/Sports_and_Outdoors_reviews_processed.csv', low_memory=False)\n",
    "reviews_df7 = pd.read_csv('Reviews/Reviews/Toys_and_Games_reviews_processed.csv')\n",
    "reviews_df8 = pd.read_csv('Reviews/Reviews/Video_Games_reviews_processed.csv')\n",
    "\n",
    "\n",
    "# Combine review datasets\n",
    "reviews_df = pd.concat([reviews_df1, reviews_df2, reviews_df3, reviews_df4, reviews_df5, reviews_df6, reviews_df7, reviews_df8], ignore_index=True)\n",
    "\n",
    "# Check for missing values\n",
    "missing_values = reviews_df.isnull().sum()\n",
    "print(\"Missing values:\\n\", missing_values)\n",
    "\n",
    "#drop any missing and incorrect value\n",
    "reviews_df = reviews_df.dropna()\n",
    "#rating must be between 0 and 5\n",
    "reviews_df = reviews_df[reviews_df['rating'] <= 5]\n",
    "reviews_df = reviews_df[reviews_df['rating'] >= 0]\n",
    "#review title must be string and not empty\n",
    "reviews_df = reviews_df[reviews_df['review_title'].apply(lambda x: isinstance(x, str))]\n",
    "reviews_df = reviews_df[reviews_df['review_title'].apply(lambda x: len(x) > 0)]\n",
    "\n",
    "# Check for and remove duplicates\n",
    "reviews_df = reviews_df.drop_duplicates()\n",
    "\n",
    "# Ensure correct data types\n",
    "reviews_df['rating'] = reviews_df['rating'].astype(float)\n",
    "reviews_df['helpful_vote'] = reviews_df['helpful_vote'].astype(int)\n",
    "reviews_df['verified_purchase'] = reviews_df['verified_purchase'].astype(bool)\n",
    "reviews_df['review_title'] = reviews_df['review_title'].astype(str)\n",
    "\n",
    "# Convert date to datetime format\n",
    "reviews_df['date'] = pd.to_datetime(reviews_df['date'])\n",
    "\n",
    "# Drop unnecessary columns\n",
    "reviews_df = reviews_df.drop(columns=['asin','review_text','main_category'])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "items_df.to_csv('items_processed.csv', index=False)\n",
    "\n",
    "reviews_df.to_csv('reviews_processed.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "#merge the two datasets\n",
    "merged_df = pd.merge(reviews_df,items_df, on='parent_asin', how='left')\n",
    "\n",
    "merged_df = merged_df.dropna()\n",
    "\n",
    "merged_df.to_csv('merged_data.csv', index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": ".venv",
   "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": 2
}