scrape_mdc_data_from_cms.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# load modules\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# read in a list of urls from cms data source: \n",
    "# https://www.cms.gov/icd10m/version39.0-fullcode-cms/fullcode_cms/P0378.html\n",
    "cms_urls = pd.read_csv(\"cms_mdc_urls.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# scrape data from each url\n",
    "df = pd.DataFrame()\n",
    "for url in cms_urls['url'].unique():\n",
    "    # list all tables from the url\n",
    "    tables_list = pd.read_html(url)\n",
    "    # the second table contains the reference table\n",
    "    df_table = tables_list[2]\n",
    "    # concat 74 tables into 1 df\n",
    "    df = pd.concat([df, df_table])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Index: 29598 entries, 0 to 264\n",
      "Data columns (total 11 columns):\n",
      " #   Column      Non-Null Count  Dtype  \n",
      "---  ------      --------------  -----  \n",
      " 0   DX          29576 non-null  object \n",
      " 1   MDC         29576 non-null  object \n",
      " 2   MS-DRG      29576 non-null  object \n",
      " 3   Unnamed: 3  0 non-null      float64\n",
      " 4   DX.1        29292 non-null  object \n",
      " 5   MDC.1       29292 non-null  object \n",
      " 6   MS-DRG.1    29292 non-null  object \n",
      " 7   Unnamed: 7  0 non-null      float64\n",
      " 8   DX.2        29295 non-null  object \n",
      " 9   MDC.2       29295 non-null  object \n",
      " 10  MS-DRG.2    29295 non-null  object \n",
      "dtypes: float64(2), object(9)\n",
      "memory usage: 2.7+ MB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Index: 29598 entries, 0 to 264\n",
      "Data columns (total 9 columns):\n",
      " #   Column    Non-Null Count  Dtype \n",
      "---  ------    --------------  ----- \n",
      " 0   DX        29576 non-null  object\n",
      " 1   MDC       29576 non-null  object\n",
      " 2   MS-DRG    29576 non-null  object\n",
      " 3   DX.1      29292 non-null  object\n",
      " 4   MDC.1     29292 non-null  object\n",
      " 5   MS-DRG.1  29292 non-null  object\n",
      " 6   DX.2      29295 non-null  object\n",
      " 7   MDC.2     29295 non-null  object\n",
      " 8   MS-DRG.2  29295 non-null  object\n",
      "dtypes: object(9)\n",
      "memory usage: 2.3+ MB\n"
     ]
    }
   ],
   "source": [
    "# select all object columns (dropping empty float64 columns)\n",
    "df = df.select_dtypes(include='object')\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# reshape to stack overflow columns\n",
    "cols0 = df[['DX', 'MDC', 'MS-DRG']]\n",
    "cols1 = df[['DX.1', 'MDC.1', 'MS-DRG.1']]\n",
    "cols1 = cols1.rename(columns={'DX.1': 'DX', 'MDC.1': 'MDC', 'MS-DRG.1': 'MS-DRG'})\n",
    "cols2 = df[['DX.2', 'MDC.2', 'MS-DRG.2']]\n",
    "cols2 = cols2.rename(columns={'DX.2': 'DX', 'MDC.2': 'MDC', 'MS-DRG.2': 'MS-DRG'})\n",
    "df = pd.concat([cols0, cols1, cols2])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop any duplicate rows\n",
    "df = df.drop_duplicates()\n",
    "# drop any empty rows\n",
    "df = df.dropna()\n",
    "# reorder for legibility\n",
    "df = df[['DX', 'MS-DRG', 'MDC']]\n",
    "# check final result\n",
    "df.head()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "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.11.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}