LaptopsDataCleaning.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "0a0d0c8d",
   "metadata": {},
   "source": [
    "# Importing Libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "f99e3108",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "import re"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0d01926e",
   "metadata": {},
   "source": [
    "# Files Loading"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c7d06f7d",
   "metadata": {},
   "source": [
    "We load all the files in the folder \"data\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "3d20f082",
   "metadata": {},
   "outputs": [],
   "source": [
    "folder = 'data'\n",
    "files = os.listdir(folder)\n",
    "\n",
    "dfs = []\n",
    "\n",
    "for file in files:\n",
    "    \n",
    "    path = os.path.join(folder, file)\n",
    "    df = pd.read_excel(path, header=0)\n",
    "    dfs.append(df)\n",
    "\n",
    "\n",
    "Laptops = pd.concat(dfs)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "08606c83",
   "metadata": {},
   "source": [
    "# Data Cleaning"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0c047bd2",
   "metadata": {},
   "source": [
    "First of all, we need to delete all the duplicated values we could have. The best column to do so is the Laptop column:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "57f73057",
   "metadata": {},
   "outputs": [],
   "source": [
    "Laptops = Laptops.drop_duplicates(subset='Laptop')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "adc76e12",
   "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>Laptop</th>\n",
       "      <th>New Price</th>\n",
       "      <th>Old Price</th>\n",
       "      <th>Price</th>\n",
       "      <th>Old Price 2</th>\n",
       "      <th>Status</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ASUS ExpertBook B1 B1502CBA-EJ0436X Intel Core...</td>\n",
       "      <td>699</td>\n",
       "      <td>1.009</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PVPR</td>\n",
       "      <td>Patrocinado</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alurin Go Start Intel Celeron N4020/8GB/256GB ...</td>\n",
       "      <td>229</td>\n",
       "      <td>299</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PVPR</td>\n",
       "      <td>Envío gratis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ASUS ExpertBook B1 B1502CBA-EJ0424X Intel Core...</td>\n",
       "      <td>549</td>\n",
       "      <td>789</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PVPR</td>\n",
       "      <td>Patrocinado</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>MSI Katana GF66 12UC-082XES Intel Core i7-1270...</td>\n",
       "      <td>899</td>\n",
       "      <td>1.199</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PVPR</td>\n",
       "      <td>Envío gratis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>HP 15S-FQ5085NS Intel Core i5-1235U/16GB/512GB...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>669,01€</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Patrocinado</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2159</th>\n",
       "      <td>Razer Blade 17 FHD 360Hz Intel Core i7-11800H/...</td>\n",
       "      <td>2.151,39€</td>\n",
       "      <td>2.699,99€</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PVPR</td>\n",
       "      <td>Reacondicionado</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2160</th>\n",
       "      <td>Razer Blade 17 FHD 360Hz Intel Core i7-11800H/...</td>\n",
       "      <td>2.016,39€</td>\n",
       "      <td>2.899,99€</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PVPR</td>\n",
       "      <td>Reacondicionado</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2161</th>\n",
       "      <td>Razer Blade 17 FHD 360Hz Intel Core i7-11800H/...</td>\n",
       "      <td>2.069,69€</td>\n",
       "      <td>3.399,99€</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PVPR</td>\n",
       "      <td>Reacondicionado</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2162</th>\n",
       "      <td>Razer Book 13 Intel Evo Core i7-1165G7/16GB/1T...</td>\n",
       "      <td>1.515,19€</td>\n",
       "      <td>1.899,99€</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PVPR</td>\n",
       "      <td>Reacondicionado</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2163</th>\n",
       "      <td>Razer Book FHD+ Intel Evo Core i7-1165G7/16GB/...</td>\n",
       "      <td>1.049,99€</td>\n",
       "      <td>1.699,99€</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PVPR</td>\n",
       "      <td>Reacondicionado</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2160 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 Laptop  New Price  Old Price  \\\n",
       "0     ASUS ExpertBook B1 B1502CBA-EJ0436X Intel Core...        699      1.009   \n",
       "1     Alurin Go Start Intel Celeron N4020/8GB/256GB ...        229        299   \n",
       "2     ASUS ExpertBook B1 B1502CBA-EJ0424X Intel Core...        549        789   \n",
       "3     MSI Katana GF66 12UC-082XES Intel Core i7-1270...        899      1.199   \n",
       "4     HP 15S-FQ5085NS Intel Core i5-1235U/16GB/512GB...        NaN        NaN   \n",
       "...                                                 ...        ...        ...   \n",
       "2159  Razer Blade 17 FHD 360Hz Intel Core i7-11800H/...  2.151,39€  2.699,99€   \n",
       "2160  Razer Blade 17 FHD 360Hz Intel Core i7-11800H/...  2.016,39€  2.899,99€   \n",
       "2161  Razer Blade 17 FHD 360Hz Intel Core i7-11800H/...  2.069,69€  3.399,99€   \n",
       "2162  Razer Book 13 Intel Evo Core i7-1165G7/16GB/1T...  1.515,19€  1.899,99€   \n",
       "2163  Razer Book FHD+ Intel Evo Core i7-1165G7/16GB/...  1.049,99€  1.699,99€   \n",
       "\n",
       "        Price Old Price 2           Status  \n",
       "0         NaN        PVPR      Patrocinado  \n",
       "1         NaN        PVPR     Envío gratis  \n",
       "2         NaN        PVPR      Patrocinado  \n",
       "3         NaN        PVPR     Envío gratis  \n",
       "4     669,01€         NaN      Patrocinado  \n",
       "...       ...         ...              ...  \n",
       "2159      NaN        PVPR  Reacondicionado  \n",
       "2160      NaN        PVPR  Reacondicionado  \n",
       "2161      NaN        PVPR  Reacondicionado  \n",
       "2162      NaN        PVPR  Reacondicionado  \n",
       "2163      NaN        PVPR  Reacondicionado  \n",
       "\n",
       "[2160 rows x 6 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Laptops"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3b501ae9",
   "metadata": {},
   "source": [
    "In a first sight, we see the values in Pricing columns are awful. Let's concentrate in the first one, New Price:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "07f82dba",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0         699\n",
       "1         229\n",
       "2         549\n",
       "3         899\n",
       "4         NaN\n",
       "5       1.349\n",
       "6         NaN\n",
       "7         529\n",
       "8         329\n",
       "10        879\n",
       "11        320\n",
       "12        999\n",
       "13        829\n",
       "14        NaN\n",
       "15        319\n",
       "16        549\n",
       "17        709\n",
       "18    458,78€\n",
       "19        469\n",
       "20        799\n",
       "21        999\n",
       "22        599\n",
       "23        NaN\n",
       "24        739\n",
       "25        339\n",
       "26        849\n",
       "27        219\n",
       "28        NaN\n",
       "29        599\n",
       "30        NaN\n",
       "Name: New Price, dtype: object"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Laptops['New Price'].head(30)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "27beae62",
   "metadata": {},
   "source": [
    "The only problem I see in New Price is the € symbol. Values such '1.099,99€' are a challenge, since includes \".\", \",\" and \"€\"."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "aadbf2a7",
   "metadata": {},
   "outputs": [],
   "source": [
    "def value_to_float(x):\n",
    "    if type(x) == float or type(x) == int:\n",
    "        return x\n",
    "    if '€' in x:\n",
    "        return float(x.replace('€', '').replace('.','').replace(',','.'))\n",
    "    return 0.0\n",
    "\n",
    "df['New Price'] = Laptops['New Price'].apply(value_to_float)\n",
    "df['New Price'] = df['New Price'].apply(lambda x: x * 1000 if x < 10 else x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "79e09c24",
   "metadata": {},
   "outputs": [],
   "source": [
    "Laptops['New Price'] = df['New Price']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a2bc7aa0",
   "metadata": {},
   "source": [
    "Now let's dive into the next column, Old Price:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "9835213b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     1.009\n",
       "1       299\n",
       "2       789\n",
       "3     1.199\n",
       "4       NaN\n",
       "5     1.699\n",
       "6       NaN\n",
       "7       NaN\n",
       "8       519\n",
       "10    1.149\n",
       "11      349\n",
       "12    1.399\n",
       "13    1.199\n",
       "14      NaN\n",
       "15      NaN\n",
       "16      699\n",
       "17      799\n",
       "18      549\n",
       "19      NaN\n",
       "20      999\n",
       "Name: Old Price, dtype: object"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Laptops['Old Price'].head(20)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f6ef426a",
   "metadata": {},
   "source": [
    "It looks very similar to New Price. The function used before (value_to_float(x)) should work:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "3488db9a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Old Price'] = Laptops['Old Price'].apply(value_to_float)\n",
    "df['Old Price'] = df['Old Price'].apply(lambda x: x * 1000 if x < 10 else x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "999d109e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     1009.0\n",
       "1      299.0\n",
       "2      789.0\n",
       "3     1199.0\n",
       "4        NaN\n",
       "5     1699.0\n",
       "6        NaN\n",
       "7        NaN\n",
       "8      519.0\n",
       "9        NaN\n",
       "10    1149.0\n",
       "11     349.0\n",
       "12    1399.0\n",
       "13    1199.0\n",
       "14       NaN\n",
       "15       NaN\n",
       "16     699.0\n",
       "17     799.0\n",
       "18     549.0\n",
       "19       NaN\n",
       "Name: Old Price, dtype: float64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Old Price'].head(20)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "604a813b",
   "metadata": {},
   "source": [
    "Looks nice, so we move it to Laptops df:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "94f0d46d",
   "metadata": {},
   "outputs": [],
   "source": [
    "Laptops['Old Price'] = df['Old Price']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "44eea0a8",
   "metadata": {},
   "source": [
    "The next column is Price. Let's see how it looks like:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "c293e229",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0                       NaN\n",
       "1                       NaN\n",
       "2                       NaN\n",
       "3                       NaN\n",
       "4                   669,01€\n",
       "5                       NaN\n",
       "6                       909\n",
       "7     Más bajo 30 días 549€\n",
       "8                       NaN\n",
       "10                      NaN\n",
       "11                      NaN\n",
       "12                      NaN\n",
       "13                      NaN\n",
       "14                  476,99€\n",
       "15    Más bajo 30 días 279€\n",
       "16                      NaN\n",
       "17                      NaN\n",
       "18                      NaN\n",
       "19    Más bajo 30 días 499€\n",
       "20                      NaN\n",
       "Name: Price, dtype: object"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Laptops['Price'].head(20)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2b890846",
   "metadata": {},
   "source": [
    "Looks similar to Old Price, but we also have values with the string \"Más bajo 30 días \". We define a function to clean it, including this, as we did in New Price:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "6bc62934",
   "metadata": {},
   "outputs": [],
   "source": [
    "def value_to_float_2(x):\n",
    "    if type(x) == float or type(x) == int:\n",
    "        return x\n",
    "    if 'bajo' in x:\n",
    "        return float(x.replace('Más bajo 30 días ', '').replace('€', '').replace('.','').replace(',','.'))\n",
    "    if '€' in x:\n",
    "        return float(x.replace('€', '').replace('.','').replace(',','.'))\n",
    "    return 0.0\n",
    "\n",
    "df['Price'] = Laptops['Price'].apply(value_to_float_2)\n",
    "df['Price'] = df['Price'].apply(lambda x: x * 1000 if x < 10 else x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "c313f0de",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        NaN\n",
       "1        NaN\n",
       "2        NaN\n",
       "3        NaN\n",
       "4     669.01\n",
       "5        NaN\n",
       "6     909.00\n",
       "7     549.00\n",
       "8        NaN\n",
       "9        NaN\n",
       "10       NaN\n",
       "11       NaN\n",
       "12       NaN\n",
       "13       NaN\n",
       "14    476.99\n",
       "15    279.00\n",
       "16       NaN\n",
       "17       NaN\n",
       "18       NaN\n",
       "19    499.00\n",
       "Name: Price, dtype: float64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Price'].head(20)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "57a08eab",
   "metadata": {},
   "source": [
    "Looks perfect, we move it to Laptops df:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "847ae2e8",
   "metadata": {},
   "outputs": [],
   "source": [
    "Laptops['Price'] = df['Price']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b850c044",
   "metadata": {},
   "source": [
    "Let's see the last one, Old Price 2:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "2ece1c1d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        PVPR\n",
       "1        PVPR\n",
       "2        PVPR\n",
       "3        PVPR\n",
       "4         NaN\n",
       "5        PVPR\n",
       "6         NaN\n",
       "7     809,01€\n",
       "8        PVPR\n",
       "10       PVPR\n",
       "11       PVPR\n",
       "12       PVPR\n",
       "13       PVPR\n",
       "14        NaN\n",
       "15        391\n",
       "16       PVPR\n",
       "17       PVPR\n",
       "18       PVPR\n",
       "19        769\n",
       "20       PVPR\n",
       "Name: Old Price 2, dtype: object"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Laptops['Old Price 2'].head(20)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9e9d3648",
   "metadata": {},
   "source": [
    "It looks like the other ones, but additionally we need to replace the string \"PVPR\". Let's build a function:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "9ac91281",
   "metadata": {},
   "outputs": [],
   "source": [
    "def value_to_float_3(x):\n",
    "    if type(x) == float or type(x) == int:\n",
    "        return x\n",
    "    if 'PVPR' in x:\n",
    "        return float(x.replace('PVPR', '0'))\n",
    "    if '€' in x:\n",
    "        return float(x.replace('€', '').replace('.','').replace(',','.'))\n",
    "    return float(0.0)\n",
    "\n",
    "df['Old Price 2'] = Laptops['Old Price 2'].apply(value_to_float_3)\n",
    "df['Old Price 2'] = df['Old Price 2'].apply(lambda x: x * 1000 if x < 10 else x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "0f9508cf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0       0.00\n",
       "1       0.00\n",
       "2       0.00\n",
       "3       0.00\n",
       "4        NaN\n",
       "5       0.00\n",
       "6        NaN\n",
       "7     809.01\n",
       "8       0.00\n",
       "9        NaN\n",
       "10      0.00\n",
       "11      0.00\n",
       "12      0.00\n",
       "13      0.00\n",
       "14       NaN\n",
       "15    391.00\n",
       "16      0.00\n",
       "17      0.00\n",
       "18      0.00\n",
       "19    769.00\n",
       "Name: Old Price 2, dtype: float64"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Old Price 2'].head(20)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f790b51c",
   "metadata": {},
   "source": [
    "Now we have nan values and 0.0 values. It's not important for our purpose, so let's go ahead."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "3450765a",
   "metadata": {},
   "outputs": [],
   "source": [
    "Laptops['Old Price 2'] = df['Old Price 2']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "75480750",
   "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>Laptop</th>\n",
       "      <th>New Price</th>\n",
       "      <th>Old Price</th>\n",
       "      <th>Price</th>\n",
       "      <th>Old Price 2</th>\n",
       "      <th>Status</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ASUS ExpertBook B1 B1502CBA-EJ0436X Intel Core...</td>\n",
       "      <td>699.00</td>\n",
       "      <td>1009.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Patrocinado</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alurin Go Start Intel Celeron N4020/8GB/256GB ...</td>\n",
       "      <td>229.00</td>\n",
       "      <td>299.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Envío gratis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ASUS ExpertBook B1 B1502CBA-EJ0424X Intel Core...</td>\n",
       "      <td>549.00</td>\n",
       "      <td>789.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Patrocinado</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>MSI Katana GF66 12UC-082XES Intel Core i7-1270...</td>\n",
       "      <td>899.00</td>\n",
       "      <td>1199.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Envío gratis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>HP 15S-FQ5085NS Intel Core i5-1235U/16GB/512GB...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>669.01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Patrocinado</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2159</th>\n",
       "      <td>Razer Blade 17 FHD 360Hz Intel Core i7-11800H/...</td>\n",
       "      <td>2151.39</td>\n",
       "      <td>2699.99</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Reacondicionado</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2160</th>\n",
       "      <td>Razer Blade 17 FHD 360Hz Intel Core i7-11800H/...</td>\n",
       "      <td>2016.39</td>\n",
       "      <td>2899.99</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Reacondicionado</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2161</th>\n",
       "      <td>Razer Blade 17 FHD 360Hz Intel Core i7-11800H/...</td>\n",
       "      <td>2069.69</td>\n",
       "      <td>3399.99</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Reacondicionado</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2162</th>\n",
       "      <td>Razer Book 13 Intel Evo Core i7-1165G7/16GB/1T...</td>\n",
       "      <td>1515.19</td>\n",
       "      <td>1899.99</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Reacondicionado</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2163</th>\n",
       "      <td>Razer Book FHD+ Intel Evo Core i7-1165G7/16GB/...</td>\n",
       "      <td>1049.99</td>\n",
       "      <td>1699.99</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Reacondicionado</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2160 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 Laptop  New Price  Old Price  \\\n",
       "0     ASUS ExpertBook B1 B1502CBA-EJ0436X Intel Core...     699.00    1009.00   \n",
       "1     Alurin Go Start Intel Celeron N4020/8GB/256GB ...     229.00     299.00   \n",
       "2     ASUS ExpertBook B1 B1502CBA-EJ0424X Intel Core...     549.00     789.00   \n",
       "3     MSI Katana GF66 12UC-082XES Intel Core i7-1270...     899.00    1199.00   \n",
       "4     HP 15S-FQ5085NS Intel Core i5-1235U/16GB/512GB...        NaN        NaN   \n",
       "...                                                 ...        ...        ...   \n",
       "2159  Razer Blade 17 FHD 360Hz Intel Core i7-11800H/...    2151.39    2699.99   \n",
       "2160  Razer Blade 17 FHD 360Hz Intel Core i7-11800H/...    2016.39    2899.99   \n",
       "2161  Razer Blade 17 FHD 360Hz Intel Core i7-11800H/...    2069.69    3399.99   \n",
       "2162  Razer Book 13 Intel Evo Core i7-1165G7/16GB/1T...    1515.19    1899.99   \n",
       "2163  Razer Book FHD+ Intel Evo Core i7-1165G7/16GB/...    1049.99    1699.99   \n",
       "\n",
       "       Price  Old Price 2           Status  \n",
       "0        NaN          0.0      Patrocinado  \n",
       "1        NaN          0.0     Envío gratis  \n",
       "2        NaN          0.0      Patrocinado  \n",
       "3        NaN          0.0     Envío gratis  \n",
       "4     669.01          NaN      Patrocinado  \n",
       "...      ...          ...              ...  \n",
       "2159     NaN          0.0  Reacondicionado  \n",
       "2160     NaN          0.0  Reacondicionado  \n",
       "2161     NaN          0.0  Reacondicionado  \n",
       "2162     NaN          0.0  Reacondicionado  \n",
       "2163     NaN          0.0  Reacondicionado  \n",
       "\n",
       "[2160 rows x 6 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Laptops"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4e45016f",
   "metadata": {},
   "source": [
    "# Working with prices "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c7302c66",
   "metadata": {},
   "source": [
    "Now we should transfer the prices. The raw structure is as follows:\n",
    "\n",
    "- #### New Price: Discounted price. Not interesting, since it's not the common price.\n",
    "- #### Old Price: Common price, in the cases a New Price exists.\n",
    "- #### Price: Price when New Price nor Old Price exists.\n",
    "- #### Old Price 2: Common price in the cases there is a Price and New Price.\n",
    "\n",
    "The goal now is to create a column 'Final Price', showing the common price, without any kind of discount.\n",
    "\n",
    "So the 'Final Price' should be the highest price from the four pricing columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "01a316c5",
   "metadata": {},
   "outputs": [],
   "source": [
    "Laptops['Final Price'] = Laptops[['Old Price', 'Old Price 2', 'New Price', 'Price']].max(axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "5491c732",
   "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>Laptop</th>\n",
       "      <th>New Price</th>\n",
       "      <th>Old Price</th>\n",
       "      <th>Price</th>\n",
       "      <th>Old Price 2</th>\n",
       "      <th>Status</th>\n",
       "      <th>Final Price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ASUS ExpertBook B1 B1502CBA-EJ0436X Intel Core...</td>\n",
       "      <td>699.00</td>\n",
       "      <td>1009.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Patrocinado</td>\n",
       "      <td>1009.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alurin Go Start Intel Celeron N4020/8GB/256GB ...</td>\n",
       "      <td>229.00</td>\n",
       "      <td>299.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Envío gratis</td>\n",
       "      <td>299.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ASUS ExpertBook B1 B1502CBA-EJ0424X Intel Core...</td>\n",
       "      <td>549.00</td>\n",
       "      <td>789.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Patrocinado</td>\n",
       "      <td>789.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>MSI Katana GF66 12UC-082XES Intel Core i7-1270...</td>\n",
       "      <td>899.00</td>\n",
       "      <td>1199.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Envío gratis</td>\n",
       "      <td>1199.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>HP 15S-FQ5085NS Intel Core i5-1235U/16GB/512GB...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>669.01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Patrocinado</td>\n",
       "      <td>669.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2159</th>\n",
       "      <td>Razer Blade 17 FHD 360Hz Intel Core i7-11800H/...</td>\n",
       "      <td>2151.39</td>\n",
       "      <td>2699.99</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Reacondicionado</td>\n",
       "      <td>2699.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2160</th>\n",
       "      <td>Razer Blade 17 FHD 360Hz Intel Core i7-11800H/...</td>\n",
       "      <td>2016.39</td>\n",
       "      <td>2899.99</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Reacondicionado</td>\n",
       "      <td>2899.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2161</th>\n",
       "      <td>Razer Blade 17 FHD 360Hz Intel Core i7-11800H/...</td>\n",
       "      <td>2069.69</td>\n",
       "      <td>3399.99</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Reacondicionado</td>\n",
       "      <td>3399.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2162</th>\n",
       "      <td>Razer Book 13 Intel Evo Core i7-1165G7/16GB/1T...</td>\n",
       "      <td>1515.19</td>\n",
       "      <td>1899.99</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Reacondicionado</td>\n",
       "      <td>1899.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2163</th>\n",
       "      <td>Razer Book FHD+ Intel Evo Core i7-1165G7/16GB/...</td>\n",
       "      <td>1049.99</td>\n",
       "      <td>1699.99</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Reacondicionado</td>\n",
       "      <td>1699.99</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2160 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 Laptop  New Price  Old Price  \\\n",
       "0     ASUS ExpertBook B1 B1502CBA-EJ0436X Intel Core...     699.00    1009.00   \n",
       "1     Alurin Go Start Intel Celeron N4020/8GB/256GB ...     229.00     299.00   \n",
       "2     ASUS ExpertBook B1 B1502CBA-EJ0424X Intel Core...     549.00     789.00   \n",
       "3     MSI Katana GF66 12UC-082XES Intel Core i7-1270...     899.00    1199.00   \n",
       "4     HP 15S-FQ5085NS Intel Core i5-1235U/16GB/512GB...        NaN        NaN   \n",
       "...                                                 ...        ...        ...   \n",
       "2159  Razer Blade 17 FHD 360Hz Intel Core i7-11800H/...    2151.39    2699.99   \n",
       "2160  Razer Blade 17 FHD 360Hz Intel Core i7-11800H/...    2016.39    2899.99   \n",
       "2161  Razer Blade 17 FHD 360Hz Intel Core i7-11800H/...    2069.69    3399.99   \n",
       "2162  Razer Book 13 Intel Evo Core i7-1165G7/16GB/1T...    1515.19    1899.99   \n",
       "2163  Razer Book FHD+ Intel Evo Core i7-1165G7/16GB/...    1049.99    1699.99   \n",
       "\n",
       "       Price  Old Price 2           Status  Final Price  \n",
       "0        NaN          0.0      Patrocinado      1009.00  \n",
       "1        NaN          0.0     Envío gratis       299.00  \n",
       "2        NaN          0.0      Patrocinado       789.00  \n",
       "3        NaN          0.0     Envío gratis      1199.00  \n",
       "4     669.01          NaN      Patrocinado       669.01  \n",
       "...      ...          ...              ...          ...  \n",
       "2159     NaN          0.0  Reacondicionado      2699.99  \n",
       "2160     NaN          0.0  Reacondicionado      2899.99  \n",
       "2161     NaN          0.0  Reacondicionado      3399.99  \n",
       "2162     NaN          0.0  Reacondicionado      1899.99  \n",
       "2163     NaN          0.0  Reacondicionado      1699.99  \n",
       "\n",
       "[2160 rows x 7 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Laptops"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "ac65ec33",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     1009.00\n",
       "1      299.00\n",
       "2      789.00\n",
       "3     1199.00\n",
       "4      669.01\n",
       "5     1699.00\n",
       "6      909.00\n",
       "7      809.01\n",
       "8      519.00\n",
       "10    1149.00\n",
       "11     349.00\n",
       "12    1399.00\n",
       "13    1199.00\n",
       "14     476.99\n",
       "15     391.00\n",
       "16     699.00\n",
       "17     799.00\n",
       "18     549.00\n",
       "19     769.00\n",
       "20     999.00\n",
       "Name: Final Price, dtype: float64"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Laptops['Final Price'].head(20)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ebdc3d3c",
   "metadata": {},
   "source": [
    "The new column looks perfect"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5ce3afdf",
   "metadata": {},
   "source": [
    "# Defining Laptops caractheristics"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "544141c0",
   "metadata": {},
   "source": [
    "Now, we should define some Laptop caracteristics. Important columns would be:\n",
    "\n",
    "- #### Brand: Apple, MSI, Asus, etc\n",
    "- #### Model: Macbook, Katana, ExpertBook, etc\n",
    "- #### CPU: Intel Core i7, AMD Ryzen 5, etc\n",
    "- #### RAM: Integer value (4, 8, 16, etc)\n",
    "- #### Storage: Integer value (512, 256, 1024, etc)\n",
    "- #### Storage type: HDD or SDD\n",
    "- #### Screen: float value (15.6, 17.3, 16.1, etc)\n",
    "- #### GPU: RTX 3060, RTX 3050 Ti, RTX 4070, etc\n",
    "- #### Touch: Yes, No\n",
    "\n",
    "So let's work on it"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "bb28086b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     ASUS ExpertBook B1 B1502CBA-EJ0436X Intel Core...\n",
       "1     Alurin Go Start Intel Celeron N4020/8GB/256GB ...\n",
       "2     ASUS ExpertBook B1 B1502CBA-EJ0424X Intel Core...\n",
       "3     MSI Katana GF66 12UC-082XES Intel Core i7-1270...\n",
       "4     HP 15S-FQ5085NS Intel Core i5-1235U/16GB/512GB...\n",
       "5     MSI Crosshair 17 C12VF-264XES Intel Core i7-12...\n",
       "6     Lenovo Thinkpad E14 Gen 4 AMD Ryzen 5 5625U/8G...\n",
       "7     ASUS VivoBook 15 F515JA-EJ2882W Intel Core i7-...\n",
       "8     Medion Akoya E15415 Intel Core i5-10210U/8GB/2...\n",
       "10    HP Victus 16-d1038ns Intel Core i7-12700H/16GB...\n",
       "11    Lenovo V15 IGL Intel Celeron N4020/8GB/256 GB ...\n",
       "12    MSI Thin GF63 12VE-021XES Intel Core i7-12650H...\n",
       "13    ASUS ROG Strix G15 G513RC-HN088 AMD Ryzen 7 68...\n",
       "14    Lenovo V15 G3 ABA AMD Ryzen 5 5625U/16GB/512GB...\n",
       "15    Lenovo IdeaPad 1 15ADA7 AMD Ryzen 3 3250U/8GB/...\n",
       "16    HP 15S-FQ5013NS Intel Core i5-1235U/8GB/512GB ...\n",
       "17    Lenovo IdeaPad 3 15IAU7 Intel Core i5-1235U/16...\n",
       "18    HP 15S-FQ2163NS Intel Core i3-1115G4/8GB/512GB...\n",
       "19    ASUS VivoBook 15 F515JA-EJ4134 Intel Core i7-1...\n",
       "20    MSI GF63 Thin 11UC-446XES Intel Core i7-11800H...\n",
       "Name: Laptop, dtype: object"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Laptops['Laptop'].head(20)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d433dd41",
   "metadata": {},
   "source": [
    "## Brand\n",
    "\n",
    "In this column, we've created the dictionary file master_data\\brands.xlsx. Let's see how it looks like:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "9bc925d2",
   "metadata": {
    "scrolled": true
   },
   "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>Keyword</th>\n",
       "      <th>Brand</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Asus</td>\n",
       "      <td>Asus</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>MSI</td>\n",
       "      <td>MSI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Lenovo</td>\n",
       "      <td>Lenovo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Apple</td>\n",
       "      <td>Apple</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Dell</td>\n",
       "      <td>Dell</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Acer</td>\n",
       "      <td>Acer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Medion</td>\n",
       "      <td>Medion</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Razer</td>\n",
       "      <td>Razer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>LG</td>\n",
       "      <td>LG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>Samsung</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Gigabyte</td>\n",
       "      <td>Gigabyte</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>PcCom</td>\n",
       "      <td>PcCom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Dynabook Toshiba</td>\n",
       "      <td>Dynabook Toshiba</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Toshiba</td>\n",
       "      <td>Toshiba</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Alurin</td>\n",
       "      <td>Alurin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Deep Gaming</td>\n",
       "      <td>Deep Gaming</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Realme</td>\n",
       "      <td>Realme</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Primux</td>\n",
       "      <td>Primux</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Prixton</td>\n",
       "      <td>Prixton</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Thomson</td>\n",
       "      <td>Thomson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Vant</td>\n",
       "      <td>Vant</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Jetwing</td>\n",
       "      <td>Jetwing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Millenium</td>\n",
       "      <td>Millenium</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Innjoo</td>\n",
       "      <td>Innjoo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Denver</td>\n",
       "      <td>Denver</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Aspire</td>\n",
       "      <td>Acer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>ConceptD</td>\n",
       "      <td>Acer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Dynabook</td>\n",
       "      <td>Dynabook Toshiba</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>HP</td>\n",
       "      <td>HP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Microsoft</td>\n",
       "      <td>Microsoft</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             Keyword             Brand\n",
       "0               Asus              Asus\n",
       "1                MSI               MSI\n",
       "2             Lenovo            Lenovo\n",
       "3              Apple             Apple\n",
       "4               Dell              Dell\n",
       "5               Acer              Acer\n",
       "6             Medion            Medion\n",
       "7              Razer             Razer\n",
       "8                 LG                LG\n",
       "9            Samsung           Samsung\n",
       "10          Gigabyte          Gigabyte\n",
       "11             PcCom             PcCom\n",
       "12  Dynabook Toshiba  Dynabook Toshiba\n",
       "13           Toshiba           Toshiba\n",
       "14            Alurin            Alurin\n",
       "15       Deep Gaming       Deep Gaming\n",
       "16            Realme            Realme\n",
       "17            Primux            Primux\n",
       "18           Prixton           Prixton\n",
       "19           Thomson           Thomson\n",
       "20              Vant              Vant\n",
       "21           Jetwing           Jetwing\n",
       "22         Millenium         Millenium\n",
       "23            Innjoo            Innjoo\n",
       "24            Denver            Denver\n",
       "25            Aspire              Acer\n",
       "26          ConceptD              Acer\n",
       "27          Dynabook  Dynabook Toshiba\n",
       "28                HP                HP\n",
       "29         Microsoft         Microsoft"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "path_brands = os.path.join('master_data', 'brands.xlsx')\n",
    "\n",
    "brands_df = pd.read_excel(path_brands)\n",
    "brands_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9be76e0b",
   "metadata": {},
   "source": [
    "Perfect. Let's generate the new column Brand, searching the values Brand in Laptop. The coincidence will be set as Brand:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "4c70a44b",
   "metadata": {},
   "outputs": [],
   "source": [
    "brand_dict = brands_df.set_index('Keyword')['Brand'].to_dict()\n",
    "\n",
    "def assign_brand(row):\n",
    "    for keyword, brand in brand_dict.items():\n",
    "        if keyword.lower() in row['Laptop'].lower():\n",
    "            return brand\n",
    "    return None\n",
    "\n",
    "Laptops['Brand'] = Laptops.apply(assign_brand, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "f9c9062e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Asus                415\n",
       "HP                  368\n",
       "Lenovo              366\n",
       "MSI                 308\n",
       "Acer                137\n",
       "Apple               116\n",
       "Dell                 84\n",
       "Microsoft            77\n",
       "Gigabyte             48\n",
       "Razer                37\n",
       "Medion               32\n",
       "LG                   32\n",
       "Alurin               29\n",
       "PcCom                24\n",
       "Samsung              22\n",
       "Dynabook Toshiba     19\n",
       "Vant                 11\n",
       "Primux                8\n",
       "Deep Gaming           8\n",
       "Innjoo                6\n",
       "Thomson               4\n",
       "Prixton               3\n",
       "Millenium             2\n",
       "Denver                1\n",
       "Jetwing               1\n",
       "Realme                1\n",
       "Toshiba               1\n",
       "Name: Brand, dtype: int64"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Laptops['Brand'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "604d7bd2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "null_values = Laptops[Laptops['Brand'].isna()]\n",
    "null_values['Laptop'].tolist()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a847307f",
   "metadata": {},
   "source": [
    "Looks nice!"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fcf1272c",
   "metadata": {},
   "source": [
    "## CPU \n",
    "\n",
    "Now, in order to simplify it, let's generate the column 'CPU'. This column will help us further to generate the column 'Model'. In order to generate CPU, we will use the dictionary cpu.xlsx"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "ef9b585f",
   "metadata": {
    "scrolled": true
   },
   "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>Keyword</th>\n",
       "      <th>CPU</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Intel Core i7</td>\n",
       "      <td>Intel Core i7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Intel Core  i7</td>\n",
       "      <td>Intel Core i7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Intel Evo Core i7</td>\n",
       "      <td>Intel Evo Core i7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Intel Evo Core  i7</td>\n",
       "      <td>Intel Evo Core i7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Intel Core i5</td>\n",
       "      <td>Intel Core i5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Intel Core  i5</td>\n",
       "      <td>Intel Core i5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Ryzen 5</td>\n",
       "      <td>AMD Ryzen 5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Ryzen 7</td>\n",
       "      <td>AMD Ryzen 7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Intel Core i9</td>\n",
       "      <td>Intel Core i9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Intel Core i3</td>\n",
       "      <td>Intel Core i3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Ryzen 3</td>\n",
       "      <td>AMD Ryzen 3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Ryzen 9</td>\n",
       "      <td>AMD Ryzen 9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>M2 Pro</td>\n",
       "      <td>Apple M2 Pro</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>M2</td>\n",
       "      <td>Apple M2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>M1 Pro</td>\n",
       "      <td>Apple M1 Pro</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Octa Core</td>\n",
       "      <td>Octa Core</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>M1</td>\n",
       "      <td>Apple M1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>M2 Max</td>\n",
       "      <td>Apple M2 Max</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>M1 Max</td>\n",
       "      <td>Apple M1 Max</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Intel Celeron</td>\n",
       "      <td>Intel Celeron</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Intel Pentium</td>\n",
       "      <td>Intel Pentium</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>AMD Athlon</td>\n",
       "      <td>AMD Athlon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Intel Core M3</td>\n",
       "      <td>Intel Core M3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Qualcomm SD7c</td>\n",
       "      <td>Qualcomm Snapdragon 7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>AMD E</td>\n",
       "      <td>AMD E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Quad Core</td>\n",
       "      <td>Quad Core</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>AMD 3015Ce</td>\n",
       "      <td>AMD 3015Ce</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>AMD 3020e</td>\n",
       "      <td>AMD 3020e</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Microsoft SQ1</td>\n",
       "      <td>Microsoft SQ1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Intel Evo Core i5</td>\n",
       "      <td>Intel Evo Core i5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>AMD 3015e</td>\n",
       "      <td>AMD 3015e</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>Intel Evo Core i9</td>\n",
       "      <td>Intel Evo Core i9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>i7</td>\n",
       "      <td>Intel Core i7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>i5</td>\n",
       "      <td>Intel Core i5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>i3</td>\n",
       "      <td>Intel Core i3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>i9</td>\n",
       "      <td>Intel Core i9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>Ryzen 7</td>\n",
       "      <td>AMD Ryzen 7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>Ryzen R5</td>\n",
       "      <td>AMD Ryzen 5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>R5</td>\n",
       "      <td>AMD Radeon 5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>R3</td>\n",
       "      <td>AMD Radeon 3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>R7</td>\n",
       "      <td>AMD Radeon 7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>R9</td>\n",
       "      <td>AMD Radeon 9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>Qualcomm Snapdragon 7c</td>\n",
       "      <td>Qualcomm Snapdragon 7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>AMD Ryzen</td>\n",
       "      <td>AMD Ryzen 9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>Mediatek MT8183</td>\n",
       "      <td>Mediatek MT8183</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>Qualcomm Snapdragon 8</td>\n",
       "      <td>Qualcomm Snapdragon 8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>Qualcomm Kryo</td>\n",
       "      <td>Qualcomm Kryo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>Qualcomm Snapdragon SC7</td>\n",
       "      <td>Qualcomm Snapdragon 7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    Keyword                    CPU\n",
       "0             Intel Core i7          Intel Core i7\n",
       "1            Intel Core  i7          Intel Core i7\n",
       "2         Intel Evo Core i7      Intel Evo Core i7\n",
       "3        Intel Evo Core  i7      Intel Evo Core i7\n",
       "4             Intel Core i5          Intel Core i5\n",
       "5            Intel Core  i5          Intel Core i5\n",
       "6                   Ryzen 5            AMD Ryzen 5\n",
       "7                   Ryzen 7            AMD Ryzen 7\n",
       "8             Intel Core i9          Intel Core i9\n",
       "9             Intel Core i3          Intel Core i3\n",
       "10                  Ryzen 3            AMD Ryzen 3\n",
       "11                  Ryzen 9            AMD Ryzen 9\n",
       "12                   M2 Pro           Apple M2 Pro\n",
       "13                       M2               Apple M2\n",
       "14                   M1 Pro           Apple M1 Pro\n",
       "15                Octa Core              Octa Core\n",
       "16                       M1               Apple M1\n",
       "17                   M2 Max           Apple M2 Max\n",
       "18                   M1 Max           Apple M1 Max\n",
       "19            Intel Celeron          Intel Celeron\n",
       "20            Intel Pentium          Intel Pentium\n",
       "21               AMD Athlon             AMD Athlon\n",
       "22            Intel Core M3          Intel Core M3\n",
       "23            Qualcomm SD7c  Qualcomm Snapdragon 7\n",
       "24                    AMD E                  AMD E\n",
       "25                Quad Core              Quad Core\n",
       "26               AMD 3015Ce             AMD 3015Ce\n",
       "27                AMD 3020e              AMD 3020e\n",
       "28            Microsoft SQ1          Microsoft SQ1\n",
       "29        Intel Evo Core i5      Intel Evo Core i5\n",
       "30                AMD 3015e              AMD 3015e\n",
       "31        Intel Evo Core i9      Intel Evo Core i9\n",
       "32                       i7          Intel Core i7\n",
       "33                       i5          Intel Core i5\n",
       "34                       i3          Intel Core i3\n",
       "35                       i9          Intel Core i9\n",
       "36                  Ryzen 7            AMD Ryzen 7\n",
       "37                 Ryzen R5            AMD Ryzen 5\n",
       "38                       R5           AMD Radeon 5\n",
       "39                       R3           AMD Radeon 3\n",
       "40                       R7           AMD Radeon 7\n",
       "41                       R9           AMD Radeon 9\n",
       "42   Qualcomm Snapdragon 7c  Qualcomm Snapdragon 7\n",
       "43                AMD Ryzen            AMD Ryzen 9\n",
       "44          Mediatek MT8183        Mediatek MT8183\n",
       "45    Qualcomm Snapdragon 8  Qualcomm Snapdragon 8\n",
       "46            Qualcomm Kryo          Qualcomm Kryo\n",
       "47  Qualcomm Snapdragon SC7  Qualcomm Snapdragon 7"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "path_cpu = os.path.join('master_data', 'cpu.xlsx')\n",
    "\n",
    "cpu_df = pd.read_excel(path_cpu)\n",
    "cpu_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "533b9e75",
   "metadata": {},
   "outputs": [],
   "source": [
    "cpu_dict = cpu_df.set_index('Keyword')['CPU'].to_dict()\n",
    "\n",
    "def assign_cpu(row):\n",
    "    for keyword, cpu in cpu_dict.items():\n",
    "        if keyword.lower() in row['Laptop'].lower():\n",
    "            return cpu\n",
    "    return None\n",
    "\n",
    "Laptops['CPU'] = Laptops.apply(assign_cpu, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "6516b063",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Intel Core i7            710\n",
       "Intel Core i5            535\n",
       "AMD Ryzen 7              156\n",
       "Intel Core i3            130\n",
       "AMD Ryzen 5              127\n",
       "Intel Core i9             94\n",
       "Intel Celeron             94\n",
       "Intel Evo Core i7         82\n",
       "AMD Ryzen 9               44\n",
       "AMD Ryzen 3               44\n",
       "Intel Evo Core i5         30\n",
       "Apple M2                  28\n",
       "AMD 3020e                 13\n",
       "Apple M2 Pro              13\n",
       "Apple M1                  11\n",
       "AMD Athlon                10\n",
       "Intel Pentium             10\n",
       "Apple M1 Pro               7\n",
       "Intel Core M3              5\n",
       "AMD 3015e                  3\n",
       "Microsoft SQ1              3\n",
       "Qualcomm Snapdragon 7      3\n",
       "AMD Radeon 9               2\n",
       "Qualcomm Snapdragon 8      2\n",
       "Intel Evo Core i9          1\n",
       "Mediatek MT8183            1\n",
       "AMD 3015Ce                 1\n",
       "AMD Radeon 5               1\n",
       "Name: CPU, dtype: int64"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Laptops['CPU'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "3502aec9",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "null_values = Laptops[Laptops['CPU'].isna()]\n",
    "null_values['Laptop'].tolist()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d76e8b6a",
   "metadata": {},
   "source": [
    "Looks perfect!"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "88201f76",
   "metadata": {},
   "source": [
    "## Model\n",
    "\n",
    "The idea to get the Model, is the same as the previous caractheristics. We search for the models in dictionary model.xlsx"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "528c5410",
   "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>Keyword</th>\n",
       "      <th>Model</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>MacBook Pro</td>\n",
       "      <td>MacBook Pro</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>MacBook Air</td>\n",
       "      <td>MacBook Air</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>250</td>\n",
       "      <td>250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>V15</td>\n",
       "      <td>V15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Flex Advance</td>\n",
       "      <td>Flex Advance</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>131</th>\n",
       "      <td>V330</td>\n",
       "      <td>V330</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>132</th>\n",
       "      <td>Alpha</td>\n",
       "      <td>Alpha</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>133</th>\n",
       "      <td>Delta</td>\n",
       "      <td>Delta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>134</th>\n",
       "      <td>GL65</td>\n",
       "      <td>GL65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>135</th>\n",
       "      <td>GL75</td>\n",
       "      <td>GL75</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>136 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          Keyword         Model\n",
       "0     MacBook Pro   MacBook Pro\n",
       "1     MacBook Air   MacBook Air\n",
       "2            250            250\n",
       "3             V15           V15\n",
       "4    Flex Advance  Flex Advance\n",
       "..            ...           ...\n",
       "131          V330          V330\n",
       "132         Alpha         Alpha\n",
       "133         Delta         Delta\n",
       "134          GL65          GL65\n",
       "135          GL75          GL75\n",
       "\n",
       "[136 rows x 2 columns]"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "path_model = os.path.join('master_data', 'model.xlsx')\n",
    "\n",
    "model_df = pd.read_excel(path_model)\n",
    "model_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "ca85b58c",
   "metadata": {},
   "outputs": [],
   "source": [
    "model_dict = model_df.set_index('Keyword')['Model'].to_dict()\n",
    "\n",
    "def assign_model(row):\n",
    "    for keyword, model in model_dict.items():\n",
    "        if keyword.lower() in row['Laptop'].lower():\n",
    "            return model\n",
    "    return None\n",
    "\n",
    "Laptops['Model'] = Laptops.apply(assign_model, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "d691198d",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "15S            115\n",
       "IdeaPad        104\n",
       "ROG            101\n",
       "ThinkPad        99\n",
       "VivoBook        99\n",
       "              ... \n",
       "100w             1\n",
       "Electronics      1\n",
       "Cyborg           1\n",
       "14S              1\n",
       "GL75             1\n",
       "Name: Model, Length: 121, dtype: int64"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Laptops['Model'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "79398b6d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "null_values = Laptops[Laptops['Model'].isna()]\n",
    "null_values['Laptop'].tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "5e1193ba",
   "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>Laptop</th>\n",
       "      <th>New Price</th>\n",
       "      <th>Old Price</th>\n",
       "      <th>Price</th>\n",
       "      <th>Old Price 2</th>\n",
       "      <th>Status</th>\n",
       "      <th>Final Price</th>\n",
       "      <th>Brand</th>\n",
       "      <th>CPU</th>\n",
       "      <th>Model</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ASUS ExpertBook B1 B1502CBA-EJ0436X Intel Core...</td>\n",
       "      <td>699.00</td>\n",
       "      <td>1009.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Patrocinado</td>\n",
       "      <td>1009.00</td>\n",
       "      <td>Asus</td>\n",
       "      <td>Intel Core i5</td>\n",
       "      <td>ExpertBook</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alurin Go Start Intel Celeron N4020/8GB/256GB ...</td>\n",
       "      <td>229.00</td>\n",
       "      <td>299.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Envío gratis</td>\n",
       "      <td>299.00</td>\n",
       "      <td>Alurin</td>\n",
       "      <td>Intel Celeron</td>\n",
       "      <td>Go</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ASUS ExpertBook B1 B1502CBA-EJ0424X Intel Core...</td>\n",
       "      <td>549.00</td>\n",
       "      <td>789.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Patrocinado</td>\n",
       "      <td>789.00</td>\n",
       "      <td>Asus</td>\n",
       "      <td>Intel Core i3</td>\n",
       "      <td>ExpertBook</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>MSI Katana GF66 12UC-082XES Intel Core i7-1270...</td>\n",
       "      <td>899.00</td>\n",
       "      <td>1199.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Envío gratis</td>\n",
       "      <td>1199.00</td>\n",
       "      <td>MSI</td>\n",
       "      <td>Intel Core i7</td>\n",
       "      <td>Katana</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>HP 15S-FQ5085NS Intel Core i5-1235U/16GB/512GB...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>669.01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Patrocinado</td>\n",
       "      <td>669.01</td>\n",
       "      <td>HP</td>\n",
       "      <td>Intel Core i5</td>\n",
       "      <td>15S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2159</th>\n",
       "      <td>Razer Blade 17 FHD 360Hz Intel Core i7-11800H/...</td>\n",
       "      <td>2151.39</td>\n",
       "      <td>2699.99</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Reacondicionado</td>\n",
       "      <td>2699.99</td>\n",
       "      <td>Razer</td>\n",
       "      <td>Intel Core i7</td>\n",
       "      <td>Blade</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2160</th>\n",
       "      <td>Razer Blade 17 FHD 360Hz Intel Core i7-11800H/...</td>\n",
       "      <td>2016.39</td>\n",
       "      <td>2899.99</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Reacondicionado</td>\n",
       "      <td>2899.99</td>\n",
       "      <td>Razer</td>\n",
       "      <td>Intel Core i7</td>\n",
       "      <td>Blade</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2161</th>\n",
       "      <td>Razer Blade 17 FHD 360Hz Intel Core i7-11800H/...</td>\n",
       "      <td>2069.69</td>\n",
       "      <td>3399.99</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Reacondicionado</td>\n",
       "      <td>3399.99</td>\n",
       "      <td>Razer</td>\n",
       "      <td>Intel Core i7</td>\n",
       "      <td>Blade</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2162</th>\n",
       "      <td>Razer Book 13 Intel Evo Core i7-1165G7/16GB/1T...</td>\n",
       "      <td>1515.19</td>\n",
       "      <td>1899.99</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Reacondicionado</td>\n",
       "      <td>1899.99</td>\n",
       "      <td>Razer</td>\n",
       "      <td>Intel Evo Core i7</td>\n",
       "      <td>Book</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2163</th>\n",
       "      <td>Razer Book FHD+ Intel Evo Core i7-1165G7/16GB/...</td>\n",
       "      <td>1049.99</td>\n",
       "      <td>1699.99</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Reacondicionado</td>\n",
       "      <td>1699.99</td>\n",
       "      <td>Razer</td>\n",
       "      <td>Intel Evo Core i7</td>\n",
       "      <td>Book</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2160 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 Laptop  New Price  Old Price  \\\n",
       "0     ASUS ExpertBook B1 B1502CBA-EJ0436X Intel Core...     699.00    1009.00   \n",
       "1     Alurin Go Start Intel Celeron N4020/8GB/256GB ...     229.00     299.00   \n",
       "2     ASUS ExpertBook B1 B1502CBA-EJ0424X Intel Core...     549.00     789.00   \n",
       "3     MSI Katana GF66 12UC-082XES Intel Core i7-1270...     899.00    1199.00   \n",
       "4     HP 15S-FQ5085NS Intel Core i5-1235U/16GB/512GB...        NaN        NaN   \n",
       "...                                                 ...        ...        ...   \n",
       "2159  Razer Blade 17 FHD 360Hz Intel Core i7-11800H/...    2151.39    2699.99   \n",
       "2160  Razer Blade 17 FHD 360Hz Intel Core i7-11800H/...    2016.39    2899.99   \n",
       "2161  Razer Blade 17 FHD 360Hz Intel Core i7-11800H/...    2069.69    3399.99   \n",
       "2162  Razer Book 13 Intel Evo Core i7-1165G7/16GB/1T...    1515.19    1899.99   \n",
       "2163  Razer Book FHD+ Intel Evo Core i7-1165G7/16GB/...    1049.99    1699.99   \n",
       "\n",
       "       Price  Old Price 2           Status  Final Price   Brand  \\\n",
       "0        NaN          0.0      Patrocinado      1009.00    Asus   \n",
       "1        NaN          0.0     Envío gratis       299.00  Alurin   \n",
       "2        NaN          0.0      Patrocinado       789.00    Asus   \n",
       "3        NaN          0.0     Envío gratis      1199.00     MSI   \n",
       "4     669.01          NaN      Patrocinado       669.01      HP   \n",
       "...      ...          ...              ...          ...     ...   \n",
       "2159     NaN          0.0  Reacondicionado      2699.99   Razer   \n",
       "2160     NaN          0.0  Reacondicionado      2899.99   Razer   \n",
       "2161     NaN          0.0  Reacondicionado      3399.99   Razer   \n",
       "2162     NaN          0.0  Reacondicionado      1899.99   Razer   \n",
       "2163     NaN          0.0  Reacondicionado      1699.99   Razer   \n",
       "\n",
       "                    CPU       Model  \n",
       "0         Intel Core i5  ExpertBook  \n",
       "1         Intel Celeron          Go  \n",
       "2         Intel Core i3  ExpertBook  \n",
       "3         Intel Core i7      Katana  \n",
       "4         Intel Core i5         15S  \n",
       "...                 ...         ...  \n",
       "2159      Intel Core i7       Blade  \n",
       "2160      Intel Core i7       Blade  \n",
       "2161      Intel Core i7       Blade  \n",
       "2162  Intel Evo Core i7        Book  \n",
       "2163  Intel Evo Core i7        Book  \n",
       "\n",
       "[2160 rows x 10 columns]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Laptops"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9c70147d",
   "metadata": {},
   "source": [
    "Looks good!"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "529ce0b2",
   "metadata": {},
   "source": [
    "## RAM\n",
    "\n",
    "The strategy for this column is to extract the digits just before \"GB/\" and \"Gb/\"."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "37cb75e8",
   "metadata": {},
   "outputs": [],
   "source": [
    "Laptops['RAM'] = Laptops['Laptop'].str.extract(r'(\\d+)\\s*(?:GB/|G\\s*B)', expand=False)\n",
    "Laptops['RAM'] = Laptops['Laptop'].str.extract(r'(\\d+)\\s*(?:Gb/|G\\s*B)', expand=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "febe393f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "16     928\n",
       "8      813\n",
       "32     301\n",
       "4       68\n",
       "64      25\n",
       "12      15\n",
       "6        3\n",
       "40       2\n",
       "128      1\n",
       "512      1\n",
       "Name: RAM, dtype: int64"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Laptops['RAM'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "8cc9700a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Portátil Alurin Flex Advance Intel Core I5-1155G7 14\"',\n",
       " 'Alurin AMD R5 5500U 15.6\" 8 256 con SO',\n",
       " 'Alurin Intel Core I7 12th 15.6\" 16 500 con SO']"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "null_values = Laptops[Laptops['RAM'].isna()]\n",
    "null_values['Laptop'].tolist()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8a0e6a79",
   "metadata": {},
   "source": [
    "After that, we see there is 3 empty values in RAM. In order to simplify it, we fill it with the value '8'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "432829c6",
   "metadata": {},
   "outputs": [],
   "source": [
    "Laptops['RAM'] = Laptops['RAM'].fillna(8)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "941fb064",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "null_values = Laptops[Laptops['RAM'].isna()]\n",
    "null_values['Laptop'].tolist()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0d1eee87",
   "metadata": {},
   "source": [
    "Now we got it. However, we have a value of RAM of 512. Seems weird, let's see it in detail:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "079b2287",
   "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>Laptop</th>\n",
       "      <th>New Price</th>\n",
       "      <th>Old Price</th>\n",
       "      <th>Price</th>\n",
       "      <th>Old Price 2</th>\n",
       "      <th>Status</th>\n",
       "      <th>Final Price</th>\n",
       "      <th>Brand</th>\n",
       "      <th>CPU</th>\n",
       "      <th>Model</th>\n",
       "      <th>RAM</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1136</th>\n",
       "      <td>Apple MacBook Intel Core i5/512GB/12\" Gris Esp...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>684.65</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Reacondicionado</td>\n",
       "      <td>684.65</td>\n",
       "      <td>Apple</td>\n",
       "      <td>Intel Core i5</td>\n",
       "      <td>Macbook</td>\n",
       "      <td>512</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 Laptop  New Price  Old Price  \\\n",
       "1136  Apple MacBook Intel Core i5/512GB/12\" Gris Esp...        NaN        NaN   \n",
       "\n",
       "       Price  Old Price 2           Status  Final Price  Brand            CPU  \\\n",
       "1136  684.65          NaN  Reacondicionado       684.65  Apple  Intel Core i5   \n",
       "\n",
       "        Model  RAM  \n",
       "1136  Macbook  512  "
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filtered_df = Laptops.loc[Laptops['RAM'] == '512']\n",
    "filtered_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "44b4dd79",
   "metadata": {},
   "source": [
    "Actually this Lap has a RAM of 8. We replace it:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "7674c3db",
   "metadata": {},
   "outputs": [],
   "source": [
    "Laptops['RAM'] = Laptops['RAM'].replace('512', '8')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "3a93c0ab",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "16     928\n",
       "8      814\n",
       "32     301\n",
       "4       68\n",
       "64      25\n",
       "12      15\n",
       "8        3\n",
       "6        3\n",
       "40       2\n",
       "128      1\n",
       "Name: RAM, dtype: int64"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Laptops['RAM'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d5a82aa7",
   "metadata": {},
   "source": [
    "Now it's perfect!"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8a7355e8",
   "metadata": {},
   "source": [
    "## Status\n",
    "\n",
    "Now, we must simplify the Column Status. We just need two values. Refurbished, and new:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "3c258e8a",
   "metadata": {
    "scrolled": false
   },
   "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>Laptop</th>\n",
       "      <th>Status</th>\n",
       "      <th>Brand</th>\n",
       "      <th>Model</th>\n",
       "      <th>CPU</th>\n",
       "      <th>RAM</th>\n",
       "      <th>Final Price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ASUS ExpertBook B1 B1502CBA-EJ0436X Intel Core...</td>\n",
       "      <td>New</td>\n",
       "      <td>Asus</td>\n",
       "      <td>ExpertBook</td>\n",
       "      <td>Intel Core i5</td>\n",
       "      <td>8</td>\n",
       "      <td>1009.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alurin Go Start Intel Celeron N4020/8GB/256GB ...</td>\n",
       "      <td>New</td>\n",
       "      <td>Alurin</td>\n",
       "      <td>Go</td>\n",
       "      <td>Intel Celeron</td>\n",
       "      <td>8</td>\n",
       "      <td>299.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ASUS ExpertBook B1 B1502CBA-EJ0424X Intel Core...</td>\n",
       "      <td>New</td>\n",
       "      <td>Asus</td>\n",
       "      <td>ExpertBook</td>\n",
       "      <td>Intel Core i3</td>\n",
       "      <td>8</td>\n",
       "      <td>789.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>MSI Katana GF66 12UC-082XES Intel Core i7-1270...</td>\n",
       "      <td>New</td>\n",
       "      <td>MSI</td>\n",
       "      <td>Katana</td>\n",
       "      <td>Intel Core i7</td>\n",
       "      <td>16</td>\n",
       "      <td>1199.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>HP 15S-FQ5085NS Intel Core i5-1235U/16GB/512GB...</td>\n",
       "      <td>New</td>\n",
       "      <td>HP</td>\n",
       "      <td>15S</td>\n",
       "      <td>Intel Core i5</td>\n",
       "      <td>16</td>\n",
       "      <td>669.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2159</th>\n",
       "      <td>Razer Blade 17 FHD 360Hz Intel Core i7-11800H/...</td>\n",
       "      <td>Refurbished</td>\n",
       "      <td>Razer</td>\n",
       "      <td>Blade</td>\n",
       "      <td>Intel Core i7</td>\n",
       "      <td>16</td>\n",
       "      <td>2699.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2160</th>\n",
       "      <td>Razer Blade 17 FHD 360Hz Intel Core i7-11800H/...</td>\n",
       "      <td>Refurbished</td>\n",
       "      <td>Razer</td>\n",
       "      <td>Blade</td>\n",
       "      <td>Intel Core i7</td>\n",
       "      <td>16</td>\n",
       "      <td>2899.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2161</th>\n",
       "      <td>Razer Blade 17 FHD 360Hz Intel Core i7-11800H/...</td>\n",
       "      <td>Refurbished</td>\n",
       "      <td>Razer</td>\n",
       "      <td>Blade</td>\n",
       "      <td>Intel Core i7</td>\n",
       "      <td>32</td>\n",
       "      <td>3399.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2162</th>\n",
       "      <td>Razer Book 13 Intel Evo Core i7-1165G7/16GB/1T...</td>\n",
       "      <td>Refurbished</td>\n",
       "      <td>Razer</td>\n",
       "      <td>Book</td>\n",
       "      <td>Intel Evo Core i7</td>\n",
       "      <td>16</td>\n",
       "      <td>1899.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2163</th>\n",
       "      <td>Razer Book FHD+ Intel Evo Core i7-1165G7/16GB/...</td>\n",
       "      <td>Refurbished</td>\n",
       "      <td>Razer</td>\n",
       "      <td>Book</td>\n",
       "      <td>Intel Evo Core i7</td>\n",
       "      <td>16</td>\n",
       "      <td>1699.99</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2160 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 Laptop       Status   Brand  \\\n",
       "0     ASUS ExpertBook B1 B1502CBA-EJ0436X Intel Core...          New    Asus   \n",
       "1     Alurin Go Start Intel Celeron N4020/8GB/256GB ...          New  Alurin   \n",
       "2     ASUS ExpertBook B1 B1502CBA-EJ0424X Intel Core...          New    Asus   \n",
       "3     MSI Katana GF66 12UC-082XES Intel Core i7-1270...          New     MSI   \n",
       "4     HP 15S-FQ5085NS Intel Core i5-1235U/16GB/512GB...          New      HP   \n",
       "...                                                 ...          ...     ...   \n",
       "2159  Razer Blade 17 FHD 360Hz Intel Core i7-11800H/...  Refurbished   Razer   \n",
       "2160  Razer Blade 17 FHD 360Hz Intel Core i7-11800H/...  Refurbished   Razer   \n",
       "2161  Razer Blade 17 FHD 360Hz Intel Core i7-11800H/...  Refurbished   Razer   \n",
       "2162  Razer Book 13 Intel Evo Core i7-1165G7/16GB/1T...  Refurbished   Razer   \n",
       "2163  Razer Book FHD+ Intel Evo Core i7-1165G7/16GB/...  Refurbished   Razer   \n",
       "\n",
       "           Model                CPU RAM  Final Price  \n",
       "0     ExpertBook      Intel Core i5   8      1009.00  \n",
       "1             Go      Intel Celeron   8       299.00  \n",
       "2     ExpertBook      Intel Core i3   8       789.00  \n",
       "3         Katana      Intel Core i7  16      1199.00  \n",
       "4            15S      Intel Core i5  16       669.01  \n",
       "...          ...                ...  ..          ...  \n",
       "2159       Blade      Intel Core i7  16      2699.99  \n",
       "2160       Blade      Intel Core i7  16      2899.99  \n",
       "2161       Blade      Intel Core i7  32      3399.99  \n",
       "2162        Book  Intel Evo Core i7  16      1899.99  \n",
       "2163        Book  Intel Evo Core i7  16      1699.99  \n",
       "\n",
       "[2160 rows x 7 columns]"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Laptops['Status'] = Laptops['Status'].replace('Reacondicionado', 'Refurbished')\n",
    "Laptops['Status'] = Laptops['Status'].where(Laptops['Status'] == 'Refurbished', 'New')\n",
    "\n",
    "Laptops[['Laptop','Status','Brand','Model','CPU','RAM','Final Price']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "7473f60e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "New            1498\n",
       "Refurbished     662\n",
       "Name: Status, dtype: int64"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Laptops['Status'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "a55c0f66",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "null_values = Laptops[Laptops['Status'].isna()]\n",
    "null_values['Laptop'].tolist()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a9e598c8",
   "metadata": {},
   "source": [
    "Perfect!"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9c0101d7",
   "metadata": {},
   "source": [
    "## Storage type\n",
    "\n",
    "Now let's see how extract the Storage type. First of all, let' focus on HDD, SSD and eMMC:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "fdab50fa",
   "metadata": {},
   "outputs": [],
   "source": [
    "Laptops['Storage type'] = Laptops['Laptop'].apply(lambda x: 'HDD' if 'HDD' in x else ('SSD' if 'SSD' in x else ('eMMC' if 'eMMC' in x else ('eMMC' if 'EMMC' in x else ''))))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "fa877e65",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "SSD     2062\n",
       "eMMC      56\n",
       "          42\n",
       "Name: Storage type, dtype: int64"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Laptops['Storage type'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "491711d7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['ASUS Chromebook CX1400CNA-BV0210 Intel Celeron N3350/8GB/32GB/14\"',\n",
       " 'Portátil Alurin Flex Advance Intel Core I5-1155G7 14\"',\n",
       " 'ASUS ROG Strix G16 G614JZ-N3008 Intel Core i7-13650HX/32GB/1TB/RTX 4080/16\"',\n",
       " 'Prixton Flex Pro Intel Celeron N4020/4GB/64GB/11.6\" Táctil',\n",
       " 'Apple MacBook Pro Intel Core i5/8GB/256GB/13\" Plateado',\n",
       " 'Alurin AMD R5 5500U 15.6\" 8 256 con SO',\n",
       " 'Alurin Intel Core I7 12th 15.6\" 16 500 con SO',\n",
       " 'ASUS F515EA-BQ1625W Intel Core i3-1115G4/8GB/256GB/15.6\"',\n",
       " 'HP Chromebook x360 11 G3 Education Edition Intel Celeron N4020/4GB/32GB/11.6\" Táctil',\n",
       " 'HP Chromebook 11 G9 Intel Celeron N4500/4GB/32GB/11.6\"',\n",
       " 'Alurin Flex Advance Intel Core i5-1155G7/8GB/256GB/15.6\" + Windows 11 Home',\n",
       " 'PcCom Revolt 3050 Intel Core i7-13700H/16GB/500GB/RTX 3050/15.6\" + Windows 11 Home',\n",
       " 'PcCom Revolt 3050 Intel Core i7-13700H/16GB/500GB/RTX 3050/15.6\"',\n",
       " 'Apple MacBook Pro Intel Core i5/8GB/128GB/13\" Plateado',\n",
       " 'Microsoft Surface Pro 7 Intel Core i5-1035G4/8 GB/256 GB/12.3\" Negra',\n",
       " 'Apple MacBook Air i5/4GB/128GB/13.3\"',\n",
       " 'Apple MacBook Air i5/8GB/128GB/13.3\" Plata',\n",
       " 'HP 15S-EQ1148NS AMD Athlon Silver 3050U/8GB/512GB/15.6\"',\n",
       " 'Prixton Netbook Pro Intel Celeron N4020/4GB/128GB/15.6\"',\n",
       " 'ASUS VivoBook F515EA-BQ3013W Intel Core i5-1135G7/8GB/512GB/15.6\"',\n",
       " 'Apple MacBook Intel Core M3/8GB/256GB/12\" Oro',\n",
       " 'Apple MacBook Intel Core M3/8GB/256GB/12\" Gris Espacial',\n",
       " 'Apple MacBook Intel Core i5/512GB/12\" Gris Espacial',\n",
       " 'HP ProBook 640 G3 Intel Core i5-7200U/4GB/500GB/14\"',\n",
       " 'HP Victus 16-E0006NP AMD Ryzen 7 5800H 8GB/512GB/RTX3050/16,1\" (PT)',\n",
       " 'ASUS VivoBook 13 Slate OLED T3300KA-LQ031WS Intel Pentium Silver N6000/4GB/128GB/13.3\" Táctil',\n",
       " 'Apple MacBook Pro Intel Core i5/8GB/128GB/13\" Gris Espacial',\n",
       " 'Apple MacBook Pro Touch Bar Intel Core i7/16GB/512GB/15\" Plata',\n",
       " 'ASUS Chromebook CR1 CR1100CKA-GJ0132 Intel Celeron N4500/4GB/32GB/11.6\"',\n",
       " 'HP ProBook 430 G6 Intel Core i5-8265U/4GB/500GB/13.3\"',\n",
       " 'Lenovo V14 IIL Intel Core i5-1035G1/8GB/256GB/14\"',\n",
       " 'Lenovo V15 G2 ITL Intel Core i3-1115G4/8GB/256GB/15.6\"',\n",
       " 'Lenovo Yoga Chromebook C630 Intel Core i5-8250U/8GB/128GB/15.6\" Táctil',\n",
       " 'Medion Akoya E4251 Intel Celeron N4020/4GB/128GB/14\"',\n",
       " 'Microsoft Surface Pro 7 Intel Core i7-1065G7/16GB/256GB/12.3\" Negro',\n",
       " 'Microsoft Surface Pro 7 Intel Core i7-1065G7/16GB/512GB/12.3\" Platino',\n",
       " 'Samsung Chromebook 2 Intel Celeron N4500/4GB/64GB/12.4\" Táctil',\n",
       " 'Thomson NEO Z3 Qualcomm Snapdragon 850/8GB/256GB/13\"',\n",
       " 'Apple MacBook Air i5/4GB/256GB/13.3\" Plata',\n",
       " 'Apple MacBook Intel Core M3/8GB/256GB/12\" Plata',\n",
       " 'HP OMEN 15-DC0005NS Intel Core i7-8750H/8GB/1TB/GTX 1050/15.6\"',\n",
       " 'Medion Akoya E4251 Intel Celeron N4020/4GB/64GB/14\"']"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filtered_df = Laptops.loc[Laptops['Storage type'] == '']\n",
    "filtered_df['Laptop'].tolist()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1f5785df",
   "metadata": {},
   "source": [
    "We see there is 42 values without storage type. After checking it, it's correct since we don't see it in Laptop column."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "08e6b62c",
   "metadata": {},
   "source": [
    "## Storage\n",
    "\n",
    "For the storage column, we can extract the values just after the first \"GB/\" string. Let's try it:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "12b12452",
   "metadata": {},
   "outputs": [],
   "source": [
    "Laptops['Storage'] = Laptops['Laptop'].apply(lambda x: re.search(r'GB\\/(.*?)\\/', x).group(1) if re.search(r'GB\\/(.*?)\\/', x) else '')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "ea9e41f1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "512GB SSD                      909\n",
       "1TB SSD                        559\n",
       "256GB SSD                      403\n",
       "128GB SSD                       42\n",
       "2TB SSD                         41\n",
       "64GB eMMC                       32\n",
       "500GB SSD                       32\n",
       "256 GB SSD                      27\n",
       "512 GB SSD                      19\n",
       "128GB eMMC                      13\n",
       "256GB                           11\n",
       "                                10\n",
       "128GB                            8\n",
       "32GB eMMC                        8\n",
       "512GB                            5\n",
       "32GB                             4\n",
       "500GB                            4\n",
       "64GB                             3\n",
       "512GBSSD                         3\n",
       "1TB+1TB SSD                      3\n",
       "128GB EMMC                       2\n",
       "128 GB SSD                       2\n",
       "1 TB SSD                         2\n",
       "256 SSD                          2\n",
       "1TB                              2\n",
       "64GB+256GB SSD                   1\n",
       "512                              1\n",
       "256 GB                           1\n",
       "512GB SSD+32GB Intel Optane      1\n",
       "2565GB SSD                       1\n",
       "32GB EMMC                        1\n",
       "512GB SSD+1TB                    1\n",
       "256GBSSD                         1\n",
       "4TB SSD                          1\n",
       "3TB SSD                          1\n",
       "240GB SSD                        1\n",
       "1TB+256GB SSD                    1\n",
       "1TB+512GB SSD                    1\n",
       "2TB+2TB SSD                      1\n",
       "Name: Storage, dtype: int64"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Laptops['Storage'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "88010a31",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Portátil Alurin Flex Advance Intel Core I5-1155G7 14\"',\n",
       " 'Alurin AMD R5 5500U 15.6\" 8 256 con SO',\n",
       " 'Alurin Intel Core I7 12th 15.6\" 16 500 con SO',\n",
       " 'Acer Extensa 15 EX215-54 Intel Core i5-1135G7/8GB/256GB SSD',\n",
       " 'ASUS VivoBook F1500EA-EJ3066W Intel Core i7-1165G7/8GB RAM/512GB SSD/15.6\"',\n",
       " 'Apple MacBook Pro Intel Core i5/16Gb/512GB SSD/13.3\" Gris Espacial',\n",
       " 'Apple MacBook Intel Core i5/512GB/12\" Gris Espacial',\n",
       " \"Dell XPS 15 9520 Intel Core i7-12700H/16GB RAM/1TB SSD/RTX 3050 Ti/15.6'' (PT)\",\n",
       " 'Vant Edge 3 Intel Core i7-1255U/40GB RAM/1TB SSD/14\"',\n",
       " 'Apple MacBook Pro Touch Bar Intel Core i7/16Gb/1TB SSD/13.3\" Gris Espacial']"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filtered_df = Laptops.loc[Laptops['Storage'] == '']\n",
    "filtered_df['Laptop'].tolist()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3a615004",
   "metadata": {},
   "source": [
    "Only 10 empty values, quite good! Let's work on this pending values:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "0aa90612",
   "metadata": {},
   "outputs": [],
   "source": [
    "Laptops['Storage'] = Laptops['Laptop'].apply(lambda x: re.search(r'GB\\/(.*?)\\/', x).group(1) if re.search(r'GB\\/(.*?)\\/', x) else (re.search(r'(\\d+\\w+)\\sSSD', x).group(1) if re.search(r'(\\d+\\w+)\\sSSD', x) else ''))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "ded33e76",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "512GB SSD                      909\n",
       "1TB SSD                        559\n",
       "256GB SSD                      403\n",
       "128GB SSD                       42\n",
       "2TB SSD                         41\n",
       "64GB eMMC                       32\n",
       "500GB SSD                       32\n",
       "256 GB SSD                      27\n",
       "512 GB SSD                      19\n",
       "128GB eMMC                      13\n",
       "256GB                           12\n",
       "128GB                            8\n",
       "32GB eMMC                        8\n",
       "512GB                            7\n",
       "1TB                              5\n",
       "                                 4\n",
       "32GB                             4\n",
       "500GB                            4\n",
       "1TB+1TB SSD                      3\n",
       "64GB                             3\n",
       "512GBSSD                         3\n",
       "128 GB SSD                       2\n",
       "1 TB SSD                         2\n",
       "128GB EMMC                       2\n",
       "256 SSD                          2\n",
       "512                              1\n",
       "64GB+256GB SSD                   1\n",
       "256 GB                           1\n",
       "512GB SSD+32GB Intel Optane      1\n",
       "2565GB SSD                       1\n",
       "32GB EMMC                        1\n",
       "512GB SSD+1TB                    1\n",
       "256GBSSD                         1\n",
       "4TB SSD                          1\n",
       "3TB SSD                          1\n",
       "240GB SSD                        1\n",
       "1TB+256GB SSD                    1\n",
       "1TB+512GB SSD                    1\n",
       "2TB+2TB SSD                      1\n",
       "Name: Storage, dtype: int64"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Laptops['Storage'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "f2465c98",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Portátil Alurin Flex Advance Intel Core I5-1155G7 14\"',\n",
       " 'Alurin AMD R5 5500U 15.6\" 8 256 con SO',\n",
       " 'Alurin Intel Core I7 12th 15.6\" 16 500 con SO',\n",
       " 'Apple MacBook Intel Core i5/512GB/12\" Gris Espacial']"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filtered_df = Laptops.loc[Laptops['Storage'] == '']\n",
    "filtered_df['Laptop'].tolist()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a03c62c9",
   "metadata": {},
   "source": [
    "Good! Now only 4 pending values. Three of them we can set it manually:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "84a613e3",
   "metadata": {},
   "outputs": [],
   "source": [
    "Laptops.loc[Laptops['Laptop'] == 'Alurin AMD R5 5500U 15.6\" 8 256 con SO', 'Storage'] = '256'\n",
    "Laptops.loc[Laptops['Laptop'] == 'Alurin Intel Core I7 12th 15.6\" 16 500 con SO', 'Storage'] = '500'\n",
    "Laptops.loc[Laptops['Laptop'] == 'Apple MacBook Intel Core i5/512GB/12\" Gris Espacial', 'Storage'] = '512'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "cba0a217",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Portátil Alurin Flex Advance Intel Core I5-1155G7 14\"']"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filtered_df = Laptops.loc[Laptops['Storage'] == '']\n",
    "filtered_df['Laptop'].tolist()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e2f48aa5",
   "metadata": {},
   "source": [
    "Nice! Now let's clean the column. We must have only integers, expressed as GB values:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "1d0eee81",
   "metadata": {},
   "outputs": [],
   "source": [
    "Laptops['Storage'] = Laptops['Storage'].str.replace(' GB', '')\n",
    "Laptops['Storage'] = Laptops['Storage'].str.replace('GB', '')\n",
    "Laptops['Storage'] = Laptops['Storage'].str.replace(' TB', '')\n",
    "Laptops['Storage'] = Laptops['Storage'].str.replace('TB', '')\n",
    "Laptops['Storage'] = Laptops['Storage'].str.replace(' SSD', '')\n",
    "Laptops['Storage'] = Laptops['Storage'].str.replace('SSD', '')\n",
    "Laptops['Storage'] = Laptops['Storage'].str.replace(' eMMC', '')\n",
    "Laptops['Storage'] = Laptops['Storage'].str.replace('eMMC', '')\n",
    "Laptops['Storage'] = Laptops['Storage'].str.replace(' EMMC', '')\n",
    "Laptops['Storage'] = Laptops['Storage'].str.replace('EMMC', '')\n",
    "Laptops['Storage'] = Laptops['Storage'].str.replace(' Intel Optane', '')\n",
    "Laptops['Storage'] = Laptops['Storage'].str.replace('2565', '256')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "d9614d8c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "512       940\n",
       "1         566\n",
       "256       448\n",
       "128        67\n",
       "2          41\n",
       "500        37\n",
       "64         35\n",
       "32         13\n",
       "1+1         3\n",
       "4           1\n",
       "1+256       1\n",
       "240         1\n",
       "3           1\n",
       "2+2         1\n",
       "512+1       1\n",
       "512+32      1\n",
       "64+256      1\n",
       "            1\n",
       "1+512       1\n",
       "Name: Storage, dtype: int64"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Laptops['Storage'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "54252601",
   "metadata": {},
   "outputs": [],
   "source": [
    "Laptops['Storage'] = Laptops['Storage'].apply(lambda x: x.split('+')[-1].strip())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "e259d5e4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "512    941\n",
       "1      570\n",
       "256    450\n",
       "128     67\n",
       "2       42\n",
       "500     37\n",
       "64      35\n",
       "32      14\n",
       "         1\n",
       "4        1\n",
       "3        1\n",
       "240      1\n",
       "Name: Storage, dtype: int64"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Laptops['Storage'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "39332af6",
   "metadata": {},
   "source": [
    "Now we normalize the values in TB, to GB:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "e6af4ba8",
   "metadata": {},
   "outputs": [],
   "source": [
    "Laptops['Storage'] = Laptops['Storage'].apply(lambda x: int(x) * 1000 if x and int(x) < 10 else x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "60f6dc47",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "512     941\n",
       "1000    570\n",
       "256     450\n",
       "128      67\n",
       "2000     42\n",
       "500      37\n",
       "64       35\n",
       "32       14\n",
       "          1\n",
       "4000      1\n",
       "3000      1\n",
       "240       1\n",
       "Name: Storage, dtype: int64"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Laptops['Storage'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "c9eac997",
   "metadata": {},
   "outputs": [],
   "source": [
    "Laptops['Storage'] = pd.to_numeric(Laptops['Storage'], errors='coerce')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "0b7d2cb5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "512     941\n",
       "1000    570\n",
       "256     450\n",
       "128      67\n",
       "2000     42\n",
       "500      37\n",
       "64       35\n",
       "32       14\n",
       "0         1\n",
       "4000      1\n",
       "3000      1\n",
       "240       1\n",
       "Name: Storage, dtype: int64"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Laptops['Storage'] = Laptops['Storage'].fillna(0)\n",
    "Laptops['Storage'] = Laptops['Storage'].astype(int)\n",
    "Laptops['Storage'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "82839a20",
   "metadata": {},
   "source": [
    "Perfect!"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "79b4ba85",
   "metadata": {},
   "source": [
    "## Screen\n",
    "\n",
    "Let's work on the Screen column. The strategy is to extract the digits just before '\"':"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "0ad90d33",
   "metadata": {},
   "outputs": [],
   "source": [
    "Laptops['Screen'] = Laptops['Laptop'].apply(lambda x: re.search(r'/(?P<screen>[^/]+)\"', x).group('screen') if re.search(r'/(?P<screen>[^/]+)\"', x) else re.search(r'(\\d+(?:\\.\\d+))', x).group() if re.search(r'(\\d+(?:\\.\\d+))', x) else re.search(r'(\\d+)\"', x).group(1) if re.search(r'(\\d+)\"', x) else re.search(r\"(\\d+)'\", x).group(1) if re.search(r\"(\\d+)'\", x) else '')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "738a60ab",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "15.6                                            1008\n",
       "14                                               392\n",
       "16                                               173\n",
       "17.3                                             161\n",
       "13.3                                             131\n",
       "16.1                                              47\n",
       "17                                                33\n",
       "13                                                27\n",
       "15                                                21\n",
       "13.4                                              19\n",
       "13.5                                              19\n",
       "11.6                                              16\n",
       "14.2                                              14\n",
       "12.3                                              13\n",
       "13.6                                              11\n",
       "14.1                                              11\n",
       "16.2                                              10\n",
       "15.3                                               8\n",
       "10.5                                               7\n",
       "12.4                                               6\n",
       "14.4                                               6\n",
       "15.4                                               5\n",
       "                                                   4\n",
       "12                                                 4\n",
       "18                                                 3\n",
       "14.5                                               3\n",
       "13.9                                               2\n",
       "12.5                                               1\n",
       "16.0                                               1\n",
       "10.1                                               1\n",
       "10.95                                              1\n",
       "16,1                                               1\n",
       "15.6\" + Razer Soporte para Portátil hasta 15       1\n",
       "Name: Screen, dtype: int64"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Laptops['Screen'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "f03b48ea",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\juanm\\AppData\\Local\\Temp\\ipykernel_11560\\1109816736.py:3: FutureWarning: The default value of regex will change from True to False in a future version. In addition, single character regular expressions will *not* be treated as literal strings when regex=True.\n",
      "  Laptops['Screen'] = Laptops['Screen'].str.replace('+', '')\n"
     ]
    }
   ],
   "source": [
    "Laptops['Screen'] = Laptops['Screen'].str.replace(' Razer Soporte para Portátil hasta 15', '')\n",
    "Laptops['Screen'] = Laptops['Screen'].str.replace('\" ', '')\n",
    "Laptops['Screen'] = Laptops['Screen'].str.replace('+', '')\n",
    "Laptops['Screen'] = Laptops['Screen'].str.replace(',', '.')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "3c1b9905",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "15.6     1009\n",
       "14        392\n",
       "16        173\n",
       "17.3      161\n",
       "13.3      131\n",
       "16.1       48\n",
       "17         33\n",
       "13         27\n",
       "15         21\n",
       "13.4       19\n",
       "13.5       19\n",
       "11.6       16\n",
       "14.2       14\n",
       "12.3       13\n",
       "13.6       11\n",
       "14.1       11\n",
       "16.2       10\n",
       "15.3        8\n",
       "10.5        7\n",
       "14.4        6\n",
       "12.4        6\n",
       "15.4        5\n",
       "            4\n",
       "12          4\n",
       "14.5        3\n",
       "18          3\n",
       "13.9        2\n",
       "12.5        1\n",
       "16.0        1\n",
       "10.1        1\n",
       "10.95       1\n",
       "Name: Screen, dtype: int64"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Laptops['Screen'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "d5fae8cc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Acer Extensa 15 EX215-54 Intel Core i5-1135G7/8GB/256GB SSD',\n",
       " 'HP ENVY x360 2-in-1 Laptop 15-ew0008np Intel Core i7-1255U/16GB/512GB SSD/RTX2050/Tátil (PT)',\n",
       " 'Lenovo IdeaPad Gaming 3 15ACH6 AMD Ryzen 5 5600H/16GB/512GB SSD/RTX 3060',\n",
       " 'Lenovo ThinkPad P15 Gen 2 Intel Core i7-11850H/16GB/512GB SSD/RTX A2000']"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filtered_df = Laptops.loc[Laptops['Screen'] == '']\n",
    "filtered_df['Laptop'].tolist()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3d21f739",
   "metadata": {},
   "source": [
    "We have 4 rows that we don't have data about the Screen size. Let's convert the column to numeric:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "700c51f1",
   "metadata": {},
   "outputs": [],
   "source": [
    "Laptops['Screen'] = Laptops['Screen'].fillna(0)\n",
    "Laptops['Screen'] = pd.to_numeric(Laptops['Screen'], errors='coerce')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "e8b854f7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "15.60    1009\n",
       "14.00     392\n",
       "16.00     174\n",
       "17.30     161\n",
       "13.30     131\n",
       "16.10      48\n",
       "17.00      33\n",
       "13.00      27\n",
       "15.00      21\n",
       "13.50      19\n",
       "13.40      19\n",
       "11.60      16\n",
       "14.20      14\n",
       "12.30      13\n",
       "14.10      11\n",
       "13.60      11\n",
       "16.20      10\n",
       "15.30       8\n",
       "10.50       7\n",
       "14.40       6\n",
       "12.40       6\n",
       "15.40       5\n",
       "12.00       4\n",
       "18.00       3\n",
       "14.50       3\n",
       "13.90       2\n",
       "12.50       1\n",
       "10.10       1\n",
       "10.95       1\n",
       "Name: Screen, dtype: int64"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Laptops['Screen'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "da6cd0ee",
   "metadata": {},
   "source": [
    "## GPU\n",
    "\n",
    "Now let's create the column GPU. Since there is many possible values, the best approach is to use a dictionary gpu.xlsx:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "17f26d87",
   "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>Keyword</th>\n",
       "      <th>GPU</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>RTX 3060</td>\n",
       "      <td>RTX 3060</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>RTX 4070</td>\n",
       "      <td>RTX 4070</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>RTX 3070</td>\n",
       "      <td>RTX 3070</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>RTX 3050</td>\n",
       "      <td>RTX 3050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>RTX 4060</td>\n",
       "      <td>RTX 4060</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93</th>\n",
       "      <td>A730M</td>\n",
       "      <td>A 730M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>RX555X</td>\n",
       "      <td>RX 555X</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>T2000</td>\n",
       "      <td>T 2000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>610M</td>\n",
       "      <td>610 M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>5300M</td>\n",
       "      <td>Radeon Pro 5300M</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>98 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Keyword               GPU\n",
       "0   RTX 3060          RTX 3060\n",
       "1   RTX 4070          RTX 4070\n",
       "2   RTX 3070          RTX 3070\n",
       "3   RTX 3050          RTX 3050\n",
       "4   RTX 4060          RTX 4060\n",
       "..       ...               ...\n",
       "93     A730M            A 730M\n",
       "94    RX555X           RX 555X\n",
       "95     T2000            T 2000\n",
       "96      610M             610 M\n",
       "97     5300M  Radeon Pro 5300M\n",
       "\n",
       "[98 rows x 2 columns]"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "path_gpu = os.path.join('master_data', 'gpu.xlsx')\n",
    "\n",
    "gpu_df = pd.read_excel(path_gpu)\n",
    "gpu_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "id": "96929b30",
   "metadata": {},
   "outputs": [],
   "source": [
    "gpu_dict = gpu_df.set_index('Keyword')['GPU'].to_dict()\n",
    "\n",
    "def assign_gpu(row):\n",
    "    for keyword, gpu in gpu_dict.items():\n",
    "        if keyword.lower() in row['Laptop'].lower():\n",
    "            return gpu\n",
    "    return None\n",
    "\n",
    "Laptops['GPU'] = Laptops.apply(assign_gpu, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "58a3567e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "RTX 3050              129\n",
       "RTX 3060              122\n",
       "RTX 3070               97\n",
       "RTX 4060               62\n",
       "RTX 3080               51\n",
       "GTX 1650               50\n",
       "RTX 4070               40\n",
       "RTX 4050               33\n",
       "RTX 2070               25\n",
       "RTX 2060               20\n",
       "RTX 4080               18\n",
       "RTX 4090               17\n",
       "GTX 1660               14\n",
       "RTX 2050               11\n",
       "Radeon Pro 5500M        9\n",
       "RTX A2000               8\n",
       "RTX 2080                7\n",
       "MX 450                  7\n",
       "RTX A3000               7\n",
       "RTX A1000               6\n",
       "T 1200                  6\n",
       "MX 550                  5\n",
       "MX 330                  5\n",
       "T 500                   5\n",
       "T 550                   4\n",
       "GTX 1050                3\n",
       "Radeon Pro RX 560X      3\n",
       "RTX A5500               3\n",
       "RX 6500M                3\n",
       "RX 7600S                2\n",
       "T 600                   2\n",
       "Radeon Pro 5300M        2\n",
       "610 M                   2\n",
       "RX 6700M                1\n",
       "P 500                   1\n",
       "MX 130                  1\n",
       "A 370M                  1\n",
       "RX 6800S                1\n",
       "T 2000                  1\n",
       "RTX 3000                1\n",
       "A 730M                  1\n",
       "Radeon RX 6600M         1\n",
       "T 1000                  1\n",
       "GTX 1070                1\n",
       "Name: GPU, dtype: int64"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Laptops['GPU'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8ce6960f",
   "metadata": {},
   "source": [
    "It looks perfect! Not all the Laptops has a GPU, so the values seems to be nice."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "59891611",
   "metadata": {},
   "source": [
    "## Touch\n",
    "\n",
    "The last column is the column Touch. In this case, let's search for the words \"Táctil\", \"Tactil\", and similars. If so, the value would be Yes, if not, No:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "id": "c2e52a3a",
   "metadata": {},
   "outputs": [],
   "source": [
    "Laptops['Touch'] = Laptops['Laptop'].apply(lambda x: 'Yes' if 'Táctil' in x else ('Yes' if 'Tactil' in x else ('Yes' if 'Tátil' in x else ('Yes' if 'Tatil' in x else 'No'))))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "id": "0832cc7d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "No     1933\n",
       "Yes     227\n",
       "Name: Touch, dtype: int64"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Laptops['Touch'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7e205e38",
   "metadata": {},
   "source": [
    "We got it! It makes sense, that just a few laptops has a touch screen."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "id": "b7fe5712",
   "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>Laptop</th>\n",
       "      <th>New Price</th>\n",
       "      <th>Old Price</th>\n",
       "      <th>Price</th>\n",
       "      <th>Old Price 2</th>\n",
       "      <th>Status</th>\n",
       "      <th>Final Price</th>\n",
       "      <th>Brand</th>\n",
       "      <th>CPU</th>\n",
       "      <th>Model</th>\n",
       "      <th>RAM</th>\n",
       "      <th>Storage type</th>\n",
       "      <th>Storage</th>\n",
       "      <th>Screen</th>\n",
       "      <th>GPU</th>\n",
       "      <th>Touch</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ASUS ExpertBook B1 B1502CBA-EJ0436X Intel Core...</td>\n",
       "      <td>699.00</td>\n",
       "      <td>1009.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>New</td>\n",
       "      <td>1009.00</td>\n",
       "      <td>Asus</td>\n",
       "      <td>Intel Core i5</td>\n",
       "      <td>ExpertBook</td>\n",
       "      <td>8</td>\n",
       "      <td>SSD</td>\n",
       "      <td>512</td>\n",
       "      <td>15.6</td>\n",
       "      <td>None</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alurin Go Start Intel Celeron N4020/8GB/256GB ...</td>\n",
       "      <td>229.00</td>\n",
       "      <td>299.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>New</td>\n",
       "      <td>299.00</td>\n",
       "      <td>Alurin</td>\n",
       "      <td>Intel Celeron</td>\n",
       "      <td>Go</td>\n",
       "      <td>8</td>\n",
       "      <td>SSD</td>\n",
       "      <td>256</td>\n",
       "      <td>15.6</td>\n",
       "      <td>None</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ASUS ExpertBook B1 B1502CBA-EJ0424X Intel Core...</td>\n",
       "      <td>549.00</td>\n",
       "      <td>789.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>New</td>\n",
       "      <td>789.00</td>\n",
       "      <td>Asus</td>\n",
       "      <td>Intel Core i3</td>\n",
       "      <td>ExpertBook</td>\n",
       "      <td>8</td>\n",
       "      <td>SSD</td>\n",
       "      <td>256</td>\n",
       "      <td>15.6</td>\n",
       "      <td>None</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>MSI Katana GF66 12UC-082XES Intel Core i7-1270...</td>\n",
       "      <td>899.00</td>\n",
       "      <td>1199.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>New</td>\n",
       "      <td>1199.00</td>\n",
       "      <td>MSI</td>\n",
       "      <td>Intel Core i7</td>\n",
       "      <td>Katana</td>\n",
       "      <td>16</td>\n",
       "      <td>SSD</td>\n",
       "      <td>1000</td>\n",
       "      <td>15.6</td>\n",
       "      <td>RTX 3050</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>HP 15S-FQ5085NS Intel Core i5-1235U/16GB/512GB...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>669.01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>New</td>\n",
       "      <td>669.01</td>\n",
       "      <td>HP</td>\n",
       "      <td>Intel Core i5</td>\n",
       "      <td>15S</td>\n",
       "      <td>16</td>\n",
       "      <td>SSD</td>\n",
       "      <td>512</td>\n",
       "      <td>15.6</td>\n",
       "      <td>None</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2159</th>\n",
       "      <td>Razer Blade 17 FHD 360Hz Intel Core i7-11800H/...</td>\n",
       "      <td>2151.39</td>\n",
       "      <td>2699.99</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Refurbished</td>\n",
       "      <td>2699.99</td>\n",
       "      <td>Razer</td>\n",
       "      <td>Intel Core i7</td>\n",
       "      <td>Blade</td>\n",
       "      <td>16</td>\n",
       "      <td>SSD</td>\n",
       "      <td>1000</td>\n",
       "      <td>17.3</td>\n",
       "      <td>RTX 3060</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2160</th>\n",
       "      <td>Razer Blade 17 FHD 360Hz Intel Core i7-11800H/...</td>\n",
       "      <td>2016.39</td>\n",
       "      <td>2899.99</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Refurbished</td>\n",
       "      <td>2899.99</td>\n",
       "      <td>Razer</td>\n",
       "      <td>Intel Core i7</td>\n",
       "      <td>Blade</td>\n",
       "      <td>16</td>\n",
       "      <td>SSD</td>\n",
       "      <td>1000</td>\n",
       "      <td>17.3</td>\n",
       "      <td>RTX 3070</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2161</th>\n",
       "      <td>Razer Blade 17 FHD 360Hz Intel Core i7-11800H/...</td>\n",
       "      <td>2069.69</td>\n",
       "      <td>3399.99</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Refurbished</td>\n",
       "      <td>3399.99</td>\n",
       "      <td>Razer</td>\n",
       "      <td>Intel Core i7</td>\n",
       "      <td>Blade</td>\n",
       "      <td>32</td>\n",
       "      <td>SSD</td>\n",
       "      <td>1000</td>\n",
       "      <td>17.3</td>\n",
       "      <td>RTX 3080</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2162</th>\n",
       "      <td>Razer Book 13 Intel Evo Core i7-1165G7/16GB/1T...</td>\n",
       "      <td>1515.19</td>\n",
       "      <td>1899.99</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Refurbished</td>\n",
       "      <td>1899.99</td>\n",
       "      <td>Razer</td>\n",
       "      <td>Intel Evo Core i7</td>\n",
       "      <td>Book</td>\n",
       "      <td>16</td>\n",
       "      <td>SSD</td>\n",
       "      <td>1000</td>\n",
       "      <td>13.4</td>\n",
       "      <td>None</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2163</th>\n",
       "      <td>Razer Book FHD+ Intel Evo Core i7-1165G7/16GB/...</td>\n",
       "      <td>1049.99</td>\n",
       "      <td>1699.99</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Refurbished</td>\n",
       "      <td>1699.99</td>\n",
       "      <td>Razer</td>\n",
       "      <td>Intel Evo Core i7</td>\n",
       "      <td>Book</td>\n",
       "      <td>16</td>\n",
       "      <td>SSD</td>\n",
       "      <td>256</td>\n",
       "      <td>13.4</td>\n",
       "      <td>None</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2160 rows × 16 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 Laptop  New Price  Old Price  \\\n",
       "0     ASUS ExpertBook B1 B1502CBA-EJ0436X Intel Core...     699.00    1009.00   \n",
       "1     Alurin Go Start Intel Celeron N4020/8GB/256GB ...     229.00     299.00   \n",
       "2     ASUS ExpertBook B1 B1502CBA-EJ0424X Intel Core...     549.00     789.00   \n",
       "3     MSI Katana GF66 12UC-082XES Intel Core i7-1270...     899.00    1199.00   \n",
       "4     HP 15S-FQ5085NS Intel Core i5-1235U/16GB/512GB...        NaN        NaN   \n",
       "...                                                 ...        ...        ...   \n",
       "2159  Razer Blade 17 FHD 360Hz Intel Core i7-11800H/...    2151.39    2699.99   \n",
       "2160  Razer Blade 17 FHD 360Hz Intel Core i7-11800H/...    2016.39    2899.99   \n",
       "2161  Razer Blade 17 FHD 360Hz Intel Core i7-11800H/...    2069.69    3399.99   \n",
       "2162  Razer Book 13 Intel Evo Core i7-1165G7/16GB/1T...    1515.19    1899.99   \n",
       "2163  Razer Book FHD+ Intel Evo Core i7-1165G7/16GB/...    1049.99    1699.99   \n",
       "\n",
       "       Price  Old Price 2       Status  Final Price   Brand  \\\n",
       "0        NaN          0.0          New      1009.00    Asus   \n",
       "1        NaN          0.0          New       299.00  Alurin   \n",
       "2        NaN          0.0          New       789.00    Asus   \n",
       "3        NaN          0.0          New      1199.00     MSI   \n",
       "4     669.01          NaN          New       669.01      HP   \n",
       "...      ...          ...          ...          ...     ...   \n",
       "2159     NaN          0.0  Refurbished      2699.99   Razer   \n",
       "2160     NaN          0.0  Refurbished      2899.99   Razer   \n",
       "2161     NaN          0.0  Refurbished      3399.99   Razer   \n",
       "2162     NaN          0.0  Refurbished      1899.99   Razer   \n",
       "2163     NaN          0.0  Refurbished      1699.99   Razer   \n",
       "\n",
       "                    CPU       Model RAM Storage type  Storage  Screen  \\\n",
       "0         Intel Core i5  ExpertBook   8          SSD      512    15.6   \n",
       "1         Intel Celeron          Go   8          SSD      256    15.6   \n",
       "2         Intel Core i3  ExpertBook   8          SSD      256    15.6   \n",
       "3         Intel Core i7      Katana  16          SSD     1000    15.6   \n",
       "4         Intel Core i5         15S  16          SSD      512    15.6   \n",
       "...                 ...         ...  ..          ...      ...     ...   \n",
       "2159      Intel Core i7       Blade  16          SSD     1000    17.3   \n",
       "2160      Intel Core i7       Blade  16          SSD     1000    17.3   \n",
       "2161      Intel Core i7       Blade  32          SSD     1000    17.3   \n",
       "2162  Intel Evo Core i7        Book  16          SSD     1000    13.4   \n",
       "2163  Intel Evo Core i7        Book  16          SSD      256    13.4   \n",
       "\n",
       "           GPU Touch  \n",
       "0         None    No  \n",
       "1         None    No  \n",
       "2         None    No  \n",
       "3     RTX 3050    No  \n",
       "4         None    No  \n",
       "...        ...   ...  \n",
       "2159  RTX 3060    No  \n",
       "2160  RTX 3070    No  \n",
       "2161  RTX 3080    No  \n",
       "2162      None   Yes  \n",
       "2163      None   Yes  \n",
       "\n",
       "[2160 rows x 16 columns]"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Laptops"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dce8459b",
   "metadata": {},
   "source": [
    "# Generating the csv file\n",
    "\n",
    "The last part is generate the final file. We are going to generate a csv format file. But first of all, we need to reorganize the columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "id": "dcfd3047",
   "metadata": {},
   "outputs": [],
   "source": [
    "Laptops_csv = Laptops[['Laptop','Status','Brand','Model','CPU','RAM','Storage','Storage type','GPU','Screen','Touch','Final Price']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "id": "bf9a1a48",
   "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>Laptop</th>\n",
       "      <th>Status</th>\n",
       "      <th>Brand</th>\n",
       "      <th>Model</th>\n",
       "      <th>CPU</th>\n",
       "      <th>RAM</th>\n",
       "      <th>Storage</th>\n",
       "      <th>Storage type</th>\n",
       "      <th>GPU</th>\n",
       "      <th>Screen</th>\n",
       "      <th>Touch</th>\n",
       "      <th>Final Price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ASUS ExpertBook B1 B1502CBA-EJ0436X Intel Core...</td>\n",
       "      <td>New</td>\n",
       "      <td>Asus</td>\n",
       "      <td>ExpertBook</td>\n",
       "      <td>Intel Core i5</td>\n",
       "      <td>8</td>\n",
       "      <td>512</td>\n",
       "      <td>SSD</td>\n",
       "      <td>None</td>\n",
       "      <td>15.6</td>\n",
       "      <td>No</td>\n",
       "      <td>1009.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alurin Go Start Intel Celeron N4020/8GB/256GB ...</td>\n",
       "      <td>New</td>\n",
       "      <td>Alurin</td>\n",
       "      <td>Go</td>\n",
       "      <td>Intel Celeron</td>\n",
       "      <td>8</td>\n",
       "      <td>256</td>\n",
       "      <td>SSD</td>\n",
       "      <td>None</td>\n",
       "      <td>15.6</td>\n",
       "      <td>No</td>\n",
       "      <td>299.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ASUS ExpertBook B1 B1502CBA-EJ0424X Intel Core...</td>\n",
       "      <td>New</td>\n",
       "      <td>Asus</td>\n",
       "      <td>ExpertBook</td>\n",
       "      <td>Intel Core i3</td>\n",
       "      <td>8</td>\n",
       "      <td>256</td>\n",
       "      <td>SSD</td>\n",
       "      <td>None</td>\n",
       "      <td>15.6</td>\n",
       "      <td>No</td>\n",
       "      <td>789.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>MSI Katana GF66 12UC-082XES Intel Core i7-1270...</td>\n",
       "      <td>New</td>\n",
       "      <td>MSI</td>\n",
       "      <td>Katana</td>\n",
       "      <td>Intel Core i7</td>\n",
       "      <td>16</td>\n",
       "      <td>1000</td>\n",
       "      <td>SSD</td>\n",
       "      <td>RTX 3050</td>\n",
       "      <td>15.6</td>\n",
       "      <td>No</td>\n",
       "      <td>1199.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>HP 15S-FQ5085NS Intel Core i5-1235U/16GB/512GB...</td>\n",
       "      <td>New</td>\n",
       "      <td>HP</td>\n",
       "      <td>15S</td>\n",
       "      <td>Intel Core i5</td>\n",
       "      <td>16</td>\n",
       "      <td>512</td>\n",
       "      <td>SSD</td>\n",
       "      <td>None</td>\n",
       "      <td>15.6</td>\n",
       "      <td>No</td>\n",
       "      <td>669.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2159</th>\n",
       "      <td>Razer Blade 17 FHD 360Hz Intel Core i7-11800H/...</td>\n",
       "      <td>Refurbished</td>\n",
       "      <td>Razer</td>\n",
       "      <td>Blade</td>\n",
       "      <td>Intel Core i7</td>\n",
       "      <td>16</td>\n",
       "      <td>1000</td>\n",
       "      <td>SSD</td>\n",
       "      <td>RTX 3060</td>\n",
       "      <td>17.3</td>\n",
       "      <td>No</td>\n",
       "      <td>2699.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2160</th>\n",
       "      <td>Razer Blade 17 FHD 360Hz Intel Core i7-11800H/...</td>\n",
       "      <td>Refurbished</td>\n",
       "      <td>Razer</td>\n",
       "      <td>Blade</td>\n",
       "      <td>Intel Core i7</td>\n",
       "      <td>16</td>\n",
       "      <td>1000</td>\n",
       "      <td>SSD</td>\n",
       "      <td>RTX 3070</td>\n",
       "      <td>17.3</td>\n",
       "      <td>No</td>\n",
       "      <td>2899.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2161</th>\n",
       "      <td>Razer Blade 17 FHD 360Hz Intel Core i7-11800H/...</td>\n",
       "      <td>Refurbished</td>\n",
       "      <td>Razer</td>\n",
       "      <td>Blade</td>\n",
       "      <td>Intel Core i7</td>\n",
       "      <td>32</td>\n",
       "      <td>1000</td>\n",
       "      <td>SSD</td>\n",
       "      <td>RTX 3080</td>\n",
       "      <td>17.3</td>\n",
       "      <td>No</td>\n",
       "      <td>3399.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2162</th>\n",
       "      <td>Razer Book 13 Intel Evo Core i7-1165G7/16GB/1T...</td>\n",
       "      <td>Refurbished</td>\n",
       "      <td>Razer</td>\n",
       "      <td>Book</td>\n",
       "      <td>Intel Evo Core i7</td>\n",
       "      <td>16</td>\n",
       "      <td>1000</td>\n",
       "      <td>SSD</td>\n",
       "      <td>None</td>\n",
       "      <td>13.4</td>\n",
       "      <td>Yes</td>\n",
       "      <td>1899.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2163</th>\n",
       "      <td>Razer Book FHD+ Intel Evo Core i7-1165G7/16GB/...</td>\n",
       "      <td>Refurbished</td>\n",
       "      <td>Razer</td>\n",
       "      <td>Book</td>\n",
       "      <td>Intel Evo Core i7</td>\n",
       "      <td>16</td>\n",
       "      <td>256</td>\n",
       "      <td>SSD</td>\n",
       "      <td>None</td>\n",
       "      <td>13.4</td>\n",
       "      <td>Yes</td>\n",
       "      <td>1699.99</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2160 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 Laptop       Status   Brand  \\\n",
       "0     ASUS ExpertBook B1 B1502CBA-EJ0436X Intel Core...          New    Asus   \n",
       "1     Alurin Go Start Intel Celeron N4020/8GB/256GB ...          New  Alurin   \n",
       "2     ASUS ExpertBook B1 B1502CBA-EJ0424X Intel Core...          New    Asus   \n",
       "3     MSI Katana GF66 12UC-082XES Intel Core i7-1270...          New     MSI   \n",
       "4     HP 15S-FQ5085NS Intel Core i5-1235U/16GB/512GB...          New      HP   \n",
       "...                                                 ...          ...     ...   \n",
       "2159  Razer Blade 17 FHD 360Hz Intel Core i7-11800H/...  Refurbished   Razer   \n",
       "2160  Razer Blade 17 FHD 360Hz Intel Core i7-11800H/...  Refurbished   Razer   \n",
       "2161  Razer Blade 17 FHD 360Hz Intel Core i7-11800H/...  Refurbished   Razer   \n",
       "2162  Razer Book 13 Intel Evo Core i7-1165G7/16GB/1T...  Refurbished   Razer   \n",
       "2163  Razer Book FHD+ Intel Evo Core i7-1165G7/16GB/...  Refurbished   Razer   \n",
       "\n",
       "           Model                CPU RAM  Storage Storage type       GPU  \\\n",
       "0     ExpertBook      Intel Core i5   8      512          SSD      None   \n",
       "1             Go      Intel Celeron   8      256          SSD      None   \n",
       "2     ExpertBook      Intel Core i3   8      256          SSD      None   \n",
       "3         Katana      Intel Core i7  16     1000          SSD  RTX 3050   \n",
       "4            15S      Intel Core i5  16      512          SSD      None   \n",
       "...          ...                ...  ..      ...          ...       ...   \n",
       "2159       Blade      Intel Core i7  16     1000          SSD  RTX 3060   \n",
       "2160       Blade      Intel Core i7  16     1000          SSD  RTX 3070   \n",
       "2161       Blade      Intel Core i7  32     1000          SSD  RTX 3080   \n",
       "2162        Book  Intel Evo Core i7  16     1000          SSD      None   \n",
       "2163        Book  Intel Evo Core i7  16      256          SSD      None   \n",
       "\n",
       "      Screen Touch  Final Price  \n",
       "0       15.6    No      1009.00  \n",
       "1       15.6    No       299.00  \n",
       "2       15.6    No       789.00  \n",
       "3       15.6    No      1199.00  \n",
       "4       15.6    No       669.01  \n",
       "...      ...   ...          ...  \n",
       "2159    17.3    No      2699.99  \n",
       "2160    17.3    No      2899.99  \n",
       "2161    17.3    No      3399.99  \n",
       "2162    13.4   Yes      1899.99  \n",
       "2163    13.4   Yes      1699.99  \n",
       "\n",
       "[2160 rows x 12 columns]"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Laptops_csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "id": "45ad265b",
   "metadata": {},
   "outputs": [],
   "source": [
    "Laptops_csv.to_csv('laptops.csv', index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}