rating-product-sorting-reviews-in-amazon.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "e6661647",
   "metadata": {
    "papermill": {
     "duration": 0.012795,
     "end_time": "2023-10-26T12:49:05.545554",
     "exception": false,
     "start_time": "2023-10-26T12:49:05.532759",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "## BUSINESS PROBLEM\n",
    "\n",
    "One of the most significant challenges in e-commerce is the accurate calculation of post-purchase ratings for products.\n",
    "\n",
    "Solving this problem means providing more customer satisfaction for e-commerce websites, better product visibility for sellers, and a seamless shopping experience for buyers.\n",
    "\n",
    "Another challenge is the proper sorting of product reviews.\n",
    "\n",
    "The prominence of misleading reviews can directly impact product sales, leading to both financial loss and customer attrition.\n",
    "\n",
    "The resolution of these two fundamental issues will result in increased sales for e-commerce platforms and sellers, as well as a smooth shopping journey for customers.\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "541ce824",
   "metadata": {
    "papermill": {
     "duration": 0.011929,
     "end_time": "2023-10-26T12:49:05.570594",
     "exception": false,
     "start_time": "2023-10-26T12:49:05.558665",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "## ABOUT DATASET\n",
    "\n",
    "Content: This dataset containing Amazon product data includes product categories along with various metadata.\n",
    "\n",
    "Context: The product with the most reviews in the Electronics category has user ratings and reviews.\n",
    "\n",
    "COLUMNS\n",
    "\n",
    "*  reviewerID: User ID\n",
    "*  asin: Product ID\n",
    "*  reviewerName: Username\n",
    "*  helpful: Helpful review rating\n",
    "*  reviewText: Review\n",
    "*  overall: Product rating\n",
    "*  summary: Evaluation summary\n",
    "*  unixReviewTime: Review time\n",
    "*  reviewTime: Review time Raw\n",
    "*  day_diff: Number of days since evaluation\n",
    "*  helpful_yes: Number of times the review was found helpful\n",
    "*  total_vote: Number of votes cast on the review"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "af9442d8",
   "metadata": {
    "papermill": {
     "duration": 0.011922,
     "end_time": "2023-10-26T12:49:05.594748",
     "exception": false,
     "start_time": "2023-10-26T12:49:05.582826",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "## TASK\n",
    "\n",
    "1. In the shared dataset, users have provided ratings and reviews for a product. The aim is to evaluate the given ratings by weighting them according to the date and comparing the \"initial average rating\" with the \"weighted rating based on the date\" to be obtained.\n",
    "\n",
    "2. Then,the reviews will be sorted."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e0394063",
   "metadata": {
    "papermill": {
     "duration": 0.011903,
     "end_time": "2023-10-26T12:49:05.618954",
     "exception": false,
     "start_time": "2023-10-26T12:49:05.607051",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "## PREPARING DATA\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "848885eb",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-10-26T12:49:05.645964Z",
     "iopub.status.busy": "2023-10-26T12:49:05.645133Z",
     "iopub.status.idle": "2023-10-26T12:49:07.109107Z",
     "shell.execute_reply": "2023-10-26T12:49:07.108067Z"
    },
    "papermill": {
     "duration": 1.480903,
     "end_time": "2023-10-26T12:49:07.112136",
     "exception": false,
     "start_time": "2023-10-26T12:49:05.631233",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import math\n",
    "import scipy.stats as st\n",
    "from sklearn.preprocessing import MinMaxScaler"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "4fb6adca",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-10-26T12:49:07.138612Z",
     "iopub.status.busy": "2023-10-26T12:49:07.138060Z",
     "iopub.status.idle": "2023-10-26T12:49:07.144620Z",
     "shell.execute_reply": "2023-10-26T12:49:07.143260Z"
    },
    "papermill": {
     "duration": 0.023092,
     "end_time": "2023-10-26T12:49:07.147518",
     "exception": false,
     "start_time": "2023-10-26T12:49:07.124426",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "pd.set_option(\"display.max_columns\",None)\n",
    "pd.set_option(\"display.max_rows\",None)\n",
    "pd.set_option(\"display.width\",500)\n",
    "pd.set_option(\"display.expand_frame_repr\",False)\n",
    "pd.set_option(\"display.float_format\",lambda x: \"%.5f\" % x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "a468de97",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-10-26T12:49:07.174366Z",
     "iopub.status.busy": "2023-10-26T12:49:07.173941Z",
     "iopub.status.idle": "2023-10-26T12:49:07.247192Z",
     "shell.execute_reply": "2023-10-26T12:49:07.245948Z"
    },
    "papermill": {
     "duration": 0.089746,
     "end_time": "2023-10-26T12:49:07.249956",
     "exception": false,
     "start_time": "2023-10-26T12:49:07.160210",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "df=pd.read_csv(\"/kaggle/input/amazon-reviews/amazon_reviews.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "a42a93ab",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-10-26T12:49:07.276263Z",
     "iopub.status.busy": "2023-10-26T12:49:07.275865Z",
     "iopub.status.idle": "2023-10-26T12:49:07.301396Z",
     "shell.execute_reply": "2023-10-26T12:49:07.300306Z"
    },
    "papermill": {
     "duration": 0.041869,
     "end_time": "2023-10-26T12:49:07.304308",
     "exception": false,
     "start_time": "2023-10-26T12:49:07.262439",
     "status": "completed"
    },
    "tags": []
   },
   "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>reviewerID</th>\n",
       "      <th>asin</th>\n",
       "      <th>reviewerName</th>\n",
       "      <th>helpful</th>\n",
       "      <th>reviewText</th>\n",
       "      <th>overall</th>\n",
       "      <th>summary</th>\n",
       "      <th>unixReviewTime</th>\n",
       "      <th>reviewTime</th>\n",
       "      <th>day_diff</th>\n",
       "      <th>helpful_yes</th>\n",
       "      <th>total_vote</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A3SBTW3WS4IQSN</td>\n",
       "      <td>B007WTAJTO</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[0, 0]</td>\n",
       "      <td>No issues.</td>\n",
       "      <td>4.00000</td>\n",
       "      <td>Four Stars</td>\n",
       "      <td>1406073600</td>\n",
       "      <td>2014-07-23</td>\n",
       "      <td>138</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A18K1ODH1I2MVB</td>\n",
       "      <td>B007WTAJTO</td>\n",
       "      <td>0mie</td>\n",
       "      <td>[0, 0]</td>\n",
       "      <td>Purchased this for my device, it worked as adv...</td>\n",
       "      <td>5.00000</td>\n",
       "      <td>MOAR SPACE!!!</td>\n",
       "      <td>1382659200</td>\n",
       "      <td>2013-10-25</td>\n",
       "      <td>409</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A2FII3I2MBMUIA</td>\n",
       "      <td>B007WTAJTO</td>\n",
       "      <td>1K3</td>\n",
       "      <td>[0, 0]</td>\n",
       "      <td>it works as expected. I should have sprung for...</td>\n",
       "      <td>4.00000</td>\n",
       "      <td>nothing to really say....</td>\n",
       "      <td>1356220800</td>\n",
       "      <td>2012-12-23</td>\n",
       "      <td>715</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A3H99DFEG68SR</td>\n",
       "      <td>B007WTAJTO</td>\n",
       "      <td>1m2</td>\n",
       "      <td>[0, 0]</td>\n",
       "      <td>This think has worked out great.Had a diff. br...</td>\n",
       "      <td>5.00000</td>\n",
       "      <td>Great buy at this price!!!  *** UPDATE</td>\n",
       "      <td>1384992000</td>\n",
       "      <td>2013-11-21</td>\n",
       "      <td>382</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A375ZM4U047O79</td>\n",
       "      <td>B007WTAJTO</td>\n",
       "      <td>2&amp;amp;1/2Men</td>\n",
       "      <td>[0, 0]</td>\n",
       "      <td>Bought it with Retail Packaging, arrived legit...</td>\n",
       "      <td>5.00000</td>\n",
       "      <td>best deal around</td>\n",
       "      <td>1373673600</td>\n",
       "      <td>2013-07-13</td>\n",
       "      <td>513</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       reviewerID        asin  reviewerName helpful                                         reviewText  overall                                 summary  unixReviewTime  reviewTime  day_diff  helpful_yes  total_vote\n",
       "0  A3SBTW3WS4IQSN  B007WTAJTO           NaN  [0, 0]                                         No issues.  4.00000                              Four Stars      1406073600  2014-07-23       138            0           0\n",
       "1  A18K1ODH1I2MVB  B007WTAJTO          0mie  [0, 0]  Purchased this for my device, it worked as adv...  5.00000                           MOAR SPACE!!!      1382659200  2013-10-25       409            0           0\n",
       "2  A2FII3I2MBMUIA  B007WTAJTO           1K3  [0, 0]  it works as expected. I should have sprung for...  4.00000               nothing to really say....      1356220800  2012-12-23       715            0           0\n",
       "3   A3H99DFEG68SR  B007WTAJTO           1m2  [0, 0]  This think has worked out great.Had a diff. br...  5.00000  Great buy at this price!!!  *** UPDATE      1384992000  2013-11-21       382            0           0\n",
       "4  A375ZM4U047O79  B007WTAJTO  2&amp;1/2Men  [0, 0]  Bought it with Retail Packaging, arrived legit...  5.00000                        best deal around      1373673600  2013-07-13       513            0           0"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "107753eb",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-10-26T12:49:07.331907Z",
     "iopub.status.busy": "2023-10-26T12:49:07.330809Z",
     "iopub.status.idle": "2023-10-26T12:49:07.337409Z",
     "shell.execute_reply": "2023-10-26T12:49:07.336384Z"
    },
    "papermill": {
     "duration": 0.022944,
     "end_time": "2023-10-26T12:49:07.339816",
     "exception": false,
     "start_time": "2023-10-26T12:49:07.316872",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(4915, 12)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "75c476c7",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-10-26T12:49:07.367052Z",
     "iopub.status.busy": "2023-10-26T12:49:07.366624Z",
     "iopub.status.idle": "2023-10-26T12:49:07.397426Z",
     "shell.execute_reply": "2023-10-26T12:49:07.396616Z"
    },
    "papermill": {
     "duration": 0.047846,
     "end_time": "2023-10-26T12:49:07.400575",
     "exception": false,
     "start_time": "2023-10-26T12:49:07.352729",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 4915 entries, 0 to 4914\n",
      "Data columns (total 12 columns):\n",
      " #   Column          Non-Null Count  Dtype  \n",
      "---  ------          --------------  -----  \n",
      " 0   reviewerID      4915 non-null   object \n",
      " 1   asin            4915 non-null   object \n",
      " 2   reviewerName    4914 non-null   object \n",
      " 3   helpful         4915 non-null   object \n",
      " 4   reviewText      4914 non-null   object \n",
      " 5   overall         4915 non-null   float64\n",
      " 6   summary         4915 non-null   object \n",
      " 7   unixReviewTime  4915 non-null   int64  \n",
      " 8   reviewTime      4915 non-null   object \n",
      " 9   day_diff        4915 non-null   int64  \n",
      " 10  helpful_yes     4915 non-null   int64  \n",
      " 11  total_vote      4915 non-null   int64  \n",
      "dtypes: float64(1), int64(4), object(7)\n",
      "memory usage: 460.9+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "811ec23e",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-10-26T12:49:07.428673Z",
     "iopub.status.busy": "2023-10-26T12:49:07.427938Z",
     "iopub.status.idle": "2023-10-26T12:49:07.436111Z",
     "shell.execute_reply": "2023-10-26T12:49:07.434938Z"
    },
    "papermill": {
     "duration": 0.025217,
     "end_time": "2023-10-26T12:49:07.438709",
     "exception": false,
     "start_time": "2023-10-26T12:49:07.413492",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "overall\n",
       "5.00000    3922\n",
       "4.00000     527\n",
       "1.00000     244\n",
       "3.00000     142\n",
       "2.00000      80\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"overall\"].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "43f66afe",
   "metadata": {
    "papermill": {
     "duration": 0.012648,
     "end_time": "2023-10-26T12:49:07.464521",
     "exception": false,
     "start_time": "2023-10-26T12:49:07.451873",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "## INITIAL AVERAGE RATING"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "548c9d60",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-10-26T12:49:07.492391Z",
     "iopub.status.busy": "2023-10-26T12:49:07.491961Z",
     "iopub.status.idle": "2023-10-26T12:49:07.498462Z",
     "shell.execute_reply": "2023-10-26T12:49:07.497687Z"
    },
    "papermill": {
     "duration": 0.022922,
     "end_time": "2023-10-26T12:49:07.500529",
     "exception": false,
     "start_time": "2023-10-26T12:49:07.477607",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4.587589013224822"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.overall.mean() ##initial average rating(4.587589013224822)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "53720c33",
   "metadata": {
    "papermill": {
     "duration": 0.012729,
     "end_time": "2023-10-26T12:49:07.526393",
     "exception": false,
     "start_time": "2023-10-26T12:49:07.513664",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "## TIME-BASED WEIGHTED AVERAGE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "14aef364",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-10-26T12:49:07.554801Z",
     "iopub.status.busy": "2023-10-26T12:49:07.554377Z",
     "iopub.status.idle": "2023-10-26T12:49:07.562677Z",
     "shell.execute_reply": "2023-10-26T12:49:07.561525Z"
    },
    "papermill": {
     "duration": 0.02508,
     "end_time": "2023-10-26T12:49:07.565014",
     "exception": false,
     "start_time": "2023-10-26T12:49:07.539934",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    138\n",
       "1    409\n",
       "2    715\n",
       "3    382\n",
       "4    513\n",
       "Name: day_diff, dtype: int64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.day_diff.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "830ab586",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-10-26T12:49:07.594077Z",
     "iopub.status.busy": "2023-10-26T12:49:07.593034Z",
     "iopub.status.idle": "2023-10-26T12:49:07.602108Z",
     "shell.execute_reply": "2023-10-26T12:49:07.601287Z"
    },
    "papermill": {
     "duration": 0.025646,
     "end_time": "2023-10-26T12:49:07.604243",
     "exception": false,
     "start_time": "2023-10-26T12:49:07.578597",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "day_diff\n",
       "343    26\n",
       "160    23\n",
       "129    22\n",
       "310    21\n",
       "452    20\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.day_diff.value_counts().head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "71c720e8",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-10-26T12:49:07.633751Z",
     "iopub.status.busy": "2023-10-26T12:49:07.632477Z",
     "iopub.status.idle": "2023-10-26T12:49:07.646799Z",
     "shell.execute_reply": "2023-10-26T12:49:07.645781Z"
    },
    "papermill": {
     "duration": 0.031652,
     "end_time": "2023-10-26T12:49:07.649432",
     "exception": false,
     "start_time": "2023-10-26T12:49:07.617780",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count   4915.00000\n",
       "mean     437.36704\n",
       "std      209.43987\n",
       "min        1.00000\n",
       "25%      281.00000\n",
       "50%      431.00000\n",
       "75%      601.00000\n",
       "max     1064.00000\n",
       "Name: day_diff, dtype: float64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.day_diff.describe().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "0b9a3bda",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-10-26T12:49:07.678662Z",
     "iopub.status.busy": "2023-10-26T12:49:07.678263Z",
     "iopub.status.idle": "2023-10-26T12:49:07.692182Z",
     "shell.execute_reply": "2023-10-26T12:49:07.691070Z"
    },
    "papermill": {
     "duration": 0.031231,
     "end_time": "2023-10-26T12:49:07.694466",
     "exception": false,
     "start_time": "2023-10-26T12:49:07.663235",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "df[\"day_diff_scaled\"]=MinMaxScaler(feature_range=(1,5)).\\\n",
    "    fit(df[[\"day_diff\"]]).\\\n",
    "    transform(df[[\"day_diff\"]]) \n",
    "\n",
    "###The range of the 'day_diff' column has been created between 1 and 5. \n",
    "#And has been created a new column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "d515a03b",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-10-26T12:49:07.723816Z",
     "iopub.status.busy": "2023-10-26T12:49:07.723055Z",
     "iopub.status.idle": "2023-10-26T12:49:07.739304Z",
     "shell.execute_reply": "2023-10-26T12:49:07.738093Z"
    },
    "papermill": {
     "duration": 0.033828,
     "end_time": "2023-10-26T12:49:07.741998",
     "exception": false,
     "start_time": "2023-10-26T12:49:07.708170",
     "status": "completed"
    },
    "tags": []
   },
   "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>reviewerID</th>\n",
       "      <th>asin</th>\n",
       "      <th>reviewerName</th>\n",
       "      <th>helpful</th>\n",
       "      <th>reviewText</th>\n",
       "      <th>overall</th>\n",
       "      <th>summary</th>\n",
       "      <th>unixReviewTime</th>\n",
       "      <th>reviewTime</th>\n",
       "      <th>day_diff</th>\n",
       "      <th>helpful_yes</th>\n",
       "      <th>total_vote</th>\n",
       "      <th>day_diff_scaled</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A3SBTW3WS4IQSN</td>\n",
       "      <td>B007WTAJTO</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[0, 0]</td>\n",
       "      <td>No issues.</td>\n",
       "      <td>4.00000</td>\n",
       "      <td>Four Stars</td>\n",
       "      <td>1406073600</td>\n",
       "      <td>2014-07-23</td>\n",
       "      <td>138</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.51552</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A18K1ODH1I2MVB</td>\n",
       "      <td>B007WTAJTO</td>\n",
       "      <td>0mie</td>\n",
       "      <td>[0, 0]</td>\n",
       "      <td>Purchased this for my device, it worked as adv...</td>\n",
       "      <td>5.00000</td>\n",
       "      <td>MOAR SPACE!!!</td>\n",
       "      <td>1382659200</td>\n",
       "      <td>2013-10-25</td>\n",
       "      <td>409</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2.53528</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A2FII3I2MBMUIA</td>\n",
       "      <td>B007WTAJTO</td>\n",
       "      <td>1K3</td>\n",
       "      <td>[0, 0]</td>\n",
       "      <td>it works as expected. I should have sprung for...</td>\n",
       "      <td>4.00000</td>\n",
       "      <td>nothing to really say....</td>\n",
       "      <td>1356220800</td>\n",
       "      <td>2012-12-23</td>\n",
       "      <td>715</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3.68674</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A3H99DFEG68SR</td>\n",
       "      <td>B007WTAJTO</td>\n",
       "      <td>1m2</td>\n",
       "      <td>[0, 0]</td>\n",
       "      <td>This think has worked out great.Had a diff. br...</td>\n",
       "      <td>5.00000</td>\n",
       "      <td>Great buy at this price!!!  *** UPDATE</td>\n",
       "      <td>1384992000</td>\n",
       "      <td>2013-11-21</td>\n",
       "      <td>382</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2.43368</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A375ZM4U047O79</td>\n",
       "      <td>B007WTAJTO</td>\n",
       "      <td>2&amp;amp;1/2Men</td>\n",
       "      <td>[0, 0]</td>\n",
       "      <td>Bought it with Retail Packaging, arrived legit...</td>\n",
       "      <td>5.00000</td>\n",
       "      <td>best deal around</td>\n",
       "      <td>1373673600</td>\n",
       "      <td>2013-07-13</td>\n",
       "      <td>513</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2.92662</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       reviewerID        asin  reviewerName helpful                                         reviewText  overall                                 summary  unixReviewTime  reviewTime  day_diff  helpful_yes  total_vote  day_diff_scaled\n",
       "0  A3SBTW3WS4IQSN  B007WTAJTO           NaN  [0, 0]                                         No issues.  4.00000                              Four Stars      1406073600  2014-07-23       138            0           0          1.51552\n",
       "1  A18K1ODH1I2MVB  B007WTAJTO          0mie  [0, 0]  Purchased this for my device, it worked as adv...  5.00000                           MOAR SPACE!!!      1382659200  2013-10-25       409            0           0          2.53528\n",
       "2  A2FII3I2MBMUIA  B007WTAJTO           1K3  [0, 0]  it works as expected. I should have sprung for...  4.00000               nothing to really say....      1356220800  2012-12-23       715            0           0          3.68674\n",
       "3   A3H99DFEG68SR  B007WTAJTO           1m2  [0, 0]  This think has worked out great.Had a diff. br...  5.00000  Great buy at this price!!!  *** UPDATE      1384992000  2013-11-21       382            0           0          2.43368\n",
       "4  A375ZM4U047O79  B007WTAJTO  2&amp;1/2Men  [0, 0]  Bought it with Retail Packaging, arrived legit...  5.00000                        best deal around      1373673600  2013-07-13       513            0           0          2.92662"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "41102ad3",
   "metadata": {
    "papermill": {
     "duration": 0.013675,
     "end_time": "2023-10-26T12:49:07.831465",
     "exception": false,
     "start_time": "2023-10-26T12:49:07.817790",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "After assigning values from 1 to 5 in ascending order based on the number of days elapsed since the evaluation, the weights in the calculation were determined as follows:\n",
    "\n",
    "* Those with a day_diff_scaled value of 1 are assigned 28.\n",
    "* Those with a day_diff_scaled value >1 and <=2 are assigned 22.\n",
    "* Those with a day_diff_scaled value >2 and <=3 are assigned 18.\n",
    "* Those with a day_diff_scaled value >3 and <=4 are assigned 15.\n",
    "* Those with a day_diff_scaled value >4 and <5 are assigned 12.\n",
    "* Those with a day_diff_scaled value of 5 are assigned 5\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "01a163cc",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-10-26T12:49:07.861650Z",
     "iopub.status.busy": "2023-10-26T12:49:07.860922Z",
     "iopub.status.idle": "2023-10-26T12:49:07.868975Z",
     "shell.execute_reply": "2023-10-26T12:49:07.868164Z"
    },
    "papermill": {
     "duration": 0.02568,
     "end_time": "2023-10-26T12:49:07.871113",
     "exception": false,
     "start_time": "2023-10-26T12:49:07.845433",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[df[\"day_diff_scaled\"]==1,\"overall\"].count()\n",
    "\n",
    "df.loc[df[\"day_diff_scaled\"]==5,\"overall\"].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "196d55d1",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-10-26T12:49:07.902531Z",
     "iopub.status.busy": "2023-10-26T12:49:07.901360Z",
     "iopub.status.idle": "2023-10-26T12:49:07.910949Z",
     "shell.execute_reply": "2023-10-26T12:49:07.909875Z"
    },
    "papermill": {
     "duration": 0.027841,
     "end_time": "2023-10-26T12:49:07.913250",
     "exception": false,
     "start_time": "2023-10-26T12:49:07.885409",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1494   5.00000\n",
       "1688   5.00000\n",
       "2603   4.00000\n",
       "2629   5.00000\n",
       "2712   5.00000\n",
       "2793   5.00000\n",
       "3690   4.00000\n",
       "3735   5.00000\n",
       "3741   5.00000\n",
       "3742   5.00000\n",
       "4364   5.00000\n",
       "4507   5.00000\n",
       "Name: overall, dtype: float64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[df[\"day_diff_scaled\"]==1,\"overall\"]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "e182c510",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-10-26T12:49:07.944340Z",
     "iopub.status.busy": "2023-10-26T12:49:07.943421Z",
     "iopub.status.idle": "2023-10-26T12:49:07.961795Z",
     "shell.execute_reply": "2023-10-26T12:49:07.960843Z"
    },
    "papermill": {
     "duration": 0.036838,
     "end_time": "2023-10-26T12:49:07.964389",
     "exception": false,
     "start_time": "2023-10-26T12:49:07.927551",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4.689651501198623"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[df[\"day_diff_scaled\"] == 1, \"overall\"].mean() * 28/100 + \\\n",
    "    df.loc[(df[\"day_diff_scaled\"] > 1) & (df[\"day_diff_scaled\"] <= 2), \"overall\"].mean() * 22/100 + \\\n",
    "    df.loc[(df[\"day_diff_scaled\"] > 2) & (df[\"day_diff_scaled\"] <= 3), \"overall\"].mean() * 18/100 + \\\n",
    "    df.loc[(df[\"day_diff_scaled\"] > 3) & (df[\"day_diff_scaled\"]<=4), \"overall\"].mean() * 15/100 +\\\n",
    "    df.loc[(df[\"day_diff_scaled\"])>4 &(df[\"day_diff_scaled\"]<5), \"overall\"].mean() *12/100 +\\\n",
    "    df.loc[df[\"day_diff_scaled\"]==5,\"overall\"].mean()*5/100\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "fb90e247",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-10-26T12:49:07.995932Z",
     "iopub.status.busy": "2023-10-26T12:49:07.995245Z",
     "iopub.status.idle": "2023-10-26T12:49:08.004244Z",
     "shell.execute_reply": "2023-10-26T12:49:08.003060Z"
    },
    "papermill": {
     "duration": 0.027919,
     "end_time": "2023-10-26T12:49:08.007194",
     "exception": false,
     "start_time": "2023-10-26T12:49:07.979275",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "def time_based_weighted_average(dataframe, w1=28, w2=22, w3=18, w4=15,w5=12,w6=5):\n",
    "    return dataframe.loc[df[\"day_diff_scaled\"] == 1, \"overall\"].mean() * 28/100 + \\\n",
    "    dataframe.loc[(df[\"day_diff_scaled\"] > 1) & (dataframe[\"day_diff_scaled\"] <= 2), \"overall\"].mean() * 22/100 + \\\n",
    "    dataframe.loc[(df[\"day_diff_scaled\"] > 2) & (dataframe[\"day_diff_scaled\"] <= 3), \"overall\"].mean() * 18/100 + \\\n",
    "    dataframe.loc[(df[\"day_diff_scaled\"] > 3) & (dataframe[\"day_diff_scaled\"]<=4), \"overall\"].mean() * 15/100 +\\\n",
    "    dataframe.loc[(df[\"day_diff_scaled\"])>4 &(dataframe[\"day_diff_scaled\"]<5), \"overall\"].mean() *12/100 +\\\n",
    "    dataframe.loc[df[\"day_diff_scaled\"]==5,\"overall\"].mean()*5/100\n",
    "\n",
    "##The operation was transformed into a function"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "66c4489f",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-10-26T12:49:08.038382Z",
     "iopub.status.busy": "2023-10-26T12:49:08.037920Z",
     "iopub.status.idle": "2023-10-26T12:49:08.051537Z",
     "shell.execute_reply": "2023-10-26T12:49:08.050340Z"
    },
    "papermill": {
     "duration": 0.0322,
     "end_time": "2023-10-26T12:49:08.054060",
     "exception": false,
     "start_time": "2023-10-26T12:49:08.021860",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4.689651501198623"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "time_based_weighted_average(df) \n",
    "\n",
    "##(4.689651501198623) this figure is the time based weighted average"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "efbf4139",
   "metadata": {
    "papermill": {
     "duration": 0.01422,
     "end_time": "2023-10-26T12:49:08.083028",
     "exception": false,
     "start_time": "2023-10-26T12:49:08.068808",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "## SORTING REVIEWS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "73d4deb0",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-10-26T12:49:08.114322Z",
     "iopub.status.busy": "2023-10-26T12:49:08.113893Z",
     "iopub.status.idle": "2023-10-26T12:49:08.130971Z",
     "shell.execute_reply": "2023-10-26T12:49:08.129994Z"
    },
    "papermill": {
     "duration": 0.035355,
     "end_time": "2023-10-26T12:49:08.133135",
     "exception": false,
     "start_time": "2023-10-26T12:49:08.097780",
     "status": "completed"
    },
    "tags": []
   },
   "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>reviewerID</th>\n",
       "      <th>asin</th>\n",
       "      <th>reviewerName</th>\n",
       "      <th>helpful</th>\n",
       "      <th>reviewText</th>\n",
       "      <th>overall</th>\n",
       "      <th>summary</th>\n",
       "      <th>unixReviewTime</th>\n",
       "      <th>reviewTime</th>\n",
       "      <th>day_diff</th>\n",
       "      <th>helpful_yes</th>\n",
       "      <th>total_vote</th>\n",
       "      <th>day_diff_scaled</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A3SBTW3WS4IQSN</td>\n",
       "      <td>B007WTAJTO</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[0, 0]</td>\n",
       "      <td>No issues.</td>\n",
       "      <td>4.00000</td>\n",
       "      <td>Four Stars</td>\n",
       "      <td>1406073600</td>\n",
       "      <td>2014-07-23</td>\n",
       "      <td>138</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.51552</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A18K1ODH1I2MVB</td>\n",
       "      <td>B007WTAJTO</td>\n",
       "      <td>0mie</td>\n",
       "      <td>[0, 0]</td>\n",
       "      <td>Purchased this for my device, it worked as adv...</td>\n",
       "      <td>5.00000</td>\n",
       "      <td>MOAR SPACE!!!</td>\n",
       "      <td>1382659200</td>\n",
       "      <td>2013-10-25</td>\n",
       "      <td>409</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2.53528</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A2FII3I2MBMUIA</td>\n",
       "      <td>B007WTAJTO</td>\n",
       "      <td>1K3</td>\n",
       "      <td>[0, 0]</td>\n",
       "      <td>it works as expected. I should have sprung for...</td>\n",
       "      <td>4.00000</td>\n",
       "      <td>nothing to really say....</td>\n",
       "      <td>1356220800</td>\n",
       "      <td>2012-12-23</td>\n",
       "      <td>715</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3.68674</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A3H99DFEG68SR</td>\n",
       "      <td>B007WTAJTO</td>\n",
       "      <td>1m2</td>\n",
       "      <td>[0, 0]</td>\n",
       "      <td>This think has worked out great.Had a diff. br...</td>\n",
       "      <td>5.00000</td>\n",
       "      <td>Great buy at this price!!!  *** UPDATE</td>\n",
       "      <td>1384992000</td>\n",
       "      <td>2013-11-21</td>\n",
       "      <td>382</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2.43368</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A375ZM4U047O79</td>\n",
       "      <td>B007WTAJTO</td>\n",
       "      <td>2&amp;amp;1/2Men</td>\n",
       "      <td>[0, 0]</td>\n",
       "      <td>Bought it with Retail Packaging, arrived legit...</td>\n",
       "      <td>5.00000</td>\n",
       "      <td>best deal around</td>\n",
       "      <td>1373673600</td>\n",
       "      <td>2013-07-13</td>\n",
       "      <td>513</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2.92662</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       reviewerID        asin  reviewerName helpful                                         reviewText  overall                                 summary  unixReviewTime  reviewTime  day_diff  helpful_yes  total_vote  day_diff_scaled\n",
       "0  A3SBTW3WS4IQSN  B007WTAJTO           NaN  [0, 0]                                         No issues.  4.00000                              Four Stars      1406073600  2014-07-23       138            0           0          1.51552\n",
       "1  A18K1ODH1I2MVB  B007WTAJTO          0mie  [0, 0]  Purchased this for my device, it worked as adv...  5.00000                           MOAR SPACE!!!      1382659200  2013-10-25       409            0           0          2.53528\n",
       "2  A2FII3I2MBMUIA  B007WTAJTO           1K3  [0, 0]  it works as expected. I should have sprung for...  4.00000               nothing to really say....      1356220800  2012-12-23       715            0           0          3.68674\n",
       "3   A3H99DFEG68SR  B007WTAJTO           1m2  [0, 0]  This think has worked out great.Had a diff. br...  5.00000  Great buy at this price!!!  *** UPDATE      1384992000  2013-11-21       382            0           0          2.43368\n",
       "4  A375ZM4U047O79  B007WTAJTO  2&amp;1/2Men  [0, 0]  Bought it with Retail Packaging, arrived legit...  5.00000                        best deal around      1373673600  2013-07-13       513            0           0          2.92662"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "b587d5cd",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-10-26T12:49:08.165711Z",
     "iopub.status.busy": "2023-10-26T12:49:08.165291Z",
     "iopub.status.idle": "2023-10-26T12:49:08.182732Z",
     "shell.execute_reply": "2023-10-26T12:49:08.181548Z"
    },
    "papermill": {
     "duration": 0.036938,
     "end_time": "2023-10-26T12:49:08.185194",
     "exception": false,
     "start_time": "2023-10-26T12:49:08.148256",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 4915 entries, 0 to 4914\n",
      "Data columns (total 13 columns):\n",
      " #   Column           Non-Null Count  Dtype  \n",
      "---  ------           --------------  -----  \n",
      " 0   reviewerID       4915 non-null   object \n",
      " 1   asin             4915 non-null   object \n",
      " 2   reviewerName     4914 non-null   object \n",
      " 3   helpful          4915 non-null   object \n",
      " 4   reviewText       4914 non-null   object \n",
      " 5   overall          4915 non-null   float64\n",
      " 6   summary          4915 non-null   object \n",
      " 7   unixReviewTime   4915 non-null   int64  \n",
      " 8   reviewTime       4915 non-null   object \n",
      " 9   day_diff         4915 non-null   int64  \n",
      " 10  helpful_yes      4915 non-null   int64  \n",
      " 11  total_vote       4915 non-null   int64  \n",
      " 12  day_diff_scaled  4915 non-null   float64\n",
      "dtypes: float64(2), int64(4), object(7)\n",
      "memory usage: 499.3+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "7a70f9bb",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-10-26T12:49:08.217136Z",
     "iopub.status.busy": "2023-10-26T12:49:08.216705Z",
     "iopub.status.idle": "2023-10-26T12:49:08.223376Z",
     "shell.execute_reply": "2023-10-26T12:49:08.222136Z"
    },
    "papermill": {
     "duration": 0.025349,
     "end_time": "2023-10-26T12:49:08.225705",
     "exception": false,
     "start_time": "2023-10-26T12:49:08.200356",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "df[\"helpful_no\"]=df[\"total_vote\"] - df[\"helpful_yes\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "3f6fdb4c",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-10-26T12:49:08.257963Z",
     "iopub.status.busy": "2023-10-26T12:49:08.257542Z",
     "iopub.status.idle": "2023-10-26T12:49:08.280092Z",
     "shell.execute_reply": "2023-10-26T12:49:08.279009Z"
    },
    "papermill": {
     "duration": 0.041333,
     "end_time": "2023-10-26T12:49:08.282369",
     "exception": false,
     "start_time": "2023-10-26T12:49:08.241036",
     "status": "completed"
    },
    "tags": []
   },
   "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>reviewerID</th>\n",
       "      <th>asin</th>\n",
       "      <th>reviewerName</th>\n",
       "      <th>helpful</th>\n",
       "      <th>reviewText</th>\n",
       "      <th>overall</th>\n",
       "      <th>summary</th>\n",
       "      <th>unixReviewTime</th>\n",
       "      <th>reviewTime</th>\n",
       "      <th>day_diff</th>\n",
       "      <th>helpful_yes</th>\n",
       "      <th>total_vote</th>\n",
       "      <th>day_diff_scaled</th>\n",
       "      <th>helpful_no</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A3SBTW3WS4IQSN</td>\n",
       "      <td>B007WTAJTO</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[0, 0]</td>\n",
       "      <td>No issues.</td>\n",
       "      <td>4.00000</td>\n",
       "      <td>Four Stars</td>\n",
       "      <td>1406073600</td>\n",
       "      <td>2014-07-23</td>\n",
       "      <td>138</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.51552</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A18K1ODH1I2MVB</td>\n",
       "      <td>B007WTAJTO</td>\n",
       "      <td>0mie</td>\n",
       "      <td>[0, 0]</td>\n",
       "      <td>Purchased this for my device, it worked as adv...</td>\n",
       "      <td>5.00000</td>\n",
       "      <td>MOAR SPACE!!!</td>\n",
       "      <td>1382659200</td>\n",
       "      <td>2013-10-25</td>\n",
       "      <td>409</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2.53528</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A2FII3I2MBMUIA</td>\n",
       "      <td>B007WTAJTO</td>\n",
       "      <td>1K3</td>\n",
       "      <td>[0, 0]</td>\n",
       "      <td>it works as expected. I should have sprung for...</td>\n",
       "      <td>4.00000</td>\n",
       "      <td>nothing to really say....</td>\n",
       "      <td>1356220800</td>\n",
       "      <td>2012-12-23</td>\n",
       "      <td>715</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3.68674</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A3H99DFEG68SR</td>\n",
       "      <td>B007WTAJTO</td>\n",
       "      <td>1m2</td>\n",
       "      <td>[0, 0]</td>\n",
       "      <td>This think has worked out great.Had a diff. br...</td>\n",
       "      <td>5.00000</td>\n",
       "      <td>Great buy at this price!!!  *** UPDATE</td>\n",
       "      <td>1384992000</td>\n",
       "      <td>2013-11-21</td>\n",
       "      <td>382</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2.43368</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A375ZM4U047O79</td>\n",
       "      <td>B007WTAJTO</td>\n",
       "      <td>2&amp;amp;1/2Men</td>\n",
       "      <td>[0, 0]</td>\n",
       "      <td>Bought it with Retail Packaging, arrived legit...</td>\n",
       "      <td>5.00000</td>\n",
       "      <td>best deal around</td>\n",
       "      <td>1373673600</td>\n",
       "      <td>2013-07-13</td>\n",
       "      <td>513</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2.92662</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>A2IDCSC6NVONIZ</td>\n",
       "      <td>B007WTAJTO</td>\n",
       "      <td>2Cents!</td>\n",
       "      <td>[0, 0]</td>\n",
       "      <td>It's mini storage.  It doesn't do anything els...</td>\n",
       "      <td>5.00000</td>\n",
       "      <td>Not a lot to really be said</td>\n",
       "      <td>1367193600</td>\n",
       "      <td>2013-04-29</td>\n",
       "      <td>588</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3.20884</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>A26YHXZD5UFPVQ</td>\n",
       "      <td>B007WTAJTO</td>\n",
       "      <td>2K1Toaster</td>\n",
       "      <td>[0, 0]</td>\n",
       "      <td>I have it in my phone and it never skips a bea...</td>\n",
       "      <td>5.00000</td>\n",
       "      <td>Works well</td>\n",
       "      <td>1382140800</td>\n",
       "      <td>2013-10-19</td>\n",
       "      <td>415</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2.55786</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>A3CW0ZLUO5X2B1</td>\n",
       "      <td>B007WTAJTO</td>\n",
       "      <td>35-year Technology Consumer \"8-tracks to 802.11\"</td>\n",
       "      <td>[0, 0]</td>\n",
       "      <td>It's hard to believe how affordable digital ha...</td>\n",
       "      <td>5.00000</td>\n",
       "      <td>32 GB for less than two sawbucks...what's not ...</td>\n",
       "      <td>1404950400</td>\n",
       "      <td>2014-10-07</td>\n",
       "      <td>62</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.22954</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>A2CYJO155QP33S</td>\n",
       "      <td>B007WTAJTO</td>\n",
       "      <td>4evryoung</td>\n",
       "      <td>[1, 1]</td>\n",
       "      <td>Works in a HTC Rezound.  Was running short of ...</td>\n",
       "      <td>5.00000</td>\n",
       "      <td>Loads of room</td>\n",
       "      <td>1395619200</td>\n",
       "      <td>2014-03-24</td>\n",
       "      <td>259</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.97084</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>A2S7XG3ZC4VGOQ</td>\n",
       "      <td>B007WTAJTO</td>\n",
       "      <td>53rdcard</td>\n",
       "      <td>[0, 0]</td>\n",
       "      <td>in my galaxy s4, super fast card, and am total...</td>\n",
       "      <td>5.00000</td>\n",
       "      <td>works great</td>\n",
       "      <td>1381449600</td>\n",
       "      <td>2013-11-10</td>\n",
       "      <td>393</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2.47507</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>A1RTQROTWR5NCB</td>\n",
       "      <td>B007WTAJTO</td>\n",
       "      <td>808TREX50</td>\n",
       "      <td>[0, 0]</td>\n",
       "      <td>I like this SD Card because it can take music ...</td>\n",
       "      <td>5.00000</td>\n",
       "      <td>32GB MicroSD Card.</td>\n",
       "      <td>1368230400</td>\n",
       "      <td>2013-11-05</td>\n",
       "      <td>398</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2.49389</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>A2Q3ICGWZLY3JJ</td>\n",
       "      <td>B007WTAJTO</td>\n",
       "      <td>98020</td>\n",
       "      <td>[0, 0]</td>\n",
       "      <td>It works, but file writes are a bit slower tha...</td>\n",
       "      <td>3.00000</td>\n",
       "      <td>It works, but not as fast as expected</td>\n",
       "      <td>1384905600</td>\n",
       "      <td>2013-11-20</td>\n",
       "      <td>383</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2.43744</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>AN2VFTXYW1SZU</td>\n",
       "      <td>B007WTAJTO</td>\n",
       "      <td>9z4cda</td>\n",
       "      <td>[0, 0]</td>\n",
       "      <td>THE NAME OF ITSELF SPEAKS OUT. GO SANDISK GO!</td>\n",
       "      <td>5.00000</td>\n",
       "      <td>A RENOUND NAME BRAND</td>\n",
       "      <td>1404432000</td>\n",
       "      <td>2014-04-07</td>\n",
       "      <td>245</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.91816</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>AVHN134ADVP4R</td>\n",
       "      <td>B007WTAJTO</td>\n",
       "      <td>A4Q96 \"Gadget Lover\"</td>\n",
       "      <td>[0, 0]</td>\n",
       "      <td>Solid SDHC card that is fast (at reading and w...</td>\n",
       "      <td>5.00000</td>\n",
       "      <td>Great SDHC card</td>\n",
       "      <td>1384992000</td>\n",
       "      <td>2013-11-21</td>\n",
       "      <td>382</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2.43368</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>A3EA7KGYC8LB0</td>\n",
       "      <td>B007WTAJTO</td>\n",
       "      <td>Aaron \"Aaron\"</td>\n",
       "      <td>[0, 0]</td>\n",
       "      <td>Heard that the card's write speed is insuffici...</td>\n",
       "      <td>5.00000</td>\n",
       "      <td>Use it for my GoPro Hero 3+ Black</td>\n",
       "      <td>1392595200</td>\n",
       "      <td>2014-02-17</td>\n",
       "      <td>294</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2.10254</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>A29RI184HRX66H</td>\n",
       "      <td>B007WTAJTO</td>\n",
       "      <td>Aaron \"Aaron\"</td>\n",
       "      <td>[0, 0]</td>\n",
       "      <td>I bought this to use with my go pro hero 3 bla...</td>\n",
       "      <td>5.00000</td>\n",
       "      <td>Awesome price, great brand name</td>\n",
       "      <td>1357257600</td>\n",
       "      <td>2013-04-01</td>\n",
       "      <td>616</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3.31421</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>A2G9ZHVA6IOGBY</td>\n",
       "      <td>B007WTAJTO</td>\n",
       "      <td>Aaron Alvarez</td>\n",
       "      <td>[0, 0]</td>\n",
       "      <td>got this because i had a 2 GB one that filled ...</td>\n",
       "      <td>5.00000</td>\n",
       "      <td>great price. fast</td>\n",
       "      <td>1393718400</td>\n",
       "      <td>2014-02-03</td>\n",
       "      <td>308</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2.15522</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>A1OATGH4DF0RF0</td>\n",
       "      <td>B007WTAJTO</td>\n",
       "      <td>Aaron F. Virginie</td>\n",
       "      <td>[0, 1]</td>\n",
       "      <td>Class 10 Speed Rating for Seamless Full HD Vid...</td>\n",
       "      <td>5.00000</td>\n",
       "      <td>Get Fast Load Times</td>\n",
       "      <td>1372896000</td>\n",
       "      <td>2013-04-07</td>\n",
       "      <td>610</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3.29163</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>A1V6CQACZ9IOLC</td>\n",
       "      <td>B007WTAJTO</td>\n",
       "      <td>Aaron Graves</td>\n",
       "      <td>[0, 0]</td>\n",
       "      <td>The read and write speeds are better than the ...</td>\n",
       "      <td>5.00000</td>\n",
       "      <td>Very good little Micro SD card</td>\n",
       "      <td>1398988800</td>\n",
       "      <td>2014-02-05</td>\n",
       "      <td>306</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2.14770</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>A2ELBSIZ26GKM2</td>\n",
       "      <td>B007WTAJTO</td>\n",
       "      <td>Aaron</td>\n",
       "      <td>[0, 0]</td>\n",
       "      <td>This works with the NL1520.  No video stutteri...</td>\n",
       "      <td>5.00000</td>\n",
       "      <td>works</td>\n",
       "      <td>1389052800</td>\n",
       "      <td>2014-07-01</td>\n",
       "      <td>160</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.59831</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        reviewerID        asin                                      reviewerName helpful                                         reviewText  overall                                            summary  unixReviewTime  reviewTime  day_diff  helpful_yes  total_vote  day_diff_scaled  helpful_no\n",
       "0   A3SBTW3WS4IQSN  B007WTAJTO                                               NaN  [0, 0]                                         No issues.  4.00000                                         Four Stars      1406073600  2014-07-23       138            0           0          1.51552           0\n",
       "1   A18K1ODH1I2MVB  B007WTAJTO                                              0mie  [0, 0]  Purchased this for my device, it worked as adv...  5.00000                                      MOAR SPACE!!!      1382659200  2013-10-25       409            0           0          2.53528           0\n",
       "2   A2FII3I2MBMUIA  B007WTAJTO                                               1K3  [0, 0]  it works as expected. I should have sprung for...  4.00000                          nothing to really say....      1356220800  2012-12-23       715            0           0          3.68674           0\n",
       "3    A3H99DFEG68SR  B007WTAJTO                                               1m2  [0, 0]  This think has worked out great.Had a diff. br...  5.00000             Great buy at this price!!!  *** UPDATE      1384992000  2013-11-21       382            0           0          2.43368           0\n",
       "4   A375ZM4U047O79  B007WTAJTO                                      2&amp;1/2Men  [0, 0]  Bought it with Retail Packaging, arrived legit...  5.00000                                   best deal around      1373673600  2013-07-13       513            0           0          2.92662           0\n",
       "5   A2IDCSC6NVONIZ  B007WTAJTO                                           2Cents!  [0, 0]  It's mini storage.  It doesn't do anything els...  5.00000                        Not a lot to really be said      1367193600  2013-04-29       588            0           0          3.20884           0\n",
       "6   A26YHXZD5UFPVQ  B007WTAJTO                                        2K1Toaster  [0, 0]  I have it in my phone and it never skips a bea...  5.00000                                         Works well      1382140800  2013-10-19       415            0           0          2.55786           0\n",
       "7   A3CW0ZLUO5X2B1  B007WTAJTO  35-year Technology Consumer \"8-tracks to 802.11\"  [0, 0]  It's hard to believe how affordable digital ha...  5.00000  32 GB for less than two sawbucks...what's not ...      1404950400  2014-10-07        62            0           0          1.22954           0\n",
       "8   A2CYJO155QP33S  B007WTAJTO                                         4evryoung  [1, 1]  Works in a HTC Rezound.  Was running short of ...  5.00000                                      Loads of room      1395619200  2014-03-24       259            1           1          1.97084           0\n",
       "9   A2S7XG3ZC4VGOQ  B007WTAJTO                                          53rdcard  [0, 0]  in my galaxy s4, super fast card, and am total...  5.00000                                        works great      1381449600  2013-11-10       393            0           0          2.47507           0\n",
       "10  A1RTQROTWR5NCB  B007WTAJTO                                         808TREX50  [0, 0]  I like this SD Card because it can take music ...  5.00000                                 32GB MicroSD Card.      1368230400  2013-11-05       398            0           0          2.49389           0\n",
       "11  A2Q3ICGWZLY3JJ  B007WTAJTO                                             98020  [0, 0]  It works, but file writes are a bit slower tha...  3.00000              It works, but not as fast as expected      1384905600  2013-11-20       383            0           0          2.43744           0\n",
       "12   AN2VFTXYW1SZU  B007WTAJTO                                            9z4cda  [0, 0]      THE NAME OF ITSELF SPEAKS OUT. GO SANDISK GO!  5.00000                               A RENOUND NAME BRAND      1404432000  2014-04-07       245            0           0          1.91816           0\n",
       "13   AVHN134ADVP4R  B007WTAJTO                              A4Q96 \"Gadget Lover\"  [0, 0]  Solid SDHC card that is fast (at reading and w...  5.00000                                    Great SDHC card      1384992000  2013-11-21       382            0           0          2.43368           0\n",
       "14   A3EA7KGYC8LB0  B007WTAJTO                                     Aaron \"Aaron\"  [0, 0]  Heard that the card's write speed is insuffici...  5.00000                  Use it for my GoPro Hero 3+ Black      1392595200  2014-02-17       294            0           0          2.10254           0\n",
       "15  A29RI184HRX66H  B007WTAJTO                                     Aaron \"Aaron\"  [0, 0]  I bought this to use with my go pro hero 3 bla...  5.00000                    Awesome price, great brand name      1357257600  2013-04-01       616            0           0          3.31421           0\n",
       "16  A2G9ZHVA6IOGBY  B007WTAJTO                                     Aaron Alvarez  [0, 0]  got this because i had a 2 GB one that filled ...  5.00000                                  great price. fast      1393718400  2014-02-03       308            0           0          2.15522           0\n",
       "17  A1OATGH4DF0RF0  B007WTAJTO                                 Aaron F. Virginie  [0, 1]  Class 10 Speed Rating for Seamless Full HD Vid...  5.00000                                Get Fast Load Times      1372896000  2013-04-07       610            0           1          3.29163           1\n",
       "18  A1V6CQACZ9IOLC  B007WTAJTO                                      Aaron Graves  [0, 0]  The read and write speeds are better than the ...  5.00000                     Very good little Micro SD card      1398988800  2014-02-05       306            0           0          2.14770           0\n",
       "19  A2ELBSIZ26GKM2  B007WTAJTO                                             Aaron  [0, 0]  This works with the NL1520.  No video stutteri...  5.00000                                              works      1389052800  2014-07-01       160            0           0          1.59831           0"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(20)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d58e9dce",
   "metadata": {
    "papermill": {
     "duration": 0.015344,
     "end_time": "2023-10-26T12:49:08.313521",
     "exception": false,
     "start_time": "2023-10-26T12:49:08.298177",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "\"score_pos_neg_diff\",\n",
    "\"score_average_rating\",\n",
    "and \"Wilson Lower Bound\" scores will be calculated and added to the dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "2b0e4149",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-10-26T12:49:08.346689Z",
     "iopub.status.busy": "2023-10-26T12:49:08.346277Z",
     "iopub.status.idle": "2023-10-26T12:49:08.351619Z",
     "shell.execute_reply": "2023-10-26T12:49:08.350488Z"
    },
    "papermill": {
     "duration": 0.024526,
     "end_time": "2023-10-26T12:49:08.353801",
     "exception": false,
     "start_time": "2023-10-26T12:49:08.329275",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "def score_pos_neg_diff(pos, neg):\n",
    "    return pos-neg"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "551e76d1",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-10-26T12:49:08.387512Z",
     "iopub.status.busy": "2023-10-26T12:49:08.386620Z",
     "iopub.status.idle": "2023-10-26T12:49:08.479054Z",
     "shell.execute_reply": "2023-10-26T12:49:08.477856Z"
    },
    "papermill": {
     "duration": 0.112184,
     "end_time": "2023-10-26T12:49:08.481651",
     "exception": false,
     "start_time": "2023-10-26T12:49:08.369467",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "df[\"score_pos_neg_diff\"]=df.apply(lambda x: score_pos_neg_diff(x[\"helpful_yes\"],x[\"helpful_no\"]), axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "ede4c2c5",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-10-26T12:49:08.515773Z",
     "iopub.status.busy": "2023-10-26T12:49:08.514694Z",
     "iopub.status.idle": "2023-10-26T12:49:08.519933Z",
     "shell.execute_reply": "2023-10-26T12:49:08.519115Z"
    },
    "papermill": {
     "duration": 0.02464,
     "end_time": "2023-10-26T12:49:08.522195",
     "exception": false,
     "start_time": "2023-10-26T12:49:08.497555",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "def score_average_rating (pos,neg):\n",
    "    if pos + neg == 0:\n",
    "        return 0\n",
    "    return pos/(pos+neg)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "b3a1b0b0",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-10-26T12:49:08.556234Z",
     "iopub.status.busy": "2023-10-26T12:49:08.555526Z",
     "iopub.status.idle": "2023-10-26T12:49:08.643704Z",
     "shell.execute_reply": "2023-10-26T12:49:08.642843Z"
    },
    "papermill": {
     "duration": 0.108119,
     "end_time": "2023-10-26T12:49:08.646228",
     "exception": false,
     "start_time": "2023-10-26T12:49:08.538109",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "df[\"score_average_rating\"]=df.apply(lambda x: score_average_rating(x[\"helpful_yes\"],x[\"helpful_no\"]), axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "65cc76a5",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-10-26T12:49:08.680953Z",
     "iopub.status.busy": "2023-10-26T12:49:08.680274Z",
     "iopub.status.idle": "2023-10-26T12:49:08.696915Z",
     "shell.execute_reply": "2023-10-26T12:49:08.695741Z"
    },
    "papermill": {
     "duration": 0.037383,
     "end_time": "2023-10-26T12:49:08.699574",
     "exception": false,
     "start_time": "2023-10-26T12:49:08.662191",
     "status": "completed"
    },
    "tags": []
   },
   "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>reviewerID</th>\n",
       "      <th>asin</th>\n",
       "      <th>reviewerName</th>\n",
       "      <th>helpful</th>\n",
       "      <th>reviewText</th>\n",
       "      <th>overall</th>\n",
       "      <th>summary</th>\n",
       "      <th>unixReviewTime</th>\n",
       "      <th>reviewTime</th>\n",
       "      <th>day_diff</th>\n",
       "      <th>helpful_yes</th>\n",
       "      <th>total_vote</th>\n",
       "      <th>day_diff_scaled</th>\n",
       "      <th>helpful_no</th>\n",
       "      <th>score_pos_neg_diff</th>\n",
       "      <th>score_average_rating</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A3SBTW3WS4IQSN</td>\n",
       "      <td>B007WTAJTO</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[0, 0]</td>\n",
       "      <td>No issues.</td>\n",
       "      <td>4.00000</td>\n",
       "      <td>Four Stars</td>\n",
       "      <td>1406073600</td>\n",
       "      <td>2014-07-23</td>\n",
       "      <td>138</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.51552</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A18K1ODH1I2MVB</td>\n",
       "      <td>B007WTAJTO</td>\n",
       "      <td>0mie</td>\n",
       "      <td>[0, 0]</td>\n",
       "      <td>Purchased this for my device, it worked as adv...</td>\n",
       "      <td>5.00000</td>\n",
       "      <td>MOAR SPACE!!!</td>\n",
       "      <td>1382659200</td>\n",
       "      <td>2013-10-25</td>\n",
       "      <td>409</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2.53528</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A2FII3I2MBMUIA</td>\n",
       "      <td>B007WTAJTO</td>\n",
       "      <td>1K3</td>\n",
       "      <td>[0, 0]</td>\n",
       "      <td>it works as expected. I should have sprung for...</td>\n",
       "      <td>4.00000</td>\n",
       "      <td>nothing to really say....</td>\n",
       "      <td>1356220800</td>\n",
       "      <td>2012-12-23</td>\n",
       "      <td>715</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3.68674</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A3H99DFEG68SR</td>\n",
       "      <td>B007WTAJTO</td>\n",
       "      <td>1m2</td>\n",
       "      <td>[0, 0]</td>\n",
       "      <td>This think has worked out great.Had a diff. br...</td>\n",
       "      <td>5.00000</td>\n",
       "      <td>Great buy at this price!!!  *** UPDATE</td>\n",
       "      <td>1384992000</td>\n",
       "      <td>2013-11-21</td>\n",
       "      <td>382</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2.43368</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A375ZM4U047O79</td>\n",
       "      <td>B007WTAJTO</td>\n",
       "      <td>2&amp;amp;1/2Men</td>\n",
       "      <td>[0, 0]</td>\n",
       "      <td>Bought it with Retail Packaging, arrived legit...</td>\n",
       "      <td>5.00000</td>\n",
       "      <td>best deal around</td>\n",
       "      <td>1373673600</td>\n",
       "      <td>2013-07-13</td>\n",
       "      <td>513</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2.92662</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       reviewerID        asin  reviewerName helpful                                         reviewText  overall                                 summary  unixReviewTime  reviewTime  day_diff  helpful_yes  total_vote  day_diff_scaled  helpful_no  score_pos_neg_diff  score_average_rating\n",
       "0  A3SBTW3WS4IQSN  B007WTAJTO           NaN  [0, 0]                                         No issues.  4.00000                              Four Stars      1406073600  2014-07-23       138            0           0          1.51552           0                   0               0.00000\n",
       "1  A18K1ODH1I2MVB  B007WTAJTO          0mie  [0, 0]  Purchased this for my device, it worked as adv...  5.00000                           MOAR SPACE!!!      1382659200  2013-10-25       409            0           0          2.53528           0                   0               0.00000\n",
       "2  A2FII3I2MBMUIA  B007WTAJTO           1K3  [0, 0]  it works as expected. I should have sprung for...  4.00000               nothing to really say....      1356220800  2012-12-23       715            0           0          3.68674           0                   0               0.00000\n",
       "3   A3H99DFEG68SR  B007WTAJTO           1m2  [0, 0]  This think has worked out great.Had a diff. br...  5.00000  Great buy at this price!!!  *** UPDATE      1384992000  2013-11-21       382            0           0          2.43368           0                   0               0.00000\n",
       "4  A375ZM4U047O79  B007WTAJTO  2&amp;1/2Men  [0, 0]  Bought it with Retail Packaging, arrived legit...  5.00000                        best deal around      1373673600  2013-07-13       513            0           0          2.92662           0                   0               0.00000"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "32ea50ac",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-10-26T12:49:08.734705Z",
     "iopub.status.busy": "2023-10-26T12:49:08.733888Z",
     "iopub.status.idle": "2023-10-26T12:49:08.741491Z",
     "shell.execute_reply": "2023-10-26T12:49:08.740538Z"
    },
    "papermill": {
     "duration": 0.027698,
     "end_time": "2023-10-26T12:49:08.743909",
     "exception": false,
     "start_time": "2023-10-26T12:49:08.716211",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "def wilson_lower_bound(up, down, confidence=0.95):\n",
    "    n = up + down\n",
    "    if n == 0:\n",
    "        return 0\n",
    "    z = st.norm.ppf(1 - (1 - confidence) / 2)\n",
    "    phat = 1.0 * up / n\n",
    "    return (phat + z * z / (2 * n) - z * math.sqrt((phat * (1 - phat) + z * z / (4 * n)) / n)) / (1 + z * z / n)\n",
    "\n",
    "##Provides the opportunity to score any item, product or review that \n",
    "#has dual interaction. like-dislike, help-or not.\n",
    "\n",
    "\n",
    "##Technique: Computes a confidence interval for the Bernoulli parameter p. \n",
    "##It accepts the lower bound of the confidence interval as the WLB score. \n",
    "##p is the probability of observing an event"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "ec7a64fe",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-10-26T12:49:08.779034Z",
     "iopub.status.busy": "2023-10-26T12:49:08.778613Z",
     "iopub.status.idle": "2023-10-26T12:49:08.788149Z",
     "shell.execute_reply": "2023-10-26T12:49:08.786495Z"
    },
    "papermill": {
     "duration": 0.030408,
     "end_time": "2023-10-26T12:49:08.790723",
     "exception": false,
     "start_time": "2023-10-26T12:49:08.760315",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.7740810353518655"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wilson_lower_bound(800,200)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "34ef2473",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-10-26T12:49:08.825353Z",
     "iopub.status.busy": "2023-10-26T12:49:08.824954Z",
     "iopub.status.idle": "2023-10-26T12:49:09.023497Z",
     "shell.execute_reply": "2023-10-26T12:49:09.022039Z"
    },
    "papermill": {
     "duration": 0.219108,
     "end_time": "2023-10-26T12:49:09.026346",
     "exception": false,
     "start_time": "2023-10-26T12:49:08.807238",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "df[\"wilson_lower_bound\"]=df.apply(lambda x: wilson_lower_bound(x[\"helpful_yes\"],x[\"helpful_no\"]),axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "71418f35",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-10-26T12:49:09.061589Z",
     "iopub.status.busy": "2023-10-26T12:49:09.060503Z",
     "iopub.status.idle": "2023-10-26T12:49:09.076247Z",
     "shell.execute_reply": "2023-10-26T12:49:09.075251Z"
    },
    "papermill": {
     "duration": 0.035852,
     "end_time": "2023-10-26T12:49:09.078645",
     "exception": false,
     "start_time": "2023-10-26T12:49:09.042793",
     "status": "completed"
    },
    "tags": []
   },
   "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>helpful_yes</th>\n",
       "      <th>helpful_no</th>\n",
       "      <th>score_pos_neg_diff</th>\n",
       "      <th>score_average_rating</th>\n",
       "      <th>wilson_lower_bound</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2031</th>\n",
       "      <td>1952</td>\n",
       "      <td>68</td>\n",
       "      <td>1884</td>\n",
       "      <td>0.96634</td>\n",
       "      <td>0.95754</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3449</th>\n",
       "      <td>1428</td>\n",
       "      <td>77</td>\n",
       "      <td>1351</td>\n",
       "      <td>0.94884</td>\n",
       "      <td>0.93652</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4212</th>\n",
       "      <td>1568</td>\n",
       "      <td>126</td>\n",
       "      <td>1442</td>\n",
       "      <td>0.92562</td>\n",
       "      <td>0.91214</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>317</th>\n",
       "      <td>422</td>\n",
       "      <td>73</td>\n",
       "      <td>349</td>\n",
       "      <td>0.85253</td>\n",
       "      <td>0.81858</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4672</th>\n",
       "      <td>45</td>\n",
       "      <td>4</td>\n",
       "      <td>41</td>\n",
       "      <td>0.91837</td>\n",
       "      <td>0.80811</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1835</th>\n",
       "      <td>60</td>\n",
       "      <td>8</td>\n",
       "      <td>52</td>\n",
       "      <td>0.88235</td>\n",
       "      <td>0.78465</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3981</th>\n",
       "      <td>112</td>\n",
       "      <td>27</td>\n",
       "      <td>85</td>\n",
       "      <td>0.80576</td>\n",
       "      <td>0.73214</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3807</th>\n",
       "      <td>22</td>\n",
       "      <td>3</td>\n",
       "      <td>19</td>\n",
       "      <td>0.88000</td>\n",
       "      <td>0.70044</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4306</th>\n",
       "      <td>51</td>\n",
       "      <td>14</td>\n",
       "      <td>37</td>\n",
       "      <td>0.78462</td>\n",
       "      <td>0.67033</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4596</th>\n",
       "      <td>82</td>\n",
       "      <td>27</td>\n",
       "      <td>55</td>\n",
       "      <td>0.75229</td>\n",
       "      <td>0.66359</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>315</th>\n",
       "      <td>38</td>\n",
       "      <td>10</td>\n",
       "      <td>28</td>\n",
       "      <td>0.79167</td>\n",
       "      <td>0.65741</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1465</th>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>1.00000</td>\n",
       "      <td>0.64567</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1609</th>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>1.00000</td>\n",
       "      <td>0.64567</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4302</th>\n",
       "      <td>14</td>\n",
       "      <td>2</td>\n",
       "      <td>12</td>\n",
       "      <td>0.87500</td>\n",
       "      <td>0.63977</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4072</th>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>1.00000</td>\n",
       "      <td>0.60967</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1072</th>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>1.00000</td>\n",
       "      <td>0.56552</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2583</th>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>1.00000</td>\n",
       "      <td>0.56552</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>1.00000</td>\n",
       "      <td>0.56552</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1142</th>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>1.00000</td>\n",
       "      <td>0.56552</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1753</th>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>1.00000</td>\n",
       "      <td>0.56552</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      helpful_yes  helpful_no  score_pos_neg_diff  score_average_rating  wilson_lower_bound\n",
       "2031         1952          68                1884               0.96634             0.95754\n",
       "3449         1428          77                1351               0.94884             0.93652\n",
       "4212         1568         126                1442               0.92562             0.91214\n",
       "317           422          73                 349               0.85253             0.81858\n",
       "4672           45           4                  41               0.91837             0.80811\n",
       "1835           60           8                  52               0.88235             0.78465\n",
       "3981          112          27                  85               0.80576             0.73214\n",
       "3807           22           3                  19               0.88000             0.70044\n",
       "4306           51          14                  37               0.78462             0.67033\n",
       "4596           82          27                  55               0.75229             0.66359\n",
       "315            38          10                  28               0.79167             0.65741\n",
       "1465            7           0                   7               1.00000             0.64567\n",
       "1609            7           0                   7               1.00000             0.64567\n",
       "4302           14           2                  12               0.87500             0.63977\n",
       "4072            6           0                   6               1.00000             0.60967\n",
       "1072            5           0                   5               1.00000             0.56552\n",
       "2583            5           0                   5               1.00000             0.56552\n",
       "121             5           0                   5               1.00000             0.56552\n",
       "1142            5           0                   5               1.00000             0.56552\n",
       "1753            5           0                   5               1.00000             0.56552"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[[\"helpful_yes\", \"helpful_no\",\"score_pos_neg_diff\",\"score_average_rating\",\"wilson_lower_bound\"]].sort_values(by=\"wilson_lower_bound\", ascending=False).head(20)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ffa55efb",
   "metadata": {
    "papermill": {
     "duration": 0.016413,
     "end_time": "2023-10-26T12:49:09.111754",
     "exception": false,
     "start_time": "2023-10-26T12:49:09.095341",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "## RESULT\n",
    "\n",
    "In summary, when considering the \"wilson_lower_bound\" proves to be a more accurate measure of ranking success. For instance, when examining the reviews with indices such as 4072, 1072, 2583, or 121, it becomes evident that there are no negative comments, but the count of positive reviews is significantly low. As a result, the score_average_rating of 1 may present a biased perspective. In contrast, the wilson_lower_bound takes into consideration both helpful feedback and review frequency."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2a14d8b5",
   "metadata": {
    "papermill": {
     "duration": 0.016467,
     "end_time": "2023-10-26T12:49:09.145087",
     "exception": false,
     "start_time": "2023-10-26T12:49:09.128620",
     "status": "completed"
    },
    "tags": []
   },
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.10.12"
  },
  "papermill": {
   "default_parameters": {},
   "duration": 7.716996,
   "end_time": "2023-10-26T12:49:09.684556",
   "environment_variables": {},
   "exception": null,
   "input_path": "__notebook__.ipynb",
   "output_path": "__notebook__.ipynb",
   "parameters": {},
   "start_time": "2023-10-26T12:49:01.967560",
   "version": "2.4.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}