Final Python Code .ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "19ef9c3f-cfad-4895-a405-f34c1a91c087",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: kaggle in c:\\users\\asus\\appdata\\local\\programs\\python\\python310\\lib\\site-packages (1.6.14)"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\n",
      "[notice] A new release of pip available: 22.3.1 -> 24.1.1\n",
      "[notice] To update, run: python.exe -m pip install --upgrade pip\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Requirement already satisfied: six>=1.10 in c:\\users\\asus\\appdata\\local\\programs\\python\\python310\\lib\\site-packages (from kaggle) (1.16.0)\n",
      "Requirement already satisfied: certifi>=2023.7.22 in c:\\users\\asus\\appdata\\local\\programs\\python\\python310\\lib\\site-packages (from kaggle) (2024.7.4)\n",
      "Requirement already satisfied: python-dateutil in c:\\users\\asus\\appdata\\local\\programs\\python\\python310\\lib\\site-packages (from kaggle) (2.9.0.post0)\n",
      "Requirement already satisfied: requests in c:\\users\\asus\\appdata\\local\\programs\\python\\python310\\lib\\site-packages (from kaggle) (2.32.3)\n",
      "Requirement already satisfied: tqdm in c:\\users\\asus\\appdata\\local\\programs\\python\\python310\\lib\\site-packages (from kaggle) (4.66.4)\n",
      "Requirement already satisfied: python-slugify in c:\\users\\asus\\appdata\\local\\programs\\python\\python310\\lib\\site-packages (from kaggle) (8.0.4)\n",
      "Requirement already satisfied: urllib3 in c:\\users\\asus\\appdata\\local\\programs\\python\\python310\\lib\\site-packages (from kaggle) (2.2.2)\n",
      "Requirement already satisfied: bleach in c:\\users\\asus\\appdata\\local\\programs\\python\\python310\\lib\\site-packages (from kaggle) (6.1.0)\n",
      "Requirement already satisfied: webencodings in c:\\users\\asus\\appdata\\local\\programs\\python\\python310\\lib\\site-packages (from bleach->kaggle) (0.5.1)\n",
      "Requirement already satisfied: text-unidecode>=1.3 in c:\\users\\asus\\appdata\\local\\programs\\python\\python310\\lib\\site-packages (from python-slugify->kaggle) (1.3)\n",
      "Requirement already satisfied: charset-normalizer<4,>=2 in c:\\users\\asus\\appdata\\local\\programs\\python\\python310\\lib\\site-packages (from requests->kaggle) (3.3.2)\n",
      "Requirement already satisfied: idna<4,>=2.5 in c:\\users\\asus\\appdata\\local\\programs\\python\\python310\\lib\\site-packages (from requests->kaggle) (3.7)\n",
      "Requirement already satisfied: colorama in c:\\users\\asus\\appdata\\local\\programs\\python\\python310\\lib\\site-packages (from tqdm->kaggle) (0.4.6)\n"
     ]
    }
   ],
   "source": [
    "\n",
    "!pip install kaggle \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "22c4084c-fc15-4177-a096-789e907765e9",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "import kaggle \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "f5e7d7c3-2024-4b42-ba61-2cbe5df975d5",
   "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": [
    "#downloading  dataset orders.csv using kaggle API\n",
    "\n",
    "!kaggle datasets download ankitbansal06/retail-orders -f orders.csv\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "72e30b66-cab8-4ad2-b453-629bd8d8244b",
   "metadata": {},
   "outputs": [],
   "source": [
    "#extracting file from zip file \n",
    "\n",
    "import zipfile\n",
    "zip_ref = zipfile.ZipFile('orders.csv.zip') \n",
    "zip_ref.extractall() # extract file to dir\n",
    "zip_ref.close() # close file\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "id": "98e84874-05ba-4a19-9de3-1c4ea439c1d7",
   "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",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Order Id  Order Date     Ship Mode   Segment        Country       City  \\\n",
       "0         1  2023-03-01  Second Class  Consumer  United States  Henderson   \n",
       "1         2  2023-08-15  Second Class  Consumer  United States  Henderson   \n",
       "\n",
       "      State  Postal Code Region   Category Sub Category       Product Id  \\\n",
       "0  Kentucky        42420  South  Furniture    Bookcases  FUR-BO-10001798   \n",
       "1  Kentucky        42420  South  Furniture       Chairs  FUR-CH-10000454   \n",
       "\n",
       "   cost price  List Price  Quantity  Discount Percent  \n",
       "0         240         260         2                 2  \n",
       "1         600         730         3                 3  "
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#read orders csv file in deef dataframe just to check data \n",
    "\n",
    "import pandas as pd \n",
    "deef= pd.read_csv('orders.csv')\n",
    "deef.head(2)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "id": "b79f571c-7d4a-4423-8b41-3b41c27ad6a9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Second Class', 'Standard Class', nan, 'First Class', 'Same Day'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 131,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#read data from the file and handle null values\n",
    "\n",
    "import pandas as pd\n",
    "df = pd.read_csv('orders.csv',na_values=['Not Available','unknown'])\n",
    "df['Ship Mode'].unique()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "id": "cd058152-b1b5-4338-86fc-0c37dabad8cd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Order Id            9994\n",
       "Order Date          9994\n",
       "Ship Mode           9988\n",
       "Segment             9994\n",
       "Country             9994\n",
       "City                9994\n",
       "State               9994\n",
       "Postal Code         9994\n",
       "Region              9994\n",
       "Category            9994\n",
       "Sub Category        9994\n",
       "Product Id          9994\n",
       "cost price          9994\n",
       "List Price          9994\n",
       "Quantity            9994\n",
       "Discount Percent    9994\n",
       "dtype: int64"
      ]
     },
     "execution_count": 132,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "df.count()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "id": "80f02681-c0a4-4b3f-97d2-07c2589a47df",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Order Id', 'Order Date', 'Ship Mode', 'Segment', 'Country', 'City',\n",
       "       'State', 'Postal Code', 'Region', 'Category', 'Sub Category',\n",
       "       'Product Id', 'cost price', 'List Price', 'Quantity',\n",
       "       'Discount Percent'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 133,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "df.columns\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "id": "9ab8a640-4394-4718-8ad2-a7097e464dc9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['order id', 'order date', 'ship mode', 'segment', 'country', 'city',\n",
       "       'state', 'postal code', 'region', 'category', 'sub category',\n",
       "       'product id', 'cost price', 'list price', 'quantity',\n",
       "       'discount percent'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 134,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "df.columns = df.columns.str.lower()\n",
    "df.columns\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "id": "f5e70f2e-7d30-4c89-8956-682796143aa2",
   "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",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [order_id, order_date, ship_mode, segment, country, city, state, postal_code, region, category, sub_category, product_id, cost_price, list_price, quantity, discount_percent]\n",
       "Index: []"
      ]
     },
     "execution_count": 135,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "df.columns = df.columns.str.replace(' ', '_')\n",
    "df.head(0)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "id": "47a819c9-34ac-490b-bc55-57ef1212d4f4",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "df['discount'] = df['list_price'] * df['discount_percent'] * .01\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "id": "ee62e11f-1527-4e9f-80d9-7f48d9d6015d",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "df['sale_price'] = df['list_price'] - df['discount']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "id": "517aec5f-cf4f-4a3f-bed6-6428bedf2a39",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "df['profit'] = df['sale_price'] - df['cost_price']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "id": "c8d1965f-918e-474f-b6cf-849ac1decb3e",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "df['total_sale_price'] = df['sale_price'] * df['quantity']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "id": "90ec69d0-f82a-4faf-b5de-e745c33f3f22",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "df['total_profit'] = df['profit'] * df['quantity']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "id": "cd311b8e-d4f1-49bf-9a03-bae971ae4efe",
   "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>...</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",
       "      <th>total_sale_price</th>\n",
       "      <th>total_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>...</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",
       "      <td>509.6</td>\n",
       "      <td>29.6</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>...</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",
       "      <td>2124.3</td>\n",
       "      <td>324.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>...</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",
       "      <td>19.0</td>\n",
       "      <td>-1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   order_id  order_date     ship_mode    segment        country         city  \\\n",
       "0         1  2023-03-01  Second Class   Consumer  United States    Henderson   \n",
       "1         2  2023-08-15  Second Class   Consumer  United States    Henderson   \n",
       "2         3  2023-01-10  Second Class  Corporate  United States  Los Angeles   \n",
       "\n",
       "        state  postal_code region         category  ...       product_id  \\\n",
       "0    Kentucky        42420  South        Furniture  ...  FUR-BO-10001798   \n",
       "1    Kentucky        42420  South        Furniture  ...  FUR-CH-10000454   \n",
       "2  California        90036   West  Office Supplies  ...  OFF-LA-10000240   \n",
       "\n",
       "  cost_price  list_price  quantity  discount_percent  discount  sale_price  \\\n",
       "0        240         260         2                 2       5.2       254.8   \n",
       "1        600         730         3                 3      21.9       708.1   \n",
       "2         10          10         2                 5       0.5         9.5   \n",
       "\n",
       "   profit  total_sale_price  total_profit  \n",
       "0    14.8             509.6          29.6  \n",
       "1   108.1            2124.3         324.3  \n",
       "2    -0.5              19.0          -1.0  \n",
       "\n",
       "[3 rows x 21 columns]"
      ]
     },
     "execution_count": 141,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "id": "616c8246-977a-491d-8411-593528116d36",
   "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",
       "total_sale_price    float64\n",
       "total_profit        float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 142,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "df.dtypes\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "id": "4ce1347d-0ebe-4b3f-9cb9-fedc1d5cbd91",
   "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>...</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",
       "      <th>total_sale_price</th>\n",
       "      <th>total_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>...</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",
       "      <td>509.6</td>\n",
       "      <td>29.6</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>...</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",
       "      <td>2124.3</td>\n",
       "      <td>324.3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   order_id order_date     ship_mode   segment        country       city  \\\n",
       "0         1 2023-03-01  Second Class  Consumer  United States  Henderson   \n",
       "1         2 2023-08-15  Second Class  Consumer  United States  Henderson   \n",
       "\n",
       "      state  postal_code region   category  ...       product_id cost_price  \\\n",
       "0  Kentucky        42420  South  Furniture  ...  FUR-BO-10001798        240   \n",
       "1  Kentucky        42420  South  Furniture  ...  FUR-CH-10000454        600   \n",
       "\n",
       "   list_price  quantity  discount_percent  discount  sale_price  profit  \\\n",
       "0         260         2                 2       5.2       254.8    14.8   \n",
       "1         730         3                 3      21.9       708.1   108.1   \n",
       "\n",
       "   total_sale_price  total_profit  \n",
       "0             509.6          29.6  \n",
       "1            2124.3         324.3  \n",
       "\n",
       "[2 rows x 21 columns]"
      ]
     },
     "execution_count": 143,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "\n",
    "#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.head(2)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "id": "cbe9be8f-8aba-4305-a478-8b2861340283",
   "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",
       "total_sale_price           float64\n",
       "total_profit               float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 144,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "df.dtypes\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "id": "ecdc5156-bc76-4dcb-9675-eb8187034535",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "#drop cost price list price and discount percent columns\n",
    "df.drop(columns=['list_price','cost_price','discount_percent'],inplace=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "id": "80f09be9-f0e7-44a1-bca6-8643036717bc",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "from sqlalchemy import create_engine\n",
    "import pandas as pd \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 147,
   "id": "3d0fb226-0099-4158-9b10-9a4213f2ada0",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "/creating connection with sql server using odbc driver 17 and window authentication using pyodbc \n",
    "\n",
    "server = 'DESKTOP-GGDI84S\\SQLEXPRESS'\n",
    "database = 'project4python_sql'\n",
    "driver = 'ODBC Driver 17 for SQL Server'\n",
    "# password = \n",
    "# username = \n",
    "\n",
    "# database_connection= f'mssql:// {username} : {password} @{server} /{database}?driver={driver}'\n",
    "\n",
    "database_connection = f'mssql+pyodbc://@{server}/{database}?driver={driver}&trusted_connection=yes'\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "id": "1ce7a5b4-0c8f-4fbb-955a-64aae8f35cf9",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "\n",
    "engine= create_engine(database_connection)\n",
    "connection = engine.connect()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "id": "6cb067b9-614b-48ac-9601-01089c06126f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "18"
      ]
     },
     "execution_count": 127,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "# writing data into sql table \n",
    "df.to_sql('df_orders', con=connection , index=False, if_exists = 'append'  )\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "id": "83660e8e-d747-4d12-92b4-159179ce50d3",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# after reading data connection is closed \n",
    "closeed = connection.close()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "f1a8d942-d7e6-4d37-968a-923f56b8fbcb",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   emp_id   name salary  dept_id\n",
      "0     101  sohan   3000       11\n",
      "1     102  rohan   4000       12\n",
      "2     103  mohan   5000       13\n",
      "3     104    cat   3000       11\n"
     ]
    }
   ],
   "source": [
    "\n",
    "# reading data from other table from sql server database \n",
    "\n",
    "# # Example query to test the connection\n",
    "# query = \"SELECT * FROM emp_salary\"\n",
    "\n",
    "# # Read data into a pandas DataFrame\n",
    "# df = pd.read_sql(query, engine)\n",
    "\n",
    "# # Display the DataFrame\n",
    "# print(df.head(4))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "d22ed7a3-ff1c-40f6-a7eb-9c4343d9ac8c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   column1 column2  numeric_column\n",
      "0        1       A            10.1\n",
      "1        2       B            20.2\n",
      "2        3       C            30.3\n"
     ]
    }
   ],
   "source": [
    "\n",
    "# checking whether this way is working or not using random data \n",
    "\n",
    "# Example DataFrame\n",
    "data = {\n",
    "    'column1': [1, 2, 3],\n",
    "    'column2': ['A', 'B', 'C'],\n",
    "    'numeric_column': [10.1, 20.2, 30.3]\n",
    "}\n",
    "df1 = pd.DataFrame(data)\n",
    "\n",
    "# Ensure numeric columns are properly formatted\n",
    "df1['numeric_column'] = df1['numeric_column'].astype('float64')\n",
    "\n",
    "# Write DataFrame to SQL Server\n",
    "df1.to_sql('random_data', con=engine, index=False, if_exists='append')\n",
    "\n",
    "# Optional: Verify by reading from the table\n",
    "query = \"SELECT * FROM RANDOM_DATA\"\n",
    "\n",
    "df_from_sql = pd.read_sql(query, engine)\n",
    "\n",
    "print(df_from_sql.head())\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "700e28d0-c668-4e13-8931-40c6be003d1b",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# another way of loading data into sql table \n",
    "\n",
    "# #load the data into sql server using replace option\n",
    "# import sqlalchemy as sal\n",
    "\n",
    "# engine = sal.create_engine('mssql://DESKTOP-GGDI84S\\SQLEXPRESS/project4python_sql?driver=SQL+SERVER')\n",
    "# conn=engine.connect()\n",
    "\n",
    "#load the data into sql server using append option\n",
    "# data_types = {'order_id': sal.Integer(), 'order_date': sal.Date() , 'ship_mode': sal.String(100) , 'segment': sal.String(100) ,\n",
    "#               'country': sal.String() , 'city': sal.String(100) , 'state': sal.String(100) ,  'postal_code': sal.Integer()\n",
    "#  , 'region': sal.String(100),  'category': sal.String(100),\n",
    "#   'sub_category': sal.String(100),  'product_id': sal.String(200)  ,  'quantity': sal.Integer(), \n",
    "#               'discount': sal.Float(), 'sale_price': sal.Float(), 'profit': sal.Float(),  }\n",
    "\n",
    "\n",
    "# df.to_sql( name= 'df_orders', con=conn , index=False , dtype=data_types, if_exists = 'append')\n"
   ]
  }
 ],
 "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.10.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}