dtimes_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\",'date_details.json','date_details.json')\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_json(\"date_details.json\")"
   ]
  },
  {
   "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>timestamp</th>\n",
       "      <th>month</th>\n",
       "      <th>year</th>\n",
       "      <th>day</th>\n",
       "      <th>time_period</th>\n",
       "      <th>date_uuid</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>22:00:06</td>\n",
       "      <td>9</td>\n",
       "      <td>2012</td>\n",
       "      <td>19</td>\n",
       "      <td>Evening</td>\n",
       "      <td>3b7ca996-37f9-433f-b6d0-ce8391b615ad</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>22:44:06</td>\n",
       "      <td>2</td>\n",
       "      <td>1997</td>\n",
       "      <td>10</td>\n",
       "      <td>Evening</td>\n",
       "      <td>adc86836-6c35-49ca-bb0d-65b6507a00fa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10:05:37</td>\n",
       "      <td>4</td>\n",
       "      <td>1994</td>\n",
       "      <td>15</td>\n",
       "      <td>Morning</td>\n",
       "      <td>5ff791bf-d8e0-4f86-8ceb-c7b60bef9b31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>17:29:27</td>\n",
       "      <td>11</td>\n",
       "      <td>2001</td>\n",
       "      <td>6</td>\n",
       "      <td>Midday</td>\n",
       "      <td>1b01fcef-5ab9-404c-b0d4-1e75a0bd19d8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>22:40:33</td>\n",
       "      <td>12</td>\n",
       "      <td>2015</td>\n",
       "      <td>31</td>\n",
       "      <td>Evening</td>\n",
       "      <td>dfa907c1-f6c5-40f0-aa0d-40ed77ac5a44</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  timestamp month  year day time_period                             date_uuid\n",
       "0  22:00:06     9  2012  19     Evening  3b7ca996-37f9-433f-b6d0-ce8391b615ad\n",
       "1  22:44:06     2  1997  10     Evening  adc86836-6c35-49ca-bb0d-65b6507a00fa\n",
       "2  10:05:37     4  1994  15     Morning  5ff791bf-d8e0-4f86-8ceb-c7b60bef9b31\n",
       "3  17:29:27    11  2001   6      Midday  1b01fcef-5ab9-404c-b0d4-1e75a0bd19d8\n",
       "4  22:40:33    12  2015  31     Evening  dfa907c1-f6c5-40f0-aa0d-40ed77ac5a44"
      ]
     },
     "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>timestamp</th>\n",
       "      <th>month</th>\n",
       "      <th>year</th>\n",
       "      <th>day</th>\n",
       "      <th>time_period</th>\n",
       "      <th>date_uuid</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>120156</th>\n",
       "      <td>22:56:56</td>\n",
       "      <td>11</td>\n",
       "      <td>2022</td>\n",
       "      <td>12</td>\n",
       "      <td>Evening</td>\n",
       "      <td>d6c4fb31-720d-4e94-aa6b-dcbcb85f2bb7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120157</th>\n",
       "      <td>18:25:20</td>\n",
       "      <td>5</td>\n",
       "      <td>1997</td>\n",
       "      <td>31</td>\n",
       "      <td>Evening</td>\n",
       "      <td>f7722027-1aae-49c3-8f8d-853e93f9f3e6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120158</th>\n",
       "      <td>18:21:40</td>\n",
       "      <td>9</td>\n",
       "      <td>2011</td>\n",
       "      <td>13</td>\n",
       "      <td>Evening</td>\n",
       "      <td>4a3b9851-52e1-463c-ac81-1960f141444e</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120159</th>\n",
       "      <td>19:10:53</td>\n",
       "      <td>7</td>\n",
       "      <td>2013</td>\n",
       "      <td>12</td>\n",
       "      <td>Evening</td>\n",
       "      <td>64974909-0d4b-42a2-822a-73b5695e8bfb</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120160</th>\n",
       "      <td>21:17:12</td>\n",
       "      <td>3</td>\n",
       "      <td>2008</td>\n",
       "      <td>18</td>\n",
       "      <td>Evening</td>\n",
       "      <td>55c228c7-14ee-4d46-99a9-01dfc57d1adf</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       timestamp month  year day time_period  \\\n",
       "120156  22:56:56    11  2022  12     Evening   \n",
       "120157  18:25:20     5  1997  31     Evening   \n",
       "120158  18:21:40     9  2011  13     Evening   \n",
       "120159  19:10:53     7  2013  12     Evening   \n",
       "120160  21:17:12     3  2008  18     Evening   \n",
       "\n",
       "                                   date_uuid  \n",
       "120156  d6c4fb31-720d-4e94-aa6b-dcbcb85f2bb7  \n",
       "120157  f7722027-1aae-49c3-8f8d-853e93f9f3e6  \n",
       "120158  4a3b9851-52e1-463c-ac81-1960f141444e  \n",
       "120159  64974909-0d4b-42a2-822a-73b5695e8bfb  \n",
       "120160  55c228c7-14ee-4d46-99a9-01dfc57d1adf  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.tail(5)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Make a string and put it in a new column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['dateTime'] = df[\"year\"] + \"-\" + df[\"month\"]  + \"-\" +  df[\"day\"] + \" \" + df[\"timestamp\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>timestamp</th>\n",
       "      <th>month</th>\n",
       "      <th>year</th>\n",
       "      <th>day</th>\n",
       "      <th>time_period</th>\n",
       "      <th>date_uuid</th>\n",
       "      <th>dateTime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>22:00:06</td>\n",
       "      <td>9</td>\n",
       "      <td>2012</td>\n",
       "      <td>19</td>\n",
       "      <td>Evening</td>\n",
       "      <td>3b7ca996-37f9-433f-b6d0-ce8391b615ad</td>\n",
       "      <td>2012-9-19 22:00:06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>22:44:06</td>\n",
       "      <td>2</td>\n",
       "      <td>1997</td>\n",
       "      <td>10</td>\n",
       "      <td>Evening</td>\n",
       "      <td>adc86836-6c35-49ca-bb0d-65b6507a00fa</td>\n",
       "      <td>1997-2-10 22:44:06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10:05:37</td>\n",
       "      <td>4</td>\n",
       "      <td>1994</td>\n",
       "      <td>15</td>\n",
       "      <td>Morning</td>\n",
       "      <td>5ff791bf-d8e0-4f86-8ceb-c7b60bef9b31</td>\n",
       "      <td>1994-4-15 10:05:37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>17:29:27</td>\n",
       "      <td>11</td>\n",
       "      <td>2001</td>\n",
       "      <td>6</td>\n",
       "      <td>Midday</td>\n",
       "      <td>1b01fcef-5ab9-404c-b0d4-1e75a0bd19d8</td>\n",
       "      <td>2001-11-6 17:29:27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>22:40:33</td>\n",
       "      <td>12</td>\n",
       "      <td>2015</td>\n",
       "      <td>31</td>\n",
       "      <td>Evening</td>\n",
       "      <td>dfa907c1-f6c5-40f0-aa0d-40ed77ac5a44</td>\n",
       "      <td>2015-12-31 22:40:33</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  timestamp month  year day time_period                             date_uuid  \\\n",
       "0  22:00:06     9  2012  19     Evening  3b7ca996-37f9-433f-b6d0-ce8391b615ad   \n",
       "1  22:44:06     2  1997  10     Evening  adc86836-6c35-49ca-bb0d-65b6507a00fa   \n",
       "2  10:05:37     4  1994  15     Morning  5ff791bf-d8e0-4f86-8ceb-c7b60bef9b31   \n",
       "3  17:29:27    11  2001   6      Midday  1b01fcef-5ab9-404c-b0d4-1e75a0bd19d8   \n",
       "4  22:40:33    12  2015  31     Evening  dfa907c1-f6c5-40f0-aa0d-40ed77ac5a44   \n",
       "\n",
       "              dateTime  \n",
       "0   2012-9-19 22:00:06  \n",
       "1   1997-2-10 22:44:06  \n",
       "2   1994-4-15 10:05:37  \n",
       "3   2001-11-6 17:29:27  \n",
       "4  2015-12-31 22:40:33  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Lets drop the old time columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.drop(columns=[\"timestamp\",\"month\",\"year\",\"day\",\"time_period\"])"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Convert datetime to datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['dateTime']=pd.to_datetime(df['dateTime'],infer_datetime_format=True, errors='coerce')"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Duplicated lines?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "14"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.duplicated().sum()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "14! ok. Let's just check on the critical columns only - not system generated ones"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "14"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.duplicated(subset=[\"date_uuid\"],keep=\"first\").sum()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Still 14\n",
    "lets drop nas too"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop_duplicates(subset=[\"date_uuid\"],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": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 120123 entries, 0 to 120160\n",
      "Data columns (total 2 columns):\n",
      " #   Column     Non-Null Count   Dtype         \n",
      "---  ------     --------------   -----         \n",
      " 0   date_uuid  120123 non-null  object        \n",
      " 1   dateTime   120123 non-null  datetime64[ns]\n",
      "dtypes: datetime64[ns](1), object(1)\n",
      "memory usage: 2.7+ MB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now put the code into a local database maybe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "import database_utils\n",
    "\n",
    "\n",
    "myObject = database_utils.DatabaseConnector()\n",
    "\n",
    "\n",
    "myObject.upload_to_db(df,\"dim_date_times\")"
   ]
  },
  {
   "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
}