product_cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import data_extraction\n",
    "import pandas as pd\n",
    "import yaml, boto3\n",
    "\n",
    "pd.set_option('display.max_columns', None)\n",
    "\n",
    "myObj = data_extraction.DataExtractor()\n",
    "\n",
    "s3 = boto3.client('s3')\n",
    "\n",
    "s3.download_file(\"data-handling-public\",'products.csv','products.csv')\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(\"products.csv\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "df is a dataframe that now contains the data from our table\n",
    "These are the first & last few lines of the database then!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>Unnamed: 0</th>\n",
       "      <th>product_name</th>\n",
       "      <th>product_price</th>\n",
       "      <th>weight</th>\n",
       "      <th>category</th>\n",
       "      <th>EAN</th>\n",
       "      <th>date_added</th>\n",
       "      <th>uuid</th>\n",
       "      <th>removed</th>\n",
       "      <th>product_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>FurReal Dazzlin' Dimples My Playful Dolphin</td>\n",
       "      <td>£39.99</td>\n",
       "      <td>1.6kg</td>\n",
       "      <td>toys-and-games</td>\n",
       "      <td>7425710935115</td>\n",
       "      <td>2005-12-02</td>\n",
       "      <td>83dc0a69-f96f-4c34-bcb7-928acae19a94</td>\n",
       "      <td>Still_avaliable</td>\n",
       "      <td>R7-3126933h</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Tiffany's World Day Out At The Park</td>\n",
       "      <td>£12.99</td>\n",
       "      <td>0.48kg</td>\n",
       "      <td>toys-and-games</td>\n",
       "      <td>487128731892</td>\n",
       "      <td>2006-01-09</td>\n",
       "      <td>712254d7-aea7-4310-aff8-8bcdd0aec7ff</td>\n",
       "      <td>Still_avaliable</td>\n",
       "      <td>C2-7287916l</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>Tiffany's World Pups Picnic Playset</td>\n",
       "      <td>£7.00</td>\n",
       "      <td>590g</td>\n",
       "      <td>toys-and-games</td>\n",
       "      <td>1945816904649</td>\n",
       "      <td>1997-03-29</td>\n",
       "      <td>b089ef6f-b628-4e37-811d-fffe0102ba64</td>\n",
       "      <td>Still_avaliable</td>\n",
       "      <td>S7-1175877v</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>Tiffany's World Wildlife Park Adventures</td>\n",
       "      <td>£12.99</td>\n",
       "      <td>540g</td>\n",
       "      <td>toys-and-games</td>\n",
       "      <td>1569790890899</td>\n",
       "      <td>2013-03-20</td>\n",
       "      <td>d55de422-8b98-47d6-9991-e4bc4c5c0cb0</td>\n",
       "      <td>Removed</td>\n",
       "      <td>D8-8421505n</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>Cosatto Cosy Dolls Pram</td>\n",
       "      <td>£30.00</td>\n",
       "      <td>1.91kg</td>\n",
       "      <td>toys-and-games</td>\n",
       "      <td>7142740213920</td>\n",
       "      <td>2007-12-23</td>\n",
       "      <td>7945b657-cb02-4cc5-96cf-f65ed0a8f235</td>\n",
       "      <td>Still_avaliable</td>\n",
       "      <td>B6-2596063a</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0                                 product_name product_price  \\\n",
       "0           0  FurReal Dazzlin' Dimples My Playful Dolphin        £39.99   \n",
       "1           1          Tiffany's World Day Out At The Park        £12.99   \n",
       "2           2          Tiffany's World Pups Picnic Playset         £7.00   \n",
       "3           3     Tiffany's World Wildlife Park Adventures        £12.99   \n",
       "4           4                      Cosatto Cosy Dolls Pram        £30.00   \n",
       "\n",
       "   weight        category            EAN  date_added  \\\n",
       "0   1.6kg  toys-and-games  7425710935115  2005-12-02   \n",
       "1  0.48kg  toys-and-games   487128731892  2006-01-09   \n",
       "2    590g  toys-and-games  1945816904649  1997-03-29   \n",
       "3    540g  toys-and-games  1569790890899  2013-03-20   \n",
       "4  1.91kg  toys-and-games  7142740213920  2007-12-23   \n",
       "\n",
       "                                   uuid          removed product_code  \n",
       "0  83dc0a69-f96f-4c34-bcb7-928acae19a94  Still_avaliable  R7-3126933h  \n",
       "1  712254d7-aea7-4310-aff8-8bcdd0aec7ff  Still_avaliable  C2-7287916l  \n",
       "2  b089ef6f-b628-4e37-811d-fffe0102ba64  Still_avaliable  S7-1175877v  \n",
       "3  d55de422-8b98-47d6-9991-e4bc4c5c0cb0          Removed  D8-8421505n  \n",
       "4  7945b657-cb02-4cc5-96cf-f65ed0a8f235  Still_avaliable  B6-2596063a  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>Unnamed: 0</th>\n",
       "      <th>product_name</th>\n",
       "      <th>product_price</th>\n",
       "      <th>weight</th>\n",
       "      <th>category</th>\n",
       "      <th>EAN</th>\n",
       "      <th>date_added</th>\n",
       "      <th>uuid</th>\n",
       "      <th>removed</th>\n",
       "      <th>product_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1848</th>\n",
       "      <td>1848</td>\n",
       "      <td>Goodmans Qi Autosense In Car Phone Holder</td>\n",
       "      <td>£15.00</td>\n",
       "      <td>125g</td>\n",
       "      <td>diy</td>\n",
       "      <td>8185827751600</td>\n",
       "      <td>2020-08-28</td>\n",
       "      <td>3ff21cb1-8e90-499d-b5a0-6839fb83e70f</td>\n",
       "      <td>Still_avaliable</td>\n",
       "      <td>x4-2227164k</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1849</th>\n",
       "      <td>1849</td>\n",
       "      <td>Goodmans Qi Wireless Charging Phone Stand</td>\n",
       "      <td>£15.00</td>\n",
       "      <td>134g</td>\n",
       "      <td>diy</td>\n",
       "      <td>9769724533935</td>\n",
       "      <td>2012-05-20</td>\n",
       "      <td>a7910f74-c2b6-4144-98b7-30ca68f34d2d</td>\n",
       "      <td>Still_avaliable</td>\n",
       "      <td>i3-8338545Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1850</th>\n",
       "      <td>1850</td>\n",
       "      <td>RAC 12V Wet &amp; Dry Vacuum</td>\n",
       "      <td>£18.00</td>\n",
       "      <td>820g</td>\n",
       "      <td>diy</td>\n",
       "      <td>3113855860327</td>\n",
       "      <td>1993-11-28</td>\n",
       "      <td>66d3efa0-f480-4da9-893b-e8aaff53e27b</td>\n",
       "      <td>Still_avaliable</td>\n",
       "      <td>n4-1642658b</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1851</th>\n",
       "      <td>1851</td>\n",
       "      <td>Goodmans Qi Autosense In Car Phone Holder</td>\n",
       "      <td>£15.00</td>\n",
       "      <td>125g</td>\n",
       "      <td>diy</td>\n",
       "      <td>9195087467400</td>\n",
       "      <td>2016-09-20</td>\n",
       "      <td>efb472f1-8f00-448e-a874-9fa9812eeab5</td>\n",
       "      <td>Still_avaliable</td>\n",
       "      <td>A3-7619070S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1852</th>\n",
       "      <td>1852</td>\n",
       "      <td>Beldray Telescopic Ladder 2.9m</td>\n",
       "      <td>£69.00</td>\n",
       "      <td>9.4kg</td>\n",
       "      <td>diy</td>\n",
       "      <td>8518402353072</td>\n",
       "      <td>2006-10-07</td>\n",
       "      <td>cfe701da-6493-4a7f-83cf-9aab1650f689</td>\n",
       "      <td>Still_avaliable</td>\n",
       "      <td>i6-2851854w</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Unnamed: 0                               product_name product_price  \\\n",
       "1848        1848  Goodmans Qi Autosense In Car Phone Holder        £15.00   \n",
       "1849        1849  Goodmans Qi Wireless Charging Phone Stand        £15.00   \n",
       "1850        1850                   RAC 12V Wet & Dry Vacuum        £18.00   \n",
       "1851        1851  Goodmans Qi Autosense In Car Phone Holder        £15.00   \n",
       "1852        1852             Beldray Telescopic Ladder 2.9m        £69.00   \n",
       "\n",
       "     weight category            EAN  date_added  \\\n",
       "1848   125g      diy  8185827751600  2020-08-28   \n",
       "1849   134g      diy  9769724533935  2012-05-20   \n",
       "1850   820g      diy  3113855860327  1993-11-28   \n",
       "1851   125g      diy  9195087467400  2016-09-20   \n",
       "1852  9.4kg      diy  8518402353072  2006-10-07   \n",
       "\n",
       "                                      uuid          removed product_code  \n",
       "1848  3ff21cb1-8e90-499d-b5a0-6839fb83e70f  Still_avaliable  x4-2227164k  \n",
       "1849  a7910f74-c2b6-4144-98b7-30ca68f34d2d  Still_avaliable  i3-8338545Z  \n",
       "1850  66d3efa0-f480-4da9-893b-e8aaff53e27b  Still_avaliable  n4-1642658b  \n",
       "1851  efb472f1-8f00-448e-a874-9fa9812eeab5  Still_avaliable  A3-7619070S  \n",
       "1852  cfe701da-6493-4a7f-83cf-9aab1650f689  Still_avaliable  i6-2851854w  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.tail(5)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "try to clean up thei weights"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "could not convert string to float: '9GO9NZ5JT'\n",
      "could not convert string to float: 'Z8ZTDGUZV'\n",
      "could not convert string to float: 'MX180RYSH'\n",
      "could not convert string to float: '77g '\n",
      "could not convert string to float: '16o'\n"
     ]
    }
   ],
   "source": [
    "import data_cleaning\n",
    "\n",
    "myObj = data_cleaning.DataCleaning()\n",
    "\n",
    "df = myObj.convert_product_weights(df=df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1853 entries, 0 to 1852\n",
      "Data columns (total 10 columns):\n",
      " #   Column         Non-Null Count  Dtype  \n",
      "---  ------         --------------  -----  \n",
      " 0   Unnamed: 0     1853 non-null   int64  \n",
      " 1   product_name   1849 non-null   object \n",
      " 2   product_price  1849 non-null   object \n",
      " 3   weight         1844 non-null   float64\n",
      " 4   category       1849 non-null   object \n",
      " 5   EAN            1849 non-null   object \n",
      " 6   date_added     1849 non-null   object \n",
      " 7   uuid           1849 non-null   object \n",
      " 8   removed        1849 non-null   object \n",
      " 9   product_code   1849 non-null   object \n",
      "dtypes: float64(1), int64(1), object(8)\n",
      "memory usage: 144.9+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Lets drop the unlabelled column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.drop(columns=[\"Unnamed: 0\"])"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "remove £ from price"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['product_price'] = df['product_price'].str.replace('£', '', regex=False)\n",
    "df['product_price'] = pd.to_numeric(df['product_price'],errors=\"coerce\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We have some date columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['date_added']=pd.to_datetime(df['date_added'],infer_datetime_format=True, errors='coerce')"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "inspect some potentially categorical data:\n",
    "removed\n",
    "category"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count                1849\n",
       "unique                  5\n",
       "top       Still_avaliable\n",
       "freq                 1752\n",
       "Name: removed, dtype: object"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"removed\"].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count         1849\n",
       "unique          10\n",
       "top       homeware\n",
       "freq          1138\n",
       "Name: category, dtype: object"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"category\"].describe()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Not bad."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"removed\"] = df[\"removed\"].astype(\"category\")\n",
    "df[\"category\"] = df[\"category\"].astype(\"category\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Yes. What about duplicate lines?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.duplicated().sum()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "3! ok. Let's just check on the critical columns only - not system generated ones"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.duplicated(subset=[\"product_name\", \"EAN\"],keep=\"first\").sum()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Still 3\n",
    "lets drop nas too"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop_duplicates(subset=[\"product_name\", \"EAN\"],keep=\"first\",inplace=True)\n",
    "df.dropna(inplace=True,axis=\"index\",how=\"any\") # drops the whole row if any columns have an nan\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "make sure we've not dropped everythign"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 1844 entries, 0 to 1852\n",
      "Data columns (total 9 columns):\n",
      " #   Column         Non-Null Count  Dtype         \n",
      "---  ------         --------------  -----         \n",
      " 0   product_name   1844 non-null   object        \n",
      " 1   product_price  1844 non-null   float64       \n",
      " 2   weight         1844 non-null   float64       \n",
      " 3   category       1844 non-null   category      \n",
      " 4   EAN            1844 non-null   object        \n",
      " 5   date_added     1844 non-null   datetime64[ns]\n",
      " 6   uuid           1844 non-null   object        \n",
      " 7   removed        1844 non-null   category      \n",
      " 8   product_code   1844 non-null   object        \n",
      "dtypes: category(2), datetime64[ns](1), float64(2), object(4)\n",
      "memory usage: 119.4+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now put the code into a local database maybe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "import database_utils\n",
    "\n",
    "\n",
    "myObject = database_utils.DatabaseConnector()\n",
    "\n",
    "\n",
    "myObject.upload_to_db(df,\"dim_products\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ideas for future:\n",
    "Misspellings: https://medium.com/geekculture/how-to-fix-misspelled-words-for-your-next-nlp-project-with-one-line-of-code-98888a5ff207\n",
    "Common variants of companies - \"ltd.\" vs \"ltd\" vs \"limited\""
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "retail-data",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.9"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}