Data Analytics Project.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "74820156",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collecting kaggle\n",
      "  Downloading kaggle-1.6.17.tar.gz (82 kB)\n",
      "     ---------------------------------------- 0.0/82.7 kB ? eta -:--:--\n",
      "     ---- ----------------------------------- 10.2/82.7 kB ? eta -:--:--\n",
      "     ---------------------------- --------- 61.4/82.7 kB 812.7 kB/s eta 0:00:01\n",
      "     -------------------------------------- 82.7/82.7 kB 770.8 kB/s eta 0:00:00\n",
      "  Preparing metadata (setup.py): started\n",
      "  Preparing metadata (setup.py): finished with status 'done'\n",
      "Requirement already satisfied: six>=1.10 in c:\\users\\lenovo\\anaconda3\\lib\\site-packages (from kaggle) (1.16.0)\n",
      "Requirement already satisfied: certifi>=2023.7.22 in c:\\users\\lenovo\\anaconda3\\lib\\site-packages (from kaggle) (2024.2.2)\n",
      "Requirement already satisfied: python-dateutil in c:\\users\\lenovo\\anaconda3\\lib\\site-packages (from kaggle) (2.8.2)\n",
      "Requirement already satisfied: requests in c:\\users\\lenovo\\anaconda3\\lib\\site-packages (from kaggle) (2.31.0)\n",
      "Requirement already satisfied: tqdm in c:\\users\\lenovo\\anaconda3\\lib\\site-packages (from kaggle) (4.65.0)\n",
      "Requirement already satisfied: python-slugify in c:\\users\\lenovo\\anaconda3\\lib\\site-packages (from kaggle) (5.0.2)\n",
      "Requirement already satisfied: urllib3 in c:\\users\\lenovo\\anaconda3\\lib\\site-packages (from kaggle) (1.26.16)\n",
      "Requirement already satisfied: bleach in c:\\users\\lenovo\\anaconda3\\lib\\site-packages (from kaggle) (4.1.0)\n",
      "Requirement already satisfied: packaging in c:\\users\\lenovo\\anaconda3\\lib\\site-packages (from bleach->kaggle) (23.1)\n",
      "Requirement already satisfied: webencodings in c:\\users\\lenovo\\anaconda3\\lib\\site-packages (from bleach->kaggle) (0.5.1)\n",
      "Requirement already satisfied: text-unidecode>=1.3 in c:\\users\\lenovo\\anaconda3\\lib\\site-packages (from python-slugify->kaggle) (1.3)\n",
      "Requirement already satisfied: charset-normalizer<4,>=2 in c:\\users\\lenovo\\anaconda3\\lib\\site-packages (from requests->kaggle) (2.0.4)\n",
      "Requirement already satisfied: idna<4,>=2.5 in c:\\users\\lenovo\\anaconda3\\lib\\site-packages (from requests->kaggle) (3.4)\n",
      "Requirement already satisfied: colorama in c:\\users\\lenovo\\anaconda3\\lib\\site-packages (from tqdm->kaggle) (0.4.6)\n",
      "Building wheels for collected packages: kaggle\n",
      "  Building wheel for kaggle (setup.py): started\n",
      "  Building wheel for kaggle (setup.py): finished with status 'done'\n",
      "  Created wheel for kaggle: filename=kaggle-1.6.17-py3-none-any.whl size=105802 sha256=1b5a121b41bb8a29303c455fafd8cf9e80b9d8fa7a8e67698b103461168c0157\n",
      "  Stored in directory: c:\\users\\lenovo\\appdata\\local\\pip\\cache\\wheels\\ff\\55\\fb\\b27a466be754d2a06ffe0e37b248d844f090a63b51becea85d\n",
      "Successfully built kaggle\n",
      "Installing collected packages: kaggle\n",
      "Successfully installed kaggle-1.6.17\n"
     ]
    }
   ],
   "source": [
    "!pip install kaggle"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "74a19e9a",
   "metadata": {},
   "outputs": [],
   "source": [
    "import kaggle"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "2d66db39",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Dataset URL: https://www.kaggle.com/datasets/parthmistry20/sql-project-data-set\n",
      "License(s): unknown\n",
      "Downloading orders.csv to C:\\Users\\Lenovo\\Downloads\\data_analytics_project-main\\data_analytics_project-main\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\n",
      "  0%|          | 0.00/1.29M [00:00<?, ?B/s]\n",
      " 78%|#######7  | 1.00M/1.29M [00:02<00:00, 517kB/s]\n",
      "100%|##########| 1.29M/1.29M [00:02<00:00, 678kB/s]\n",
      "100%|##########| 1.29M/1.29M [00:02<00:00, 632kB/s]\n"
     ]
    }
   ],
   "source": [
    "!kaggle datasets download parthmistry20/sql-project-data-set -f orders.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "31b43c2e",
   "metadata": {},
   "outputs": [],
   "source": [
    "import zipfile"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "a8d1be63",
   "metadata": {},
   "outputs": [],
   "source": [
    "zip_ref = zipfile.ZipFile(r'C:\\Users\\Lenovo\\Downloads\\data_analytics_project-main\\data_analytics_project-main\\archive.zip')\n",
    "zip_ref.extractall()  # extract file to dir\n",
    "zip_ref.close()  # close file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "b8eaf249",
   "metadata": {},
   "outputs": [],
   "source": [
    "#read data from the file and handle null values\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "a2d8bd3f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('orders.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "8763c1b9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Order Id</th>\n",
       "      <th>Order Date</th>\n",
       "      <th>Ship Mode</th>\n",
       "      <th>Segment</th>\n",
       "      <th>Country</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>Postal Code</th>\n",
       "      <th>Region</th>\n",
       "      <th>Category</th>\n",
       "      <th>Sub Category</th>\n",
       "      <th>Product Id</th>\n",
       "      <th>cost price</th>\n",
       "      <th>List Price</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>Discount Percent</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2023-03-01</td>\n",
       "      <td>Second Class</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>United States</td>\n",
       "      <td>Henderson</td>\n",
       "      <td>Kentucky</td>\n",
       "      <td>42420</td>\n",
       "      <td>South</td>\n",
       "      <td>Furniture</td>\n",
       "      <td>Bookcases</td>\n",
       "      <td>FUR-BO-10001798</td>\n",
       "      <td>240</td>\n",
       "      <td>260</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2023-08-15</td>\n",
       "      <td>Second Class</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>United States</td>\n",
       "      <td>Henderson</td>\n",
       "      <td>Kentucky</td>\n",
       "      <td>42420</td>\n",
       "      <td>South</td>\n",
       "      <td>Furniture</td>\n",
       "      <td>Chairs</td>\n",
       "      <td>FUR-CH-10000454</td>\n",
       "      <td>600</td>\n",
       "      <td>730</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>2023-01-10</td>\n",
       "      <td>Second Class</td>\n",
       "      <td>Corporate</td>\n",
       "      <td>United States</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>California</td>\n",
       "      <td>90036</td>\n",
       "      <td>West</td>\n",
       "      <td>Office Supplies</td>\n",
       "      <td>Labels</td>\n",
       "      <td>OFF-LA-10000240</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>2022-06-18</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>United States</td>\n",
       "      <td>Fort Lauderdale</td>\n",
       "      <td>Florida</td>\n",
       "      <td>33311</td>\n",
       "      <td>South</td>\n",
       "      <td>Furniture</td>\n",
       "      <td>Tables</td>\n",
       "      <td>FUR-TA-10000577</td>\n",
       "      <td>780</td>\n",
       "      <td>960</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>2022-07-13</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>United States</td>\n",
       "      <td>Fort Lauderdale</td>\n",
       "      <td>Florida</td>\n",
       "      <td>33311</td>\n",
       "      <td>South</td>\n",
       "      <td>Office Supplies</td>\n",
       "      <td>Storage</td>\n",
       "      <td>OFF-ST-10000760</td>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>2022-03-13</td>\n",
       "      <td>Not Available</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>United States</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>California</td>\n",
       "      <td>90032</td>\n",
       "      <td>West</td>\n",
       "      <td>Furniture</td>\n",
       "      <td>Furnishings</td>\n",
       "      <td>FUR-FU-10001487</td>\n",
       "      <td>50</td>\n",
       "      <td>50</td>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>2022-12-28</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>United States</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>California</td>\n",
       "      <td>90032</td>\n",
       "      <td>West</td>\n",
       "      <td>Office Supplies</td>\n",
       "      <td>Art</td>\n",
       "      <td>OFF-AR-10002833</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>2022-01-25</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>United States</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>California</td>\n",
       "      <td>90032</td>\n",
       "      <td>West</td>\n",
       "      <td>Technology</td>\n",
       "      <td>Phones</td>\n",
       "      <td>TEC-PH-10002275</td>\n",
       "      <td>860</td>\n",
       "      <td>910</td>\n",
       "      <td>6</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>2023-03-23</td>\n",
       "      <td>Not Available</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>United States</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>California</td>\n",
       "      <td>90032</td>\n",
       "      <td>West</td>\n",
       "      <td>Office Supplies</td>\n",
       "      <td>Binders</td>\n",
       "      <td>OFF-BI-10003910</td>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>2023-05-16</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>United States</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>California</td>\n",
       "      <td>90032</td>\n",
       "      <td>West</td>\n",
       "      <td>Office Supplies</td>\n",
       "      <td>Appliances</td>\n",
       "      <td>OFF-AP-10002892</td>\n",
       "      <td>90</td>\n",
       "      <td>110</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>11</td>\n",
       "      <td>2023-03-31</td>\n",
       "      <td>Not Available</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>United States</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>California</td>\n",
       "      <td>90032</td>\n",
       "      <td>West</td>\n",
       "      <td>Furniture</td>\n",
       "      <td>Tables</td>\n",
       "      <td>FUR-TA-10001539</td>\n",
       "      <td>1470</td>\n",
       "      <td>1710</td>\n",
       "      <td>9</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>12</td>\n",
       "      <td>2023-12-25</td>\n",
       "      <td>Not Available</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>United States</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>California</td>\n",
       "      <td>90032</td>\n",
       "      <td>West</td>\n",
       "      <td>Technology</td>\n",
       "      <td>Phones</td>\n",
       "      <td>TEC-PH-10002033</td>\n",
       "      <td>750</td>\n",
       "      <td>910</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>13</td>\n",
       "      <td>2022-02-11</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>United States</td>\n",
       "      <td>Concord</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>28027</td>\n",
       "      <td>South</td>\n",
       "      <td>Office Supplies</td>\n",
       "      <td>Paper</td>\n",
       "      <td>OFF-PA-10002365</td>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>14</td>\n",
       "      <td>2023-07-18</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>United States</td>\n",
       "      <td>Seattle</td>\n",
       "      <td>Washington</td>\n",
       "      <td>98103</td>\n",
       "      <td>West</td>\n",
       "      <td>Office Supplies</td>\n",
       "      <td>Binders</td>\n",
       "      <td>OFF-BI-10003656</td>\n",
       "      <td>360</td>\n",
       "      <td>410</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>15</td>\n",
       "      <td>2023-11-09</td>\n",
       "      <td>unknown</td>\n",
       "      <td>Home Office</td>\n",
       "      <td>United States</td>\n",
       "      <td>Fort Worth</td>\n",
       "      <td>Texas</td>\n",
       "      <td>76106</td>\n",
       "      <td>Central</td>\n",
       "      <td>Office Supplies</td>\n",
       "      <td>Appliances</td>\n",
       "      <td>OFF-AP-10002311</td>\n",
       "      <td>60</td>\n",
       "      <td>70</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>16</td>\n",
       "      <td>2022-06-18</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>Home Office</td>\n",
       "      <td>United States</td>\n",
       "      <td>Fort Worth</td>\n",
       "      <td>Texas</td>\n",
       "      <td>76106</td>\n",
       "      <td>Central</td>\n",
       "      <td>Office Supplies</td>\n",
       "      <td>Binders</td>\n",
       "      <td>OFF-BI-10000756</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>17</td>\n",
       "      <td>2022-02-04</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>United States</td>\n",
       "      <td>Madison</td>\n",
       "      <td>Wisconsin</td>\n",
       "      <td>53711</td>\n",
       "      <td>Central</td>\n",
       "      <td>Office Supplies</td>\n",
       "      <td>Storage</td>\n",
       "      <td>OFF-ST-10004186</td>\n",
       "      <td>610</td>\n",
       "      <td>670</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>18</td>\n",
       "      <td>2023-08-04</td>\n",
       "      <td>Second Class</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>United States</td>\n",
       "      <td>West Jordan</td>\n",
       "      <td>Utah</td>\n",
       "      <td>84084</td>\n",
       "      <td>West</td>\n",
       "      <td>Office Supplies</td>\n",
       "      <td>Storage</td>\n",
       "      <td>OFF-ST-10000107</td>\n",
       "      <td>60</td>\n",
       "      <td>60</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>19</td>\n",
       "      <td>2022-01-23</td>\n",
       "      <td>Second Class</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>United States</td>\n",
       "      <td>San Francisco</td>\n",
       "      <td>California</td>\n",
       "      <td>94109</td>\n",
       "      <td>West</td>\n",
       "      <td>Office Supplies</td>\n",
       "      <td>Art</td>\n",
       "      <td>OFF-AR-10003056</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>20</td>\n",
       "      <td>2022-01-11</td>\n",
       "      <td>Second Class</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>United States</td>\n",
       "      <td>San Francisco</td>\n",
       "      <td>California</td>\n",
       "      <td>94109</td>\n",
       "      <td>West</td>\n",
       "      <td>Technology</td>\n",
       "      <td>Phones</td>\n",
       "      <td>TEC-PH-10001949</td>\n",
       "      <td>170</td>\n",
       "      <td>210</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Order Id  Order Date       Ship Mode      Segment        Country  \\\n",
       "0          1  2023-03-01    Second Class     Consumer  United States   \n",
       "1          2  2023-08-15    Second Class     Consumer  United States   \n",
       "2          3  2023-01-10    Second Class    Corporate  United States   \n",
       "3          4  2022-06-18  Standard Class     Consumer  United States   \n",
       "4          5  2022-07-13  Standard Class     Consumer  United States   \n",
       "5          6  2022-03-13   Not Available     Consumer  United States   \n",
       "6          7  2022-12-28  Standard Class     Consumer  United States   \n",
       "7          8  2022-01-25  Standard Class     Consumer  United States   \n",
       "8          9  2023-03-23   Not Available     Consumer  United States   \n",
       "9         10  2023-05-16  Standard Class     Consumer  United States   \n",
       "10        11  2023-03-31   Not Available     Consumer  United States   \n",
       "11        12  2023-12-25   Not Available     Consumer  United States   \n",
       "12        13  2022-02-11  Standard Class     Consumer  United States   \n",
       "13        14  2023-07-18  Standard Class     Consumer  United States   \n",
       "14        15  2023-11-09         unknown  Home Office  United States   \n",
       "15        16  2022-06-18  Standard Class  Home Office  United States   \n",
       "16        17  2022-02-04  Standard Class     Consumer  United States   \n",
       "17        18  2023-08-04    Second Class     Consumer  United States   \n",
       "18        19  2022-01-23    Second Class     Consumer  United States   \n",
       "19        20  2022-01-11    Second Class     Consumer  United States   \n",
       "\n",
       "               City           State  Postal Code   Region         Category  \\\n",
       "0         Henderson        Kentucky        42420    South        Furniture   \n",
       "1         Henderson        Kentucky        42420    South        Furniture   \n",
       "2       Los Angeles      California        90036     West  Office Supplies   \n",
       "3   Fort Lauderdale         Florida        33311    South        Furniture   \n",
       "4   Fort Lauderdale         Florida        33311    South  Office Supplies   \n",
       "5       Los Angeles      California        90032     West        Furniture   \n",
       "6       Los Angeles      California        90032     West  Office Supplies   \n",
       "7       Los Angeles      California        90032     West       Technology   \n",
       "8       Los Angeles      California        90032     West  Office Supplies   \n",
       "9       Los Angeles      California        90032     West  Office Supplies   \n",
       "10      Los Angeles      California        90032     West        Furniture   \n",
       "11      Los Angeles      California        90032     West       Technology   \n",
       "12          Concord  North Carolina        28027    South  Office Supplies   \n",
       "13          Seattle      Washington        98103     West  Office Supplies   \n",
       "14       Fort Worth           Texas        76106  Central  Office Supplies   \n",
       "15       Fort Worth           Texas        76106  Central  Office Supplies   \n",
       "16          Madison       Wisconsin        53711  Central  Office Supplies   \n",
       "17      West Jordan            Utah        84084     West  Office Supplies   \n",
       "18    San Francisco      California        94109     West  Office Supplies   \n",
       "19    San Francisco      California        94109     West       Technology   \n",
       "\n",
       "   Sub Category       Product Id  cost price  List Price  Quantity  \\\n",
       "0     Bookcases  FUR-BO-10001798         240         260         2   \n",
       "1        Chairs  FUR-CH-10000454         600         730         3   \n",
       "2        Labels  OFF-LA-10000240          10          10         2   \n",
       "3        Tables  FUR-TA-10000577         780         960         5   \n",
       "4       Storage  OFF-ST-10000760          20          20         2   \n",
       "5   Furnishings  FUR-FU-10001487          50          50         7   \n",
       "6           Art  OFF-AR-10002833          10          10         4   \n",
       "7        Phones  TEC-PH-10002275         860         910         6   \n",
       "8       Binders  OFF-BI-10003910          20          20         3   \n",
       "9    Appliances  OFF-AP-10002892          90         110         5   \n",
       "10       Tables  FUR-TA-10001539        1470        1710         9   \n",
       "11       Phones  TEC-PH-10002033         750         910         4   \n",
       "12        Paper  OFF-PA-10002365          20          20         3   \n",
       "13      Binders  OFF-BI-10003656         360         410         3   \n",
       "14   Appliances  OFF-AP-10002311          60          70         5   \n",
       "15      Binders  OFF-BI-10000756           0           0         3   \n",
       "16      Storage  OFF-ST-10004186         610         670         6   \n",
       "17      Storage  OFF-ST-10000107          60          60         2   \n",
       "18          Art  OFF-AR-10003056          10          10         2   \n",
       "19       Phones  TEC-PH-10001949         170         210         3   \n",
       "\n",
       "    Discount Percent  \n",
       "0                  2  \n",
       "1                  3  \n",
       "2                  5  \n",
       "3                  2  \n",
       "4                  5  \n",
       "5                  3  \n",
       "6                  3  \n",
       "7                  5  \n",
       "8                  2  \n",
       "9                  3  \n",
       "10                 3  \n",
       "11                 3  \n",
       "12                 3  \n",
       "13                 2  \n",
       "14                 5  \n",
       "15                 5  \n",
       "16                 3  \n",
       "17                 4  \n",
       "18                 4  \n",
       "19                 3  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "cb5ac93a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Order Id</th>\n",
       "      <th>Order Date</th>\n",
       "      <th>Ship Mode</th>\n",
       "      <th>Segment</th>\n",
       "      <th>Country</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>Postal Code</th>\n",
       "      <th>Region</th>\n",
       "      <th>Category</th>\n",
       "      <th>Sub Category</th>\n",
       "      <th>Product Id</th>\n",
       "      <th>cost price</th>\n",
       "      <th>List Price</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>Discount Percent</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9974</th>\n",
       "      <td>9975</td>\n",
       "      <td>2022-11-17</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>Home Office</td>\n",
       "      <td>United States</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>California</td>\n",
       "      <td>90032</td>\n",
       "      <td>West</td>\n",
       "      <td>Office Supplies</td>\n",
       "      <td>Art</td>\n",
       "      <td>OFF-AR-10004752</td>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9975</th>\n",
       "      <td>9976</td>\n",
       "      <td>2023-12-16</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>Home Office</td>\n",
       "      <td>United States</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>California</td>\n",
       "      <td>90032</td>\n",
       "      <td>West</td>\n",
       "      <td>Office Supplies</td>\n",
       "      <td>Paper</td>\n",
       "      <td>OFF-PA-10000743</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9976</th>\n",
       "      <td>9977</td>\n",
       "      <td>2022-09-11</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>Home Office</td>\n",
       "      <td>United States</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>California</td>\n",
       "      <td>90032</td>\n",
       "      <td>West</td>\n",
       "      <td>Technology</td>\n",
       "      <td>Phones</td>\n",
       "      <td>TEC-PH-10002496</td>\n",
       "      <td>200</td>\n",
       "      <td>250</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9977</th>\n",
       "      <td>9978</td>\n",
       "      <td>2023-12-03</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>Home Office</td>\n",
       "      <td>United States</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>California</td>\n",
       "      <td>90032</td>\n",
       "      <td>West</td>\n",
       "      <td>Office Supplies</td>\n",
       "      <td>Fasteners</td>\n",
       "      <td>OFF-FA-10003467</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9978</th>\n",
       "      <td>9979</td>\n",
       "      <td>2023-06-05</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>Home Office</td>\n",
       "      <td>United States</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>California</td>\n",
       "      <td>90032</td>\n",
       "      <td>West</td>\n",
       "      <td>Office Supplies</td>\n",
       "      <td>Binders</td>\n",
       "      <td>OFF-BI-10003727</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9979</th>\n",
       "      <td>9980</td>\n",
       "      <td>2023-10-12</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>Home Office</td>\n",
       "      <td>United States</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>California</td>\n",
       "      <td>90032</td>\n",
       "      <td>West</td>\n",
       "      <td>Office Supplies</td>\n",
       "      <td>Binders</td>\n",
       "      <td>OFF-BI-10002026</td>\n",
       "      <td>410</td>\n",
       "      <td>440</td>\n",
       "      <td>14</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9980</th>\n",
       "      <td>9981</td>\n",
       "      <td>2023-06-26</td>\n",
       "      <td>Second Class</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>United States</td>\n",
       "      <td>Lafayette</td>\n",
       "      <td>Louisiana</td>\n",
       "      <td>70506</td>\n",
       "      <td>South</td>\n",
       "      <td>Furniture</td>\n",
       "      <td>Tables</td>\n",
       "      <td>FUR-TA-10001039</td>\n",
       "      <td>80</td>\n",
       "      <td>90</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9981</th>\n",
       "      <td>9982</td>\n",
       "      <td>2022-11-14</td>\n",
       "      <td>First Class</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>United States</td>\n",
       "      <td>Fairfield</td>\n",
       "      <td>Ohio</td>\n",
       "      <td>45014</td>\n",
       "      <td>East</td>\n",
       "      <td>Office Supplies</td>\n",
       "      <td>Labels</td>\n",
       "      <td>OFF-LA-10004484</td>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9982</th>\n",
       "      <td>9983</td>\n",
       "      <td>2023-05-27</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>United States</td>\n",
       "      <td>Grand Rapids</td>\n",
       "      <td>Michigan</td>\n",
       "      <td>49505</td>\n",
       "      <td>Central</td>\n",
       "      <td>Office Supplies</td>\n",
       "      <td>Paper</td>\n",
       "      <td>OFF-PA-10002195</td>\n",
       "      <td>40</td>\n",
       "      <td>40</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9983</th>\n",
       "      <td>9984</td>\n",
       "      <td>2022-09-14</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>United States</td>\n",
       "      <td>Grand Rapids</td>\n",
       "      <td>Michigan</td>\n",
       "      <td>49505</td>\n",
       "      <td>Central</td>\n",
       "      <td>Technology</td>\n",
       "      <td>Phones</td>\n",
       "      <td>TEC-PH-10001305</td>\n",
       "      <td>80</td>\n",
       "      <td>100</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9984</th>\n",
       "      <td>9985</td>\n",
       "      <td>2023-08-04</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>United States</td>\n",
       "      <td>Long Beach</td>\n",
       "      <td>New York</td>\n",
       "      <td>11561</td>\n",
       "      <td>East</td>\n",
       "      <td>Office Supplies</td>\n",
       "      <td>Labels</td>\n",
       "      <td>OFF-LA-10003766</td>\n",
       "      <td>30</td>\n",
       "      <td>30</td>\n",
       "      <td>10</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9985</th>\n",
       "      <td>9986</td>\n",
       "      <td>2023-09-30</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>United States</td>\n",
       "      <td>Long Beach</td>\n",
       "      <td>New York</td>\n",
       "      <td>11561</td>\n",
       "      <td>East</td>\n",
       "      <td>Office Supplies</td>\n",
       "      <td>Supplies</td>\n",
       "      <td>OFF-SU-10000898</td>\n",
       "      <td>50</td>\n",
       "      <td>60</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9986</th>\n",
       "      <td>9987</td>\n",
       "      <td>2022-11-30</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>United States</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>California</td>\n",
       "      <td>90008</td>\n",
       "      <td>West</td>\n",
       "      <td>Technology</td>\n",
       "      <td>Accessories</td>\n",
       "      <td>TEC-AC-10003399</td>\n",
       "      <td>40</td>\n",
       "      <td>40</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9987</th>\n",
       "      <td>9988</td>\n",
       "      <td>2023-04-01</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>Corporate</td>\n",
       "      <td>United States</td>\n",
       "      <td>Athens</td>\n",
       "      <td>Georgia</td>\n",
       "      <td>30605</td>\n",
       "      <td>South</td>\n",
       "      <td>Technology</td>\n",
       "      <td>Accessories</td>\n",
       "      <td>TEC-AC-10001539</td>\n",
       "      <td>80</td>\n",
       "      <td>80</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9988</th>\n",
       "      <td>9989</td>\n",
       "      <td>2022-11-17</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>Corporate</td>\n",
       "      <td>United States</td>\n",
       "      <td>Athens</td>\n",
       "      <td>Georgia</td>\n",
       "      <td>30605</td>\n",
       "      <td>South</td>\n",
       "      <td>Technology</td>\n",
       "      <td>Phones</td>\n",
       "      <td>TEC-PH-10004006</td>\n",
       "      <td>180</td>\n",
       "      <td>210</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9989</th>\n",
       "      <td>9990</td>\n",
       "      <td>2023-02-18</td>\n",
       "      <td>Second Class</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>United States</td>\n",
       "      <td>Miami</td>\n",
       "      <td>Florida</td>\n",
       "      <td>33180</td>\n",
       "      <td>South</td>\n",
       "      <td>Furniture</td>\n",
       "      <td>Furnishings</td>\n",
       "      <td>FUR-FU-10001889</td>\n",
       "      <td>30</td>\n",
       "      <td>30</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9990</th>\n",
       "      <td>9991</td>\n",
       "      <td>2023-03-17</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>United States</td>\n",
       "      <td>Costa Mesa</td>\n",
       "      <td>California</td>\n",
       "      <td>92627</td>\n",
       "      <td>West</td>\n",
       "      <td>Furniture</td>\n",
       "      <td>Furnishings</td>\n",
       "      <td>FUR-FU-10000747</td>\n",
       "      <td>70</td>\n",
       "      <td>90</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9991</th>\n",
       "      <td>9992</td>\n",
       "      <td>2022-08-07</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>United States</td>\n",
       "      <td>Costa Mesa</td>\n",
       "      <td>California</td>\n",
       "      <td>92627</td>\n",
       "      <td>West</td>\n",
       "      <td>Technology</td>\n",
       "      <td>Phones</td>\n",
       "      <td>TEC-PH-10003645</td>\n",
       "      <td>220</td>\n",
       "      <td>260</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9992</th>\n",
       "      <td>9993</td>\n",
       "      <td>2022-11-19</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>United States</td>\n",
       "      <td>Costa Mesa</td>\n",
       "      <td>California</td>\n",
       "      <td>92627</td>\n",
       "      <td>West</td>\n",
       "      <td>Office Supplies</td>\n",
       "      <td>Paper</td>\n",
       "      <td>OFF-PA-10004041</td>\n",
       "      <td>30</td>\n",
       "      <td>30</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9993</th>\n",
       "      <td>9994</td>\n",
       "      <td>2022-07-17</td>\n",
       "      <td>Second Class</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>United States</td>\n",
       "      <td>Westminster</td>\n",
       "      <td>California</td>\n",
       "      <td>92683</td>\n",
       "      <td>West</td>\n",
       "      <td>Office Supplies</td>\n",
       "      <td>Appliances</td>\n",
       "      <td>OFF-AP-10002684</td>\n",
       "      <td>210</td>\n",
       "      <td>240</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Order Id  Order Date       Ship Mode      Segment        Country  \\\n",
       "9974      9975  2022-11-17  Standard Class  Home Office  United States   \n",
       "9975      9976  2023-12-16  Standard Class  Home Office  United States   \n",
       "9976      9977  2022-09-11  Standard Class  Home Office  United States   \n",
       "9977      9978  2023-12-03  Standard Class  Home Office  United States   \n",
       "9978      9979  2023-06-05  Standard Class  Home Office  United States   \n",
       "9979      9980  2023-10-12  Standard Class  Home Office  United States   \n",
       "9980      9981  2023-06-26    Second Class     Consumer  United States   \n",
       "9981      9982  2022-11-14     First Class     Consumer  United States   \n",
       "9982      9983  2023-05-27  Standard Class     Consumer  United States   \n",
       "9983      9984  2022-09-14  Standard Class     Consumer  United States   \n",
       "9984      9985  2023-08-04  Standard Class     Consumer  United States   \n",
       "9985      9986  2023-09-30  Standard Class     Consumer  United States   \n",
       "9986      9987  2022-11-30  Standard Class     Consumer  United States   \n",
       "9987      9988  2023-04-01  Standard Class    Corporate  United States   \n",
       "9988      9989  2022-11-17  Standard Class    Corporate  United States   \n",
       "9989      9990  2023-02-18    Second Class     Consumer  United States   \n",
       "9990      9991  2023-03-17  Standard Class     Consumer  United States   \n",
       "9991      9992  2022-08-07  Standard Class     Consumer  United States   \n",
       "9992      9993  2022-11-19  Standard Class     Consumer  United States   \n",
       "9993      9994  2022-07-17    Second Class     Consumer  United States   \n",
       "\n",
       "              City       State  Postal Code   Region         Category  \\\n",
       "9974   Los Angeles  California        90032     West  Office Supplies   \n",
       "9975   Los Angeles  California        90032     West  Office Supplies   \n",
       "9976   Los Angeles  California        90032     West       Technology   \n",
       "9977   Los Angeles  California        90032     West  Office Supplies   \n",
       "9978   Los Angeles  California        90032     West  Office Supplies   \n",
       "9979   Los Angeles  California        90032     West  Office Supplies   \n",
       "9980     Lafayette   Louisiana        70506    South        Furniture   \n",
       "9981     Fairfield        Ohio        45014     East  Office Supplies   \n",
       "9982  Grand Rapids    Michigan        49505  Central  Office Supplies   \n",
       "9983  Grand Rapids    Michigan        49505  Central       Technology   \n",
       "9984    Long Beach    New York        11561     East  Office Supplies   \n",
       "9985    Long Beach    New York        11561     East  Office Supplies   \n",
       "9986   Los Angeles  California        90008     West       Technology   \n",
       "9987        Athens     Georgia        30605    South       Technology   \n",
       "9988        Athens     Georgia        30605    South       Technology   \n",
       "9989         Miami     Florida        33180    South        Furniture   \n",
       "9990    Costa Mesa  California        92627     West        Furniture   \n",
       "9991    Costa Mesa  California        92627     West       Technology   \n",
       "9992    Costa Mesa  California        92627     West  Office Supplies   \n",
       "9993   Westminster  California        92683     West  Office Supplies   \n",
       "\n",
       "     Sub Category       Product Id  cost price  List Price  Quantity  \\\n",
       "9974          Art  OFF-AR-10004752          20          20         7   \n",
       "9975        Paper  OFF-PA-10000743          10          10         2   \n",
       "9976       Phones  TEC-PH-10002496         200         250         2   \n",
       "9977    Fasteners  OFF-FA-10003467          10          10         7   \n",
       "9978      Binders  OFF-BI-10003727          10          10         4   \n",
       "9979      Binders  OFF-BI-10002026         410         440        14   \n",
       "9980       Tables  FUR-TA-10001039          80          90         1   \n",
       "9981       Labels  OFF-LA-10004484          20          20         5   \n",
       "9982        Paper  OFF-PA-10002195          40          40         7   \n",
       "9983       Phones  TEC-PH-10001305          80         100         2   \n",
       "9984       Labels  OFF-LA-10003766          30          30        10   \n",
       "9985     Supplies  OFF-SU-10000898          50          60         4   \n",
       "9986  Accessories  TEC-AC-10003399          40          40         1   \n",
       "9987  Accessories  TEC-AC-10001539          80          80         1   \n",
       "9988       Phones  TEC-PH-10004006         180         210         5   \n",
       "9989  Furnishings  FUR-FU-10001889          30          30         3   \n",
       "9990  Furnishings  FUR-FU-10000747          70          90         2   \n",
       "9991       Phones  TEC-PH-10003645         220         260         2   \n",
       "9992        Paper  OFF-PA-10004041          30          30         4   \n",
       "9993   Appliances  OFF-AP-10002684         210         240         2   \n",
       "\n",
       "      Discount Percent  \n",
       "9974                 2  \n",
       "9975                 4  \n",
       "9976                 5  \n",
       "9977                 4  \n",
       "9978                 4  \n",
       "9979                 3  \n",
       "9980                 2  \n",
       "9981                 5  \n",
       "9982                 4  \n",
       "9983                 4  \n",
       "9984                 2  \n",
       "9985                 3  \n",
       "9986                 5  \n",
       "9987                 3  \n",
       "9988                 5  \n",
       "9989                 4  \n",
       "9990                 4  \n",
       "9991                 2  \n",
       "9992                 3  \n",
       "9993                 3  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.tail(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "543ecced",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Second Class', 'Standard Class', nan, 'First Class', 'Same Day'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#read data from the file and handle null values\n",
    "df['Ship Mode'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "4d9bca12",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Second Class', 'Standard Class', nan, 'First Class', 'Same Day'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('orders.csv',na_values=['Not Available','unknown'])\n",
    "df['Ship Mode'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "b771e0d4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>order_date</th>\n",
       "      <th>ship_mode</th>\n",
       "      <th>segment</th>\n",
       "      <th>country</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>postal_code</th>\n",
       "      <th>region</th>\n",
       "      <th>category</th>\n",
       "      <th>sub_category</th>\n",
       "      <th>product_id</th>\n",
       "      <th>cost_price</th>\n",
       "      <th>list_price</th>\n",
       "      <th>quantity</th>\n",
       "      <th>discount_percent</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2023-03-01</td>\n",
       "      <td>Second Class</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>United States</td>\n",
       "      <td>Henderson</td>\n",
       "      <td>Kentucky</td>\n",
       "      <td>42420</td>\n",
       "      <td>South</td>\n",
       "      <td>Furniture</td>\n",
       "      <td>Bookcases</td>\n",
       "      <td>FUR-BO-10001798</td>\n",
       "      <td>240</td>\n",
       "      <td>260</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2023-08-15</td>\n",
       "      <td>Second Class</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>United States</td>\n",
       "      <td>Henderson</td>\n",
       "      <td>Kentucky</td>\n",
       "      <td>42420</td>\n",
       "      <td>South</td>\n",
       "      <td>Furniture</td>\n",
       "      <td>Chairs</td>\n",
       "      <td>FUR-CH-10000454</td>\n",
       "      <td>600</td>\n",
       "      <td>730</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>2023-01-10</td>\n",
       "      <td>Second Class</td>\n",
       "      <td>Corporate</td>\n",
       "      <td>United States</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>California</td>\n",
       "      <td>90036</td>\n",
       "      <td>West</td>\n",
       "      <td>Office Supplies</td>\n",
       "      <td>Labels</td>\n",
       "      <td>OFF-LA-10000240</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>2022-06-18</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>United States</td>\n",
       "      <td>Fort Lauderdale</td>\n",
       "      <td>Florida</td>\n",
       "      <td>33311</td>\n",
       "      <td>South</td>\n",
       "      <td>Furniture</td>\n",
       "      <td>Tables</td>\n",
       "      <td>FUR-TA-10000577</td>\n",
       "      <td>780</td>\n",
       "      <td>960</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>2022-07-13</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>United States</td>\n",
       "      <td>Fort Lauderdale</td>\n",
       "      <td>Florida</td>\n",
       "      <td>33311</td>\n",
       "      <td>South</td>\n",
       "      <td>Office Supplies</td>\n",
       "      <td>Storage</td>\n",
       "      <td>OFF-ST-10000760</td>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>2022-03-13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>United States</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>California</td>\n",
       "      <td>90032</td>\n",
       "      <td>West</td>\n",
       "      <td>Furniture</td>\n",
       "      <td>Furnishings</td>\n",
       "      <td>FUR-FU-10001487</td>\n",
       "      <td>50</td>\n",
       "      <td>50</td>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>2022-12-28</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>United States</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>California</td>\n",
       "      <td>90032</td>\n",
       "      <td>West</td>\n",
       "      <td>Office Supplies</td>\n",
       "      <td>Art</td>\n",
       "      <td>OFF-AR-10002833</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   order_id  order_date       ship_mode    segment        country  \\\n",
       "0         1  2023-03-01    Second Class   Consumer  United States   \n",
       "1         2  2023-08-15    Second Class   Consumer  United States   \n",
       "2         3  2023-01-10    Second Class  Corporate  United States   \n",
       "3         4  2022-06-18  Standard Class   Consumer  United States   \n",
       "4         5  2022-07-13  Standard Class   Consumer  United States   \n",
       "5         6  2022-03-13             NaN   Consumer  United States   \n",
       "6         7  2022-12-28  Standard Class   Consumer  United States   \n",
       "\n",
       "              city       state  postal_code region         category  \\\n",
       "0        Henderson    Kentucky        42420  South        Furniture   \n",
       "1        Henderson    Kentucky        42420  South        Furniture   \n",
       "2      Los Angeles  California        90036   West  Office Supplies   \n",
       "3  Fort Lauderdale     Florida        33311  South        Furniture   \n",
       "4  Fort Lauderdale     Florida        33311  South  Office Supplies   \n",
       "5      Los Angeles  California        90032   West        Furniture   \n",
       "6      Los Angeles  California        90032   West  Office Supplies   \n",
       "\n",
       "  sub_category       product_id  cost_price  list_price  quantity  \\\n",
       "0    Bookcases  FUR-BO-10001798         240         260         2   \n",
       "1       Chairs  FUR-CH-10000454         600         730         3   \n",
       "2       Labels  OFF-LA-10000240          10          10         2   \n",
       "3       Tables  FUR-TA-10000577         780         960         5   \n",
       "4      Storage  OFF-ST-10000760          20          20         2   \n",
       "5  Furnishings  FUR-FU-10001487          50          50         7   \n",
       "6          Art  OFF-AR-10002833          10          10         4   \n",
       "\n",
       "   discount_percent  \n",
       "0                 2  \n",
       "1                 3  \n",
       "2                 5  \n",
       "3                 2  \n",
       "4                 5  \n",
       "5                 3  \n",
       "6                 3  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#rename columns names ..make them lower case and replace space with underscore\n",
    "#df.rename(columns={'Order Id':'order_id', 'City':'city'})\n",
    "#Impliment below code for change all the columns at ones\n",
    "df.columns=df.columns.str.lower()\n",
    "df.columns=df.columns.str.replace(' ','_')\n",
    "df.head(7)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "4ef6c75a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>order_date</th>\n",
       "      <th>ship_mode</th>\n",
       "      <th>segment</th>\n",
       "      <th>country</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>postal_code</th>\n",
       "      <th>region</th>\n",
       "      <th>category</th>\n",
       "      <th>sub_category</th>\n",
       "      <th>product_id</th>\n",
       "      <th>cost_price</th>\n",
       "      <th>list_price</th>\n",
       "      <th>quantity</th>\n",
       "      <th>discount_percent</th>\n",
       "      <th>discount</th>\n",
       "      <th>sale_price</th>\n",
       "      <th>profit</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2023-03-01</td>\n",
       "      <td>Second Class</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>United States</td>\n",
       "      <td>Henderson</td>\n",
       "      <td>Kentucky</td>\n",
       "      <td>42420</td>\n",
       "      <td>South</td>\n",
       "      <td>Furniture</td>\n",
       "      <td>Bookcases</td>\n",
       "      <td>FUR-BO-10001798</td>\n",
       "      <td>240</td>\n",
       "      <td>260</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>5.2</td>\n",
       "      <td>254.8</td>\n",
       "      <td>14.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2023-08-15</td>\n",
       "      <td>Second Class</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>United States</td>\n",
       "      <td>Henderson</td>\n",
       "      <td>Kentucky</td>\n",
       "      <td>42420</td>\n",
       "      <td>South</td>\n",
       "      <td>Furniture</td>\n",
       "      <td>Chairs</td>\n",
       "      <td>FUR-CH-10000454</td>\n",
       "      <td>600</td>\n",
       "      <td>730</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>21.9</td>\n",
       "      <td>708.1</td>\n",
       "      <td>108.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>2023-01-10</td>\n",
       "      <td>Second Class</td>\n",
       "      <td>Corporate</td>\n",
       "      <td>United States</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>California</td>\n",
       "      <td>90036</td>\n",
       "      <td>West</td>\n",
       "      <td>Office Supplies</td>\n",
       "      <td>Labels</td>\n",
       "      <td>OFF-LA-10000240</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>9.5</td>\n",
       "      <td>-0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>2022-06-18</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>United States</td>\n",
       "      <td>Fort Lauderdale</td>\n",
       "      <td>Florida</td>\n",
       "      <td>33311</td>\n",
       "      <td>South</td>\n",
       "      <td>Furniture</td>\n",
       "      <td>Tables</td>\n",
       "      <td>FUR-TA-10000577</td>\n",
       "      <td>780</td>\n",
       "      <td>960</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>19.2</td>\n",
       "      <td>940.8</td>\n",
       "      <td>160.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>2022-07-13</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>United States</td>\n",
       "      <td>Fort Lauderdale</td>\n",
       "      <td>Florida</td>\n",
       "      <td>33311</td>\n",
       "      <td>South</td>\n",
       "      <td>Office Supplies</td>\n",
       "      <td>Storage</td>\n",
       "      <td>OFF-ST-10000760</td>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>-1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9989</th>\n",
       "      <td>9990</td>\n",
       "      <td>2023-02-18</td>\n",
       "      <td>Second Class</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>United States</td>\n",
       "      <td>Miami</td>\n",
       "      <td>Florida</td>\n",
       "      <td>33180</td>\n",
       "      <td>South</td>\n",
       "      <td>Furniture</td>\n",
       "      <td>Furnishings</td>\n",
       "      <td>FUR-FU-10001889</td>\n",
       "      <td>30</td>\n",
       "      <td>30</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>1.2</td>\n",
       "      <td>28.8</td>\n",
       "      <td>-1.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9990</th>\n",
       "      <td>9991</td>\n",
       "      <td>2023-03-17</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>United States</td>\n",
       "      <td>Costa Mesa</td>\n",
       "      <td>California</td>\n",
       "      <td>92627</td>\n",
       "      <td>West</td>\n",
       "      <td>Furniture</td>\n",
       "      <td>Furnishings</td>\n",
       "      <td>FUR-FU-10000747</td>\n",
       "      <td>70</td>\n",
       "      <td>90</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>3.6</td>\n",
       "      <td>86.4</td>\n",
       "      <td>16.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9991</th>\n",
       "      <td>9992</td>\n",
       "      <td>2022-08-07</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>United States</td>\n",
       "      <td>Costa Mesa</td>\n",
       "      <td>California</td>\n",
       "      <td>92627</td>\n",
       "      <td>West</td>\n",
       "      <td>Technology</td>\n",
       "      <td>Phones</td>\n",
       "      <td>TEC-PH-10003645</td>\n",
       "      <td>220</td>\n",
       "      <td>260</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>5.2</td>\n",
       "      <td>254.8</td>\n",
       "      <td>34.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9992</th>\n",
       "      <td>9993</td>\n",
       "      <td>2022-11-19</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>United States</td>\n",
       "      <td>Costa Mesa</td>\n",
       "      <td>California</td>\n",
       "      <td>92627</td>\n",
       "      <td>West</td>\n",
       "      <td>Office Supplies</td>\n",
       "      <td>Paper</td>\n",
       "      <td>OFF-PA-10004041</td>\n",
       "      <td>30</td>\n",
       "      <td>30</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>0.9</td>\n",
       "      <td>29.1</td>\n",
       "      <td>-0.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9993</th>\n",
       "      <td>9994</td>\n",
       "      <td>2022-07-17</td>\n",
       "      <td>Second Class</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>United States</td>\n",
       "      <td>Westminster</td>\n",
       "      <td>California</td>\n",
       "      <td>92683</td>\n",
       "      <td>West</td>\n",
       "      <td>Office Supplies</td>\n",
       "      <td>Appliances</td>\n",
       "      <td>OFF-AP-10002684</td>\n",
       "      <td>210</td>\n",
       "      <td>240</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>7.2</td>\n",
       "      <td>232.8</td>\n",
       "      <td>22.8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>9994 rows × 19 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      order_id  order_date       ship_mode    segment        country  \\\n",
       "0            1  2023-03-01    Second Class   Consumer  United States   \n",
       "1            2  2023-08-15    Second Class   Consumer  United States   \n",
       "2            3  2023-01-10    Second Class  Corporate  United States   \n",
       "3            4  2022-06-18  Standard Class   Consumer  United States   \n",
       "4            5  2022-07-13  Standard Class   Consumer  United States   \n",
       "...        ...         ...             ...        ...            ...   \n",
       "9989      9990  2023-02-18    Second Class   Consumer  United States   \n",
       "9990      9991  2023-03-17  Standard Class   Consumer  United States   \n",
       "9991      9992  2022-08-07  Standard Class   Consumer  United States   \n",
       "9992      9993  2022-11-19  Standard Class   Consumer  United States   \n",
       "9993      9994  2022-07-17    Second Class   Consumer  United States   \n",
       "\n",
       "                 city       state  postal_code region         category  \\\n",
       "0           Henderson    Kentucky        42420  South        Furniture   \n",
       "1           Henderson    Kentucky        42420  South        Furniture   \n",
       "2         Los Angeles  California        90036   West  Office Supplies   \n",
       "3     Fort Lauderdale     Florida        33311  South        Furniture   \n",
       "4     Fort Lauderdale     Florida        33311  South  Office Supplies   \n",
       "...               ...         ...          ...    ...              ...   \n",
       "9989            Miami     Florida        33180  South        Furniture   \n",
       "9990       Costa Mesa  California        92627   West        Furniture   \n",
       "9991       Costa Mesa  California        92627   West       Technology   \n",
       "9992       Costa Mesa  California        92627   West  Office Supplies   \n",
       "9993      Westminster  California        92683   West  Office Supplies   \n",
       "\n",
       "     sub_category       product_id  cost_price  list_price  quantity  \\\n",
       "0       Bookcases  FUR-BO-10001798         240         260         2   \n",
       "1          Chairs  FUR-CH-10000454         600         730         3   \n",
       "2          Labels  OFF-LA-10000240          10          10         2   \n",
       "3          Tables  FUR-TA-10000577         780         960         5   \n",
       "4         Storage  OFF-ST-10000760          20          20         2   \n",
       "...           ...              ...         ...         ...       ...   \n",
       "9989  Furnishings  FUR-FU-10001889          30          30         3   \n",
       "9990  Furnishings  FUR-FU-10000747          70          90         2   \n",
       "9991       Phones  TEC-PH-10003645         220         260         2   \n",
       "9992        Paper  OFF-PA-10004041          30          30         4   \n",
       "9993   Appliances  OFF-AP-10002684         210         240         2   \n",
       "\n",
       "      discount_percent  discount  sale_price  profit  \n",
       "0                    2       5.2       254.8    14.8  \n",
       "1                    3      21.9       708.1   108.1  \n",
       "2                    5       0.5         9.5    -0.5  \n",
       "3                    2      19.2       940.8   160.8  \n",
       "4                    5       1.0        19.0    -1.0  \n",
       "...                ...       ...         ...     ...  \n",
       "9989                 4       1.2        28.8    -1.2  \n",
       "9990                 4       3.6        86.4    16.4  \n",
       "9991                 2       5.2       254.8    34.8  \n",
       "9992                 3       0.9        29.1    -0.9  \n",
       "9993                 3       7.2       232.8    22.8  \n",
       "\n",
       "[9994 rows x 19 columns]"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#derive new columns discount ,sale price and profit\n",
    "df['discount']=df['list_price']*df['discount_percent']*.01 #New column discount\n",
    "df['sale_price']= df['list_price']-df['discount'] #New column sale_price\n",
    "df['profit']=df['sale_price']-df['cost_price'] #New column profit\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "d0ed14e0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "order_id              int64\n",
       "order_date           object\n",
       "ship_mode            object\n",
       "segment              object\n",
       "country              object\n",
       "city                 object\n",
       "state                object\n",
       "postal_code           int64\n",
       "region               object\n",
       "category             object\n",
       "sub_category         object\n",
       "product_id           object\n",
       "cost_price            int64\n",
       "list_price            int64\n",
       "quantity              int64\n",
       "discount_percent      int64\n",
       "discount            float64\n",
       "sale_price          float64\n",
       "profit              float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#convert order date from object data type to datetime\n",
    "df.dtypes #here orderdate is in object formate and we need it in date time format"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "a9eb2607",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "order_id                     int64\n",
       "order_date          datetime64[ns]\n",
       "ship_mode                   object\n",
       "segment                     object\n",
       "country                     object\n",
       "city                        object\n",
       "state                       object\n",
       "postal_code                  int64\n",
       "region                      object\n",
       "category                    object\n",
       "sub_category                object\n",
       "product_id                  object\n",
       "cost_price                   int64\n",
       "list_price                   int64\n",
       "quantity                     int64\n",
       "discount_percent             int64\n",
       "discount                   float64\n",
       "sale_price                 float64\n",
       "profit                     float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#convert order date from object data type to datetime\n",
    "df['order_date']= pd.to_datetime(df['order_date'],format=\"%Y-%m-%d\")\n",
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "184575e1",
   "metadata": {},
   "outputs": [],
   "source": [
    "#drop cost price list price and discount percent columns\n",
    "df.drop(columns=['list_price','cost_price','discount_percent'],inplace=True) #inplace=True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "4f09cb6b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>order_date</th>\n",
       "      <th>ship_mode</th>\n",
       "      <th>segment</th>\n",
       "      <th>country</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>postal_code</th>\n",
       "      <th>region</th>\n",
       "      <th>category</th>\n",
       "      <th>sub_category</th>\n",
       "      <th>product_id</th>\n",
       "      <th>quantity</th>\n",
       "      <th>discount</th>\n",
       "      <th>sale_price</th>\n",
       "      <th>profit</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2023-03-01</td>\n",
       "      <td>Second Class</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>United States</td>\n",
       "      <td>Henderson</td>\n",
       "      <td>Kentucky</td>\n",
       "      <td>42420</td>\n",
       "      <td>South</td>\n",
       "      <td>Furniture</td>\n",
       "      <td>Bookcases</td>\n",
       "      <td>FUR-BO-10001798</td>\n",
       "      <td>2</td>\n",
       "      <td>5.2</td>\n",
       "      <td>254.8</td>\n",
       "      <td>14.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2023-08-15</td>\n",
       "      <td>Second Class</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>United States</td>\n",
       "      <td>Henderson</td>\n",
       "      <td>Kentucky</td>\n",
       "      <td>42420</td>\n",
       "      <td>South</td>\n",
       "      <td>Furniture</td>\n",
       "      <td>Chairs</td>\n",
       "      <td>FUR-CH-10000454</td>\n",
       "      <td>3</td>\n",
       "      <td>21.9</td>\n",
       "      <td>708.1</td>\n",
       "      <td>108.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>2023-01-10</td>\n",
       "      <td>Second Class</td>\n",
       "      <td>Corporate</td>\n",
       "      <td>United States</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>California</td>\n",
       "      <td>90036</td>\n",
       "      <td>West</td>\n",
       "      <td>Office Supplies</td>\n",
       "      <td>Labels</td>\n",
       "      <td>OFF-LA-10000240</td>\n",
       "      <td>2</td>\n",
       "      <td>0.5</td>\n",
       "      <td>9.5</td>\n",
       "      <td>-0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>2022-06-18</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>United States</td>\n",
       "      <td>Fort Lauderdale</td>\n",
       "      <td>Florida</td>\n",
       "      <td>33311</td>\n",
       "      <td>South</td>\n",
       "      <td>Furniture</td>\n",
       "      <td>Tables</td>\n",
       "      <td>FUR-TA-10000577</td>\n",
       "      <td>5</td>\n",
       "      <td>19.2</td>\n",
       "      <td>940.8</td>\n",
       "      <td>160.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>2022-07-13</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>United States</td>\n",
       "      <td>Fort Lauderdale</td>\n",
       "      <td>Florida</td>\n",
       "      <td>33311</td>\n",
       "      <td>South</td>\n",
       "      <td>Office Supplies</td>\n",
       "      <td>Storage</td>\n",
       "      <td>OFF-ST-10000760</td>\n",
       "      <td>2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>-1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9989</th>\n",
       "      <td>9990</td>\n",
       "      <td>2023-02-18</td>\n",
       "      <td>Second Class</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>United States</td>\n",
       "      <td>Miami</td>\n",
       "      <td>Florida</td>\n",
       "      <td>33180</td>\n",
       "      <td>South</td>\n",
       "      <td>Furniture</td>\n",
       "      <td>Furnishings</td>\n",
       "      <td>FUR-FU-10001889</td>\n",
       "      <td>3</td>\n",
       "      <td>1.2</td>\n",
       "      <td>28.8</td>\n",
       "      <td>-1.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9990</th>\n",
       "      <td>9991</td>\n",
       "      <td>2023-03-17</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>United States</td>\n",
       "      <td>Costa Mesa</td>\n",
       "      <td>California</td>\n",
       "      <td>92627</td>\n",
       "      <td>West</td>\n",
       "      <td>Furniture</td>\n",
       "      <td>Furnishings</td>\n",
       "      <td>FUR-FU-10000747</td>\n",
       "      <td>2</td>\n",
       "      <td>3.6</td>\n",
       "      <td>86.4</td>\n",
       "      <td>16.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9991</th>\n",
       "      <td>9992</td>\n",
       "      <td>2022-08-07</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>United States</td>\n",
       "      <td>Costa Mesa</td>\n",
       "      <td>California</td>\n",
       "      <td>92627</td>\n",
       "      <td>West</td>\n",
       "      <td>Technology</td>\n",
       "      <td>Phones</td>\n",
       "      <td>TEC-PH-10003645</td>\n",
       "      <td>2</td>\n",
       "      <td>5.2</td>\n",
       "      <td>254.8</td>\n",
       "      <td>34.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9992</th>\n",
       "      <td>9993</td>\n",
       "      <td>2022-11-19</td>\n",
       "      <td>Standard Class</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>United States</td>\n",
       "      <td>Costa Mesa</td>\n",
       "      <td>California</td>\n",
       "      <td>92627</td>\n",
       "      <td>West</td>\n",
       "      <td>Office Supplies</td>\n",
       "      <td>Paper</td>\n",
       "      <td>OFF-PA-10004041</td>\n",
       "      <td>4</td>\n",
       "      <td>0.9</td>\n",
       "      <td>29.1</td>\n",
       "      <td>-0.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9993</th>\n",
       "      <td>9994</td>\n",
       "      <td>2022-07-17</td>\n",
       "      <td>Second Class</td>\n",
       "      <td>Consumer</td>\n",
       "      <td>United States</td>\n",
       "      <td>Westminster</td>\n",
       "      <td>California</td>\n",
       "      <td>92683</td>\n",
       "      <td>West</td>\n",
       "      <td>Office Supplies</td>\n",
       "      <td>Appliances</td>\n",
       "      <td>OFF-AP-10002684</td>\n",
       "      <td>2</td>\n",
       "      <td>7.2</td>\n",
       "      <td>232.8</td>\n",
       "      <td>22.8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>9994 rows × 16 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      order_id order_date       ship_mode    segment        country  \\\n",
       "0            1 2023-03-01    Second Class   Consumer  United States   \n",
       "1            2 2023-08-15    Second Class   Consumer  United States   \n",
       "2            3 2023-01-10    Second Class  Corporate  United States   \n",
       "3            4 2022-06-18  Standard Class   Consumer  United States   \n",
       "4            5 2022-07-13  Standard Class   Consumer  United States   \n",
       "...        ...        ...             ...        ...            ...   \n",
       "9989      9990 2023-02-18    Second Class   Consumer  United States   \n",
       "9990      9991 2023-03-17  Standard Class   Consumer  United States   \n",
       "9991      9992 2022-08-07  Standard Class   Consumer  United States   \n",
       "9992      9993 2022-11-19  Standard Class   Consumer  United States   \n",
       "9993      9994 2022-07-17    Second Class   Consumer  United States   \n",
       "\n",
       "                 city       state  postal_code region         category  \\\n",
       "0           Henderson    Kentucky        42420  South        Furniture   \n",
       "1           Henderson    Kentucky        42420  South        Furniture   \n",
       "2         Los Angeles  California        90036   West  Office Supplies   \n",
       "3     Fort Lauderdale     Florida        33311  South        Furniture   \n",
       "4     Fort Lauderdale     Florida        33311  South  Office Supplies   \n",
       "...               ...         ...          ...    ...              ...   \n",
       "9989            Miami     Florida        33180  South        Furniture   \n",
       "9990       Costa Mesa  California        92627   West        Furniture   \n",
       "9991       Costa Mesa  California        92627   West       Technology   \n",
       "9992       Costa Mesa  California        92627   West  Office Supplies   \n",
       "9993      Westminster  California        92683   West  Office Supplies   \n",
       "\n",
       "     sub_category       product_id  quantity  discount  sale_price  profit  \n",
       "0       Bookcases  FUR-BO-10001798         2       5.2       254.8    14.8  \n",
       "1          Chairs  FUR-CH-10000454         3      21.9       708.1   108.1  \n",
       "2          Labels  OFF-LA-10000240         2       0.5         9.5    -0.5  \n",
       "3          Tables  FUR-TA-10000577         5      19.2       940.8   160.8  \n",
       "4         Storage  OFF-ST-10000760         2       1.0        19.0    -1.0  \n",
       "...           ...              ...       ...       ...         ...     ...  \n",
       "9989  Furnishings  FUR-FU-10001889         3       1.2        28.8    -1.2  \n",
       "9990  Furnishings  FUR-FU-10000747         2       3.6        86.4    16.4  \n",
       "9991       Phones  TEC-PH-10003645         2       5.2       254.8    34.8  \n",
       "9992        Paper  OFF-PA-10004041         4       0.9        29.1    -0.9  \n",
       "9993   Appliances  OFF-AP-10002684         2       7.2       232.8    22.8  \n",
       "\n",
       "[9994 rows x 16 columns]"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "d6b9a9ef",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save the updated DataFrame to a new CSV file for loading it into ssms manually \n",
    "df.to_csv('updated_orders.csv', index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}