kernel.ipynb

{
  "cells": [
    {
      "metadata": {
        "_cell_guid": "3e29e49b-e169-443c-9fbe-f677ed716c58",
        "_uuid": "031acb533a87624826c0756ce8016d4efe95402b"
      },
      "cell_type": "markdown",
      "source": "![](http://www.kiplinger.com/kipimages/pages/14024.jpg)\n\n# An Extensive Medicare Data Exploration - Healthcare Analysis\n\n## About Medicare \n\nIn the United States, Medicare is a single-payer, national social insurance program administered by the U.S. federal government since 1966. United States Medicare is funded by a payroll tax, premiums and surtaxes from beneficiaries, and general revenue. It provides health insurance for Americans aged 65 and older who have worked and paid into the system through the payroll tax. It also provides health insurance to younger people with some disability status as determined by the Social Security Administration, as well as people with end stage renal disease and amyotrophic lateral sclerosis.\n\nIn 2015, Medicare provided health insurance for over **55 million** (46 million people age 65 and older and 9 million younger people). On average, Medicare covers about half of the health care charges for those enrolled. The enrollees must then cover their remaining costs either with supplemental insurance, separate insurance, or out-of-pocket. Out-of-pocket costs can vary depending on the amount of health care a Medicare enrollee needs. \n\n## About CMS\n\nThe Centers for Medicare & Medicaid Services (CMS), previously known as the Health Care Financing Administration (HCFA), is a federal agency within the United States Department of Health and Human Services (HHS) that administers the Medicare program and works in partnership with state governments to administer Medicaid, the Children's Health Insurance Program (CHIP), and health insurance portability standards. In addition to these programs, CMS has other responsibilities, including the administrative simplification standards from the Health Insurance Portability and Accountability Act of 1996 (HIPAA), quality standards in long-term care facilities (more commonly referred to as nursing homes) through its survey and certification process, clinical laboratory quality standards under the Clinical Laboratory Improvement Amendments, and oversight of HealthCare.gov.\n\n## About the Data-Set\n\nThis [public dataset](https://www.kaggle.com/cms/cms-medicare) was created by the Centers for Medicare & Medicaid Services. The data summarizes the utilization and payments for procedures, services, and prescription drugs provided to Medicare beneficiaries by specific inpatient and outpatient hospitals, physicians, and other suppliers. The dataset includes the following data: Common inpatient and outpatient services, All physician and other supplier procedures and services, All Part D prescriptions. Providers determine what they will charge for items, services, and procedures provided to patients and these charges are the amount that providers bill for an item, service, or procedure.\n\nIn this notebook, I have explored the complete dataset using BigQuery, performed clustering on nursing facilities data to identify groups of nursing facilities having similar characteristics\n\n## Contents\n\n1. States having more nursing facilities  \n2. HCC Scores - Which State and Which facilities have poor HCC scores  \n&nbsp;&nbsp;&nbsp;&nbsp;  2.1 Max and Min HCC Scores for different States   \n&nbsp;&nbsp;&nbsp;&nbsp;  2.2 Average HCC scores for different States  \n&nbsp;&nbsp;&nbsp;&nbsp;  2.3 Facilities with High HCC scores  \n&nbsp;&nbsp;&nbsp;&nbsp;  2.4 Facilities with Low HCC Scores\n   \n3. Beneficiary Gender    \n&nbsp;&nbsp;&nbsp;&nbsp;  3.1 States having more male beneficiaries than female beneficiaries      \n&nbsp;&nbsp;&nbsp;&nbsp;  3.2 States and the difference in male and femal beneficiaries    \n&nbsp;&nbsp;&nbsp;&nbsp;  3.3 Difference in Average Inpatient and Outpatient payments   \n\n4. Which state has the largest number of stay days  \n&nbsp;&nbsp;&nbsp;&nbsp;  4.1 States and their Average Total Stays (of different facilities) and Average length of stays    \n&nbsp;&nbsp;&nbsp;&nbsp;  4.2  Facilities having highest average length of stay days  \n\n5. Total Claims, Day Supply and Drug Costs  \n&nbsp;&nbsp;&nbsp;&nbsp;  5.1 States having highest claims, supply, and cost      \n&nbsp;&nbsp;&nbsp;&nbsp;  5.2 Drugs having highest claims, supply and cost      \n&nbsp;&nbsp;&nbsp;&nbsp;  5.3 Nursing Specialities having highest claims, supply, and cost      \n&nbsp;&nbsp;&nbsp;&nbsp;  5.4 Most prescribed Drug of each state    \n   \n6. Clustering the Nursing Facilities   \n&nbsp;&nbsp;&nbsp;&nbsp;    6.1 Applying K-Means Clustering to obtain Groups  \n&nbsp;&nbsp;&nbsp;&nbsp;    6.2 Exploring the Nursing Facilities Clusters  \n&nbsp;&nbsp;&nbsp;&nbsp;    6.3 Mean Comparisons of Key Features  \n&nbsp;&nbsp;&nbsp;&nbsp;    6.4 Distribution of Key Features  \n&nbsp;&nbsp;&nbsp;&nbsp;    6.5 Inference about different Groups of Nursing Facilities  \n\n"
    },
    {
      "metadata": {
        "_cell_guid": "79c7e3d0-c299-4dcb-8224-4455121ee9b0",
        "_kg_hide-input": true,
        "_uuid": "d629ff2d2480ee46fbb7e2d37f6b5fab8052498a",
        "trusted": true
      },
      "cell_type": "code",
      "source": "# Import the required libraries \n\nfrom bq_helper import BigQueryHelper\nimport bq_helper\n\nfrom plotly.offline import init_notebook_mode, iplot\nfrom wordcloud import WordCloud\nimport plotly.graph_objs as go\nimport matplotlib.pyplot as plt\nimport plotly.plotly as py\nfrom plotly import tools\n\nimport warnings\nwarnings.filterwarnings(\"ignore\")\n\nimport pandas as pd\ninit_notebook_mode(connected=True)\n\nmedicare = bq_helper.BigQueryHelper(active_project=\"bigquery-public-data\", dataset_name=\"cms_medicare\")\nbq_assistant = BigQueryHelper(\"bigquery-public-data\", \"cms_medicare\")",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "_cell_guid": "e3ff4b23-437b-4142-aea6-5443a013d02f",
        "_uuid": "6a2c1cc3ba381b59eb50fe113f8507260ec5dbcb"
      },
      "cell_type": "markdown",
      "source": "## 1. Which State has the highest (and lowest) number of Nursing Facilities\n\nSkilled nursing facilities (SNFs) and nursing facilities (NFs) are provided by CMS under medicare. Different States of US have differnt number of nursing facilities.  Number of nursing facilities in a state might be an indicator of how good is the quality of care in different states. Lets obtain the number if of nursing facilities using 2014 dataset\n\n![](http://www.arh.org/Uploads/Images/Page/services-skillednursing%5B1%5D.jpg)\n"
    },
    {
      "metadata": {
        "_cell_guid": "ed3542ed-8810-44ce-892c-02127684b72c",
        "_kg_hide-input": true,
        "_uuid": "e4f8cf8f402eddcef918f974aac19af3e3cba45c",
        "trusted": true
      },
      "cell_type": "code",
      "source": "# How many nurrsing facilitins in different States \nquery1 = \"\"\"SELECT\nstate, COUNT(state) as total_facilities\nFROM\n  `bigquery-public-data.cms_medicare.nursing_facilities_2014`\n  GROUP BY \n  state\nORDER BY\n  total_facilities DESC;\"\"\"\nresponse1 = medicare.query_to_pandas_safe(query1)\nresponse1.head(10)",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "_cell_guid": "6392383b-b3ea-4328-adec-3ede7a75030c",
        "_kg_hide-input": true,
        "_uuid": "5cbfbadb831864c281e6d0c16410b78437c0e83d",
        "trusted": true
      },
      "cell_type": "code",
      "source": "scl = [[0.0, 'rgb(248,255,206)'],[0.2, 'rgb(203,255,205)'],[0.4, 'rgb(155,255,164)'], [0.6, 'rgb(79,255,178)'],[0.8, 'rgb(15,183,132)'], [1, '#008059']]\nquery1 = \"\"\"SELECT\nstate, COUNT(state) as total_facilities\nFROM\n  `bigquery-public-data.cms_medicare.nursing_facilities_2014`\n  GROUP BY \n  state\nORDER BY\n  total_facilities DESC;\"\"\"\nresponse1 = medicare.query_to_pandas_safe(query1)\nresponse1.head(10)\ndata = [ dict(\n        type='choropleth',\n        colorscale = scl,\n        autocolorscale = False,\n        locations = response1.state,\n        z = response1.total_facilities,\n        locationmode = 'USA-states',\n        text = response1.state,\n        marker = dict(\n            line = dict (\n                color = 'rgb(255,255,255)',\n                width = 2\n            ) ),\n        colorbar = dict(\n            title = \"Facilities in Different States\")\n        )\n       ]\n\nlayout = dict(\n        title = 'Nursing Facilities in Different States',\n        geo = dict(\n            scope='usa',\n            projection=dict( type='albers usa' ),\n            showlakes = True,\n            lakecolor = 'rgb(255, 255, 255)'),\n             )\n    \nfig = dict( data=data, layout=layout )\niplot( fig, filename='d3-cloropleth-map' )",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "_cell_guid": "5702e6f4-0b18-4942-a0ce-66ffac901abb",
        "_uuid": "8766a17ad542cc436fc52f4e6efd07a3045dd921"
      },
      "cell_type": "markdown",
      "source": "**Inference**  \n> Texas and California are the only states of US having more than 1000 Nursing Facilities   \n> Purto Rico and Alaska are the states having exteremly low Nursing facilities with 5 and 7 respectively  \n> About 40% of the total states have less than 200 nursing facilities \n\n\n\n## 2. Which State (and Nursing Facilities) have poor HCC risk adjustment scores \n\nThe Center for Medicare & Medicaid Services (CMS) uses a Hierarchical Condition Category (HCC) risk adjustment model to calculate risk scores. The HCC model ranks diagnoses into categories that represent conditions with similar cost patterns. Higher categories represent higher predicted healthcare costs, resulting in higher risk scores. CMS uses HCCs to reimburse Medicare Advantage plans based on the health of their members. It pays accurately for the predicted cost expenditures of patients by adjusting those payments based on demographic information and patient health status. The risk assessment data used is based on the diagnosis information pulled from claims and medical records which are collected by physician offices, hospital inpatient visits and in outpatient settings.\n\nCMS states that higher HCC scores means there is higher risk of chronic diseases while lower hcc indicates healthy population. It will be interesting to see what is the average hcc score for different states and facilities. \n\n\n### 2.1 Max and Min HCCs for different states\n"
    },
    {
      "metadata": {
        "_cell_guid": "08e94b2a-c3ed-4762-9ea8-f1466ba7805a",
        "_kg_hide-input": true,
        "_uuid": "e122219323fdde660cd51ff62b9b8cc50bb38b92",
        "trusted": true
      },
      "cell_type": "code",
      "source": "query2 = \"\"\"SELECT\n  state, AVG(average_hcc_score) as avg_hcc_score, Max(average_hcc_score) as max_hcc_score, Min(average_hcc_score) as min_hcc_score, COUNT(average_hcc_score) as total_facilities\nFROM\n  `bigquery-public-data.cms_medicare.nursing_facilities_2014`\n  GROUP BY \n  state\nORDER BY\n  avg_hcc_score DESC;\n        \"\"\"\nresponse2 = medicare.query_to_pandas_safe(query2)\nresponse2.head(10)",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "_cell_guid": "22e0b53c-9835-48b4-89af-91e499479ec2",
        "_kg_hide-input": true,
        "_uuid": "e4c93a16599b88e71f2f3c35df140fb79b6e9419",
        "trusted": true
      },
      "cell_type": "code",
      "source": "trace1 = go.Bar(\n    x=response2.state,\n    y=response2.max_hcc_score,\n    name='Max HCC Score',\n    marker=dict(color='rgb(158,202,225)'),\n)\ntrace2 = go.Bar(\n    x=response2.state,\n    y=response2.min_hcc_score,\n    name='Min HCC Score',\n    marker=dict(color='rgb(58,22,25)'),\n)\n\ndata = [trace1, trace2]\nlayout = go.Layout(\n    barmode='group',\n    title='Minimum and Maximum HCC scores for different States',\n    legend=dict(orientation=\"h\")\n)\n\nfig = go.Figure(data=data, layout=layout)\niplot(fig, filename='grouped-bar')",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "_cell_guid": "2d1886d5-7ef6-45cc-b672-634a3cadcd0b",
        "_uuid": "5f5c89ec5fa86c361471ea4fdfcf620eb0484453"
      },
      "cell_type": "markdown",
      "source": "**Inference**  \n> California's maximum HCC Score is exteremly high for one of the facility = 9.15  \n> Other States having HCC score on the higher side: Pennsylvania (7.84), Minnesotta (7.12), OHIO (7.07)  \n> Hawaii and Montana are the states having facilites with least possible HCC scores of 0.75 and 0.79 respectively \n\n### 2.2 Average HCC for different states"
    },
    {
      "metadata": {
        "_cell_guid": "3a3c9ce0-a217-4fad-8f10-7bb0e350d36e",
        "_kg_hide-input": true,
        "_uuid": "8852fdf96ac3e6ff9420ceb5b80d5a0b22e3f45a",
        "trusted": true
      },
      "cell_type": "code",
      "source": "### Average HCC Scores for different states \n\nscl = [[0.0, 'rgb(255,233,225)'],[0.2, 'rgb(249,220,211)'],[0.4, 'rgb(227,192,183)'], [0.6, 'rgb(210,178,167)'],[0.8, 'rgb(182,151,146)'],[1.0, '#734842']]\n\ndata = [ dict(\n        type='choropleth',\n        colorscale = scl,\n        autocolorscale = False,\n        locations = response2.state,\n        z = response2.avg_hcc_score,\n        locationmode = 'USA-states',\n        text = response2.state,\n        marker = dict(\n            line = dict (\n                color = 'rgb(255,255,255)',\n                width = 2\n            ) ),\n        colorbar = dict(\n            title = \"Average HCC Scores\")\n        )\n       ]\n\nlayout = dict(\n        title = 'Average HCC Scores for different states',\n        geo = dict(\n            scope='usa',\n            projection=dict( type='albers usa' ),\n            showlakes = True,\n            lakecolor = 'rgb(255, 255, 255)'),\n             )\n    \nfig = dict( data=data, layout=layout )\niplot( fig, filename='d3-cloropleth-map' )",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "_cell_guid": "b66db712-ba2b-4b54-a3af-a0d9a8873a36",
        "_uuid": "3921a4f3199134e65de8a5a9f52d5d747c0aa97d"
      },
      "cell_type": "markdown",
      "source": "**Inference**  \n> States having highest Average HCC scores are California (2.6), Texas(2.5), Florida(2.5), Minnesotta (2.5), Louisiana(2.5), and New Jersy (2.5)  \n> States having lowest Average HCC scores are Montana (1.8), South Dakota (1.8), Wyoming (1.7), Purto Rico (1.3)\n\n### 2.3 Nursing Facilities with very HIGH HCC score"
    },
    {
      "metadata": {
        "_cell_guid": "cf9ac394-b8e7-483f-8725-66f0cdd1e05f",
        "_kg_hide-input": true,
        "_uuid": "1c168a98edc753ebca2b1d06b5d425ae21e36480",
        "trusted": true
      },
      "cell_type": "code",
      "source": "query3 = \"\"\"SELECT facility_name, city, state, average_hcc_score\nFROM `bigquery-public-data.cms_medicare.nursing_facilities_2014`\nORDER BY average_hcc_score DESC;\n        \"\"\"\nresponse3 = medicare.query_to_pandas_safe(query3)\nresponse3.head(5)",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "_cell_guid": "138f54aa-8593-4e32-95a3-5ab343ebc287",
        "_uuid": "deea96e6f2f4b60c8953e1bb3a328d7e35799b9f"
      },
      "cell_type": "markdown",
      "source": "### 2.4 Nursing Facilities with very LOW HCC score"
    },
    {
      "metadata": {
        "_cell_guid": "68c9ac79-cecf-4a89-90bc-823ff53c2117",
        "_kg_hide-input": true,
        "_uuid": "ec26489883a78ec5c051874fb8f72908da6148f0",
        "trusted": true
      },
      "cell_type": "code",
      "source": "response3.tail(5)",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "_cell_guid": "f66cc234-deda-46d2-aeb6-32fcc4789e96",
        "_kg_hide-input": true,
        "_uuid": "47bc8b0a601b60c896e9c19a54e7fddc82ab2438",
        "trusted": true
      },
      "cell_type": "code",
      "source": "tempdf = response3.head(10)\ny = list(reversed(list(tempdf.facility_name)))\nx = list(reversed(list(tempdf.average_hcc_score)))\ntrace1 = go.Bar(x=x, y=y, orientation=\"h\", marker=dict(color='#f79284'),)\nlayout = dict(height=400, width=700, margin=dict(l=400), title='Nursing Facilities having Maximum Average HCC Scores')\ndata = [trace1]\n\nfig = go.Figure(data=data, layout=layout)\niplot(fig, filename='stacked-bar')",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "_cell_guid": "85b4e51d-417e-4f36-81e6-01adac00eecf",
        "_uuid": "e3a31e04f9ebeae13096b454eb252112600bd86b"
      },
      "cell_type": "markdown",
      "source": "**Inference**  \n> Facilities having highest Average HCC Score of 9.15 is Doctors Hosp of West Covina, located in California.  \n> KFH - Malama Ohana Nursing and Rehab Center' in HONOLULU, Hawaii has the lowest Average HCC Score\n\n## 3. Is there any state where the number of Male Beneficiaries is greater than Female Beneficiaries ?"
    },
    {
      "metadata": {
        "_cell_guid": "b5a6d550-86f3-47d7-b969-05d2bd6dd2fd",
        "_kg_hide-input": true,
        "_uuid": "ad47a792c446135a018f85baff8b12f4d48456fa",
        "trusted": true
      },
      "cell_type": "code",
      "source": "query1 = \"\"\"SELECT\n  state, Sum(male_beneficiaries) as male_ben, Sum(female_beneficiaries) as female_ben\nFROM\n  `bigquery-public-data.cms_medicare.nursing_facilities_2014`\n  GROUP BY \n      state\n  ORDER BY\n      male_ben DESC;\n        \"\"\"\nresponse1 = medicare.query_to_pandas_safe(query1)\n\n\ntrace1 = go.Bar(\n    x=response1.state,\n    y=response1.male_ben,\n    marker=dict(color='#e584f7'),\n    name='Total Male beneficiaries'\n)\ntrace2 = go.Bar(\n    x=response1.state,\n    y=response1.female_ben,\n    marker=dict(color='#a0f784'),\n    name='Total Female beneficiaries'\n)\n\ndata = [trace1, trace2]\nlayout = go.Layout(barmode='group', legend=dict(orientation='h'), title='Male and Female Beneficiaries in 2014 in different states')\nfig = go.Figure(data=data, layout=layout)\niplot(fig, filename='grouped-bar')",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "_cell_guid": "2385b3e1-8883-4e1e-bff0-3f8a3d269b2f",
        "_uuid": "aa37faa471c04de6e50b36e6dcac576a298c14ac"
      },
      "cell_type": "markdown",
      "source": "**Inference**  \n> All of the states have more female beneficiaries than male beneficiaries, which indicates that there might exist a fixed quota for number of beneficiaries.   \n> Highest number of feamle beneficiaries are from Florida State while highest number of male beneficiaries are from California State\n\n### 3.2 Difference in Male and Female Beneficiaries"
    },
    {
      "metadata": {
        "_cell_guid": "08c59813-95a5-430c-8f7c-052b22fc89f9",
        "_kg_hide-input": true,
        "_uuid": "82cc70db09931903e748c007f15a1c33e0fbba32",
        "trusted": true
      },
      "cell_type": "code",
      "source": "response1['difference'] = response1['female_ben'] - response1['male_ben']\n\ntrace1 = go.Bar(\n    x=response1.state,\n    y=response1.difference,\n    marker=dict(color='#aabfe0'),\n    name='Total Male beneficiaries'\n)\ndata = [trace1]\nlayout = go.Layout(barmode='group', legend=dict(orientation='h'), title='Difference in Male and Female Beneficiaries in Different State')\nfig = go.Figure(data=data, layout=layout)\niplot(fig, filename='grouped-bar')",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "_cell_guid": "fd170819-d544-47d4-815c-e6f2ad209121",
        "_uuid": "62f30298e94e2bee99e46f61ca2b8bc115e34b49"
      },
      "cell_type": "markdown",
      "source": "**Inference**  \n> Florida has the highest gap in number of male vs female beneficiaries with about 30,000 more female beneficiaries  \n> Alaska, Purto Rico, Hawaii, Wyoming has the lowest\n\n### 3.3 Difference in Average Inpatient payment and Average Outpatient payments"
    },
    {
      "metadata": {
        "_kg_hide-input": true,
        "trusted": true,
        "_uuid": "55e88c6de2ce8052f24e63a62ab530d38a381e44"
      },
      "cell_type": "code",
      "source": "query3_a = \"\"\"SELECT provider_id, avg(average_total_payments) as average_outpatient_payments\nFROM `bigquery-public-data.cms_medicare.outpatient_charges_2014` group by provider_id;\"\"\"\noutp = medicare.query_to_pandas_safe(query3_a)\n\nquery3_b = \"\"\"SELECT provider_id, avg(average_total_payments) as average_inpatient_payments\nFROM `bigquery-public-data.cms_medicare.inpatient_charges_2014` group by provider_id;\"\"\"\ninp = medicare.query_to_pandas_safe(query3_b)\n\nmerged = outp.merge(inp, on=\"provider_id\", how = 'inner')\nmerged[\"difference\"] = merged[\"average_inpatient_payments\"] - merged['average_outpatient_payments']\n\ntracex = go.Histogram(x = merged[\"difference\"]) \nfig = go.Figure(data = [tracex])\nfig['layout'].update(height=400, showlegend=False, title=\"Difference in average inpatient and outpatient payments\");\niplot(fig, filename='simple-subplot');",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "_cell_guid": "8e18d07a-aa17-4ac9-bdb1-c1be40f1e6a6",
        "_uuid": "7916d58c069559cbe6511e66cb6fb6fd046bb4d8"
      },
      "cell_type": "markdown",
      "source": "## 4. Finding States / Facilities with poor average length of stay days \n\n### 4.1 States and their average total number stays (of different facilities) and average length of stays (in different facilities)"
    },
    {
      "metadata": {
        "_cell_guid": "c510832d-a1fb-437d-9835-70b9573f1f5c",
        "_kg_hide-input": true,
        "_uuid": "a2a431b26d2e10c55673711926ccfcfebd1e83b2",
        "trusted": true
      },
      "cell_type": "code",
      "source": "query2 = \"\"\"SELECT\n  state, AVG(average_length_of_stays_days) as average_length_of_stays_days, AVG(total_stays) as total_stays\nFROM\n  `bigquery-public-data.cms_medicare.nursing_facilities_2014`\n  GROUP BY \n  state\nORDER BY\n  average_length_of_stays_days DESC;\n        \"\"\"\nresponse2 = medicare.query_to_pandas_safe(query2)\nresponse2.head(10)\n\n\ntrace0 = go.Scatter(\n    x=response2.average_length_of_stays_days,\n    y=response2.state,\n    mode='markers',\n    name='Average Length of Stay in a State',\n    marker=dict(\n        color='rgba(156, 165, 196, 0.95)',\n        line=dict(\n            color='rgba(156, 165, 196, 1.0)',\n            width=1,\n        ),\n        symbol='circle',\n        size=16,\n    )\n)\ntrace1 = go.Scatter(\n    x=response2.total_stays,\n    y=response2.state,\n    mode='markers',\n    name='Average of Total Stays in a State ',\n    marker=dict(\n        color='#00d8d8',\n        line=dict(\n            color='rgba(217, 217, 217, 1.0)',\n            width=1,\n        ),\n        symbol='circle',\n        size=16,\n    )\n)\n\ndata = [trace0, trace1]\nlayout = go.Layout(\n    title=\"Average Total Stays of each State and Average Length of Stays for each state \",\n    xaxis=dict(\n        showgrid=False,\n        showline=True,\n        linecolor='rgb(102, 102, 102)',\n        titlefont=dict(\n            color='rgb(204, 204, 204)'\n        ),\n        tickfont=dict(\n            color='rgb(102, 102, 102)',\n        ),\n#         autotick=False,\n        dtick=10,\n        ticks='outside',\n        tickcolor='rgb(102, 102, 102)',\n    ),\n    margin=dict(\n        l=140,\n        r=40,\n        b=50,\n        t=80\n    ),\n    legend=dict(\n        font=dict(\n            size=10,\n        ),\n        yanchor='middle',\n        xanchor='right',\n    ),\n#     width=800,\n    height=600,\n    paper_bgcolor='rgb(254, 247, 234)',\n    plot_bgcolor='rgb(254, 247, 234)',\n    hovermode='closest',\n)\nfig = go.Figure(data=data, layout=layout)\niplot(fig, filename='lowest-oecd-votes-cast')",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "_cell_guid": "a0260a3e-0bab-465b-a21e-be52c2240ada",
        "_uuid": "619d6e940c17a339a1316220fd331da2bb77b420"
      },
      "cell_type": "markdown",
      "source": "**Inference**  \n> - Average Length of Stay among the patients in all the states is very close to 30 days. (They also want to avoid 30-day readmission penalty)  \n> - Arizona has the significantly lower average lenght of stay equal to 22 which is quite less as compared to other states   \n> - Louisiana, Texas, Illinois, Mississippi are the states having greater than 30 day average length of stay, which indicates that patients are admitted there for longer number of days than average  \n> - Purto Rico, a territory of US has the lowest average length of stay among all the states and terriroties equal to 12.\n  \n  > - New Jersy's nursing facilities has on-an-average very large number of total stays (equal to 321)  \n  > - Iowa has the minium among the states equal to 73 total stays among its nursing facilities\n\n### 4.2 Facilities having highest average length of stay days"
    },
    {
      "metadata": {
        "_cell_guid": "f2a622c4-698f-45cd-b550-d30e80809fb4",
        "_kg_hide-input": true,
        "_uuid": "4bae17c6a81706b5fcd1e449cac7f77d639c0b93",
        "trusted": true
      },
      "cell_type": "code",
      "source": "# which facilities have highest stays count \n\nquery1 = \"\"\"SELECT\n  facility_name, city, state, average_length_of_stays_days\nFROM\n  `bigquery-public-data.cms_medicare.nursing_facilities_2014`\nORDER BY\n  average_length_of_stays_days DESC;\n        \"\"\"\nresponse1 = medicare.query_to_pandas_safe(query1)\n\ntempdf = response1.head(10)\ny = list(reversed(list(tempdf.facility_name)))\nx = list(reversed(list(tempdf.average_length_of_stays_days)))\ntrace1 = go.Bar(x=x, y=y, orientation=\"h\", marker=dict(color='#f7e484'))\nlayout = dict(height=400, width=700, title='Nursing Facilities having Maximum Average Length of Stay Days', margin=dict(l=400))\ndata = [trace1]\n\nfig = go.Figure(data=data, layout=layout)\niplot(fig, filename='stacked-bar')",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "_cell_guid": "0bbedf67-1e31-4e18-b4ca-182c307dafcd",
        "_uuid": "90a24ff863ed4d665e53a660586eb40ded42c659"
      },
      "cell_type": "markdown",
      "source": "## 5. Lets Explore the Total Claims, Day Supply and Drug Costs \n\n### 5.1 Which State has highest claims, Day Supply, and Drug Costs"
    },
    {
      "metadata": {
        "_cell_guid": "45789737-8575-46df-acc9-89ea67dbf013",
        "_kg_hide-input": true,
        "_uuid": "591f73681358c5e7a14c208ddde84c7a881e29fe",
        "trusted": true
      },
      "cell_type": "code",
      "source": "query1 = \"\"\"SELECT\n  nppes_provider_state AS state,\n  ROUND(SUM(total_claim_count) / 1e6) AS total_claim_count_millions,\n  ROUND(SUM(total_day_supply) / 1e6) AS total_day_supply_millions,\n  ROUND(SUM(total_drug_cost) / 1e6) AS total_drug_cost_millions\nFROM\n  `bigquery-public-data.cms_medicare.part_d_prescriber_2014`\nGROUP BY\n  state\nORDER BY\n  total_claim_count_millions DESC;\n        \"\"\"\nresponse1 = medicare.query_to_pandas_safe(query1)\nresponse1.head(10)",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "_cell_guid": "1075f564-358b-432e-bd8e-1943de7309d5",
        "_kg_hide-input": true,
        "_uuid": "928cb79258126d688cdb08cce482598916a0f295",
        "trusted": true
      },
      "cell_type": "code",
      "source": "trace1 = go.Scatter(\n    x=response1.total_day_supply_millions,\n    y=response1.total_drug_cost_millions,\n    mode='markers',\n    text=response1.state,\n    marker=dict(\n        color=list(reversed([i*11 for i in range(200,251)])),\n        size=response1.total_claim_count_millions,\n        line=dict(\n            width=2\n        ),\n    )\n)\ndata = [trace1]\n\nlayout = go.Layout(\n    title='Total claim count VS Total Drug Cost VS Total Day Supply for every state',\n    xaxis=dict(\n        title='Total Day Supply',\n        gridcolor='rgb(255, 255, 255)',\n        zerolinewidth=1,\n        ticklen=5,\n        gridwidth=2,\n    ),\n    yaxis=dict(\n        title='Total Drug Cost',\n        gridcolor='rgb(255, 255, 255)',\n        zerolinewidth=1,\n        ticklen=5,\n        gridwidth=2,\n    ),\n    paper_bgcolor='rgb(243, 243, 243)',\n    plot_bgcolor='rgb(243, 243, 243)',\n)\n\nfig = go.Figure(data=data, layout=layout)\niplot(fig, filename='scatter-colorscale')",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "_cell_guid": "95f877ed-f98e-442c-89f5-8bdeccc00692",
        "_uuid": "f98fa78590f98517354921d3bb8f131a1b5d0e8b"
      },
      "cell_type": "markdown",
      "source": "**Inference**  \n> California is the state having highest total day supply, highest total drug cost and highest claim count.  \n> Other states having more claim counts include Florida, Texas, and New York \n\n### 5.2 Which Drugs have been most prescribed in US"
    },
    {
      "metadata": {
        "_cell_guid": "7ba82c6a-ec48-47b2-b482-13c65f0e0d6f",
        "_kg_hide-input": true,
        "_uuid": "9bca47060db562552619f002afe6373e1972fb81",
        "trusted": true
      },
      "cell_type": "code",
      "source": "# state wise count of claims \n\n## Which providers have highest claims \n\nquery1 = \"\"\"SELECT\n  generic_name AS drug_name,\n  ROUND(SUM(total_claim_count) / 1e6) AS total_claim_count_millions,\n  ROUND(SUM(total_day_supply) / 1e6) AS total_day_supply_millions,\n  ROUND(SUM(total_drug_cost) / 1e6) AS total_drug_cost_millions\nFROM\n  `bigquery-public-data.cms_medicare.part_d_prescriber_2014`\nGROUP BY\n  drug_name\nORDER BY\n  total_claim_count_millions DESC;\n        \"\"\"\nresponse1 = medicare.query_to_pandas_safe(query1)\ntempdf = response1.head(50)\n\ntrace1 = go.Bar(\n    x=tempdf.drug_name,\n    y=tempdf.total_claim_count_millions,\n    name='Total Claims'\n)\ntrace2 = go.Bar(\n    x=tempdf.drug_name,\n    y=tempdf.total_day_supply_millions,\n    name='Total Day Supply'\n)\ntrace3 = go.Bar(\n    x=tempdf.drug_name,\n    y=tempdf.total_drug_cost_millions,\n    name='Total Drug Cost'\n)\n\ndata = [trace1, trace2, trace3]\nlayout = go.Layout(\n    barmode='group'\n    , margin=dict(b=200)\n)\n\nfig = go.Figure(data=data, layout=layout)\niplot(fig, filename='grouped-bar')",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "_cell_guid": "5bf66de6-1b81-46dc-b455-9523a5c2f07f",
        "_uuid": "58a7a9d58a7f57f5090e4ae4e1e8fa1b32e28189"
      },
      "cell_type": "markdown",
      "source": "**Inference**  \n\n![](https://img.medscapestatic.com/pi/features/drugdirectory/octupdate/APP06470.jpg)\n\n> - Levothyroxine Sodium and LISINOPRIL are the drugs which are associated with the highest number of claims  as well as highest number of supply  \n> - Insulin Glargine, Hum.Rec.An is the drug which is associated with the highest Total Drug Cost  \n> - Drugs having more Total Supply than Drug Costs are AMLODIPINE BESYLATE and SIMVASTATIN  \n> - Drugs having more Total Cost than Supply are Rosuvastatin Calcium, and Esomeprazole magnesium\n\n### 5.3 What about Nursing Specialities associated with highest claims"
    },
    {
      "metadata": {
        "_cell_guid": "a36f0be8-b28a-421f-92d7-2a01770a64f6",
        "_uuid": "946cd880d2dd646f2166510aeb8e974d207d9578",
        "_kg_hide-input": true,
        "_kg_hide-output": true,
        "trusted": true
      },
      "cell_type": "code",
      "source": "query1 = \"\"\"SELECT\n  specialty_description AS specialty_description,\n  ROUND(SUM(total_claim_count) / 1e6) AS total_claim_count_millions,\n  ROUND(SUM(total_day_supply) / 1e6) AS total_day_supply_millions,\n  ROUND(SUM(total_drug_cost) / 1e6) AS total_drug_cost_millions\nFROM\n  `bigquery-public-data.cms_medicare.part_d_prescriber_2014`\nGROUP BY\n  specialty_description\nORDER BY\n  total_claim_count_millions DESC;\n        \"\"\"\nresponse1 = medicare.query_to_pandas_safe(query1)\ntempdf = response1.head(40)\ntempdf['specialty_description1'] = tempdf['specialty_description'].apply(lambda x: x.replace(\" \",\"_\"))",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "_cell_guid": "3c8eddb0-99ca-407b-ae5d-ccdaaa80c70c",
        "_kg_hide-input": true,
        "_uuid": "7c06b11970066f31b2b2777184019e03f9958c6a",
        "trusted": true
      },
      "cell_type": "code",
      "source": "txt = \" \".join(tempdf.specialty_description1)\nwordcloud = WordCloud(background_color='white', max_font_size=50, width=600, height=300).generate(txt)\nplt.figure(figsize=(16,8))\nplt.imshow(wordcloud)\nplt.title(\"Top Speciality Services\", fontsize=15)\nplt.axis(\"off\")\nplt.show() ",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "_cell_guid": "1a0f906c-4795-4a3b-b652-aa6abeecc59a",
        "_kg_hide-input": true,
        "_uuid": "7ef823b9b2ad4434178a4e8593ef42f80c8a335e",
        "trusted": true
      },
      "cell_type": "code",
      "source": "trace1 = go.Bar(\n    x=tempdf.specialty_description,\n    y=tempdf.total_claim_count_millions,\n    name='Total Claims'\n)\ntrace2 = go.Bar(\n    x=tempdf.specialty_description,\n    y=tempdf.total_day_supply_millions,\n    name='Total Day Supply'\n)\ntrace3 = go.Bar(\n    x=tempdf.specialty_description,\n    y=tempdf.total_drug_cost_millions,\n    name='Total Drug Cost'\n)\n\ndata = [trace1, trace2, trace3]\nlayout = go.Layout(\n    barmode='stack'\n    , margin=dict(b=200)\n)\n\nfig = go.Figure(data=data, layout=layout)\niplot(fig, filename='grouped-bar')",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "_cell_guid": "70eeb4f2-0c1b-494e-8aa4-4d5a9260aff3",
        "_uuid": "63d5e7143c2f6a832a61de52de4df70e6c443eac"
      },
      "cell_type": "markdown",
      "source": "**Inference**\n> Internal Medicine and Family Pracitve are associated with highest claims   \n> These specialities also have highest total drug cost. Other Specialities with similar stats include Nurse Practitioner, Cardiology and General Practice\n\n### 5.4 Which Drugs are most prescribed in each state "
    },
    {
      "metadata": {
        "_cell_guid": "f92bc955-77a5-4112-9c9a-8234a6804895",
        "_kg_hide-input": true,
        "_uuid": "f03067001927343abe6e88ed9f244948b0b6a93e",
        "trusted": true
      },
      "cell_type": "code",
      "source": "query1 = \"\"\"select A.state, B.drug_name, A.MaxClaimCount from (select state, MAX(total_claim_count_millions) as MaxClaimCount from (\nSELECT\n  nppes_provider_state AS state, generic_name AS drug_name, SUM(total_claim_count) AS total_claim_count_millions\nFROM\n  `bigquery-public-data.cms_medicare.part_d_prescriber_2014`\nGROUP BY state, drug_name\nORDER BY\n  total_claim_count_millions DESC) group by state) A INNER JOIN (SELECT\n  nppes_provider_state AS state,\n  generic_name AS drug_name,\n  SUM(total_claim_count) AS total_claim_count_millions\nFROM\n  `bigquery-public-data.cms_medicare.part_d_prescriber_2014`\nGROUP BY\n  state, drug_name\nORDER BY\n  total_claim_count_millions DESC) B ON A.MaxClaimCount = B.total_claim_count_millions;\n\"\"\"\nresponse1 = medicare.query_to_pandas_safe(query1)\n\nvisited = {}\nrows = []\nfor index, row in response1.iterrows():\n    stat = row['state']\n    if stat not in visited:\n        visited[stat] = 1\n        rows.append(row)\nnewDF = pd.DataFrame(rows).reset_index()\nnewDF[['state', 'drug_name', 'MaxClaimCount']].head(21)",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "_cell_guid": "9ab70538-6e97-4caf-b753-c7fae3deea18",
        "_kg_hide-input": true,
        "_uuid": "454aa54d59370b450aef81e69793fda29831832e",
        "trusted": true
      },
      "cell_type": "code",
      "source": "a = newDF['drug_name'].value_counts()\nlabels = a.index\nvalues = a.values\ncolors = ['lightblue','gray','#eee','#999', '#9f9f']\ntrace = go.Pie(labels=labels, values=values, hoverinfo='label+percent', \n               textinfo='value', name='Top Drugs Used',\n               marker=dict(colors=colors))\nlayout = dict(title = 'Top Drugs in Each State',\n              xaxis= dict(title= 'Drug Name', ticklen= 5,zeroline= False),\n              width=800\n             )\nfig = dict(data = [trace], layout = layout)\niplot(fig)",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "_uuid": "8b55e82b815866f2447775a2bb9e4833ee83bdb3"
      },
      "cell_type": "markdown",
      "source": "### 5.5 Most Common Provider Types in US "
    },
    {
      "metadata": {
        "_kg_hide-input": true,
        "trusted": true,
        "_uuid": "3c0dbdca4cd38d6818301012110380268e80a6d2"
      },
      "cell_type": "code",
      "source": "query1 = \"\"\"SELECT provider_type, COUNT(provider_type) as count FROM\n  `bigquery-public-data.cms_medicare.referring_durable_medical_equip_2014`\n  GROUP BY provider_type ORDER BY count DESC;\"\"\"\nresponse1 = medicare.query_to_pandas_safe(query1)\n\ntrace1 = go.Bar(x=list(response1.head(30)['provider_type']),\n    y=list(response1.head(30)['count']),\n    name='Popular Provider Types ',\n    marker=dict(color='#ff23de'), opacity=0.5,\n)\n\ndata = [trace1]\nlayout = go.Layout(\n    barmode='group',\n    title='Most Common Provider Types in US',\n    legend=dict(orientation=\"h\")\n)\n\nfig = go.Figure(data=data, layout=layout)\niplot(fig)",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "trusted": true,
        "_uuid": "8760b4fa09380444c2235ccd8d2eaee13a98bd35"
      },
      "cell_type": "code",
      "source": "# query1 = \"\"\"SELECT provider_type, COUNT(provider_type) as count FROM\n#   `bigquery-public-data.cms_medicare.referring_durable_medical_equip_2014`\n#   GROUP BY provider_type ORDER BY count DESC;\"\"\"\n# response1 = medicare.query_to_pandas_safe(query1)\n\n# trace1 = go.Bar(x=list(response1.head(30)['provider_type']),\n#     y=list(response1.head(30)['count']),\n#     name='Popular Provider Types ',\n#     marker=dict(color='#ff23de'), opacity=0.5,\n# )\n\n# data = [trace1]\n# layout = go.Layout(\n#     barmode='group',\n#     title='Most Common Provider Types in US',\n#     legend=dict(orientation=\"h\")\n# )\n\n# fig = go.Figure(data=data, layout=layout)\n# iplot(fig)",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "_cell_guid": "46b2b45a-86a3-43c7-8797-501cde19354a",
        "_uuid": "e24d44e308f0a7cfa18634384192e1d2558e7d15"
      },
      "cell_type": "markdown",
      "source": "## 6. Clustering Nursing Facilities having similar behaviours \n\nIn this section, we will try to cluster the nursing facilities based on the 29 key features about them. I have choosen following features based on the amount of missing values and healthcare domain importance. \n\n **features_used_for_clustering** = [  'total_stays', 'distinct_beneficiaries_per_provider',\n   'average_length_of_stays_days', 'total_snf_charge_amount',\n   'total_snf_medicare_allowed_amount',\n   'total_snf_medicare_payment_amount',\n   'total_snf_medicare_standard_payment_amount', 'average_age',\n   'male_beneficiaries', 'female_beneficiaries', 'nondual_beneficiaries',\n   'dual_beneficiaries', 'white_beneficiaries', 'black_beneficiaries'\n   , 'average_hcc_score',\n   'percent_of_beneficiaries_with_atrial_fibrillation',\n   'percent_of_beneficiaries_with_asthma',\n   'percent_of_beneficiaries_with_cancer',\n   'percent_of_beneficiaries_with_chf',\n   'percent_of_beneficiaries_with_chronic_kidney_disease',\n   'percent_of_beneficiaries_with_copd',\n   'percent_of_beneficiaries_with_depression',\n   'percent_of_beneficiaries_with_diabetes',\n   'percent_of_beneficiaries_with_hyperlipidemia',\n   'percent_of_beneficiaries_with_ihd',\n   'percent_of_beneficiaries_with_osteoporosis',\n   'percent_of_beneficiaries_with_ra_oa',\n   'percent_of_beneficiaries_with_schizophrenia',\n   'percent_of_beneficiaries_with_stroke']\n   \n### 6.1 K-Means Clustering \n\nBy hit-and-trial and running elbow method iteratively, I have choosen the number of clusters to be equal to 3. ie. every nursing facility will be classified to one of the three clusters. This will help to identify the Nursing Facilities having similar behaviours / characteristics. Before Running Clustering I have applied data Preprocessing which includes Removing textual columns and dropping the missing Values\n\nReference - https://en.wikipedia.org/wiki/K-means_clustering"
    },
    {
      "metadata": {
        "_cell_guid": "5a03ce9b-c579-4d0d-ae32-772c1cd3a3a9",
        "_uuid": "76c2bb35295890f1c50ddf567c81cb5e02ea0e68",
        "collapsed": true,
        "trusted": true
      },
      "cell_type": "code",
      "source": "query1 = \"\"\"SELECT provider_id, facility_name, city, state, total_stays, distinct_beneficiaries_per_provider,\n       average_length_of_stays_days, total_snf_charge_amount,\n       total_snf_medicare_allowed_amount,\n       total_snf_medicare_payment_amount,\n       total_snf_medicare_standard_payment_amount, average_age,\n       male_beneficiaries, female_beneficiaries, nondual_beneficiaries,\n       dual_beneficiaries, white_beneficiaries, black_beneficiaries, average_hcc_score,\n       percent_of_beneficiaries_with_atrial_fibrillation,\n       percent_of_beneficiaries_with_asthma,\n       percent_of_beneficiaries_with_cancer,\n       percent_of_beneficiaries_with_chf,\n       percent_of_beneficiaries_with_chronic_kidney_disease,\n       percent_of_beneficiaries_with_copd,\n       percent_of_beneficiaries_with_depression,\n       percent_of_beneficiaries_with_diabetes,\n       percent_of_beneficiaries_with_hyperlipidemia,\n       percent_of_beneficiaries_with_ihd,\n       percent_of_beneficiaries_with_osteoporosis,\n       percent_of_beneficiaries_with_ra_oa,\n       percent_of_beneficiaries_with_schizophrenia,\n       percent_of_beneficiaries_with_stroke FROM\n  `bigquery-public-data.cms_medicare.nursing_facilities_2014`;\"\"\"\nresponse1 = medicare.query_to_pandas_safe(query1)\n\n# preprocessing the data before applying clustering \nX = response1\nX.index = X.provider_id\nX = X.drop(['provider_id', 'facility_name', 'city', 'state'], axis=1)\nX = X.dropna()\n\n# apply k-means clustering\nfrom sklearn.cluster import KMeans \nimport numpy as np \nkmeans = KMeans(n_clusters=4,init='k-means++',max_iter=300,n_init=10,random_state=0) \nX['cluster'] = kmeans.fit_predict(X)",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "_cell_guid": "d4d83845-8c8b-4c24-8f37-a9171da83b16",
        "_uuid": "38ee157b4bd3df0ecb46ed5756cc623e004cf836"
      },
      "cell_type": "markdown",
      "source": "### 6.2 Exploring the Clusters of Nursing Facilities \n\nLet us now explore the clusters / groups of nursing facilities. "
    },
    {
      "metadata": {
        "_cell_guid": "b74035eb-ca42-46b4-b398-23ce88357a09",
        "_kg_hide-input": true,
        "_uuid": "3c6881db03c75308b87e497b0d87c3b7783f4b25",
        "trusted": true
      },
      "cell_type": "code",
      "source": "print (\"Group A Cluster Elements: \")\ncluster_elements = list(X[X['cluster'] == 0].index[:5])\ntemp = response1.loc[response1['provider_id'].isin(cluster_elements)]\nj = 1\nfor i, row in temp[['facility_name', 'city', 'state']].iterrows():\n    print (j, \"\\t \"+\", \".join(list(row)).title())\n    j+= 1\nprint ()\n\nprint (\"Group B Cluster Elements: \")\ncluster_elements = list(X[X['cluster'] == 1].index[:5])\ntemp = response1.loc[response1['provider_id'].isin(cluster_elements)]\nj = 1\nfor i, row in temp[['facility_name', 'city', 'state']].iterrows():\n    print (j, \"\\t \"+\", \".join(list(row)).title())\n    j+= 1\nprint ()    \n\nprint (\"Group C Cluster Elements: \")\ncluster_elements = list(X[X['cluster'] == 2].index[:5])\ntemp = response1.loc[response1['provider_id'].isin(cluster_elements)]\nj = 1\nfor i, row in temp[['facility_name', 'city', 'state']].iterrows():\n    print (j, \"\\t \"+\", \".join(list(row)).title())\n    j+= 1\nprint ()\n\nprint (\"Group D Cluster Elements: \")\ncluster_elements = list(X[X['cluster'] == 3].index[:5])\ntemp = response1.loc[response1['provider_id'].isin(cluster_elements)]\nj = 1\nfor i, row in temp[['facility_name', 'city', 'state']].iterrows():\n    print (j, \"\\t \"+\", \".join(list(row)).title())\n    j+= 1",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "_cell_guid": "ade12396-6b8f-4a89-8fc1-54fe3392a9ef",
        "_uuid": "7fc7ef67d398932fdfa61176b27636e7f4a71eef"
      },
      "cell_type": "markdown",
      "source": "### 6.3 Mean Value of Key Features of Groups "
    },
    {
      "metadata": {
        "_cell_guid": "b3e6a0c0-740a-43bf-a992-e1e209d41d0f",
        "_kg_hide-input": true,
        "_uuid": "4b8d6813961b2a829f1ffc06b4137bcda9dd77ec",
        "trusted": true
      },
      "cell_type": "code",
      "source": "def plot_cluster_variables(col):\n    mean_score1 =  (np.mean(X[X['cluster'] == 0][col]))\n    mean_score2 = (np.mean(X[X['cluster'] == 1][col]))\n    mean_score3 = (np.mean(X[X['cluster'] == 2][col]))\n    mean_score4 = (np.mean(X[X['cluster'] == 3][col]))\n\n    trace1 = go.Bar(\n        y = ['Group A', 'Group B', 'Group C', 'Group D'],\n        x = [mean_score1, mean_score2, mean_score3, mean_score4],\n        orientation = 'h',\n        marker=dict(color=['#bdd4f9', '#a8f7c1', '#f9727b', '#f7eab7'])\n    )\n    return trace1\n\nf1 = plot_cluster_variables('average_hcc_score')\nf2 = plot_cluster_variables('total_snf_charge_amount')\nf3 = plot_cluster_variables('distinct_beneficiaries_per_provider')\nf4 = plot_cluster_variables('total_snf_medicare_payment_amount')\n\nfig = tools.make_subplots(rows=2, cols=2, print_grid=False, subplot_titles=('HCC_Risk_Score mean of Groups','Total_SNF_Charge mean of Groups', 'Distinct_Beneficiaries mean of Groups', 'Total_SNF_medicare_payment mean of Groups'))\nfig.append_trace(f1, 1, 1);\nfig.append_trace(f2, 1, 2);\nfig.append_trace(f3, 2, 1);\nfig.append_trace(f4, 2, 2);\n\nfig['layout'].update(height=600, width=800, showlegend=False);\niplot(fig, filename='simple-subplot');",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "_cell_guid": "4e86f927-d49f-4e7a-a659-bccee4fc77ee",
        "_uuid": "67a122cc007c536a058b11b0ec06ef507c354df9"
      },
      "cell_type": "markdown",
      "source": "### 6.4 Distribution of Key Features of the groups\n\n#### White Beneficiaries"
    },
    {
      "metadata": {
        "_cell_guid": "d1775399-0260-4691-9813-081979c82061",
        "_kg_hide-input": true,
        "_uuid": "552ab9b37b72c9a409c9d679f5fa70394f45a91c",
        "trusted": true
      },
      "cell_type": "code",
      "source": "def generate_hist_plots(col):\n    c1 = X[X['cluster'] == 0]\n    trace1 = go.Histogram(x = c1[col]) \n\n    c2 = X[X['cluster'] == 1]\n    trace2 = go.Histogram(x = c2[col]) \n\n    c3 = X[X['cluster'] == 2]\n    trace3 = go.Histogram(x = c3[col]) \n\n    c4 = X[X['cluster'] == 3]\n    trace4 = go.Histogram(x = c4[col]) \n\n    fig = tools.make_subplots(rows=2, cols=2, print_grid=False, subplot_titles=('Group A ' + col + ' distribution','Group B ' + col+ ' distribution','Group C ' + col+ ' distribution','Group D ' + col+ ' distribution'))\n    fig.append_trace(trace1, 1, 1);\n    fig.append_trace(trace2, 1, 2);\n    fig.append_trace(trace3, 2, 1);\n    fig.append_trace(trace4, 2, 2);\n    fig['layout'].update(height=600, showlegend=False);\n    iplot(fig, filename='simple-subplot');\n\ncol = 'white_beneficiaries'\ngenerate_hist_plots(col)",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "_cell_guid": "fe4ea2b6-647a-446c-a5ef-4b08569f500f",
        "_uuid": "8c2a9a2a973900198a40e5f5f393fbb5d8e882b5"
      },
      "cell_type": "markdown",
      "source": "#### Black Beneficiaries"
    },
    {
      "metadata": {
        "_cell_guid": "c3409713-029f-4e5c-a991-cd03b051aed5",
        "_kg_hide-input": true,
        "_uuid": "aebe92124a82898636146b3d8bb981e532872989",
        "trusted": true
      },
      "cell_type": "code",
      "source": "col = 'black_beneficiaries'\ngenerate_hist_plots(col)",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "_cell_guid": "baa557e8-a310-4187-a23d-d5f9a2d16c43",
        "_uuid": "e9c7c8d9a67ed979026a216230af1be2989398b2"
      },
      "cell_type": "markdown",
      "source": "#### Distinct Beneficiaries Per Provider "
    },
    {
      "metadata": {
        "_cell_guid": "8abcd674-678a-4085-9be5-eb835dde8411",
        "_kg_hide-input": true,
        "collapsed": true,
        "_uuid": "40e7d46c6938fead5dba01d02d6bfbe7cc0f7a22",
        "trusted": false
      },
      "cell_type": "code",
      "source": "col = 'distinct_beneficiaries_per_provider'\ngenerate_hist_plots(col)",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "_cell_guid": "73c511ea-4246-4a81-93fc-f86df47b5329",
        "_uuid": "0101aa3779dc570e3a8ffe84aac47853a5ea519d"
      },
      "cell_type": "markdown",
      "source": "#### Total Stays"
    },
    {
      "metadata": {
        "_cell_guid": "5682da42-53fe-497d-bca8-2caba2d52e3f",
        "_kg_hide-input": true,
        "collapsed": true,
        "_uuid": "065e5c3f1c2278906f75e00c00410086d607c821",
        "trusted": false
      },
      "cell_type": "code",
      "source": "col = 'total_stays'\ngenerate_hist_plots(col)",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "_cell_guid": "6b42bbe6-a607-4f05-88ca-026576c9b713",
        "_uuid": "f4fc9ef95033d72184b7c3dc627510351df72aa5",
        "collapsed": true
      },
      "cell_type": "markdown",
      "source": "### 6.5 Inference about Different Groups\n\n**Characteristics of Nursing Facilities in Group A**    \n> - This group contains one of the best performing nursing facilities having low HCC risk scores, medicare payments etc than other groups. \n> - This group consists of nursing facilities having the HCC score is the LOWEST (mean = 2.25), which indicates that these Nursing Facilities might have performed / served better to maintain a healthy population\n> - Average value of Total Skilled Nursing Facility Charge in this group is also lowest (about 1.54 Million), Medicare Payment's average value is equal to 962K with the mean value of distinct beneficiaries equal to 79. All of these values are lowest when compared to values for Nursing Facilities in Group B, C, and D. \n> - The White Beneficiary distribution of this group is scattered over to lower side with the mean of the normal curve equal to about 50. While the Black beneficiary distribution of this group contains very less data. Does this indicate that the nursing facilities in this group (almost) always serve to White Population? Maybe Yes ! \n> - Similar distribution behaviour is shown by the variables - total stays and distinct beneficiaries per providers\n\n**Characteristics of Nursing Facilities in Group B**    \n> - Group B is one of those group in which the HCC risk value is on the higher side (average = 2.40) \n> - The White population beneficiaries of this group are scattered normally pivoted at 220-239 while black population beneficiaries is much higher than group A but it still on the lower side \n\n**Characteristics of Nursing Facilities in Group C**    \n> - Group C is the one in which mean HCC value is not that higher but mean value of Total Skilled Nursing Facility Charge (9.1 M), Medicare Payment (16.1 M), and the number of distinct beneficiaries (580) is the highest among any other groups. \n> - There is less variability in the number of black beneficiaries  and it follows a pretty standard set of values \n\n**Characteristics of Nursing Facilities in Group D**    \n> - In Group D, the average HCC score of Nursing Facilities is the highest than any other group equal to 2.45  \n\n### Thanks for Viewing the Kernel"
    }
  ],
  "metadata": {
    "kernelspec": {
      "display_name": "Python 3",
      "language": "python",
      "name": "python3"
    },
    "language_info": {
      "name": "python",
      "version": "3.6.6",
      "mimetype": "text/x-python",
      "codemirror_mode": {
        "name": "ipython",
        "version": 3
      },
      "pygments_lexer": "ipython3",
      "nbconvert_exporter": "python",
      "file_extension": ".py"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 1
}