Merging_Sheets.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "xls=pd.ExcelFile('Paavan Dairy-automated-invoice.xlsx')\n",
    "client_details=pd.read_excel(xls,'Clients')\n",
    "product_details=pd.read_excel(xls,'Products')\n",
    "Bill=pd.read_excel(xls,'Sheet1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Client nr</th>\n",
       "      <th>Name</th>\n",
       "      <th>Street</th>\n",
       "      <th>City</th>\n",
       "      <th>Phone Num</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>501</td>\n",
       "      <td>Pratik Bansal</td>\n",
       "      <td>Bansal Bhawan, G.T. Road, Kumarpur</td>\n",
       "      <td>Asansol</td>\n",
       "      <td>9647616532</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>502</td>\n",
       "      <td>Simarjit Gandhi</td>\n",
       "      <td>Lucas Estate, Durgamandir</td>\n",
       "      <td>Asansol</td>\n",
       "      <td>9570109807</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>503</td>\n",
       "      <td>G B Gupta</td>\n",
       "      <td>Sreepally</td>\n",
       "      <td>Asansol</td>\n",
       "      <td>9999999999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>504</td>\n",
       "      <td>Munna Sharma</td>\n",
       "      <td>Murgasol</td>\n",
       "      <td>Asansol</td>\n",
       "      <td>1234567890</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>505</td>\n",
       "      <td>Hotel Signature</td>\n",
       "      <td>G.T.Road,Near Ushagram Bus Stand</td>\n",
       "      <td>Asansol</td>\n",
       "      <td>8373051336</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Client nr             Name                              Street     City  \\\n",
       "0        501    Pratik Bansal  Bansal Bhawan, G.T. Road, Kumarpur  Asansol   \n",
       "1        502  Simarjit Gandhi           Lucas Estate, Durgamandir  Asansol   \n",
       "2        503        G B Gupta                           Sreepally  Asansol   \n",
       "3        504     Munna Sharma                            Murgasol  Asansol   \n",
       "4        505  Hotel Signature    G.T.Road,Near Ushagram Bus Stand  Asansol   \n",
       "\n",
       "    Phone Num  \n",
       "0  9647616532  \n",
       "1  9570109807  \n",
       "2  9999999999  \n",
       "3  1234567890  \n",
       "4  8373051336  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "client_details"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Product nr</th>\n",
       "      <th>Description</th>\n",
       "      <th>Price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>Milk</td>\n",
       "      <td>38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>Bottle</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>Dahi</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1004</td>\n",
       "      <td>Paneer</td>\n",
       "      <td>260</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1005</td>\n",
       "      <td>Mawa</td>\n",
       "      <td>300</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Product nr Description  Price\n",
       "0        1001        Milk     38\n",
       "1        1002      Bottle     50\n",
       "2        1003        Dahi     20\n",
       "3        1004      Paneer    260\n",
       "4        1005        Mawa    300"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "product_details"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Date</th>\n",
       "      <th>Month</th>\n",
       "      <th>Client Number</th>\n",
       "      <th>Client Name</th>\n",
       "      <th>Product</th>\n",
       "      <th>Quantity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>17</td>\n",
       "      <td>June</td>\n",
       "      <td>501</td>\n",
       "      <td>Pratik Bansal</td>\n",
       "      <td>1002</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>17</td>\n",
       "      <td>May</td>\n",
       "      <td>501</td>\n",
       "      <td>Pratik Bansal</td>\n",
       "      <td>1001</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>15</td>\n",
       "      <td>June</td>\n",
       "      <td>502</td>\n",
       "      <td>Simarjit Gandhi</td>\n",
       "      <td>1003</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>14</td>\n",
       "      <td>June</td>\n",
       "      <td>503</td>\n",
       "      <td>G B Gupta</td>\n",
       "      <td>1001</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12</td>\n",
       "      <td>June</td>\n",
       "      <td>501</td>\n",
       "      <td>Pratik Bansal</td>\n",
       "      <td>1003</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>12</td>\n",
       "      <td>June</td>\n",
       "      <td>502</td>\n",
       "      <td>Simarjit Gandhi</td>\n",
       "      <td>1003</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>19</td>\n",
       "      <td>June</td>\n",
       "      <td>502</td>\n",
       "      <td>Simarjit Gandhi</td>\n",
       "      <td>1004</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Date Month  Client Number      Client Name  Product  Quantity\n",
       "0    17  June            501    Pratik Bansal     1002         0\n",
       "1    17   May            501    Pratik Bansal     1001         0\n",
       "2    15  June            502  Simarjit Gandhi     1003         2\n",
       "3    14  June            503        G B Gupta     1001         0\n",
       "4    12  June            501    Pratik Bansal     1003         1\n",
       "5    12  June            502  Simarjit Gandhi     1003         2\n",
       "6    19  June            502  Simarjit Gandhi     1004         5"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Bill"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# final_bill={}\n",
    "\n",
    "# for index,row in Bill.iterrows():\n",
    "#     client_id=row['Client Number']\n",
    "#     if client_id=='nan':\n",
    "#         break\n",
    "#     product_id=row['Product']\n",
    "#     quantity=row['Quantity']\n",
    "#     name=(str(client_details.loc[client_details['Client nr']==row['Client Number'],'Name'])[1:]).strip()\n",
    "#     name=(name.split('\\n'))[0]\n",
    "#     street=(str(client_details.loc[client_details['Client nr']==row['Client Number'],'Street'])[1:]).strip()\n",
    "#     street=(street.split('\\n'))[0]\n",
    "#     city=(str(client_details.loc[client_details['Client nr']==row['Client Number'],'City'])[1:]).strip()\n",
    "#     city=(city.split('\\n'))[0]\n",
    "#     Phone_Number=(str(client_details.loc[client_details['Client nr']==row['Client Number'],'Phone Num'])[1:]).strip()\n",
    "#     Phone_Number=(Phone_Number.split('\\n'))[0]\n",
    "#     Product=(str(product_details.loc[product_details['Product nr']==row['Product'],'Description'])[1:]).strip()\n",
    "#     Product=(Product.split('\\n'))[0]\n",
    "#     Price=(str(product_details.loc[product_details['Product nr']==row['Product'],'Price'])[1:]).strip()\n",
    "#     Price=(Price.split('\\n'))[0]\n",
    "#     if row['Client Number'] not in final_bill.keys():\n",
    "#         prod={}\n",
    "#         prod[0]=({'product_id':product_id,'Product':Product,'Price':Price,'Quantity':quantity})\n",
    "#         final_bill[row['Client Number']]={'Client_Name':name,'Street':street,'City':city,'Phone_Number':Phone_Number,'prod':prod}\n",
    "#     else:\n",
    "#         prod={}\n",
    "#         prod=final_bill[row['Client Number']]['prod']\n",
    "#         product_number=prod.keys()\n",
    "#         count=len(product_number)\n",
    "#         prod[count]=({'product_id':product_id,'Product':Product,'Price':Price,'Quantity':quantity})\n",
    "#         final_bill[row['Client Number']]={'Client_Name':name,'Street':street,'City':city,'Phone_Number':Phone_Number,'prod':prod}\n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "final_bill={}\n",
    "flag=0\n",
    "for index,row in Bill.iterrows():\n",
    "    client_id=row['Client Number']\n",
    "    if client_id=='nan':\n",
    "        break\n",
    "    product_id=row['Product']\n",
    "    quantity=row['Quantity']\n",
    "    name=(str(client_details.loc[client_details['Client nr']==row['Client Number'],'Name'])[1:]).strip()\n",
    "    name=(name.split('\\n'))[0]\n",
    "    street=(str(client_details.loc[client_details['Client nr']==row['Client Number'],'Street'])[1:]).strip()\n",
    "    street=(street.split('\\n'))[0]\n",
    "    city=(str(client_details.loc[client_details['Client nr']==row['Client Number'],'City'])[1:]).strip()\n",
    "    city=(city.split('\\n'))[0]\n",
    "    Phone_Number=(str(client_details.loc[client_details['Client nr']==row['Client Number'],'Phone Num'])[1:]).strip()\n",
    "    Phone_Number=(Phone_Number.split('\\n'))[0]\n",
    "    Product=(str(product_details.loc[product_details['Product nr']==row['Product'],'Description'])[1:]).strip()\n",
    "    Product=(Product.split('\\n'))[0]\n",
    "    Price=(str(product_details.loc[product_details['Product nr']==row['Product'],'Price'])[1:]).strip()\n",
    "    Price=(Price.split('\\n'))[0]\n",
    "    if row['Client Number'] not in final_bill.keys():\n",
    "        prod={}\n",
    "        prod[0]=({'product_id':product_id,'Product':Product,'Price':Price,'Quantity':quantity})\n",
    "        final_bill[row['Client Number']]={'Client_Name':name,'Street':street,'City':city,'Phone_Number':Phone_Number,'prod':prod}\n",
    "    else:\n",
    "        prod={}\n",
    "        prod=final_bill[row['Client Number']]['prod']\n",
    "        product_number=prod.keys()\n",
    "        count=len(product_number)\n",
    "        for j in range(count):\n",
    "            if prod[j]['product_id']==product_id:\n",
    "                final_bill[row['Client Number']]['prod'][j]['Quantity']+=quantity\n",
    "                flag=1\n",
    "                break\n",
    "        #print(name+str(\"\")+str(flag))\n",
    "        if flag==1:\n",
    "            flag=0\n",
    "        else:\n",
    "            prod[count]=({'product_id':product_id,'Product':Product,'Price':Price,'Quantity':quantity})\n",
    "            final_bill[row['Client Number']]={'Client_Name':name,'Street':street,'City':city,'Phone_Number':Phone_Number,'prod':prod}\n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df=(pd.DataFrame(final_bill))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>501</th>\n",
       "      <th>502</th>\n",
       "      <th>503</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>City</th>\n",
       "      <td>Asansol</td>\n",
       "      <td>Asansol</td>\n",
       "      <td>Asansol</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Client_Name</th>\n",
       "      <td>Pratik Bansal</td>\n",
       "      <td>Simarjit Gandhi</td>\n",
       "      <td>G B Gupta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Phone_Number</th>\n",
       "      <td>9647616532</td>\n",
       "      <td>9570109807</td>\n",
       "      <td>9999999999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Street</th>\n",
       "      <td>Bansal Bhawan, G.T. Road, Kumarpur</td>\n",
       "      <td>Lucas Estate, Durgamandir</td>\n",
       "      <td>Sreepally</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>prod</th>\n",
       "      <td>{0: {'product_id': 1002, 'Product': 'Bottle', ...</td>\n",
       "      <td>{0: {'product_id': 1003, 'Product': 'Dahi', 'P...</td>\n",
       "      <td>{0: {'product_id': 1001, 'Product': 'Milk', 'P...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                            501  \\\n",
       "City                                                    Asansol   \n",
       "Client_Name                                       Pratik Bansal   \n",
       "Phone_Number                                         9647616532   \n",
       "Street                       Bansal Bhawan, G.T. Road, Kumarpur   \n",
       "prod          {0: {'product_id': 1002, 'Product': 'Bottle', ...   \n",
       "\n",
       "                                                            502  \\\n",
       "City                                                    Asansol   \n",
       "Client_Name                                     Simarjit Gandhi   \n",
       "Phone_Number                                         9570109807   \n",
       "Street                                Lucas Estate, Durgamandir   \n",
       "prod          {0: {'product_id': 1003, 'Product': 'Dahi', 'P...   \n",
       "\n",
       "                                                            503  \n",
       "City                                                    Asansol  \n",
       "Client_Name                                           G B Gupta  \n",
       "Phone_Number                                         9999999999  \n",
       "Street                                                Sreepally  \n",
       "prod          {0: {'product_id': 1001, 'Product': 'Milk', 'P...  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df=df.transpose()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>City</th>\n",
       "      <th>Client_Name</th>\n",
       "      <th>Phone_Number</th>\n",
       "      <th>Street</th>\n",
       "      <th>prod</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>501</th>\n",
       "      <td>Asansol</td>\n",
       "      <td>Pratik Bansal</td>\n",
       "      <td>9647616532</td>\n",
       "      <td>Bansal Bhawan, G.T. Road, Kumarpur</td>\n",
       "      <td>{0: {'product_id': 1002, 'Product': 'Bottle', ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>502</th>\n",
       "      <td>Asansol</td>\n",
       "      <td>Simarjit Gandhi</td>\n",
       "      <td>9570109807</td>\n",
       "      <td>Lucas Estate, Durgamandir</td>\n",
       "      <td>{0: {'product_id': 1003, 'Product': 'Dahi', 'P...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>503</th>\n",
       "      <td>Asansol</td>\n",
       "      <td>G B Gupta</td>\n",
       "      <td>9999999999</td>\n",
       "      <td>Sreepally</td>\n",
       "      <td>{0: {'product_id': 1001, 'Product': 'Milk', 'P...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        City      Client_Name Phone_Number  \\\n",
       "501  Asansol    Pratik Bansal   9647616532   \n",
       "502  Asansol  Simarjit Gandhi   9570109807   \n",
       "503  Asansol        G B Gupta   9999999999   \n",
       "\n",
       "                                 Street  \\\n",
       "501  Bansal Bhawan, G.T. Road, Kumarpur   \n",
       "502           Lucas Estate, Durgamandir   \n",
       "503                           Sreepally   \n",
       "\n",
       "                                                  prod  \n",
       "501  {0: {'product_id': 1002, 'Product': 'Bottle', ...  \n",
       "502  {0: {'product_id': 1003, 'Product': 'Dahi', 'P...  \n",
       "503  {0: {'product_id': 1001, 'Product': 'Milk', 'P...  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "for index,j in df.iterrows():\n",
    "    j['prod']=(pd.DataFrame(j['prod']))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>City</th>\n",
       "      <th>Client_Name</th>\n",
       "      <th>Phone_Number</th>\n",
       "      <th>Street</th>\n",
       "      <th>prod</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>501</th>\n",
       "      <td>Asansol</td>\n",
       "      <td>Pratik Bansal</td>\n",
       "      <td>9647616532</td>\n",
       "      <td>Bansal Bhawan, G.T. Road, Kumarpur</td>\n",
       "      <td>0     1     2\n",
       "Price          ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>502</th>\n",
       "      <td>Asansol</td>\n",
       "      <td>Simarjit Gandhi</td>\n",
       "      <td>9570109807</td>\n",
       "      <td>Lucas Estate, Durgamandir</td>\n",
       "      <td>0       1\n",
       "Price         20     ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>503</th>\n",
       "      <td>Asansol</td>\n",
       "      <td>G B Gupta</td>\n",
       "      <td>9999999999</td>\n",
       "      <td>Sreepally</td>\n",
       "      <td>0\n",
       "Price         38\n",
       "Product     ...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        City      Client_Name Phone_Number  \\\n",
       "501  Asansol    Pratik Bansal   9647616532   \n",
       "502  Asansol  Simarjit Gandhi   9570109807   \n",
       "503  Asansol        G B Gupta   9999999999   \n",
       "\n",
       "                                 Street  \\\n",
       "501  Bansal Bhawan, G.T. Road, Kumarpur   \n",
       "502           Lucas Estate, Durgamandir   \n",
       "503                           Sreepally   \n",
       "\n",
       "                                                  prod  \n",
       "501                   0     1     2\n",
       "Price          ...  \n",
       "502                 0       1\n",
       "Price         20     ...  \n",
       "503                 0\n",
       "Price         38\n",
       "Product     ...  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df=df.transpose()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>501</th>\n",
       "      <th>502</th>\n",
       "      <th>503</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>City</th>\n",
       "      <td>Asansol</td>\n",
       "      <td>Asansol</td>\n",
       "      <td>Asansol</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Client_Name</th>\n",
       "      <td>Pratik Bansal</td>\n",
       "      <td>Simarjit Gandhi</td>\n",
       "      <td>G B Gupta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Phone_Number</th>\n",
       "      <td>9647616532</td>\n",
       "      <td>9570109807</td>\n",
       "      <td>9999999999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Street</th>\n",
       "      <td>Bansal Bhawan, G.T. Road, Kumarpur</td>\n",
       "      <td>Lucas Estate, Durgamandir</td>\n",
       "      <td>Sreepally</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>prod</th>\n",
       "      <td>0     1     2\n",
       "Price          ...</td>\n",
       "      <td>0       1\n",
       "Price         20     ...</td>\n",
       "      <td>0\n",
       "Price         38\n",
       "Product     ...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                            501  \\\n",
       "City                                                    Asansol   \n",
       "Client_Name                                       Pratik Bansal   \n",
       "Phone_Number                                         9647616532   \n",
       "Street                       Bansal Bhawan, G.T. Road, Kumarpur   \n",
       "prod                           0     1     2\n",
       "Price          ...   \n",
       "\n",
       "                                                            502  \\\n",
       "City                                                    Asansol   \n",
       "Client_Name                                     Simarjit Gandhi   \n",
       "Phone_Number                                         9570109807   \n",
       "Street                                Lucas Estate, Durgamandir   \n",
       "prod                         0       1\n",
       "Price         20     ...   \n",
       "\n",
       "                                                            503  \n",
       "City                                                    Asansol  \n",
       "Client_Name                                           G B Gupta  \n",
       "Phone_Number                                         9999999999  \n",
       "Street                                                Sreepally  \n",
       "prod                         0\n",
       "Price         38\n",
       "Product     ...  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Price</th>\n",
       "      <td>50</td>\n",
       "      <td>38</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Product</th>\n",
       "      <td>Bottle</td>\n",
       "      <td>Milk</td>\n",
       "      <td>Dahi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Quantity</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>product_id</th>\n",
       "      <td>1002</td>\n",
       "      <td>1001</td>\n",
       "      <td>1003</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 0     1     2\n",
       "Price           50    38    20\n",
       "Product     Bottle  Milk  Dahi\n",
       "Quantity         0     0     1\n",
       "product_id    1002  1001  1003"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[501]['prod']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Price             50\n",
       "Product       Bottle\n",
       "Quantity           0\n",
       "product_id      1002\n",
       "Name: 0, dtype: object"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[501]['prod'][0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'50'"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[501]['prod'][0]['Price']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df.to_csv('Merged_Sheet.csv')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}