files_overview.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "/Users/ksenia/healthcare_fcst/code/healthcare_data\n",
      "README.md            files_overview.ipynb\n"
     ]
    }
   ],
   "source": [
    "! pwd\n",
    "! cd ..\n",
    "! ls"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/ksenia/anaconda/lib/python2.7/site-packages/IPython/core/interactiveshell.py:2717: DtypeWarning: Columns (9,10,11,46,47,48,49,50,136,137,138,139,140,141) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    }
   ],
   "source": [
    "#claim files for merging\n",
    "claims1 = pd.read_csv('../../DE1_0_2008_to_2010_Carrier_Claims_Sample_2A.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/ksenia/anaconda/lib/python2.7/site-packages/IPython/core/interactiveshell.py:2717: DtypeWarning: Columns (8,9,10,11,45,46,47,48,49,50,135,136,137,138,139,140,141) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    }
   ],
   "source": [
    "claims2 = pd.read_csv('../../DE1_0_2008_to_2010_Carrier_Claims_Sample_2B.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(4745914, 142)"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Merging two claims files\n",
    "claims = pd.concat([claims1, claims2])\n",
    "claims.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(98686,)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "count = claims['DESYNPUF_ID'].unique()\n",
    "count.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "20080101"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Earliest claim date\n",
    "claims['CLM_FROM_DT'].min()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "20101231"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Latest claim date\n",
    "claims['CLM_FROM_DT'].max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#Obtain inpatient claims\n",
    "inpatient = pd.read_csv('../../DE1_0_2008_to_2010_Inpatient_Claims_Sample_2.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(66494, 81)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "inpatient.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0         True\n",
       "1         True\n",
       "2         True\n",
       "3        False\n",
       "4        False\n",
       "5        False\n",
       "6        False\n",
       "7        False\n",
       "8        False\n",
       "9        False\n",
       "10       False\n",
       "11       False\n",
       "12       False\n",
       "13       False\n",
       "14       False\n",
       "15       False\n",
       "16       False\n",
       "17       False\n",
       "18       False\n",
       "19       False\n",
       "20       False\n",
       "21       False\n",
       "22       False\n",
       "23       False\n",
       "24       False\n",
       "25       False\n",
       "26       False\n",
       "27       False\n",
       "28       False\n",
       "29       False\n",
       "         ...  \n",
       "66464    False\n",
       "66465    False\n",
       "66466    False\n",
       "66467    False\n",
       "66468    False\n",
       "66469    False\n",
       "66470    False\n",
       "66471    False\n",
       "66472    False\n",
       "66473    False\n",
       "66474    False\n",
       "66475    False\n",
       "66476    False\n",
       "66477    False\n",
       "66478    False\n",
       "66479    False\n",
       "66480    False\n",
       "66481    False\n",
       "66482    False\n",
       "66483    False\n",
       "66484    False\n",
       "66485    False\n",
       "66486    False\n",
       "66487    False\n",
       "66488    False\n",
       "66489    False\n",
       "66490    False\n",
       "66491    False\n",
       "66492    False\n",
       "66493    False\n",
       "Name: DESYNPUF_ID, Length: 66494, dtype: bool"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "inpatient['DESYNPUF_ID'] == '00000B48BCF4AD29'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(37661,)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Number of unique patients\n",
    "count= inpatient['DESYNPUF_ID'].unique()\n",
    "count.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "20071127.0"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "inpatient['CLM_FROM_DT'].min()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "20101229.0"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "inpatient['CLM_FROM_DT'].max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/ksenia/anaconda/lib/python2.7/site-packages/IPython/core/interactiveshell.py:2717: DtypeWarning: Columns (21,23,24,25,26,27) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    }
   ],
   "source": [
    "#Obtain outpatient claims data\n",
    "outpatient = pd.read_csv('../../DE1_0_2008_to_2010_Outpatient_Claims_Sample_2.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(792562, 76)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "outpatient.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(85295,)"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "count= outpatient['DESYNPUF_ID'].unique()\n",
    "count.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "20071212.0"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "outpatient['CLM_FROM_DT'].min()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "20101231.0"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "outpatient['CLM_FROM_DT'].max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/ksenia/anaconda/lib/python2.7/site-packages/IPython/core/interactiveshell.py:2717: DtypeWarning: Columns (3) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    }
   ],
   "source": [
    "#Obtain prescription drug data\n",
    "pde = pd.read_csv('../../DE1_0_2008_to_2010_Prescription_Drug_Events_Sample_2.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(5561154, 8)"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pde.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(99746,)"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "count= pde['DESYNPUF_ID'].unique()\n",
    "count.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#Beneficiary data files for merging\n",
    "ben8 = pd.read_csv('../../DE1_0_2008_Beneficiary_Summary_File_Sample_2.csv')\n",
    "ben9 = pd.read_csv('../../DE1_0_2009_Beneficiary_Summary_File_Sample_2.csv')\n",
    "ben10 = pd.read_csv('../../DE1_0_2010_Beneficiary_Summary_File_Sample_2.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['DESYNPUF_ID',\n",
       " 'BENE_BIRTH_DT',\n",
       " 'BENE_DEATH_DT',\n",
       " 'BENE_SEX_IDENT_CD',\n",
       " 'BENE_RACE_CD',\n",
       " 'BENE_ESRD_IND',\n",
       " 'SP_STATE_CODE',\n",
       " 'BENE_COUNTY_CD',\n",
       " 'BENE_HI_CVRAGE_TOT_MONS',\n",
       " 'BENE_SMI_CVRAGE_TOT_MONS',\n",
       " 'BENE_HMO_CVRAGE_TOT_MONS',\n",
       " 'PLAN_CVRG_MOS_NUM',\n",
       " 'SP_ALZHDMTA',\n",
       " 'SP_CHF',\n",
       " 'SP_CHRNKIDN',\n",
       " 'SP_CNCR',\n",
       " 'SP_COPD',\n",
       " 'SP_DEPRESSN',\n",
       " 'SP_DIABETES',\n",
       " 'SP_ISCHMCHT',\n",
       " 'SP_OSTEOPRS',\n",
       " 'SP_RA_OA',\n",
       " 'SP_STRKETIA',\n",
       " 'MEDREIMB_IP',\n",
       " 'BENRES_IP',\n",
       " 'PPPYMT_IP',\n",
       " 'MEDREIMB_OP',\n",
       " 'BENRES_OP',\n",
       " 'PPPYMT_OP',\n",
       " 'MEDREIMB_CAR',\n",
       " 'BENRES_CAR',\n",
       " 'PPPYMT_CAR']"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(ben8)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "ben8.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "ben9.head()\n",
    "ben9.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "ben10.head()\n",
    "ben10.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "ben_all = pd.concat([ben8, ben9, ben10])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "ben_all.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "ben_all.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "count = ben_all['DESYNPUF_ID'].unique()\n",
    "count.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "ben_all.describe().transpose()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "for field in ben_all:\n",
    "    print(field, ben_all[field].unique().shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "print(ben_all['SP_CHF'].unique())"
   ]
  },
  {
   "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.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}