importPostGreSQL.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "def dict_factory(cursor, row):\n",
    "    fields = [column[0] for column in cursor.description]\n",
    "    return {key: value for key, value in zip(fields, row)}"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# DB Connect"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "import pandas as pd\n",
    "\n",
    "import datetime\n",
    "\n",
    "#con = sqlite3.connect(\"superstore2.db\")\n",
    "# #con.row_factory = dict_factory\n",
    "# #cur = con.cursor()\n",
    "# con.execute('PRAGMA foreign_keys = ON;')\n",
    "# cur.execute('PRAGMA foreign_keys = ON;')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# engine = sqlalchemy.create_engine('postgresql://fgwctbbl:pwy-MGnMWbbTJcrSzofNhxUA7o7JQF6E@rogue.db.elephantsql.com/fgwctbbl')\n",
    "# print(bool(engine)) # <- just to keep track of the process\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# from sqlalchemy import create_engine\n",
    "# import sqlalchemy\n",
    "# sql_engine = create_engine('postgresql://fgwctbbl:pwy-MGnMWbbTJcrSzofNhxUA7o7JQF6E@rogue.db.elephantsql.com/fgwctbbl')\n",
    "# conn = sql_engine.raw_connection()\n",
    "\n",
    "# import psycopg2\n",
    "# conn = psycopg2.connect(\n",
    "#     host=\"rogue.db.elephantsql.com\",\n",
    "#     database=\"fgwctbbl\",\n",
    "#     user=\"fgwctbbl\",\n",
    "#     password=\"pwy-MGnMWbbTJcrSzofNhxUA7o7JQF6E\"\n",
    "# )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "#cur = conn.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# to show all rows and columns \n",
    "pd.set_option('display.max_columns', None)\n",
    "pd.set_option('display.max_rows', None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# read the csv into a pandas dataframe\n",
    "orders = pd.read_csv('Data/Global-Superstore-Orders.csv')\n",
    "returns = pd.read_csv('Data/Global-Superstore-Returns.csv')\n",
    "people = pd.read_csv('Data/Global-Superstore-People.csv')\n",
    "\n",
    "# Convert column names to lowercase and remove spaces and hyphens\n",
    "orders.rename(columns=lambda x: x.replace(' ', '_').replace('-', ''), inplace=True)\n",
    "returns.rename(columns=lambda x: x.replace(' ', '_').replace('-', ''), inplace=True)\n",
    "people.rename(columns=lambda x: x.replace(' ', '_').replace('-', ''), inplace=True)\n",
    "\n",
    "orders['Order_Date'] = pd.to_datetime(orders['Order_Date'])\n",
    "orders['Ship_Date'] = pd.to_datetime(orders['Ship_Date'])\n",
    "\n",
    "orders['Sales'] = orders['Sales'].str.replace(',', '.')\n",
    "orders['Discount'] = orders['Discount'].str.replace(',', '.')\n",
    "orders['Profit'] = orders['Profit'].str.replace(',', '.')\n",
    "orders['Shipping_Cost'] = orders['Shipping_Cost'].str.replace(',', '.')\n",
    "orders = orders.astype({\n",
    "    'Sales': 'float64',\n",
    "    'Discount': 'float64',\n",
    "    'Profit': 'float64',\n",
    "    'Shipping_Cost': 'float64'\n",
    "})\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Merge Returns"
   ]
  },
  {
   "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>Row_ID</th>\n",
       "      <th>Order_ID</th>\n",
       "      <th>Order_Date</th>\n",
       "      <th>Ship_Date</th>\n",
       "      <th>Ship_Mode</th>\n",
       "      <th>Customer_ID</th>\n",
       "      <th>Customer_Name</th>\n",
       "      <th>Segment</th>\n",
       "      <th>Postal_Code</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>Country</th>\n",
       "      <th>Region</th>\n",
       "      <th>Market</th>\n",
       "      <th>Product_ID</th>\n",
       "      <th>Product_Name</th>\n",
       "      <th>SubCategory</th>\n",
       "      <th>Category</th>\n",
       "      <th>Sales</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>Discount</th>\n",
       "      <th>Profit</th>\n",
       "      <th>Shipping_Cost</th>\n",
       "      <th>Order_Priority</th>\n",
       "      <th>Returned</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>24599</td>\n",
       "      <td>IN-2017-CA120551-42816</td>\n",
       "      <td>2017-03-22</td>\n",
       "      <td>2017-03-29</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>CA-120551</td>\n",
       "      <td>Cathy Armstrong</td>\n",
       "      <td>Home Office</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Herat</td>\n",
       "      <td>Hirat</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Southern Asia</td>\n",
       "      <td>Asia Pacific</td>\n",
       "      <td>FUR-BO-4861</td>\n",
       "      <td>Ikea Library with Doors, Mobile</td>\n",
       "      <td>Bookcases</td>\n",
       "      <td>Furniture</td>\n",
       "      <td>731.82</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>102.42</td>\n",
       "      <td>39.66</td>\n",
       "      <td>Medium</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Row_ID                Order_ID Order_Date  Ship_Date       Ship_Mode  \\\n",
       "0   24599  IN-2017-CA120551-42816 2017-03-22 2017-03-29  Standard Class   \n",
       "\n",
       "  Customer_ID    Customer_Name      Segment  Postal_Code   City  State  \\\n",
       "0   CA-120551  Cathy Armstrong  Home Office          NaN  Herat  Hirat   \n",
       "\n",
       "       Country         Region        Market   Product_ID  \\\n",
       "0  Afghanistan  Southern Asia  Asia Pacific  FUR-BO-4861   \n",
       "\n",
       "                      Product_Name SubCategory   Category   Sales  Quantity  \\\n",
       "0  Ikea Library with Doors, Mobile   Bookcases  Furniture  731.82         2   \n",
       "\n",
       "   Discount  Profit  Shipping_Cost Order_Priority  Returned  \n",
       "0       0.0  102.42          39.66         Medium      True  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "if \"Region\" in returns: \n",
    "    returns = returns.drop([\"Region\"], axis=1)\n",
    "    returns = returns.rename(columns={'f': 'Returned'})\n",
    "    \n",
    "#display(returns.head())\n",
    "orders = pd.merge(orders, returns, on=\"Order_ID\", how=\"outer\")\n",
    "orders['Returned'] = orders['Returned'].fillna(False)\n",
    "orders['Returned'] = orders['Returned'].replace(\"Yes\", True)\n",
    "orders = orders.astype({'Returned': 'bool'})\n",
    "orders.head(1)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Merge People"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define the division of Western and Eastern Canada\n",
    "western_provinces = ['Alberta', 'British Columbia', 'Manitoba', 'Saskatchewan']\n",
    "eastern_provinces = ['Newfoundland', 'Nova Scotia', 'Ontario', 'Quebec']\n",
    "\n",
    "# Create a new column \"Region\" and assign the corresponding region\n",
    "orders.loc[orders['State'].isin(western_provinces), 'Region'] = 'Western Canada'\n",
    "orders.loc[orders['State'].isin(eastern_provinces), 'Region'] = 'Eastern Canada'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(51593, 26)\n",
      "(51593, 26)\n"
     ]
    },
    {
     "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>Row_ID</th>\n",
       "      <th>Order_ID</th>\n",
       "      <th>Order_Date</th>\n",
       "      <th>Ship_Date</th>\n",
       "      <th>Ship_Mode</th>\n",
       "      <th>Customer_ID</th>\n",
       "      <th>Customer_Name</th>\n",
       "      <th>Segment</th>\n",
       "      <th>Postal_Code</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>Country</th>\n",
       "      <th>Region</th>\n",
       "      <th>Market</th>\n",
       "      <th>Product_ID</th>\n",
       "      <th>Product_Name</th>\n",
       "      <th>SubCategory</th>\n",
       "      <th>Category</th>\n",
       "      <th>Sales</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>Discount</th>\n",
       "      <th>Profit</th>\n",
       "      <th>Shipping_Cost</th>\n",
       "      <th>Order_Priority</th>\n",
       "      <th>Returned</th>\n",
       "      <th>Person</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>24599</td>\n",
       "      <td>IN-2017-CA120551-42816</td>\n",
       "      <td>2017-03-22</td>\n",
       "      <td>2017-03-29</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>CA-120551</td>\n",
       "      <td>Cathy Armstrong</td>\n",
       "      <td>Home Office</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Herat</td>\n",
       "      <td>Hirat</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Southern Asia</td>\n",
       "      <td>Asia Pacific</td>\n",
       "      <td>FUR-BO-4861</td>\n",
       "      <td>Ikea Library with Doors, Mobile</td>\n",
       "      <td>Bookcases</td>\n",
       "      <td>Furniture</td>\n",
       "      <td>731.82</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>102.42</td>\n",
       "      <td>39.66</td>\n",
       "      <td>Medium</td>\n",
       "      <td>True</td>\n",
       "      <td>Chandrakant Chaudhri</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Row_ID                Order_ID Order_Date  Ship_Date       Ship_Mode  \\\n",
       "0   24599  IN-2017-CA120551-42816 2017-03-22 2017-03-29  Standard Class   \n",
       "\n",
       "  Customer_ID    Customer_Name      Segment  Postal_Code   City  State  \\\n",
       "0   CA-120551  Cathy Armstrong  Home Office          NaN  Herat  Hirat   \n",
       "\n",
       "       Country         Region        Market   Product_ID  \\\n",
       "0  Afghanistan  Southern Asia  Asia Pacific  FUR-BO-4861   \n",
       "\n",
       "                      Product_Name SubCategory   Category   Sales  Quantity  \\\n",
       "0  Ikea Library with Doors, Mobile   Bookcases  Furniture  731.82         2   \n",
       "\n",
       "   Discount  Profit  Shipping_Cost Order_Priority  Returned  \\\n",
       "0       0.0  102.42          39.66         Medium      True   \n",
       "\n",
       "                 Person  \n",
       "0  Chandrakant Chaudhri  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orders = pd.merge(orders, people, on=\"Region\", how=\"outer\")\n",
    "print(orders.shape)\n",
    "orders.dropna(subset=[\"Order_ID\"], inplace=True)\n",
    "#orders[\"People_ID\"] = orders.groupby(\"Person\").ngroup() + 1\n",
    "print(orders.shape)\n",
    "orders.head(1)  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 51593 entries, 0 to 51592\n",
      "Data columns (total 26 columns):\n",
      " #   Column          Non-Null Count  Dtype         \n",
      "---  ------          --------------  -----         \n",
      " 0   Row_ID          51593 non-null  int64         \n",
      " 1   Order_ID        51593 non-null  object        \n",
      " 2   Order_Date      51593 non-null  datetime64[ns]\n",
      " 3   Ship_Date       51593 non-null  datetime64[ns]\n",
      " 4   Ship_Mode       51593 non-null  object        \n",
      " 5   Customer_ID     51593 non-null  object        \n",
      " 6   Customer_Name   51593 non-null  object        \n",
      " 7   Segment         51593 non-null  object        \n",
      " 8   Postal_Code     10078 non-null  float64       \n",
      " 9   City            51593 non-null  object        \n",
      " 10  State           51593 non-null  object        \n",
      " 11  Country         51593 non-null  object        \n",
      " 12  Region          51593 non-null  object        \n",
      " 13  Market          51593 non-null  object        \n",
      " 14  Product_ID      51593 non-null  object        \n",
      " 15  Product_Name    51593 non-null  object        \n",
      " 16  SubCategory     51593 non-null  object        \n",
      " 17  Category        51593 non-null  object        \n",
      " 18  Sales           51593 non-null  float64       \n",
      " 19  Quantity        51593 non-null  int64         \n",
      " 20  Discount        51593 non-null  float64       \n",
      " 21  Profit          51593 non-null  float64       \n",
      " 22  Shipping_Cost   51593 non-null  float64       \n",
      " 23  Order_Priority  51593 non-null  object        \n",
      " 24  Returned        51593 non-null  bool          \n",
      " 25  Person          51593 non-null  object        \n",
      "dtypes: bool(1), datetime64[ns](2), float64(5), int64(2), object(16)\n",
      "memory usage: 10.3+ MB\n"
     ]
    }
   ],
   "source": [
    "orders.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Row_ID [1 2 3 4 5] ... [51286 51287 51288 51289 51290]\n",
      "Order_ID ['AE-2014-PO8865138-41914' 'AE-2016-EB4110138-42657'\n",
      " 'AE-2016-MY7380138-42735' 'AE-2017-GH4665138-43082'\n",
      " 'AE-2017-JD5790138-42801'] ... ['ZA-2017-RC9960146-42988' 'ZA-2017-RP9390146-42830'\n",
      " 'ZA-2017-SM10005146-42972' 'ZA-2017-SW10350146-42792'\n",
      " 'ZA-2017-TS11205146-42793']\n",
      "Order_Date ['2014-01-01T00:00:00.000000000' '2014-01-02T00:00:00.000000000'\n",
      " '2014-01-03T00:00:00.000000000' '2014-01-04T00:00:00.000000000'\n",
      " '2014-01-05T00:00:00.000000000'] ... ['2017-12-27T00:00:00.000000000' '2017-12-28T00:00:00.000000000'\n",
      " '2017-12-29T00:00:00.000000000' '2017-12-30T00:00:00.000000000'\n",
      " '2017-12-31T00:00:00.000000000']\n",
      "Ship_Date ['2014-01-03T00:00:00.000000000' '2014-01-05T00:00:00.000000000'\n",
      " '2014-01-06T00:00:00.000000000' '2014-01-07T00:00:00.000000000'\n",
      " '2014-01-08T00:00:00.000000000'] ... ['2018-01-03T00:00:00.000000000' '2018-01-04T00:00:00.000000000'\n",
      " '2018-01-05T00:00:00.000000000' '2018-01-06T00:00:00.000000000'\n",
      " '2018-01-07T00:00:00.000000000']\n",
      "Ship_Mode ['First Class' 'Same Day' 'Second Class' 'Standard Class']\n",
      "Customer_ID ['AA-10315102' 'AA-10315120' 'AA-10315139' 'AA-103151402' 'AA-103151404'] ... ['ZD-2192548' 'ZD-2192564' 'ZD-219257' 'ZD-2192582' 'ZD-2192596']\n",
      "Customer_Name ['Aaron Bergman' 'Aaron Hawkins' 'Aaron Smayling' 'Adam Bellavance'\n",
      " 'Adam Hart'] ... ['Xylona Preis' 'Yana Sorensen' 'Yoseph Carroll' 'Zuschuss Carroll'\n",
      " 'Zuschuss Donatelli']\n",
      "Segment ['Consumer' 'Corporate' 'Home Office']\n",
      "Postal_Code [1040. 1453. 1752. 1810. 1841.] ... [98632. 98661. 99207. 99301.    nan]\n",
      "City ['Aachen' 'Aalen' 'Aalst' 'Aba' 'Abadan'] ... ['Zwedru' 'Zwickau' 'Zwolle' 'eMbalenhle' 'Águas Lindas de Goiás']\n",
      "State [\"'Ajman\" \"'Amman\" \"'Asir\" 'Abia' 'Abidjan'] ... ['Zinder' 'Zulia' 'Zürich' 'Šiauliai' 'Žilina']\n",
      "Country ['Afghanistan' 'Albania' 'Algeria' 'Angola' 'Argentina'] ... ['Vietnam' 'Western Sahara' 'Yemen' 'Zambia' 'Zimbabwe']\n",
      "Region ['Caribbean' 'Central Africa' 'Central America' 'Central Asia'\n",
      " 'Central US'] ... ['Western Africa' 'Western Asia' 'Western Canada' 'Western Europe'\n",
      " 'Western US']\n",
      "Market ['Africa' 'Asia Pacific' 'Europe' 'LATAM' 'USCA']\n",
      "Product_ID ['FUR-BO-3174' 'FUR-BO-3175' 'FUR-BO-3176' 'FUR-BO-3177' 'FUR-BO-3409'] ... ['TEC-PH-6365' 'TEC-PH-6413' 'TEC-PH-6425' 'TEC-PH-6427' 'TEC-PH-6626']\n",
      "Product_Name ['\"While you Were Out\" Message Book, One Form per Page'\n",
      " '#10 Gummed Flap White Envelopes, 100/Box'\n",
      " '#10 Self-Seal White Envelopes'\n",
      " '#10 White Business Envelopes,4 1/8 x 9 1/2'\n",
      " '#10- 4 1/8\" x 9 1/2\" Recycled Envelopes'] ... ['iKross Bluetooth Portable Keyboard + Cell Phone Stand Holder + Brush for Apple iPhone 5S 5C 5, 4S 4'\n",
      " 'iOttie HLCRIO102 Car Mount' 'iOttie XL Car Mount'\n",
      " 'invisibleSHIELD by ZAGG Smudge-Free Screen Protector'\n",
      " 'netTALK DUO VoIP Telephone Service']\n",
      "SubCategory ['Accessories' 'Appliances' 'Art' 'Binders' 'Bookcases'] ... ['Paper' 'Phones' 'Storage' 'Supplies' 'Tables']\n",
      "Category ['Furniture' 'Office Supplies' 'Technology']\n",
      "Sales [0.444 0.556 0.836 0.852 0.876] ... [10499.97  11199.968 13999.96  17499.95  22638.48 ]\n",
      "Quantity [1 2 3 4 5] ... [10 11 12 13 14]\n",
      "Discount [0.    0.002 0.07  0.1   0.15 ] ... [0.602 0.65  0.7   0.8   0.85 ]\n",
      "Profit [-6599.978  -4088.376  -3839.9904 -3701.8928 -3399.98  ] ... [4630.4755 4946.37   5039.9856 6719.9808 8399.976 ]\n",
      "Shipping_Cost [1.002 1.003 1.01  1.019 1.02 ] ... [903.04 910.16 915.49 923.63 933.57]\n",
      "Order_Priority ['Critical' 'High' 'Low' 'Medium']\n",
      "Returned [False  True]\n",
      "Person ['Andile\\xa0Ihejirika' 'Angela Jephson' 'Cansu Peynirci'\n",
      " 'Chandrakant Chaudhri' 'Derrick Snyders'] ... ['Nora Cuijper' 'Oxana Lagunov' 'Preecha Metharom' 'Vasco Magalhães'\n",
      " 'Wasswa\\xa0Ahmed']\n"
     ]
    }
   ],
   "source": [
    "for key in orders.columns:\n",
    "    #print(key + \":\",  orders[key].unique())\n",
    "    if len(orders[key].unique()) <= 10:\n",
    "        print(key, orders[key].sort_values().unique())\n",
    "    else:\n",
    "        print(key, orders[key].sort_values().unique()[:5], \"...\", orders[key].sort_values().unique()[-5:])\n",
    "    #break"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# CustomerID (1)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Extract RegionID From CustomerID"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>Row_ID</th>\n",
       "      <th>Order_ID</th>\n",
       "      <th>Order_Date</th>\n",
       "      <th>Ship_Date</th>\n",
       "      <th>Ship_Mode</th>\n",
       "      <th>Customer_ID</th>\n",
       "      <th>Customer_Name</th>\n",
       "      <th>Segment</th>\n",
       "      <th>Postal_Code</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>Country</th>\n",
       "      <th>Region</th>\n",
       "      <th>Market</th>\n",
       "      <th>Product_ID</th>\n",
       "      <th>Product_Name</th>\n",
       "      <th>SubCategory</th>\n",
       "      <th>Category</th>\n",
       "      <th>Sales</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>Discount</th>\n",
       "      <th>Profit</th>\n",
       "      <th>Shipping_Cost</th>\n",
       "      <th>Order_Priority</th>\n",
       "      <th>Returned</th>\n",
       "      <th>Person</th>\n",
       "      <th>Region_Number</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>41515</th>\n",
       "      <td>33312</td>\n",
       "      <td>CA-2016-AM10705140-42632</td>\n",
       "      <td>2016-09-19</td>\n",
       "      <td>2016-09-24</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>AM-10705140</td>\n",
       "      <td>Anne McFarland</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>36830.0</td>\n",
       "      <td>Auburn</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>United States</td>\n",
       "      <td>Southern US</td>\n",
       "      <td>USCA</td>\n",
       "      <td>OFF-ST-6289</td>\n",
       "      <td>Tennsco Double-Tier Lockers</td>\n",
       "      <td>Storage</td>\n",
       "      <td>Office Supplies</td>\n",
       "      <td>900.08</td>\n",
       "      <td>4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>117.0104</td>\n",
       "      <td>53.84</td>\n",
       "      <td>Medium</td>\n",
       "      <td>False</td>\n",
       "      <td>Flannery Newton</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41516</th>\n",
       "      <td>33310</td>\n",
       "      <td>CA-2016-AM10705140-42632</td>\n",
       "      <td>2016-09-19</td>\n",
       "      <td>2016-09-24</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>AM-10705140</td>\n",
       "      <td>Anne McFarland</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>36830.0</td>\n",
       "      <td>Auburn</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>United States</td>\n",
       "      <td>Southern US</td>\n",
       "      <td>USCA</td>\n",
       "      <td>FUR-CH-5431</td>\n",
       "      <td>Office Star - Professional Matrix Back Chair w...</td>\n",
       "      <td>Chairs</td>\n",
       "      <td>Furniture</td>\n",
       "      <td>350.98</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>84.2352</td>\n",
       "      <td>17.04</td>\n",
       "      <td>Medium</td>\n",
       "      <td>False</td>\n",
       "      <td>Flannery Newton</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Row_ID                  Order_ID Order_Date  Ship_Date       Ship_Mode  \\\n",
       "41515   33312  CA-2016-AM10705140-42632 2016-09-19 2016-09-24  Standard Class   \n",
       "41516   33310  CA-2016-AM10705140-42632 2016-09-19 2016-09-24  Standard Class   \n",
       "\n",
       "       Customer_ID   Customer_Name   Segment  Postal_Code    City    State  \\\n",
       "41515  AM-10705140  Anne McFarland  Consumer      36830.0  Auburn  Alabama   \n",
       "41516  AM-10705140  Anne McFarland  Consumer      36830.0  Auburn  Alabama   \n",
       "\n",
       "             Country       Region Market   Product_ID  \\\n",
       "41515  United States  Southern US   USCA  OFF-ST-6289   \n",
       "41516  United States  Southern US   USCA  FUR-CH-5431   \n",
       "\n",
       "                                            Product_Name SubCategory  \\\n",
       "41515                        Tennsco Double-Tier Lockers     Storage   \n",
       "41516  Office Star - Professional Matrix Back Chair w...      Chairs   \n",
       "\n",
       "              Category   Sales  Quantity  Discount    Profit  Shipping_Cost  \\\n",
       "41515  Office Supplies  900.08         4       0.0  117.0104          53.84   \n",
       "41516        Furniture  350.98         1       0.0   84.2352          17.04   \n",
       "\n",
       "      Order_Priority  Returned           Person  Region_Number  \n",
       "41515         Medium     False  Flannery Newton              8  \n",
       "41516         Medium     False  Flannery Newton              8  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>Row_ID</th>\n",
       "      <th>Order_ID</th>\n",
       "      <th>Order_Date</th>\n",
       "      <th>Ship_Date</th>\n",
       "      <th>Ship_Mode</th>\n",
       "      <th>Customer_ID</th>\n",
       "      <th>Customer_Name</th>\n",
       "      <th>Segment</th>\n",
       "      <th>Postal_Code</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>Country</th>\n",
       "      <th>Region</th>\n",
       "      <th>Market</th>\n",
       "      <th>Product_ID</th>\n",
       "      <th>Product_Name</th>\n",
       "      <th>SubCategory</th>\n",
       "      <th>Category</th>\n",
       "      <th>Sales</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>Discount</th>\n",
       "      <th>Profit</th>\n",
       "      <th>Shipping_Cost</th>\n",
       "      <th>Order_Priority</th>\n",
       "      <th>Returned</th>\n",
       "      <th>Person</th>\n",
       "      <th>Region_Number</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>24599</td>\n",
       "      <td>IN-2017-CA120551-42816</td>\n",
       "      <td>2017-03-22</td>\n",
       "      <td>2017-03-29</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>CA-120551</td>\n",
       "      <td>Cathy Armstrong</td>\n",
       "      <td>Home Office</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Herat</td>\n",
       "      <td>Hirat</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Southern Asia</td>\n",
       "      <td>Asia Pacific</td>\n",
       "      <td>FUR-BO-4861</td>\n",
       "      <td>Ikea Library with Doors, Mobile</td>\n",
       "      <td>Bookcases</td>\n",
       "      <td>Furniture</td>\n",
       "      <td>731.82</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>102.42</td>\n",
       "      <td>39.66</td>\n",
       "      <td>Medium</td>\n",
       "      <td>True</td>\n",
       "      <td>Chandrakant Chaudhri</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>24598</td>\n",
       "      <td>IN-2017-CA120551-42816</td>\n",
       "      <td>2017-03-22</td>\n",
       "      <td>2017-03-29</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>CA-120551</td>\n",
       "      <td>Cathy Armstrong</td>\n",
       "      <td>Home Office</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Herat</td>\n",
       "      <td>Hirat</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Southern Asia</td>\n",
       "      <td>Asia Pacific</td>\n",
       "      <td>TEC-MA-4211</td>\n",
       "      <td>Epson Receipt Printer, White</td>\n",
       "      <td>Machines</td>\n",
       "      <td>Technology</td>\n",
       "      <td>346.32</td>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13.77</td>\n",
       "      <td>14.10</td>\n",
       "      <td>Medium</td>\n",
       "      <td>True</td>\n",
       "      <td>Chandrakant Chaudhri</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Row_ID                Order_ID Order_Date  Ship_Date       Ship_Mode  \\\n",
       "0   24599  IN-2017-CA120551-42816 2017-03-22 2017-03-29  Standard Class   \n",
       "1   24598  IN-2017-CA120551-42816 2017-03-22 2017-03-29  Standard Class   \n",
       "\n",
       "  Customer_ID    Customer_Name      Segment  Postal_Code   City  State  \\\n",
       "0   CA-120551  Cathy Armstrong  Home Office          NaN  Herat  Hirat   \n",
       "1   CA-120551  Cathy Armstrong  Home Office          NaN  Herat  Hirat   \n",
       "\n",
       "       Country         Region        Market   Product_ID  \\\n",
       "0  Afghanistan  Southern Asia  Asia Pacific  FUR-BO-4861   \n",
       "1  Afghanistan  Southern Asia  Asia Pacific  TEC-MA-4211   \n",
       "\n",
       "                      Product_Name SubCategory    Category   Sales  Quantity  \\\n",
       "0  Ikea Library with Doors, Mobile   Bookcases   Furniture  731.82         2   \n",
       "1     Epson Receipt Printer, White    Machines  Technology  346.32         3   \n",
       "\n",
       "   Discount  Profit  Shipping_Cost Order_Priority  Returned  \\\n",
       "0       0.0  102.42          39.66         Medium      True   \n",
       "1       0.0   13.77          14.10         Medium      True   \n",
       "\n",
       "                 Person  Region_Number  \n",
       "0  Chandrakant Chaudhri              0  \n",
       "1  Chandrakant Chaudhri              0  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "4    3244\n",
      "6    2876\n",
      "2    2333\n",
      "8    1625\n",
      "Name: Region_Number, dtype: int64\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Coding\\AppData\\Local\\Temp\\ipykernel_11108\\3559798285.py:11: UserWarning: The sum() of all RegionIDs should be 47506\n",
      "  warnings.warn(\"The sum() of all RegionIDs should be 47506\")\n"
     ]
    }
   ],
   "source": [
    "import warnings\n",
    "\n",
    "# Let's remove the last digit if the Country is \"United States\"\n",
    "orders['Region_Number'] = orders.apply(lambda row: int(row['Customer_ID'][-1]) if row['Country'] == 'United States' else 0, axis=1)\n",
    "orders['Customer_ID'] = orders.apply(lambda row: row['Customer_ID'][:-1] if row['Country'] == 'United States' else row['Customer_ID'], axis=1)\n",
    "display(orders[orders[\"Country\"] == \"United States\"].head(2))\n",
    "display(orders[orders[\"Country\"] != \"United States\"].head(2))\n",
    "print(orders[orders[\"Country\"] == \"United States\"][\"Region_Number\"].value_counts())\n",
    "\n",
    "if orders[orders[\"Country\"] == \"United States\"][\"Region_Number\"].sum() != 47506:\n",
    "    warnings.warn(\"The sum() of all RegionIDs should be 47506\")\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# OrderID"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "check = True\n",
    "for date, data in orders.groupby(\"Order_Date\"):\n",
    "    dates = (data['Order_ID'].str[-5:].drop_duplicates().astype(\"int\").values)\n",
    "    \n",
    "    if (len(dates) > 1):\n",
    "        print(date, dates)\n",
    "        break\n",
    "\n",
    "    new_date = datetime.datetime(1899, 12, 30) + datetime.timedelta(days=int(dates[0]))\n",
    "    if str(date) != str(new_date):\n",
    "        print(date, \"!=\", date.strftime('%Y-%m-%m 00:00:00'))\n",
    "        check = False\n",
    "        break\n",
    "\n",
    "if check == True:\n",
    "    orders['Order_ID'] = orders['Order_ID'].str.replace(r'-(\\d{5})$', '', regex=True)\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Delete CustomerID from OrderID"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>Row_ID</th>\n",
       "      <th>Order_ID</th>\n",
       "      <th>Order_Date</th>\n",
       "      <th>Ship_Date</th>\n",
       "      <th>Ship_Mode</th>\n",
       "      <th>Customer_ID</th>\n",
       "      <th>Customer_Name</th>\n",
       "      <th>Segment</th>\n",
       "      <th>Postal_Code</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>Country</th>\n",
       "      <th>Region</th>\n",
       "      <th>Market</th>\n",
       "      <th>Product_ID</th>\n",
       "      <th>Product_Name</th>\n",
       "      <th>SubCategory</th>\n",
       "      <th>Category</th>\n",
       "      <th>Sales</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>Discount</th>\n",
       "      <th>Profit</th>\n",
       "      <th>Shipping_Cost</th>\n",
       "      <th>Order_Priority</th>\n",
       "      <th>Returned</th>\n",
       "      <th>Person</th>\n",
       "      <th>Region_Number</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>51588</th>\n",
       "      <td>40095</td>\n",
       "      <td>US-2016-VM21685140</td>\n",
       "      <td>2016-04-07</td>\n",
       "      <td>2016-04-11</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>VM-21685140</td>\n",
       "      <td>Valerie Mitchum</td>\n",
       "      <td>Home Office</td>\n",
       "      <td>5408.0</td>\n",
       "      <td>Burlington</td>\n",
       "      <td>Vermont</td>\n",
       "      <td>United States</td>\n",
       "      <td>Eastern US</td>\n",
       "      <td>USCA</td>\n",
       "      <td>TEC-PH-5364</td>\n",
       "      <td>Nortel Meridian M5316 Digital phone</td>\n",
       "      <td>Phones</td>\n",
       "      <td>Technology</td>\n",
       "      <td>1294.750</td>\n",
       "      <td>5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>336.6350</td>\n",
       "      <td>214.54</td>\n",
       "      <td>High</td>\n",
       "      <td>False</td>\n",
       "      <td>Dolores Davis</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51589</th>\n",
       "      <td>39193</td>\n",
       "      <td>CA-2017-EH14125140</td>\n",
       "      <td>2017-06-22</td>\n",
       "      <td>2017-06-26</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>EH-14125140</td>\n",
       "      <td>Eugene Hildebrand</td>\n",
       "      <td>Home Office</td>\n",
       "      <td>26003.0</td>\n",
       "      <td>Wheeling</td>\n",
       "      <td>West Virginia</td>\n",
       "      <td>United States</td>\n",
       "      <td>Eastern US</td>\n",
       "      <td>USCA</td>\n",
       "      <td>OFF-PA-6459</td>\n",
       "      <td>Xerox 1908</td>\n",
       "      <td>Paper</td>\n",
       "      <td>Office Supplies</td>\n",
       "      <td>447.840</td>\n",
       "      <td>8</td>\n",
       "      <td>0.0</td>\n",
       "      <td>219.4416</td>\n",
       "      <td>32.88</td>\n",
       "      <td>Medium</td>\n",
       "      <td>False</td>\n",
       "      <td>Dolores Davis</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51590</th>\n",
       "      <td>39191</td>\n",
       "      <td>CA-2017-EH14125140</td>\n",
       "      <td>2017-06-22</td>\n",
       "      <td>2017-06-26</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>EH-14125140</td>\n",
       "      <td>Eugene Hildebrand</td>\n",
       "      <td>Home Office</td>\n",
       "      <td>26003.0</td>\n",
       "      <td>Wheeling</td>\n",
       "      <td>West Virginia</td>\n",
       "      <td>United States</td>\n",
       "      <td>Eastern US</td>\n",
       "      <td>USCA</td>\n",
       "      <td>OFF-BI-4837</td>\n",
       "      <td>Ibico Standard Transparent Covers</td>\n",
       "      <td>Binders</td>\n",
       "      <td>Office Supplies</td>\n",
       "      <td>82.400</td>\n",
       "      <td>5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40.3760</td>\n",
       "      <td>7.22</td>\n",
       "      <td>Medium</td>\n",
       "      <td>False</td>\n",
       "      <td>Dolores Davis</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51591</th>\n",
       "      <td>39192</td>\n",
       "      <td>CA-2017-EH14125140</td>\n",
       "      <td>2017-06-22</td>\n",
       "      <td>2017-06-26</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>EH-14125140</td>\n",
       "      <td>Eugene Hildebrand</td>\n",
       "      <td>Home Office</td>\n",
       "      <td>26003.0</td>\n",
       "      <td>Wheeling</td>\n",
       "      <td>West Virginia</td>\n",
       "      <td>United States</td>\n",
       "      <td>Eastern US</td>\n",
       "      <td>USCA</td>\n",
       "      <td>OFF-BI-6634</td>\n",
       "      <td>Zipper Ring Binder Pockets</td>\n",
       "      <td>Binders</td>\n",
       "      <td>Office Supplies</td>\n",
       "      <td>6.240</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0576</td>\n",
       "      <td>1.49</td>\n",
       "      <td>Medium</td>\n",
       "      <td>False</td>\n",
       "      <td>Dolores Davis</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51592</th>\n",
       "      <td>40783</td>\n",
       "      <td>CA-2017-NF18385140</td>\n",
       "      <td>2017-10-13</td>\n",
       "      <td>2017-10-13</td>\n",
       "      <td>Same Day</td>\n",
       "      <td>NF-18385140</td>\n",
       "      <td>Natalie Fritzler</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>26003.0</td>\n",
       "      <td>Wheeling</td>\n",
       "      <td>West Virginia</td>\n",
       "      <td>United States</td>\n",
       "      <td>Eastern US</td>\n",
       "      <td>USCA</td>\n",
       "      <td>FUR-TA-3751</td>\n",
       "      <td>Chromcraft 48\" x 96\" Racetrack Double Pedestal...</td>\n",
       "      <td>Tables</td>\n",
       "      <td>Furniture</td>\n",
       "      <td>673.344</td>\n",
       "      <td>3</td>\n",
       "      <td>0.3</td>\n",
       "      <td>-76.9536</td>\n",
       "      <td>56.90</td>\n",
       "      <td>Medium</td>\n",
       "      <td>True</td>\n",
       "      <td>Dolores Davis</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Row_ID            Order_ID Order_Date  Ship_Date       Ship_Mode  \\\n",
       "51588   40095  US-2016-VM21685140 2016-04-07 2016-04-11  Standard Class   \n",
       "51589   39193  CA-2017-EH14125140 2017-06-22 2017-06-26  Standard Class   \n",
       "51590   39191  CA-2017-EH14125140 2017-06-22 2017-06-26  Standard Class   \n",
       "51591   39192  CA-2017-EH14125140 2017-06-22 2017-06-26  Standard Class   \n",
       "51592   40783  CA-2017-NF18385140 2017-10-13 2017-10-13        Same Day   \n",
       "\n",
       "       Customer_ID      Customer_Name      Segment  Postal_Code        City  \\\n",
       "51588  VM-21685140    Valerie Mitchum  Home Office       5408.0  Burlington   \n",
       "51589  EH-14125140  Eugene Hildebrand  Home Office      26003.0    Wheeling   \n",
       "51590  EH-14125140  Eugene Hildebrand  Home Office      26003.0    Wheeling   \n",
       "51591  EH-14125140  Eugene Hildebrand  Home Office      26003.0    Wheeling   \n",
       "51592  NF-18385140   Natalie Fritzler     Consumer      26003.0    Wheeling   \n",
       "\n",
       "               State        Country      Region Market   Product_ID  \\\n",
       "51588        Vermont  United States  Eastern US   USCA  TEC-PH-5364   \n",
       "51589  West Virginia  United States  Eastern US   USCA  OFF-PA-6459   \n",
       "51590  West Virginia  United States  Eastern US   USCA  OFF-BI-4837   \n",
       "51591  West Virginia  United States  Eastern US   USCA  OFF-BI-6634   \n",
       "51592  West Virginia  United States  Eastern US   USCA  FUR-TA-3751   \n",
       "\n",
       "                                            Product_Name SubCategory  \\\n",
       "51588                Nortel Meridian M5316 Digital phone      Phones   \n",
       "51589                                         Xerox 1908       Paper   \n",
       "51590                  Ibico Standard Transparent Covers     Binders   \n",
       "51591                         Zipper Ring Binder Pockets     Binders   \n",
       "51592  Chromcraft 48\" x 96\" Racetrack Double Pedestal...      Tables   \n",
       "\n",
       "              Category     Sales  Quantity  Discount    Profit  Shipping_Cost  \\\n",
       "51588       Technology  1294.750         5       0.0  336.6350         214.54   \n",
       "51589  Office Supplies   447.840         8       0.0  219.4416          32.88   \n",
       "51590  Office Supplies    82.400         5       0.0   40.3760           7.22   \n",
       "51591  Office Supplies     6.240         2       0.0    3.0576           1.49   \n",
       "51592        Furniture   673.344         3       0.3  -76.9536          56.90   \n",
       "\n",
       "      Order_Priority  Returned         Person  Region_Number  \n",
       "51588           High     False  Dolores Davis              6  \n",
       "51589         Medium     False  Dolores Davis              6  \n",
       "51590         Medium     False  Dolores Davis              6  \n",
       "51591         Medium     False  Dolores Davis              6  \n",
       "51592         Medium      True  Dolores Davis              6  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orders.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "customer_ids = orders['Customer_ID'].str.replace('-', '')\n",
    "if (orders['Order_ID'].str.endswith(customer_ids)).all():\n",
    "    for index, row in orders.iterrows():\n",
    "        customer_id = \"-\" + row['Customer_ID'].replace('-', '')\n",
    "        if row['Order_ID'].endswith(customer_id):\n",
    "            orders.loc[index, 'Order_ID'] = row['Order_ID'][:-len(customer_id)]\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "orders['Order_ID'] = orders.apply(lambda row: row['Order_ID'][:-5] if row['Order_ID'].endswith(str(row['Order_Date'].year)) else row['Order_ID'], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "orders = orders.rename(columns={'Order_ID': 'Order_TwoLC'})"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Customer ID (2)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Extract Initials from CustomerID"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Let's generate the Initials on our own\n",
    "# We need to take extra care for Names with more than words or just one word  \n",
    "orders['Initials'] = orders['Customer_Name'].apply(lambda name: str((''.join(word[:1] for word in name.split()) if len(name.split()) > 1 else name)[0:2]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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>Row_ID</th>\n",
       "      <th>Order_TwoLC</th>\n",
       "      <th>Order_Date</th>\n",
       "      <th>Ship_Date</th>\n",
       "      <th>Ship_Mode</th>\n",
       "      <th>Customer_ID</th>\n",
       "      <th>Customer_Name</th>\n",
       "      <th>Segment</th>\n",
       "      <th>Postal_Code</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>Country</th>\n",
       "      <th>Region</th>\n",
       "      <th>Market</th>\n",
       "      <th>Product_ID</th>\n",
       "      <th>Product_Name</th>\n",
       "      <th>SubCategory</th>\n",
       "      <th>Category</th>\n",
       "      <th>Sales</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>Discount</th>\n",
       "      <th>Profit</th>\n",
       "      <th>Shipping_Cost</th>\n",
       "      <th>Order_Priority</th>\n",
       "      <th>Returned</th>\n",
       "      <th>Person</th>\n",
       "      <th>Region_Number</th>\n",
       "      <th>Initials</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>26466</th>\n",
       "      <td>33</td>\n",
       "      <td>US</td>\n",
       "      <td>2016-05-24</td>\n",
       "      <td>2016-05-31</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>SC-2005055</td>\n",
       "      <td>Kai Rey</td>\n",
       "      <td>Home Office</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Tegucigalpa</td>\n",
       "      <td>Francisco Morazán</td>\n",
       "      <td>Honduras</td>\n",
       "      <td>Central America</td>\n",
       "      <td>LATAM</td>\n",
       "      <td>TEC-CO-5998</td>\n",
       "      <td>Sharp Fax Machine, Digital</td>\n",
       "      <td>Copiers</td>\n",
       "      <td>Technology</td>\n",
       "      <td>941.77824</td>\n",
       "      <td>8</td>\n",
       "      <td>0.402</td>\n",
       "      <td>-601.74176</td>\n",
       "      <td>80.281</td>\n",
       "      <td>Medium</td>\n",
       "      <td>False</td>\n",
       "      <td>Nicodemo Bautista</td>\n",
       "      <td>0</td>\n",
       "      <td>KR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26467</th>\n",
       "      <td>32</td>\n",
       "      <td>US</td>\n",
       "      <td>2016-05-24</td>\n",
       "      <td>2016-05-31</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>SC-2005055</td>\n",
       "      <td>Kai Rey</td>\n",
       "      <td>Home Office</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Tegucigalpa</td>\n",
       "      <td>Francisco Morazán</td>\n",
       "      <td>Honduras</td>\n",
       "      <td>Central America</td>\n",
       "      <td>LATAM</td>\n",
       "      <td>OFF-AP-3582</td>\n",
       "      <td>Breville Toaster, Black</td>\n",
       "      <td>Appliances</td>\n",
       "      <td>Office Supplies</td>\n",
       "      <td>152.28000</td>\n",
       "      <td>5</td>\n",
       "      <td>0.400</td>\n",
       "      <td>-99.02000</td>\n",
       "      <td>16.245</td>\n",
       "      <td>Medium</td>\n",
       "      <td>False</td>\n",
       "      <td>Nicodemo Bautista</td>\n",
       "      <td>0</td>\n",
       "      <td>KR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26468</th>\n",
       "      <td>28</td>\n",
       "      <td>US</td>\n",
       "      <td>2016-05-24</td>\n",
       "      <td>2016-05-31</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>SC-2005055</td>\n",
       "      <td>Kai Rey</td>\n",
       "      <td>Home Office</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Tegucigalpa</td>\n",
       "      <td>Francisco Morazán</td>\n",
       "      <td>Honduras</td>\n",
       "      <td>Central America</td>\n",
       "      <td>LATAM</td>\n",
       "      <td>OFF-BI-6383</td>\n",
       "      <td>Wilson Jones Binding Machine, Durable</td>\n",
       "      <td>Binders</td>\n",
       "      <td>Office Supplies</td>\n",
       "      <td>141.28800</td>\n",
       "      <td>7</td>\n",
       "      <td>0.400</td>\n",
       "      <td>-73.05200</td>\n",
       "      <td>9.465</td>\n",
       "      <td>Medium</td>\n",
       "      <td>False</td>\n",
       "      <td>Nicodemo Bautista</td>\n",
       "      <td>0</td>\n",
       "      <td>KR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26469</th>\n",
       "      <td>30</td>\n",
       "      <td>US</td>\n",
       "      <td>2016-05-24</td>\n",
       "      <td>2016-05-31</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>SC-2005055</td>\n",
       "      <td>Kai Rey</td>\n",
       "      <td>Home Office</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Tegucigalpa</td>\n",
       "      <td>Francisco Morazán</td>\n",
       "      <td>Honduras</td>\n",
       "      <td>Central America</td>\n",
       "      <td>LATAM</td>\n",
       "      <td>TEC-PH-5262</td>\n",
       "      <td>Motorola Office Telephone, with Caller ID</td>\n",
       "      <td>Phones</td>\n",
       "      <td>Technology</td>\n",
       "      <td>114.81600</td>\n",
       "      <td>4</td>\n",
       "      <td>0.400</td>\n",
       "      <td>-49.82400</td>\n",
       "      <td>5.923</td>\n",
       "      <td>Medium</td>\n",
       "      <td>False</td>\n",
       "      <td>Nicodemo Bautista</td>\n",
       "      <td>0</td>\n",
       "      <td>KR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26470</th>\n",
       "      <td>34</td>\n",
       "      <td>US</td>\n",
       "      <td>2016-05-24</td>\n",
       "      <td>2016-05-31</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>SC-2005055</td>\n",
       "      <td>Kai Rey</td>\n",
       "      <td>Home Office</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Tegucigalpa</td>\n",
       "      <td>Francisco Morazán</td>\n",
       "      <td>Honduras</td>\n",
       "      <td>Central America</td>\n",
       "      <td>LATAM</td>\n",
       "      <td>OFF-AR-6118</td>\n",
       "      <td>Stanley Pencil Sharpener, Easy-Erase</td>\n",
       "      <td>Art</td>\n",
       "      <td>Office Supplies</td>\n",
       "      <td>10.41600</td>\n",
       "      <td>1</td>\n",
       "      <td>0.400</td>\n",
       "      <td>-0.70400</td>\n",
       "      <td>1.931</td>\n",
       "      <td>Medium</td>\n",
       "      <td>False</td>\n",
       "      <td>Nicodemo Bautista</td>\n",
       "      <td>0</td>\n",
       "      <td>KR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26471</th>\n",
       "      <td>31</td>\n",
       "      <td>US</td>\n",
       "      <td>2016-05-24</td>\n",
       "      <td>2016-05-31</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>SC-2005055</td>\n",
       "      <td>Kai Rey</td>\n",
       "      <td>Home Office</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Tegucigalpa</td>\n",
       "      <td>Francisco Morazán</td>\n",
       "      <td>Honduras</td>\n",
       "      <td>Central America</td>\n",
       "      <td>LATAM</td>\n",
       "      <td>OFF-AR-3488</td>\n",
       "      <td>Binney &amp; Smith Markers, Blue</td>\n",
       "      <td>Art</td>\n",
       "      <td>Office Supplies</td>\n",
       "      <td>19.27200</td>\n",
       "      <td>2</td>\n",
       "      <td>0.400</td>\n",
       "      <td>2.87200</td>\n",
       "      <td>1.843</td>\n",
       "      <td>Medium</td>\n",
       "      <td>False</td>\n",
       "      <td>Nicodemo Bautista</td>\n",
       "      <td>0</td>\n",
       "      <td>KR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26472</th>\n",
       "      <td>29</td>\n",
       "      <td>US</td>\n",
       "      <td>2016-05-24</td>\n",
       "      <td>2016-05-31</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>SC-2005055</td>\n",
       "      <td>Kai Rey</td>\n",
       "      <td>Home Office</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Tegucigalpa</td>\n",
       "      <td>Francisco Morazán</td>\n",
       "      <td>Honduras</td>\n",
       "      <td>Central America</td>\n",
       "      <td>LATAM</td>\n",
       "      <td>OFF-FA-6208</td>\n",
       "      <td>Stockwell Thumb Tacks, Metal</td>\n",
       "      <td>Fasteners</td>\n",
       "      <td>Office Supplies</td>\n",
       "      <td>21.84000</td>\n",
       "      <td>4</td>\n",
       "      <td>0.400</td>\n",
       "      <td>-3.28000</td>\n",
       "      <td>1.091</td>\n",
       "      <td>Medium</td>\n",
       "      <td>False</td>\n",
       "      <td>Nicodemo Bautista</td>\n",
       "      <td>0</td>\n",
       "      <td>KR</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Row_ID Order_TwoLC Order_Date  Ship_Date       Ship_Mode Customer_ID  \\\n",
       "26466      33          US 2016-05-24 2016-05-31  Standard Class  SC-2005055   \n",
       "26467      32          US 2016-05-24 2016-05-31  Standard Class  SC-2005055   \n",
       "26468      28          US 2016-05-24 2016-05-31  Standard Class  SC-2005055   \n",
       "26469      30          US 2016-05-24 2016-05-31  Standard Class  SC-2005055   \n",
       "26470      34          US 2016-05-24 2016-05-31  Standard Class  SC-2005055   \n",
       "26471      31          US 2016-05-24 2016-05-31  Standard Class  SC-2005055   \n",
       "26472      29          US 2016-05-24 2016-05-31  Standard Class  SC-2005055   \n",
       "\n",
       "      Customer_Name      Segment  Postal_Code         City              State  \\\n",
       "26466       Kai Rey  Home Office          NaN  Tegucigalpa  Francisco Morazán   \n",
       "26467       Kai Rey  Home Office          NaN  Tegucigalpa  Francisco Morazán   \n",
       "26468       Kai Rey  Home Office          NaN  Tegucigalpa  Francisco Morazán   \n",
       "26469       Kai Rey  Home Office          NaN  Tegucigalpa  Francisco Morazán   \n",
       "26470       Kai Rey  Home Office          NaN  Tegucigalpa  Francisco Morazán   \n",
       "26471       Kai Rey  Home Office          NaN  Tegucigalpa  Francisco Morazán   \n",
       "26472       Kai Rey  Home Office          NaN  Tegucigalpa  Francisco Morazán   \n",
       "\n",
       "        Country           Region Market   Product_ID  \\\n",
       "26466  Honduras  Central America  LATAM  TEC-CO-5998   \n",
       "26467  Honduras  Central America  LATAM  OFF-AP-3582   \n",
       "26468  Honduras  Central America  LATAM  OFF-BI-6383   \n",
       "26469  Honduras  Central America  LATAM  TEC-PH-5262   \n",
       "26470  Honduras  Central America  LATAM  OFF-AR-6118   \n",
       "26471  Honduras  Central America  LATAM  OFF-AR-3488   \n",
       "26472  Honduras  Central America  LATAM  OFF-FA-6208   \n",
       "\n",
       "                                    Product_Name SubCategory         Category  \\\n",
       "26466                 Sharp Fax Machine, Digital     Copiers       Technology   \n",
       "26467                    Breville Toaster, Black  Appliances  Office Supplies   \n",
       "26468      Wilson Jones Binding Machine, Durable     Binders  Office Supplies   \n",
       "26469  Motorola Office Telephone, with Caller ID      Phones       Technology   \n",
       "26470       Stanley Pencil Sharpener, Easy-Erase         Art  Office Supplies   \n",
       "26471               Binney & Smith Markers, Blue         Art  Office Supplies   \n",
       "26472               Stockwell Thumb Tacks, Metal   Fasteners  Office Supplies   \n",
       "\n",
       "           Sales  Quantity  Discount     Profit  Shipping_Cost Order_Priority  \\\n",
       "26466  941.77824         8     0.402 -601.74176         80.281         Medium   \n",
       "26467  152.28000         5     0.400  -99.02000         16.245         Medium   \n",
       "26468  141.28800         7     0.400  -73.05200          9.465         Medium   \n",
       "26469  114.81600         4     0.400  -49.82400          5.923         Medium   \n",
       "26470   10.41600         1     0.400   -0.70400          1.931         Medium   \n",
       "26471   19.27200         2     0.400    2.87200          1.843         Medium   \n",
       "26472   21.84000         4     0.400   -3.28000          1.091         Medium   \n",
       "\n",
       "       Returned             Person  Region_Number Initials  \n",
       "26466     False  Nicodemo Bautista              0       KR  \n",
       "26467     False  Nicodemo Bautista              0       KR  \n",
       "26468     False  Nicodemo Bautista              0       KR  \n",
       "26469     False  Nicodemo Bautista              0       KR  \n",
       "26470     False  Nicodemo Bautista              0       KR  \n",
       "26471     False  Nicodemo Bautista              0       KR  \n",
       "26472     False  Nicodemo Bautista              0       KR  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Check if Customer ID contains our generated Initials\n",
    "filtered_df = orders[orders.apply(lambda row: not row['Customer_ID'].startswith(row['Initials']), axis=1)]\n",
    "display(filtered_df)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### There are multiple things suspicious with Kai Rey\n",
    "* The RowIDs are very low\n",
    "* The Customer_ID contains a SC instead of his initials KR\n",
    "* There is another Customer with the same Customer ID named \"Sample Company A\" - That is where the initials SC comes from"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Coding\\AppData\\Local\\Temp\\ipykernel_11108\\1550134358.py:1: FutureWarning: The default value of regex will change from True to False in a future version.\n",
      "  orders.loc[orders['Customer_ID'].isin(filtered_df['Customer_ID']), 'Customer_ID'] = orders['Customer_ID'].str.replace(r'^SC-', 'KR-')\n"
     ]
    }
   ],
   "source": [
    "orders.loc[orders['Customer_ID'].isin(filtered_df['Customer_ID']), 'Customer_ID'] = orders['Customer_ID'].str.replace(r'^SC-', 'KR-')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "orders['Customer_ID'] = orders.apply(lambda row: row['Customer_ID'][3:] if row['Customer_ID'].startswith(row['Initials']) else row['Customer_ID'], axis=1)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Extract CountyCode from CustomerID"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    }
   ],
   "source": [
    "import re\n",
    "\n",
    "prev_id = 0\n",
    "out = \"\"\n",
    "\n",
    "matching = []\n",
    "manual_aliases = {\n",
    "    \"Belize\": 82, # Matches Mexico (!)\n",
    "    \"Bhutan\": 58, # Matches India (!)\n",
    "    \"Botswana\": 117, # Matches South Africa (!)\n",
    "    \"Burkina Faso\": 111, # Matches Senegal (!)\n",
    "    \"Costa Rica\": 28, # Matches Mexico (!)\n",
    "    \"Cyprus\": 64, # Matches Italy (!!!)\n",
    "    \"French Guiana\": 18, # Matches Brazil (!)\n",
    "    \"Guyana\": 54, # Matches Brazil (!)\n",
    "    \"Kuwait\": 110, # Matches Saudi Arabia (!)\n",
    "    \"Laos\": 144, # Matches Laos (!)\n",
    "    \"Luxembourg\": 48, # Matches Germany (!)\n",
    "    \"Malawi\": 87, # Matches Mozambique (!)\n",
    "    \"Oman\": 110, # Matches Saudi Arabia (!)\n",
    "    \"Serbia\": 19, # Matches Bulgaria (!)\n",
    "    \"Suriname\": 18, # Matches Brazil (!)\n",
    "    \"The Gambia\": 111, # Matches Senegal (!)\n",
    "\n",
    "}\n",
    "manual_matching = {\n",
    "    \"Bahrain\": 10,\n",
    "    \"Burundi\": 20,\n",
    "    \"Chad\": 25,\n",
    "    \"Equatorial Guinea\": 40,\n",
    "    \"Eritrea\": 41,\n",
    "    \"Greece\": 148, # No zf found(!!!!!) / Must be added very late since it has the highest CountryID\n",
    "    \"Guadeloupe\": 50,\n",
    "    \"Montenegro\": 85,\n",
    "    \"Republic of the Congo\": 106,\n",
    "    \"South Sudan\": 119,\n",
    "    \"Tajikistan\": 128, # [128]\n",
    "    \"Western Sahara\": 29, # No clue why western sahara has such a low CountryID\n",
    "}\n",
    "\n",
    "for country, data in orders.groupby(\"Country\"):\n",
    "    zf_found = False\n",
    "    for i in reversed(range(1,5)):\n",
    "\n",
    "        ids = (data['Customer_ID'].str[-i:].drop_duplicates().astype(\"int\").values)\n",
    "        #print(i, prev_id, ids)\n",
    "\n",
    "        # lets see if we can find the \"0\" and \"5\" before each countryID\n",
    "        if (len(ids) == 2) and (abs(ids[0]-ids[-1]) in [50, 500, 5000]):\n",
    "            zf_found = True\n",
    "\n",
    "        if (zf_found == True) & (len(ids) == 1) & (int(ids[0]) not in matching):\n",
    "            prev_id = int(ids[0])\n",
    "            #print(\"Found CountyID for\", country, \"=\", prev_id)\n",
    "            # TODO matching[country] = prev_id\n",
    "            matching.append({'Name': country, 'ID': prev_id, 'Alias': 0})\n",
    "            #display(data[[\"Row ID\", \"Customer ID\", \"Country\"]].head())\n",
    "            orders.loc[orders['Country'] == country, 'Country_ID'] = prev_id\n",
    "            orders.loc[orders['Country'] == country, 'CountryAlias_ID'] = 0\n",
    "            orders.loc[orders['Country'] == country, 'Customer_ID'] = orders.loc[orders['Country'] == country, 'Customer_ID'].str[:-i]\n",
    "            break\n",
    "\n",
    "    if country in manual_matching:\n",
    "        #print(\"Manual CountyID for\", country, \"=\", manual_matching[country])\n",
    "        # TODO matching[country] = manual_matching[country]\n",
    "        matching.append({'Name': country, 'ID': prev_id, 'Alias': 0})\n",
    "        orders.loc[orders['Country'] == country, 'Country_ID'] = prev_id\n",
    "        orders.loc[orders['Country'] == country, 'CountryAlias_ID'] = 0\n",
    "        orders.loc[orders['Country'] == country, 'Customer_ID'] = orders.loc[orders['Country'] == country, 'Customer_ID'].str[:-len(str(manual_matching[country]))]\n",
    "    elif country in manual_aliases:\n",
    "        #print(\"Manual AliasID for\", country, \"=\", manual_aliases[country])\n",
    "        # TODO matching[country] = manual_matching[country]\n",
    "        matching.append({'Name': country, 'ID': prev_id + 1000, 'Alias': manual_aliases[country]})\n",
    "        orders.loc[orders['Country'] == country, 'Country_ID'] = prev_id+1000\n",
    "        orders.loc[orders['Country'] == country, 'CountryAlias_ID'] = manual_aliases[country]\n",
    "        orders.loc[orders['Country'] == country, 'Customer_ID'] = orders.loc[orders['Country'] == country, 'Customer_ID'].str[:-len(str(manual_aliases[country]))]\n",
    "    elif zf_found == False:\n",
    "        print(\"Coudn't find a CountryID for\", country)\n",
    "        ids = (data['Customer_ID'].str[-4:].drop_duplicates().astype(\"int\").values)\n",
    "        print(ids, len(ids))\n",
    "        out = out + \"\\n\" + '\"' + country + '\": ' + str(prev_id + 1) + ', # ' + ids[0]\n",
    "print(out)\n",
    "#display(matching)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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>Name</th>\n",
       "      <th>ID</th>\n",
       "      <th>Alias</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Albania</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Algeria</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Angola</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Argentina</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Name  ID  Alias\n",
       "0  Afghanistan   1      0\n",
       "1      Albania   2      0\n",
       "2      Algeria   3      0\n",
       "3       Angola   4      0\n",
       "4    Argentina   5      0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "countries = pd.DataFrame(matching)\n",
    "display(countries.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    12055\n",
       "1    12055\n",
       "2    12055\n",
       "3    11605\n",
       "4    11605\n",
       "Name: Customer_ID, dtype: object"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(orders[\"Customer_ID\"].head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "WTF: Kai Rey 4 6225 [20050]\n"
     ]
    }
   ],
   "source": [
    "last_id = 0\n",
    "\n",
    "# TODO The reason why there a sometimes multiple ids (i.e.:AG-270 and AG-10270) has something to do with the row ID? \n",
    "# Somewhere over the ID 40.000 the ID in Customer ID changes by 10.000 \n",
    "\n",
    "for name, data in orders.groupby(\"Customer_Name\"):\n",
    "    \n",
    "    temp_id = last_id + 15\n",
    "\n",
    "    ids = (data['Customer_ID'].drop_duplicates().astype(\"int\").values)\n",
    "    ids = ids[~((ids == 88887) | (ids == 88888))]  # Just needed for \"Aleksandra Gannaway\" and \"Denny Joy\" in Greece\n",
    "    \n",
    "    if (len(ids) == 1) & (temp_id == ids[0]):\n",
    "        # print(\"Treffer:\", name, len(str(temp_id)), temp_id, ids)\n",
    "        last_id = temp_id\n",
    "    elif (len(ids) == 1) & (temp_id + 10000 == ids[0]):\n",
    "        # print(\"Treffer:\", name, len(str(temp_id)), temp_id, ids)\n",
    "        last_id = temp_id\n",
    "    elif name == \"Kai Rey\":\n",
    "        # TODO No idea what happened here, but Kai Rey shares the same ID than the stupid Customer Name \"Sample Company A\"\n",
    "        # Since Kai Rey also has the Initials \"SC\" (same as Sample Company A) I guess Kai Rey shoud be renamed \"Sample Company A\"\n",
    "        print(\"WTF:\", name, len(str(temp_id)), temp_id, ids)\n",
    "        continue\n",
    "    elif (len(ids) == 2) & (abs(ids[0] - ids[-1]) == 10000) & (ids.min() == temp_id):\n",
    "        # print(\"Treffer:\", name, len(str(temp_id)), temp_id, ids)\n",
    "        last_id = temp_id\n",
    "        #break\n",
    "    else:\n",
    "        print(\"Unknown:\", name, len(str(temp_id)), temp_id, ids)\n",
    "    #break\n",
    "    #for country, data2 in data.group(\"Country\"):\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "orders = orders.drop([\"Customer_ID\"], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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>Row_ID</th>\n",
       "      <th>Order_TwoLC</th>\n",
       "      <th>Order_Date</th>\n",
       "      <th>Ship_Date</th>\n",
       "      <th>Ship_Mode</th>\n",
       "      <th>Customer_Name</th>\n",
       "      <th>Segment</th>\n",
       "      <th>Postal_Code</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>Country</th>\n",
       "      <th>Region</th>\n",
       "      <th>Market</th>\n",
       "      <th>Product_ID</th>\n",
       "      <th>Product_Name</th>\n",
       "      <th>SubCategory</th>\n",
       "      <th>Category</th>\n",
       "      <th>Sales</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>Discount</th>\n",
       "      <th>Profit</th>\n",
       "      <th>Shipping_Cost</th>\n",
       "      <th>Order_Priority</th>\n",
       "      <th>Returned</th>\n",
       "      <th>Person</th>\n",
       "      <th>Region_Number</th>\n",
       "      <th>Initials</th>\n",
       "      <th>Country_ID</th>\n",
       "      <th>CountryAlias_ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>24599</td>\n",
       "      <td>IN</td>\n",
       "      <td>2017-03-22</td>\n",
       "      <td>2017-03-29</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>Cathy Armstrong</td>\n",
       "      <td>Home Office</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Herat</td>\n",
       "      <td>Hirat</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Southern Asia</td>\n",
       "      <td>Asia Pacific</td>\n",
       "      <td>FUR-BO-4861</td>\n",
       "      <td>Ikea Library with Doors, Mobile</td>\n",
       "      <td>Bookcases</td>\n",
       "      <td>Furniture</td>\n",
       "      <td>731.82</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>102.42</td>\n",
       "      <td>39.66</td>\n",
       "      <td>Medium</td>\n",
       "      <td>True</td>\n",
       "      <td>Chandrakant Chaudhri</td>\n",
       "      <td>0</td>\n",
       "      <td>CA</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>24598</td>\n",
       "      <td>IN</td>\n",
       "      <td>2017-03-22</td>\n",
       "      <td>2017-03-29</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>Cathy Armstrong</td>\n",
       "      <td>Home Office</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Herat</td>\n",
       "      <td>Hirat</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Southern Asia</td>\n",
       "      <td>Asia Pacific</td>\n",
       "      <td>TEC-MA-4211</td>\n",
       "      <td>Epson Receipt Printer, White</td>\n",
       "      <td>Machines</td>\n",
       "      <td>Technology</td>\n",
       "      <td>346.32</td>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13.77</td>\n",
       "      <td>14.10</td>\n",
       "      <td>Medium</td>\n",
       "      <td>True</td>\n",
       "      <td>Chandrakant Chaudhri</td>\n",
       "      <td>0</td>\n",
       "      <td>CA</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>24597</td>\n",
       "      <td>IN</td>\n",
       "      <td>2017-03-22</td>\n",
       "      <td>2017-03-29</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>Cathy Armstrong</td>\n",
       "      <td>Home Office</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Herat</td>\n",
       "      <td>Hirat</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Southern Asia</td>\n",
       "      <td>Asia Pacific</td>\n",
       "      <td>FUR-FU-5726</td>\n",
       "      <td>Rubbermaid Door Stop, Erganomic</td>\n",
       "      <td>Furnishings</td>\n",
       "      <td>Furniture</td>\n",
       "      <td>169.68</td>\n",
       "      <td>4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>79.68</td>\n",
       "      <td>11.01</td>\n",
       "      <td>Medium</td>\n",
       "      <td>True</td>\n",
       "      <td>Chandrakant Chaudhri</td>\n",
       "      <td>0</td>\n",
       "      <td>CA</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>29465</td>\n",
       "      <td>ID</td>\n",
       "      <td>2015-09-01</td>\n",
       "      <td>2015-09-04</td>\n",
       "      <td>Second Class</td>\n",
       "      <td>Brian Dahlen</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Herat</td>\n",
       "      <td>Hirat</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Southern Asia</td>\n",
       "      <td>Asia Pacific</td>\n",
       "      <td>OFF-SU-2988</td>\n",
       "      <td>Acme Scissors, Easy Grip</td>\n",
       "      <td>Supplies</td>\n",
       "      <td>Office Supplies</td>\n",
       "      <td>243.54</td>\n",
       "      <td>9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>104.49</td>\n",
       "      <td>18.72</td>\n",
       "      <td>Medium</td>\n",
       "      <td>False</td>\n",
       "      <td>Chandrakant Chaudhri</td>\n",
       "      <td>0</td>\n",
       "      <td>BD</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>29464</td>\n",
       "      <td>ID</td>\n",
       "      <td>2015-09-01</td>\n",
       "      <td>2015-09-04</td>\n",
       "      <td>Second Class</td>\n",
       "      <td>Brian Dahlen</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Herat</td>\n",
       "      <td>Hirat</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Southern Asia</td>\n",
       "      <td>Asia Pacific</td>\n",
       "      <td>OFF-EN-3664</td>\n",
       "      <td>Cameo Interoffice Envelope, with clear poly wi...</td>\n",
       "      <td>Envelopes</td>\n",
       "      <td>Office Supplies</td>\n",
       "      <td>203.88</td>\n",
       "      <td>4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>24.36</td>\n",
       "      <td>5.72</td>\n",
       "      <td>Medium</td>\n",
       "      <td>False</td>\n",
       "      <td>Chandrakant Chaudhri</td>\n",
       "      <td>0</td>\n",
       "      <td>BD</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Row_ID Order_TwoLC Order_Date  Ship_Date       Ship_Mode    Customer_Name  \\\n",
       "0   24599          IN 2017-03-22 2017-03-29  Standard Class  Cathy Armstrong   \n",
       "1   24598          IN 2017-03-22 2017-03-29  Standard Class  Cathy Armstrong   \n",
       "2   24597          IN 2017-03-22 2017-03-29  Standard Class  Cathy Armstrong   \n",
       "3   29465          ID 2015-09-01 2015-09-04    Second Class     Brian Dahlen   \n",
       "4   29464          ID 2015-09-01 2015-09-04    Second Class     Brian Dahlen   \n",
       "\n",
       "       Segment  Postal_Code   City  State      Country         Region  \\\n",
       "0  Home Office          NaN  Herat  Hirat  Afghanistan  Southern Asia   \n",
       "1  Home Office          NaN  Herat  Hirat  Afghanistan  Southern Asia   \n",
       "2  Home Office          NaN  Herat  Hirat  Afghanistan  Southern Asia   \n",
       "3     Consumer          NaN  Herat  Hirat  Afghanistan  Southern Asia   \n",
       "4     Consumer          NaN  Herat  Hirat  Afghanistan  Southern Asia   \n",
       "\n",
       "         Market   Product_ID  \\\n",
       "0  Asia Pacific  FUR-BO-4861   \n",
       "1  Asia Pacific  TEC-MA-4211   \n",
       "2  Asia Pacific  FUR-FU-5726   \n",
       "3  Asia Pacific  OFF-SU-2988   \n",
       "4  Asia Pacific  OFF-EN-3664   \n",
       "\n",
       "                                        Product_Name  SubCategory  \\\n",
       "0                    Ikea Library with Doors, Mobile    Bookcases   \n",
       "1                       Epson Receipt Printer, White     Machines   \n",
       "2                    Rubbermaid Door Stop, Erganomic  Furnishings   \n",
       "3                           Acme Scissors, Easy Grip     Supplies   \n",
       "4  Cameo Interoffice Envelope, with clear poly wi...    Envelopes   \n",
       "\n",
       "          Category   Sales  Quantity  Discount  Profit  Shipping_Cost  \\\n",
       "0        Furniture  731.82         2       0.0  102.42          39.66   \n",
       "1       Technology  346.32         3       0.0   13.77          14.10   \n",
       "2        Furniture  169.68         4       0.0   79.68          11.01   \n",
       "3  Office Supplies  243.54         9       0.0  104.49          18.72   \n",
       "4  Office Supplies  203.88         4       0.0   24.36           5.72   \n",
       "\n",
       "  Order_Priority  Returned                Person  Region_Number Initials  \\\n",
       "0         Medium      True  Chandrakant Chaudhri              0       CA   \n",
       "1         Medium      True  Chandrakant Chaudhri              0       CA   \n",
       "2         Medium      True  Chandrakant Chaudhri              0       CA   \n",
       "3         Medium     False  Chandrakant Chaudhri              0       BD   \n",
       "4         Medium     False  Chandrakant Chaudhri              0       BD   \n",
       "\n",
       "   Country_ID  CountryAlias_ID  \n",
       "0         1.0              0.0  \n",
       "1         1.0              0.0  \n",
       "2         1.0              0.0  \n",
       "3         1.0              0.0  \n",
       "4         1.0              0.0  "
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orders.head()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Split the dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "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>Row_ID</th>\n",
       "      <th>Order_TwoLC</th>\n",
       "      <th>Order_Date</th>\n",
       "      <th>Ship_Date</th>\n",
       "      <th>Ship_Mode</th>\n",
       "      <th>Customer_Name</th>\n",
       "      <th>Segment</th>\n",
       "      <th>Postal_Code</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>Country</th>\n",
       "      <th>Region</th>\n",
       "      <th>Market</th>\n",
       "      <th>Product_ID</th>\n",
       "      <th>Product_Name</th>\n",
       "      <th>SubCategory</th>\n",
       "      <th>Category</th>\n",
       "      <th>Sales</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>Discount</th>\n",
       "      <th>Profit</th>\n",
       "      <th>Shipping_Cost</th>\n",
       "      <th>Order_Priority</th>\n",
       "      <th>Returned</th>\n",
       "      <th>Person</th>\n",
       "      <th>Region_Number</th>\n",
       "      <th>Initials</th>\n",
       "      <th>Country_ID</th>\n",
       "      <th>CountryAlias_ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>24599</td>\n",
       "      <td>IN</td>\n",
       "      <td>2017-03-22</td>\n",
       "      <td>2017-03-29</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>Cathy Armstrong</td>\n",
       "      <td>Home Office</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Herat</td>\n",
       "      <td>Hirat</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Southern Asia</td>\n",
       "      <td>Asia Pacific</td>\n",
       "      <td>FUR-BO-4861</td>\n",
       "      <td>Ikea Library with Doors, Mobile</td>\n",
       "      <td>Bookcases</td>\n",
       "      <td>Furniture</td>\n",
       "      <td>731.82</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>102.42</td>\n",
       "      <td>39.66</td>\n",
       "      <td>Medium</td>\n",
       "      <td>True</td>\n",
       "      <td>Chandrakant Chaudhri</td>\n",
       "      <td>0</td>\n",
       "      <td>CA</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>24598</td>\n",
       "      <td>IN</td>\n",
       "      <td>2017-03-22</td>\n",
       "      <td>2017-03-29</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>Cathy Armstrong</td>\n",
       "      <td>Home Office</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Herat</td>\n",
       "      <td>Hirat</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Southern Asia</td>\n",
       "      <td>Asia Pacific</td>\n",
       "      <td>TEC-MA-4211</td>\n",
       "      <td>Epson Receipt Printer, White</td>\n",
       "      <td>Machines</td>\n",
       "      <td>Technology</td>\n",
       "      <td>346.32</td>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13.77</td>\n",
       "      <td>14.10</td>\n",
       "      <td>Medium</td>\n",
       "      <td>True</td>\n",
       "      <td>Chandrakant Chaudhri</td>\n",
       "      <td>0</td>\n",
       "      <td>CA</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>24597</td>\n",
       "      <td>IN</td>\n",
       "      <td>2017-03-22</td>\n",
       "      <td>2017-03-29</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>Cathy Armstrong</td>\n",
       "      <td>Home Office</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Herat</td>\n",
       "      <td>Hirat</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Southern Asia</td>\n",
       "      <td>Asia Pacific</td>\n",
       "      <td>FUR-FU-5726</td>\n",
       "      <td>Rubbermaid Door Stop, Erganomic</td>\n",
       "      <td>Furnishings</td>\n",
       "      <td>Furniture</td>\n",
       "      <td>169.68</td>\n",
       "      <td>4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>79.68</td>\n",
       "      <td>11.01</td>\n",
       "      <td>Medium</td>\n",
       "      <td>True</td>\n",
       "      <td>Chandrakant Chaudhri</td>\n",
       "      <td>0</td>\n",
       "      <td>CA</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>29465</td>\n",
       "      <td>ID</td>\n",
       "      <td>2015-09-01</td>\n",
       "      <td>2015-09-04</td>\n",
       "      <td>Second Class</td>\n",
       "      <td>Brian Dahlen</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Herat</td>\n",
       "      <td>Hirat</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Southern Asia</td>\n",
       "      <td>Asia Pacific</td>\n",
       "      <td>OFF-SU-2988</td>\n",
       "      <td>Acme Scissors, Easy Grip</td>\n",
       "      <td>Supplies</td>\n",
       "      <td>Office Supplies</td>\n",
       "      <td>243.54</td>\n",
       "      <td>9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>104.49</td>\n",
       "      <td>18.72</td>\n",
       "      <td>Medium</td>\n",
       "      <td>False</td>\n",
       "      <td>Chandrakant Chaudhri</td>\n",
       "      <td>0</td>\n",
       "      <td>BD</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>29464</td>\n",
       "      <td>ID</td>\n",
       "      <td>2015-09-01</td>\n",
       "      <td>2015-09-04</td>\n",
       "      <td>Second Class</td>\n",
       "      <td>Brian Dahlen</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Herat</td>\n",
       "      <td>Hirat</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Southern Asia</td>\n",
       "      <td>Asia Pacific</td>\n",
       "      <td>OFF-EN-3664</td>\n",
       "      <td>Cameo Interoffice Envelope, with clear poly wi...</td>\n",
       "      <td>Envelopes</td>\n",
       "      <td>Office Supplies</td>\n",
       "      <td>203.88</td>\n",
       "      <td>4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>24.36</td>\n",
       "      <td>5.72</td>\n",
       "      <td>Medium</td>\n",
       "      <td>False</td>\n",
       "      <td>Chandrakant Chaudhri</td>\n",
       "      <td>0</td>\n",
       "      <td>BD</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Row_ID Order_TwoLC Order_Date  Ship_Date       Ship_Mode    Customer_Name  \\\n",
       "0   24599          IN 2017-03-22 2017-03-29  Standard Class  Cathy Armstrong   \n",
       "1   24598          IN 2017-03-22 2017-03-29  Standard Class  Cathy Armstrong   \n",
       "2   24597          IN 2017-03-22 2017-03-29  Standard Class  Cathy Armstrong   \n",
       "3   29465          ID 2015-09-01 2015-09-04    Second Class     Brian Dahlen   \n",
       "4   29464          ID 2015-09-01 2015-09-04    Second Class     Brian Dahlen   \n",
       "\n",
       "       Segment  Postal_Code   City  State      Country         Region  \\\n",
       "0  Home Office          NaN  Herat  Hirat  Afghanistan  Southern Asia   \n",
       "1  Home Office          NaN  Herat  Hirat  Afghanistan  Southern Asia   \n",
       "2  Home Office          NaN  Herat  Hirat  Afghanistan  Southern Asia   \n",
       "3     Consumer          NaN  Herat  Hirat  Afghanistan  Southern Asia   \n",
       "4     Consumer          NaN  Herat  Hirat  Afghanistan  Southern Asia   \n",
       "\n",
       "         Market   Product_ID  \\\n",
       "0  Asia Pacific  FUR-BO-4861   \n",
       "1  Asia Pacific  TEC-MA-4211   \n",
       "2  Asia Pacific  FUR-FU-5726   \n",
       "3  Asia Pacific  OFF-SU-2988   \n",
       "4  Asia Pacific  OFF-EN-3664   \n",
       "\n",
       "                                        Product_Name  SubCategory  \\\n",
       "0                    Ikea Library with Doors, Mobile    Bookcases   \n",
       "1                       Epson Receipt Printer, White     Machines   \n",
       "2                    Rubbermaid Door Stop, Erganomic  Furnishings   \n",
       "3                           Acme Scissors, Easy Grip     Supplies   \n",
       "4  Cameo Interoffice Envelope, with clear poly wi...    Envelopes   \n",
       "\n",
       "          Category   Sales  Quantity  Discount  Profit  Shipping_Cost  \\\n",
       "0        Furniture  731.82         2       0.0  102.42          39.66   \n",
       "1       Technology  346.32         3       0.0   13.77          14.10   \n",
       "2        Furniture  169.68         4       0.0   79.68          11.01   \n",
       "3  Office Supplies  243.54         9       0.0  104.49          18.72   \n",
       "4  Office Supplies  203.88         4       0.0   24.36           5.72   \n",
       "\n",
       "  Order_Priority  Returned                Person  Region_Number Initials  \\\n",
       "0         Medium      True  Chandrakant Chaudhri              0       CA   \n",
       "1         Medium      True  Chandrakant Chaudhri              0       CA   \n",
       "2         Medium      True  Chandrakant Chaudhri              0       CA   \n",
       "3         Medium     False  Chandrakant Chaudhri              0       BD   \n",
       "4         Medium     False  Chandrakant Chaudhri              0       BD   \n",
       "\n",
       "   Country_ID  CountryAlias_ID  \n",
       "0         1.0              0.0  \n",
       "1         1.0              0.0  \n",
       "2         1.0              0.0  \n",
       "3         1.0              0.0  \n",
       "4         1.0              0.0  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(orders.head(5))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Let's try to create a card_ID for every bought product per Customer and per Day\n",
    "# There are Orders with different destinations - these needs to be separated!\n",
    "# 10124,US-2014-BT1130518-41643,1/4/2014,1/11/2014,Standard Class,BT-1130518,Beth Thompson,Home Office,,Pilar,Alagoas,Brazil,South America,LATAM,OFF-EN-4912,\"Jiffy Interoffice Envelope, Set of 50\",Envelopes,Office Supplies,\"74,304\",6,\"0,6\",\"-107,856\",\"7,042\",Medium\n",
    "# 130,MX-2014-BT1130531-41643,1/4/2014,1/11/2014,Standard Class,BT-1130531,Beth Thompson,Home Office,,Manzanillo,Granma,Cuba,Caribbean,LATAM,OFF-EN-4912,\"Jiffy Interoffice Envelope, Set of 50\",Envelopes,Office Supplies,\"185,76\",6,0,\"3,6\",\"16,394\",Medium\n",
    "\n",
    "orders['Cart_ID'] = orders.groupby(['Order_Date', 'Customer_Name', 'City', 'Order_TwoLC', 'Order_Priority']).ngroup() + 1\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "orders['Region_ID'] = orders.groupby(\"Region\").ngroup() + 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "orders[\"Customer_ID\"] = orders.groupby(\"Customer_Name\").ngroup() + 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    4861\n",
       "1    4211\n",
       "2    5726\n",
       "3    2988\n",
       "4    3664\n",
       "Name: Product_ID, dtype: object"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check if the ProductID can be reduced to the ID:\n",
    "for name, data in orders.groupby(\"Product_ID\"):\n",
    "    e = name.split(sep=\"-\")\n",
    "    if ( len(data.Category.unique()) == 1 and data.Category.unique()[0].upper()[0:3] != e[0]) & ( len(data.SubCategory.unique()) == 1 and data.SubCategory.unique()[0].upper()[0:2] != e[1]):\n",
    "        print(name, e)\n",
    "        print(data.Category.unique()[0].upper()[0:3])\n",
    "        print(data.SubCategory.unique()[0].upper()[0:2])\n",
    "        break\n",
    "orders['Product_ID'] = orders['Product_ID'].str.rsplit('-', n=1).str[-1]\n",
    "orders[\"Product_ID\"].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Most frequently used Product_ID: 6129\n",
      "Count: 227\n"
     ]
    }
   ],
   "source": [
    "most_used_product = None\n",
    "most_used_count = 0\n",
    "\n",
    "for pid, data in orders.groupby(\"Product_ID\"):\n",
    "    group_size = len(data)\n",
    "    if group_size > most_used_count:\n",
    "        most_used_product = pid\n",
    "        most_used_count = group_size\n",
    "\n",
    "print(\"Most frequently used Product_ID:\", most_used_product)\n",
    "print(\"Count:\", most_used_count)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "#  * (1-orders[\"Discount\"])\n",
    "orders[\"Unit_Price\"] = round(orders[\"Sales\"] / orders[\"Quantity\"] / (1-orders[\"Discount\"]), 4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Unfortunately the Column \"ProductID\" is more a \"CategoryID\" therefore we create our own ProductID per Unit_Price and \"Product Category\"\n",
    "orders = orders.rename(columns={'Product_ID': 'Category_ID'})\n",
    "orders[\"Product_ID\"] = orders.groupby([\"Category_ID\", \"Unit_Price\"]).ngroup() + 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "orders[\"City_ID\"] = orders.groupby([\"Country\", \"City\", \"State\"]).ngroup() + 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "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>Row_ID</th>\n",
       "      <th>Order_TwoLC</th>\n",
       "      <th>Order_Date</th>\n",
       "      <th>Ship_Date</th>\n",
       "      <th>Ship_Mode</th>\n",
       "      <th>Customer_Name</th>\n",
       "      <th>Segment</th>\n",
       "      <th>Postal_Code</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>Country</th>\n",
       "      <th>Region</th>\n",
       "      <th>Market</th>\n",
       "      <th>Category_ID</th>\n",
       "      <th>Product_Name</th>\n",
       "      <th>SubCategory</th>\n",
       "      <th>Category</th>\n",
       "      <th>Sales</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>Discount</th>\n",
       "      <th>Profit</th>\n",
       "      <th>Shipping_Cost</th>\n",
       "      <th>Order_Priority</th>\n",
       "      <th>Returned</th>\n",
       "      <th>Person</th>\n",
       "      <th>Region_Number</th>\n",
       "      <th>Initials</th>\n",
       "      <th>Country_ID</th>\n",
       "      <th>CountryAlias_ID</th>\n",
       "      <th>Cart_ID</th>\n",
       "      <th>Region_ID</th>\n",
       "      <th>Customer_ID</th>\n",
       "      <th>Unit_Price</th>\n",
       "      <th>Product_ID</th>\n",
       "      <th>City_ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>24599</td>\n",
       "      <td>IN</td>\n",
       "      <td>2017-03-22</td>\n",
       "      <td>2017-03-29</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>Cathy Armstrong</td>\n",
       "      <td>Home Office</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Herat</td>\n",
       "      <td>Hirat</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Southern Asia</td>\n",
       "      <td>Asia Pacific</td>\n",
       "      <td>4861</td>\n",
       "      <td>Ikea Library with Doors, Mobile</td>\n",
       "      <td>Bookcases</td>\n",
       "      <td>Furniture</td>\n",
       "      <td>731.82</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>102.42</td>\n",
       "      <td>39.66</td>\n",
       "      <td>Medium</td>\n",
       "      <td>True</td>\n",
       "      <td>Chandrakant Chaudhri</td>\n",
       "      <td>0</td>\n",
       "      <td>CA</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>18106</td>\n",
       "      <td>17</td>\n",
       "      <td>137</td>\n",
       "      <td>365.91</td>\n",
       "      <td>3155</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>24598</td>\n",
       "      <td>IN</td>\n",
       "      <td>2017-03-22</td>\n",
       "      <td>2017-03-29</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>Cathy Armstrong</td>\n",
       "      <td>Home Office</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Herat</td>\n",
       "      <td>Hirat</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Southern Asia</td>\n",
       "      <td>Asia Pacific</td>\n",
       "      <td>4211</td>\n",
       "      <td>Epson Receipt Printer, White</td>\n",
       "      <td>Machines</td>\n",
       "      <td>Technology</td>\n",
       "      <td>346.32</td>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13.77</td>\n",
       "      <td>14.10</td>\n",
       "      <td>Medium</td>\n",
       "      <td>True</td>\n",
       "      <td>Chandrakant Chaudhri</td>\n",
       "      <td>0</td>\n",
       "      <td>CA</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>18106</td>\n",
       "      <td>17</td>\n",
       "      <td>137</td>\n",
       "      <td>115.44</td>\n",
       "      <td>2151</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>24597</td>\n",
       "      <td>IN</td>\n",
       "      <td>2017-03-22</td>\n",
       "      <td>2017-03-29</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>Cathy Armstrong</td>\n",
       "      <td>Home Office</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Herat</td>\n",
       "      <td>Hirat</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Southern Asia</td>\n",
       "      <td>Asia Pacific</td>\n",
       "      <td>5726</td>\n",
       "      <td>Rubbermaid Door Stop, Erganomic</td>\n",
       "      <td>Furnishings</td>\n",
       "      <td>Furniture</td>\n",
       "      <td>169.68</td>\n",
       "      <td>4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>79.68</td>\n",
       "      <td>11.01</td>\n",
       "      <td>Medium</td>\n",
       "      <td>True</td>\n",
       "      <td>Chandrakant Chaudhri</td>\n",
       "      <td>0</td>\n",
       "      <td>CA</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>18106</td>\n",
       "      <td>17</td>\n",
       "      <td>137</td>\n",
       "      <td>42.42</td>\n",
       "      <td>4424</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>29465</td>\n",
       "      <td>ID</td>\n",
       "      <td>2015-09-01</td>\n",
       "      <td>2015-09-04</td>\n",
       "      <td>Second Class</td>\n",
       "      <td>Brian Dahlen</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Herat</td>\n",
       "      <td>Hirat</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Southern Asia</td>\n",
       "      <td>Asia Pacific</td>\n",
       "      <td>2988</td>\n",
       "      <td>Acme Scissors, Easy Grip</td>\n",
       "      <td>Supplies</td>\n",
       "      <td>Office Supplies</td>\n",
       "      <td>243.54</td>\n",
       "      <td>9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>104.49</td>\n",
       "      <td>18.72</td>\n",
       "      <td>Medium</td>\n",
       "      <td>False</td>\n",
       "      <td>Chandrakant Chaudhri</td>\n",
       "      <td>0</td>\n",
       "      <td>BD</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7505</td>\n",
       "      <td>17</td>\n",
       "      <td>107</td>\n",
       "      <td>27.06</td>\n",
       "      <td>205</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>29464</td>\n",
       "      <td>ID</td>\n",
       "      <td>2015-09-01</td>\n",
       "      <td>2015-09-04</td>\n",
       "      <td>Second Class</td>\n",
       "      <td>Brian Dahlen</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Herat</td>\n",
       "      <td>Hirat</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Southern Asia</td>\n",
       "      <td>Asia Pacific</td>\n",
       "      <td>3664</td>\n",
       "      <td>Cameo Interoffice Envelope, with clear poly wi...</td>\n",
       "      <td>Envelopes</td>\n",
       "      <td>Office Supplies</td>\n",
       "      <td>203.88</td>\n",
       "      <td>4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>24.36</td>\n",
       "      <td>5.72</td>\n",
       "      <td>Medium</td>\n",
       "      <td>False</td>\n",
       "      <td>Chandrakant Chaudhri</td>\n",
       "      <td>0</td>\n",
       "      <td>BD</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7505</td>\n",
       "      <td>17</td>\n",
       "      <td>107</td>\n",
       "      <td>50.97</td>\n",
       "      <td>1263</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Row_ID Order_TwoLC Order_Date  Ship_Date       Ship_Mode    Customer_Name  \\\n",
       "0   24599          IN 2017-03-22 2017-03-29  Standard Class  Cathy Armstrong   \n",
       "1   24598          IN 2017-03-22 2017-03-29  Standard Class  Cathy Armstrong   \n",
       "2   24597          IN 2017-03-22 2017-03-29  Standard Class  Cathy Armstrong   \n",
       "3   29465          ID 2015-09-01 2015-09-04    Second Class     Brian Dahlen   \n",
       "4   29464          ID 2015-09-01 2015-09-04    Second Class     Brian Dahlen   \n",
       "\n",
       "       Segment  Postal_Code   City  State      Country         Region  \\\n",
       "0  Home Office          NaN  Herat  Hirat  Afghanistan  Southern Asia   \n",
       "1  Home Office          NaN  Herat  Hirat  Afghanistan  Southern Asia   \n",
       "2  Home Office          NaN  Herat  Hirat  Afghanistan  Southern Asia   \n",
       "3     Consumer          NaN  Herat  Hirat  Afghanistan  Southern Asia   \n",
       "4     Consumer          NaN  Herat  Hirat  Afghanistan  Southern Asia   \n",
       "\n",
       "         Market Category_ID  \\\n",
       "0  Asia Pacific        4861   \n",
       "1  Asia Pacific        4211   \n",
       "2  Asia Pacific        5726   \n",
       "3  Asia Pacific        2988   \n",
       "4  Asia Pacific        3664   \n",
       "\n",
       "                                        Product_Name  SubCategory  \\\n",
       "0                    Ikea Library with Doors, Mobile    Bookcases   \n",
       "1                       Epson Receipt Printer, White     Machines   \n",
       "2                    Rubbermaid Door Stop, Erganomic  Furnishings   \n",
       "3                           Acme Scissors, Easy Grip     Supplies   \n",
       "4  Cameo Interoffice Envelope, with clear poly wi...    Envelopes   \n",
       "\n",
       "          Category   Sales  Quantity  Discount  Profit  Shipping_Cost  \\\n",
       "0        Furniture  731.82         2       0.0  102.42          39.66   \n",
       "1       Technology  346.32         3       0.0   13.77          14.10   \n",
       "2        Furniture  169.68         4       0.0   79.68          11.01   \n",
       "3  Office Supplies  243.54         9       0.0  104.49          18.72   \n",
       "4  Office Supplies  203.88         4       0.0   24.36           5.72   \n",
       "\n",
       "  Order_Priority  Returned                Person  Region_Number Initials  \\\n",
       "0         Medium      True  Chandrakant Chaudhri              0       CA   \n",
       "1         Medium      True  Chandrakant Chaudhri              0       CA   \n",
       "2         Medium      True  Chandrakant Chaudhri              0       CA   \n",
       "3         Medium     False  Chandrakant Chaudhri              0       BD   \n",
       "4         Medium     False  Chandrakant Chaudhri              0       BD   \n",
       "\n",
       "   Country_ID  CountryAlias_ID  Cart_ID  Region_ID  Customer_ID  Unit_Price  \\\n",
       "0         1.0              0.0    18106         17          137      365.91   \n",
       "1         1.0              0.0    18106         17          137      115.44   \n",
       "2         1.0              0.0    18106         17          137       42.42   \n",
       "3         1.0              0.0     7505         17          107       27.06   \n",
       "4         1.0              0.0     7505         17          107       50.97   \n",
       "\n",
       "   Product_ID  City_ID  \n",
       "0        3155        1  \n",
       "1        2151        1  \n",
       "2        4424        1  \n",
       "3         205        1  \n",
       "4        1263        1  "
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orders.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "orders['Postal_Code'].fillna(0, inplace=True)\n",
    "orders[\"Address_ID\"] = orders.groupby([\"Postal_Code\", \"City\", \"State\", \"Country\", \"Region\", \"Market\"]).ngroup() + 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "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>Postal_Code</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>Country</th>\n",
       "      <th>Region</th>\n",
       "      <th>Market</th>\n",
       "      <th>Address_ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.0</td>\n",
       "      <td>Herat</td>\n",
       "      <td>Hirat</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Southern Asia</td>\n",
       "      <td>Asia Pacific</td>\n",
       "      <td>1205</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0.0</td>\n",
       "      <td>Kabul</td>\n",
       "      <td>Kabul</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Southern Asia</td>\n",
       "      <td>Asia Pacific</td>\n",
       "      <td>1396</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>0.0</td>\n",
       "      <td>Kandahar</td>\n",
       "      <td>Kandahar</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Southern Asia</td>\n",
       "      <td>Asia Pacific</td>\n",
       "      <td>1419</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>0.0</td>\n",
       "      <td>Jalalabad</td>\n",
       "      <td>Nangarhar</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Southern Asia</td>\n",
       "      <td>Asia Pacific</td>\n",
       "      <td>1334</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>0.0</td>\n",
       "      <td>Chittagong</td>\n",
       "      <td>Chittagong</td>\n",
       "      <td>Bangladesh</td>\n",
       "      <td>Southern Asia</td>\n",
       "      <td>Asia Pacific</td>\n",
       "      <td>657</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Postal_Code        City       State      Country         Region  \\\n",
       "0           0.0       Herat       Hirat  Afghanistan  Southern Asia   \n",
       "5           0.0       Kabul       Kabul  Afghanistan  Southern Asia   \n",
       "46          0.0    Kandahar    Kandahar  Afghanistan  Southern Asia   \n",
       "50          0.0   Jalalabad   Nangarhar  Afghanistan  Southern Asia   \n",
       "55          0.0  Chittagong  Chittagong   Bangladesh  Southern Asia   \n",
       "\n",
       "          Market  Address_ID  \n",
       "0   Asia Pacific        1205  \n",
       "5   Asia Pacific        1396  \n",
       "46  Asia Pacific        1419  \n",
       "50  Asia Pacific        1334  \n",
       "55  Asia Pacific         657  "
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orders[[\"Postal_Code\", \"City\", \"State\", \"Country\", \"Region\", \"Market\", \"Address_ID\"]].drop_duplicates().head()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Splitting"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Asia Pacific', 'Europe', 'Africa', 'LATAM', 'USCA']"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(orders.Market.drop_duplicates().values)\n",
    "#orders.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "# TODO We could just use city ID and then outsource State, Country, Region and Market into another tables\n",
    "db = {\n",
    "    \"Purchase\": {\n",
    "        \"groupby\": {\n",
    "            'Customer_ID': { \"NOT NULL\": True }, \n",
    "            'Order_Date': { \"NOT NULL\": True, } \n",
    "        },\n",
    "        \"fields\": {\n",
    "            'Cart_ID': { \"NOT NULL\": True, 'PRIMARY KEY': True },\n",
    "            'Address_ID': { \"NOT NULL\": True },\n",
    "            'Order_TwoLC': { \"NOT NULL\": True },\n",
    "            'Ship_Date': { 'CHECK': \"(Ship_Date >= Order_Date)\"},\n",
    "            'Ship_Mode': { 'CHECK': \"(Ship_Mode IN ('Standard Class', 'Second Class', 'First Class', 'Same Day'))\" } , \n",
    "            'Order_Priority': { 'CHECK': \"(Order_Priority IN ('Medium', 'High', 'Critical', 'Low'))\" },\n",
    "            'Returned': { 'DEFAULT': 'False' }\n",
    "        }\n",
    "    },\n",
    "    \"Address\": {\n",
    "        \"groupby\": {\n",
    "            'Address_ID': { \"NOT NULL\": True, 'PRIMARY KEY': True }\n",
    "        },\n",
    "        # Postal_Code\tCity\tState\tCountry\tRegion\tMarket  Country_ID CountryAlias_ID Region_ID\n",
    "        \"fields\": {\n",
    "            'Postal_Code': { \"NOT NULL\": False },\n",
    "            'City_ID': { \"NOT NULL\": True },\n",
    "        }\n",
    "    },\n",
    "    \"City\": {\n",
    "        \"groupby\": {\n",
    "            'City_ID': { \"NOT NULL\": True, 'PRIMARY KEY': True }\n",
    "        },\n",
    "        \"fields\": {\n",
    "            'City': { \"NOT NULL\": True },\n",
    "            'State': { \"NOT NULL\": True },\n",
    "            'Country_ID': { \"NOT NULL\": True},\n",
    "            'Region_ID': { \"NOT NULL\": True }\n",
    "        }\n",
    "    },\n",
    "    \"Country\": {\n",
    "        \"groupby\": {\n",
    "            'Country_ID': { \"NOT NULL\": True, 'PRIMARY KEY': True }\n",
    "        },\n",
    "        \"fields\": {\n",
    "            'Country': { \"NOT NULL\": True },\n",
    "            'Market': { \"CHECK\": \"(Market IN ('Asia Pacific', 'Europe', 'Africa', 'LATAM', 'USCA'))\" },\n",
    "            'CountryAlias_ID': { \"NOT NULL\": True },\n",
    "        }\n",
    "    },\n",
    "    \"Region\": {\n",
    "        \"groupby\": {\n",
    "            'Region_ID': { \"NOT NULL\": True, 'PRIMARY KEY': True }\n",
    "        },\n",
    "        \"fields\": {\n",
    "            'Region': { \"RENAME\": \"Name\", \"NOT NULL\": True },\n",
    "            'Region_Number': { \"DEFAULT\": \"0\", \"NOT NULL\": True},\n",
    "            'Person': { \"NOT NULL\": True }\n",
    "        }\n",
    "    },\n",
    "    \"Customer\": {\n",
    "        \"groupby\": {\n",
    "            'Customer_ID': { \"NOT NULL\": True, 'PRIMARY KEY': True }\n",
    "        },\n",
    "        \"fields\": {\n",
    "            'Customer_Name': { \"NOT NULL\": True },\n",
    "            'Segment': { 'CHECK': \"(Segment IN ('Consumer', 'Corporate', 'Home Office'))\" },\n",
    "            'Initials': {}\n",
    "        }\n",
    "    },\n",
    "    \"Cart\": {\n",
    "        \"groupby\": {\n",
    "            'Cart_ID': { \"NOT NULL\": True },\n",
    "            'Row_ID': { \"NOT NULL\": True, 'PRIMARY KEY': True }\n",
    "        },\n",
    "        \"fields\": {\n",
    "            'Product_ID': { \"NOT NULL\": True },\n",
    "            'Quantity': { \"NOT NULL\": True },\n",
    "            'Sales': { \"NOT NULL\": True },\n",
    "            'Discount': { \"DEFAULT\": \"0\" , \"NOT NULL\": True },\n",
    "            'Profit': { \"NOT NULL\": True },\n",
    "            'Shipping_Cost': { \"NOT NULL\": True },\n",
    "        }\n",
    "    },\n",
    "    \"Product\": {\n",
    "        \"groupby\": {\n",
    "            'Product_ID': { \"NOT NULL\": True, 'PRIMARY KEY': True }\n",
    "        },\n",
    "        \"fields\": {\n",
    "            'Product_Name': { \"NOT NULL\": True },\n",
    "            'Category_ID': { \"NOT NULL\": True },\n",
    "            'Unit_Price': { \"NOT NULL\": True },\n",
    "        }\n",
    "    },\n",
    "    \"Category\": {\n",
    "        \"groupby\": {\n",
    "            'Category_ID': { \"NOT NULL\": True, 'PRIMARY KEY': True }\n",
    "        },\n",
    "        \"fields\": {\n",
    "            'Category': { \"NOT NULL\": True, 'CHECK': \"(Category IN ('Office Supplies', 'Technology', 'Furniture'))\" },\n",
    "            'SubCategory': { \"NOT NULL\": True, 'CHECK': \"(SubCategory in ('Bookcases', 'Supplies', 'Machines', 'Furnishings', 'Envelopes', 'Tables', 'Chairs', 'Phones', 'Appliances', 'Copiers', 'Storage', 'Paper', 'Accessories', 'Art', 'Binders', 'Labels', 'Fasteners'))\" }\n",
    "        }\n",
    "    }\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Found Ref for Address_ID in table Address to Purchase\n",
      "Found Ref for City_ID in table City to Address\n",
      "Found Ref for Country_ID in table Country to City\n",
      "Found Ref for Region_ID in table Region to City\n",
      "Found Ref for Customer_ID in table Customer to Purchase\n",
      "Found Ref for Cart_ID in table Cart to Purchase\n",
      "Found Ref for Product_ID in table Product to Cart\n",
      "Found Ref for Category_ID in table Category to Product\n"
     ]
    }
   ],
   "source": [
    "check = {column: 0 for column in orders.columns.to_series().to_dict()}\n",
    "references = {}\n",
    "for table, data in db.items():\n",
    "    for t, data in db[table].items():\n",
    "        if (t == \"REF\"):\n",
    "            continue\n",
    "        elif (t != \"groupby\" and t != \"fields\"):\n",
    "            print(f\"Unknown type {t}\")\n",
    "            break\n",
    "        for column, data in db[table][t].items():\n",
    "\n",
    "            if \"_ID\" in column:\n",
    "                if column in references:\n",
    "                    print(f\"Found Ref for {column} in table {table} to {references[column]}\")\n",
    "                    #db[table][t][column][\"RENAME\"] = \"ID\"\n",
    "                    if \"REF\" not in db[references[column]]:\n",
    "                        db[references[column]]['REF'] = []\n",
    "                    db[references[column]]['REF'].append(f\"CONSTRAINT fk_{table}_{column} FOREIGN KEY ({column}) REFERENCES \\\"public\\\".\\\"{table}\\\"({column})\")\n",
    "                else:\n",
    "                    references[column] = table\n",
    "                    check[column] += 1\n",
    "            else:\n",
    "                check[column] += 1\n",
    "\n",
    "for key in check.keys():\n",
    "    if check[key] == 0:\n",
    "        print(f\"Column {key} wasn't used\")\n",
    "    elif check[key] > 1:\n",
    "        print(f\"Column {key} was used {check[key]} times\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "product = orders.groupby(['Product_ID']).agg({\n",
    "    'Product_Name': 'first',\n",
    "    'Category_ID': 'first',\n",
    "    'Unit_Price': 'first'\n",
    "}).reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "# TODO We might want to get the max length of TEXT fields and limit them to that size with \"VARCHAR(X)\"\n",
    "\n",
    "datatypes = {\n",
    "    \"datetime64[ns]\": \"DATE\",\n",
    "    \"int64\": \"INT\",\n",
    "    \"float64\": \"INT\",\n",
    "    \"object\": \"TEXT\"\n",
    "}\n",
    "\n",
    "def createTable(name):\n",
    "    if name not in db:\n",
    "        return \"\"\n",
    "    \n",
    "    fields = []\n",
    "    for t in db[name].keys():\n",
    "        if t != \"REF\":\n",
    "\n",
    "            for key in db[name][t]:\n",
    "                datatype = str(orders[key].dtype)\n",
    "\n",
    "                if \"_ID\" in key:\n",
    "                    datatype = \"SERIAL\"\n",
    "                elif key == \"Returned\":\n",
    "                    datatype = \"BOOL\"\n",
    "                elif datatype in datatypes.keys():\n",
    "                    datatype = datatypes[datatype]\n",
    "                else:\n",
    "                    print(f\"Unknown datatype {datatype} in {datatypes.keys()}\")\n",
    "                    datatype = \"UNKNOWN\"\n",
    "\n",
    "                key2 = key\n",
    "                # if \"RENAME\" in db[name][t][key]:\n",
    "                #     key2 = db[name][t][key][\"RENAME\"]\n",
    "\n",
    "                f = \"\\n\\t\" + key2 + \" \" + datatype\n",
    "                \n",
    "                for constraint in db[name][t][key]:\n",
    "                    if constraint == \"RENAME\":\n",
    "                        continue\n",
    "                    elif constraint == \"CHECK\":    \n",
    "                        f = f + \" CHECK \" + db[name][t][key][constraint]\n",
    "                    elif constraint == \"DEFAULT\":\n",
    "                        f = f + \" DEFAULT \" + db[name][t][key][constraint]\n",
    "                    elif db[name][t][key][constraint] == True:\n",
    "                        f = f + \" \" + constraint\n",
    "                fields.append(f)\n",
    "    \n",
    "    sql = \"CREATE TABLE \" + name + \" (\" + \",\".join(fields)\n",
    "    # if \"REF\" in db[name].keys():\n",
    "    #     sql = sql + \",\\n\\n\\t\" + \",\\n\\t\".join(db[name][\"REF\"])\n",
    "    sql = sql + \"\\n)\"\n",
    "    return sql\n",
    "\n",
    "\n",
    "def createData(name):\n",
    "    if name not in db:\n",
    "        return \"\"\n",
    "    \n",
    "    d = {}\n",
    "    for k in db[name][\"fields\"]:\n",
    "        d[k] = 'first'\n",
    "    data = orders.groupby(list(db[name][\"groupby\"].keys())).agg(d).reset_index()\n",
    "\n",
    "    for k in db[name][\"fields\"]:\n",
    "        if \"RENAME\" in db[name][\"fields\"][k]:\n",
    "            data = data.rename(columns={k: db[name][\"fields\"][k][\"RENAME\"]})\n",
    "\n",
    "    for k in db[name][\"groupby\"]:\n",
    "        if \"RENAME\" in db[name][\"groupby\"][k]:\n",
    "            data = data.rename(columns={k: db[name][\"groupby\"][k][\"RENAME\"]})            \n",
    "\n",
    "    return data\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dict_keys(['Purchase', 'Address', 'City', 'Country', 'Region', 'Customer', 'Cart', 'Product', 'Category'])"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "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>Row_ID</th>\n",
       "      <th>Order_TwoLC</th>\n",
       "      <th>Order_Date</th>\n",
       "      <th>Ship_Date</th>\n",
       "      <th>Ship_Mode</th>\n",
       "      <th>Customer_Name</th>\n",
       "      <th>Segment</th>\n",
       "      <th>Postal_Code</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>Country</th>\n",
       "      <th>Region</th>\n",
       "      <th>Market</th>\n",
       "      <th>Category_ID</th>\n",
       "      <th>Product_Name</th>\n",
       "      <th>SubCategory</th>\n",
       "      <th>Category</th>\n",
       "      <th>Sales</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>Discount</th>\n",
       "      <th>Profit</th>\n",
       "      <th>Shipping_Cost</th>\n",
       "      <th>Order_Priority</th>\n",
       "      <th>Returned</th>\n",
       "      <th>Person</th>\n",
       "      <th>Region_Number</th>\n",
       "      <th>Initials</th>\n",
       "      <th>Country_ID</th>\n",
       "      <th>CountryAlias_ID</th>\n",
       "      <th>Cart_ID</th>\n",
       "      <th>Region_ID</th>\n",
       "      <th>Customer_ID</th>\n",
       "      <th>Unit_Price</th>\n",
       "      <th>Product_ID</th>\n",
       "      <th>City_ID</th>\n",
       "      <th>Address_ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>29890</th>\n",
       "      <td>14</td>\n",
       "      <td>MX</td>\n",
       "      <td>2017-09-24</td>\n",
       "      <td>2017-10-01</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>Paul Knutson</td>\n",
       "      <td>Home Office</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Managua</td>\n",
       "      <td>Managua</td>\n",
       "      <td>Nicaragua</td>\n",
       "      <td>Central America</td>\n",
       "      <td>LATAM</td>\n",
       "      <td>5034</td>\n",
       "      <td>Kraft Mailers, Security-Tint</td>\n",
       "      <td>Envelopes</td>\n",
       "      <td>Office Supplies</td>\n",
       "      <td>80.1</td>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>37.62</td>\n",
       "      <td>8.863</td>\n",
       "      <td>Low</td>\n",
       "      <td>False</td>\n",
       "      <td>Nicodemo Bautista</td>\n",
       "      <td>0</td>\n",
       "      <td>PK</td>\n",
       "      <td>93.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>22557</td>\n",
       "      <td>3</td>\n",
       "      <td>595</td>\n",
       "      <td>26.7</td>\n",
       "      <td>3440</td>\n",
       "      <td>2343</td>\n",
       "      <td>1777</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Row_ID Order_TwoLC Order_Date  Ship_Date       Ship_Mode Customer_Name  \\\n",
       "29890      14          MX 2017-09-24 2017-10-01  Standard Class  Paul Knutson   \n",
       "\n",
       "           Segment  Postal_Code     City    State    Country           Region  \\\n",
       "29890  Home Office          0.0  Managua  Managua  Nicaragua  Central America   \n",
       "\n",
       "      Market Category_ID                  Product_Name SubCategory  \\\n",
       "29890  LATAM        5034  Kraft Mailers, Security-Tint   Envelopes   \n",
       "\n",
       "              Category  Sales  Quantity  Discount  Profit  Shipping_Cost  \\\n",
       "29890  Office Supplies   80.1         3       0.0   37.62          8.863   \n",
       "\n",
       "      Order_Priority  Returned             Person  Region_Number Initials  \\\n",
       "29890            Low     False  Nicodemo Bautista              0       PK   \n",
       "\n",
       "       Country_ID  CountryAlias_ID  Cart_ID  Region_ID  Customer_ID  \\\n",
       "29890        93.0              0.0    22557          3          595   \n",
       "\n",
       "       Unit_Price  Product_ID  City_ID  Address_ID  \n",
       "29890        26.7        3440     2343        1777  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(orders[orders.Cart_ID == 22557])"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Import"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "import configparser\n",
    "\n",
    "config = configparser.ConfigParser()\n",
    "config.read('config.ini')\n",
    "\n",
    "# Access the values\n",
    "username = config.get('Credentials', 'username')\n",
    "password = config.get('Credentials', 'password')\n",
    "\n",
    "host = config.get('Database', 'host')\n",
    "database = config.get('Database', 'database')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Category\n",
      "Product\n",
      "Cart\n",
      "Customer\n",
      "CREATE TABLE Customer (\n",
      "\tCustomer_ID SERIAL NOT NULL PRIMARY KEY,\n",
      "\tCustomer_Name TEXT NOT NULL,\n",
      "\tSegment TEXT CHECK (Segment IN ('Consumer', 'Corporate', 'Home Office')),\n",
      "\tInitials TEXT\n",
      ")\n"
     ]
    },
    {
     "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>Customer_ID</th>\n",
       "      <th>Customer_Name</th>\n",
       "      <th>Segment</th>\n",
       "      <th>Initials</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Aaron Bergman</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>AB</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Customer_ID  Customer_Name   Segment Initials\n",
       "0            1  Aaron Bergman  Consumer       AB"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "ename": "ValueError",
     "evalue": "Table 'Customer' already exists.",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mValueError\u001b[0m                                Traceback (most recent call last)",
      "\u001b[1;32m~\\AppData\\Local\\Temp\\ipykernel_11108\\953412804.py\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m     35\u001b[0m     \u001b[0mdisplay\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdata\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mhead\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;36m1\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     36\u001b[0m     \u001b[1;31m#data.to_sql(name, conn, if_exists='replace', index=False)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 37\u001b[1;33m     \u001b[0mpdsql\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mto_sql\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdata\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mname\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     38\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     39\u001b[0m     \u001b[1;31m#break\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\Users\\Coding\\anaconda3\\lib\\site-packages\\pandas\\io\\sql.py\u001b[0m in \u001b[0;36mto_sql\u001b[1;34m(self, frame, name, if_exists, index, index_label, schema, chunksize, dtype, method, engine, **engine_kwargs)\u001b[0m\n\u001b[0;32m   1730\u001b[0m         \u001b[0msql_engine\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mget_engine\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mengine\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1731\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1732\u001b[1;33m         table = self.prep_table(\n\u001b[0m\u001b[0;32m   1733\u001b[0m             \u001b[0mframe\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mframe\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1734\u001b[0m             \u001b[0mname\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mname\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\Users\\Coding\\anaconda3\\lib\\site-packages\\pandas\\io\\sql.py\u001b[0m in \u001b[0;36mprep_table\u001b[1;34m(self, frame, name, if_exists, index, index_label, schema, dtype)\u001b[0m\n\u001b[0;32m   1629\u001b[0m             \u001b[0mdtype\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mdtype\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1630\u001b[0m         )\n\u001b[1;32m-> 1631\u001b[1;33m         \u001b[0mtable\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcreate\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1632\u001b[0m         \u001b[1;32mreturn\u001b[0m \u001b[0mtable\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1633\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\Users\\Coding\\anaconda3\\lib\\site-packages\\pandas\\io\\sql.py\u001b[0m in \u001b[0;36mcreate\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m    832\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexists\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    833\u001b[0m             \u001b[1;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mif_exists\u001b[0m \u001b[1;33m==\u001b[0m \u001b[1;34m\"fail\"\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 834\u001b[1;33m                 \u001b[1;32mraise\u001b[0m \u001b[0mValueError\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34mf\"Table '{self.name}' already exists.\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    835\u001b[0m             \u001b[1;32melif\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mif_exists\u001b[0m \u001b[1;33m==\u001b[0m \u001b[1;34m\"replace\"\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    836\u001b[0m                 \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mpd_sql\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mdrop_table\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mname\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mschema\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mValueError\u001b[0m: Table 'Customer' already exists."
     ]
    }
   ],
   "source": [
    "from sqlalchemy import create_engine, MetaData\n",
    "\n",
    "engine = create_engine('postgresql://' + username + \":\" + password + \"@\" + host + \"/\" + database)\n",
    "conn = engine.raw_connection()\n",
    "cur = conn.cursor()\n",
    "\n",
    "# meta = sqlalchemy.MetaData(engine, schema='a_schema')\n",
    "# meta.reflect(engine, schema='a_schema')\n",
    "pdsql = pd.io.sql.SQLDatabase(engine)\n",
    "\n",
    "#df = pd.read_sql(\"SELECT * FROM xxx\", con=engine)    \n",
    "#pdsql.to_sql(df, 'test')\n",
    "\n",
    "for name in reversed(db.keys()):\n",
    "    print(name)\n",
    "    if name == \"Category\":\n",
    "        continue\n",
    "\n",
    "    if name == \"Product\":\n",
    "        continue\n",
    "\n",
    "    if name == \"Cart\":\n",
    "        continue\n",
    "\n",
    "\n",
    "    sql = createTable(name)\n",
    "    print(sql)\n",
    "    #continue\n",
    "    cur.execute(\"DROP TABLE IF EXISTS \" + name)\n",
    "    cur.execute(sql)\n",
    "    # continue\n",
    "\n",
    "    data = createData(name)\n",
    "    display(data.head(1))\n",
    "    #data.to_sql(name, conn, if_exists='replace', index=False)\n",
    "    pdsql.to_sql(data, name)\n",
    "    \n",
    "    #break\n",
    "\n",
    "cur.close()\n",
    "conn.close()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# DB Disconnect"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.close()\n",
    "conn.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.13"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}