Task1.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "be3acf41",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "1d2db4bb",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Order_Report\n",
      "Pincode_Zones\n",
      "SKU_Master\n",
      "Invoice\n",
      "Rates\n"
     ]
    }
   ],
   "source": [
    "c=0\n",
    "title=[]\n",
    "for i in os.listdir():\n",
    "    if \".xlsx\" in i and i != \"Expected_Result.xlsx\" and i != \"DATA1.xlsx\" and i != \"Summary.xlsx\":\n",
    "        x=\"\"\"{}=pd.read_excel('{}')\"\"\".format(i.split(\"-\")[1].split('.')[0].strip().replace(\" \",\"_\"),i)\n",
    "        #print(x)\n",
    "        c=c+1\n",
    "        exec(x)\n",
    "        print(i.split(\"-\")[1].split('.')[0].strip().replace(\" \",\"_\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "c4bd5624",
   "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>ExternOrderNo</th>\n",
       "      <th>SKU</th>\n",
       "      <th>Order Qty</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2001827036</td>\n",
       "      <td>8904223818706</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2001827036</td>\n",
       "      <td>8904223819093</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2001827036</td>\n",
       "      <td>8904223819109</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2001827036</td>\n",
       "      <td>8904223818430</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2001827036</td>\n",
       "      <td>8904223819277</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ExternOrderNo            SKU  Order Qty\n",
       "0     2001827036  8904223818706        1.0\n",
       "1     2001827036  8904223819093        1.0\n",
       "2     2001827036  8904223819109        1.0\n",
       "3     2001827036  8904223818430        1.0\n",
       "4     2001827036  8904223819277        1.0"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Order_Report.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "fdb6d7f2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(400, 3)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Order_Report.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "18dc6803",
   "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>Warehouse Pincode</th>\n",
       "      <th>Customer Pincode</th>\n",
       "      <th>Zone</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>121003</td>\n",
       "      <td>507101</td>\n",
       "      <td>d</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>121003</td>\n",
       "      <td>486886</td>\n",
       "      <td>d</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>121003</td>\n",
       "      <td>532484</td>\n",
       "      <td>d</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>121003</td>\n",
       "      <td>143001</td>\n",
       "      <td>b</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>121003</td>\n",
       "      <td>515591</td>\n",
       "      <td>d</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Warehouse Pincode  Customer Pincode Zone\n",
       "0             121003            507101    d\n",
       "1             121003            486886    d\n",
       "2             121003            532484    d\n",
       "3             121003            143001    b\n",
       "4             121003            515591    d"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Pincode_Zones.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "6c4f5d59",
   "metadata": {},
   "outputs": [],
   "source": [
    "Pincode_Zones.drop_duplicates(subset='Customer Pincode', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "a2ec16a4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(108, 3)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Pincode_Zones.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "dc32005f",
   "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>SKU</th>\n",
       "      <th>Weight (g)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8904223815682</td>\n",
       "      <td>210</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8904223815859</td>\n",
       "      <td>165</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8904223815866</td>\n",
       "      <td>113</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8904223815873</td>\n",
       "      <td>65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8904223816214</td>\n",
       "      <td>120</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             SKU  Weight (g)\n",
       "0  8904223815682         210\n",
       "1  8904223815859         165\n",
       "2  8904223815866         113\n",
       "3  8904223815873          65\n",
       "4  8904223816214         120"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SKU_Master.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "a1669d2e",
   "metadata": {},
   "outputs": [],
   "source": [
    "SKU_Master.drop_duplicates(subset='SKU', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "7da916ee",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(65, 2)"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SKU_Master.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "318f8f82",
   "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>AWB Code</th>\n",
       "      <th>Order ID</th>\n",
       "      <th>Charged Weight</th>\n",
       "      <th>Warehouse Pincode</th>\n",
       "      <th>Customer Pincode</th>\n",
       "      <th>Zone</th>\n",
       "      <th>Type of Shipment</th>\n",
       "      <th>Billing Amount (Rs.)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1091117222124</td>\n",
       "      <td>2001806232</td>\n",
       "      <td>1.30</td>\n",
       "      <td>121003</td>\n",
       "      <td>507101</td>\n",
       "      <td>d</td>\n",
       "      <td>Forward charges</td>\n",
       "      <td>135.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1091117222194</td>\n",
       "      <td>2001806273</td>\n",
       "      <td>1.00</td>\n",
       "      <td>121003</td>\n",
       "      <td>486886</td>\n",
       "      <td>d</td>\n",
       "      <td>Forward charges</td>\n",
       "      <td>90.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1091117222931</td>\n",
       "      <td>2001806408</td>\n",
       "      <td>2.50</td>\n",
       "      <td>121003</td>\n",
       "      <td>532484</td>\n",
       "      <td>d</td>\n",
       "      <td>Forward charges</td>\n",
       "      <td>224.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1091117223244</td>\n",
       "      <td>2001806458</td>\n",
       "      <td>1.00</td>\n",
       "      <td>121003</td>\n",
       "      <td>143001</td>\n",
       "      <td>b</td>\n",
       "      <td>Forward charges</td>\n",
       "      <td>61.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1091117229345</td>\n",
       "      <td>2001807012</td>\n",
       "      <td>0.15</td>\n",
       "      <td>121003</td>\n",
       "      <td>515591</td>\n",
       "      <td>d</td>\n",
       "      <td>Forward charges</td>\n",
       "      <td>45.4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        AWB Code    Order ID  Charged Weight  Warehouse Pincode  \\\n",
       "0  1091117222124  2001806232            1.30             121003   \n",
       "1  1091117222194  2001806273            1.00             121003   \n",
       "2  1091117222931  2001806408            2.50             121003   \n",
       "3  1091117223244  2001806458            1.00             121003   \n",
       "4  1091117229345  2001807012            0.15             121003   \n",
       "\n",
       "   Customer Pincode Zone Type of Shipment  Billing Amount (Rs.)  \n",
       "0            507101    d  Forward charges                 135.0  \n",
       "1            486886    d  Forward charges                  90.2  \n",
       "2            532484    d  Forward charges                 224.6  \n",
       "3            143001    b  Forward charges                  61.3  \n",
       "4            515591    d  Forward charges                  45.4  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Invoice.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "5b78288b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(124, 8)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Invoice.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "d20f462d",
   "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>fwd_a_fixed</th>\n",
       "      <th>fwd_a_additional</th>\n",
       "      <th>fwd_b_fixed</th>\n",
       "      <th>fwd_b_additional</th>\n",
       "      <th>fwd_c_fixed</th>\n",
       "      <th>fwd_c_additional</th>\n",
       "      <th>fwd_d_fixed</th>\n",
       "      <th>fwd_d_additional</th>\n",
       "      <th>fwd_e_fixed</th>\n",
       "      <th>fwd_e_additional</th>\n",
       "      <th>rto_a_fixed</th>\n",
       "      <th>rto_a_additional</th>\n",
       "      <th>rto_b_fixed</th>\n",
       "      <th>rto_b_additional</th>\n",
       "      <th>rto_c_fixed</th>\n",
       "      <th>rto_c_additional</th>\n",
       "      <th>rto_d_fixed</th>\n",
       "      <th>rto_d_additional</th>\n",
       "      <th>rto_e_fixed</th>\n",
       "      <th>rto_e_additional</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>29.5</td>\n",
       "      <td>23.6</td>\n",
       "      <td>33</td>\n",
       "      <td>28.3</td>\n",
       "      <td>40.1</td>\n",
       "      <td>38.9</td>\n",
       "      <td>45.4</td>\n",
       "      <td>44.8</td>\n",
       "      <td>56.6</td>\n",
       "      <td>55.5</td>\n",
       "      <td>13.6</td>\n",
       "      <td>23.6</td>\n",
       "      <td>20.5</td>\n",
       "      <td>28.3</td>\n",
       "      <td>31.9</td>\n",
       "      <td>38.9</td>\n",
       "      <td>41.3</td>\n",
       "      <td>44.8</td>\n",
       "      <td>50.7</td>\n",
       "      <td>55.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   fwd_a_fixed  fwd_a_additional  fwd_b_fixed  fwd_b_additional  fwd_c_fixed  \\\n",
       "0         29.5              23.6           33              28.3         40.1   \n",
       "\n",
       "   fwd_c_additional  fwd_d_fixed  fwd_d_additional  fwd_e_fixed  \\\n",
       "0              38.9         45.4              44.8         56.6   \n",
       "\n",
       "   fwd_e_additional  rto_a_fixed  rto_a_additional  rto_b_fixed  \\\n",
       "0              55.5         13.6              23.6         20.5   \n",
       "\n",
       "   rto_b_additional  rto_c_fixed  rto_c_additional  rto_d_fixed  \\\n",
       "0              28.3         31.9              38.9         41.3   \n",
       "\n",
       "   rto_d_additional  rto_e_fixed  rto_e_additional  \n",
       "0              44.8         50.7              55.5  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Rates.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "29eb17b6",
   "metadata": {},
   "source": [
    "## Output Data 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "eb1b1e41",
   "metadata": {},
   "outputs": [],
   "source": [
    "data1=pd.DataFrame()\n",
    "data1[[\"Order ID\",\"AWB NO.\"]]=Invoice[['Order ID',\"AWB Code\"]].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "eddbf8d4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(124, 2)"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data1.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "dc6efd34",
   "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>AWB NO.</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2001806232</td>\n",
       "      <td>1091117222124</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2001806273</td>\n",
       "      <td>1091117222194</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2001806408</td>\n",
       "      <td>1091117222931</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2001806458</td>\n",
       "      <td>1091117223244</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2001807012</td>\n",
       "      <td>1091117229345</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119</th>\n",
       "      <td>2001812941</td>\n",
       "      <td>1091118551656</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120</th>\n",
       "      <td>2001809383</td>\n",
       "      <td>1091117614452</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>2001820978</td>\n",
       "      <td>1091120922803</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>2001811475</td>\n",
       "      <td>1091121844806</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>2001811305</td>\n",
       "      <td>1091121846136</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>124 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Order ID        AWB NO.\n",
       "0    2001806232  1091117222124\n",
       "1    2001806273  1091117222194\n",
       "2    2001806408  1091117222931\n",
       "3    2001806458  1091117223244\n",
       "4    2001807012  1091117229345\n",
       "..          ...            ...\n",
       "119  2001812941  1091118551656\n",
       "120  2001809383  1091117614452\n",
       "121  2001820978  1091120922803\n",
       "122  2001811475  1091121844806\n",
       "123  2001811305  1091121846136\n",
       "\n",
       "[124 rows x 2 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "23d3becd",
   "metadata": {},
   "outputs": [],
   "source": [
    "Order_SKU=Order_Report.merge(SKU_Master,on='SKU',how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "40025735",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(400, 4)"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Order_SKU.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "5c53da61",
   "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>ExternOrderNo</th>\n",
       "      <th>SKU</th>\n",
       "      <th>Order Qty</th>\n",
       "      <th>Weight (g)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2001827036</td>\n",
       "      <td>8904223818706</td>\n",
       "      <td>1.0</td>\n",
       "      <td>127</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2001827036</td>\n",
       "      <td>8904223819093</td>\n",
       "      <td>1.0</td>\n",
       "      <td>150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2001827036</td>\n",
       "      <td>8904223819109</td>\n",
       "      <td>1.0</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2001827036</td>\n",
       "      <td>8904223818430</td>\n",
       "      <td>1.0</td>\n",
       "      <td>165</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2001827036</td>\n",
       "      <td>8904223819277</td>\n",
       "      <td>1.0</td>\n",
       "      <td>350</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>395</th>\n",
       "      <td>2001806229</td>\n",
       "      <td>8904223818942</td>\n",
       "      <td>1.0</td>\n",
       "      <td>133</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>396</th>\n",
       "      <td>2001806229</td>\n",
       "      <td>8904223818850</td>\n",
       "      <td>1.0</td>\n",
       "      <td>240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>397</th>\n",
       "      <td>2001806226</td>\n",
       "      <td>8904223818850</td>\n",
       "      <td>2.0</td>\n",
       "      <td>240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>398</th>\n",
       "      <td>2001806210</td>\n",
       "      <td>8904223816214</td>\n",
       "      <td>1.0</td>\n",
       "      <td>120</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>399</th>\n",
       "      <td>2001806210</td>\n",
       "      <td>8904223818874</td>\n",
       "      <td>1.0</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>400 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     ExternOrderNo            SKU  Order Qty  Weight (g)\n",
       "0       2001827036  8904223818706        1.0         127\n",
       "1       2001827036  8904223819093        1.0         150\n",
       "2       2001827036  8904223819109        1.0         100\n",
       "3       2001827036  8904223818430        1.0         165\n",
       "4       2001827036  8904223819277        1.0         350\n",
       "..             ...            ...        ...         ...\n",
       "395     2001806229  8904223818942        1.0         133\n",
       "396     2001806229  8904223818850        1.0         240\n",
       "397     2001806226  8904223818850        2.0         240\n",
       "398     2001806210  8904223816214        1.0         120\n",
       "399     2001806210  8904223818874        1.0         100\n",
       "\n",
       "[400 rows x 4 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Order_SKU"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "ed0839c9",
   "metadata": {},
   "outputs": [],
   "source": [
    "Order_SKU['Per Wt']=Order_SKU['Order Qty']*Order_SKU['Weight (g)'].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "2cf60a8b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(400, 5)"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Order_SKU.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "d56a7076",
   "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>ExternOrderNo</th>\n",
       "      <th>SKU</th>\n",
       "      <th>Order Qty</th>\n",
       "      <th>Weight (g)</th>\n",
       "      <th>Per Wt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2001827036</td>\n",
       "      <td>8904223818706</td>\n",
       "      <td>1.0</td>\n",
       "      <td>127</td>\n",
       "      <td>127.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2001827036</td>\n",
       "      <td>8904223819093</td>\n",
       "      <td>1.0</td>\n",
       "      <td>150</td>\n",
       "      <td>150.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2001827036</td>\n",
       "      <td>8904223819109</td>\n",
       "      <td>1.0</td>\n",
       "      <td>100</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2001827036</td>\n",
       "      <td>8904223818430</td>\n",
       "      <td>1.0</td>\n",
       "      <td>165</td>\n",
       "      <td>165.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2001827036</td>\n",
       "      <td>8904223819277</td>\n",
       "      <td>1.0</td>\n",
       "      <td>350</td>\n",
       "      <td>350.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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>395</th>\n",
       "      <td>2001806229</td>\n",
       "      <td>8904223818942</td>\n",
       "      <td>1.0</td>\n",
       "      <td>133</td>\n",
       "      <td>133.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>396</th>\n",
       "      <td>2001806229</td>\n",
       "      <td>8904223818850</td>\n",
       "      <td>1.0</td>\n",
       "      <td>240</td>\n",
       "      <td>240.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>397</th>\n",
       "      <td>2001806226</td>\n",
       "      <td>8904223818850</td>\n",
       "      <td>2.0</td>\n",
       "      <td>240</td>\n",
       "      <td>480.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>398</th>\n",
       "      <td>2001806210</td>\n",
       "      <td>8904223816214</td>\n",
       "      <td>1.0</td>\n",
       "      <td>120</td>\n",
       "      <td>120.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>399</th>\n",
       "      <td>2001806210</td>\n",
       "      <td>8904223818874</td>\n",
       "      <td>1.0</td>\n",
       "      <td>100</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>400 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     ExternOrderNo            SKU  Order Qty  Weight (g)  Per Wt\n",
       "0       2001827036  8904223818706        1.0         127   127.0\n",
       "1       2001827036  8904223819093        1.0         150   150.0\n",
       "2       2001827036  8904223819109        1.0         100   100.0\n",
       "3       2001827036  8904223818430        1.0         165   165.0\n",
       "4       2001827036  8904223819277        1.0         350   350.0\n",
       "..             ...            ...        ...         ...     ...\n",
       "395     2001806229  8904223818942        1.0         133   133.0\n",
       "396     2001806229  8904223818850        1.0         240   240.0\n",
       "397     2001806226  8904223818850        2.0         240   480.0\n",
       "398     2001806210  8904223816214        1.0         120   120.0\n",
       "399     2001806210  8904223818874        1.0         100   100.0\n",
       "\n",
       "[400 rows x 5 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Order_SKU"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "154dbe86",
   "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>Per Wt</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ExternOrderNo</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2001806210</th>\n",
       "      <td>220.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2001806226</th>\n",
       "      <td>480.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2001806229</th>\n",
       "      <td>500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2001806232</th>\n",
       "      <td>1302.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2001806233</th>\n",
       "      <td>245.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2001821995</th>\n",
       "      <td>477.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2001822466</th>\n",
       "      <td>1376.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2001823564</th>\n",
       "      <td>672.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2001825261</th>\n",
       "      <td>1557.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2001827036</th>\n",
       "      <td>1676.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>124 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               Per Wt\n",
       "ExternOrderNo        \n",
       "2001806210      220.0\n",
       "2001806226      480.0\n",
       "2001806229      500.0\n",
       "2001806232     1302.0\n",
       "2001806233      245.0\n",
       "...               ...\n",
       "2001821995      477.0\n",
       "2001822466     1376.0\n",
       "2001823564      672.0\n",
       "2001825261     1557.0\n",
       "2001827036     1676.0\n",
       "\n",
       "[124 rows x 1 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Order_SKU.groupby('ExternOrderNo')['Per Wt'].sum().to_frame()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "b5e38b82",
   "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>ExternOrderNo</th>\n",
       "      <th>SKU</th>\n",
       "      <th>Order Qty</th>\n",
       "      <th>Weight (g)</th>\n",
       "      <th>Per Wt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2001821995</td>\n",
       "      <td>8904223819130</td>\n",
       "      <td>1.0</td>\n",
       "      <td>350</td>\n",
       "      <td>350.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2001821995</td>\n",
       "      <td>8904223818706</td>\n",
       "      <td>1.0</td>\n",
       "      <td>127</td>\n",
       "      <td>127.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    ExternOrderNo            SKU  Order Qty  Weight (g)  Per Wt\n",
       "18     2001821995  8904223819130        1.0         350   350.0\n",
       "19     2001821995  8904223818706        1.0         127   127.0"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Order_SKU[Order_SKU['ExternOrderNo']==2001821995]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "675a45fe",
   "metadata": {},
   "outputs": [],
   "source": [
    "data1=data1.merge(Order_SKU.groupby('ExternOrderNo')['Per Wt'].sum().to_frame(),left_on='Order ID',right_on='ExternOrderNo')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "a5eda454",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(124, 3)"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data1.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "75283d76",
   "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>AWB NO.</th>\n",
       "      <th>Per Wt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2001806232</td>\n",
       "      <td>1091117222124</td>\n",
       "      <td>1302.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2001806273</td>\n",
       "      <td>1091117222194</td>\n",
       "      <td>615.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2001806408</td>\n",
       "      <td>1091117222931</td>\n",
       "      <td>2265.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2001806458</td>\n",
       "      <td>1091117223244</td>\n",
       "      <td>700.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2001807012</td>\n",
       "      <td>1091117229345</td>\n",
       "      <td>240.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119</th>\n",
       "      <td>2001812941</td>\n",
       "      <td>1091118551656</td>\n",
       "      <td>500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120</th>\n",
       "      <td>2001809383</td>\n",
       "      <td>1091117614452</td>\n",
       "      <td>607.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>2001820978</td>\n",
       "      <td>1091120922803</td>\n",
       "      <td>515.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>2001811475</td>\n",
       "      <td>1091121844806</td>\n",
       "      <td>689.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>2001811305</td>\n",
       "      <td>1091121846136</td>\n",
       "      <td>750.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>124 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Order ID        AWB NO.  Per Wt\n",
       "0    2001806232  1091117222124  1302.0\n",
       "1    2001806273  1091117222194   615.0\n",
       "2    2001806408  1091117222931  2265.0\n",
       "3    2001806458  1091117223244   700.0\n",
       "4    2001807012  1091117229345   240.0\n",
       "..          ...            ...     ...\n",
       "119  2001812941  1091118551656   500.0\n",
       "120  2001809383  1091117614452   607.0\n",
       "121  2001820978  1091120922803   515.0\n",
       "122  2001811475  1091121844806   689.0\n",
       "123  2001811305  1091121846136   750.0\n",
       "\n",
       "[124 rows x 3 columns]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "65afd42a",
   "metadata": {},
   "outputs": [],
   "source": [
    "data1['Total weight as per X (KG)']=data1['Per Wt']/1000"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "88116cb2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(124, 4)"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data1.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "0b7c30b6",
   "metadata": {},
   "outputs": [],
   "source": [
    "data1.drop('Per Wt',axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "7bd41fe5",
   "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>AWB NO.</th>\n",
       "      <th>Total weight as per X (KG)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2001806232</td>\n",
       "      <td>1091117222124</td>\n",
       "      <td>1.302</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2001806273</td>\n",
       "      <td>1091117222194</td>\n",
       "      <td>0.615</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2001806408</td>\n",
       "      <td>1091117222931</td>\n",
       "      <td>2.265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2001806458</td>\n",
       "      <td>1091117223244</td>\n",
       "      <td>0.700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2001807012</td>\n",
       "      <td>1091117229345</td>\n",
       "      <td>0.240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119</th>\n",
       "      <td>2001812941</td>\n",
       "      <td>1091118551656</td>\n",
       "      <td>0.500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120</th>\n",
       "      <td>2001809383</td>\n",
       "      <td>1091117614452</td>\n",
       "      <td>0.607</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>2001820978</td>\n",
       "      <td>1091120922803</td>\n",
       "      <td>0.515</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>2001811475</td>\n",
       "      <td>1091121844806</td>\n",
       "      <td>0.689</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>2001811305</td>\n",
       "      <td>1091121846136</td>\n",
       "      <td>0.750</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>124 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Order ID        AWB NO.  Total weight as per X (KG)\n",
       "0    2001806232  1091117222124                       1.302\n",
       "1    2001806273  1091117222194                       0.615\n",
       "2    2001806408  1091117222931                       2.265\n",
       "3    2001806458  1091117223244                       0.700\n",
       "4    2001807012  1091117229345                       0.240\n",
       "..          ...            ...                         ...\n",
       "119  2001812941  1091118551656                       0.500\n",
       "120  2001809383  1091117614452                       0.607\n",
       "121  2001820978  1091120922803                       0.515\n",
       "122  2001811475  1091121844806                       0.689\n",
       "123  2001811305  1091121846136                       0.750\n",
       "\n",
       "[124 rows x 3 columns]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "154800ba",
   "metadata": {},
   "outputs": [],
   "source": [
    "data1['Weight slab as per X (KG)']=np.ceil(data1['Total weight as per X (KG)']*2)/2 #np.ceil((1.3*2)/2)=1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "e1a7a016",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(124, 4)"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data1.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "5f0eec62",
   "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>AWB NO.</th>\n",
       "      <th>Total weight as per X (KG)</th>\n",
       "      <th>Weight slab as per X (KG)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2001806232</td>\n",
       "      <td>1091117222124</td>\n",
       "      <td>1.302</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2001806273</td>\n",
       "      <td>1091117222194</td>\n",
       "      <td>0.615</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2001806408</td>\n",
       "      <td>1091117222931</td>\n",
       "      <td>2.265</td>\n",
       "      <td>2.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2001806458</td>\n",
       "      <td>1091117223244</td>\n",
       "      <td>0.700</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2001807012</td>\n",
       "      <td>1091117229345</td>\n",
       "      <td>0.240</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119</th>\n",
       "      <td>2001812941</td>\n",
       "      <td>1091118551656</td>\n",
       "      <td>0.500</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120</th>\n",
       "      <td>2001809383</td>\n",
       "      <td>1091117614452</td>\n",
       "      <td>0.607</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>2001820978</td>\n",
       "      <td>1091120922803</td>\n",
       "      <td>0.515</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>2001811475</td>\n",
       "      <td>1091121844806</td>\n",
       "      <td>0.689</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>2001811305</td>\n",
       "      <td>1091121846136</td>\n",
       "      <td>0.750</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>124 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Order ID        AWB NO.  Total weight as per X (KG)  \\\n",
       "0    2001806232  1091117222124                       1.302   \n",
       "1    2001806273  1091117222194                       0.615   \n",
       "2    2001806408  1091117222931                       2.265   \n",
       "3    2001806458  1091117223244                       0.700   \n",
       "4    2001807012  1091117229345                       0.240   \n",
       "..          ...            ...                         ...   \n",
       "119  2001812941  1091118551656                       0.500   \n",
       "120  2001809383  1091117614452                       0.607   \n",
       "121  2001820978  1091120922803                       0.515   \n",
       "122  2001811475  1091121844806                       0.689   \n",
       "123  2001811305  1091121846136                       0.750   \n",
       "\n",
       "     Weight slab as per X (KG)  \n",
       "0                          1.5  \n",
       "1                          1.0  \n",
       "2                          2.5  \n",
       "3                          1.0  \n",
       "4                          0.5  \n",
       "..                         ...  \n",
       "119                        0.5  \n",
       "120                        1.0  \n",
       "121                        1.0  \n",
       "122                        1.0  \n",
       "123                        1.0  \n",
       "\n",
       "[124 rows x 4 columns]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "0de75027",
   "metadata": {},
   "outputs": [],
   "source": [
    "Invoice.rename({'AWB Code':'AWB NO.'},axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "09708728",
   "metadata": {},
   "outputs": [],
   "source": [
    "data1=data1.merge(Invoice[['Charged Weight','AWB NO.']],on='AWB NO.')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "7b714c1b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(124, 5)"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data1.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "69d61880",
   "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>AWB NO.</th>\n",
       "      <th>Total weight as per X (KG)</th>\n",
       "      <th>Weight slab as per X (KG)</th>\n",
       "      <th>Charged Weight</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2001806232</td>\n",
       "      <td>1091117222124</td>\n",
       "      <td>1.302</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2001806273</td>\n",
       "      <td>1091117222194</td>\n",
       "      <td>0.615</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2001806408</td>\n",
       "      <td>1091117222931</td>\n",
       "      <td>2.265</td>\n",
       "      <td>2.5</td>\n",
       "      <td>2.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2001806458</td>\n",
       "      <td>1091117223244</td>\n",
       "      <td>0.700</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2001807012</td>\n",
       "      <td>1091117229345</td>\n",
       "      <td>0.240</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119</th>\n",
       "      <td>2001812941</td>\n",
       "      <td>1091118551656</td>\n",
       "      <td>0.500</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120</th>\n",
       "      <td>2001809383</td>\n",
       "      <td>1091117614452</td>\n",
       "      <td>0.607</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>2001820978</td>\n",
       "      <td>1091120922803</td>\n",
       "      <td>0.515</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>2001811475</td>\n",
       "      <td>1091121844806</td>\n",
       "      <td>0.689</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>2001811305</td>\n",
       "      <td>1091121846136</td>\n",
       "      <td>0.750</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.50</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>124 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Order ID        AWB NO.  Total weight as per X (KG)  \\\n",
       "0    2001806232  1091117222124                       1.302   \n",
       "1    2001806273  1091117222194                       0.615   \n",
       "2    2001806408  1091117222931                       2.265   \n",
       "3    2001806458  1091117223244                       0.700   \n",
       "4    2001807012  1091117229345                       0.240   \n",
       "..          ...            ...                         ...   \n",
       "119  2001812941  1091118551656                       0.500   \n",
       "120  2001809383  1091117614452                       0.607   \n",
       "121  2001820978  1091120922803                       0.515   \n",
       "122  2001811475  1091121844806                       0.689   \n",
       "123  2001811305  1091121846136                       0.750   \n",
       "\n",
       "     Weight slab as per X (KG)  Charged Weight  \n",
       "0                          1.5            1.30  \n",
       "1                          1.0            1.00  \n",
       "2                          2.5            2.50  \n",
       "3                          1.0            1.00  \n",
       "4                          0.5            0.15  \n",
       "..                         ...             ...  \n",
       "119                        0.5            0.73  \n",
       "120                        1.0            0.50  \n",
       "121                        1.0            0.50  \n",
       "122                        1.0            0.50  \n",
       "123                        1.0            0.50  \n",
       "\n",
       "[124 rows x 5 columns]"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "f68fbb9e",
   "metadata": {},
   "outputs": [],
   "source": [
    "data1.rename({'Charged Weight':'Total weight as per Courier Company (KG)'},axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "1d0b4b86",
   "metadata": {},
   "outputs": [],
   "source": [
    "data1['Weight slab charged by Courier Company (KG)']=np.ceil(data1['Total weight as per Courier Company (KG)']*2)/2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "2e15c22b",
   "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>AWB NO.</th>\n",
       "      <th>Total weight as per X (KG)</th>\n",
       "      <th>Weight slab as per X (KG)</th>\n",
       "      <th>Total weight as per Courier Company (KG)</th>\n",
       "      <th>Weight slab charged by Courier Company (KG)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2001806232</td>\n",
       "      <td>1091117222124</td>\n",
       "      <td>1.302</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.30</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2001806273</td>\n",
       "      <td>1091117222194</td>\n",
       "      <td>0.615</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2001806408</td>\n",
       "      <td>1091117222931</td>\n",
       "      <td>2.265</td>\n",
       "      <td>2.5</td>\n",
       "      <td>2.50</td>\n",
       "      <td>2.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2001806458</td>\n",
       "      <td>1091117223244</td>\n",
       "      <td>0.700</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2001807012</td>\n",
       "      <td>1091117229345</td>\n",
       "      <td>0.240</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.15</td>\n",
       "      <td>0.5</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119</th>\n",
       "      <td>2001812941</td>\n",
       "      <td>1091118551656</td>\n",
       "      <td>0.500</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.73</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120</th>\n",
       "      <td>2001809383</td>\n",
       "      <td>1091117614452</td>\n",
       "      <td>0.607</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.50</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>2001820978</td>\n",
       "      <td>1091120922803</td>\n",
       "      <td>0.515</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.50</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>2001811475</td>\n",
       "      <td>1091121844806</td>\n",
       "      <td>0.689</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.50</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>2001811305</td>\n",
       "      <td>1091121846136</td>\n",
       "      <td>0.750</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.50</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>124 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Order ID        AWB NO.  Total weight as per X (KG)  \\\n",
       "0    2001806232  1091117222124                       1.302   \n",
       "1    2001806273  1091117222194                       0.615   \n",
       "2    2001806408  1091117222931                       2.265   \n",
       "3    2001806458  1091117223244                       0.700   \n",
       "4    2001807012  1091117229345                       0.240   \n",
       "..          ...            ...                         ...   \n",
       "119  2001812941  1091118551656                       0.500   \n",
       "120  2001809383  1091117614452                       0.607   \n",
       "121  2001820978  1091120922803                       0.515   \n",
       "122  2001811475  1091121844806                       0.689   \n",
       "123  2001811305  1091121846136                       0.750   \n",
       "\n",
       "     Weight slab as per X (KG)  Total weight as per Courier Company (KG)  \\\n",
       "0                          1.5                                      1.30   \n",
       "1                          1.0                                      1.00   \n",
       "2                          2.5                                      2.50   \n",
       "3                          1.0                                      1.00   \n",
       "4                          0.5                                      0.15   \n",
       "..                         ...                                       ...   \n",
       "119                        0.5                                      0.73   \n",
       "120                        1.0                                      0.50   \n",
       "121                        1.0                                      0.50   \n",
       "122                        1.0                                      0.50   \n",
       "123                        1.0                                      0.50   \n",
       "\n",
       "     Weight slab charged by Courier Company (KG)  \n",
       "0                                            1.5  \n",
       "1                                            1.0  \n",
       "2                                            2.5  \n",
       "3                                            1.0  \n",
       "4                                            0.5  \n",
       "..                                           ...  \n",
       "119                                          1.0  \n",
       "120                                          0.5  \n",
       "121                                          0.5  \n",
       "122                                          0.5  \n",
       "123                                          0.5  \n",
       "\n",
       "[124 rows x 6 columns]"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "2d8328e1",
   "metadata": {},
   "outputs": [],
   "source": [
    "Temp=Invoice.merge(Pincode_Zones,on=['Customer Pincode','Warehouse Pincode'],how='right')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "a59bf974",
   "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>AWB NO.</th>\n",
       "      <th>Order ID</th>\n",
       "      <th>Charged Weight</th>\n",
       "      <th>Warehouse Pincode</th>\n",
       "      <th>Customer Pincode</th>\n",
       "      <th>Zone_x</th>\n",
       "      <th>Type of Shipment</th>\n",
       "      <th>Billing Amount (Rs.)</th>\n",
       "      <th>Zone_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1091117222124</td>\n",
       "      <td>2001806232</td>\n",
       "      <td>1.30</td>\n",
       "      <td>121003</td>\n",
       "      <td>507101</td>\n",
       "      <td>d</td>\n",
       "      <td>Forward charges</td>\n",
       "      <td>135.0</td>\n",
       "      <td>d</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1091117222194</td>\n",
       "      <td>2001806273</td>\n",
       "      <td>1.00</td>\n",
       "      <td>121003</td>\n",
       "      <td>486886</td>\n",
       "      <td>d</td>\n",
       "      <td>Forward charges</td>\n",
       "      <td>90.2</td>\n",
       "      <td>d</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1091117222931</td>\n",
       "      <td>2001806408</td>\n",
       "      <td>2.50</td>\n",
       "      <td>121003</td>\n",
       "      <td>532484</td>\n",
       "      <td>d</td>\n",
       "      <td>Forward charges</td>\n",
       "      <td>224.6</td>\n",
       "      <td>d</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1091117223244</td>\n",
       "      <td>2001806458</td>\n",
       "      <td>1.00</td>\n",
       "      <td>121003</td>\n",
       "      <td>143001</td>\n",
       "      <td>b</td>\n",
       "      <td>Forward charges</td>\n",
       "      <td>61.3</td>\n",
       "      <td>b</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1091117229345</td>\n",
       "      <td>2001807012</td>\n",
       "      <td>0.15</td>\n",
       "      <td>121003</td>\n",
       "      <td>515591</td>\n",
       "      <td>d</td>\n",
       "      <td>Forward charges</td>\n",
       "      <td>45.4</td>\n",
       "      <td>d</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119</th>\n",
       "      <td>1091117904860</td>\n",
       "      <td>2001811039</td>\n",
       "      <td>0.68</td>\n",
       "      <td>121003</td>\n",
       "      <td>302020</td>\n",
       "      <td>d</td>\n",
       "      <td>Forward charges</td>\n",
       "      <td>90.2</td>\n",
       "      <td>b</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120</th>\n",
       "      <td>1091121846136</td>\n",
       "      <td>2001811305</td>\n",
       "      <td>0.50</td>\n",
       "      <td>121003</td>\n",
       "      <td>302020</td>\n",
       "      <td>d</td>\n",
       "      <td>Forward charges</td>\n",
       "      <td>45.4</td>\n",
       "      <td>b</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>1091118551656</td>\n",
       "      <td>2001812941</td>\n",
       "      <td>0.73</td>\n",
       "      <td>121003</td>\n",
       "      <td>325207</td>\n",
       "      <td>d</td>\n",
       "      <td>Forward charges</td>\n",
       "      <td>90.2</td>\n",
       "      <td>b</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>1091117614452</td>\n",
       "      <td>2001809383</td>\n",
       "      <td>0.50</td>\n",
       "      <td>121003</td>\n",
       "      <td>303702</td>\n",
       "      <td>d</td>\n",
       "      <td>Forward and RTO charges</td>\n",
       "      <td>86.7</td>\n",
       "      <td>b</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>1091120922803</td>\n",
       "      <td>2001820978</td>\n",
       "      <td>0.50</td>\n",
       "      <td>121003</td>\n",
       "      <td>313301</td>\n",
       "      <td>d</td>\n",
       "      <td>Forward charges</td>\n",
       "      <td>45.4</td>\n",
       "      <td>b</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>124 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           AWB NO.    Order ID  Charged Weight  Warehouse Pincode  \\\n",
       "0    1091117222124  2001806232            1.30             121003   \n",
       "1    1091117222194  2001806273            1.00             121003   \n",
       "2    1091117222931  2001806408            2.50             121003   \n",
       "3    1091117223244  2001806458            1.00             121003   \n",
       "4    1091117229345  2001807012            0.15             121003   \n",
       "..             ...         ...             ...                ...   \n",
       "119  1091117904860  2001811039            0.68             121003   \n",
       "120  1091121846136  2001811305            0.50             121003   \n",
       "121  1091118551656  2001812941            0.73             121003   \n",
       "122  1091117614452  2001809383            0.50             121003   \n",
       "123  1091120922803  2001820978            0.50             121003   \n",
       "\n",
       "     Customer Pincode Zone_x         Type of Shipment  Billing Amount (Rs.)  \\\n",
       "0              507101      d          Forward charges                 135.0   \n",
       "1              486886      d          Forward charges                  90.2   \n",
       "2              532484      d          Forward charges                 224.6   \n",
       "3              143001      b          Forward charges                  61.3   \n",
       "4              515591      d          Forward charges                  45.4   \n",
       "..                ...    ...                      ...                   ...   \n",
       "119            302020      d          Forward charges                  90.2   \n",
       "120            302020      d          Forward charges                  45.4   \n",
       "121            325207      d          Forward charges                  90.2   \n",
       "122            303702      d  Forward and RTO charges                  86.7   \n",
       "123            313301      d          Forward charges                  45.4   \n",
       "\n",
       "    Zone_y  \n",
       "0        d  \n",
       "1        d  \n",
       "2        d  \n",
       "3        b  \n",
       "4        d  \n",
       "..     ...  \n",
       "119      b  \n",
       "120      b  \n",
       "121      b  \n",
       "122      b  \n",
       "123      b  \n",
       "\n",
       "[124 rows x 9 columns]"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Temp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "232276b2",
   "metadata": {},
   "outputs": [],
   "source": [
    "data1=data1.merge(Temp[['Zone_y','AWB NO.']],on='AWB NO.')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "cf4a4c10",
   "metadata": {},
   "outputs": [],
   "source": [
    "data1.rename({'Zone_y':'Delivery Zone as per X'},axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "35829e39",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(124, 7)"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data1.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "68a72213",
   "metadata": {},
   "outputs": [],
   "source": [
    "data1=data1.merge(Temp[['Zone_x','AWB NO.']],on='AWB NO.')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "4535c4ac",
   "metadata": {},
   "outputs": [],
   "source": [
    "data1.rename({'Zone_x':'Delivery Zone charged by Courier Company'},axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "72198ad1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(124, 8)"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data1.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "c1fbaac9",
   "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>AWB NO.</th>\n",
       "      <th>Total weight as per X (KG)</th>\n",
       "      <th>Weight slab as per X (KG)</th>\n",
       "      <th>Total weight as per Courier Company (KG)</th>\n",
       "      <th>Weight slab charged by Courier Company (KG)</th>\n",
       "      <th>Delivery Zone as per X</th>\n",
       "      <th>Delivery Zone charged by Courier Company</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2001806232</td>\n",
       "      <td>1091117222124</td>\n",
       "      <td>1.302</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.30</td>\n",
       "      <td>1.5</td>\n",
       "      <td>d</td>\n",
       "      <td>d</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2001806273</td>\n",
       "      <td>1091117222194</td>\n",
       "      <td>0.615</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>d</td>\n",
       "      <td>d</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2001806408</td>\n",
       "      <td>1091117222931</td>\n",
       "      <td>2.265</td>\n",
       "      <td>2.5</td>\n",
       "      <td>2.50</td>\n",
       "      <td>2.5</td>\n",
       "      <td>d</td>\n",
       "      <td>d</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2001806458</td>\n",
       "      <td>1091117223244</td>\n",
       "      <td>0.700</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>b</td>\n",
       "      <td>b</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2001807012</td>\n",
       "      <td>1091117229345</td>\n",
       "      <td>0.240</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.15</td>\n",
       "      <td>0.5</td>\n",
       "      <td>d</td>\n",
       "      <td>d</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119</th>\n",
       "      <td>2001812941</td>\n",
       "      <td>1091118551656</td>\n",
       "      <td>0.500</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.73</td>\n",
       "      <td>1.0</td>\n",
       "      <td>b</td>\n",
       "      <td>d</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120</th>\n",
       "      <td>2001809383</td>\n",
       "      <td>1091117614452</td>\n",
       "      <td>0.607</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.50</td>\n",
       "      <td>0.5</td>\n",
       "      <td>b</td>\n",
       "      <td>d</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>2001820978</td>\n",
       "      <td>1091120922803</td>\n",
       "      <td>0.515</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.50</td>\n",
       "      <td>0.5</td>\n",
       "      <td>b</td>\n",
       "      <td>d</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>2001811475</td>\n",
       "      <td>1091121844806</td>\n",
       "      <td>0.689</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.50</td>\n",
       "      <td>0.5</td>\n",
       "      <td>e</td>\n",
       "      <td>b</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>2001811305</td>\n",
       "      <td>1091121846136</td>\n",
       "      <td>0.750</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.50</td>\n",
       "      <td>0.5</td>\n",
       "      <td>b</td>\n",
       "      <td>d</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>124 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Order ID        AWB NO.  Total weight as per X (KG)  \\\n",
       "0    2001806232  1091117222124                       1.302   \n",
       "1    2001806273  1091117222194                       0.615   \n",
       "2    2001806408  1091117222931                       2.265   \n",
       "3    2001806458  1091117223244                       0.700   \n",
       "4    2001807012  1091117229345                       0.240   \n",
       "..          ...            ...                         ...   \n",
       "119  2001812941  1091118551656                       0.500   \n",
       "120  2001809383  1091117614452                       0.607   \n",
       "121  2001820978  1091120922803                       0.515   \n",
       "122  2001811475  1091121844806                       0.689   \n",
       "123  2001811305  1091121846136                       0.750   \n",
       "\n",
       "     Weight slab as per X (KG)  Total weight as per Courier Company (KG)  \\\n",
       "0                          1.5                                      1.30   \n",
       "1                          1.0                                      1.00   \n",
       "2                          2.5                                      2.50   \n",
       "3                          1.0                                      1.00   \n",
       "4                          0.5                                      0.15   \n",
       "..                         ...                                       ...   \n",
       "119                        0.5                                      0.73   \n",
       "120                        1.0                                      0.50   \n",
       "121                        1.0                                      0.50   \n",
       "122                        1.0                                      0.50   \n",
       "123                        1.0                                      0.50   \n",
       "\n",
       "     Weight slab charged by Courier Company (KG) Delivery Zone as per X  \\\n",
       "0                                            1.5                      d   \n",
       "1                                            1.0                      d   \n",
       "2                                            2.5                      d   \n",
       "3                                            1.0                      b   \n",
       "4                                            0.5                      d   \n",
       "..                                           ...                    ...   \n",
       "119                                          1.0                      b   \n",
       "120                                          0.5                      b   \n",
       "121                                          0.5                      b   \n",
       "122                                          0.5                      e   \n",
       "123                                          0.5                      b   \n",
       "\n",
       "    Delivery Zone charged by Courier Company  \n",
       "0                                          d  \n",
       "1                                          d  \n",
       "2                                          d  \n",
       "3                                          b  \n",
       "4                                          d  \n",
       "..                                       ...  \n",
       "119                                        d  \n",
       "120                                        d  \n",
       "121                                        d  \n",
       "122                                        b  \n",
       "123                                        d  \n",
       "\n",
       "[124 rows x 8 columns]"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "b0840dd9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Forward charges            109\n",
       "Forward and RTO charges     15\n",
       "Name: Type of Shipment, dtype: int64"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Invoice['Type of Shipment'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "e9b6364b",
   "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>Billing Amount (Rs.)</th>\n",
       "      <th>Zone</th>\n",
       "      <th>Charged Weight</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>107.3</td>\n",
       "      <td>e</td>\n",
       "      <td>0.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>172.8</td>\n",
       "      <td>d</td>\n",
       "      <td>0.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>102.3</td>\n",
       "      <td>b</td>\n",
       "      <td>0.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>172.8</td>\n",
       "      <td>d</td>\n",
       "      <td>0.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>172.8</td>\n",
       "      <td>d</td>\n",
       "      <td>0.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>213.5</td>\n",
       "      <td>e</td>\n",
       "      <td>0.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>258.9</td>\n",
       "      <td>d</td>\n",
       "      <td>1.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>151.1</td>\n",
       "      <td>b</td>\n",
       "      <td>1.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>172.8</td>\n",
       "      <td>d</td>\n",
       "      <td>0.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>345.0</td>\n",
       "      <td>d</td>\n",
       "      <td>1.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>258.9</td>\n",
       "      <td>d</td>\n",
       "      <td>1.13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>172.8</td>\n",
       "      <td>d</td>\n",
       "      <td>0.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td>86.7</td>\n",
       "      <td>d</td>\n",
       "      <td>0.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>86.7</td>\n",
       "      <td>d</td>\n",
       "      <td>0.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120</th>\n",
       "      <td>86.7</td>\n",
       "      <td>d</td>\n",
       "      <td>0.50</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Billing Amount (Rs.) Zone  Charged Weight\n",
       "18                  107.3    e            0.20\n",
       "22                  172.8    d            0.70\n",
       "23                  102.3    b            0.60\n",
       "24                  172.8    d            0.99\n",
       "25                  172.8    d            0.70\n",
       "26                  213.5    e            0.80\n",
       "27                  258.9    d            1.20\n",
       "28                  151.1    b            1.30\n",
       "29                  172.8    d            0.70\n",
       "30                  345.0    d            1.60\n",
       "31                  258.9    d            1.13\n",
       "32                  172.8    d            0.60\n",
       "69                   86.7    d            0.50\n",
       "73                   86.7    d            0.15\n",
       "120                  86.7    d            0.50"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Invoice[['Billing Amount (Rs.)','Zone','Charged Weight']][Invoice['Type of Shipment']=='Forward and RTO charges']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "48da4865",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "fwd_a_fixed\n",
      "fwd_a_additional\n",
      "fwd_b_fixed\n",
      "fwd_b_additional\n",
      "fwd_c_fixed\n",
      "fwd_c_additional\n",
      "fwd_d_fixed\n",
      "fwd_d_additional\n",
      "fwd_e_fixed\n",
      "fwd_e_additional\n",
      "rto_a_fixed\n",
      "rto_a_additional\n",
      "rto_b_fixed\n",
      "rto_b_additional\n",
      "rto_c_fixed\n",
      "rto_c_additional\n",
      "rto_d_fixed\n",
      "rto_d_additional\n",
      "rto_e_fixed\n",
      "rto_e_additional\n"
     ]
    }
   ],
   "source": [
    "for i in Rates.columns:\n",
    "    x='{}={}'.format(i,Rates[i][0])\n",
    "    exec(x)\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "2d905f67",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "29.5"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fwd_a_fixed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "f8ea01f2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2.0"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.ceil(0.8/0.5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "9d17c140",
   "metadata": {},
   "outputs": [],
   "source": [
    "Temp=Temp.merge(data1[['Weight slab as per X (KG)','AWB NO.']],on='AWB NO.')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "84ec2b8c",
   "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>AWB NO.</th>\n",
       "      <th>Order ID</th>\n",
       "      <th>Charged Weight</th>\n",
       "      <th>Warehouse Pincode</th>\n",
       "      <th>Customer Pincode</th>\n",
       "      <th>Zone_x</th>\n",
       "      <th>Type of Shipment</th>\n",
       "      <th>Billing Amount (Rs.)</th>\n",
       "      <th>Zone_y</th>\n",
       "      <th>Weight slab as per X (KG)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1091117222124</td>\n",
       "      <td>2001806232</td>\n",
       "      <td>1.30</td>\n",
       "      <td>121003</td>\n",
       "      <td>507101</td>\n",
       "      <td>d</td>\n",
       "      <td>Forward charges</td>\n",
       "      <td>135.0</td>\n",
       "      <td>d</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1091117222194</td>\n",
       "      <td>2001806273</td>\n",
       "      <td>1.00</td>\n",
       "      <td>121003</td>\n",
       "      <td>486886</td>\n",
       "      <td>d</td>\n",
       "      <td>Forward charges</td>\n",
       "      <td>90.2</td>\n",
       "      <td>d</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1091117222931</td>\n",
       "      <td>2001806408</td>\n",
       "      <td>2.50</td>\n",
       "      <td>121003</td>\n",
       "      <td>532484</td>\n",
       "      <td>d</td>\n",
       "      <td>Forward charges</td>\n",
       "      <td>224.6</td>\n",
       "      <td>d</td>\n",
       "      <td>2.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1091117223244</td>\n",
       "      <td>2001806458</td>\n",
       "      <td>1.00</td>\n",
       "      <td>121003</td>\n",
       "      <td>143001</td>\n",
       "      <td>b</td>\n",
       "      <td>Forward charges</td>\n",
       "      <td>61.3</td>\n",
       "      <td>b</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1091117229345</td>\n",
       "      <td>2001807012</td>\n",
       "      <td>0.15</td>\n",
       "      <td>121003</td>\n",
       "      <td>515591</td>\n",
       "      <td>d</td>\n",
       "      <td>Forward charges</td>\n",
       "      <td>45.4</td>\n",
       "      <td>d</td>\n",
       "      <td>0.5</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119</th>\n",
       "      <td>1091117904860</td>\n",
       "      <td>2001811039</td>\n",
       "      <td>0.68</td>\n",
       "      <td>121003</td>\n",
       "      <td>302020</td>\n",
       "      <td>d</td>\n",
       "      <td>Forward charges</td>\n",
       "      <td>90.2</td>\n",
       "      <td>b</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120</th>\n",
       "      <td>1091121846136</td>\n",
       "      <td>2001811305</td>\n",
       "      <td>0.50</td>\n",
       "      <td>121003</td>\n",
       "      <td>302020</td>\n",
       "      <td>d</td>\n",
       "      <td>Forward charges</td>\n",
       "      <td>45.4</td>\n",
       "      <td>b</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>1091118551656</td>\n",
       "      <td>2001812941</td>\n",
       "      <td>0.73</td>\n",
       "      <td>121003</td>\n",
       "      <td>325207</td>\n",
       "      <td>d</td>\n",
       "      <td>Forward charges</td>\n",
       "      <td>90.2</td>\n",
       "      <td>b</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>1091117614452</td>\n",
       "      <td>2001809383</td>\n",
       "      <td>0.50</td>\n",
       "      <td>121003</td>\n",
       "      <td>303702</td>\n",
       "      <td>d</td>\n",
       "      <td>Forward and RTO charges</td>\n",
       "      <td>86.7</td>\n",
       "      <td>b</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>1091120922803</td>\n",
       "      <td>2001820978</td>\n",
       "      <td>0.50</td>\n",
       "      <td>121003</td>\n",
       "      <td>313301</td>\n",
       "      <td>d</td>\n",
       "      <td>Forward charges</td>\n",
       "      <td>45.4</td>\n",
       "      <td>b</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>124 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           AWB NO.    Order ID  Charged Weight  Warehouse Pincode  \\\n",
       "0    1091117222124  2001806232            1.30             121003   \n",
       "1    1091117222194  2001806273            1.00             121003   \n",
       "2    1091117222931  2001806408            2.50             121003   \n",
       "3    1091117223244  2001806458            1.00             121003   \n",
       "4    1091117229345  2001807012            0.15             121003   \n",
       "..             ...         ...             ...                ...   \n",
       "119  1091117904860  2001811039            0.68             121003   \n",
       "120  1091121846136  2001811305            0.50             121003   \n",
       "121  1091118551656  2001812941            0.73             121003   \n",
       "122  1091117614452  2001809383            0.50             121003   \n",
       "123  1091120922803  2001820978            0.50             121003   \n",
       "\n",
       "     Customer Pincode Zone_x         Type of Shipment  Billing Amount (Rs.)  \\\n",
       "0              507101      d          Forward charges                 135.0   \n",
       "1              486886      d          Forward charges                  90.2   \n",
       "2              532484      d          Forward charges                 224.6   \n",
       "3              143001      b          Forward charges                  61.3   \n",
       "4              515591      d          Forward charges                  45.4   \n",
       "..                ...    ...                      ...                   ...   \n",
       "119            302020      d          Forward charges                  90.2   \n",
       "120            302020      d          Forward charges                  45.4   \n",
       "121            325207      d          Forward charges                  90.2   \n",
       "122            303702      d  Forward and RTO charges                  86.7   \n",
       "123            313301      d          Forward charges                  45.4   \n",
       "\n",
       "    Zone_y  Weight slab as per X (KG)  \n",
       "0        d                        1.5  \n",
       "1        d                        1.0  \n",
       "2        d                        2.5  \n",
       "3        b                        1.0  \n",
       "4        d                        0.5  \n",
       "..     ...                        ...  \n",
       "119      b                        0.5  \n",
       "120      b                        1.0  \n",
       "121      b                        0.5  \n",
       "122      b                        1.0  \n",
       "123      b                        1.0  \n",
       "\n",
       "[124 rows x 10 columns]"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Temp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "4a4db3f5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "AWB NO.                        int64\n",
       "Order ID                       int64\n",
       "Charged Weight               float64\n",
       "Warehouse Pincode              int64\n",
       "Customer Pincode               int64\n",
       "Zone_x                        object\n",
       "Type of Shipment              object\n",
       "Billing Amount (Rs.)         float64\n",
       "Zone_y                        object\n",
       "Weight slab as per X (KG)    float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Temp.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "def5bfe7",
   "metadata": {},
   "outputs": [],
   "source": [
    "#a+((np.ceil(x[1]/0.5)-1)*a_add)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "c8aebb61",
   "metadata": {},
   "outputs": [],
   "source": [
    "def Calc(x):\n",
    "    x=x.tolist()\n",
    "    #print(x)\n",
    "    if x[2]=='Forward charges':\n",
    "        if x[0]=='a':\n",
    "            return fwd_a_fixed+((np.ceil(x[1]/0.5)-1)*fwd_a_additional)\n",
    "        elif x[0]=='b':\n",
    "            return fwd_b_fixed+((np.ceil(x[1]/0.5)-1)*fwd_b_additional)\n",
    "        elif x[0]=='c':\n",
    "            return fwd_c_fixed+((np.ceil(x[1]/0.5)-1)*fwd_c_additional)\n",
    "        elif x[0]=='d':\n",
    "            return fwd_d_fixed+((np.ceil(x[1]/0.5)-1)*fwd_d_additional)\n",
    "        else:\n",
    "            return fwd_e_fixed+((np.ceil(x[1]/0.5)-1)*fwd_e_additional)\n",
    "    else:\n",
    "        if x[0]=='a':\n",
    "            return fwd_a_fixed+((np.ceil(x[1]/0.5)-1)*fwd_a_additional)+rto_a_fixed+((np.ceil(x[1]/0.5)-1)*rto_a_additional)\n",
    "        elif x[0]=='b':\n",
    "            return fwd_b_fixed+((np.ceil(x[1]/0.5)-1)*fwd_a_additional)+rto_b_fixed+((np.ceil(x[1]/0.5)-1)*rto_a_additional)\n",
    "        elif x[0]=='c':\n",
    "            return fwd_c_fixed+((np.ceil(x[1]/0.5)-1)*fwd_a_additional)+rto_c_fixed+((np.ceil(x[1]/0.5)-1)*rto_a_additional)\n",
    "        elif x[0]=='d':\n",
    "            return fwd_d_fixed+((np.ceil(x[1]/0.5)-1)*fwd_a_additional)+rto_d_fixed+((np.ceil(x[1]/0.5)-1)*rto_a_additional)\n",
    "        else:\n",
    "            return fwd_e_fixed+((np.ceil(x[1]/0.5)-1)*fwd_a_additional)+rto_e_fixed+((np.ceil(x[1]/0.5)-1)*rto_a_additional)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "6ae02005",
   "metadata": {},
   "outputs": [],
   "source": [
    "Temp['Expected Charge as per X (Rs.)']=Temp[['Zone_y','Weight slab as per X (KG)','Type of Shipment']].apply(Calc,axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "241a1cbe",
   "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>AWB NO.</th>\n",
       "      <th>Order ID</th>\n",
       "      <th>Charged Weight</th>\n",
       "      <th>Warehouse Pincode</th>\n",
       "      <th>Customer Pincode</th>\n",
       "      <th>Zone_x</th>\n",
       "      <th>Type of Shipment</th>\n",
       "      <th>Billing Amount (Rs.)</th>\n",
       "      <th>Zone_y</th>\n",
       "      <th>Weight slab as per X (KG)</th>\n",
       "      <th>Expected Charge as per X (Rs.)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1091117222124</td>\n",
       "      <td>2001806232</td>\n",
       "      <td>1.30</td>\n",
       "      <td>121003</td>\n",
       "      <td>507101</td>\n",
       "      <td>d</td>\n",
       "      <td>Forward charges</td>\n",
       "      <td>135.0</td>\n",
       "      <td>d</td>\n",
       "      <td>1.5</td>\n",
       "      <td>135.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1091117222194</td>\n",
       "      <td>2001806273</td>\n",
       "      <td>1.00</td>\n",
       "      <td>121003</td>\n",
       "      <td>486886</td>\n",
       "      <td>d</td>\n",
       "      <td>Forward charges</td>\n",
       "      <td>90.2</td>\n",
       "      <td>d</td>\n",
       "      <td>1.0</td>\n",
       "      <td>90.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1091117222931</td>\n",
       "      <td>2001806408</td>\n",
       "      <td>2.50</td>\n",
       "      <td>121003</td>\n",
       "      <td>532484</td>\n",
       "      <td>d</td>\n",
       "      <td>Forward charges</td>\n",
       "      <td>224.6</td>\n",
       "      <td>d</td>\n",
       "      <td>2.5</td>\n",
       "      <td>224.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1091117223244</td>\n",
       "      <td>2001806458</td>\n",
       "      <td>1.00</td>\n",
       "      <td>121003</td>\n",
       "      <td>143001</td>\n",
       "      <td>b</td>\n",
       "      <td>Forward charges</td>\n",
       "      <td>61.3</td>\n",
       "      <td>b</td>\n",
       "      <td>1.0</td>\n",
       "      <td>61.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1091117229345</td>\n",
       "      <td>2001807012</td>\n",
       "      <td>0.15</td>\n",
       "      <td>121003</td>\n",
       "      <td>515591</td>\n",
       "      <td>d</td>\n",
       "      <td>Forward charges</td>\n",
       "      <td>45.4</td>\n",
       "      <td>d</td>\n",
       "      <td>0.5</td>\n",
       "      <td>45.4</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119</th>\n",
       "      <td>1091117904860</td>\n",
       "      <td>2001811039</td>\n",
       "      <td>0.68</td>\n",
       "      <td>121003</td>\n",
       "      <td>302020</td>\n",
       "      <td>d</td>\n",
       "      <td>Forward charges</td>\n",
       "      <td>90.2</td>\n",
       "      <td>b</td>\n",
       "      <td>0.5</td>\n",
       "      <td>33.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120</th>\n",
       "      <td>1091121846136</td>\n",
       "      <td>2001811305</td>\n",
       "      <td>0.50</td>\n",
       "      <td>121003</td>\n",
       "      <td>302020</td>\n",
       "      <td>d</td>\n",
       "      <td>Forward charges</td>\n",
       "      <td>45.4</td>\n",
       "      <td>b</td>\n",
       "      <td>1.0</td>\n",
       "      <td>61.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>1091118551656</td>\n",
       "      <td>2001812941</td>\n",
       "      <td>0.73</td>\n",
       "      <td>121003</td>\n",
       "      <td>325207</td>\n",
       "      <td>d</td>\n",
       "      <td>Forward charges</td>\n",
       "      <td>90.2</td>\n",
       "      <td>b</td>\n",
       "      <td>0.5</td>\n",
       "      <td>33.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>1091117614452</td>\n",
       "      <td>2001809383</td>\n",
       "      <td>0.50</td>\n",
       "      <td>121003</td>\n",
       "      <td>303702</td>\n",
       "      <td>d</td>\n",
       "      <td>Forward and RTO charges</td>\n",
       "      <td>86.7</td>\n",
       "      <td>b</td>\n",
       "      <td>1.0</td>\n",
       "      <td>100.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>1091120922803</td>\n",
       "      <td>2001820978</td>\n",
       "      <td>0.50</td>\n",
       "      <td>121003</td>\n",
       "      <td>313301</td>\n",
       "      <td>d</td>\n",
       "      <td>Forward charges</td>\n",
       "      <td>45.4</td>\n",
       "      <td>b</td>\n",
       "      <td>1.0</td>\n",
       "      <td>61.3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>124 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           AWB NO.    Order ID  Charged Weight  Warehouse Pincode  \\\n",
       "0    1091117222124  2001806232            1.30             121003   \n",
       "1    1091117222194  2001806273            1.00             121003   \n",
       "2    1091117222931  2001806408            2.50             121003   \n",
       "3    1091117223244  2001806458            1.00             121003   \n",
       "4    1091117229345  2001807012            0.15             121003   \n",
       "..             ...         ...             ...                ...   \n",
       "119  1091117904860  2001811039            0.68             121003   \n",
       "120  1091121846136  2001811305            0.50             121003   \n",
       "121  1091118551656  2001812941            0.73             121003   \n",
       "122  1091117614452  2001809383            0.50             121003   \n",
       "123  1091120922803  2001820978            0.50             121003   \n",
       "\n",
       "     Customer Pincode Zone_x         Type of Shipment  Billing Amount (Rs.)  \\\n",
       "0              507101      d          Forward charges                 135.0   \n",
       "1              486886      d          Forward charges                  90.2   \n",
       "2              532484      d          Forward charges                 224.6   \n",
       "3              143001      b          Forward charges                  61.3   \n",
       "4              515591      d          Forward charges                  45.4   \n",
       "..                ...    ...                      ...                   ...   \n",
       "119            302020      d          Forward charges                  90.2   \n",
       "120            302020      d          Forward charges                  45.4   \n",
       "121            325207      d          Forward charges                  90.2   \n",
       "122            303702      d  Forward and RTO charges                  86.7   \n",
       "123            313301      d          Forward charges                  45.4   \n",
       "\n",
       "    Zone_y  Weight slab as per X (KG)  Expected Charge as per X (Rs.)  \n",
       "0        d                        1.5                           135.0  \n",
       "1        d                        1.0                            90.2  \n",
       "2        d                        2.5                           224.6  \n",
       "3        b                        1.0                            61.3  \n",
       "4        d                        0.5                            45.4  \n",
       "..     ...                        ...                             ...  \n",
       "119      b                        0.5                            33.0  \n",
       "120      b                        1.0                            61.3  \n",
       "121      b                        0.5                            33.0  \n",
       "122      b                        1.0                           100.7  \n",
       "123      b                        1.0                            61.3  \n",
       "\n",
       "[124 rows x 11 columns]"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Temp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "4587289a",
   "metadata": {},
   "outputs": [],
   "source": [
    "data1=data1.merge(Temp[['Expected Charge as per X (Rs.)','AWB NO.']],on='AWB NO.')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "7b1a0d37",
   "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>AWB NO.</th>\n",
       "      <th>Total weight as per X (KG)</th>\n",
       "      <th>Weight slab as per X (KG)</th>\n",
       "      <th>Total weight as per Courier Company (KG)</th>\n",
       "      <th>Weight slab charged by Courier Company (KG)</th>\n",
       "      <th>Delivery Zone as per X</th>\n",
       "      <th>Delivery Zone charged by Courier Company</th>\n",
       "      <th>Expected Charge as per X (Rs.)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2001806232</td>\n",
       "      <td>1091117222124</td>\n",
       "      <td>1.302</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.30</td>\n",
       "      <td>1.5</td>\n",
       "      <td>d</td>\n",
       "      <td>d</td>\n",
       "      <td>135.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2001806273</td>\n",
       "      <td>1091117222194</td>\n",
       "      <td>0.615</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>d</td>\n",
       "      <td>d</td>\n",
       "      <td>90.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2001806408</td>\n",
       "      <td>1091117222931</td>\n",
       "      <td>2.265</td>\n",
       "      <td>2.5</td>\n",
       "      <td>2.50</td>\n",
       "      <td>2.5</td>\n",
       "      <td>d</td>\n",
       "      <td>d</td>\n",
       "      <td>224.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2001806458</td>\n",
       "      <td>1091117223244</td>\n",
       "      <td>0.700</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>b</td>\n",
       "      <td>b</td>\n",
       "      <td>61.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2001807012</td>\n",
       "      <td>1091117229345</td>\n",
       "      <td>0.240</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.15</td>\n",
       "      <td>0.5</td>\n",
       "      <td>d</td>\n",
       "      <td>d</td>\n",
       "      <td>45.4</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119</th>\n",
       "      <td>2001812941</td>\n",
       "      <td>1091118551656</td>\n",
       "      <td>0.500</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.73</td>\n",
       "      <td>1.0</td>\n",
       "      <td>b</td>\n",
       "      <td>d</td>\n",
       "      <td>33.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120</th>\n",
       "      <td>2001809383</td>\n",
       "      <td>1091117614452</td>\n",
       "      <td>0.607</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.50</td>\n",
       "      <td>0.5</td>\n",
       "      <td>b</td>\n",
       "      <td>d</td>\n",
       "      <td>100.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>2001820978</td>\n",
       "      <td>1091120922803</td>\n",
       "      <td>0.515</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.50</td>\n",
       "      <td>0.5</td>\n",
       "      <td>b</td>\n",
       "      <td>d</td>\n",
       "      <td>61.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>2001811475</td>\n",
       "      <td>1091121844806</td>\n",
       "      <td>0.689</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.50</td>\n",
       "      <td>0.5</td>\n",
       "      <td>e</td>\n",
       "      <td>b</td>\n",
       "      <td>112.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>2001811305</td>\n",
       "      <td>1091121846136</td>\n",
       "      <td>0.750</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.50</td>\n",
       "      <td>0.5</td>\n",
       "      <td>b</td>\n",
       "      <td>d</td>\n",
       "      <td>61.3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>124 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Order ID        AWB NO.  Total weight as per X (KG)  \\\n",
       "0    2001806232  1091117222124                       1.302   \n",
       "1    2001806273  1091117222194                       0.615   \n",
       "2    2001806408  1091117222931                       2.265   \n",
       "3    2001806458  1091117223244                       0.700   \n",
       "4    2001807012  1091117229345                       0.240   \n",
       "..          ...            ...                         ...   \n",
       "119  2001812941  1091118551656                       0.500   \n",
       "120  2001809383  1091117614452                       0.607   \n",
       "121  2001820978  1091120922803                       0.515   \n",
       "122  2001811475  1091121844806                       0.689   \n",
       "123  2001811305  1091121846136                       0.750   \n",
       "\n",
       "     Weight slab as per X (KG)  Total weight as per Courier Company (KG)  \\\n",
       "0                          1.5                                      1.30   \n",
       "1                          1.0                                      1.00   \n",
       "2                          2.5                                      2.50   \n",
       "3                          1.0                                      1.00   \n",
       "4                          0.5                                      0.15   \n",
       "..                         ...                                       ...   \n",
       "119                        0.5                                      0.73   \n",
       "120                        1.0                                      0.50   \n",
       "121                        1.0                                      0.50   \n",
       "122                        1.0                                      0.50   \n",
       "123                        1.0                                      0.50   \n",
       "\n",
       "     Weight slab charged by Courier Company (KG) Delivery Zone as per X  \\\n",
       "0                                            1.5                      d   \n",
       "1                                            1.0                      d   \n",
       "2                                            2.5                      d   \n",
       "3                                            1.0                      b   \n",
       "4                                            0.5                      d   \n",
       "..                                           ...                    ...   \n",
       "119                                          1.0                      b   \n",
       "120                                          0.5                      b   \n",
       "121                                          0.5                      b   \n",
       "122                                          0.5                      e   \n",
       "123                                          0.5                      b   \n",
       "\n",
       "    Delivery Zone charged by Courier Company  Expected Charge as per X (Rs.)  \n",
       "0                                          d                           135.0  \n",
       "1                                          d                            90.2  \n",
       "2                                          d                           224.6  \n",
       "3                                          b                            61.3  \n",
       "4                                          d                            45.4  \n",
       "..                                       ...                             ...  \n",
       "119                                        d                            33.0  \n",
       "120                                        d                           100.7  \n",
       "121                                        d                            61.3  \n",
       "122                                        b                           112.1  \n",
       "123                                        d                            61.3  \n",
       "\n",
       "[124 rows x 9 columns]"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "31a2002f",
   "metadata": {},
   "outputs": [],
   "source": [
    "Temp=Temp.merge(data1[['Weight slab charged by Courier Company (KG)','AWB NO.']],on='AWB NO.')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "a5c57e6e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(124, 12)"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Temp.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "c04dc2df",
   "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>AWB NO.</th>\n",
       "      <th>Order ID</th>\n",
       "      <th>Charged Weight</th>\n",
       "      <th>Warehouse Pincode</th>\n",
       "      <th>Customer Pincode</th>\n",
       "      <th>Zone</th>\n",
       "      <th>Type of Shipment</th>\n",
       "      <th>Billing Amount (Rs.)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1091117222124</td>\n",
       "      <td>2001806232</td>\n",
       "      <td>1.30</td>\n",
       "      <td>121003</td>\n",
       "      <td>507101</td>\n",
       "      <td>d</td>\n",
       "      <td>Forward charges</td>\n",
       "      <td>135.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1091117222194</td>\n",
       "      <td>2001806273</td>\n",
       "      <td>1.00</td>\n",
       "      <td>121003</td>\n",
       "      <td>486886</td>\n",
       "      <td>d</td>\n",
       "      <td>Forward charges</td>\n",
       "      <td>90.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1091117222931</td>\n",
       "      <td>2001806408</td>\n",
       "      <td>2.50</td>\n",
       "      <td>121003</td>\n",
       "      <td>532484</td>\n",
       "      <td>d</td>\n",
       "      <td>Forward charges</td>\n",
       "      <td>224.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1091117223244</td>\n",
       "      <td>2001806458</td>\n",
       "      <td>1.00</td>\n",
       "      <td>121003</td>\n",
       "      <td>143001</td>\n",
       "      <td>b</td>\n",
       "      <td>Forward charges</td>\n",
       "      <td>61.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1091117229345</td>\n",
       "      <td>2001807012</td>\n",
       "      <td>0.15</td>\n",
       "      <td>121003</td>\n",
       "      <td>515591</td>\n",
       "      <td>d</td>\n",
       "      <td>Forward charges</td>\n",
       "      <td>45.4</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119</th>\n",
       "      <td>1091118551656</td>\n",
       "      <td>2001812941</td>\n",
       "      <td>0.73</td>\n",
       "      <td>121003</td>\n",
       "      <td>325207</td>\n",
       "      <td>d</td>\n",
       "      <td>Forward charges</td>\n",
       "      <td>90.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120</th>\n",
       "      <td>1091117614452</td>\n",
       "      <td>2001809383</td>\n",
       "      <td>0.50</td>\n",
       "      <td>121003</td>\n",
       "      <td>303702</td>\n",
       "      <td>d</td>\n",
       "      <td>Forward and RTO charges</td>\n",
       "      <td>86.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>1091120922803</td>\n",
       "      <td>2001820978</td>\n",
       "      <td>0.50</td>\n",
       "      <td>121003</td>\n",
       "      <td>313301</td>\n",
       "      <td>d</td>\n",
       "      <td>Forward charges</td>\n",
       "      <td>45.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>1091121844806</td>\n",
       "      <td>2001811475</td>\n",
       "      <td>0.50</td>\n",
       "      <td>121003</td>\n",
       "      <td>173212</td>\n",
       "      <td>b</td>\n",
       "      <td>Forward charges</td>\n",
       "      <td>33.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>1091121846136</td>\n",
       "      <td>2001811305</td>\n",
       "      <td>0.50</td>\n",
       "      <td>121003</td>\n",
       "      <td>302020</td>\n",
       "      <td>d</td>\n",
       "      <td>Forward charges</td>\n",
       "      <td>45.4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>124 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           AWB NO.    Order ID  Charged Weight  Warehouse Pincode  \\\n",
       "0    1091117222124  2001806232            1.30             121003   \n",
       "1    1091117222194  2001806273            1.00             121003   \n",
       "2    1091117222931  2001806408            2.50             121003   \n",
       "3    1091117223244  2001806458            1.00             121003   \n",
       "4    1091117229345  2001807012            0.15             121003   \n",
       "..             ...         ...             ...                ...   \n",
       "119  1091118551656  2001812941            0.73             121003   \n",
       "120  1091117614452  2001809383            0.50             121003   \n",
       "121  1091120922803  2001820978            0.50             121003   \n",
       "122  1091121844806  2001811475            0.50             121003   \n",
       "123  1091121846136  2001811305            0.50             121003   \n",
       "\n",
       "     Customer Pincode Zone         Type of Shipment  Billing Amount (Rs.)  \n",
       "0              507101    d          Forward charges                 135.0  \n",
       "1              486886    d          Forward charges                  90.2  \n",
       "2              532484    d          Forward charges                 224.6  \n",
       "3              143001    b          Forward charges                  61.3  \n",
       "4              515591    d          Forward charges                  45.4  \n",
       "..                ...  ...                      ...                   ...  \n",
       "119            325207    d          Forward charges                  90.2  \n",
       "120            303702    d  Forward and RTO charges                  86.7  \n",
       "121            313301    d          Forward charges                  45.4  \n",
       "122            173212    b          Forward charges                  33.0  \n",
       "123            302020    d          Forward charges                  45.4  \n",
       "\n",
       "[124 rows x 8 columns]"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Invoice"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "10924cf6",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Temp['Charges Billed by Courier Company (Rs.)']=Temp[['Zone_x','Weight slab charged by Courier Company (KG)','Type of Shipment']].apply(Calc,axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "c9df5e0b",
   "metadata": {},
   "outputs": [],
   "source": [
    "data1=data1.merge(Invoice[['Billing Amount (Rs.)','AWB NO.']],on='AWB NO.')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "0e62e1d2",
   "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>AWB NO.</th>\n",
       "      <th>Order ID</th>\n",
       "      <th>Charged Weight</th>\n",
       "      <th>Warehouse Pincode</th>\n",
       "      <th>Customer Pincode</th>\n",
       "      <th>Zone_x</th>\n",
       "      <th>Type of Shipment</th>\n",
       "      <th>Billing Amount (Rs.)</th>\n",
       "      <th>Zone_y</th>\n",
       "      <th>Weight slab as per X (KG)</th>\n",
       "      <th>Expected Charge as per X (Rs.)</th>\n",
       "      <th>Weight slab charged by Courier Company (KG)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1091117222124</td>\n",
       "      <td>2001806232</td>\n",
       "      <td>1.30</td>\n",
       "      <td>121003</td>\n",
       "      <td>507101</td>\n",
       "      <td>d</td>\n",
       "      <td>Forward charges</td>\n",
       "      <td>135.0</td>\n",
       "      <td>d</td>\n",
       "      <td>1.5</td>\n",
       "      <td>135.0</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1091117222194</td>\n",
       "      <td>2001806273</td>\n",
       "      <td>1.00</td>\n",
       "      <td>121003</td>\n",
       "      <td>486886</td>\n",
       "      <td>d</td>\n",
       "      <td>Forward charges</td>\n",
       "      <td>90.2</td>\n",
       "      <td>d</td>\n",
       "      <td>1.0</td>\n",
       "      <td>90.2</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1091117222931</td>\n",
       "      <td>2001806408</td>\n",
       "      <td>2.50</td>\n",
       "      <td>121003</td>\n",
       "      <td>532484</td>\n",
       "      <td>d</td>\n",
       "      <td>Forward charges</td>\n",
       "      <td>224.6</td>\n",
       "      <td>d</td>\n",
       "      <td>2.5</td>\n",
       "      <td>224.6</td>\n",
       "      <td>2.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1091117223244</td>\n",
       "      <td>2001806458</td>\n",
       "      <td>1.00</td>\n",
       "      <td>121003</td>\n",
       "      <td>143001</td>\n",
       "      <td>b</td>\n",
       "      <td>Forward charges</td>\n",
       "      <td>61.3</td>\n",
       "      <td>b</td>\n",
       "      <td>1.0</td>\n",
       "      <td>61.3</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1091117229345</td>\n",
       "      <td>2001807012</td>\n",
       "      <td>0.15</td>\n",
       "      <td>121003</td>\n",
       "      <td>515591</td>\n",
       "      <td>d</td>\n",
       "      <td>Forward charges</td>\n",
       "      <td>45.4</td>\n",
       "      <td>d</td>\n",
       "      <td>0.5</td>\n",
       "      <td>45.4</td>\n",
       "      <td>0.5</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119</th>\n",
       "      <td>1091117904860</td>\n",
       "      <td>2001811039</td>\n",
       "      <td>0.68</td>\n",
       "      <td>121003</td>\n",
       "      <td>302020</td>\n",
       "      <td>d</td>\n",
       "      <td>Forward charges</td>\n",
       "      <td>90.2</td>\n",
       "      <td>b</td>\n",
       "      <td>0.5</td>\n",
       "      <td>33.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120</th>\n",
       "      <td>1091121846136</td>\n",
       "      <td>2001811305</td>\n",
       "      <td>0.50</td>\n",
       "      <td>121003</td>\n",
       "      <td>302020</td>\n",
       "      <td>d</td>\n",
       "      <td>Forward charges</td>\n",
       "      <td>45.4</td>\n",
       "      <td>b</td>\n",
       "      <td>1.0</td>\n",
       "      <td>61.3</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>1091118551656</td>\n",
       "      <td>2001812941</td>\n",
       "      <td>0.73</td>\n",
       "      <td>121003</td>\n",
       "      <td>325207</td>\n",
       "      <td>d</td>\n",
       "      <td>Forward charges</td>\n",
       "      <td>90.2</td>\n",
       "      <td>b</td>\n",
       "      <td>0.5</td>\n",
       "      <td>33.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>1091117614452</td>\n",
       "      <td>2001809383</td>\n",
       "      <td>0.50</td>\n",
       "      <td>121003</td>\n",
       "      <td>303702</td>\n",
       "      <td>d</td>\n",
       "      <td>Forward and RTO charges</td>\n",
       "      <td>86.7</td>\n",
       "      <td>b</td>\n",
       "      <td>1.0</td>\n",
       "      <td>100.7</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>1091120922803</td>\n",
       "      <td>2001820978</td>\n",
       "      <td>0.50</td>\n",
       "      <td>121003</td>\n",
       "      <td>313301</td>\n",
       "      <td>d</td>\n",
       "      <td>Forward charges</td>\n",
       "      <td>45.4</td>\n",
       "      <td>b</td>\n",
       "      <td>1.0</td>\n",
       "      <td>61.3</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>124 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           AWB NO.    Order ID  Charged Weight  Warehouse Pincode  \\\n",
       "0    1091117222124  2001806232            1.30             121003   \n",
       "1    1091117222194  2001806273            1.00             121003   \n",
       "2    1091117222931  2001806408            2.50             121003   \n",
       "3    1091117223244  2001806458            1.00             121003   \n",
       "4    1091117229345  2001807012            0.15             121003   \n",
       "..             ...         ...             ...                ...   \n",
       "119  1091117904860  2001811039            0.68             121003   \n",
       "120  1091121846136  2001811305            0.50             121003   \n",
       "121  1091118551656  2001812941            0.73             121003   \n",
       "122  1091117614452  2001809383            0.50             121003   \n",
       "123  1091120922803  2001820978            0.50             121003   \n",
       "\n",
       "     Customer Pincode Zone_x         Type of Shipment  Billing Amount (Rs.)  \\\n",
       "0              507101      d          Forward charges                 135.0   \n",
       "1              486886      d          Forward charges                  90.2   \n",
       "2              532484      d          Forward charges                 224.6   \n",
       "3              143001      b          Forward charges                  61.3   \n",
       "4              515591      d          Forward charges                  45.4   \n",
       "..                ...    ...                      ...                   ...   \n",
       "119            302020      d          Forward charges                  90.2   \n",
       "120            302020      d          Forward charges                  45.4   \n",
       "121            325207      d          Forward charges                  90.2   \n",
       "122            303702      d  Forward and RTO charges                  86.7   \n",
       "123            313301      d          Forward charges                  45.4   \n",
       "\n",
       "    Zone_y  Weight slab as per X (KG)  Expected Charge as per X (Rs.)  \\\n",
       "0        d                        1.5                           135.0   \n",
       "1        d                        1.0                            90.2   \n",
       "2        d                        2.5                           224.6   \n",
       "3        b                        1.0                            61.3   \n",
       "4        d                        0.5                            45.4   \n",
       "..     ...                        ...                             ...   \n",
       "119      b                        0.5                            33.0   \n",
       "120      b                        1.0                            61.3   \n",
       "121      b                        0.5                            33.0   \n",
       "122      b                        1.0                           100.7   \n",
       "123      b                        1.0                            61.3   \n",
       "\n",
       "     Weight slab charged by Courier Company (KG)  \n",
       "0                                            1.5  \n",
       "1                                            1.0  \n",
       "2                                            2.5  \n",
       "3                                            1.0  \n",
       "4                                            0.5  \n",
       "..                                           ...  \n",
       "119                                          1.0  \n",
       "120                                          0.5  \n",
       "121                                          1.0  \n",
       "122                                          0.5  \n",
       "123                                          0.5  \n",
       "\n",
       "[124 rows x 12 columns]"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Temp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "245d35ac",
   "metadata": {},
   "outputs": [],
   "source": [
    "#data1=data1.merge(Temp[['Charges Billed by Courier Company (Rs.)','AWB NO.']],on='AWB NO.')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "1be85f88",
   "metadata": {},
   "outputs": [],
   "source": [
    "data1.rename({'Billing Amount (Rs.)':'Charges Billed by Courier Company (Rs.)'},inplace=True,axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "44025a15",
   "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>AWB NO.</th>\n",
       "      <th>Total weight as per X (KG)</th>\n",
       "      <th>Weight slab as per X (KG)</th>\n",
       "      <th>Total weight as per Courier Company (KG)</th>\n",
       "      <th>Weight slab charged by Courier Company (KG)</th>\n",
       "      <th>Delivery Zone as per X</th>\n",
       "      <th>Delivery Zone charged by Courier Company</th>\n",
       "      <th>Expected Charge as per X (Rs.)</th>\n",
       "      <th>Charges Billed by Courier Company (Rs.)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2001806232</td>\n",
       "      <td>1091117222124</td>\n",
       "      <td>1.302</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.30</td>\n",
       "      <td>1.5</td>\n",
       "      <td>d</td>\n",
       "      <td>d</td>\n",
       "      <td>135.0</td>\n",
       "      <td>135.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2001806273</td>\n",
       "      <td>1091117222194</td>\n",
       "      <td>0.615</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>d</td>\n",
       "      <td>d</td>\n",
       "      <td>90.2</td>\n",
       "      <td>90.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2001806408</td>\n",
       "      <td>1091117222931</td>\n",
       "      <td>2.265</td>\n",
       "      <td>2.5</td>\n",
       "      <td>2.50</td>\n",
       "      <td>2.5</td>\n",
       "      <td>d</td>\n",
       "      <td>d</td>\n",
       "      <td>224.6</td>\n",
       "      <td>224.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2001806458</td>\n",
       "      <td>1091117223244</td>\n",
       "      <td>0.700</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>b</td>\n",
       "      <td>b</td>\n",
       "      <td>61.3</td>\n",
       "      <td>61.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2001807012</td>\n",
       "      <td>1091117229345</td>\n",
       "      <td>0.240</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.15</td>\n",
       "      <td>0.5</td>\n",
       "      <td>d</td>\n",
       "      <td>d</td>\n",
       "      <td>45.4</td>\n",
       "      <td>45.4</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119</th>\n",
       "      <td>2001812941</td>\n",
       "      <td>1091118551656</td>\n",
       "      <td>0.500</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.73</td>\n",
       "      <td>1.0</td>\n",
       "      <td>b</td>\n",
       "      <td>d</td>\n",
       "      <td>33.0</td>\n",
       "      <td>90.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120</th>\n",
       "      <td>2001809383</td>\n",
       "      <td>1091117614452</td>\n",
       "      <td>0.607</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.50</td>\n",
       "      <td>0.5</td>\n",
       "      <td>b</td>\n",
       "      <td>d</td>\n",
       "      <td>100.7</td>\n",
       "      <td>86.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>2001820978</td>\n",
       "      <td>1091120922803</td>\n",
       "      <td>0.515</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.50</td>\n",
       "      <td>0.5</td>\n",
       "      <td>b</td>\n",
       "      <td>d</td>\n",
       "      <td>61.3</td>\n",
       "      <td>45.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>2001811475</td>\n",
       "      <td>1091121844806</td>\n",
       "      <td>0.689</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.50</td>\n",
       "      <td>0.5</td>\n",
       "      <td>e</td>\n",
       "      <td>b</td>\n",
       "      <td>112.1</td>\n",
       "      <td>33.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>2001811305</td>\n",
       "      <td>1091121846136</td>\n",
       "      <td>0.750</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.50</td>\n",
       "      <td>0.5</td>\n",
       "      <td>b</td>\n",
       "      <td>d</td>\n",
       "      <td>61.3</td>\n",
       "      <td>45.4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>124 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Order ID        AWB NO.  Total weight as per X (KG)  \\\n",
       "0    2001806232  1091117222124                       1.302   \n",
       "1    2001806273  1091117222194                       0.615   \n",
       "2    2001806408  1091117222931                       2.265   \n",
       "3    2001806458  1091117223244                       0.700   \n",
       "4    2001807012  1091117229345                       0.240   \n",
       "..          ...            ...                         ...   \n",
       "119  2001812941  1091118551656                       0.500   \n",
       "120  2001809383  1091117614452                       0.607   \n",
       "121  2001820978  1091120922803                       0.515   \n",
       "122  2001811475  1091121844806                       0.689   \n",
       "123  2001811305  1091121846136                       0.750   \n",
       "\n",
       "     Weight slab as per X (KG)  Total weight as per Courier Company (KG)  \\\n",
       "0                          1.5                                      1.30   \n",
       "1                          1.0                                      1.00   \n",
       "2                          2.5                                      2.50   \n",
       "3                          1.0                                      1.00   \n",
       "4                          0.5                                      0.15   \n",
       "..                         ...                                       ...   \n",
       "119                        0.5                                      0.73   \n",
       "120                        1.0                                      0.50   \n",
       "121                        1.0                                      0.50   \n",
       "122                        1.0                                      0.50   \n",
       "123                        1.0                                      0.50   \n",
       "\n",
       "     Weight slab charged by Courier Company (KG) Delivery Zone as per X  \\\n",
       "0                                            1.5                      d   \n",
       "1                                            1.0                      d   \n",
       "2                                            2.5                      d   \n",
       "3                                            1.0                      b   \n",
       "4                                            0.5                      d   \n",
       "..                                           ...                    ...   \n",
       "119                                          1.0                      b   \n",
       "120                                          0.5                      b   \n",
       "121                                          0.5                      b   \n",
       "122                                          0.5                      e   \n",
       "123                                          0.5                      b   \n",
       "\n",
       "    Delivery Zone charged by Courier Company  Expected Charge as per X (Rs.)  \\\n",
       "0                                          d                           135.0   \n",
       "1                                          d                            90.2   \n",
       "2                                          d                           224.6   \n",
       "3                                          b                            61.3   \n",
       "4                                          d                            45.4   \n",
       "..                                       ...                             ...   \n",
       "119                                        d                            33.0   \n",
       "120                                        d                           100.7   \n",
       "121                                        d                            61.3   \n",
       "122                                        b                           112.1   \n",
       "123                                        d                            61.3   \n",
       "\n",
       "     Charges Billed by Courier Company (Rs.)  \n",
       "0                                      135.0  \n",
       "1                                       90.2  \n",
       "2                                      224.6  \n",
       "3                                       61.3  \n",
       "4                                       45.4  \n",
       "..                                       ...  \n",
       "119                                     90.2  \n",
       "120                                     86.7  \n",
       "121                                     45.4  \n",
       "122                                     33.0  \n",
       "123                                     45.4  \n",
       "\n",
       "[124 rows x 10 columns]"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "edce95e4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(124, 10)"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data1.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "af0b1499",
   "metadata": {},
   "outputs": [],
   "source": [
    "data1['Difference Between Expected Charges and Billed Charges (Rs.)']=data1['Expected Charge as per X (Rs.)']-data1['Charges Billed by Courier Company (Rs.)']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "id": "2d24995c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(124, 11)"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data1.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "ede45127",
   "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>AWB NO.</th>\n",
       "      <th>Total weight as per X (KG)</th>\n",
       "      <th>Weight slab as per X (KG)</th>\n",
       "      <th>Total weight as per Courier Company (KG)</th>\n",
       "      <th>Weight slab charged by Courier Company (KG)</th>\n",
       "      <th>Delivery Zone as per X</th>\n",
       "      <th>Delivery Zone charged by Courier Company</th>\n",
       "      <th>Expected Charge as per X (Rs.)</th>\n",
       "      <th>Charges Billed by Courier Company (Rs.)</th>\n",
       "      <th>Difference Between Expected Charges and Billed Charges (Rs.)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2001806232</td>\n",
       "      <td>1091117222124</td>\n",
       "      <td>1.302</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.30</td>\n",
       "      <td>1.5</td>\n",
       "      <td>d</td>\n",
       "      <td>d</td>\n",
       "      <td>135.0</td>\n",
       "      <td>135.0</td>\n",
       "      <td>0.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2001806273</td>\n",
       "      <td>1091117222194</td>\n",
       "      <td>0.615</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>d</td>\n",
       "      <td>d</td>\n",
       "      <td>90.2</td>\n",
       "      <td>90.2</td>\n",
       "      <td>-1.421085e-14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2001806408</td>\n",
       "      <td>1091117222931</td>\n",
       "      <td>2.265</td>\n",
       "      <td>2.5</td>\n",
       "      <td>2.50</td>\n",
       "      <td>2.5</td>\n",
       "      <td>d</td>\n",
       "      <td>d</td>\n",
       "      <td>224.6</td>\n",
       "      <td>224.6</td>\n",
       "      <td>0.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2001806458</td>\n",
       "      <td>1091117223244</td>\n",
       "      <td>0.700</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>b</td>\n",
       "      <td>b</td>\n",
       "      <td>61.3</td>\n",
       "      <td>61.3</td>\n",
       "      <td>0.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2001807012</td>\n",
       "      <td>1091117229345</td>\n",
       "      <td>0.240</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.15</td>\n",
       "      <td>0.5</td>\n",
       "      <td>d</td>\n",
       "      <td>d</td>\n",
       "      <td>45.4</td>\n",
       "      <td>45.4</td>\n",
       "      <td>0.000000e+00</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119</th>\n",
       "      <td>2001812941</td>\n",
       "      <td>1091118551656</td>\n",
       "      <td>0.500</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.73</td>\n",
       "      <td>1.0</td>\n",
       "      <td>b</td>\n",
       "      <td>d</td>\n",
       "      <td>33.0</td>\n",
       "      <td>90.2</td>\n",
       "      <td>-5.720000e+01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120</th>\n",
       "      <td>2001809383</td>\n",
       "      <td>1091117614452</td>\n",
       "      <td>0.607</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.50</td>\n",
       "      <td>0.5</td>\n",
       "      <td>b</td>\n",
       "      <td>d</td>\n",
       "      <td>100.7</td>\n",
       "      <td>86.7</td>\n",
       "      <td>1.400000e+01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>2001820978</td>\n",
       "      <td>1091120922803</td>\n",
       "      <td>0.515</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.50</td>\n",
       "      <td>0.5</td>\n",
       "      <td>b</td>\n",
       "      <td>d</td>\n",
       "      <td>61.3</td>\n",
       "      <td>45.4</td>\n",
       "      <td>1.590000e+01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>2001811475</td>\n",
       "      <td>1091121844806</td>\n",
       "      <td>0.689</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.50</td>\n",
       "      <td>0.5</td>\n",
       "      <td>e</td>\n",
       "      <td>b</td>\n",
       "      <td>112.1</td>\n",
       "      <td>33.0</td>\n",
       "      <td>7.910000e+01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>2001811305</td>\n",
       "      <td>1091121846136</td>\n",
       "      <td>0.750</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.50</td>\n",
       "      <td>0.5</td>\n",
       "      <td>b</td>\n",
       "      <td>d</td>\n",
       "      <td>61.3</td>\n",
       "      <td>45.4</td>\n",
       "      <td>1.590000e+01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>124 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Order ID        AWB NO.  Total weight as per X (KG)  \\\n",
       "0    2001806232  1091117222124                       1.302   \n",
       "1    2001806273  1091117222194                       0.615   \n",
       "2    2001806408  1091117222931                       2.265   \n",
       "3    2001806458  1091117223244                       0.700   \n",
       "4    2001807012  1091117229345                       0.240   \n",
       "..          ...            ...                         ...   \n",
       "119  2001812941  1091118551656                       0.500   \n",
       "120  2001809383  1091117614452                       0.607   \n",
       "121  2001820978  1091120922803                       0.515   \n",
       "122  2001811475  1091121844806                       0.689   \n",
       "123  2001811305  1091121846136                       0.750   \n",
       "\n",
       "     Weight slab as per X (KG)  Total weight as per Courier Company (KG)  \\\n",
       "0                          1.5                                      1.30   \n",
       "1                          1.0                                      1.00   \n",
       "2                          2.5                                      2.50   \n",
       "3                          1.0                                      1.00   \n",
       "4                          0.5                                      0.15   \n",
       "..                         ...                                       ...   \n",
       "119                        0.5                                      0.73   \n",
       "120                        1.0                                      0.50   \n",
       "121                        1.0                                      0.50   \n",
       "122                        1.0                                      0.50   \n",
       "123                        1.0                                      0.50   \n",
       "\n",
       "     Weight slab charged by Courier Company (KG) Delivery Zone as per X  \\\n",
       "0                                            1.5                      d   \n",
       "1                                            1.0                      d   \n",
       "2                                            2.5                      d   \n",
       "3                                            1.0                      b   \n",
       "4                                            0.5                      d   \n",
       "..                                           ...                    ...   \n",
       "119                                          1.0                      b   \n",
       "120                                          0.5                      b   \n",
       "121                                          0.5                      b   \n",
       "122                                          0.5                      e   \n",
       "123                                          0.5                      b   \n",
       "\n",
       "    Delivery Zone charged by Courier Company  Expected Charge as per X (Rs.)  \\\n",
       "0                                          d                           135.0   \n",
       "1                                          d                            90.2   \n",
       "2                                          d                           224.6   \n",
       "3                                          b                            61.3   \n",
       "4                                          d                            45.4   \n",
       "..                                       ...                             ...   \n",
       "119                                        d                            33.0   \n",
       "120                                        d                           100.7   \n",
       "121                                        d                            61.3   \n",
       "122                                        b                           112.1   \n",
       "123                                        d                            61.3   \n",
       "\n",
       "     Charges Billed by Courier Company (Rs.)  \\\n",
       "0                                      135.0   \n",
       "1                                       90.2   \n",
       "2                                      224.6   \n",
       "3                                       61.3   \n",
       "4                                       45.4   \n",
       "..                                       ...   \n",
       "119                                     90.2   \n",
       "120                                     86.7   \n",
       "121                                     45.4   \n",
       "122                                     33.0   \n",
       "123                                     45.4   \n",
       "\n",
       "     Difference Between Expected Charges and Billed Charges (Rs.)  \n",
       "0                                         0.000000e+00             \n",
       "1                                        -1.421085e-14             \n",
       "2                                         0.000000e+00             \n",
       "3                                         0.000000e+00             \n",
       "4                                         0.000000e+00             \n",
       "..                                                 ...             \n",
       "119                                      -5.720000e+01             \n",
       "120                                       1.400000e+01             \n",
       "121                                       1.590000e+01             \n",
       "122                                       7.910000e+01             \n",
       "123                                       1.590000e+01             \n",
       "\n",
       "[124 rows x 11 columns]"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "id": "25d838ae",
   "metadata": {},
   "outputs": [],
   "source": [
    "data1.to_excel('DATA1.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2c378197",
   "metadata": {},
   "source": [
    "## Summary Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "id": "6501fc97",
   "metadata": {},
   "outputs": [],
   "source": [
    "Summary=pd.DataFrame({' ' : [\"Total orders where X has been correctly charged\", \"Total Orders where X has been overcharged\", \"Total Orders where X has been undercharged\"],'Count':'','Amount (Rs.)':''})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "id": "5ddea136",
   "metadata": {},
   "outputs": [],
   "source": [
    "Summary.index=Summary[' ']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "id": "c50b90da",
   "metadata": {},
   "outputs": [],
   "source": [
    "del Summary[\" \"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "id": "30248897",
   "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>Count</th>\n",
       "      <th>Amount (Rs.)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Total orders where X has been correctly charged</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Total Orders where X has been overcharged</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Total Orders where X has been undercharged</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                Count Amount (Rs.)\n",
       "                                                                  \n",
       "Total orders where X has been correctly charged                   \n",
       "Total Orders where X has been overcharged                         \n",
       "Total Orders where X has been undercharged                        "
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Summary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "id": "568c363f",
   "metadata": {},
   "outputs": [],
   "source": [
    "Summary.loc[:]['Count'][0]=data1[\"Difference Between Expected Charges and Billed Charges (Rs.)\"][data1[\"Difference Between Expected Charges and Billed Charges (Rs.)\"]==0].value_counts()[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "id": "acb07744",
   "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>Count</th>\n",
       "      <th>Amount (Rs.)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Total orders where X has been correctly charged</th>\n",
       "      <td>16</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Total Orders where X has been overcharged</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Total Orders where X has been undercharged</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                Count Amount (Rs.)\n",
       "                                                                  \n",
       "Total orders where X has been correctly charged    16             \n",
       "Total Orders where X has been overcharged                         \n",
       "Total Orders where X has been undercharged                        "
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Summary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "id": "d9c47938",
   "metadata": {},
   "outputs": [],
   "source": [
    "Summary.loc[:]['Amount (Rs.)'][0]=data1[\"Charges Billed by Courier Company (Rs.)\"][data1[\"Difference Between Expected Charges and Billed Charges (Rs.)\"]==0].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "id": "8fa4f607",
   "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>Count</th>\n",
       "      <th>Amount (Rs.)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Total orders where X has been correctly charged</th>\n",
       "      <td>16</td>\n",
       "      <td>1268.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Total Orders where X has been overcharged</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Total Orders where X has been undercharged</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                Count Amount (Rs.)\n",
       "                                                                  \n",
       "Total orders where X has been correctly charged    16       1268.6\n",
       "Total Orders where X has been overcharged                         \n",
       "Total Orders where X has been undercharged                        "
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Summary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "id": "4d0b678c",
   "metadata": {},
   "outputs": [],
   "source": [
    "Summary.loc[:]['Count'][1]=data1[\"Difference Between Expected Charges and Billed Charges (Rs.)\"][data1[\"Difference Between Expected Charges and Billed Charges (Rs.)\"]<0].value_counts().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "id": "4e0e826d",
   "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>Count</th>\n",
       "      <th>Amount (Rs.)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Total orders where X has been correctly charged</th>\n",
       "      <td>16</td>\n",
       "      <td>1268.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Total Orders where X has been overcharged</th>\n",
       "      <td>95</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Total Orders where X has been undercharged</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                Count Amount (Rs.)\n",
       "                                                                  \n",
       "Total orders where X has been correctly charged    16       1268.6\n",
       "Total Orders where X has been overcharged          95             \n",
       "Total Orders where X has been undercharged                        "
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Summary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "id": "f4b85c63",
   "metadata": {},
   "outputs": [],
   "source": [
    "Summary.loc[:]['Amount (Rs.)'][1]=abs(data1[\"Difference Between Expected Charges and Billed Charges (Rs.)\"][data1[\"Difference Between Expected Charges and Billed Charges (Rs.)\"]<0].sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "id": "50b65698",
   "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>Count</th>\n",
       "      <th>Amount (Rs.)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Total orders where X has been correctly charged</th>\n",
       "      <td>16</td>\n",
       "      <td>1268.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Total Orders where X has been overcharged</th>\n",
       "      <td>95</td>\n",
       "      <td>4957.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Total Orders where X has been undercharged</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                Count Amount (Rs.)\n",
       "                                                                  \n",
       "Total orders where X has been correctly charged    16       1268.6\n",
       "Total Orders where X has been overcharged          95       4957.2\n",
       "Total Orders where X has been undercharged                        "
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Summary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "id": "cc3724a8",
   "metadata": {},
   "outputs": [],
   "source": [
    "Summary.loc[:]['Count'][2]=data1[\"Difference Between Expected Charges and Billed Charges (Rs.)\"][data1[\"Difference Between Expected Charges and Billed Charges (Rs.)\"]>0].value_counts().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "id": "4d76464a",
   "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>Count</th>\n",
       "      <th>Amount (Rs.)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Total orders where X has been correctly charged</th>\n",
       "      <td>16</td>\n",
       "      <td>1268.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Total Orders where X has been overcharged</th>\n",
       "      <td>95</td>\n",
       "      <td>4957.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Total Orders where X has been undercharged</th>\n",
       "      <td>13</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                Count Amount (Rs.)\n",
       "                                                                  \n",
       "Total orders where X has been correctly charged    16       1268.6\n",
       "Total Orders where X has been overcharged          95       4957.2\n",
       "Total Orders where X has been undercharged         13             "
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Summary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "id": "c9291945",
   "metadata": {},
   "outputs": [],
   "source": [
    "Summary.loc[:]['Amount (Rs.)'][2]=abs(data1[\"Difference Between Expected Charges and Billed Charges (Rs.)\"][data1[\"Difference Between Expected Charges and Billed Charges (Rs.)\"]>0].sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "id": "d6d69277",
   "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>Count</th>\n",
       "      <th>Amount (Rs.)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Total orders where X has been correctly charged</th>\n",
       "      <td>16</td>\n",
       "      <td>1268.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Total Orders where X has been overcharged</th>\n",
       "      <td>95</td>\n",
       "      <td>4957.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Total Orders where X has been undercharged</th>\n",
       "      <td>13</td>\n",
       "      <td>495.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                Count Amount (Rs.)\n",
       "                                                                  \n",
       "Total orders where X has been correctly charged    16       1268.6\n",
       "Total Orders where X has been overcharged          95       4957.2\n",
       "Total Orders where X has been undercharged         13        495.5"
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Summary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "id": "1c5c7215",
   "metadata": {},
   "outputs": [],
   "source": [
    "Summary.to_excel('Summary.xlsx')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.10.4 64-bit",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.4"
  },
  "vscode": {
   "interpreter": {
    "hash": "b91482d921f5a84871d250fe0a1df3a1376a5fba0ca55bf48acb89e750990a19"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}