3. Transactions Data Cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 123,
   "id": "f5ca55c0",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import scipy.stats as stats\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "id": "6fc224cf",
   "metadata": {},
   "outputs": [],
   "source": [
    "transactions = pd.read_excel('Client Data.xlsx', sheet_name=3, header=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "id": "148a151b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 20000 entries, 0 to 19999\n",
      "Data columns (total 13 columns):\n",
      " #   Column                   Non-Null Count  Dtype         \n",
      "---  ------                   --------------  -----         \n",
      " 0   transaction_id           20000 non-null  int64         \n",
      " 1   product_id               20000 non-null  int64         \n",
      " 2   customer_id              20000 non-null  int64         \n",
      " 3   transaction_date         20000 non-null  datetime64[ns]\n",
      " 4   online_order             19640 non-null  float64       \n",
      " 5   order_status             20000 non-null  object        \n",
      " 6   brand                    19803 non-null  object        \n",
      " 7   product_line             19803 non-null  object        \n",
      " 8   product_class            19803 non-null  object        \n",
      " 9   product_size             19803 non-null  object        \n",
      " 10  list_price               20000 non-null  float64       \n",
      " 11  standard_cost            19803 non-null  float64       \n",
      " 12  product_first_sold_date  19803 non-null  float64       \n",
      "dtypes: datetime64[ns](1), float64(4), int64(3), object(5)\n",
      "memory usage: 2.0+ MB\n"
     ]
    }
   ],
   "source": [
    "transactions.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "id": "b88e532f",
   "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>transaction_id</th>\n",
       "      <th>product_id</th>\n",
       "      <th>customer_id</th>\n",
       "      <th>transaction_date</th>\n",
       "      <th>online_order</th>\n",
       "      <th>order_status</th>\n",
       "      <th>brand</th>\n",
       "      <th>product_line</th>\n",
       "      <th>product_class</th>\n",
       "      <th>product_size</th>\n",
       "      <th>list_price</th>\n",
       "      <th>standard_cost</th>\n",
       "      <th>product_first_sold_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2950</td>\n",
       "      <td>2017-02-25</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Approved</td>\n",
       "      <td>Solex</td>\n",
       "      <td>Standard</td>\n",
       "      <td>medium</td>\n",
       "      <td>medium</td>\n",
       "      <td>71.49</td>\n",
       "      <td>53.62</td>\n",
       "      <td>41245.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>3120</td>\n",
       "      <td>2017-05-21</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Approved</td>\n",
       "      <td>Trek Bicycles</td>\n",
       "      <td>Standard</td>\n",
       "      <td>medium</td>\n",
       "      <td>large</td>\n",
       "      <td>2091.47</td>\n",
       "      <td>388.92</td>\n",
       "      <td>41701.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>37</td>\n",
       "      <td>402</td>\n",
       "      <td>2017-10-16</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Approved</td>\n",
       "      <td>OHM Cycles</td>\n",
       "      <td>Standard</td>\n",
       "      <td>low</td>\n",
       "      <td>medium</td>\n",
       "      <td>1793.43</td>\n",
       "      <td>248.82</td>\n",
       "      <td>36361.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>88</td>\n",
       "      <td>3135</td>\n",
       "      <td>2017-08-31</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Approved</td>\n",
       "      <td>Norco Bicycles</td>\n",
       "      <td>Standard</td>\n",
       "      <td>medium</td>\n",
       "      <td>medium</td>\n",
       "      <td>1198.46</td>\n",
       "      <td>381.10</td>\n",
       "      <td>36145.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>78</td>\n",
       "      <td>787</td>\n",
       "      <td>2017-10-01</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Approved</td>\n",
       "      <td>Giant Bicycles</td>\n",
       "      <td>Standard</td>\n",
       "      <td>medium</td>\n",
       "      <td>large</td>\n",
       "      <td>1765.30</td>\n",
       "      <td>709.48</td>\n",
       "      <td>42226.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   transaction_id  product_id  customer_id transaction_date  online_order  \\\n",
       "0               1           2         2950       2017-02-25           0.0   \n",
       "1               2           3         3120       2017-05-21           1.0   \n",
       "2               3          37          402       2017-10-16           0.0   \n",
       "3               4          88         3135       2017-08-31           0.0   \n",
       "4               5          78          787       2017-10-01           1.0   \n",
       "\n",
       "  order_status           brand product_line product_class product_size  \\\n",
       "0     Approved           Solex     Standard        medium       medium   \n",
       "1     Approved   Trek Bicycles     Standard        medium        large   \n",
       "2     Approved      OHM Cycles     Standard           low       medium   \n",
       "3     Approved  Norco Bicycles     Standard        medium       medium   \n",
       "4     Approved  Giant Bicycles     Standard        medium        large   \n",
       "\n",
       "   list_price  standard_cost  product_first_sold_date  \n",
       "0       71.49          53.62                  41245.0  \n",
       "1     2091.47         388.92                  41701.0  \n",
       "2     1793.43         248.82                  36361.0  \n",
       "3     1198.46         381.10                  36145.0  \n",
       "4     1765.30         709.48                  42226.0  "
      ]
     },
     "execution_count": 126,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transactions.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "id": "adf3e643",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "transaction_id               0\n",
       "product_id                   0\n",
       "customer_id                  0\n",
       "transaction_date             0\n",
       "online_order               360\n",
       "order_status                 0\n",
       "brand                      197\n",
       "product_line               197\n",
       "product_class              197\n",
       "product_size               197\n",
       "list_price                   0\n",
       "standard_cost              197\n",
       "product_first_sold_date    197\n",
       "dtype: int64"
      ]
     },
     "execution_count": 127,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transactions.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "id": "7c69f85c",
   "metadata": {},
   "outputs": [],
   "source": [
    "#sort the columns by customer id and transaction date\n",
    "transactions = transactions.sort_values(['customer_id', 'transaction_date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "id": "305eee89",
   "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>transaction_id</th>\n",
       "      <th>product_id</th>\n",
       "      <th>customer_id</th>\n",
       "      <th>transaction_date</th>\n",
       "      <th>online_order</th>\n",
       "      <th>order_status</th>\n",
       "      <th>brand</th>\n",
       "      <th>product_line</th>\n",
       "      <th>product_class</th>\n",
       "      <th>product_size</th>\n",
       "      <th>list_price</th>\n",
       "      <th>standard_cost</th>\n",
       "      <th>product_first_sold_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9784</th>\n",
       "      <td>9785</td>\n",
       "      <td>72</td>\n",
       "      <td>1</td>\n",
       "      <td>2017-01-05</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Approved</td>\n",
       "      <td>Norco Bicycles</td>\n",
       "      <td>Standard</td>\n",
       "      <td>medium</td>\n",
       "      <td>medium</td>\n",
       "      <td>360.40</td>\n",
       "      <td>270.300000</td>\n",
       "      <td>37873.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13423</th>\n",
       "      <td>13424</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2017-02-21</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Approved</td>\n",
       "      <td>Solex</td>\n",
       "      <td>Standard</td>\n",
       "      <td>medium</td>\n",
       "      <td>medium</td>\n",
       "      <td>71.49</td>\n",
       "      <td>53.620000</td>\n",
       "      <td>38573.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14485</th>\n",
       "      <td>14486</td>\n",
       "      <td>23</td>\n",
       "      <td>1</td>\n",
       "      <td>2017-03-27</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Approved</td>\n",
       "      <td>Norco Bicycles</td>\n",
       "      <td>Mountain</td>\n",
       "      <td>low</td>\n",
       "      <td>small</td>\n",
       "      <td>688.63</td>\n",
       "      <td>612.880000</td>\n",
       "      <td>33455.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18969</th>\n",
       "      <td>18970</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>2017-03-29</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Approved</td>\n",
       "      <td>Giant Bicycles</td>\n",
       "      <td>Standard</td>\n",
       "      <td>high</td>\n",
       "      <td>medium</td>\n",
       "      <td>1274.93</td>\n",
       "      <td>764.960000</td>\n",
       "      <td>35378.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3764</th>\n",
       "      <td>3765</td>\n",
       "      <td>38</td>\n",
       "      <td>1</td>\n",
       "      <td>2017-04-06</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Approved</td>\n",
       "      <td>Solex</td>\n",
       "      <td>Standard</td>\n",
       "      <td>medium</td>\n",
       "      <td>medium</td>\n",
       "      <td>1577.53</td>\n",
       "      <td>826.510000</td>\n",
       "      <td>39526.0</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2462</th>\n",
       "      <td>2463</td>\n",
       "      <td>2</td>\n",
       "      <td>3500</td>\n",
       "      <td>2017-04-25</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Approved</td>\n",
       "      <td>Solex</td>\n",
       "      <td>Standard</td>\n",
       "      <td>medium</td>\n",
       "      <td>medium</td>\n",
       "      <td>71.49</td>\n",
       "      <td>53.620000</td>\n",
       "      <td>41245.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8561</th>\n",
       "      <td>8562</td>\n",
       "      <td>33</td>\n",
       "      <td>3500</td>\n",
       "      <td>2017-08-08</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Approved</td>\n",
       "      <td>Giant Bicycles</td>\n",
       "      <td>Standard</td>\n",
       "      <td>medium</td>\n",
       "      <td>small</td>\n",
       "      <td>1311.44</td>\n",
       "      <td>1167.180000</td>\n",
       "      <td>33888.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17468</th>\n",
       "      <td>17469</td>\n",
       "      <td>0</td>\n",
       "      <td>5034</td>\n",
       "      <td>2017-01-03</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Approved</td>\n",
       "      <td>OHM Cycles</td>\n",
       "      <td>Road</td>\n",
       "      <td>medium</td>\n",
       "      <td>medium</td>\n",
       "      <td>742.54</td>\n",
       "      <td>667.400024</td>\n",
       "      <td>33879.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16700</th>\n",
       "      <td>16701</td>\n",
       "      <td>0</td>\n",
       "      <td>5034</td>\n",
       "      <td>2017-01-27</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Approved</td>\n",
       "      <td>Norco Bicycles</td>\n",
       "      <td>Standard</td>\n",
       "      <td>medium</td>\n",
       "      <td>medium</td>\n",
       "      <td>360.40</td>\n",
       "      <td>270.299988</td>\n",
       "      <td>35667.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8707</th>\n",
       "      <td>8708</td>\n",
       "      <td>0</td>\n",
       "      <td>5034</td>\n",
       "      <td>2017-10-07</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Approved</td>\n",
       "      <td>Solex</td>\n",
       "      <td>Road</td>\n",
       "      <td>medium</td>\n",
       "      <td>medium</td>\n",
       "      <td>416.98</td>\n",
       "      <td>312.735016</td>\n",
       "      <td>42218.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>20000 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       transaction_id  product_id  customer_id transaction_date  online_order  \\\n",
       "9784             9785          72            1       2017-01-05           0.0   \n",
       "13423           13424           2            1       2017-02-21           0.0   \n",
       "14485           14486          23            1       2017-03-27           0.0   \n",
       "18969           18970          11            1       2017-03-29           1.0   \n",
       "3764             3765          38            1       2017-04-06           1.0   \n",
       "...               ...         ...          ...              ...           ...   \n",
       "2462             2463           2         3500       2017-04-25           0.0   \n",
       "8561             8562          33         3500       2017-08-08           0.0   \n",
       "17468           17469           0         5034       2017-01-03           0.0   \n",
       "16700           16701           0         5034       2017-01-27           0.0   \n",
       "8707             8708           0         5034       2017-10-07           0.0   \n",
       "\n",
       "      order_status           brand product_line product_class product_size  \\\n",
       "9784      Approved  Norco Bicycles     Standard        medium       medium   \n",
       "13423     Approved           Solex     Standard        medium       medium   \n",
       "14485     Approved  Norco Bicycles     Mountain           low        small   \n",
       "18969     Approved  Giant Bicycles     Standard          high       medium   \n",
       "3764      Approved           Solex     Standard        medium       medium   \n",
       "...            ...             ...          ...           ...          ...   \n",
       "2462      Approved           Solex     Standard        medium       medium   \n",
       "8561      Approved  Giant Bicycles     Standard        medium        small   \n",
       "17468     Approved      OHM Cycles         Road        medium       medium   \n",
       "16700     Approved  Norco Bicycles     Standard        medium       medium   \n",
       "8707      Approved           Solex         Road        medium       medium   \n",
       "\n",
       "       list_price  standard_cost  product_first_sold_date  \n",
       "9784       360.40     270.300000                  37873.0  \n",
       "13423       71.49      53.620000                  38573.0  \n",
       "14485      688.63     612.880000                  33455.0  \n",
       "18969     1274.93     764.960000                  35378.0  \n",
       "3764      1577.53     826.510000                  39526.0  \n",
       "...           ...            ...                      ...  \n",
       "2462        71.49      53.620000                  41245.0  \n",
       "8561      1311.44    1167.180000                  33888.0  \n",
       "17468      742.54     667.400024                  33879.0  \n",
       "16700      360.40     270.299988                  35667.0  \n",
       "8707       416.98     312.735016                  42218.0  \n",
       "\n",
       "[20000 rows x 13 columns]"
      ]
     },
     "execution_count": 129,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transactions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "id": "b607c84f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1.0    9829\n",
       "0.0    9811\n",
       "Name: online_order, dtype: int64"
      ]
     },
     "execution_count": 130,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# count the number of online order or otherwise\n",
    "transactions['online_order'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "id": "2729b146",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1.0"
      ]
     },
     "execution_count": 131,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transactions['online_order'].mode()[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "id": "3e32a975",
   "metadata": {},
   "outputs": [],
   "source": [
    "#transactions['online_order'].fillna(transactions['online_order'].mode()[0], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "id": "04f46a5a",
   "metadata": {},
   "outputs": [],
   "source": [
    "transactions['online_order'] = transactions['online_order'].fillna(method='bfill')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "id": "22791b0c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Solex             4253\n",
       "Giant Bicycles    3312\n",
       "WeareA2B          3295\n",
       "OHM Cycles        3043\n",
       "Trek Bicycles     2990\n",
       "Norco Bicycles    2910\n",
       "Name: brand, dtype: int64"
      ]
     },
     "execution_count": 135,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# count the number of different brands\n",
    "transactions['brand'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "id": "6cecf0a9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Solex'"
      ]
     },
     "execution_count": 136,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transactions['brand'].mode()[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "id": "aac6efa9",
   "metadata": {},
   "outputs": [],
   "source": [
    "transactions['brand'].fillna(transactions['brand'].mode()[0], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "id": "e0e24363",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Standard    14176\n",
       "Road         3970\n",
       "Touring      1234\n",
       "Mountain      423\n",
       "Name: product_line, dtype: int64"
      ]
     },
     "execution_count": 139,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# count the number of different product lines\n",
    "transactions['product_line'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "id": "2f1ae136",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Standard'"
      ]
     },
     "execution_count": 140,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transactions['product_line'].mode()[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "id": "a21a2fcc",
   "metadata": {},
   "outputs": [],
   "source": [
    "transactions['product_line'].fillna(transactions['product_line'].mode()[0], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "id": "09392a67",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "medium    13826\n",
       "high       3013\n",
       "low        2964\n",
       "Name: product_class, dtype: int64"
      ]
     },
     "execution_count": 143,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# count the number of dfferent product classes\n",
    "transactions['product_class'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "id": "3b271030",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'medium'"
      ]
     },
     "execution_count": 144,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transactions['product_class'].mode()[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "id": "f898e614",
   "metadata": {},
   "outputs": [],
   "source": [
    "transactions['product_class'].fillna(transactions['product_class'].mode()[0], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 147,
   "id": "da8d1a2a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "medium    12990\n",
       "large      3976\n",
       "small      2837\n",
       "Name: product_size, dtype: int64"
      ]
     },
     "execution_count": 147,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# count sizes of which the products belong to\n",
    "transactions['product_size'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "id": "6cac7676",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'medium'"
      ]
     },
     "execution_count": 148,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transactions['product_size'].mode()[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "id": "acfd5184",
   "metadata": {},
   "outputs": [],
   "source": [
    "transactions['product_size'].fillna(transactions['product_size'].mode()[0], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "id": "1e0f8f01",
   "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>transaction_id</th>\n",
       "      <th>product_id</th>\n",
       "      <th>online_order</th>\n",
       "      <th>list_price</th>\n",
       "      <th>standard_cost</th>\n",
       "      <th>product_first_sold_date</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>customer_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11485.64</td>\n",
       "      <td>34.18</td>\n",
       "      <td>0.55</td>\n",
       "      <td>825.86</td>\n",
       "      <td>551.49</td>\n",
       "      <td>37314.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8471.67</td>\n",
       "      <td>37.33</td>\n",
       "      <td>0.33</td>\n",
       "      <td>1383.02</td>\n",
       "      <td>640.94</td>\n",
       "      <td>38775.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>13842.62</td>\n",
       "      <td>60.38</td>\n",
       "      <td>0.12</td>\n",
       "      <td>1236.03</td>\n",
       "      <td>815.68</td>\n",
       "      <td>39350.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>13544.50</td>\n",
       "      <td>78.50</td>\n",
       "      <td>0.50</td>\n",
       "      <td>523.86</td>\n",
       "      <td>413.58</td>\n",
       "      <td>36008.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>7969.67</td>\n",
       "      <td>48.17</td>\n",
       "      <td>0.33</td>\n",
       "      <td>983.87</td>\n",
       "      <td>584.71</td>\n",
       "      <td>37361.00</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>3497</th>\n",
       "      <td>8565.00</td>\n",
       "      <td>32.33</td>\n",
       "      <td>0.67</td>\n",
       "      <td>1248.02</td>\n",
       "      <td>698.58</td>\n",
       "      <td>38680.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3498</th>\n",
       "      <td>9327.50</td>\n",
       "      <td>72.17</td>\n",
       "      <td>0.83</td>\n",
       "      <td>862.84</td>\n",
       "      <td>338.29</td>\n",
       "      <td>37723.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3499</th>\n",
       "      <td>6871.29</td>\n",
       "      <td>42.71</td>\n",
       "      <td>0.57</td>\n",
       "      <td>1096.21</td>\n",
       "      <td>388.32</td>\n",
       "      <td>38163.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3500</th>\n",
       "      <td>10076.00</td>\n",
       "      <td>40.00</td>\n",
       "      <td>0.33</td>\n",
       "      <td>820.40</td>\n",
       "      <td>522.76</td>\n",
       "      <td>36432.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5034</th>\n",
       "      <td>14292.67</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>506.64</td>\n",
       "      <td>416.81</td>\n",
       "      <td>37254.67</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3494 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             transaction_id  product_id  online_order  list_price  \\\n",
       "customer_id                                                         \n",
       "1                  11485.64       34.18          0.55      825.86   \n",
       "2                   8471.67       37.33          0.33     1383.02   \n",
       "3                  13842.62       60.38          0.12     1236.03   \n",
       "4                  13544.50       78.50          0.50      523.86   \n",
       "5                   7969.67       48.17          0.33      983.87   \n",
       "...                     ...         ...           ...         ...   \n",
       "3497                8565.00       32.33          0.67     1248.02   \n",
       "3498                9327.50       72.17          0.83      862.84   \n",
       "3499                6871.29       42.71          0.57     1096.21   \n",
       "3500               10076.00       40.00          0.33      820.40   \n",
       "5034               14292.67        0.00          0.00      506.64   \n",
       "\n",
       "             standard_cost  product_first_sold_date  \n",
       "customer_id                                          \n",
       "1                   551.49                 37314.09  \n",
       "2                   640.94                 38775.67  \n",
       "3                   815.68                 39350.25  \n",
       "4                   413.58                 36008.50  \n",
       "5                   584.71                 37361.00  \n",
       "...                    ...                      ...  \n",
       "3497                698.58                 38680.33  \n",
       "3498                338.29                 37723.67  \n",
       "3499                388.32                 38163.86  \n",
       "3500                522.76                 36432.50  \n",
       "5034                416.81                 37254.67  \n",
       "\n",
       "[3494 rows x 6 columns]"
      ]
     },
     "execution_count": 151,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "customerwise_standard_cost = transactions.groupby(['customer_id']).mean().round(decimals=2)\n",
    "customerwise_standard_cost"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "id": "00125e8d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# work out the average cost (to 2 d.p.) for each customer\n",
    "#pd.options.display.float_format = '{:,.2f}'.format\n",
    "#customerStandardCost = df3.groupby(['customer_id']).mean().round(decimals=2)\n",
    "##customerStandardCost"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "id": "70b163fd",
   "metadata": {},
   "outputs": [],
   "source": [
    "#customerStandardCost = transactions.groupby(['customer_id']).mean().round(decimals=2)\n",
    "#customerStandardCost"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "id": "1fa8f6d4",
   "metadata": {},
   "outputs": [],
   "source": [
    "transactions['standard_cost'].fillna(customerwise_standard_cost.standard_cost, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "id": "f9171136",
   "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>transaction_id</th>\n",
       "      <th>product_id</th>\n",
       "      <th>customer_id</th>\n",
       "      <th>transaction_date</th>\n",
       "      <th>online_order</th>\n",
       "      <th>order_status</th>\n",
       "      <th>brand</th>\n",
       "      <th>product_line</th>\n",
       "      <th>product_class</th>\n",
       "      <th>product_size</th>\n",
       "      <th>list_price</th>\n",
       "      <th>standard_cost</th>\n",
       "      <th>product_first_sold_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9784</th>\n",
       "      <td>9785</td>\n",
       "      <td>72</td>\n",
       "      <td>1</td>\n",
       "      <td>2017-01-05</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Approved</td>\n",
       "      <td>Norco Bicycles</td>\n",
       "      <td>Standard</td>\n",
       "      <td>medium</td>\n",
       "      <td>medium</td>\n",
       "      <td>360.40</td>\n",
       "      <td>270.300000</td>\n",
       "      <td>37873.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13423</th>\n",
       "      <td>13424</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2017-02-21</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Approved</td>\n",
       "      <td>Solex</td>\n",
       "      <td>Standard</td>\n",
       "      <td>medium</td>\n",
       "      <td>medium</td>\n",
       "      <td>71.49</td>\n",
       "      <td>53.620000</td>\n",
       "      <td>38573.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14485</th>\n",
       "      <td>14486</td>\n",
       "      <td>23</td>\n",
       "      <td>1</td>\n",
       "      <td>2017-03-27</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Approved</td>\n",
       "      <td>Norco Bicycles</td>\n",
       "      <td>Mountain</td>\n",
       "      <td>low</td>\n",
       "      <td>small</td>\n",
       "      <td>688.63</td>\n",
       "      <td>612.880000</td>\n",
       "      <td>33455.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18969</th>\n",
       "      <td>18970</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>2017-03-29</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Approved</td>\n",
       "      <td>Giant Bicycles</td>\n",
       "      <td>Standard</td>\n",
       "      <td>high</td>\n",
       "      <td>medium</td>\n",
       "      <td>1274.93</td>\n",
       "      <td>764.960000</td>\n",
       "      <td>35378.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3764</th>\n",
       "      <td>3765</td>\n",
       "      <td>38</td>\n",
       "      <td>1</td>\n",
       "      <td>2017-04-06</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Approved</td>\n",
       "      <td>Solex</td>\n",
       "      <td>Standard</td>\n",
       "      <td>medium</td>\n",
       "      <td>medium</td>\n",
       "      <td>1577.53</td>\n",
       "      <td>826.510000</td>\n",
       "      <td>39526.0</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2462</th>\n",
       "      <td>2463</td>\n",
       "      <td>2</td>\n",
       "      <td>3500</td>\n",
       "      <td>2017-04-25</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Approved</td>\n",
       "      <td>Solex</td>\n",
       "      <td>Standard</td>\n",
       "      <td>medium</td>\n",
       "      <td>medium</td>\n",
       "      <td>71.49</td>\n",
       "      <td>53.620000</td>\n",
       "      <td>41245.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8561</th>\n",
       "      <td>8562</td>\n",
       "      <td>33</td>\n",
       "      <td>3500</td>\n",
       "      <td>2017-08-08</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Approved</td>\n",
       "      <td>Giant Bicycles</td>\n",
       "      <td>Standard</td>\n",
       "      <td>medium</td>\n",
       "      <td>small</td>\n",
       "      <td>1311.44</td>\n",
       "      <td>1167.180000</td>\n",
       "      <td>33888.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17468</th>\n",
       "      <td>17469</td>\n",
       "      <td>0</td>\n",
       "      <td>5034</td>\n",
       "      <td>2017-01-03</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Approved</td>\n",
       "      <td>OHM Cycles</td>\n",
       "      <td>Road</td>\n",
       "      <td>medium</td>\n",
       "      <td>medium</td>\n",
       "      <td>742.54</td>\n",
       "      <td>667.400024</td>\n",
       "      <td>33879.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16700</th>\n",
       "      <td>16701</td>\n",
       "      <td>0</td>\n",
       "      <td>5034</td>\n",
       "      <td>2017-01-27</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Approved</td>\n",
       "      <td>Norco Bicycles</td>\n",
       "      <td>Standard</td>\n",
       "      <td>medium</td>\n",
       "      <td>medium</td>\n",
       "      <td>360.40</td>\n",
       "      <td>270.299988</td>\n",
       "      <td>35667.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8707</th>\n",
       "      <td>8708</td>\n",
       "      <td>0</td>\n",
       "      <td>5034</td>\n",
       "      <td>2017-10-07</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Approved</td>\n",
       "      <td>Solex</td>\n",
       "      <td>Road</td>\n",
       "      <td>medium</td>\n",
       "      <td>medium</td>\n",
       "      <td>416.98</td>\n",
       "      <td>312.735016</td>\n",
       "      <td>42218.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>20000 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       transaction_id  product_id  customer_id transaction_date  online_order  \\\n",
       "9784             9785          72            1       2017-01-05           0.0   \n",
       "13423           13424           2            1       2017-02-21           0.0   \n",
       "14485           14486          23            1       2017-03-27           0.0   \n",
       "18969           18970          11            1       2017-03-29           1.0   \n",
       "3764             3765          38            1       2017-04-06           1.0   \n",
       "...               ...         ...          ...              ...           ...   \n",
       "2462             2463           2         3500       2017-04-25           0.0   \n",
       "8561             8562          33         3500       2017-08-08           0.0   \n",
       "17468           17469           0         5034       2017-01-03           0.0   \n",
       "16700           16701           0         5034       2017-01-27           0.0   \n",
       "8707             8708           0         5034       2017-10-07           0.0   \n",
       "\n",
       "      order_status           brand product_line product_class product_size  \\\n",
       "9784      Approved  Norco Bicycles     Standard        medium       medium   \n",
       "13423     Approved           Solex     Standard        medium       medium   \n",
       "14485     Approved  Norco Bicycles     Mountain           low        small   \n",
       "18969     Approved  Giant Bicycles     Standard          high       medium   \n",
       "3764      Approved           Solex     Standard        medium       medium   \n",
       "...            ...             ...          ...           ...          ...   \n",
       "2462      Approved           Solex     Standard        medium       medium   \n",
       "8561      Approved  Giant Bicycles     Standard        medium        small   \n",
       "17468     Approved      OHM Cycles         Road        medium       medium   \n",
       "16700     Approved  Norco Bicycles     Standard        medium       medium   \n",
       "8707      Approved           Solex         Road        medium       medium   \n",
       "\n",
       "       list_price  standard_cost  product_first_sold_date  \n",
       "9784       360.40     270.300000                  37873.0  \n",
       "13423       71.49      53.620000                  38573.0  \n",
       "14485      688.63     612.880000                  33455.0  \n",
       "18969     1274.93     764.960000                  35378.0  \n",
       "3764      1577.53     826.510000                  39526.0  \n",
       "...           ...            ...                      ...  \n",
       "2462        71.49      53.620000                  41245.0  \n",
       "8561      1311.44    1167.180000                  33888.0  \n",
       "17468      742.54     667.400024                  33879.0  \n",
       "16700      360.40     270.299988                  35667.0  \n",
       "8707       416.98     312.735016                  42218.0  \n",
       "\n",
       "[20000 rows x 13 columns]"
      ]
     },
     "execution_count": 156,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transactions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "id": "b2570707",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 20000 entries, 9784 to 8707\n",
      "Data columns (total 13 columns):\n",
      " #   Column                   Non-Null Count  Dtype         \n",
      "---  ------                   --------------  -----         \n",
      " 0   transaction_id           20000 non-null  int64         \n",
      " 1   product_id               20000 non-null  int64         \n",
      " 2   customer_id              20000 non-null  int64         \n",
      " 3   transaction_date         20000 non-null  datetime64[ns]\n",
      " 4   online_order             20000 non-null  float64       \n",
      " 5   order_status             20000 non-null  object        \n",
      " 6   brand                    20000 non-null  object        \n",
      " 7   product_line             20000 non-null  object        \n",
      " 8   product_class            20000 non-null  object        \n",
      " 9   product_size             20000 non-null  object        \n",
      " 10  list_price               20000 non-null  float64       \n",
      " 11  standard_cost            19803 non-null  float64       \n",
      " 12  product_first_sold_date  19803 non-null  float64       \n",
      "dtypes: datetime64[ns](1), float64(4), int64(3), object(5)\n",
      "memory usage: 2.1+ MB\n"
     ]
    }
   ],
   "source": [
    "transactions.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "id": "9970aa3e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "transaction_id               0\n",
       "product_id                   0\n",
       "customer_id                  0\n",
       "transaction_date             0\n",
       "online_order                 0\n",
       "order_status                 0\n",
       "brand                        0\n",
       "product_line                 0\n",
       "product_class                0\n",
       "product_size                 0\n",
       "list_price                   0\n",
       "standard_cost              197\n",
       "product_first_sold_date    197\n",
       "dtype: int64"
      ]
     },
     "execution_count": 158,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transactions.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "id": "f00b08e2",
   "metadata": {},
   "outputs": [],
   "source": [
    "#print to csv file after the cleaning\n",
    "transactions.to_csv('./transactions_cleaned.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
}