Rashid_Khi_Python_Assignment2-checkpoint.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df=pd.read_csv(\"D:/DIH/Assignment Material/Assignment 2/hospitaldata.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "for i in df.columns:\n",
    "    if \"..\" in i  :\n",
    "        df.rename(columns={i: i.replace(\"..\",\"\")},inplace=True)\n",
    "    elif \".\" in i:\n",
    "        df.rename(columns={i: i.replace(\".\",\"\")},inplace=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Date', 'id', 'Time', 'Age', 'Sex', 'ConsultingDoctor', 'Specialty',\n",
       "       'Procedure', 'TotalCharges', 'AmountReceived.', 'AmountBalance',\n",
       "       'AmountReceivedBy', 'AmountinHospital', 'ReceptionistName', 'NextApt'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Monday\n"
     ]
    }
   ],
   "source": [
    "days = []\n",
    "for i in df.Date:\n",
    "    days.append((str(i).split(',')[0])) \n",
    "days= pd.Series(days)\n",
    "  #pandas.Series.tolist(days)\n",
    "print(days.value_counts().idxmax())\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "32.57216494845361"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.Age = df.Age.replace(\"-\",np.nan)   \n",
    "df.Age = df.Age.replace(\"6M\",\"6\")   \n",
    "df.Age = df.Age.replace(\"28M\",\"28\")   \n",
    "#for i in df.Age:\n",
    "#    i = i.strip(\"M\")           \n",
    "df.Age = pd.to_numeric(df.Age)\n",
    "df.Age.mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "24"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "count_child = df[df.Age<=12]\n",
    "len (count_child)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Consultation\n",
      "Consultation\n"
     ]
    }
   ],
   "source": [
    "M= df[df.Sex==\"M\"]\n",
    "print (M['Procedure'].value_counts().idxmax()) \n",
    " \n",
    "F =df[(df.Sex==\"F\") |(df.Sex==\"f\")]\n",
    "print (F['Procedure'].value_counts().idxmax())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TotalCharges</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ConsultingDoctor</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Brig Asif</th>\n",
       "      <td>2800.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Brig Farrukh</th>\n",
       "      <td>3750.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Col Ulfat Ellahi</th>\n",
       "      <td>1000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dr Alaf Khan</th>\n",
       "      <td>513050.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dr Ali</th>\n",
       "      <td>26100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dr Ammad</th>\n",
       "      <td>1400.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dr Ammara</th>\n",
       "      <td>1500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dr Fakiha</th>\n",
       "      <td>22600.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dr Irfan</th>\n",
       "      <td>11000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dr Kinza Alam</th>\n",
       "      <td>76700.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dr Mehwish</th>\n",
       "      <td>1000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dr Mumtaz</th>\n",
       "      <td>1000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dr Paul</th>\n",
       "      <td>1500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dr Qurat ul Ain</th>\n",
       "      <td>20900.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dr Riffat Naheed</th>\n",
       "      <td>18800.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dr Saad</th>\n",
       "      <td>52000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dr Saad Riaz</th>\n",
       "      <td>5700.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dr Saima Shams</th>\n",
       "      <td>1500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dr Saniya</th>\n",
       "      <td>4000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dr Shireen</th>\n",
       "      <td>3200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dr Waqar Azeem</th>\n",
       "      <td>6000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dr Zubair</th>\n",
       "      <td>1700.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Nursing Staff</th>\n",
       "      <td>9150.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  TotalCharges\n",
       "ConsultingDoctor              \n",
       "Brig Asif               2800.0\n",
       "Brig Farrukh            3750.0\n",
       "Col Ulfat Ellahi        1000.0\n",
       "Dr Alaf Khan          513050.0\n",
       "Dr Ali                 26100.0\n",
       "Dr Ammad                1400.0\n",
       "Dr Ammara               1500.0\n",
       "Dr Fakiha              22600.0\n",
       "Dr Irfan               11000.0\n",
       "Dr Kinza Alam          76700.0\n",
       "Dr Mehwish              1000.0\n",
       "Dr Mumtaz               1000.0\n",
       "Dr Paul                 1500.0\n",
       "Dr Qurat ul Ain        20900.0\n",
       "Dr Riffat Naheed       18800.0\n",
       "Dr Saad                52000.0\n",
       "Dr Saad Riaz            5700.0\n",
       "Dr Saima Shams          1500.0\n",
       "Dr Saniya               4000.0\n",
       "Dr Shireen              3200.0\n",
       "Dr Waqar Azeem          6000.0\n",
       "Dr Zubair               1700.0\n",
       "Nursing Staff           9150.0"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Q6\n",
    "df['TotalCharges']=pd.to_numeric(df['TotalCharges'],errors='coerce') \n",
    "grouped_dr = df[['ConsultingDoctor', 'TotalCharges']].groupby(['ConsultingDoctor']).sum()\n",
    "grouped_dr\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TotalCharges</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Procedure</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>22 Unit Bridge</th>\n",
       "      <td>69500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4 Unit Bridge</th>\n",
       "      <td>11000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8 Unit Bridge+2 R.C.T</th>\n",
       "      <td>30000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BSR</th>\n",
       "      <td>50.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C Section</th>\n",
       "      <td>65000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cancelled</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Consultation</th>\n",
       "      <td>83950.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Consultation +   X Ray</th>\n",
       "      <td>3000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Consultation + Dressing</th>\n",
       "      <td>5000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Consultation + Nebulize</th>\n",
       "      <td>1150.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Consultation + USG</th>\n",
       "      <td>1500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Consultation+Denture</th>\n",
       "      <td>1500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Consultation+ECG</th>\n",
       "      <td>1300.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Consultation+ER Retain</th>\n",
       "      <td>2800.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Consultation+Retain</th>\n",
       "      <td>3500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Consultation+USG</th>\n",
       "      <td>1500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Consultation+X Ray</th>\n",
       "      <td>4750.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Consultation+x Ray</th>\n",
       "      <td>650.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Coupety</th>\n",
       "      <td>300.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Crown</th>\n",
       "      <td>20000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Denture+Scalling+Filling</th>\n",
       "      <td>5500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dressing</th>\n",
       "      <td>2900.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Er Retain</th>\n",
       "      <td>300.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Extraction</th>\n",
       "      <td>14600.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Filling</th>\n",
       "      <td>6500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Filling + X Rays</th>\n",
       "      <td>2600.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Injection</th>\n",
       "      <td>1800.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Laboratory Test</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Medicine</th>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Operation</th>\n",
       "      <td>50000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Orthodontics</th>\n",
       "      <td>240000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pharmacy</th>\n",
       "      <td>900.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Polishing</th>\n",
       "      <td>500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pop</th>\n",
       "      <td>3700.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R.C.T</th>\n",
       "      <td>15500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R.C.T+Crown</th>\n",
       "      <td>8000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R.C.T+Scalling+Crown</th>\n",
       "      <td>9500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R.P.D + Crown</th>\n",
       "      <td>11000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RCT</th>\n",
       "      <td>3000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RCT (4 teeth) Bridge (9 teeth)</th>\n",
       "      <td>48000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Scalling</th>\n",
       "      <td>16500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Scalling+Polishing</th>\n",
       "      <td>4000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Stiches</th>\n",
       "      <td>700.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USG</th>\n",
       "      <td>5500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USG Abdomen</th>\n",
       "      <td>1000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USG KUB</th>\n",
       "      <td>1000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>X Ray</th>\n",
       "      <td>5800.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                TotalCharges\n",
       "Procedure                                   \n",
       "22 Unit Bridge                       69500.0\n",
       "4 Unit Bridge                        11000.0\n",
       "8 Unit Bridge+2 R.C.T                30000.0\n",
       "BSR                                     50.0\n",
       "C Section                            65000.0\n",
       "Cancelled                                NaN\n",
       "Consultation                         83950.0\n",
       "Consultation +   X Ray                3000.0\n",
       "Consultation + Dressing               5000.0\n",
       "Consultation + Nebulize               1150.0\n",
       "Consultation + USG                    1500.0\n",
       "Consultation+Denture                  1500.0\n",
       "Consultation+ECG                      1300.0\n",
       "Consultation+ER Retain                2800.0\n",
       "Consultation+Retain                   3500.0\n",
       "Consultation+USG                      1500.0\n",
       "Consultation+X Ray                    4750.0\n",
       "Consultation+x Ray                     650.0\n",
       "Coupety                                300.0\n",
       "Crown                                20000.0\n",
       "Denture+Scalling+Filling              5500.0\n",
       "Dressing                              2900.0\n",
       "Er Retain                              300.0\n",
       "Extraction                           14600.0\n",
       "Filling                               6500.0\n",
       "Filling + X Rays                      2600.0\n",
       "Injection                             1800.0\n",
       "Laboratory Test                          NaN\n",
       "Medicine                               100.0\n",
       "Operation                            50000.0\n",
       "Orthodontics                        240000.0\n",
       "Pharmacy                               900.0\n",
       "Polishing                              500.0\n",
       "Pop                                   3700.0\n",
       "R.C.T                                15500.0\n",
       "R.C.T+Crown                           8000.0\n",
       "R.C.T+Scalling+Crown                  9500.0\n",
       "R.P.D + Crown                        11000.0\n",
       "RCT                                   3000.0\n",
       "RCT (4 teeth) Bridge (9 teeth)       48000.0\n",
       "Scalling                             16500.0\n",
       "Scalling+Polishing                    4000.0\n",
       "Stiches                                700.0\n",
       "USG                                   5500.0\n",
       "USG Abdomen                           1000.0\n",
       "USG KUB                               1000.0\n",
       "X Ray                                 5800.0"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "proc = df[['Procedure','TotalCharges']].groupby(['Procedure']).sum()\n",
    "proc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "13.0"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Q8\n",
    "df.Time=pd.to_datetime(df.Time,errors='coerce')\n",
    "df.Time.dt.hour.value_counts().idxmax()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0         Morning\n",
       "1         Morning\n",
       "2         Morning\n",
       "3      After Noon\n",
       "4      After Noon\n",
       "5      After Noon\n",
       "6      After Noon\n",
       "7      After Noon\n",
       "8      After Noon\n",
       "9         Evening\n",
       "10        Evening\n",
       "11        Evening\n",
       "12     After Noon\n",
       "13     After Noon\n",
       "14        Evening\n",
       "15            NaN\n",
       "16        Morning\n",
       "17            NaN\n",
       "18            NaN\n",
       "19        Morning\n",
       "20     After Noon\n",
       "21     After Noon\n",
       "22        Morning\n",
       "23     After Noon\n",
       "24     After Noon\n",
       "25            NaN\n",
       "26            NaN\n",
       "27            NaN\n",
       "28        Morning\n",
       "29     After Noon\n",
       "          ...    \n",
       "192       Morning\n",
       "193       Morning\n",
       "194    After Noon\n",
       "195    After Noon\n",
       "196       Evening\n",
       "197    After Noon\n",
       "198           NaN\n",
       "199    After Noon\n",
       "200       Evening\n",
       "201       Evening\n",
       "202           NaN\n",
       "203    After Noon\n",
       "204           NaN\n",
       "205       Evening\n",
       "206    After Noon\n",
       "207           NaN\n",
       "208           NaN\n",
       "209           NaN\n",
       "210    After Noon\n",
       "211       Evening\n",
       "212       Morning\n",
       "213       Evening\n",
       "214       Morning\n",
       "215       Morning\n",
       "216           NaN\n",
       "217           NaN\n",
       "218    After Noon\n",
       "219       Evening\n",
       "220       Morning\n",
       "221           NaN\n",
       "Name: Time, dtype: object"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "#Q9\n",
    " \n",
    "def TimeBracket (hour):\n",
    "    if hour >= 6.0 and hour <= 12 :\n",
    "        return \"Morning\"\n",
    "    elif hour > 12 and hour <= 16  :\n",
    "        return \"After Noon\"\n",
    "    elif hour > 16 and hour <= 19  :\n",
    "        return \"Evening\"\n",
    "    elif hour > 19 and hour < 6  :\n",
    "        return \"Night\"\n",
    "    else :\n",
    "        return np.NAN\n",
    "         \n",
    "df.Time.dt.hour.map(TimeBracket)\n",
    "  \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "37\n"
     ]
    }
   ],
   "source": [
    "#Q10\n",
    "\n",
    "pt_vst =df['id'].value_counts()\n",
    "print(len(pt_vst[pt_vst>1].index))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1      12\n",
      "46      5\n",
      "122     5\n",
      "140     4\n",
      "94      4\n",
      "17      4\n",
      "45      3\n",
      "101     3\n",
      "63      3\n",
      "132     3\n",
      "114     3\n",
      "109     3\n",
      "107     3\n",
      "145     3\n",
      "20      2\n",
      "97      2\n",
      "59      2\n",
      "88      2\n",
      "96      2\n",
      "112     2\n",
      "116     2\n",
      "118     2\n",
      "25      2\n",
      "80      2\n",
      "120     2\n",
      "40      2\n",
      "100     2\n",
      "64      2\n",
      "4       2\n",
      "133     2\n",
      "       ..\n",
      "68      1\n",
      "69      1\n",
      "70      1\n",
      "71      1\n",
      "72      1\n",
      "73      1\n",
      "74      1\n",
      "75      1\n",
      "76      1\n",
      "77      1\n",
      "154     1\n",
      "79      1\n",
      "81      1\n",
      "82      1\n",
      "83      1\n",
      "84      1\n",
      "85      1\n",
      "86      1\n",
      "87      1\n",
      "89      1\n",
      "90      1\n",
      "91      1\n",
      "92      1\n",
      "93      1\n",
      "95      1\n",
      "98      1\n",
      "99      1\n",
      "102     1\n",
      "103     1\n",
      "78      1\n",
      "Name: id, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "#Q11\n",
    "print (pt_vst) \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "id   Procedure                     \n",
      "1    Pharmacy                          10\n",
      "12   22 Unit Bridge                     2\n",
      "13   Consultation                       2\n",
      "17   Consultation                       2\n",
      "     RCT (4 teeth) Bridge (9 teeth)     2\n",
      "20   Consultation                       2\n",
      "25   Consultation                       2\n",
      "45   R.P.D + Crown                      2\n",
      "46   Dressing                           4\n",
      "63   Consultation                       2\n",
      "80   Consultation                       2\n",
      "94   Injection                          3\n",
      "97   Consultation                       2\n",
      "101  C Section                          2\n",
      "109  R.C.T                              2\n",
      "112  Operation                          2\n",
      "114  Consultation                       3\n",
      "116  X Ray                              2\n",
      "122  Injection                          5\n",
      "130  Consultation                       2\n",
      "140  4 Unit Bridge                      2\n",
      "145  Crown                              3\n",
      "151  R.C.T+Scalling+Crown               2\n",
      "153  Orthodontics                       2\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "#Q12\n",
    "x=df[['id','Procedure']]\n",
    "patient_visits=x.groupby(['id','Procedure']).size()\n",
    "print(patient_visits[patient_visits>1])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "29.0"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Q13\n",
    "F.Age.median()\n",
    "M.Age.median()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "222500.0\n"
     ]
    }
   ],
   "source": [
    "#Q14\n",
    "df.replace(to_replace=df['AmountBalance'].unique()[0], value=np.nan, inplace=True)\n",
    "df['AmountBalance']=df['AmountBalance'].map(lambda x: str(x).replace(',',''))\n",
    "df['AmountBalance'] = df['AmountBalance'].astype(float)\n",
    "print(df['AmountBalance'].sum())\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "83950.0"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Q15\n",
    "\n",
    "Consultation=df[df.Procedure=='Consultation']\n",
    "Consultation['TotalCharges'].sum()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TotalCharges</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Age</th>\n",
       "      <td>0.029521</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     TotalCharges\n",
       "Age      0.029521"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Q16\n",
    "Corr=df.corr()\n",
    "Corr.loc[['Age'],['TotalCharges']]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "16500.0"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Q18\n",
    "df[(df.Procedure== 'X Rays') | (df.Procedure == 'Scalling')]['TotalCharges'].sum()\n",
    "\n",
    " "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "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.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}