Transforming Data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Transforming Data\n",
    "There are 100 values of DRG Definition. Construct 100 DRG Charges features, one for\n",
    "each unique value of DRG Definition. The feature should record the Average Covered\n",
    "Charges for the specified DRG category. Then construct a transformed version of the data\n",
    "that only includes the provider id, provider state, and the 100 new DRG Charges features.\n",
    "For example, the data should look like the format in the table below. Make sure to include\n",
    "missing values for any provider that doesn’t have a charge for a specific DRG."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.read_csv('data.csv')\n",
    "df[' Average Covered Charges '] = df[' Average Covered Charges '].str[1:].astype(float)\n",
    "pv = df.pivot(index=['Provider Id', 'Provider State'], \n",
    "              values=' Average Covered Charges ', \n",
    "              columns='DRG Definition')\n",
    "pv = pv.reset_index()\n",
    "pv.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Correlation\n",
    "\n",
    "1. Find the Highest and lowest Correlations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#pv = pv.fillna(value=0.0)\n",
    "\n",
    "def get_redundant_pairs(df):\n",
    "    pairs_to_drop = set()\n",
    "    cols = df.columns\n",
    "    for i in range(0, df.shape[1]):\n",
    "        for j in range(0, i+1):\n",
    "            pairs_to_drop.add((cols[i], cols[j]))\n",
    "    return pairs_to_drop\n",
    "\n",
    "def get_top_abs_correlations(df, n=2):\n",
    "    au_corr = df.corr().abs().unstack()\n",
    "    labels_to_drop = get_redundant_pairs(df)\n",
    "    au_corr = au_corr.drop(labels=labels_to_drop).sort_values(ascending=False)\n",
    "    return au_corr[0:n]\n",
    "\n",
    "def get_bottom_abs_correlations(df, n=2):\n",
    "    au_corr = df.corr().abs().unstack()\n",
    "    labels_to_drop = get_redundant_pairs(df)\n",
    "    au_corr = au_corr.drop(labels=labels_to_drop).sort_values(ascending=True)\n",
    "    return au_corr[0:n]\n",
    "\n",
    "print(get_top_abs_correlations(pv))\n",
    "print(get_bottom_abs_correlations(pv))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Scatterplots\n",
    "\n",
    "2. Plot a scatterplot to show their relationship. </br>\n",
    "Make sure to label both axis of the plot with the feature names. </br>\n",
    "Discuss whether the observed relationsare interesting or expected, given the DRG category names. </br>\n",
    "(This will result in 4\n",
    "scatter plots total.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import plotly.express as px\n",
    "\n",
    "fig = px.scatter(pv, \n",
    "                 x=\"194 - SIMPLE PNEUMONIA & PLEURISY W CC\", \n",
    "                 y=\"690 - KIDNEY & URINARY TRACT INFECTIONS W/O MCC\")\n",
    "fig.show()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fig = px.scatter(pv, \n",
    "                 x=\"392 - ESOPHAGITIS, GASTROENT & MISC DIGEST DISORDERS W/O MCC\", \n",
    "                 y=\"690 - KIDNEY & URINARY TRACT INFECTIONS W/O MCC\")\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fig = px.scatter(pv, \n",
    "                 x=\"460 - SPINAL FUSION EXCEPT CERVICAL W/O MCC\", \n",
    "                 y=\"885 - PSYCHOSES\")\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fig = px.scatter(pv, \n",
    "                 x=\"473 - CERVICAL SPINAL FUSION W/O CC/MCC\", \n",
    "                 y=\"885 - PSYCHOSES\")\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pv['Provider State'].unique()\n",
    "# CA, TX, NY, FL, WA, KY"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pv = pv[pv['Provider State'].isin(['CA', 'TX', 'NY', 'FL', 'WA', 'KY'])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "group = pv.groupby('Provider State').mean().reset_index()\n",
    "group"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import plotly.express as px\n",
    "\n",
    "fig = px.box(pv, x=\"Provider State\", y=\"064 - INTRACRANIAL HEMORRHAGE OR CEREBRAL INFARCTION W MCC\", points='all')\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fig = px.box(pv, x=\"Provider State\", y=\"853 - INFECTIOUS & PARASITIC DISEASES W O.R. PROCEDURE W MCC\", points='all')\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fig = px.box(pv, x=\"Provider State\", y=\"917 - POISONING & TOXIC EFFECTS OF DRUGS W MCC\", points='all')\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Based on the three box plots, identify the pair of states that you think have the\n",
    "most significant differences in their charges for a single DRG category. Explicitly\n",
    "state your hypothesis in terms of H0 and H1."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p>H1 California and Kentucky have significant differences in 917 Charges</p>\n",
    "<p>H0 California and Kentucky have no significant differences in 917 Charges</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from scipy.stats import ttest_ind\n",
    "\n",
    "cat1 = pv[pv['Provider State'] == 'CA'].dropna(subset=['917 - POISONING & TOXIC EFFECTS OF DRUGS W MCC', \"853 - INFECTIOUS & PARASITIC DISEASES W O.R. PROCEDURE W MCC\", \"064 - INTRACRANIAL HEMORRHAGE OR CEREBRAL INFARCTION W MCC\"]).head(16)\n",
    "print(len(cat1.index))\n",
    "cat2 = pv[pv['Provider State'] == 'KY'].dropna(subset=['917 - POISONING & TOXIC EFFECTS OF DRUGS W MCC', \"853 - INFECTIOUS & PARASITIC DISEASES W O.R. PROCEDURE W MCC\", \"064 - INTRACRANIAL HEMORRHAGE OR CEREBRAL INFARCTION W MCC\"])\n",
    "print(len(cat2.index))\n",
    "ttest_ind(cat1[['917 - POISONING & TOXIC EFFECTS OF DRUGS W MCC', \"853 - INFECTIOUS & PARASITIC DISEASES W O.R. PROCEDURE W MCC\", \"064 - INTRACRANIAL HEMORRHAGE OR CEREBRAL INFARCTION W MCC\"]], cat2[['917 - POISONING & TOXIC EFFECTS OF DRUGS W MCC', \"853 - INFECTIOUS & PARASITIC DISEASES W O.R. PROCEDURE W MCC\", \"064 - INTRACRANIAL HEMORRHAGE OR CEREBRAL INFARCTION W MCC\"]])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from scipy.stats import ttest_rel\n",
    "\n",
    "\n",
    "cat1 = pv[pv['Provider State'] == 'CA'].dropna(subset=['917 - POISONING & TOXIC EFFECTS OF DRUGS W MCC', \"853 - INFECTIOUS & PARASITIC DISEASES W O.R. PROCEDURE W MCC\", \"064 - INTRACRANIAL HEMORRHAGE OR CEREBRAL INFARCTION W MCC\"]).head(16)\n",
    "print(len(cat1.index))\n",
    "cat2 = pv[pv['Provider State'] == 'KY'].dropna(subset=['917 - POISONING & TOXIC EFFECTS OF DRUGS W MCC', \"853 - INFECTIOUS & PARASITIC DISEASES W O.R. PROCEDURE W MCC\", \"064 - INTRACRANIAL HEMORRHAGE OR CEREBRAL INFARCTION W MCC\"])\n",
    "print(len(cat2.index))\n",
    "ttest_rel(cat1[['917 - POISONING & TOXIC EFFECTS OF DRUGS W MCC', \"853 - INFECTIOUS & PARASITIC DISEASES W O.R. PROCEDURE W MCC\", \"064 - INTRACRANIAL HEMORRHAGE OR CEREBRAL INFARCTION W MCC\"]], cat2[['917 - POISONING & TOXIC EFFECTS OF DRUGS W MCC', \"853 - INFECTIOUS & PARASITIC DISEASES W O.R. PROCEDURE W MCC\", \"064 - INTRACRANIAL HEMORRHAGE OR CEREBRAL INFARCTION W MCC\"]])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from scipy.stats import ttest_ind\n",
    "from scipy.stats import ttest_1samp\n",
    "\n",
    "cat1 = pv[pv['Provider State'] == 'CA'].dropna(subset=['917 - POISONING & TOXIC EFFECTS OF DRUGS W MCC'])\n",
    "cat2 = pv[pv['Provider State'] == 'KY'].dropna(subset=['917 - POISONING & TOXIC EFFECTS OF DRUGS W MCC'])\n",
    "\n",
    "ttest_1samp(cat1['917 - POISONING & TOXIC EFFECTS OF DRUGS W MCC'], float(cat2['917 - POISONING & TOXIC EFFECTS OF DRUGS W MCC'].mean()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "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.8.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}