ETL_alexa_rating_2018.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h1>ETL Project: Amazon Alexa Product Ratings\n",
    "<h4>This ETL (Extract, Transform, Load) project focuses on analyzing Amazon Alexa product ratings from 2018. The project aims to clean and structure the data, calculate average ratings for each product, and load the results into a SQLite database.\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h2>Extract"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h4>Loading the file and extracting the columns that we are interested in: \"rating\" and \"variation\"."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>rating</th>\n",
       "      <th>variation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5</td>\n",
       "      <td>Charcoal Fabric</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5</td>\n",
       "      <td>Charcoal Fabric</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>Walnut Finish</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>Charcoal Fabric</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Charcoal Fabric</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   rating         variation\n",
       "0       5  Charcoal Fabric \n",
       "1       5  Charcoal Fabric \n",
       "2       4    Walnut Finish \n",
       "3       5  Charcoal Fabric \n",
       "4       5  Charcoal Fabric "
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "csv_file_path = \"data/amazon_alexa.csv\"\n",
    "\n",
    "data = pd.read_csv(csv_file_path)\n",
    "\n",
    "ratings_df = data[[\"rating\", \"variation\"]]\n",
    "\n",
    "ratings_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h2> Transform"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h4> Checking the given label to the Alexa products."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Charcoal Fabric ' 'Walnut Finish ' 'Heather Gray Fabric '\n",
      " 'Sandstone Fabric ' 'Oak Finish ' 'Black' 'White' 'Black  Spot'\n",
      " 'White  Spot' 'Black  Show' 'White  Show' 'Black  Plus' 'White  Plus'\n",
      " 'Configuration: Fire TV Stick' 'Black  Dot' 'White  Dot']\n"
     ]
    }
   ],
   "source": [
    "unique_variations = ratings_df['variation'].unique()\n",
    "print(unique_variations)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h4> Replacing the colour variations with the model name in order to normalize our data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Echo' 'Echo Dot' 'Echo Show' 'Echo Plus' 'Fire TV Stick']\n"
     ]
    }
   ],
   "source": [
    "replacement_dict = {\n",
    "    'Black  Dot': 'Echo Dot',\n",
    "    'White  Dot': 'Echo Dot',\n",
    "    'Black  Spot': 'Echo Dot',\n",
    "    'White  Spot': 'Echo Dot',\n",
    "    'White  Show': 'Echo Show',\n",
    "    'Black  Show': 'Echo Show',\n",
    "    'Heather Gray Fabric ': 'Echo Dot',\n",
    "    'Sandstone Fabric ': 'Echo',\n",
    "    'Walnut Finish ': 'Echo',\n",
    "    'Oak Finish ': 'Echo',\n",
    "    'Charcoal Fabric ': 'Echo',\n",
    "    'Configuration: Fire TV Stick': 'Fire TV Stick',\n",
    "    'White Plus': 'Echo Plus',\n",
    "    'Black Plus': 'Echo Plus',\n",
    "    'White': 'Echo',\n",
    "    'Black': 'Echo',\n",
    "    'White  Plus': 'Echo Plus',\n",
    "    'Black  Plus': 'Echo Plus',\n",
    "    \n",
    "    \n",
    "}\n",
    "\n",
    "ratings_df.loc[:, 'variation'] = ratings_df['variation'].replace(replacement_dict)\n",
    "\n",
    "unique_variations = ratings_df['variation'].unique()\n",
    "print(unique_variations)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h4>Calculating the average mean for each product and renaming the columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         Product  Average_Rating\n",
      "0           Echo            4.49\n",
      "1       Echo Dot            4.44\n",
      "2      Echo Plus            4.37\n",
      "3      Echo Show            4.44\n",
      "4  Fire TV Stick            4.59\n"
     ]
    }
   ],
   "source": [
    "average_ratings_df = ratings_df.groupby('variation')['rating'].mean().round(decimals = 2)\n",
    "\n",
    "average_ratings_df = average_ratings_df.reset_index()\n",
    "\n",
    "average_ratings_df.columns = ['Product', 'Average_Rating']\n",
    "\n",
    "\n",
    "print(average_ratings_df)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h4>Creating a new column for couting the reviews for each product."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         Product  Average_Rating  Count\n",
      "0           Echo            4.49    895\n",
      "1       Echo Dot            4.44   1207\n",
      "2      Echo Plus            4.37    348\n",
      "3      Echo Show            4.44    350\n",
      "4  Fire TV Stick            4.59    350\n"
     ]
    }
   ],
   "source": [
    "product_counts = ratings_df['variation'].value_counts().reset_index()\n",
    "product_counts.columns = ['Product', 'Count']\n",
    "alexa_rating_df = average_ratings_df.merge(product_counts, on='Product')\n",
    "\n",
    "print(alexa_rating_df)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h2>Load"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 169,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h4>Connecting to our SQL database/creating it in case it doesn't exist already."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 170,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = sqlite3.connect('amazon_alexa_rating.db')\n",
    "cursor = conn.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h4>Creating a SQL table that matches our pandas dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 171,
   "metadata": {},
   "outputs": [],
   "source": [
    "create_table_sql = '''\n",
    "    CREATE TABLE IF NOT EXISTS alexa_product_ratings (\n",
    "        Product TEXT,\n",
    "        Average_Rating REAL,\n",
    "        Count INTEGER\n",
    "    )\n",
    "'''\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 172,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x1315dbe40>"
      ]
     },
     "execution_count": 172,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.execute(create_table_sql)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h4>Loading our dataframe to the SQL database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 173,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5"
      ]
     },
     "execution_count": 173,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "alexa_rating_df.to_sql('alexa_product_ratings', conn, if_exists='replace', index=False)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h4>Commiting our changes and closing the database connection."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.commit()\n",
    "conn.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "venv-dio-bootcampds",
   "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.11.4"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}