Code.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "04cfea27-1d00-4c1d-81ec-d9a37d605b84",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "#importing necessary packages\n",
    "import kaggle\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "f9793b8a-bfa2-4418-aeb0-cdd6e05fedad",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Dataset URL: https://www.kaggle.com/datasets/ankitbansal06/retail-orders\n",
      "License(s): CC0-1.0\n",
      "orders.csv.zip: Skipping, found more recently modified local copy (use --force to force download)\n"
     ]
    }
   ],
   "source": [
    "#used to download zipfile - dataset which are available in kaggle\n",
    "!kaggle datasets download ankitbansal06/retail-orders -f orders.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "fe430335-9337-468f-9b31-e236983564bf",
   "metadata": {},
   "outputs": [],
   "source": [
    "#extract file from zip file\n",
    "import zipfile\n",
    "zip = zipfile.ZipFile('orders.csv.zip')\n",
    "zip.extractall()\n",
    "zip.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "68af8a87-f6e2-42ea-9f99-3cb6ce5551fa",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Second Class', 'Standard Class', nan, 'First Class', 'Same Day'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#read a csv and handle null values present in the dataset\n",
    "df = pd.read_csv('orders.csv', na_values=['Not Available', 'unknown'])\n",
    "#checking how much values available inside the ship mode column\n",
    "df['Ship Mode'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "d7aa2630-ec63-41ce-878d-213460e1042e",
   "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",
       "  </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",
       "\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",
       "  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",
       "   discount_percent  \n",
       "0                 2  \n",
       "1                 3  \n",
       "2                 5  \n",
       "3                 2  \n",
       "4                 5  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#rename column names - make all into lower case & replace with underscore\n",
    "#high time complexity, because takes time to change all columns\n",
    "\n",
    "#df.rename(columns = {'Order Id' : 'order_id'})\n",
    "\n",
    "#instead change into str and use lower()\n",
    "df.columns = df.columns.str.lower()\n",
    "\n",
    "#replace all spaces with underscore\n",
    "df.columns = df.columns.str.replace(' ', '_')\n",
    "df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "9d6ebf7d-d7f7-4403-93e4-ae524d3b7a39",
   "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",
       "  </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",
       "\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",
       "  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",
       "   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  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# creating a new column discount\n",
    "df['discount'] = df['list_price'] * df['discount_percent'] / 100\n",
    "\n",
    "#creating sale price (list_price - discount)\n",
    "df['sale_price'] = df['list_price'] - df['discount']\n",
    "\n",
    "#find the total profit (sale_price - cost_price)\n",
    "df['profit'] = df['sale_price'] - df['cost_price']\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "91978d4d-c53b-4109-8fad-4719d0dac371",
   "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": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#converting order_date from object to datetime\n",
    "df.dtypes\n",
    "df['order_date'] = pd.to_datetime(df['order_date'], format = \"%Y-%m-%d\")\n",
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "ef4cf4cc-3572-4518-8bef-42dc67d60437",
   "metadata": {},
   "outputs": [],
   "source": [
    "#droping the unnecessary columns\n",
    "df.drop(columns = ['list_price', 'cost_price', 'discount_percent'], inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "85fdbb48-b235-405d-8d63-31da4ebee7db",
   "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",
       "  </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",
       "\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",
       "  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  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "1ed05eb2-13b4-4f53-b558-08128051db7e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: mysql-connector-python in c:\\users\\krisa\\appdata\\local\\programs\\python\\python38\\lib\\site-packages (8.3.0)\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\n",
      "[notice] A new release of pip is available: 24.2 -> 24.3.1\n",
      "[notice] To update, run: python.exe -m pip install --upgrade pip\n"
     ]
    }
   ],
   "source": [
    "#installing required packages\n",
    "!pip install mysql-connector-python"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "76bddba3-df0e-4283-9540-ad1c23209e92",
   "metadata": {},
   "outputs": [],
   "source": [
    "#importing required libraries\n",
    "import mysql.connector\n",
    "import os\n",
    "\n",
    "#connecting python with mysql database\n",
    "mydb = mysql.connector.connect(host = \"localhost\", user = \"root\", password = \"1234\", database = \"project\", use_pure = True)    \n",
    "mycursor = mydb.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "c4acfc41-c72f-4d07-8108-595055857b77",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine\n",
    "import pandas as pd\n",
    "\n",
    "# Define connection parameters\n",
    "username = 'root'\n",
    "password = '1234'\n",
    "host = 'localhost'\n",
    "database = \"project\"\n",
    "\n",
    "# Create an SQLAlchemy engine\n",
    "engine = create_engine(f'mysql+mysqlconnector://{username}:{password}@{host}/{database}')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cedb3436-55cd-4329-a01a-083ebc6ddc51",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "288f7c7c-7a45-4b2e-927e-860f8c07fdab",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "53b30f4b-73e7-4dc4-8920-782d7c75a115",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}