updated_coffee_sales_clean.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# **DATA CLEANING, WRANGLING, AND MANIPULATION**\n",
    "\n",
    "**(Before Data Visualization)**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Importing Pandas and creating DataFrames\n",
    "\n",
    "First I import necessary packages and create a DataFrame for each sheet."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "pwd = os.getcwd()\n",
    "\n",
    "df1 = pd.read_excel(pwd + r'\\updated_coffee_sales_data.xlsx', sheet_name='transactions')\n",
    "df2 = pd.read_excel(pwd + r'\\updated_coffee_sales_data.xlsx', sheet_name='employees')\n",
    "df3 = pd.read_excel(pwd + r'\\updated_coffee_sales_data.xlsx', sheet_name='coffee')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Then I check to see if there are any columns with mismatched Dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 2494 entries, 0 to 2493\n",
      "Data columns (total 9 columns):\n",
      " #   Column                 Non-Null Count  Dtype         \n",
      "---  ------                 --------------  -----         \n",
      " 0   Transaction ID         2494 non-null   object        \n",
      " 1   Emp_id                 2494 non-null   int64         \n",
      " 2   Employee Name          2494 non-null   object        \n",
      " 3   Date and Time          2494 non-null   datetime64[ns]\n",
      " 4   Purchased Coffee Type  2494 non-null   object        \n",
      " 5   Purchase Quantity      2494 non-null   int64         \n",
      " 6   total_price            2494 non-null   float64       \n",
      " 7   Payment Method         2494 non-null   object        \n",
      " 8   Customer Name          2494 non-null   object        \n",
      "dtypes: datetime64[ns](1), float64(1), int64(2), object(5)\n",
      "memory usage: 175.5+ KB\n"
     ]
    }
   ],
   "source": [
    "df1.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 4 entries, 0 to 3\n",
      "Data columns (total 4 columns):\n",
      " #   Column         Non-Null Count  Dtype \n",
      "---  ------         --------------  ----- \n",
      " 0   Emp_id         4 non-null      int64 \n",
      " 1   Emp_Name       4 non-null      object\n",
      " 2   Job Title      4 non-null      object\n",
      " 3   Hourly Salary  4 non-null      int64 \n",
      "dtypes: int64(2), object(2)\n",
      "memory usage: 260.0+ bytes\n"
     ]
    }
   ],
   "source": [
    "df2.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 5 entries, 0 to 4\n",
      "Data columns (total 2 columns):\n",
      " #   Column       Non-Null Count  Dtype  \n",
      "---  ------       --------------  -----  \n",
      " 0   Coffee type  5 non-null      object \n",
      " 1   price        5 non-null      float64\n",
      "dtypes: float64(1), object(1)\n",
      "memory usage: 212.0+ bytes\n"
     ]
    }
   ],
   "source": [
    "df3.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Merging tables\n",
    "\n",
    "Everything looks fine so I can proceed.\n",
    "\n",
    "In this case, I will join all of the tables together to create one DataFrame.\n",
    "\n",
    "The first step to achieving this is to rename the columns and make the match across all DataFrames."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = df1.rename(columns=\n",
    "          {'Transaction ID' : 'trn_id', 'Emp_id' : 'emp_id',\n",
    "          'Employee Name' : 'emp_name', 'Date and Time' : 'date_time',\n",
    "          'Purchased Coffee Type' : 'coffee_type', 'Purchase Quantity' : 'purchase_qty',\n",
    "          'Payment Method' : 'pay_method', 'Customer Name' : 'customer_name'}\n",
    "     ) \n",
    "df2 = df2.rename(columns=\n",
    "          {'Emp_id' : 'emp_id', 'Emp_Name' : 'emp_name',\n",
    "          'Job Title' : 'job_title', 'Hourly Salary' : 'hr_salary'}\n",
    "     )\n",
    "df3 = df3.rename(columns=\n",
    "          {'Coffee type' : 'coffee_type'}\n",
    "     )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that everything is named properly, I can merge the three DataFrames with no problems."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "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>trn_id</th>\n",
       "      <th>emp_id</th>\n",
       "      <th>emp_name_x</th>\n",
       "      <th>date_time</th>\n",
       "      <th>coffee_type</th>\n",
       "      <th>purchase_qty</th>\n",
       "      <th>total_price</th>\n",
       "      <th>pay_method</th>\n",
       "      <th>customer_name</th>\n",
       "      <th>emp_name_y</th>\n",
       "      <th>job_title</th>\n",
       "      <th>hr_salary</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>80dbda09-0c0f-4360-87b8-82058756a097</td>\n",
       "      <td>98845</td>\n",
       "      <td>Kevin Swanson</td>\n",
       "      <td>2024-08-01 10:17:03</td>\n",
       "      <td>Latte</td>\n",
       "      <td>4</td>\n",
       "      <td>8.0</td>\n",
       "      <td>Card</td>\n",
       "      <td>Brandon Johnson</td>\n",
       "      <td>Kevin Swanson</td>\n",
       "      <td>Jr. Barista</td>\n",
       "      <td>15</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>a0ee1475-c554-45bc-862e-2bbacaf11d67</td>\n",
       "      <td>98845</td>\n",
       "      <td>Kevin Swanson</td>\n",
       "      <td>2024-08-01 09:38:21</td>\n",
       "      <td>Cappuccino</td>\n",
       "      <td>2</td>\n",
       "      <td>4.0</td>\n",
       "      <td>Cash</td>\n",
       "      <td>Julia Dennis</td>\n",
       "      <td>Kevin Swanson</td>\n",
       "      <td>Jr. Barista</td>\n",
       "      <td>15</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>e7447dc1-6289-4e54-8b27-9b295b18d67f</td>\n",
       "      <td>82456</td>\n",
       "      <td>Diana Collins</td>\n",
       "      <td>2024-08-01 05:03:38</td>\n",
       "      <td>Latte</td>\n",
       "      <td>3</td>\n",
       "      <td>6.0</td>\n",
       "      <td>Card</td>\n",
       "      <td>Nicholas Hernandez</td>\n",
       "      <td>Diana Collins</td>\n",
       "      <td>Sr. Barista</td>\n",
       "      <td>15</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>fd99c34d-9895-4209-905c-b245a9e78257</td>\n",
       "      <td>99854</td>\n",
       "      <td>Rebecca Edwards</td>\n",
       "      <td>2024-08-01 01:47:53</td>\n",
       "      <td>Cappuccino</td>\n",
       "      <td>3</td>\n",
       "      <td>6.0</td>\n",
       "      <td>Card</td>\n",
       "      <td>Nicholas Hernandez</td>\n",
       "      <td>Rebecca Edwards</td>\n",
       "      <td>Sr. Barista</td>\n",
       "      <td>12</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>427b3d1d-9fb9-48d8-862f-2405b58e1167</td>\n",
       "      <td>82456</td>\n",
       "      <td>Diana Collins</td>\n",
       "      <td>2024-08-01 01:35:16</td>\n",
       "      <td>Latte</td>\n",
       "      <td>2</td>\n",
       "      <td>4.0</td>\n",
       "      <td>Card</td>\n",
       "      <td>Shane Ibarra</td>\n",
       "      <td>Diana Collins</td>\n",
       "      <td>Sr. Barista</td>\n",
       "      <td>15</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 trn_id  emp_id       emp_name_x  \\\n",
       "0  80dbda09-0c0f-4360-87b8-82058756a097   98845    Kevin Swanson   \n",
       "1  a0ee1475-c554-45bc-862e-2bbacaf11d67   98845    Kevin Swanson   \n",
       "2  e7447dc1-6289-4e54-8b27-9b295b18d67f   82456    Diana Collins   \n",
       "3  fd99c34d-9895-4209-905c-b245a9e78257   99854  Rebecca Edwards   \n",
       "4  427b3d1d-9fb9-48d8-862f-2405b58e1167   82456    Diana Collins   \n",
       "\n",
       "            date_time coffee_type  purchase_qty  total_price pay_method  \\\n",
       "0 2024-08-01 10:17:03       Latte             4          8.0       Card   \n",
       "1 2024-08-01 09:38:21  Cappuccino             2          4.0       Cash   \n",
       "2 2024-08-01 05:03:38       Latte             3          6.0       Card   \n",
       "3 2024-08-01 01:47:53  Cappuccino             3          6.0       Card   \n",
       "4 2024-08-01 01:35:16       Latte             2          4.0       Card   \n",
       "\n",
       "        customer_name       emp_name_y    job_title  hr_salary  price  \n",
       "0     Brandon Johnson    Kevin Swanson  Jr. Barista         15    2.0  \n",
       "1        Julia Dennis    Kevin Swanson  Jr. Barista         15    2.0  \n",
       "2  Nicholas Hernandez    Diana Collins  Sr. Barista         15    2.0  \n",
       "3  Nicholas Hernandez  Rebecca Edwards  Sr. Barista         12    2.0  \n",
       "4        Shane Ibarra    Diana Collins  Sr. Barista         15    2.0  "
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_merged = df1.merge(df2, on='emp_id', how='inner').merge(df3, on='coffee_type', how='inner')\n",
    "df_merged.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data cleaning.\n",
    "\n",
    "Now it is finally time to clean the data.\n",
    "\n",
    "From what I can see, there are a few changes that I can make:\n",
    "1. Shorten the transaction IDs to the first 8 string.\n",
    "2. Drop 1 employee name column 'emp_name_y\" and renaming \"emp_name_x\".\n",
    "3. Separate the date and time then drop the column 'date_time'\n",
    "4. Rearrange the columns.\n",
    "5. Finishing touches."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_cleaning = df_merged.copy() # Always best practice to create new copies of your dataframes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "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>trn_id</th>\n",
       "      <th>emp_id</th>\n",
       "      <th>emp_name</th>\n",
       "      <th>coffee_type</th>\n",
       "      <th>purchase_qty</th>\n",
       "      <th>total_price</th>\n",
       "      <th>pay_method</th>\n",
       "      <th>customer_name</th>\n",
       "      <th>job_title</th>\n",
       "      <th>hr_salary</th>\n",
       "      <th>price</th>\n",
       "      <th>date</th>\n",
       "      <th>time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>80dbda09</td>\n",
       "      <td>98845</td>\n",
       "      <td>Kevin Swanson</td>\n",
       "      <td>Latte</td>\n",
       "      <td>4</td>\n",
       "      <td>8.0</td>\n",
       "      <td>Card</td>\n",
       "      <td>Brandon Johnson</td>\n",
       "      <td>Jr. Barista</td>\n",
       "      <td>15</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2024-08-01</td>\n",
       "      <td>10:17:03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>a0ee1475</td>\n",
       "      <td>98845</td>\n",
       "      <td>Kevin Swanson</td>\n",
       "      <td>Cappuccino</td>\n",
       "      <td>2</td>\n",
       "      <td>4.0</td>\n",
       "      <td>Cash</td>\n",
       "      <td>Julia Dennis</td>\n",
       "      <td>Jr. Barista</td>\n",
       "      <td>15</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2024-08-01</td>\n",
       "      <td>09:38:21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>e7447dc1</td>\n",
       "      <td>82456</td>\n",
       "      <td>Diana Collins</td>\n",
       "      <td>Latte</td>\n",
       "      <td>3</td>\n",
       "      <td>6.0</td>\n",
       "      <td>Card</td>\n",
       "      <td>Nicholas Hernandez</td>\n",
       "      <td>Sr. Barista</td>\n",
       "      <td>15</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2024-08-01</td>\n",
       "      <td>05:03:38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>fd99c34d</td>\n",
       "      <td>99854</td>\n",
       "      <td>Rebecca Edwards</td>\n",
       "      <td>Cappuccino</td>\n",
       "      <td>3</td>\n",
       "      <td>6.0</td>\n",
       "      <td>Card</td>\n",
       "      <td>Nicholas Hernandez</td>\n",
       "      <td>Sr. Barista</td>\n",
       "      <td>12</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2024-08-01</td>\n",
       "      <td>01:47:53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>427b3d1d</td>\n",
       "      <td>82456</td>\n",
       "      <td>Diana Collins</td>\n",
       "      <td>Latte</td>\n",
       "      <td>2</td>\n",
       "      <td>4.0</td>\n",
       "      <td>Card</td>\n",
       "      <td>Shane Ibarra</td>\n",
       "      <td>Sr. Barista</td>\n",
       "      <td>15</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2024-08-01</td>\n",
       "      <td>01:35:16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     trn_id  emp_id         emp_name coffee_type  purchase_qty  total_price  \\\n",
       "0  80dbda09   98845    Kevin Swanson       Latte             4          8.0   \n",
       "1  a0ee1475   98845    Kevin Swanson  Cappuccino             2          4.0   \n",
       "2  e7447dc1   82456    Diana Collins       Latte             3          6.0   \n",
       "3  fd99c34d   99854  Rebecca Edwards  Cappuccino             3          6.0   \n",
       "4  427b3d1d   82456    Diana Collins       Latte             2          4.0   \n",
       "\n",
       "  pay_method       customer_name    job_title  hr_salary  price        date  \\\n",
       "0       Card     Brandon Johnson  Jr. Barista         15    2.0  2024-08-01   \n",
       "1       Cash        Julia Dennis  Jr. Barista         15    2.0  2024-08-01   \n",
       "2       Card  Nicholas Hernandez  Sr. Barista         15    2.0  2024-08-01   \n",
       "3       Card  Nicholas Hernandez  Sr. Barista         12    2.0  2024-08-01   \n",
       "4       Card        Shane Ibarra  Sr. Barista         15    2.0  2024-08-01   \n",
       "\n",
       "       time  \n",
       "0  10:17:03  \n",
       "1  09:38:21  \n",
       "2  05:03:38  \n",
       "3  01:47:53  \n",
       "4  01:35:16  "
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_cleaning['trn_id'] = df_cleaning['trn_id'].str[:8] # Shorten the transaction IDs to the first 8 string.\n",
    "df_cleaning = df_cleaning.rename(columns={'emp_name_x' : 'emp_name'}) # Drop 1 employee name column 'emp_name_y\" and renaming \"emp_name_x\".\n",
    "df_cleaning = df_cleaning.drop(columns=['emp_name_y'])\n",
    "df_cleaning['date'] = df_cleaning['date_time'].dt.date # Separate the date and time then drop the column 'date_time'\n",
    "df_cleaning['time'] = df_cleaning['date_time'].dt.time\n",
    "df_cleaning = df_cleaning.drop(columns=['date_time'])\n",
    "df_cleaning.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "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>trn_id</th>\n",
       "      <th>emp_id</th>\n",
       "      <th>hr_salary</th>\n",
       "      <th>job_title</th>\n",
       "      <th>emp_name</th>\n",
       "      <th>date</th>\n",
       "      <th>time</th>\n",
       "      <th>coffee_type</th>\n",
       "      <th>purchase_qty</th>\n",
       "      <th>price</th>\n",
       "      <th>total_price</th>\n",
       "      <th>pay_method</th>\n",
       "      <th>customer_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>80dbda09</td>\n",
       "      <td>98845</td>\n",
       "      <td>15</td>\n",
       "      <td>Jr. Barista</td>\n",
       "      <td>Kevin Swanson</td>\n",
       "      <td>2024-08-01</td>\n",
       "      <td>10:17:03</td>\n",
       "      <td>Latte</td>\n",
       "      <td>4</td>\n",
       "      <td>2.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>Card</td>\n",
       "      <td>Brandon Johnson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>a0ee1475</td>\n",
       "      <td>98845</td>\n",
       "      <td>15</td>\n",
       "      <td>Jr. Barista</td>\n",
       "      <td>Kevin Swanson</td>\n",
       "      <td>2024-08-01</td>\n",
       "      <td>09:38:21</td>\n",
       "      <td>Cappuccino</td>\n",
       "      <td>2</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>Cash</td>\n",
       "      <td>Julia Dennis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>e7447dc1</td>\n",
       "      <td>82456</td>\n",
       "      <td>15</td>\n",
       "      <td>Sr. Barista</td>\n",
       "      <td>Diana Collins</td>\n",
       "      <td>2024-08-01</td>\n",
       "      <td>05:03:38</td>\n",
       "      <td>Latte</td>\n",
       "      <td>3</td>\n",
       "      <td>2.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>Card</td>\n",
       "      <td>Nicholas Hernandez</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>fd99c34d</td>\n",
       "      <td>99854</td>\n",
       "      <td>12</td>\n",
       "      <td>Sr. Barista</td>\n",
       "      <td>Rebecca Edwards</td>\n",
       "      <td>2024-08-01</td>\n",
       "      <td>01:47:53</td>\n",
       "      <td>Cappuccino</td>\n",
       "      <td>3</td>\n",
       "      <td>2.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>Card</td>\n",
       "      <td>Nicholas Hernandez</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>427b3d1d</td>\n",
       "      <td>82456</td>\n",
       "      <td>15</td>\n",
       "      <td>Sr. Barista</td>\n",
       "      <td>Diana Collins</td>\n",
       "      <td>2024-08-01</td>\n",
       "      <td>01:35:16</td>\n",
       "      <td>Latte</td>\n",
       "      <td>2</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>Card</td>\n",
       "      <td>Shane Ibarra</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     trn_id  emp_id  hr_salary    job_title         emp_name        date  \\\n",
       "0  80dbda09   98845         15  Jr. Barista    Kevin Swanson  2024-08-01   \n",
       "1  a0ee1475   98845         15  Jr. Barista    Kevin Swanson  2024-08-01   \n",
       "2  e7447dc1   82456         15  Sr. Barista    Diana Collins  2024-08-01   \n",
       "3  fd99c34d   99854         12  Sr. Barista  Rebecca Edwards  2024-08-01   \n",
       "4  427b3d1d   82456         15  Sr. Barista    Diana Collins  2024-08-01   \n",
       "\n",
       "       time coffee_type  purchase_qty  price  total_price pay_method  \\\n",
       "0  10:17:03       Latte             4    2.0          8.0       Card   \n",
       "1  09:38:21  Cappuccino             2    2.0          4.0       Cash   \n",
       "2  05:03:38       Latte             3    2.0          6.0       Card   \n",
       "3  01:47:53  Cappuccino             3    2.0          6.0       Card   \n",
       "4  01:35:16       Latte             2    2.0          4.0       Card   \n",
       "\n",
       "        customer_name  \n",
       "0     Brandon Johnson  \n",
       "1        Julia Dennis  \n",
       "2  Nicholas Hernandez  \n",
       "3  Nicholas Hernandez  \n",
       "4        Shane Ibarra  "
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Rearrange the columns.\n",
    "reorder = [\n",
    "     'trn_id', 'emp_id', 'hr_salary', 'job_title', 'emp_name',\n",
    "     'date', 'time', 'coffee_type', 'purchase_qty', 'price',\n",
    "     'total_price', 'pay_method', 'customer_name'\n",
    "]\n",
    "\n",
    "df_reorder = df_cleaning[reorder]\n",
    "df_reorder.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 2494 entries, 0 to 2493\n",
      "Data columns (total 13 columns):\n",
      " #   Column         Non-Null Count  Dtype  \n",
      "---  ------         --------------  -----  \n",
      " 0   trn_id         2494 non-null   object \n",
      " 1   emp_id         2494 non-null   int64  \n",
      " 2   hr_salary      2494 non-null   int64  \n",
      " 3   job_title      2494 non-null   object \n",
      " 4   emp_name       2494 non-null   object \n",
      " 5   date           2494 non-null   object \n",
      " 6   time           2494 non-null   object \n",
      " 7   coffee_type    2494 non-null   object \n",
      " 8   purchase_qty   2494 non-null   int64  \n",
      " 9   price          2494 non-null   float64\n",
      " 10  total_price    2494 non-null   float64\n",
      " 11  pay_method     2494 non-null   object \n",
      " 12  customer_name  2494 non-null   object \n",
      "dtypes: float64(2), int64(3), object(8)\n",
      "memory usage: 253.4+ KB\n"
     ]
    }
   ],
   "source": [
    "df_reorder.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Upon inspection, I noticed that date is Dtype Objects which will be a problem if not fixed.\n",
    "\n",
    "Let me fix that."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_dt = df_reorder.copy() # Always best practice to create new copies of your dataframes.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 2494 entries, 0 to 2493\n",
      "Data columns (total 13 columns):\n",
      " #   Column         Non-Null Count  Dtype         \n",
      "---  ------         --------------  -----         \n",
      " 0   trn_id         2494 non-null   object        \n",
      " 1   emp_id         2494 non-null   int64         \n",
      " 2   hr_salary      2494 non-null   int64         \n",
      " 3   job_title      2494 non-null   object        \n",
      " 4   emp_name       2494 non-null   object        \n",
      " 5   date           2494 non-null   datetime64[ns]\n",
      " 6   time           2494 non-null   object        \n",
      " 7   coffee_type    2494 non-null   object        \n",
      " 8   purchase_qty   2494 non-null   int64         \n",
      " 9   price          2494 non-null   float64       \n",
      " 10  total_price    2494 non-null   float64       \n",
      " 11  pay_method     2494 non-null   object        \n",
      " 12  customer_name  2494 non-null   object        \n",
      "dtypes: datetime64[ns](1), float64(2), int64(3), object(7)\n",
      "memory usage: 253.4+ KB\n"
     ]
    }
   ],
   "source": [
    "df_dt['date'] = pd.to_datetime(df_dt['date'], format='%Y-%m-%d')\n",
    "df_dt.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "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>trn_id</th>\n",
       "      <th>emp_id</th>\n",
       "      <th>hr_salary</th>\n",
       "      <th>job_title</th>\n",
       "      <th>emp_name</th>\n",
       "      <th>date</th>\n",
       "      <th>time</th>\n",
       "      <th>coffee_type</th>\n",
       "      <th>purchase_qty</th>\n",
       "      <th>price</th>\n",
       "      <th>total_price</th>\n",
       "      <th>pay_method</th>\n",
       "      <th>customer_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>80dbda09</td>\n",
       "      <td>98845</td>\n",
       "      <td>15</td>\n",
       "      <td>Jr. Barista</td>\n",
       "      <td>Kevin Swanson</td>\n",
       "      <td>2024-08-01</td>\n",
       "      <td>10:17:03</td>\n",
       "      <td>Latte</td>\n",
       "      <td>4</td>\n",
       "      <td>2.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>Card</td>\n",
       "      <td>Brandon Johnson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>a0ee1475</td>\n",
       "      <td>98845</td>\n",
       "      <td>15</td>\n",
       "      <td>Jr. Barista</td>\n",
       "      <td>Kevin Swanson</td>\n",
       "      <td>2024-08-01</td>\n",
       "      <td>09:38:21</td>\n",
       "      <td>Cappuccino</td>\n",
       "      <td>2</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>Cash</td>\n",
       "      <td>Julia Dennis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>e7447dc1</td>\n",
       "      <td>82456</td>\n",
       "      <td>15</td>\n",
       "      <td>Sr. Barista</td>\n",
       "      <td>Diana Collins</td>\n",
       "      <td>2024-08-01</td>\n",
       "      <td>05:03:38</td>\n",
       "      <td>Latte</td>\n",
       "      <td>3</td>\n",
       "      <td>2.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>Card</td>\n",
       "      <td>Nicholas Hernandez</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>fd99c34d</td>\n",
       "      <td>99854</td>\n",
       "      <td>12</td>\n",
       "      <td>Sr. Barista</td>\n",
       "      <td>Rebecca Edwards</td>\n",
       "      <td>2024-08-01</td>\n",
       "      <td>01:47:53</td>\n",
       "      <td>Cappuccino</td>\n",
       "      <td>3</td>\n",
       "      <td>2.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>Card</td>\n",
       "      <td>Nicholas Hernandez</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>427b3d1d</td>\n",
       "      <td>82456</td>\n",
       "      <td>15</td>\n",
       "      <td>Sr. Barista</td>\n",
       "      <td>Diana Collins</td>\n",
       "      <td>2024-08-01</td>\n",
       "      <td>01:35:16</td>\n",
       "      <td>Latte</td>\n",
       "      <td>2</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>Card</td>\n",
       "      <td>Shane Ibarra</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     trn_id  emp_id  hr_salary    job_title         emp_name       date  \\\n",
       "0  80dbda09   98845         15  Jr. Barista    Kevin Swanson 2024-08-01   \n",
       "1  a0ee1475   98845         15  Jr. Barista    Kevin Swanson 2024-08-01   \n",
       "2  e7447dc1   82456         15  Sr. Barista    Diana Collins 2024-08-01   \n",
       "3  fd99c34d   99854         12  Sr. Barista  Rebecca Edwards 2024-08-01   \n",
       "4  427b3d1d   82456         15  Sr. Barista    Diana Collins 2024-08-01   \n",
       "\n",
       "       time coffee_type  purchase_qty  price  total_price pay_method  \\\n",
       "0  10:17:03       Latte             4    2.0          8.0       Card   \n",
       "1  09:38:21  Cappuccino             2    2.0          4.0       Cash   \n",
       "2  05:03:38       Latte             3    2.0          6.0       Card   \n",
       "3  01:47:53  Cappuccino             3    2.0          6.0       Card   \n",
       "4  01:35:16       Latte             2    2.0          4.0       Card   \n",
       "\n",
       "        customer_name  \n",
       "0     Brandon Johnson  \n",
       "1        Julia Dennis  \n",
       "2  Nicholas Hernandez  \n",
       "3  Nicholas Hernandez  \n",
       "4        Shane Ibarra  "
      ]
     },
     "execution_count": 113,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_dt.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exporting to CSV\n",
    "\n",
    "Now that the data is clean and ready for visualization.\n",
    "I am going to export it into a csv file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_final = df_dt.copy()\n",
    "df_final.to_csv('updated_coffee_sales_cleaned.csv', index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "DSmain",
   "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.12.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}