HealthData 9.28.2015 CS,UB,KH,KK.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#Graph with summary statistics - I'm assuming this is just for Average Covered Charges\n",
    "#What is the most expensive state for healthcare and what is the least expensive?\n",
    "#Geographic plot by state of healthcare billing using Average Covered Costs.\n",
    "#Sum and group by state\n",
    "#Sum and group by DRG\n",
    "#Use bokeh instead of matplotlib\n",
    "\n",
    "#Prepare data for Summary Statistics - from last exercise\n",
    "__author__ = 'Chris Sneed'\n",
    "# Import section\n",
    "import os\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib as mpl\n",
    "from bokeh.sampledata import us_states\n",
    "from bokeh.plotting import figure, show, output_file\n",
    "\n",
    "# Read in the health data\n",
    "govdata = pd.read_csv ('C:\\Users\\W540\\Jupyter\\datagov.csv')\n",
    "\n",
    "# Assign each column to a new data column without the $\n",
    "acc = govdata[' Average Covered Charges '].str.lstrip('$')\n",
    "atp = govdata[' Average Total Payments '].str.lstrip('$')\n",
    "amp = govdata['Average Medicare Payments'].str.lstrip('$')\n",
    "\n",
    "# Change the data type from oject to float so that it is numeric\n",
    "# Assign to another new variable so the new data type \"sticks\"\n",
    "accnum = acc.astype(float)\n",
    "atpnum = atp.astype(float)\n",
    "ampnum = amp.astype(float)\n",
    "\n",
    "#Mean, Mode, Std. Dev for last 3 columns\n",
    "accmean = accnum.mean()\n",
    "accmode = accnum.mode()\n",
    "accstd = accnum.std()\n",
    "\n",
    "atpmean = atpnum.mean()\n",
    "atpmode = atpnum.mode()\n",
    "atpstd = atpnum.std()\n",
    "\n",
    "ampmean = ampnum.mean()\n",
    "ampmode = ampnum.mode()\n",
    "ampstd = ampnum.std()\n",
    "\n",
    "#update govdata table\n",
    "govdata.loc[:,'Avg Covered Charges - Float'] = accnum\n",
    "govdata.loc[:,'Avg Total Payments - Float'] = atpnum\n",
    "govdata.loc[:,'Avg Medicare Payments - Float'] = ampnum\n",
    "\n",
    "#Display results\n",
    "print \"Mean of Average Covered Charges\" \" = \" \"%.2f\" % accmean\n",
    "print \"Mode of Average Covered Charges\" \" = \" \"%.2f\" % accmode\n",
    "print \"STD of Average Covered Charges\" \" = \" \"%.2f\" % accstd\n",
    "\n",
    "print \"Mean of Average Total Payments\" \" = \" \"%.2f\" % atpmean\n",
    "print \"Mode of Average Total Payments\" \" = \" \"%.2f\" % atpmode\n",
    "print \"STD of Average Total Payments\" \" = \" \"%.2f\" % atpstd\n",
    "\n",
    "print \"Mean of Average Medical Payments\" \" = \" \"%.2f\" % ampmean\n",
    "print \"Mode of Average Medical Payments\"\n",
    "print ampmode\n",
    "print \"STD of Average Medical Payments\" \" = \" \"%.2f\" % ampstd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#save new columns back to original csv\n",
    "govdata.to_csv('C:\\Users\\W540\\Jupyter\\datagov.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#Convert Average Covered Charges ('Avg Covered Charges - Float') from float to integer\n",
    "govdata['Avg Covered Charges - Int'] = govdata['Avg Covered Charges - Float'].astype('int')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#Append new column to end of data set and write it back to original csv\n",
    "govdata.loc[:,'Avg Covered Charges - Int'] = govdata['Avg Covered Charges - Int']\n",
    "govdata.to_csv('C:\\Users\\W540\\Jupyter\\datagov.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "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>Mean</th>\n",
       "      <th>Std Dev.</th>\n",
       "      <th>State Sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Provider State</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>CA</th>\n",
       "      <td>67508.141457</td>\n",
       "      <td>58784.488889</td>\n",
       "      <td>881926360</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FL</th>\n",
       "      <td>46015.760197</td>\n",
       "      <td>36107.466017</td>\n",
       "      <td>513305805</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TX</th>\n",
       "      <td>41479.719319</td>\n",
       "      <td>33915.800108</td>\n",
       "      <td>492115390</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NJ</th>\n",
       "      <td>66125.205968</td>\n",
       "      <td>47242.551727</td>\n",
       "      <td>319120244</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PA</th>\n",
       "      <td>39633.485905</td>\n",
       "      <td>41910.130294</td>\n",
       "      <td>309299724</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NY</th>\n",
       "      <td>31435.212465</td>\n",
       "      <td>28205.859017</td>\n",
       "      <td>288512380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IL</th>\n",
       "      <td>36061.369200</td>\n",
       "      <td>28891.585241</td>\n",
       "      <td>285209369</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OH</th>\n",
       "      <td>28343.742869</td>\n",
       "      <td>23697.492618</td>\n",
       "      <td>198746325</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GA</th>\n",
       "      <td>31096.454106</td>\n",
       "      <td>26427.370257</td>\n",
       "      <td>154487184</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MI</th>\n",
       "      <td>24123.769515</td>\n",
       "      <td>19222.135702</td>\n",
       "      <td>130726707</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NC</th>\n",
       "      <td>25140.473120</td>\n",
       "      <td>19761.365918</td>\n",
       "      <td>126733125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VA</th>\n",
       "      <td>29221.518236</td>\n",
       "      <td>27907.086917</td>\n",
       "      <td>126587617</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MO</th>\n",
       "      <td>31184.145849</td>\n",
       "      <td>26715.836482</td>\n",
       "      <td>126576448</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TN</th>\n",
       "      <td>29279.454415</td>\n",
       "      <td>25163.437501</td>\n",
       "      <td>122036766</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IN</th>\n",
       "      <td>28144.241080</td>\n",
       "      <td>21779.666285</td>\n",
       "      <td>119894467</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AZ</th>\n",
       "      <td>41199.598036</td>\n",
       "      <td>31169.121867</td>\n",
       "      <td>117460054</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AL</th>\n",
       "      <td>31315.983494</td>\n",
       "      <td>31651.606382</td>\n",
       "      <td>113833600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SC</th>\n",
       "      <td>35862.016860</td>\n",
       "      <td>30104.773378</td>\n",
       "      <td>102099162</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LA</th>\n",
       "      <td>33084.902213</td>\n",
       "      <td>29763.143170</td>\n",
       "      <td>100147999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WA</th>\n",
       "      <td>34713.754140</td>\n",
       "      <td>28553.004813</td>\n",
       "      <td>96434809</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>KY</th>\n",
       "      <td>24523.327965</td>\n",
       "      <td>20173.225562</td>\n",
       "      <td>79185826</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MA</th>\n",
       "      <td>20533.540344</td>\n",
       "      <td>19280.222893</td>\n",
       "      <td>78889862</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CO</th>\n",
       "      <td>41094.644974</td>\n",
       "      <td>33098.991648</td>\n",
       "      <td>77668879</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OK</th>\n",
       "      <td>29587.101984</td>\n",
       "      <td>28852.888015</td>\n",
       "      <td>74559497</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WI</th>\n",
       "      <td>26148.856740</td>\n",
       "      <td>20383.874653</td>\n",
       "      <td>74105860</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NV</th>\n",
       "      <td>61046.650582</td>\n",
       "      <td>50440.896270</td>\n",
       "      <td>73378074</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MS</th>\n",
       "      <td>30292.312448</td>\n",
       "      <td>27465.521923</td>\n",
       "      <td>73004473</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CT</th>\n",
       "      <td>31317.928394</td>\n",
       "      <td>26217.312872</td>\n",
       "      <td>62980354</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MN</th>\n",
       "      <td>27893.879663</td>\n",
       "      <td>24515.480882</td>\n",
       "      <td>62817017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AR</th>\n",
       "      <td>26174.046444</td>\n",
       "      <td>22658.591895</td>\n",
       "      <td>54101754</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>KS</th>\n",
       "      <td>31579.785104</td>\n",
       "      <td>26626.982722</td>\n",
       "      <td>51727688</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MD</th>\n",
       "      <td>13377.323724</td>\n",
       "      <td>10225.908497</td>\n",
       "      <td>44546488</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IA</th>\n",
       "      <td>24168.265859</td>\n",
       "      <td>18271.415942</td>\n",
       "      <td>41907773</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OR</th>\n",
       "      <td>27389.642802</td>\n",
       "      <td>20946.061225</td>\n",
       "      <td>35579146</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NE</th>\n",
       "      <td>31735.972693</td>\n",
       "      <td>27051.752175</td>\n",
       "      <td>33703603</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WV</th>\n",
       "      <td>19191.028949</td>\n",
       "      <td>15523.201940</td>\n",
       "      <td>30494545</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NM</th>\n",
       "      <td>30010.959651</td>\n",
       "      <td>22511.948764</td>\n",
       "      <td>27520050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NH</th>\n",
       "      <td>27058.561361</td>\n",
       "      <td>21346.224017</td>\n",
       "      <td>22269196</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DC</th>\n",
       "      <td>40116.175325</td>\n",
       "      <td>35096.155807</td>\n",
       "      <td>18533673</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>UT</th>\n",
       "      <td>25092.333333</td>\n",
       "      <td>18356.782323</td>\n",
       "      <td>18367588</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ME</th>\n",
       "      <td>20394.472973</td>\n",
       "      <td>14255.999133</td>\n",
       "      <td>18110292</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RI</th>\n",
       "      <td>29942.229592</td>\n",
       "      <td>23510.611724</td>\n",
       "      <td>17606031</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SD</th>\n",
       "      <td>29609.522634</td>\n",
       "      <td>24748.028282</td>\n",
       "      <td>14390228</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HI</th>\n",
       "      <td>32174.287330</td>\n",
       "      <td>23170.712254</td>\n",
       "      <td>14221035</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID</th>\n",
       "      <td>25565.085299</td>\n",
       "      <td>19284.398337</td>\n",
       "      <td>14086362</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MT</th>\n",
       "      <td>22669.537549</td>\n",
       "      <td>17021.721631</td>\n",
       "      <td>11470786</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DE</th>\n",
       "      <td>27071.197970</td>\n",
       "      <td>20018.573942</td>\n",
       "      <td>10666052</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AK</th>\n",
       "      <td>40348.264069</td>\n",
       "      <td>30249.744920</td>\n",
       "      <td>9320449</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ND</th>\n",
       "      <td>21636.407583</td>\n",
       "      <td>17010.941469</td>\n",
       "      <td>9130564</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WY</th>\n",
       "      <td>28700.137652</td>\n",
       "      <td>21605.668160</td>\n",
       "      <td>7088934</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VT</th>\n",
       "      <td>20074.492593</td>\n",
       "      <td>14034.280850</td>\n",
       "      <td>5420113</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        Mean      Std Dev.  State Sum\n",
       "Provider State                                       \n",
       "CA              67508.141457  58784.488889  881926360\n",
       "FL              46015.760197  36107.466017  513305805\n",
       "TX              41479.719319  33915.800108  492115390\n",
       "NJ              66125.205968  47242.551727  319120244\n",
       "PA              39633.485905  41910.130294  309299724\n",
       "NY              31435.212465  28205.859017  288512380\n",
       "IL              36061.369200  28891.585241  285209369\n",
       "OH              28343.742869  23697.492618  198746325\n",
       "GA              31096.454106  26427.370257  154487184\n",
       "MI              24123.769515  19222.135702  130726707\n",
       "NC              25140.473120  19761.365918  126733125\n",
       "VA              29221.518236  27907.086917  126587617\n",
       "MO              31184.145849  26715.836482  126576448\n",
       "TN              29279.454415  25163.437501  122036766\n",
       "IN              28144.241080  21779.666285  119894467\n",
       "AZ              41199.598036  31169.121867  117460054\n",
       "AL              31315.983494  31651.606382  113833600\n",
       "SC              35862.016860  30104.773378  102099162\n",
       "LA              33084.902213  29763.143170  100147999\n",
       "WA              34713.754140  28553.004813   96434809\n",
       "KY              24523.327965  20173.225562   79185826\n",
       "MA              20533.540344  19280.222893   78889862\n",
       "CO              41094.644974  33098.991648   77668879\n",
       "OK              29587.101984  28852.888015   74559497\n",
       "WI              26148.856740  20383.874653   74105860\n",
       "NV              61046.650582  50440.896270   73378074\n",
       "MS              30292.312448  27465.521923   73004473\n",
       "CT              31317.928394  26217.312872   62980354\n",
       "MN              27893.879663  24515.480882   62817017\n",
       "AR              26174.046444  22658.591895   54101754\n",
       "KS              31579.785104  26626.982722   51727688\n",
       "MD              13377.323724  10225.908497   44546488\n",
       "IA              24168.265859  18271.415942   41907773\n",
       "OR              27389.642802  20946.061225   35579146\n",
       "NE              31735.972693  27051.752175   33703603\n",
       "WV              19191.028949  15523.201940   30494545\n",
       "NM              30010.959651  22511.948764   27520050\n",
       "NH              27058.561361  21346.224017   22269196\n",
       "DC              40116.175325  35096.155807   18533673\n",
       "UT              25092.333333  18356.782323   18367588\n",
       "ME              20394.472973  14255.999133   18110292\n",
       "RI              29942.229592  23510.611724   17606031\n",
       "SD              29609.522634  24748.028282   14390228\n",
       "HI              32174.287330  23170.712254   14221035\n",
       "ID              25565.085299  19284.398337   14086362\n",
       "MT              22669.537549  17021.721631   11470786\n",
       "DE              27071.197970  20018.573942   10666052\n",
       "AK              40348.264069  30249.744920    9320449\n",
       "ND              21636.407583  17010.941469    9130564\n",
       "WY              28700.137652  21605.668160    7088934\n",
       "VT              20074.492593  14034.280850    5420113"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Group by state and sum the Average Covered Charges by state\n",
    "#Sort from most expensive to least expensive\n",
    "group_state = govdata.groupby(['Provider State',])\n",
    "group_state['Avg Covered Charges - Int'].agg({'State Sum': np.sum, 'Mean': np.mean, 'Std Dev.': np.std}).sort('State Sum',ascending=[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "California is most expensive while Vermont is least expensive\n"
     ]
    }
   ],
   "source": [
    "print \"California is most expensive while Vermont is least expensive\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "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>DRG Definition</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DRG Definition</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>329 - MAJOR SMALL &amp; LARGE BOWEL PROCEDURES W MCC</th>\n",
       "      <td>199747778</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>853 - INFECTIOUS &amp; PARASITIC DISEASES W O.R. PROCEDURE W MCC</th>\n",
       "      <td>191519783</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>207 - RESPIRATORY SYSTEM DIAGNOSIS W VENTILATOR SUPPORT 96+ HOURS</th>\n",
       "      <td>166806286</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>870 - SEPTICEMIA OR SEVERE SEPSIS W MV 96+ HOURS</th>\n",
       "      <td>153270031</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>470 - MAJOR JOINT REPLACEMENT OR REATTACHMENT OF LOWER EXTREMITY W/O MCC</th>\n",
       "      <td>143171765</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>871 - SEPTICEMIA OR SEVERE SEPSIS W/O MV 96+ HOURS W MCC</th>\n",
       "      <td>129167829</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>460 - SPINAL FUSION EXCEPT CERVICAL W/O MCC</th>\n",
       "      <td>127296308</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>208 - RESPIRATORY SYSTEM DIAGNOSIS W VENTILATOR SUPPORT &lt;96 HOURS</th>\n",
       "      <td>124351732</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>330 - MAJOR SMALL &amp; LARGE BOWEL PROCEDURES W CC</th>\n",
       "      <td>111199770</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>481 - HIP &amp; FEMUR PROCEDURES EXCEPT MAJOR JOINT W CC</th>\n",
       "      <td>102457732</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>291 - HEART FAILURE &amp; SHOCK W MCC</th>\n",
       "      <td>96467126</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193 - SIMPLE PNEUMONIA &amp; PLEURISY W MCC</th>\n",
       "      <td>96326714</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>252 - OTHER VASCULAR PROCEDURES W MCC</th>\n",
       "      <td>96111345</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>177 - RESPIRATORY INFECTIONS &amp; INFLAMMATIONS W MCC</th>\n",
       "      <td>95928234</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>247 - PERC CARDIOVASC PROC W DRUG-ELUTING STENT W/O MCC</th>\n",
       "      <td>93966053</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>238 - MAJOR CARDIOVASC PROCEDURES W/O MCC</th>\n",
       "      <td>89600514</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>246 - PERC CARDIOVASC PROC W DRUG-ELUTING STENT W MCC OR 4+ VESSELS/STENTS</th>\n",
       "      <td>88351423</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>280 - ACUTE MYOCARDIAL INFARCTION, DISCHARGED ALIVE W MCC</th>\n",
       "      <td>86382688</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>682 - RENAL FAILURE W MCC</th>\n",
       "      <td>85601042</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>253 - OTHER VASCULAR PROCEDURES W CC</th>\n",
       "      <td>84387914</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>064 - INTRACRANIAL HEMORRHAGE OR CEREBRAL INFARCTION W MCC</th>\n",
       "      <td>80547975</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190 - CHRONIC OBSTRUCTIVE PULMONARY DISEASE W MCC</th>\n",
       "      <td>79058082</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>243 - PERMANENT CARDIAC PACEMAKER IMPLANT W CC</th>\n",
       "      <td>78045028</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>377 - G.I. HEMORRHAGE W MCC</th>\n",
       "      <td>77615411</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>469 - MAJOR JOINT REPLACEMENT OR REATTACHMENT OF LOWER EXTREMITY W MCC</th>\n",
       "      <td>76147909</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>314 - OTHER CIRCULATORY SYSTEM DIAGNOSES W MCC</th>\n",
       "      <td>74892286</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>194 - SIMPLE PNEUMONIA &amp; PLEURISY W CC</th>\n",
       "      <td>74211335</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>189 - PULMONARY EDEMA &amp; RESPIRATORY FAILURE</th>\n",
       "      <td>71841898</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>480 - HIP &amp; FEMUR PROCEDURES EXCEPT MAJOR JOINT W MCC</th>\n",
       "      <td>69990064</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>292 - HEART FAILURE &amp; SHOCK W CC</th>\n",
       "      <td>69447155</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>300 - PERIPHERAL VASCULAR DISORDERS W CC</th>\n",
       "      <td>36690603</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>310 - CARDIAC ARRHYTHMIA &amp; CONDUCTION DISORDERS W/O CC/MCC</th>\n",
       "      <td>35885021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>698 - OTHER KIDNEY &amp; URINARY TRACT DIAGNOSES W MCC</th>\n",
       "      <td>35872206</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>602 - CELLULITIS W MCC</th>\n",
       "      <td>35836722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101 - SEIZURES W/O MCC</th>\n",
       "      <td>34269622</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>917 - POISONING &amp; TOXIC EFFECTS OF DRUGS W MCC</th>\n",
       "      <td>34010894</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>948 - SIGNS &amp; SYMPTOMS W/O MCC</th>\n",
       "      <td>33012536</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>039 - EXTRACRANIAL PROCEDURES W/O CC/MCC</th>\n",
       "      <td>32889536</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202 - BRONCHITIS &amp; ASTHMA W CC/MCC</th>\n",
       "      <td>32110614</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>419 - LAPAROSCOPIC CHOLECYSTECTOMY W/O C.D.E. W/O CC/MCC</th>\n",
       "      <td>30244040</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>491 - BACK &amp; NECK PROC EXC SPINAL FUSION W/O CC/MCC</th>\n",
       "      <td>29976956</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>057 - DEGENERATIVE NERVOUS SYSTEM DISORDERS W/O MCC</th>\n",
       "      <td>29193042</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>379 - G.I. HEMORRHAGE W/O CC/MCC</th>\n",
       "      <td>26979514</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>439 - DISORDERS OF PANCREAS EXCEPT MALIGNANCY W CC</th>\n",
       "      <td>26041467</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>282 - ACUTE MYOCARDIAL INFARCTION, DISCHARGED ALIVE W/O CC/MCC</th>\n",
       "      <td>25898861</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>390 - G.I. OBSTRUCTION W/O CC/MCC</th>\n",
       "      <td>24962489</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>074 - CRANIAL &amp; PERIPHERAL NERVE DISORDERS W/O MCC</th>\n",
       "      <td>24618738</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>699 - OTHER KIDNEY &amp; URINARY TRACT DIAGNOSES W CC</th>\n",
       "      <td>24066161</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>918 - POISONING &amp; TOXIC EFFECTS OF DRUGS W/O MCC</th>\n",
       "      <td>23684554</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>315 - OTHER CIRCULATORY SYSTEM DIAGNOSES W CC</th>\n",
       "      <td>22262589</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>303 - ATHEROSCLEROSIS W/O MCC</th>\n",
       "      <td>20783641</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>536 - FRACTURES OF HIP &amp; PELVIS W/O MCC</th>\n",
       "      <td>19964067</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>305 - HYPERTENSION W/O MCC</th>\n",
       "      <td>19777282</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>563 - FX, SPRN, STRN &amp; DISL EXCEPT FEMUR, HIP, PELVIS &amp; THIGH W/O MCC</th>\n",
       "      <td>19180772</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149 - DYSEQUILIBRIUM</th>\n",
       "      <td>18644050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>684 - RENAL FAILURE W/O CC/MCC</th>\n",
       "      <td>17163607</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>897 - ALCOHOL/DRUG ABUSE OR DEPENDENCE W/O REHABILITATION THERAPY W/O MCC</th>\n",
       "      <td>15935305</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>203 - BRONCHITIS &amp; ASTHMA W/O CC/MCC</th>\n",
       "      <td>15094787</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>301 - PERIPHERAL VASCULAR DISORDERS W/O CC/MCC</th>\n",
       "      <td>15042256</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>885 - PSYCHOSES</th>\n",
       "      <td>12216452</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                    DRG Definition\n",
       "DRG Definition                                                    \n",
       "329 - MAJOR SMALL & LARGE BOWEL PROCEDURES W MCC         199747778\n",
       "853 - INFECTIOUS & PARASITIC DISEASES W O.R. PR...       191519783\n",
       "207 - RESPIRATORY SYSTEM DIAGNOSIS W VENTILATOR...       166806286\n",
       "870 - SEPTICEMIA OR SEVERE SEPSIS W MV 96+ HOURS         153270031\n",
       "470 - MAJOR JOINT REPLACEMENT OR REATTACHMENT O...       143171765\n",
       "871 - SEPTICEMIA OR SEVERE SEPSIS W/O MV 96+ HO...       129167829\n",
       "460 - SPINAL FUSION EXCEPT CERVICAL W/O MCC              127296308\n",
       "208 - RESPIRATORY SYSTEM DIAGNOSIS W VENTILATOR...       124351732\n",
       "330 - MAJOR SMALL & LARGE BOWEL PROCEDURES W CC          111199770\n",
       "481 - HIP & FEMUR PROCEDURES EXCEPT MAJOR JOINT...       102457732\n",
       "291 - HEART FAILURE & SHOCK W MCC                         96467126\n",
       "193 - SIMPLE PNEUMONIA & PLEURISY W MCC                   96326714\n",
       "252 - OTHER VASCULAR PROCEDURES W MCC                     96111345\n",
       "177 - RESPIRATORY INFECTIONS & INFLAMMATIONS W MCC        95928234\n",
       "247 - PERC CARDIOVASC PROC W DRUG-ELUTING STENT...        93966053\n",
       "238 - MAJOR CARDIOVASC PROCEDURES W/O MCC                 89600514\n",
       "246 - PERC CARDIOVASC PROC W DRUG-ELUTING STENT...        88351423\n",
       "280 - ACUTE MYOCARDIAL INFARCTION, DISCHARGED A...        86382688\n",
       "682 - RENAL FAILURE W MCC                                 85601042\n",
       "253 - OTHER VASCULAR PROCEDURES W CC                      84387914\n",
       "064 - INTRACRANIAL HEMORRHAGE OR CEREBRAL INFAR...        80547975\n",
       "190 - CHRONIC OBSTRUCTIVE PULMONARY DISEASE W MCC         79058082\n",
       "243 - PERMANENT CARDIAC PACEMAKER IMPLANT W CC            78045028\n",
       "377 - G.I. HEMORRHAGE W MCC                               77615411\n",
       "469 - MAJOR JOINT REPLACEMENT OR REATTACHMENT O...        76147909\n",
       "314 - OTHER CIRCULATORY SYSTEM DIAGNOSES W MCC            74892286\n",
       "194 - SIMPLE PNEUMONIA & PLEURISY W CC                    74211335\n",
       "189 - PULMONARY EDEMA & RESPIRATORY FAILURE               71841898\n",
       "480 - HIP & FEMUR PROCEDURES EXCEPT MAJOR JOINT...        69990064\n",
       "292 - HEART FAILURE & SHOCK W CC                          69447155\n",
       "...                                                            ...\n",
       "300 - PERIPHERAL VASCULAR DISORDERS W CC                  36690603\n",
       "310 - CARDIAC ARRHYTHMIA & CONDUCTION DISORDERS...        35885021\n",
       "698 - OTHER KIDNEY & URINARY TRACT DIAGNOSES W MCC        35872206\n",
       "602 - CELLULITIS W MCC                                    35836722\n",
       "101 - SEIZURES W/O MCC                                    34269622\n",
       "917 - POISONING & TOXIC EFFECTS OF DRUGS W MCC            34010894\n",
       "948 - SIGNS & SYMPTOMS W/O MCC                            33012536\n",
       "039 - EXTRACRANIAL PROCEDURES W/O CC/MCC                  32889536\n",
       "202 - BRONCHITIS & ASTHMA W CC/MCC                        32110614\n",
       "419 - LAPAROSCOPIC CHOLECYSTECTOMY W/O C.D.E. W...        30244040\n",
       "491 - BACK & NECK PROC EXC SPINAL FUSION W/O CC...        29976956\n",
       "057 - DEGENERATIVE NERVOUS SYSTEM DISORDERS W/O...        29193042\n",
       "379 - G.I. HEMORRHAGE W/O CC/MCC                          26979514\n",
       "439 - DISORDERS OF PANCREAS EXCEPT MALIGNANCY W CC        26041467\n",
       "282 - ACUTE MYOCARDIAL INFARCTION, DISCHARGED A...        25898861\n",
       "390 - G.I. OBSTRUCTION W/O CC/MCC                         24962489\n",
       "074 - CRANIAL & PERIPHERAL NERVE DISORDERS W/O MCC        24618738\n",
       "699 - OTHER KIDNEY & URINARY TRACT DIAGNOSES W CC         24066161\n",
       "918 - POISONING & TOXIC EFFECTS OF DRUGS W/O MCC          23684554\n",
       "315 - OTHER CIRCULATORY SYSTEM DIAGNOSES W CC             22262589\n",
       "303 - ATHEROSCLEROSIS W/O MCC                             20783641\n",
       "536 - FRACTURES OF HIP & PELVIS W/O MCC                   19964067\n",
       "305 - HYPERTENSION W/O MCC                                19777282\n",
       "563 - FX, SPRN, STRN & DISL EXCEPT FEMUR, HIP, ...        19180772\n",
       "149 - DYSEQUILIBRIUM                                      18644050\n",
       "684 - RENAL FAILURE W/O CC/MCC                            17163607\n",
       "897 - ALCOHOL/DRUG ABUSE OR DEPENDENCE W/O REHA...        15935305\n",
       "203 - BRONCHITIS & ASTHMA W/O CC/MCC                      15094787\n",
       "301 - PERIPHERAL VASCULAR DISORDERS W/O CC/MCC            15042256\n",
       "885 - PSYCHOSES                                           12216452\n",
       "\n",
       "[100 rows x 1 columns]"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Group by DRG and sum the Average Covered Charges by DRG\n",
    "#Sort from most expensive to least expensive\n",
    "group_drg = govdata.groupby(['DRG Definition',])\n",
    "group_drg['Avg Covered Charges - Int'].agg({'DRG Definition': np.sum}).sort('DRG Definition',ascending=[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}