store_cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "451\n"
     ]
    }
   ],
   "source": [
    "import data_extraction\n",
    "import pandas as pd\n",
    "import yaml\n",
    "\n",
    "pd.set_option('display.max_columns', None)\n",
    "\n",
    "myObj = data_extraction.DataExtractor()\n",
    "\n",
    "stream = open('db_creds.yaml', 'r') \n",
    "headers = yaml.safe_load(stream)\n",
    "\n",
    "number = myObj.list_number_of_stores(endpoint=\"/prod/number_stores\",header= headers)\n",
    "print(number)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DataFrame constructor not properly called!\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "pandas.core.frame.DataFrame"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "\n",
    "intNumStores = int(number)\n",
    "\n",
    "df = myObj.retrieve_stores_data(endpoint=\"/prod/store_details/\",header= headers,totalStores=intNumStores)\n",
    "\n",
    "type(df)"
   ]
  },
  {
   "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>index</th>\n",
       "      <th>address</th>\n",
       "      <th>longitude</th>\n",
       "      <th>lat</th>\n",
       "      <th>locality</th>\n",
       "      <th>store_code</th>\n",
       "      <th>staff_numbers</th>\n",
       "      <th>opening_date</th>\n",
       "      <th>store_type</th>\n",
       "      <th>latitude</th>\n",
       "      <th>country_code</th>\n",
       "      <th>continent</th>\n",
       "      <th>message</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.0</td>\n",
       "      <td>N/A</td>\n",
       "      <td>N/A</td>\n",
       "      <td>N/A</td>\n",
       "      <td>N/A</td>\n",
       "      <td>WEB-1388012W</td>\n",
       "      <td>325</td>\n",
       "      <td>2010-06-12</td>\n",
       "      <td>Web Portal</td>\n",
       "      <td>None</td>\n",
       "      <td>GB</td>\n",
       "      <td>Europe</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.0</td>\n",
       "      <td>Flat 72W\\nSally isle\\nEast Deantown\\nE7B 8EB, ...</td>\n",
       "      <td>51.62907</td>\n",
       "      <td>None</td>\n",
       "      <td>High Wycombe</td>\n",
       "      <td>HI-9B97EE4E</td>\n",
       "      <td>34</td>\n",
       "      <td>1996-10-25</td>\n",
       "      <td>Local</td>\n",
       "      <td>-0.74934</td>\n",
       "      <td>GB</td>\n",
       "      <td>Europe</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2.0</td>\n",
       "      <td>Heckerstraße 4/5\\n50491 Säckingen, Landshut</td>\n",
       "      <td>48.52961</td>\n",
       "      <td>None</td>\n",
       "      <td>Landshut</td>\n",
       "      <td>LA-0772C7B9</td>\n",
       "      <td>92</td>\n",
       "      <td>2013-04-12</td>\n",
       "      <td>Super Store</td>\n",
       "      <td>12.16179</td>\n",
       "      <td>DE</td>\n",
       "      <td>Europe</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3.0</td>\n",
       "      <td>5 Harrison tunnel\\nSouth Lydia\\nWC9 2BE, Westbury</td>\n",
       "      <td>51.26</td>\n",
       "      <td>None</td>\n",
       "      <td>Westbury</td>\n",
       "      <td>WE-1DE82CEE</td>\n",
       "      <td>69</td>\n",
       "      <td>2014-01-02</td>\n",
       "      <td>Super Store</td>\n",
       "      <td>-2.1875</td>\n",
       "      <td>GB</td>\n",
       "      <td>Europe</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4.0</td>\n",
       "      <td>Studio 6\\nStephen landing\\nSouth Simon\\nB77 2W...</td>\n",
       "      <td>53.0233</td>\n",
       "      <td>None</td>\n",
       "      <td>Belper</td>\n",
       "      <td>BE-18074576</td>\n",
       "      <td>35</td>\n",
       "      <td>2019-09-09</td>\n",
       "      <td>Local</td>\n",
       "      <td>-1.48119</td>\n",
       "      <td>GB</td>\n",
       "      <td>Europe</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   index                                            address longitude   lat  \\\n",
       "0    0.0                                                N/A       N/A   N/A   \n",
       "1    1.0  Flat 72W\\nSally isle\\nEast Deantown\\nE7B 8EB, ...  51.62907  None   \n",
       "2    2.0        Heckerstraße 4/5\\n50491 Säckingen, Landshut  48.52961  None   \n",
       "3    3.0  5 Harrison tunnel\\nSouth Lydia\\nWC9 2BE, Westbury     51.26  None   \n",
       "4    4.0  Studio 6\\nStephen landing\\nSouth Simon\\nB77 2W...   53.0233  None   \n",
       "\n",
       "       locality    store_code staff_numbers opening_date   store_type  \\\n",
       "0           N/A  WEB-1388012W           325   2010-06-12   Web Portal   \n",
       "1  High Wycombe   HI-9B97EE4E            34   1996-10-25        Local   \n",
       "2      Landshut   LA-0772C7B9            92   2013-04-12  Super Store   \n",
       "3      Westbury   WE-1DE82CEE            69   2014-01-02  Super Store   \n",
       "4        Belper   BE-18074576            35   2019-09-09        Local   \n",
       "\n",
       "   latitude country_code continent message  \n",
       "0      None           GB    Europe     NaN  \n",
       "1  -0.74934           GB    Europe     NaN  \n",
       "2  12.16179           DE    Europe     NaN  \n",
       "3   -2.1875           GB    Europe     NaN  \n",
       "4  -1.48119           GB    Europe     NaN  "
      ]
     },
     "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>index</th>\n",
       "      <th>address</th>\n",
       "      <th>longitude</th>\n",
       "      <th>lat</th>\n",
       "      <th>locality</th>\n",
       "      <th>store_code</th>\n",
       "      <th>staff_numbers</th>\n",
       "      <th>opening_date</th>\n",
       "      <th>store_type</th>\n",
       "      <th>latitude</th>\n",
       "      <th>country_code</th>\n",
       "      <th>continent</th>\n",
       "      <th>message</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>446</th>\n",
       "      <td>446.0</td>\n",
       "      <td>Täschestraße 25\\n39039 Nördlingen, Kirchlengern</td>\n",
       "      <td>52.2</td>\n",
       "      <td>None</td>\n",
       "      <td>Kirchlengern</td>\n",
       "      <td>KI-78096E8C</td>\n",
       "      <td>61</td>\n",
       "      <td>2005-05-12</td>\n",
       "      <td>Super Store</td>\n",
       "      <td>8.63333</td>\n",
       "      <td>DE</td>\n",
       "      <td>Europe</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>447</th>\n",
       "      <td>447.0</td>\n",
       "      <td>K0ODETRLS3</td>\n",
       "      <td>K8CXLZDP07</td>\n",
       "      <td>UXMWDMX1LC</td>\n",
       "      <td>3VHFDNP8ET</td>\n",
       "      <td>9D4LK7X4LZ</td>\n",
       "      <td>D23PCWSM6S</td>\n",
       "      <td>36IIMAQD58</td>\n",
       "      <td>NN04B3F6UQ</td>\n",
       "      <td>JZP8MIJTPZ</td>\n",
       "      <td>B3EH2ZGQAV</td>\n",
       "      <td>1WZB1TE1HL</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>448</th>\n",
       "      <td>448.0</td>\n",
       "      <td>Studio 8\\nMoss mall\\nWest Linda\\nM0E 6XR, High...</td>\n",
       "      <td>51.62907</td>\n",
       "      <td>None</td>\n",
       "      <td>High Wycombe</td>\n",
       "      <td>HI-EEA7AE62</td>\n",
       "      <td>33</td>\n",
       "      <td>1998-05-14</td>\n",
       "      <td>Local</td>\n",
       "      <td>-0.74934</td>\n",
       "      <td>GB</td>\n",
       "      <td>Europe</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>449</th>\n",
       "      <td>449.0</td>\n",
       "      <td>Baumplatz 6\\n80114 Kötzting, Bretten</td>\n",
       "      <td>49.03685</td>\n",
       "      <td>None</td>\n",
       "      <td>Bretten</td>\n",
       "      <td>BR-662EC74C</td>\n",
       "      <td>35</td>\n",
       "      <td>2020-10-17</td>\n",
       "      <td>Local</td>\n",
       "      <td>8.70745</td>\n",
       "      <td>DE</td>\n",
       "      <td>Europe</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>450</th>\n",
       "      <td>450.0</td>\n",
       "      <td>Gotthilf-Rose-Straße 7/3\\n45457 Feuchtwangen, ...</td>\n",
       "      <td>50.64336</td>\n",
       "      <td>None</td>\n",
       "      <td>Bad Honnef</td>\n",
       "      <td>BA-B4AED588</td>\n",
       "      <td>36</td>\n",
       "      <td>2001-05-12</td>\n",
       "      <td>Local</td>\n",
       "      <td>7.2278</td>\n",
       "      <td>DE</td>\n",
       "      <td>eeEurope</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     index                                            address   longitude  \\\n",
       "446  446.0    Täschestraße 25\\n39039 Nördlingen, Kirchlengern        52.2   \n",
       "447  447.0                                         K0ODETRLS3  K8CXLZDP07   \n",
       "448  448.0  Studio 8\\nMoss mall\\nWest Linda\\nM0E 6XR, High...    51.62907   \n",
       "449  449.0               Baumplatz 6\\n80114 Kötzting, Bretten    49.03685   \n",
       "450  450.0  Gotthilf-Rose-Straße 7/3\\n45457 Feuchtwangen, ...    50.64336   \n",
       "\n",
       "            lat      locality   store_code staff_numbers opening_date  \\\n",
       "446        None  Kirchlengern  KI-78096E8C            61   2005-05-12   \n",
       "447  UXMWDMX1LC    3VHFDNP8ET   9D4LK7X4LZ    D23PCWSM6S   36IIMAQD58   \n",
       "448        None  High Wycombe  HI-EEA7AE62            33   1998-05-14   \n",
       "449        None       Bretten  BR-662EC74C            35   2020-10-17   \n",
       "450        None    Bad Honnef  BA-B4AED588            36   2001-05-12   \n",
       "\n",
       "      store_type    latitude country_code   continent message  \n",
       "446  Super Store     8.63333           DE      Europe     NaN  \n",
       "447   NN04B3F6UQ  JZP8MIJTPZ   B3EH2ZGQAV  1WZB1TE1HL     NaN  \n",
       "448        Local    -0.74934           GB      Europe     NaN  \n",
       "449        Local     8.70745           DE      Europe     NaN  \n",
       "450        Local      7.2278           DE    eeEurope     NaN  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.tail(5)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's drop \"lat\" and \"message\" as they appear to be rubbish"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.drop(columns=[\"lat\",\"message\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 451 entries, 0 to 450\n",
      "Data columns (total 11 columns):\n",
      " #   Column         Non-Null Count  Dtype  \n",
      "---  ------         --------------  -----  \n",
      " 0   index          444 non-null    float64\n",
      " 1   address        444 non-null    object \n",
      " 2   longitude      444 non-null    object \n",
      " 3   locality       444 non-null    object \n",
      " 4   store_code     444 non-null    object \n",
      " 5   staff_numbers  444 non-null    object \n",
      " 6   opening_date   444 non-null    object \n",
      " 7   store_type     444 non-null    object \n",
      " 8   latitude       443 non-null    object \n",
      " 9   country_code   444 non-null    object \n",
      " 10  continent      444 non-null    object \n",
      "dtypes: float64(1), object(10)\n",
      "memory usage: 38.9+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "index            float64\n",
       "address           object\n",
       "longitude         object\n",
       "locality          object\n",
       "store_code        object\n",
       "staff_numbers     object\n",
       "opening_date      object\n",
       "store_type        object\n",
       "latitude          object\n",
       "country_code      object\n",
       "continent         object\n",
       "dtype: object"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "index seems to be an integer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['index'] = pd.to_numeric(df['index'],errors=\"coerce\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As do latitude and longitude"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['longitude']= pd.to_numeric(df['longitude'],errors=\"coerce\")\n",
    "df['latitude'] = pd.to_numeric(df['latitude'],errors=\"coerce\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "lets drop the rows where the lat and long failed "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.dropna(inplace=True,axis=\"index\",how=\"any\") # drops the whole row if any columns have an nan"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We have some date columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['opening_date']=pd.to_datetime(df['opening_date'],infer_datetime_format=True, errors='coerce')"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "inspect some potentially categorical data:\n",
    "Country\n",
    "Country Code"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count        433\n",
       "unique         4\n",
       "top       Europe\n",
       "freq         376\n",
       "Name: continent, dtype: object"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"continent\"].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count     433\n",
       "unique      3\n",
       "top        GB\n",
       "freq      260\n",
       "Name: country_code, dtype: object"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"country_code\"].describe()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Not bad, though there's 4 continents and 3 codes!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"continent\"] = df[\"continent\"].astype(\"category\")\n",
    "df[\"country_code\"] = df[\"country_code\"].astype(\"category\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Store Types as categories?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count       433\n",
       "unique        4\n",
       "top       Local\n",
       "freq        251\n",
       "Name: store_type, dtype: object"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"store_type\"].describe()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Yes. What about duplicate lines?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"store_type\"] = df[\"store_type\"].astype(\"category\")\n",
    "\n",
    "df.duplicated().sum()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "None! ok. Let's just check on the critical columns only - not system generated ones"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.duplicated(subset=[\"address\", \"locality\"],keep=\"first\").sum()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Still none"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "clean up address"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"address\"] = df[\"address\"].str.replace('\\W', ' ', regex=True)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now put the code into a local database maybe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "import database_utils\n",
    "\n",
    "\n",
    "myObject = database_utils.DatabaseConnector()\n",
    "\n",
    "\n",
    "myObject.upload_to_db(df,\"dim_store_details\")"
   ]
  },
  {
   "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
}