data_manipulation.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import pandasql as ps\n",
    "import calendar"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_cust = pd.read_excel('customer.xlsx')\n",
    "df_order = pd.read_excel('order.xlsx')\n",
    "df_prod = pd.read_excel('product.xlsx')\n",
    "df_prod_cat = pd.read_excel('product_category.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CustomerID</th>\n",
       "      <th>FirstName</th>\n",
       "      <th>LastName</th>\n",
       "      <th>CustomerEmail</th>\n",
       "      <th>CustomerPhone</th>\n",
       "      <th>CustomerAddress</th>\n",
       "      <th>CustomerCity</th>\n",
       "      <th>CustomerState</th>\n",
       "      <th>CustomerZip</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Grazia</td>\n",
       "      <td>Rasmus</td>\n",
       "      <td>grasmusas@i2i.jp#mailto:grasmusas@i2i.jp#</td>\n",
       "      <td>(202) 577-2595</td>\n",
       "      <td>628 Buhler Junction</td>\n",
       "      <td>Washington</td>\n",
       "      <td>District of Columbia</td>\n",
       "      <td>20029</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Bunny</td>\n",
       "      <td>Trevan</td>\n",
       "      <td>btrevanmj@wordpress.org#mailto:btrevanmj@wordp...</td>\n",
       "      <td>917-903-2827</td>\n",
       "      <td>52 Cascade Drive</td>\n",
       "      <td>Jamaica</td>\n",
       "      <td>New York</td>\n",
       "      <td>11436</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Tracie</td>\n",
       "      <td>Grayston</td>\n",
       "      <td>tgrayston7k@pagesperso-orange.fr#mailto:tgrays...</td>\n",
       "      <td>404-868-2391</td>\n",
       "      <td>672 Comanche Way</td>\n",
       "      <td>Atlanta</td>\n",
       "      <td>Georgia</td>\n",
       "      <td>30343</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Amerigo</td>\n",
       "      <td>Garrelts</td>\n",
       "      <td>agarrelts6e@oaic.gov.au#mailto:agarrelts6e@oai...</td>\n",
       "      <td>415-190-3290</td>\n",
       "      <td>8252 Village Green Hill</td>\n",
       "      <td>San Francisco</td>\n",
       "      <td>California</td>\n",
       "      <td>94177</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Shea</td>\n",
       "      <td>Stronghill</td>\n",
       "      <td>sstronghillc1@google.nl#mailto:sstronghillc1@g...</td>\n",
       "      <td>432-775-7828</td>\n",
       "      <td>542 3rd Point</td>\n",
       "      <td>Midland</td>\n",
       "      <td>Texas</td>\n",
       "      <td>79705</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   CustomerID FirstName    LastName  \\\n",
       "0           1    Grazia      Rasmus   \n",
       "1           2     Bunny      Trevan   \n",
       "2           3    Tracie    Grayston   \n",
       "3           4   Amerigo    Garrelts   \n",
       "4           5      Shea  Stronghill   \n",
       "\n",
       "                                       CustomerEmail   CustomerPhone  \\\n",
       "0          grasmusas@i2i.jp#mailto:grasmusas@i2i.jp#  (202) 577-2595   \n",
       "1  btrevanmj@wordpress.org#mailto:btrevanmj@wordp...    917-903-2827   \n",
       "2  tgrayston7k@pagesperso-orange.fr#mailto:tgrays...    404-868-2391   \n",
       "3  agarrelts6e@oaic.gov.au#mailto:agarrelts6e@oai...    415-190-3290   \n",
       "4  sstronghillc1@google.nl#mailto:sstronghillc1@g...    432-775-7828   \n",
       "\n",
       "           CustomerAddress   CustomerCity         CustomerState  CustomerZip  \n",
       "0      628 Buhler Junction     Washington  District of Columbia        20029  \n",
       "1         52 Cascade Drive        Jamaica              New York        11436  \n",
       "2         672 Comanche Way        Atlanta               Georgia        30343  \n",
       "3  8252 Village Green Hill  San Francisco            California        94177  \n",
       "4            542 3rd Point        Midland                 Texas        79705  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_cust.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>OrderID</th>\n",
       "      <th>Date</th>\n",
       "      <th>CustomerID</th>\n",
       "      <th>ProdNumber</th>\n",
       "      <th>Quantity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>1866</td>\n",
       "      <td>EB514</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>1567</td>\n",
       "      <td>RS706</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>2064</td>\n",
       "      <td>TV804</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>287</td>\n",
       "      <td>DK203</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>422</td>\n",
       "      <td>EB517</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   OrderID       Date  CustomerID ProdNumber  Quantity\n",
       "0        1 2020-01-01        1866      EB514         2\n",
       "1        2 2020-01-01        1567      RS706         3\n",
       "2        3 2020-01-01        2064      TV804         6\n",
       "3        4 2020-01-01         287      DK203         1\n",
       "4        5 2020-01-01         422      EB517         5"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_order.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>ProdNumber</th>\n",
       "      <th>ProdName</th>\n",
       "      <th>Category</th>\n",
       "      <th>Price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BP101</td>\n",
       "      <td>All Eyes Drone Blueprint</td>\n",
       "      <td>1</td>\n",
       "      <td>9.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BP102</td>\n",
       "      <td>Bsquare Robot Blueprint</td>\n",
       "      <td>1</td>\n",
       "      <td>8.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BP104</td>\n",
       "      <td>Cat Robot Blueprint</td>\n",
       "      <td>1</td>\n",
       "      <td>4.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BP105</td>\n",
       "      <td>Creature Robot Arms Blueprint</td>\n",
       "      <td>1</td>\n",
       "      <td>12.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BP106</td>\n",
       "      <td>Hexacopter Drone Blueprint</td>\n",
       "      <td>1</td>\n",
       "      <td>8.99</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  ProdNumber                       ProdName  Category  Price\n",
       "0      BP101       All Eyes Drone Blueprint         1   9.99\n",
       "1      BP102        Bsquare Robot Blueprint         1   8.99\n",
       "2      BP104            Cat Robot Blueprint         1   4.99\n",
       "3      BP105  Creature Robot Arms Blueprint         1  12.00\n",
       "4      BP106     Hexacopter Drone Blueprint         1   8.99"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_prod.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CategoryID</th>\n",
       "      <th>CategoryName</th>\n",
       "      <th>CategoryAbbreviation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Blueprints</td>\n",
       "      <td>BP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Drone Kits</td>\n",
       "      <td>DK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Drones</td>\n",
       "      <td>DS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>eBooks</td>\n",
       "      <td>EB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Robot Kits</td>\n",
       "      <td>RK</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   CategoryID CategoryName CategoryAbbreviation\n",
       "0           1   Blueprints                   BP\n",
       "1           2   Drone Kits                   DK\n",
       "2           3       Drones                   DS\n",
       "3           4       eBooks                   EB\n",
       "4           5   Robot Kits                   RK"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_prod_cat.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "join_data = ps.sqldf('''\n",
    "                        SELECT \n",
    "                            a.Date, \n",
    "                            d.CustomerID, \n",
    "                            d.FirstName, \n",
    "                            d.LastName, \n",
    "                            d.CustomerEmail,\n",
    "                            d.CustomerPhone,\n",
    "                            d.CustomerAddress,\n",
    "                            d.CustomerCity,\n",
    "                            d.CustomerState,\n",
    "                            d.CustomerZip,\n",
    "                            c.CategoryName,\n",
    "                            b.ProdName,\n",
    "                            SUM(a.Quantity) Quantity,\n",
    "                            SUM(b.Price) Price\n",
    "                        FROM df_order a\n",
    "                        JOIN df_prod b on a.ProdNumber = b.ProdNumber\n",
    "                        JOIN df_prod_cat c on b.Category = c.CategoryID\n",
    "                        JOIN df_cust d on a.CustomerID = d.CustomerID\n",
    "                        GROUP BY  \n",
    "                            a.Date, \n",
    "                            d.CustomerID, \n",
    "                            d.FirstName, \n",
    "                            d.LastName, \n",
    "                            d.CustomerEmail,\n",
    "                            d.CustomerPhone,\n",
    "                            d.CustomerAddress,\n",
    "                            d.CustomerCity,\n",
    "                            d.CustomerState,\n",
    "                            d.CustomerZip,\n",
    "                            c.CategoryName,\n",
    "                            b.ProdName\n",
    "            ''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>Date</th>\n",
       "      <th>CustomerID</th>\n",
       "      <th>FirstName</th>\n",
       "      <th>LastName</th>\n",
       "      <th>CustomerEmail</th>\n",
       "      <th>CustomerPhone</th>\n",
       "      <th>CustomerAddress</th>\n",
       "      <th>CustomerCity</th>\n",
       "      <th>CustomerState</th>\n",
       "      <th>CustomerZip</th>\n",
       "      <th>CategoryName</th>\n",
       "      <th>ProdName</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>Price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-01-01 00:00:00.000000</td>\n",
       "      <td>287</td>\n",
       "      <td>Elna</td>\n",
       "      <td>De Angelo</td>\n",
       "      <td>edew@nba.com#mailto:edew@nba.com#</td>\n",
       "      <td>808-945-4067</td>\n",
       "      <td>78 Shasta Park</td>\n",
       "      <td>Honolulu</td>\n",
       "      <td>Hawaii</td>\n",
       "      <td>96820</td>\n",
       "      <td>Drone Kits</td>\n",
       "      <td>BYOD-220</td>\n",
       "      <td>1</td>\n",
       "      <td>69.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-01-01 00:00:00.000000</td>\n",
       "      <td>422</td>\n",
       "      <td>Lucita</td>\n",
       "      <td>Lesper</td>\n",
       "      <td>llespercx@com.com#mailto:llespercx@com.com#</td>\n",
       "      <td>515-193-2721</td>\n",
       "      <td>393 Holmberg Center</td>\n",
       "      <td>Des Moines</td>\n",
       "      <td>Iowa</td>\n",
       "      <td>50315</td>\n",
       "      <td>eBooks</td>\n",
       "      <td>SCARA Robots</td>\n",
       "      <td>5</td>\n",
       "      <td>19.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-01-01 00:00:00.000000</td>\n",
       "      <td>954</td>\n",
       "      <td>Llewellyn</td>\n",
       "      <td>Fromont</td>\n",
       "      <td>lfromonte9@de.vu#mailto:lfromonte9@de.vu#</td>\n",
       "      <td>205-279-7028</td>\n",
       "      <td>14 Rowland Lane</td>\n",
       "      <td>Birmingham</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>35244</td>\n",
       "      <td>eBooks</td>\n",
       "      <td>Spherical Robots</td>\n",
       "      <td>5</td>\n",
       "      <td>16.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-01-01 00:00:00.000000</td>\n",
       "      <td>1567</td>\n",
       "      <td>Terencio</td>\n",
       "      <td>McKern</td>\n",
       "      <td>tmckernot@tinyurl.com#mailto:tmckernot@tinyurl...</td>\n",
       "      <td>832-987-8363</td>\n",
       "      <td>26 Muir Lane</td>\n",
       "      <td>Katy</td>\n",
       "      <td>Texas</td>\n",
       "      <td>77493</td>\n",
       "      <td>Robots</td>\n",
       "      <td>RWW-75 Robot</td>\n",
       "      <td>3</td>\n",
       "      <td>883.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-01-01 00:00:00.000000</td>\n",
       "      <td>1866</td>\n",
       "      <td>Farrand</td>\n",
       "      <td>Vasler</td>\n",
       "      <td>fvaslerqt@comsenz.com#mailto:fvaslerqt@comsenz...</td>\n",
       "      <td>601-786-0195</td>\n",
       "      <td>134 Melrose Pass</td>\n",
       "      <td>Jackson</td>\n",
       "      <td>Mississippi</td>\n",
       "      <td>39216</td>\n",
       "      <td>eBooks</td>\n",
       "      <td>Polar Robots</td>\n",
       "      <td>2</td>\n",
       "      <td>23.99</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",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3331</th>\n",
       "      <td>2021-12-31 00:00:00.000000</td>\n",
       "      <td>582</td>\n",
       "      <td>Nancie</td>\n",
       "      <td>Motherwell</td>\n",
       "      <td>nmotherwello3@istockphoto.com#mailto:nmotherwe...</td>\n",
       "      <td>770-130-2276</td>\n",
       "      <td>73 Reinke Junction</td>\n",
       "      <td>Atlanta</td>\n",
       "      <td>Georgia</td>\n",
       "      <td>31119</td>\n",
       "      <td>Robot Kits</td>\n",
       "      <td>BYOR-1000</td>\n",
       "      <td>4</td>\n",
       "      <td>189.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3332</th>\n",
       "      <td>2021-12-31 00:00:00.000000</td>\n",
       "      <td>625</td>\n",
       "      <td>Robinett</td>\n",
       "      <td>Cossum</td>\n",
       "      <td>rcossumba@devhub.com#mailto:rcossumba@devhub.com#</td>\n",
       "      <td>314-377-5588</td>\n",
       "      <td>226 Karstens Hill</td>\n",
       "      <td>Saint Louis</td>\n",
       "      <td>Missouri</td>\n",
       "      <td>63169</td>\n",
       "      <td>Drones</td>\n",
       "      <td>DC-304 Drone</td>\n",
       "      <td>5</td>\n",
       "      <td>395.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3333</th>\n",
       "      <td>2021-12-31 00:00:00.000000</td>\n",
       "      <td>1214</td>\n",
       "      <td>Joey</td>\n",
       "      <td>Sumpner</td>\n",
       "      <td>jsumpner5u@google.com.au#mailto:jsumpner5u@goo...</td>\n",
       "      <td>786-405-4171</td>\n",
       "      <td>420 Transport Center</td>\n",
       "      <td>Miami</td>\n",
       "      <td>Florida</td>\n",
       "      <td>33169</td>\n",
       "      <td>Drones</td>\n",
       "      <td>MICR-564K Drone</td>\n",
       "      <td>3</td>\n",
       "      <td>499.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3334</th>\n",
       "      <td>2021-12-31 00:00:00.000000</td>\n",
       "      <td>1225</td>\n",
       "      <td>Chelsy</td>\n",
       "      <td>Collop</td>\n",
       "      <td>ccollopoi@delicious.com#mailto:ccollopoi@delic...</td>\n",
       "      <td>412-943-7336</td>\n",
       "      <td>680 Bluestem Trail</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>15274</td>\n",
       "      <td>Training Videos</td>\n",
       "      <td>Understanding Automation</td>\n",
       "      <td>5</td>\n",
       "      <td>44.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3335</th>\n",
       "      <td>2021-12-31 00:00:00.000000</td>\n",
       "      <td>1439</td>\n",
       "      <td>Robby</td>\n",
       "      <td>Drinkale</td>\n",
       "      <td>rdrinkale8z@tripod.com#mailto:rdrinkale8z@trip...</td>\n",
       "      <td>209-943-7338</td>\n",
       "      <td>2203 Fordem Center</td>\n",
       "      <td>Fresno</td>\n",
       "      <td>California</td>\n",
       "      <td>93721</td>\n",
       "      <td>Training Videos</td>\n",
       "      <td>Open Source Code</td>\n",
       "      <td>3</td>\n",
       "      <td>32.95</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3336 rows × 14 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                            Date  CustomerID  FirstName    LastName  \\\n",
       "0     2020-01-01 00:00:00.000000         287       Elna   De Angelo   \n",
       "1     2020-01-01 00:00:00.000000         422     Lucita      Lesper   \n",
       "2     2020-01-01 00:00:00.000000         954  Llewellyn     Fromont   \n",
       "3     2020-01-01 00:00:00.000000        1567   Terencio      McKern   \n",
       "4     2020-01-01 00:00:00.000000        1866    Farrand      Vasler   \n",
       "...                          ...         ...        ...         ...   \n",
       "3331  2021-12-31 00:00:00.000000         582     Nancie  Motherwell   \n",
       "3332  2021-12-31 00:00:00.000000         625   Robinett      Cossum   \n",
       "3333  2021-12-31 00:00:00.000000        1214       Joey     Sumpner   \n",
       "3334  2021-12-31 00:00:00.000000        1225     Chelsy      Collop   \n",
       "3335  2021-12-31 00:00:00.000000        1439      Robby    Drinkale   \n",
       "\n",
       "                                          CustomerEmail CustomerPhone  \\\n",
       "0                     edew@nba.com#mailto:edew@nba.com#  808-945-4067   \n",
       "1           llespercx@com.com#mailto:llespercx@com.com#  515-193-2721   \n",
       "2             lfromonte9@de.vu#mailto:lfromonte9@de.vu#  205-279-7028   \n",
       "3     tmckernot@tinyurl.com#mailto:tmckernot@tinyurl...  832-987-8363   \n",
       "4     fvaslerqt@comsenz.com#mailto:fvaslerqt@comsenz...  601-786-0195   \n",
       "...                                                 ...           ...   \n",
       "3331  nmotherwello3@istockphoto.com#mailto:nmotherwe...  770-130-2276   \n",
       "3332  rcossumba@devhub.com#mailto:rcossumba@devhub.com#  314-377-5588   \n",
       "3333  jsumpner5u@google.com.au#mailto:jsumpner5u@goo...  786-405-4171   \n",
       "3334  ccollopoi@delicious.com#mailto:ccollopoi@delic...  412-943-7336   \n",
       "3335  rdrinkale8z@tripod.com#mailto:rdrinkale8z@trip...  209-943-7338   \n",
       "\n",
       "           CustomerAddress CustomerCity CustomerState  CustomerZip  \\\n",
       "0           78 Shasta Park     Honolulu        Hawaii        96820   \n",
       "1      393 Holmberg Center   Des Moines          Iowa        50315   \n",
       "2          14 Rowland Lane   Birmingham       Alabama        35244   \n",
       "3             26 Muir Lane         Katy         Texas        77493   \n",
       "4         134 Melrose Pass      Jackson   Mississippi        39216   \n",
       "...                    ...          ...           ...          ...   \n",
       "3331    73 Reinke Junction      Atlanta       Georgia        31119   \n",
       "3332     226 Karstens Hill  Saint Louis      Missouri        63169   \n",
       "3333  420 Transport Center        Miami       Florida        33169   \n",
       "3334    680 Bluestem Trail   Pittsburgh  Pennsylvania        15274   \n",
       "3335    2203 Fordem Center       Fresno    California        93721   \n",
       "\n",
       "         CategoryName                  ProdName  Quantity   Price  \n",
       "0          Drone Kits                  BYOD-220         1   69.00  \n",
       "1              eBooks              SCARA Robots         5   19.50  \n",
       "2              eBooks          Spherical Robots         5   16.75  \n",
       "3              Robots              RWW-75 Robot         3  883.00  \n",
       "4              eBooks              Polar Robots         2   23.99  \n",
       "...               ...                       ...       ...     ...  \n",
       "3331       Robot Kits                 BYOR-1000         4  189.00  \n",
       "3332           Drones              DC-304 Drone         5  395.00  \n",
       "3333           Drones           MICR-564K Drone         3  499.00  \n",
       "3334  Training Videos  Understanding Automation         5   44.95  \n",
       "3335  Training Videos          Open Source Code         3   32.95  \n",
       "\n",
       "[3336 rows x 14 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "join_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "join_data['Day'] = join_data['Date'].str.slice(8, 10)\n",
    "join_data['Month'] = join_data['Date'].str.slice(5, 7)\n",
    "join_data['Year'] = join_data['Date'].str.slice(0, 4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>Date</th>\n",
       "      <th>CustomerID</th>\n",
       "      <th>FirstName</th>\n",
       "      <th>LastName</th>\n",
       "      <th>CustomerEmail</th>\n",
       "      <th>CustomerPhone</th>\n",
       "      <th>CustomerAddress</th>\n",
       "      <th>CustomerCity</th>\n",
       "      <th>CustomerState</th>\n",
       "      <th>CustomerZip</th>\n",
       "      <th>CategoryName</th>\n",
       "      <th>ProdName</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>Price</th>\n",
       "      <th>Day</th>\n",
       "      <th>Month</th>\n",
       "      <th>Year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-01-01 00:00:00.000000</td>\n",
       "      <td>287</td>\n",
       "      <td>Elna</td>\n",
       "      <td>De Angelo</td>\n",
       "      <td>edew@nba.com#mailto:edew@nba.com#</td>\n",
       "      <td>808-945-4067</td>\n",
       "      <td>78 Shasta Park</td>\n",
       "      <td>Honolulu</td>\n",
       "      <td>Hawaii</td>\n",
       "      <td>96820</td>\n",
       "      <td>Drone Kits</td>\n",
       "      <td>BYOD-220</td>\n",
       "      <td>1</td>\n",
       "      <td>69.00</td>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-01-01 00:00:00.000000</td>\n",
       "      <td>422</td>\n",
       "      <td>Lucita</td>\n",
       "      <td>Lesper</td>\n",
       "      <td>llespercx@com.com#mailto:llespercx@com.com#</td>\n",
       "      <td>515-193-2721</td>\n",
       "      <td>393 Holmberg Center</td>\n",
       "      <td>Des Moines</td>\n",
       "      <td>Iowa</td>\n",
       "      <td>50315</td>\n",
       "      <td>eBooks</td>\n",
       "      <td>SCARA Robots</td>\n",
       "      <td>5</td>\n",
       "      <td>19.50</td>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-01-01 00:00:00.000000</td>\n",
       "      <td>954</td>\n",
       "      <td>Llewellyn</td>\n",
       "      <td>Fromont</td>\n",
       "      <td>lfromonte9@de.vu#mailto:lfromonte9@de.vu#</td>\n",
       "      <td>205-279-7028</td>\n",
       "      <td>14 Rowland Lane</td>\n",
       "      <td>Birmingham</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>35244</td>\n",
       "      <td>eBooks</td>\n",
       "      <td>Spherical Robots</td>\n",
       "      <td>5</td>\n",
       "      <td>16.75</td>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-01-01 00:00:00.000000</td>\n",
       "      <td>1567</td>\n",
       "      <td>Terencio</td>\n",
       "      <td>McKern</td>\n",
       "      <td>tmckernot@tinyurl.com#mailto:tmckernot@tinyurl...</td>\n",
       "      <td>832-987-8363</td>\n",
       "      <td>26 Muir Lane</td>\n",
       "      <td>Katy</td>\n",
       "      <td>Texas</td>\n",
       "      <td>77493</td>\n",
       "      <td>Robots</td>\n",
       "      <td>RWW-75 Robot</td>\n",
       "      <td>3</td>\n",
       "      <td>883.00</td>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-01-01 00:00:00.000000</td>\n",
       "      <td>1866</td>\n",
       "      <td>Farrand</td>\n",
       "      <td>Vasler</td>\n",
       "      <td>fvaslerqt@comsenz.com#mailto:fvaslerqt@comsenz...</td>\n",
       "      <td>601-786-0195</td>\n",
       "      <td>134 Melrose Pass</td>\n",
       "      <td>Jackson</td>\n",
       "      <td>Mississippi</td>\n",
       "      <td>39216</td>\n",
       "      <td>eBooks</td>\n",
       "      <td>Polar Robots</td>\n",
       "      <td>2</td>\n",
       "      <td>23.99</td>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>2020</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3331</th>\n",
       "      <td>2021-12-31 00:00:00.000000</td>\n",
       "      <td>582</td>\n",
       "      <td>Nancie</td>\n",
       "      <td>Motherwell</td>\n",
       "      <td>nmotherwello3@istockphoto.com#mailto:nmotherwe...</td>\n",
       "      <td>770-130-2276</td>\n",
       "      <td>73 Reinke Junction</td>\n",
       "      <td>Atlanta</td>\n",
       "      <td>Georgia</td>\n",
       "      <td>31119</td>\n",
       "      <td>Robot Kits</td>\n",
       "      <td>BYOR-1000</td>\n",
       "      <td>4</td>\n",
       "      <td>189.00</td>\n",
       "      <td>31</td>\n",
       "      <td>12</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3332</th>\n",
       "      <td>2021-12-31 00:00:00.000000</td>\n",
       "      <td>625</td>\n",
       "      <td>Robinett</td>\n",
       "      <td>Cossum</td>\n",
       "      <td>rcossumba@devhub.com#mailto:rcossumba@devhub.com#</td>\n",
       "      <td>314-377-5588</td>\n",
       "      <td>226 Karstens Hill</td>\n",
       "      <td>Saint Louis</td>\n",
       "      <td>Missouri</td>\n",
       "      <td>63169</td>\n",
       "      <td>Drones</td>\n",
       "      <td>DC-304 Drone</td>\n",
       "      <td>5</td>\n",
       "      <td>395.00</td>\n",
       "      <td>31</td>\n",
       "      <td>12</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3333</th>\n",
       "      <td>2021-12-31 00:00:00.000000</td>\n",
       "      <td>1214</td>\n",
       "      <td>Joey</td>\n",
       "      <td>Sumpner</td>\n",
       "      <td>jsumpner5u@google.com.au#mailto:jsumpner5u@goo...</td>\n",
       "      <td>786-405-4171</td>\n",
       "      <td>420 Transport Center</td>\n",
       "      <td>Miami</td>\n",
       "      <td>Florida</td>\n",
       "      <td>33169</td>\n",
       "      <td>Drones</td>\n",
       "      <td>MICR-564K Drone</td>\n",
       "      <td>3</td>\n",
       "      <td>499.00</td>\n",
       "      <td>31</td>\n",
       "      <td>12</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3334</th>\n",
       "      <td>2021-12-31 00:00:00.000000</td>\n",
       "      <td>1225</td>\n",
       "      <td>Chelsy</td>\n",
       "      <td>Collop</td>\n",
       "      <td>ccollopoi@delicious.com#mailto:ccollopoi@delic...</td>\n",
       "      <td>412-943-7336</td>\n",
       "      <td>680 Bluestem Trail</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>15274</td>\n",
       "      <td>Training Videos</td>\n",
       "      <td>Understanding Automation</td>\n",
       "      <td>5</td>\n",
       "      <td>44.95</td>\n",
       "      <td>31</td>\n",
       "      <td>12</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3335</th>\n",
       "      <td>2021-12-31 00:00:00.000000</td>\n",
       "      <td>1439</td>\n",
       "      <td>Robby</td>\n",
       "      <td>Drinkale</td>\n",
       "      <td>rdrinkale8z@tripod.com#mailto:rdrinkale8z@trip...</td>\n",
       "      <td>209-943-7338</td>\n",
       "      <td>2203 Fordem Center</td>\n",
       "      <td>Fresno</td>\n",
       "      <td>California</td>\n",
       "      <td>93721</td>\n",
       "      <td>Training Videos</td>\n",
       "      <td>Open Source Code</td>\n",
       "      <td>3</td>\n",
       "      <td>32.95</td>\n",
       "      <td>31</td>\n",
       "      <td>12</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3336 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                            Date  CustomerID  FirstName    LastName  \\\n",
       "0     2020-01-01 00:00:00.000000         287       Elna   De Angelo   \n",
       "1     2020-01-01 00:00:00.000000         422     Lucita      Lesper   \n",
       "2     2020-01-01 00:00:00.000000         954  Llewellyn     Fromont   \n",
       "3     2020-01-01 00:00:00.000000        1567   Terencio      McKern   \n",
       "4     2020-01-01 00:00:00.000000        1866    Farrand      Vasler   \n",
       "...                          ...         ...        ...         ...   \n",
       "3331  2021-12-31 00:00:00.000000         582     Nancie  Motherwell   \n",
       "3332  2021-12-31 00:00:00.000000         625   Robinett      Cossum   \n",
       "3333  2021-12-31 00:00:00.000000        1214       Joey     Sumpner   \n",
       "3334  2021-12-31 00:00:00.000000        1225     Chelsy      Collop   \n",
       "3335  2021-12-31 00:00:00.000000        1439      Robby    Drinkale   \n",
       "\n",
       "                                          CustomerEmail CustomerPhone  \\\n",
       "0                     edew@nba.com#mailto:edew@nba.com#  808-945-4067   \n",
       "1           llespercx@com.com#mailto:llespercx@com.com#  515-193-2721   \n",
       "2             lfromonte9@de.vu#mailto:lfromonte9@de.vu#  205-279-7028   \n",
       "3     tmckernot@tinyurl.com#mailto:tmckernot@tinyurl...  832-987-8363   \n",
       "4     fvaslerqt@comsenz.com#mailto:fvaslerqt@comsenz...  601-786-0195   \n",
       "...                                                 ...           ...   \n",
       "3331  nmotherwello3@istockphoto.com#mailto:nmotherwe...  770-130-2276   \n",
       "3332  rcossumba@devhub.com#mailto:rcossumba@devhub.com#  314-377-5588   \n",
       "3333  jsumpner5u@google.com.au#mailto:jsumpner5u@goo...  786-405-4171   \n",
       "3334  ccollopoi@delicious.com#mailto:ccollopoi@delic...  412-943-7336   \n",
       "3335  rdrinkale8z@tripod.com#mailto:rdrinkale8z@trip...  209-943-7338   \n",
       "\n",
       "           CustomerAddress CustomerCity CustomerState  CustomerZip  \\\n",
       "0           78 Shasta Park     Honolulu        Hawaii        96820   \n",
       "1      393 Holmberg Center   Des Moines          Iowa        50315   \n",
       "2          14 Rowland Lane   Birmingham       Alabama        35244   \n",
       "3             26 Muir Lane         Katy         Texas        77493   \n",
       "4         134 Melrose Pass      Jackson   Mississippi        39216   \n",
       "...                    ...          ...           ...          ...   \n",
       "3331    73 Reinke Junction      Atlanta       Georgia        31119   \n",
       "3332     226 Karstens Hill  Saint Louis      Missouri        63169   \n",
       "3333  420 Transport Center        Miami       Florida        33169   \n",
       "3334    680 Bluestem Trail   Pittsburgh  Pennsylvania        15274   \n",
       "3335    2203 Fordem Center       Fresno    California        93721   \n",
       "\n",
       "         CategoryName                  ProdName  Quantity   Price Day Month  \\\n",
       "0          Drone Kits                  BYOD-220         1   69.00  01    01   \n",
       "1              eBooks              SCARA Robots         5   19.50  01    01   \n",
       "2              eBooks          Spherical Robots         5   16.75  01    01   \n",
       "3              Robots              RWW-75 Robot         3  883.00  01    01   \n",
       "4              eBooks              Polar Robots         2   23.99  01    01   \n",
       "...               ...                       ...       ...     ...  ..   ...   \n",
       "3331       Robot Kits                 BYOR-1000         4  189.00  31    12   \n",
       "3332           Drones              DC-304 Drone         5  395.00  31    12   \n",
       "3333           Drones           MICR-564K Drone         3  499.00  31    12   \n",
       "3334  Training Videos  Understanding Automation         5   44.95  31    12   \n",
       "3335  Training Videos          Open Source Code         3   32.95  31    12   \n",
       "\n",
       "      Year  \n",
       "0     2020  \n",
       "1     2020  \n",
       "2     2020  \n",
       "3     2020  \n",
       "4     2020  \n",
       "...    ...  \n",
       "3331  2021  \n",
       "3332  2021  \n",
       "3333  2021  \n",
       "3334  2021  \n",
       "3335  2021  \n",
       "\n",
       "[3336 rows x 17 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "join_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>Date</th>\n",
       "      <th>CustomerID</th>\n",
       "      <th>FirstName</th>\n",
       "      <th>LastName</th>\n",
       "      <th>CustomerEmail</th>\n",
       "      <th>CustomerPhone</th>\n",
       "      <th>CustomerAddress</th>\n",
       "      <th>CustomerCity</th>\n",
       "      <th>CustomerState</th>\n",
       "      <th>CustomerZip</th>\n",
       "      <th>CategoryName</th>\n",
       "      <th>ProdName</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>Price</th>\n",
       "      <th>Day</th>\n",
       "      <th>Month</th>\n",
       "      <th>Year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-01-01 00:00:00.000000</td>\n",
       "      <td>287</td>\n",
       "      <td>Elna</td>\n",
       "      <td>De Angelo</td>\n",
       "      <td>edew@nba.com#mailto:edew@nba.com#</td>\n",
       "      <td>808-945-4067</td>\n",
       "      <td>78 Shasta Park</td>\n",
       "      <td>Honolulu</td>\n",
       "      <td>Hawaii</td>\n",
       "      <td>96820</td>\n",
       "      <td>Drone Kits</td>\n",
       "      <td>BYOD-220</td>\n",
       "      <td>1</td>\n",
       "      <td>69.00</td>\n",
       "      <td>01</td>\n",
       "      <td>Jan</td>\n",
       "      <td>2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-01-01 00:00:00.000000</td>\n",
       "      <td>422</td>\n",
       "      <td>Lucita</td>\n",
       "      <td>Lesper</td>\n",
       "      <td>llespercx@com.com#mailto:llespercx@com.com#</td>\n",
       "      <td>515-193-2721</td>\n",
       "      <td>393 Holmberg Center</td>\n",
       "      <td>Des Moines</td>\n",
       "      <td>Iowa</td>\n",
       "      <td>50315</td>\n",
       "      <td>eBooks</td>\n",
       "      <td>SCARA Robots</td>\n",
       "      <td>5</td>\n",
       "      <td>19.50</td>\n",
       "      <td>01</td>\n",
       "      <td>Jan</td>\n",
       "      <td>2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-01-01 00:00:00.000000</td>\n",
       "      <td>954</td>\n",
       "      <td>Llewellyn</td>\n",
       "      <td>Fromont</td>\n",
       "      <td>lfromonte9@de.vu#mailto:lfromonte9@de.vu#</td>\n",
       "      <td>205-279-7028</td>\n",
       "      <td>14 Rowland Lane</td>\n",
       "      <td>Birmingham</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>35244</td>\n",
       "      <td>eBooks</td>\n",
       "      <td>Spherical Robots</td>\n",
       "      <td>5</td>\n",
       "      <td>16.75</td>\n",
       "      <td>01</td>\n",
       "      <td>Jan</td>\n",
       "      <td>2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-01-01 00:00:00.000000</td>\n",
       "      <td>1567</td>\n",
       "      <td>Terencio</td>\n",
       "      <td>McKern</td>\n",
       "      <td>tmckernot@tinyurl.com#mailto:tmckernot@tinyurl...</td>\n",
       "      <td>832-987-8363</td>\n",
       "      <td>26 Muir Lane</td>\n",
       "      <td>Katy</td>\n",
       "      <td>Texas</td>\n",
       "      <td>77493</td>\n",
       "      <td>Robots</td>\n",
       "      <td>RWW-75 Robot</td>\n",
       "      <td>3</td>\n",
       "      <td>883.00</td>\n",
       "      <td>01</td>\n",
       "      <td>Jan</td>\n",
       "      <td>2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-01-01 00:00:00.000000</td>\n",
       "      <td>1866</td>\n",
       "      <td>Farrand</td>\n",
       "      <td>Vasler</td>\n",
       "      <td>fvaslerqt@comsenz.com#mailto:fvaslerqt@comsenz...</td>\n",
       "      <td>601-786-0195</td>\n",
       "      <td>134 Melrose Pass</td>\n",
       "      <td>Jackson</td>\n",
       "      <td>Mississippi</td>\n",
       "      <td>39216</td>\n",
       "      <td>eBooks</td>\n",
       "      <td>Polar Robots</td>\n",
       "      <td>2</td>\n",
       "      <td>23.99</td>\n",
       "      <td>01</td>\n",
       "      <td>Jan</td>\n",
       "      <td>2020</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         Date  CustomerID  FirstName   LastName  \\\n",
       "0  2020-01-01 00:00:00.000000         287       Elna  De Angelo   \n",
       "1  2020-01-01 00:00:00.000000         422     Lucita     Lesper   \n",
       "2  2020-01-01 00:00:00.000000         954  Llewellyn    Fromont   \n",
       "3  2020-01-01 00:00:00.000000        1567   Terencio     McKern   \n",
       "4  2020-01-01 00:00:00.000000        1866    Farrand     Vasler   \n",
       "\n",
       "                                       CustomerEmail CustomerPhone  \\\n",
       "0                  edew@nba.com#mailto:edew@nba.com#  808-945-4067   \n",
       "1        llespercx@com.com#mailto:llespercx@com.com#  515-193-2721   \n",
       "2          lfromonte9@de.vu#mailto:lfromonte9@de.vu#  205-279-7028   \n",
       "3  tmckernot@tinyurl.com#mailto:tmckernot@tinyurl...  832-987-8363   \n",
       "4  fvaslerqt@comsenz.com#mailto:fvaslerqt@comsenz...  601-786-0195   \n",
       "\n",
       "       CustomerAddress CustomerCity CustomerState  CustomerZip CategoryName  \\\n",
       "0       78 Shasta Park     Honolulu        Hawaii        96820   Drone Kits   \n",
       "1  393 Holmberg Center   Des Moines          Iowa        50315       eBooks   \n",
       "2      14 Rowland Lane   Birmingham       Alabama        35244       eBooks   \n",
       "3         26 Muir Lane         Katy         Texas        77493       Robots   \n",
       "4     134 Melrose Pass      Jackson   Mississippi        39216       eBooks   \n",
       "\n",
       "           ProdName  Quantity   Price Day Month  Year  \n",
       "0          BYOD-220         1   69.00  01   Jan  2020  \n",
       "1      SCARA Robots         5   19.50  01   Jan  2020  \n",
       "2  Spherical Robots         5   16.75  01   Jan  2020  \n",
       "3      RWW-75 Robot         3  883.00  01   Jan  2020  \n",
       "4      Polar Robots         2   23.99  01   Jan  2020  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "look_up = {'01': 'Jan', '02': 'Feb', '03': 'Mar', '04': 'Apr', '05': 'May',\n",
    "            '06': 'Jun', '07': 'Jul', '08': 'Aug', '09': 'Sep', '10': 'Oct', '11': 'Nov', '12': 'Dec'}\n",
    "\n",
    "join_data['Month'] = join_data['Month'].apply(lambda x: look_up[x])\n",
    "join_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# file_name = 'new_transaction.xlsx'\n",
    "# join_data.to_excel(file_name)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.10.3 64-bit",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.3"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "369f2c481f4da34e4445cda3fffd2e751bd1c4d706f27375911949ba6bb62e1c"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}