Provider Relief Fund data analysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "48ad0820",
   "metadata": {},
   "source": [
    "# Provider Relief Fund data analysis\n",
    "\n",
    "This notebook has a little Python code to work with the U.S. Department of Health and Human Services' COVID-19 [Provider Relief Fund (PRF) data](https://www.hrsa.gov/provider-relief/data).\n",
    "\n",
    "According to [the Pandemic Response Accountability Committee](https://www.pandemicoversight.gov/data-interactive-tools/data-download-center), the PRF represents\n",
    ">$50 billion in payments for general distribution to Medicare facilities and providers impacted by COVID-19, based on eligible providers' net reimbursements.\n",
    "\n",
    "[Here's a link to this dataset on the CDC's open data portal](https://data.cdc.gov/Administrative/HHS-Provider-Relief-Fund/kh8y-3es6) -- we'll be importing the URL from the Export > CSV link."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "cf03e47d",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "6c7e9f02",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('https://data.cdc.gov/api/views/kh8y-3es6/rows.csv?accessType=DOWNLOAD')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "9ce9189b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Provider Name</th>\n",
       "      <th>State</th>\n",
       "      <th>City</th>\n",
       "      <th>Payment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BRANDON ASTIN DMD LLC</td>\n",
       "      <td>AK</td>\n",
       "      <td>ANCHOR POINT</td>\n",
       "      <td>$113,026</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ELIZABETH WATNEY</td>\n",
       "      <td>AK</td>\n",
       "      <td>ANCHOR POINT</td>\n",
       "      <td>$724</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A HAND UP BEHAVIOR SERVICES</td>\n",
       "      <td>AK</td>\n",
       "      <td>ANCHORAGE</td>\n",
       "      <td>$1,191</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A JOINT EFFORT PHYSICAL THERAPY</td>\n",
       "      <td>AK</td>\n",
       "      <td>ANCHORAGE</td>\n",
       "      <td>$23,361</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AA PAIN CLINIC, INC.</td>\n",
       "      <td>AK</td>\n",
       "      <td>ANCHORAGE</td>\n",
       "      <td>$69,976</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     Provider Name State          City   Payment\n",
       "0            BRANDON ASTIN DMD LLC    AK  ANCHOR POINT  $113,026\n",
       "1                 ELIZABETH WATNEY    AK  ANCHOR POINT      $724\n",
       "2      A HAND UP BEHAVIOR SERVICES    AK     ANCHORAGE    $1,191\n",
       "3  A JOINT EFFORT PHYSICAL THERAPY    AK     ANCHORAGE   $23,361\n",
       "4             AA PAIN CLINIC, INC.    AK     ANCHORAGE   $69,976"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "ce77bb42",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 415123 entries, 0 to 415122\n",
      "Data columns (total 4 columns):\n",
      " #   Column         Non-Null Count   Dtype \n",
      "---  ------         --------------   ----- \n",
      " 0   Provider Name  415121 non-null  object\n",
      " 1   State          415123 non-null  object\n",
      " 2   City           415123 non-null  object\n",
      " 3   Payment        415123 non-null  object\n",
      "dtypes: object(4)\n",
      "memory usage: 12.7+ MB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8a5ece13",
   "metadata": {},
   "source": [
    "### Convert the currency values to numbers\n",
    "\n",
    "Strip out dollar signs and commas and coerce the values to integers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "57b9cc49",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Payment'] = df['Payment'].str.replace('[\\$,]', '', regex=True).astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "ea3e8257",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 415123 entries, 0 to 415122\n",
      "Data columns (total 4 columns):\n",
      " #   Column         Non-Null Count   Dtype \n",
      "---  ------         --------------   ----- \n",
      " 0   Provider Name  415121 non-null  object\n",
      " 1   State          415123 non-null  object\n",
      " 2   City           415123 non-null  object\n",
      " 3   Payment        415123 non-null  int64 \n",
      "dtypes: int64(1), object(3)\n",
      "memory usage: 12.7+ MB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2473570b",
   "metadata": {},
   "source": [
    "### Do a little sorting\n",
    "\n",
    "Finding the largest/smallest payments."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "94ccd0dd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Provider Name</th>\n",
       "      <th>State</th>\n",
       "      <th>City</th>\n",
       "      <th>Payment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>280992</th>\n",
       "      <td>NEW YORK CITY HEALTH HOSTITALS</td>\n",
       "      <td>NY</td>\n",
       "      <td>NEW YORK</td>\n",
       "      <td>1217422679</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>282137</th>\n",
       "      <td>THE NEW YORK PRESBYTERIAN HOSPITAL</td>\n",
       "      <td>NY</td>\n",
       "      <td>NEW YORK</td>\n",
       "      <td>741523422</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>261575</th>\n",
       "      <td>MONTEFIORE MEDICAL CENTER</td>\n",
       "      <td>NY</td>\n",
       "      <td>BRONX</td>\n",
       "      <td>476046376</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>282556</th>\n",
       "      <td>NYU LANGONE HOSPITALS</td>\n",
       "      <td>NY</td>\n",
       "      <td>NEW YORK CITY</td>\n",
       "      <td>435297143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>295611</th>\n",
       "      <td>THE CLEVELAND CLINIC FOUNDATION</td>\n",
       "      <td>OH</td>\n",
       "      <td>CLEVELAND</td>\n",
       "      <td>431032935</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>256588</th>\n",
       "      <td>DIGNITY HEALTH</td>\n",
       "      <td>NV</td>\n",
       "      <td>LAS VEGAS</td>\n",
       "      <td>430824536</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>290863</th>\n",
       "      <td>LONG ISLAND JEWISH MEDICAL CENTER</td>\n",
       "      <td>NY</td>\n",
       "      <td>WESTBURY</td>\n",
       "      <td>409079102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>241666</th>\n",
       "      <td>HMH HOSPITALS CORPORATION</td>\n",
       "      <td>NJ</td>\n",
       "      <td>HACKENSACK</td>\n",
       "      <td>401192239</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62105</th>\n",
       "      <td>STANFORD HEALTH CARE</td>\n",
       "      <td>CA</td>\n",
       "      <td>STANFORD</td>\n",
       "      <td>387915656</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56353</th>\n",
       "      <td>UNIVERSITY OF CALIFORNIA SAN FRANCISCO</td>\n",
       "      <td>CA</td>\n",
       "      <td>SAN FRANCISCO</td>\n",
       "      <td>383521496</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 Provider Name State           City  \\\n",
       "280992          NEW YORK CITY HEALTH HOSTITALS    NY       NEW YORK   \n",
       "282137      THE NEW YORK PRESBYTERIAN HOSPITAL    NY       NEW YORK   \n",
       "261575               MONTEFIORE MEDICAL CENTER    NY          BRONX   \n",
       "282556                   NYU LANGONE HOSPITALS    NY  NEW YORK CITY   \n",
       "295611         THE CLEVELAND CLINIC FOUNDATION    OH      CLEVELAND   \n",
       "256588                          DIGNITY HEALTH    NV      LAS VEGAS   \n",
       "290863       LONG ISLAND JEWISH MEDICAL CENTER    NY       WESTBURY   \n",
       "241666               HMH HOSPITALS CORPORATION    NJ     HACKENSACK   \n",
       "62105                     STANFORD HEALTH CARE    CA       STANFORD   \n",
       "56353   UNIVERSITY OF CALIFORNIA SAN FRANCISCO    CA  SAN FRANCISCO   \n",
       "\n",
       "           Payment  \n",
       "280992  1217422679  \n",
       "282137   741523422  \n",
       "261575   476046376  \n",
       "282556   435297143  \n",
       "295611   431032935  \n",
       "256588   430824536  \n",
       "290863   409079102  \n",
       "241666   401192239  \n",
       "62105    387915656  \n",
       "56353    383521496  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 10 largest\n",
    "df.sort_values('Payment', ascending=False).head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "01468da1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Provider Name</th>\n",
       "      <th>State</th>\n",
       "      <th>City</th>\n",
       "      <th>Payment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>282502</th>\n",
       "      <td>YUNA RAPOPORT</td>\n",
       "      <td>NY</td>\n",
       "      <td>NEW YORK</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>309680</th>\n",
       "      <td>EMERGE MEDICAL &amp; WELL SPA</td>\n",
       "      <td>OK</td>\n",
       "      <td>TULSA</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>265278</th>\n",
       "      <td>PLANET RX LLC</td>\n",
       "      <td>NY</td>\n",
       "      <td>BROOKLYN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96546</th>\n",
       "      <td>JANE O'BRIEN</td>\n",
       "      <td>FL</td>\n",
       "      <td>MIAMI</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>145116</th>\n",
       "      <td>CAREPOINT HEALTHCARE LLC</td>\n",
       "      <td>IL</td>\n",
       "      <td>SCHAUMBURG</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>366274</th>\n",
       "      <td>OUR FAMILY MEDICAL CLINIC</td>\n",
       "      <td>TX</td>\n",
       "      <td>GARLAND</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33853</th>\n",
       "      <td>INSTITUTE FOR PROGRESSIVE MEDICINE A PROFESSIO...</td>\n",
       "      <td>CA</td>\n",
       "      <td>IRVINE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>356790</th>\n",
       "      <td>DAISY ARCO</td>\n",
       "      <td>TX</td>\n",
       "      <td>BEAUMONT</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>369385</th>\n",
       "      <td>LA RX</td>\n",
       "      <td>TX</td>\n",
       "      <td>HOUSTON</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>288497</th>\n",
       "      <td>PRESCRIPTION CTR OF ST NY.</td>\n",
       "      <td>NY</td>\n",
       "      <td>STATEN ISLAND</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                            Provider Name State  \\\n",
       "282502                                      YUNA RAPOPORT    NY   \n",
       "309680                          EMERGE MEDICAL & WELL SPA    OK   \n",
       "265278                                      PLANET RX LLC    NY   \n",
       "96546                                        JANE O'BRIEN    FL   \n",
       "145116                           CAREPOINT HEALTHCARE LLC    IL   \n",
       "366274                          OUR FAMILY MEDICAL CLINIC    TX   \n",
       "33853   INSTITUTE FOR PROGRESSIVE MEDICINE A PROFESSIO...    CA   \n",
       "356790                                         DAISY ARCO    TX   \n",
       "369385                                              LA RX    TX   \n",
       "288497                         PRESCRIPTION CTR OF ST NY.    NY   \n",
       "\n",
       "                 City  Payment  \n",
       "282502       NEW YORK        0  \n",
       "309680          TULSA        1  \n",
       "265278       BROOKLYN        1  \n",
       "96546           MIAMI        1  \n",
       "145116     SCHAUMBURG        1  \n",
       "366274        GARLAND        1  \n",
       "33853          IRVINE        1  \n",
       "356790       BEAUMONT        1  \n",
       "369385        HOUSTON        1  \n",
       "288497  STATEN ISLAND        1  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 10 smallest\n",
    "df.sort_values('Payment').head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eeaf4582",
   "metadata": {},
   "source": [
    "### Drill down to find data in your state\n",
    "\n",
    "Filter to show data from one or more states."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "8438fafb",
   "metadata": {},
   "outputs": [],
   "source": [
    "states_of_interest = [\n",
    "    'IL',\n",
    "    'IN',\n",
    "    'MI',\n",
    "    'MN',\n",
    "    'OH',\n",
    "    'WI'\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "db3b4a19",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_filtered = df[df['State'].isin(states_of_interest)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "31e32baa",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Provider Name</th>\n",
       "      <th>State</th>\n",
       "      <th>City</th>\n",
       "      <th>Payment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>131269</th>\n",
       "      <td>WALTER D PEREZ MD SC</td>\n",
       "      <td>IL</td>\n",
       "      <td>ADDISO</td>\n",
       "      <td>22672</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>131270</th>\n",
       "      <td>1ST FAMILY DENTAL OF ADDISON PC</td>\n",
       "      <td>IL</td>\n",
       "      <td>ADDISON</td>\n",
       "      <td>26222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>131271</th>\n",
       "      <td>ACCESS DENTAL &amp; MEDICAL CENTER PC</td>\n",
       "      <td>IL</td>\n",
       "      <td>ADDISON</td>\n",
       "      <td>55358</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>131272</th>\n",
       "      <td>ADDISON FIRE PROTECTION DISTRICT</td>\n",
       "      <td>IL</td>\n",
       "      <td>ADDISON</td>\n",
       "      <td>26641</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>131273</th>\n",
       "      <td>ADDISON MEDICAL ASSOCIATES LTD</td>\n",
       "      <td>IL</td>\n",
       "      <td>ADDISON</td>\n",
       "      <td>13499</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            Provider Name State     City  Payment\n",
       "131269               WALTER D PEREZ MD SC    IL   ADDISO    22672\n",
       "131270    1ST FAMILY DENTAL OF ADDISON PC    IL  ADDISON    26222\n",
       "131271  ACCESS DENTAL & MEDICAL CENTER PC    IL  ADDISON    55358\n",
       "131272   ADDISON FIRE PROTECTION DISTRICT    IL  ADDISON    26641\n",
       "131273     ADDISON MEDICAL ASSOCIATES LTD    IL  ADDISON    13499"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_filtered.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "f7fbc52d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(60383, 4)"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_filtered.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "21f518b3",
   "metadata": {},
   "source": [
    "### Group and aggregate by state\n",
    "\n",
    "Group the data by state and compare the total payment amounts. Sort descending, while we're at it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "5040aa55",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Payment</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>State</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>NY</th>\n",
       "      <td>14108521929</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA</th>\n",
       "      <td>11399072842</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TX</th>\n",
       "      <td>8960558382</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PA</th>\n",
       "      <td>6027656300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IL</th>\n",
       "      <td>5939323146</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FL</th>\n",
       "      <td>5209899858</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OH</th>\n",
       "      <td>4893816118</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NJ</th>\n",
       "      <td>4793928920</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MA</th>\n",
       "      <td>3991846712</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MI</th>\n",
       "      <td>3885965815</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TN</th>\n",
       "      <td>3698154113</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WA</th>\n",
       "      <td>3270753654</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GA</th>\n",
       "      <td>3242074140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NC</th>\n",
       "      <td>3135119546</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MO</th>\n",
       "      <td>2452556955</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MD</th>\n",
       "      <td>2406997789</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IN</th>\n",
       "      <td>2397787743</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WI</th>\n",
       "      <td>2224225649</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LA</th>\n",
       "      <td>2170510563</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MN</th>\n",
       "      <td>2082687562</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VA</th>\n",
       "      <td>1953512670</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>KY</th>\n",
       "      <td>1915519511</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CT</th>\n",
       "      <td>1855982460</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AZ</th>\n",
       "      <td>1651497773</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CO</th>\n",
       "      <td>1639945310</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AL</th>\n",
       "      <td>1474545683</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OK</th>\n",
       "      <td>1378358376</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SC</th>\n",
       "      <td>1357424246</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IA</th>\n",
       "      <td>1255671082</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MS</th>\n",
       "      <td>1183645702</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>KS</th>\n",
       "      <td>1131125903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AR</th>\n",
       "      <td>1086264458</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OR</th>\n",
       "      <td>1082034839</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NV</th>\n",
       "      <td>926973789</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NE</th>\n",
       "      <td>905636662</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WV</th>\n",
       "      <td>759781158</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>UT</th>\n",
       "      <td>717579742</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ME</th>\n",
       "      <td>690037569</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NM</th>\n",
       "      <td>650038250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SD</th>\n",
       "      <td>645439415</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NH</th>\n",
       "      <td>586834534</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MT</th>\n",
       "      <td>462272758</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DE</th>\n",
       "      <td>452364131</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RI</th>\n",
       "      <td>443715303</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HI</th>\n",
       "      <td>443357740</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID</th>\n",
       "      <td>439756360</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DC</th>\n",
       "      <td>371939571</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ND</th>\n",
       "      <td>365657128</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VT</th>\n",
       "      <td>269501282</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AK</th>\n",
       "      <td>255798325</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WY</th>\n",
       "      <td>220696701</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PR</th>\n",
       "      <td>211524468</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GU</th>\n",
       "      <td>27918447</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VI</th>\n",
       "      <td>22561913</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FM</th>\n",
       "      <td>17945919</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MP</th>\n",
       "      <td>10075089</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AS</th>\n",
       "      <td>8103156</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PW</th>\n",
       "      <td>3290970</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>QC</th>\n",
       "      <td>48652</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MH</th>\n",
       "      <td>32365</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Payment\n",
       "State             \n",
       "NY     14108521929\n",
       "CA     11399072842\n",
       "TX      8960558382\n",
       "PA      6027656300\n",
       "IL      5939323146\n",
       "FL      5209899858\n",
       "OH      4893816118\n",
       "NJ      4793928920\n",
       "MA      3991846712\n",
       "MI      3885965815\n",
       "TN      3698154113\n",
       "WA      3270753654\n",
       "GA      3242074140\n",
       "NC      3135119546\n",
       "MO      2452556955\n",
       "MD      2406997789\n",
       "IN      2397787743\n",
       "WI      2224225649\n",
       "LA      2170510563\n",
       "MN      2082687562\n",
       "VA      1953512670\n",
       "KY      1915519511\n",
       "CT      1855982460\n",
       "AZ      1651497773\n",
       "CO      1639945310\n",
       "AL      1474545683\n",
       "OK      1378358376\n",
       "SC      1357424246\n",
       "IA      1255671082\n",
       "MS      1183645702\n",
       "KS      1131125903\n",
       "AR      1086264458\n",
       "OR      1082034839\n",
       "NV       926973789\n",
       "NE       905636662\n",
       "WV       759781158\n",
       "UT       717579742\n",
       "ME       690037569\n",
       "NM       650038250\n",
       "SD       645439415\n",
       "NH       586834534\n",
       "MT       462272758\n",
       "DE       452364131\n",
       "RI       443715303\n",
       "HI       443357740\n",
       "ID       439756360\n",
       "DC       371939571\n",
       "ND       365657128\n",
       "VT       269501282\n",
       "AK       255798325\n",
       "WY       220696701\n",
       "PR       211524468\n",
       "GU        27918447\n",
       "VI        22561913\n",
       "FM        17945919\n",
       "MP        10075089\n",
       "AS         8103156\n",
       "PW         3290970\n",
       "QC           48652\n",
       "MH           32365"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[['State', 'Payment']].groupby('State').sum().sort_values('Payment', ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "f388edfa",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Payment</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>State</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>FM</th>\n",
       "      <td>897295.950000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MP</th>\n",
       "      <td>671672.600000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SD</th>\n",
       "      <td>543299.170875</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TN</th>\n",
       "      <td>452484.291325</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DC</th>\n",
       "      <td>435526.429742</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IN</th>\n",
       "      <td>425667.982070</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NY</th>\n",
       "      <td>423666.614486</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WA</th>\n",
       "      <td>408180.912767</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WI</th>\n",
       "      <td>405584.545770</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>KY</th>\n",
       "      <td>399232.911838</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PA</th>\n",
       "      <td>377862.105065</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MO</th>\n",
       "      <td>376794.738823</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DE</th>\n",
       "      <td>372316.157202</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ND</th>\n",
       "      <td>371980.801628</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IL</th>\n",
       "      <td>370512.984779</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MS</th>\n",
       "      <td>369889.281875</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WV</th>\n",
       "      <td>366336.141755</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PW</th>\n",
       "      <td>365663.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OH</th>\n",
       "      <td>362478.047404</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MN</th>\n",
       "      <td>358281.018751</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MA</th>\n",
       "      <td>357276.175781</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ME</th>\n",
       "      <td>335458.225085</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LA</th>\n",
       "      <td>332696.284948</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CT</th>\n",
       "      <td>330069.795483</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IA</th>\n",
       "      <td>328365.868724</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NH</th>\n",
       "      <td>322082.620198</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NE</th>\n",
       "      <td>321946.911482</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NC</th>\n",
       "      <td>321715.705080</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AR</th>\n",
       "      <td>311786.583812</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NM</th>\n",
       "      <td>310428.963706</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GA</th>\n",
       "      <td>303934.952658</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OK</th>\n",
       "      <td>288842.911987</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>KS</th>\n",
       "      <td>286869.364190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SC</th>\n",
       "      <td>285473.027550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NJ</th>\n",
       "      <td>284895.044868</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GU</th>\n",
       "      <td>284882.112245</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MD</th>\n",
       "      <td>282610.988494</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RI</th>\n",
       "      <td>281366.710843</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MI</th>\n",
       "      <td>279124.106809</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TX</th>\n",
       "      <td>278235.006428</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NV</th>\n",
       "      <td>271282.935031</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AL</th>\n",
       "      <td>267661.223997</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MT</th>\n",
       "      <td>265064.654817</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WY</th>\n",
       "      <td>256028.655452</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>UT</th>\n",
       "      <td>254460.901418</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CO</th>\n",
       "      <td>251255.601348</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VT</th>\n",
       "      <td>230935.117395</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AK</th>\n",
       "      <td>229827.785265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA</th>\n",
       "      <td>224144.109682</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OR</th>\n",
       "      <td>222046.960599</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VA</th>\n",
       "      <td>219915.869639</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AZ</th>\n",
       "      <td>211324.091235</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID</th>\n",
       "      <td>185004.779133</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FL</th>\n",
       "      <td>182630.485435</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VI</th>\n",
       "      <td>177652.858268</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AS</th>\n",
       "      <td>168815.750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HI</th>\n",
       "      <td>163479.992625</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>QC</th>\n",
       "      <td>48652.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PR</th>\n",
       "      <td>32372.890725</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MH</th>\n",
       "      <td>8091.250000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             Payment\n",
       "State               \n",
       "FM     897295.950000\n",
       "MP     671672.600000\n",
       "SD     543299.170875\n",
       "TN     452484.291325\n",
       "DC     435526.429742\n",
       "IN     425667.982070\n",
       "NY     423666.614486\n",
       "WA     408180.912767\n",
       "WI     405584.545770\n",
       "KY     399232.911838\n",
       "PA     377862.105065\n",
       "MO     376794.738823\n",
       "DE     372316.157202\n",
       "ND     371980.801628\n",
       "IL     370512.984779\n",
       "MS     369889.281875\n",
       "WV     366336.141755\n",
       "PW     365663.333333\n",
       "OH     362478.047404\n",
       "MN     358281.018751\n",
       "MA     357276.175781\n",
       "ME     335458.225085\n",
       "LA     332696.284948\n",
       "CT     330069.795483\n",
       "IA     328365.868724\n",
       "NH     322082.620198\n",
       "NE     321946.911482\n",
       "NC     321715.705080\n",
       "AR     311786.583812\n",
       "NM     310428.963706\n",
       "GA     303934.952658\n",
       "OK     288842.911987\n",
       "KS     286869.364190\n",
       "SC     285473.027550\n",
       "NJ     284895.044868\n",
       "GU     284882.112245\n",
       "MD     282610.988494\n",
       "RI     281366.710843\n",
       "MI     279124.106809\n",
       "TX     278235.006428\n",
       "NV     271282.935031\n",
       "AL     267661.223997\n",
       "MT     265064.654817\n",
       "WY     256028.655452\n",
       "UT     254460.901418\n",
       "CO     251255.601348\n",
       "VT     230935.117395\n",
       "AK     229827.785265\n",
       "CA     224144.109682\n",
       "OR     222046.960599\n",
       "VA     219915.869639\n",
       "AZ     211324.091235\n",
       "ID     185004.779133\n",
       "FL     182630.485435\n",
       "VI     177652.858268\n",
       "AS     168815.750000\n",
       "HI     163479.992625\n",
       "QC      48652.000000\n",
       "PR      32372.890725\n",
       "MH       8091.250000"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# same thing, but look at the average (mean) payment instead\n",
    "df[['State', 'Payment']].groupby('State').mean().sort_values('Payment', ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "63ba3014",
   "metadata": {},
   "source": [
    "### Ideas to explore on your own\n",
    "- Bring in another dataset -- Census population/demographics, CMS facility ratings, etc. -- to merge with this dataset\n",
    "- Get a breakdown of outlays by city in your state(s) of interest\n",
    "- What else?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8f83ce95",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}