CS Project, Part 1 - Data Wrangling.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "4e763ea2",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import glob\n",
    "import pandas as pd\n",
    "from datetime import datetime as dt"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7d38e7e5",
   "metadata": {},
   "source": [
    ">#### Creating csv file for project DATA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "8df7339f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(189889, 7)"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# initializing DataFrame\n",
    "data = pd.DataFrame()\n",
    "\n",
    "# getting folder path\n",
    "path = './Project Data/'\n",
    "\n",
    "# getting all files in folder into a list\n",
    "csv_files = glob.glob(path + \"/*.csv\")\n",
    "\n",
    "# concatenating files into one df\n",
    "data = pd.concat(map(pd.read_csv, csv_files))\n",
    "\n",
    "data.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "4dc07a82",
   "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>Order ID</th>\n",
       "      <th>Product</th>\n",
       "      <th>Quantity Ordered</th>\n",
       "      <th>Price Each</th>\n",
       "      <th>Order Date</th>\n",
       "      <th>Purchase Address</th>\n",
       "      <th>Customer ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>176558</td>\n",
       "      <td>USB-C Charging Cable</td>\n",
       "      <td>2</td>\n",
       "      <td>11.95</td>\n",
       "      <td>04/19/19 08:46</td>\n",
       "      <td>917 1st St, Dallas, TX 75001</td>\n",
       "      <td>75315</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>176559</td>\n",
       "      <td>Bose SoundSport Headphones</td>\n",
       "      <td>1</td>\n",
       "      <td>99.99</td>\n",
       "      <td>04/07/19 22:30</td>\n",
       "      <td>682 Chestnut St, Boston, MA 02215</td>\n",
       "      <td>59963</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>176560</td>\n",
       "      <td>Google Phone</td>\n",
       "      <td>1</td>\n",
       "      <td>600</td>\n",
       "      <td>04/12/19 14:38</td>\n",
       "      <td>669 Spruce St, Los Angeles, CA 90001</td>\n",
       "      <td>25532</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>176560</td>\n",
       "      <td>Wired Headphones</td>\n",
       "      <td>1</td>\n",
       "      <td>11.99</td>\n",
       "      <td>04/12/19 14:38</td>\n",
       "      <td>669 Spruce St, Los Angeles, CA 90001</td>\n",
       "      <td>25532</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>176561</td>\n",
       "      <td>Wired Headphones</td>\n",
       "      <td>1</td>\n",
       "      <td>11.99</td>\n",
       "      <td>04/30/19 09:27</td>\n",
       "      <td>333 8th St, Los Angeles, CA 90001</td>\n",
       "      <td>89128</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Order ID                     Product Quantity Ordered Price Each  \\\n",
       "0   176558        USB-C Charging Cable                2      11.95   \n",
       "1   176559  Bose SoundSport Headphones                1      99.99   \n",
       "2   176560                Google Phone                1        600   \n",
       "3   176560            Wired Headphones                1      11.99   \n",
       "4   176561            Wired Headphones                1      11.99   \n",
       "\n",
       "       Order Date                      Purchase Address  Customer ID  \n",
       "0  04/19/19 08:46          917 1st St, Dallas, TX 75001        75315  \n",
       "1  04/07/19 22:30     682 Chestnut St, Boston, MA 02215        59963  \n",
       "2  04/12/19 14:38  669 Spruce St, Los Angeles, CA 90001        25532  \n",
       "3  04/12/19 14:38  669 Spruce St, Los Angeles, CA 90001        25532  \n",
       "4  04/30/19 09:27     333 8th St, Los Angeles, CA 90001        89128  "
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# confirming operation\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "f53b763c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(array(['2', '1', '3', '5', 'Quantity Ordered', '4', '7', '6', nan, '8',\n",
       "        '9'], dtype=object),\n",
       " array(['11.95', '99.99', '600', '11.99', '1700', '14.95', '389.99',\n",
       "        '3.84', '150', '2.99', '700', '300', '149.99', '109.99', '600.0',\n",
       "        '999.99', '400', '379.99', 'Price Each', '700.0', '1700.0',\n",
       "        '150.0', '300.0', '400.0', nan], dtype=object))"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# getting quantity and price series\n",
    "qty = pd.DataFrame(data[\"Quantity Ordered\"])\n",
    "price = pd.DataFrame(data[\"Price Each\"])\n",
    "\n",
    "# checking series values\n",
    "qty['Quantity Ordered'].unique(), price['Price Each'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "409446ca",
   "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>Order ID</th>\n",
       "      <th>Product</th>\n",
       "      <th>Quantity Ordered</th>\n",
       "      <th>Price Each</th>\n",
       "      <th>Order Date</th>\n",
       "      <th>Purchase Address</th>\n",
       "      <th>Customer ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>517</th>\n",
       "      <td>Order ID</td>\n",
       "      <td>Product</td>\n",
       "      <td>Quantity Ordered</td>\n",
       "      <td>Price Each</td>\n",
       "      <td>Order Date</td>\n",
       "      <td>Purchase Address</td>\n",
       "      <td>57838</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1146</th>\n",
       "      <td>Order ID</td>\n",
       "      <td>Product</td>\n",
       "      <td>Quantity Ordered</td>\n",
       "      <td>Price Each</td>\n",
       "      <td>Order Date</td>\n",
       "      <td>Purchase Address</td>\n",
       "      <td>57838</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1152</th>\n",
       "      <td>Order ID</td>\n",
       "      <td>Product</td>\n",
       "      <td>Quantity Ordered</td>\n",
       "      <td>Price Each</td>\n",
       "      <td>Order Date</td>\n",
       "      <td>Purchase Address</td>\n",
       "      <td>57838</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2869</th>\n",
       "      <td>Order ID</td>\n",
       "      <td>Product</td>\n",
       "      <td>Quantity Ordered</td>\n",
       "      <td>Price Each</td>\n",
       "      <td>Order Date</td>\n",
       "      <td>Purchase Address</td>\n",
       "      <td>57838</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2884</th>\n",
       "      <td>Order ID</td>\n",
       "      <td>Product</td>\n",
       "      <td>Quantity Ordered</td>\n",
       "      <td>Price Each</td>\n",
       "      <td>Order Date</td>\n",
       "      <td>Purchase Address</td>\n",
       "      <td>57838</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11384</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>243400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11399</th>\n",
       "      <td>Order ID</td>\n",
       "      <td>Product</td>\n",
       "      <td>Quantity Ordered</td>\n",
       "      <td>Price Each</td>\n",
       "      <td>Order Date</td>\n",
       "      <td>Purchase Address</td>\n",
       "      <td>285268</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11468</th>\n",
       "      <td>Order ID</td>\n",
       "      <td>Product</td>\n",
       "      <td>Quantity Ordered</td>\n",
       "      <td>Price Each</td>\n",
       "      <td>Order Date</td>\n",
       "      <td>Purchase Address</td>\n",
       "      <td>285268</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11574</th>\n",
       "      <td>Order ID</td>\n",
       "      <td>Product</td>\n",
       "      <td>Quantity Ordered</td>\n",
       "      <td>Price Each</td>\n",
       "      <td>Order Date</td>\n",
       "      <td>Purchase Address</td>\n",
       "      <td>285268</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11662</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>243400</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>804 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Order ID  Product  Quantity Ordered  Price Each  Order Date  \\\n",
       "517    Order ID  Product  Quantity Ordered  Price Each  Order Date   \n",
       "1146   Order ID  Product  Quantity Ordered  Price Each  Order Date   \n",
       "1152   Order ID  Product  Quantity Ordered  Price Each  Order Date   \n",
       "2869   Order ID  Product  Quantity Ordered  Price Each  Order Date   \n",
       "2884   Order ID  Product  Quantity Ordered  Price Each  Order Date   \n",
       "...         ...      ...               ...         ...         ...   \n",
       "11384       NaN      NaN               NaN         NaN         NaN   \n",
       "11399  Order ID  Product  Quantity Ordered  Price Each  Order Date   \n",
       "11468  Order ID  Product  Quantity Ordered  Price Each  Order Date   \n",
       "11574  Order ID  Product  Quantity Ordered  Price Each  Order Date   \n",
       "11662       NaN      NaN               NaN         NaN         NaN   \n",
       "\n",
       "       Purchase Address  Customer ID  \n",
       "517    Purchase Address        57838  \n",
       "1146   Purchase Address        57838  \n",
       "1152   Purchase Address        57838  \n",
       "2869   Purchase Address        57838  \n",
       "2884   Purchase Address        57838  \n",
       "...                 ...          ...  \n",
       "11384               NaN       243400  \n",
       "11399  Purchase Address       285268  \n",
       "11468  Purchase Address       285268  \n",
       "11574  Purchase Address       285268  \n",
       "11662               NaN       243400  \n",
       "\n",
       "[804 rows x 7 columns]"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# confirming presence or 'nan' or strings in what should be a numerical field\n",
    "data.loc[(data[\"Quantity Ordered\"] == \"Quantity Ordered\") | (data[\"Quantity Ordered\"].isna())]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "a5e8cc43",
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "#term = [\"price ea\"]\n",
    "#data.loc[data[\"Price Each\"].str.contains('|'.join(term), case=False)]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "03ca3187",
   "metadata": {},
   "source": [
    "> #### Refining Dataset for better Analysis and Analytics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "7ead9fd2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(189085, 7)"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# dropping all rows with NaN, or str values under \"Quantity Ordered\" & \"Price Each\" series\n",
    "# then, saving copy of dataframe to mydata\n",
    "mydata = data[~(data[\"Quantity Ordered\"] == \"Quantity Ordered\") & ~(data[\"Quantity Ordered\"].isna())].copy()\n",
    "mydata.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "37cc42a8",
   "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>Order ID</th>\n",
       "      <th>Product</th>\n",
       "      <th>Quantity Ordered</th>\n",
       "      <th>Price Each</th>\n",
       "      <th>Order Date</th>\n",
       "      <th>Purchase Address</th>\n",
       "      <th>Customer ID</th>\n",
       "      <th>Sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>176558</td>\n",
       "      <td>USB-C Charging Cable</td>\n",
       "      <td>2</td>\n",
       "      <td>11.95</td>\n",
       "      <td>04/19/19 08:46</td>\n",
       "      <td>917 1st St, Dallas, TX 75001</td>\n",
       "      <td>75315</td>\n",
       "      <td>23.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>176559</td>\n",
       "      <td>Bose SoundSport Headphones</td>\n",
       "      <td>1</td>\n",
       "      <td>99.99</td>\n",
       "      <td>04/07/19 22:30</td>\n",
       "      <td>682 Chestnut St, Boston, MA 02215</td>\n",
       "      <td>59963</td>\n",
       "      <td>99.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>176560</td>\n",
       "      <td>Google Phone</td>\n",
       "      <td>1</td>\n",
       "      <td>600</td>\n",
       "      <td>04/12/19 14:38</td>\n",
       "      <td>669 Spruce St, Los Angeles, CA 90001</td>\n",
       "      <td>25532</td>\n",
       "      <td>600.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>176560</td>\n",
       "      <td>Wired Headphones</td>\n",
       "      <td>1</td>\n",
       "      <td>11.99</td>\n",
       "      <td>04/12/19 14:38</td>\n",
       "      <td>669 Spruce St, Los Angeles, CA 90001</td>\n",
       "      <td>25532</td>\n",
       "      <td>11.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>176561</td>\n",
       "      <td>Wired Headphones</td>\n",
       "      <td>1</td>\n",
       "      <td>11.99</td>\n",
       "      <td>04/30/19 09:27</td>\n",
       "      <td>333 8th St, Los Angeles, CA 90001</td>\n",
       "      <td>89128</td>\n",
       "      <td>11.99</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Order ID                     Product Quantity Ordered Price Each  \\\n",
       "0   176558        USB-C Charging Cable                2      11.95   \n",
       "1   176559  Bose SoundSport Headphones                1      99.99   \n",
       "2   176560                Google Phone                1        600   \n",
       "3   176560            Wired Headphones                1      11.99   \n",
       "4   176561            Wired Headphones                1      11.99   \n",
       "\n",
       "       Order Date                      Purchase Address  Customer ID   Sales  \n",
       "0  04/19/19 08:46          917 1st St, Dallas, TX 75001        75315   23.90  \n",
       "1  04/07/19 22:30     682 Chestnut St, Boston, MA 02215        59963   99.99  \n",
       "2  04/12/19 14:38  669 Spruce St, Los Angeles, CA 90001        25532  600.00  \n",
       "3  04/12/19 14:38  669 Spruce St, Los Angeles, CA 90001        25532   11.99  \n",
       "4  04/30/19 09:27     333 8th St, Los Angeles, CA 90001        89128   11.99  "
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# adding sales series i.e quantity ordered * price each\n",
    "mydata[\"Sales\"] = mydata.loc[:, \"Quantity Ordered\"].astype(int) * mydata.loc[:, \"Price Each\"].astype(float)\n",
    "mydata.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "52dfa42b",
   "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>Order ID</th>\n",
       "      <th>Product</th>\n",
       "      <th>Quantity Ordered</th>\n",
       "      <th>Price Each</th>\n",
       "      <th>Order Date</th>\n",
       "      <th>Purchase Address</th>\n",
       "      <th>Customer ID</th>\n",
       "      <th>Sales</th>\n",
       "      <th>Datetime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>176558</td>\n",
       "      <td>USB-C Charging Cable</td>\n",
       "      <td>2</td>\n",
       "      <td>11.95</td>\n",
       "      <td>04/19/19 08:46</td>\n",
       "      <td>917 1st St, Dallas, TX 75001</td>\n",
       "      <td>75315</td>\n",
       "      <td>23.90</td>\n",
       "      <td>2019-04-19 08:46:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>176559</td>\n",
       "      <td>Bose SoundSport Headphones</td>\n",
       "      <td>1</td>\n",
       "      <td>99.99</td>\n",
       "      <td>04/07/19 22:30</td>\n",
       "      <td>682 Chestnut St, Boston, MA 02215</td>\n",
       "      <td>59963</td>\n",
       "      <td>99.99</td>\n",
       "      <td>2019-04-07 22:30:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>176560</td>\n",
       "      <td>Google Phone</td>\n",
       "      <td>1</td>\n",
       "      <td>600</td>\n",
       "      <td>04/12/19 14:38</td>\n",
       "      <td>669 Spruce St, Los Angeles, CA 90001</td>\n",
       "      <td>25532</td>\n",
       "      <td>600.00</td>\n",
       "      <td>2019-04-12 14:38:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>176560</td>\n",
       "      <td>Wired Headphones</td>\n",
       "      <td>1</td>\n",
       "      <td>11.99</td>\n",
       "      <td>04/12/19 14:38</td>\n",
       "      <td>669 Spruce St, Los Angeles, CA 90001</td>\n",
       "      <td>25532</td>\n",
       "      <td>11.99</td>\n",
       "      <td>2019-04-12 14:38:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>176561</td>\n",
       "      <td>Wired Headphones</td>\n",
       "      <td>1</td>\n",
       "      <td>11.99</td>\n",
       "      <td>04/30/19 09:27</td>\n",
       "      <td>333 8th St, Los Angeles, CA 90001</td>\n",
       "      <td>89128</td>\n",
       "      <td>11.99</td>\n",
       "      <td>2019-04-30 09:27:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Order ID                     Product Quantity Ordered Price Each  \\\n",
       "0   176558        USB-C Charging Cable                2      11.95   \n",
       "1   176559  Bose SoundSport Headphones                1      99.99   \n",
       "2   176560                Google Phone                1        600   \n",
       "3   176560            Wired Headphones                1      11.99   \n",
       "4   176561            Wired Headphones                1      11.99   \n",
       "\n",
       "       Order Date                      Purchase Address  Customer ID   Sales  \\\n",
       "0  04/19/19 08:46          917 1st St, Dallas, TX 75001        75315   23.90   \n",
       "1  04/07/19 22:30     682 Chestnut St, Boston, MA 02215        59963   99.99   \n",
       "2  04/12/19 14:38  669 Spruce St, Los Angeles, CA 90001        25532  600.00   \n",
       "3  04/12/19 14:38  669 Spruce St, Los Angeles, CA 90001        25532   11.99   \n",
       "4  04/30/19 09:27     333 8th St, Los Angeles, CA 90001        89128   11.99   \n",
       "\n",
       "             Datetime  \n",
       "0 2019-04-19 08:46:00  \n",
       "1 2019-04-07 22:30:00  \n",
       "2 2019-04-12 14:38:00  \n",
       "3 2019-04-12 14:38:00  \n",
       "4 2019-04-30 09:27:00  "
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# creating datetime series apropos to python date format\n",
    "mydata[\"Datetime\"] = pd.to_datetime(mydata[\"Order Date\"])\n",
    "mydata.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "363cc89b",
   "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>Order ID</th>\n",
       "      <th>Product</th>\n",
       "      <th>Quantity Ordered</th>\n",
       "      <th>Price Each</th>\n",
       "      <th>Order Date</th>\n",
       "      <th>Purchase Address</th>\n",
       "      <th>Customer ID</th>\n",
       "      <th>Sales</th>\n",
       "      <th>Datetime</th>\n",
       "      <th>Period</th>\n",
       "      <th>Day</th>\n",
       "      <th>Month</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>176558</td>\n",
       "      <td>USB-C Charging Cable</td>\n",
       "      <td>2</td>\n",
       "      <td>11.95</td>\n",
       "      <td>04/19/19 08:46</td>\n",
       "      <td>917 1st St, Dallas, TX 75001</td>\n",
       "      <td>75315</td>\n",
       "      <td>23.90</td>\n",
       "      <td>2019-04-19 08:46:00</td>\n",
       "      <td>8</td>\n",
       "      <td>Friday</td>\n",
       "      <td>Apr</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>176559</td>\n",
       "      <td>Bose SoundSport Headphones</td>\n",
       "      <td>1</td>\n",
       "      <td>99.99</td>\n",
       "      <td>04/07/19 22:30</td>\n",
       "      <td>682 Chestnut St, Boston, MA 02215</td>\n",
       "      <td>59963</td>\n",
       "      <td>99.99</td>\n",
       "      <td>2019-04-07 22:30:00</td>\n",
       "      <td>22</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>Apr</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>176560</td>\n",
       "      <td>Google Phone</td>\n",
       "      <td>1</td>\n",
       "      <td>600</td>\n",
       "      <td>04/12/19 14:38</td>\n",
       "      <td>669 Spruce St, Los Angeles, CA 90001</td>\n",
       "      <td>25532</td>\n",
       "      <td>600.00</td>\n",
       "      <td>2019-04-12 14:38:00</td>\n",
       "      <td>14</td>\n",
       "      <td>Friday</td>\n",
       "      <td>Apr</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>176560</td>\n",
       "      <td>Wired Headphones</td>\n",
       "      <td>1</td>\n",
       "      <td>11.99</td>\n",
       "      <td>04/12/19 14:38</td>\n",
       "      <td>669 Spruce St, Los Angeles, CA 90001</td>\n",
       "      <td>25532</td>\n",
       "      <td>11.99</td>\n",
       "      <td>2019-04-12 14:38:00</td>\n",
       "      <td>14</td>\n",
       "      <td>Friday</td>\n",
       "      <td>Apr</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>176561</td>\n",
       "      <td>Wired Headphones</td>\n",
       "      <td>1</td>\n",
       "      <td>11.99</td>\n",
       "      <td>04/30/19 09:27</td>\n",
       "      <td>333 8th St, Los Angeles, CA 90001</td>\n",
       "      <td>89128</td>\n",
       "      <td>11.99</td>\n",
       "      <td>2019-04-30 09:27:00</td>\n",
       "      <td>9</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>Apr</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Order ID                     Product Quantity Ordered Price Each  \\\n",
       "0   176558        USB-C Charging Cable                2      11.95   \n",
       "1   176559  Bose SoundSport Headphones                1      99.99   \n",
       "2   176560                Google Phone                1        600   \n",
       "3   176560            Wired Headphones                1      11.99   \n",
       "4   176561            Wired Headphones                1      11.99   \n",
       "\n",
       "       Order Date                      Purchase Address  Customer ID   Sales  \\\n",
       "0  04/19/19 08:46          917 1st St, Dallas, TX 75001        75315   23.90   \n",
       "1  04/07/19 22:30     682 Chestnut St, Boston, MA 02215        59963   99.99   \n",
       "2  04/12/19 14:38  669 Spruce St, Los Angeles, CA 90001        25532  600.00   \n",
       "3  04/12/19 14:38  669 Spruce St, Los Angeles, CA 90001        25532   11.99   \n",
       "4  04/30/19 09:27     333 8th St, Los Angeles, CA 90001        89128   11.99   \n",
       "\n",
       "             Datetime  Period      Day Month  \n",
       "0 2019-04-19 08:46:00       8   Friday   Apr  \n",
       "1 2019-04-07 22:30:00      22   Sunday   Apr  \n",
       "2 2019-04-12 14:38:00      14   Friday   Apr  \n",
       "3 2019-04-12 14:38:00      14   Friday   Apr  \n",
       "4 2019-04-30 09:27:00       9  Tuesday   Apr  "
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# adding period, day and month series\n",
    "    # adding period series\n",
    "mydata[\"Period\"] = mydata[\"Datetime\"].dt.hour\n",
    "\n",
    "    # adding day series\n",
    "mydata[\"Day\"] = mydata[\"Datetime\"].dt.day_name()\n",
    "\n",
    "    # then, set month series\n",
    "mydata[\"Month\"] = mydata[\"Datetime\"].apply(lambda c: dt.strftime(c, \"%b\"))\n",
    "\n",
    "# confirming operation\n",
    "mydata.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "4cc589e3",
   "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>Order ID</th>\n",
       "      <th>Product</th>\n",
       "      <th>Quantity Ordered</th>\n",
       "      <th>Price Each</th>\n",
       "      <th>Order Date</th>\n",
       "      <th>Purchase Address</th>\n",
       "      <th>Customer ID</th>\n",
       "      <th>Sales</th>\n",
       "      <th>Datetime</th>\n",
       "      <th>Period</th>\n",
       "      <th>Day</th>\n",
       "      <th>Month</th>\n",
       "      <th>City State</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>176558</td>\n",
       "      <td>USB-C Charging Cable</td>\n",
       "      <td>2</td>\n",
       "      <td>11.95</td>\n",
       "      <td>04/19/19 08:46</td>\n",
       "      <td>917 1st St, Dallas, TX 75001</td>\n",
       "      <td>75315</td>\n",
       "      <td>23.90</td>\n",
       "      <td>2019-04-19 08:46:00</td>\n",
       "      <td>8</td>\n",
       "      <td>Friday</td>\n",
       "      <td>Apr</td>\n",
       "      <td>Dallas TX</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>176559</td>\n",
       "      <td>Bose SoundSport Headphones</td>\n",
       "      <td>1</td>\n",
       "      <td>99.99</td>\n",
       "      <td>04/07/19 22:30</td>\n",
       "      <td>682 Chestnut St, Boston, MA 02215</td>\n",
       "      <td>59963</td>\n",
       "      <td>99.99</td>\n",
       "      <td>2019-04-07 22:30:00</td>\n",
       "      <td>22</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>Apr</td>\n",
       "      <td>Boston MA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>176560</td>\n",
       "      <td>Google Phone</td>\n",
       "      <td>1</td>\n",
       "      <td>600</td>\n",
       "      <td>04/12/19 14:38</td>\n",
       "      <td>669 Spruce St, Los Angeles, CA 90001</td>\n",
       "      <td>25532</td>\n",
       "      <td>600.00</td>\n",
       "      <td>2019-04-12 14:38:00</td>\n",
       "      <td>14</td>\n",
       "      <td>Friday</td>\n",
       "      <td>Apr</td>\n",
       "      <td>Los Angeles CA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>176560</td>\n",
       "      <td>Wired Headphones</td>\n",
       "      <td>1</td>\n",
       "      <td>11.99</td>\n",
       "      <td>04/12/19 14:38</td>\n",
       "      <td>669 Spruce St, Los Angeles, CA 90001</td>\n",
       "      <td>25532</td>\n",
       "      <td>11.99</td>\n",
       "      <td>2019-04-12 14:38:00</td>\n",
       "      <td>14</td>\n",
       "      <td>Friday</td>\n",
       "      <td>Apr</td>\n",
       "      <td>Los Angeles CA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>176561</td>\n",
       "      <td>Wired Headphones</td>\n",
       "      <td>1</td>\n",
       "      <td>11.99</td>\n",
       "      <td>04/30/19 09:27</td>\n",
       "      <td>333 8th St, Los Angeles, CA 90001</td>\n",
       "      <td>89128</td>\n",
       "      <td>11.99</td>\n",
       "      <td>2019-04-30 09:27:00</td>\n",
       "      <td>9</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>Apr</td>\n",
       "      <td>Los Angeles CA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Order ID                     Product Quantity Ordered Price Each  \\\n",
       "0   176558        USB-C Charging Cable                2      11.95   \n",
       "1   176559  Bose SoundSport Headphones                1      99.99   \n",
       "2   176560                Google Phone                1        600   \n",
       "3   176560            Wired Headphones                1      11.99   \n",
       "4   176561            Wired Headphones                1      11.99   \n",
       "\n",
       "       Order Date                      Purchase Address  Customer ID   Sales  \\\n",
       "0  04/19/19 08:46          917 1st St, Dallas, TX 75001        75315   23.90   \n",
       "1  04/07/19 22:30     682 Chestnut St, Boston, MA 02215        59963   99.99   \n",
       "2  04/12/19 14:38  669 Spruce St, Los Angeles, CA 90001        25532  600.00   \n",
       "3  04/12/19 14:38  669 Spruce St, Los Angeles, CA 90001        25532   11.99   \n",
       "4  04/30/19 09:27     333 8th St, Los Angeles, CA 90001        89128   11.99   \n",
       "\n",
       "             Datetime  Period      Day Month       City State  \n",
       "0 2019-04-19 08:46:00       8   Friday   Apr        Dallas TX  \n",
       "1 2019-04-07 22:30:00      22   Sunday   Apr        Boston MA  \n",
       "2 2019-04-12 14:38:00      14   Friday   Apr   Los Angeles CA  \n",
       "3 2019-04-12 14:38:00      14   Friday   Apr   Los Angeles CA  \n",
       "4 2019-04-30 09:27:00       9  Tuesday   Apr   Los Angeles CA  "
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# adding city state series \n",
    "    # getting city\n",
    "def get_city(address):\n",
    "    return address.split(',')[1]\n",
    "\n",
    "    # getting state\n",
    "def get_state(address):\n",
    "    return address.split(',')[2].split(' ')[1]\n",
    "\n",
    "# extrapolating cities and thier respective states\n",
    "mydata[\"City State\"] = mydata[\"Purchase Address\"].apply(lambda c: f\"{get_city(c)} {get_state(c)}\")  \n",
    "\n",
    "# confirming operation\n",
    "mydata.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "356268ec",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['USB-C Charging Cable', 'Bose SoundSport Headphones',\n",
       "       'Google Phone', 'Wired Headphones', 'Macbook Pro Laptop',\n",
       "       'Lightning Charging Cable', '27in 4K Gaming Monitor',\n",
       "       'AA Batteries (4-pack)', 'Apple Airpods Headphones',\n",
       "       'AAA Batteries (4-pack)', 'iPhone', 'Flatscreen TV',\n",
       "       '27in FHD Monitor', '20in Monitor', 'LG Dryer', 'ThinkPad Laptop',\n",
       "       'Vareebadd Phone', 'LG Washing Machine', '34in Ultrawide Monitor'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# checking unique products, to see if data is relevant\n",
    "mydata[\"Product\"].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "0e050382",
   "metadata": {},
   "outputs": [],
   "source": [
    "# dropping unwanted columns\n",
    "mydata.drop([\"Order Date\", \"Purchase Address\"], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "395c665c",
   "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>Order ID</th>\n",
       "      <th>Product</th>\n",
       "      <th>Quantity Ordered</th>\n",
       "      <th>Price Each</th>\n",
       "      <th>Customer ID</th>\n",
       "      <th>Sales</th>\n",
       "      <th>Datetime</th>\n",
       "      <th>Period</th>\n",
       "      <th>Day</th>\n",
       "      <th>Month</th>\n",
       "      <th>City State</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>176558</td>\n",
       "      <td>USB-C Charging Cable</td>\n",
       "      <td>2</td>\n",
       "      <td>11.95</td>\n",
       "      <td>75315</td>\n",
       "      <td>23.90</td>\n",
       "      <td>2019-04-19 08:46:00</td>\n",
       "      <td>8</td>\n",
       "      <td>Friday</td>\n",
       "      <td>Apr</td>\n",
       "      <td>Dallas TX</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>176559</td>\n",
       "      <td>Bose SoundSport Headphones</td>\n",
       "      <td>1</td>\n",
       "      <td>99.99</td>\n",
       "      <td>59963</td>\n",
       "      <td>99.99</td>\n",
       "      <td>2019-04-07 22:30:00</td>\n",
       "      <td>22</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>Apr</td>\n",
       "      <td>Boston MA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>176560</td>\n",
       "      <td>Google Phone</td>\n",
       "      <td>1</td>\n",
       "      <td>600</td>\n",
       "      <td>25532</td>\n",
       "      <td>600.00</td>\n",
       "      <td>2019-04-12 14:38:00</td>\n",
       "      <td>14</td>\n",
       "      <td>Friday</td>\n",
       "      <td>Apr</td>\n",
       "      <td>Los Angeles CA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>176560</td>\n",
       "      <td>Wired Headphones</td>\n",
       "      <td>1</td>\n",
       "      <td>11.99</td>\n",
       "      <td>25532</td>\n",
       "      <td>11.99</td>\n",
       "      <td>2019-04-12 14:38:00</td>\n",
       "      <td>14</td>\n",
       "      <td>Friday</td>\n",
       "      <td>Apr</td>\n",
       "      <td>Los Angeles CA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>176561</td>\n",
       "      <td>Wired Headphones</td>\n",
       "      <td>1</td>\n",
       "      <td>11.99</td>\n",
       "      <td>89128</td>\n",
       "      <td>11.99</td>\n",
       "      <td>2019-04-30 09:27:00</td>\n",
       "      <td>9</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>Apr</td>\n",
       "      <td>Los Angeles CA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Order ID                     Product Quantity Ordered Price Each  \\\n",
       "0   176558        USB-C Charging Cable                2      11.95   \n",
       "1   176559  Bose SoundSport Headphones                1      99.99   \n",
       "2   176560                Google Phone                1        600   \n",
       "3   176560            Wired Headphones                1      11.99   \n",
       "4   176561            Wired Headphones                1      11.99   \n",
       "\n",
       "   Customer ID   Sales            Datetime  Period      Day Month  \\\n",
       "0        75315   23.90 2019-04-19 08:46:00       8   Friday   Apr   \n",
       "1        59963   99.99 2019-04-07 22:30:00      22   Sunday   Apr   \n",
       "2        25532  600.00 2019-04-12 14:38:00      14   Friday   Apr   \n",
       "3        25532   11.99 2019-04-12 14:38:00      14   Friday   Apr   \n",
       "4        89128   11.99 2019-04-30 09:27:00       9  Tuesday   Apr   \n",
       "\n",
       "        City State  \n",
       "0        Dallas TX  \n",
       "1        Boston MA  \n",
       "2   Los Angeles CA  \n",
       "3   Los Angeles CA  \n",
       "4   Los Angeles CA  "
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Refined Data\n",
    "mydata.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "5d6b0288",
   "metadata": {},
   "outputs": [],
   "source": [
    "# writing to csv\n",
    "mydata.to_csv(\"Data.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "48914b05",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d4a0b422",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ed5e00e1",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.8.12"
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}