Medicare payment comparisons

For this post I wanted to experiment with Choropleth Maps, and interactive visuals. At my past company I could have used Tableau which has built in Geo charts which are really pretty, but for a more open alternative I am using Plotly which also has a R integration which I would have needed anyway to clean the data first.

A choropleth map is a thematic map in which areas are shaded or patterned in proportion to the measurement of the statistical variable being displayed on the map, such as population density or per-capita income.

The data comes from Enigma.io: Payment Comparisons by Hospital

Assumptions made: for the rows with an asterisk (*) under number_of_cases I have substituted a 4

An asterisk (*) appears in the table where data cannot be disclosed to protect personal health information due to the small number of Medicare patients (fewer than 11)

#R Code
# Load Libraries 

library(devtools)
library(readxl)
library(dplyr)
library(plotly)

# Read in Data

State <- read_excel("MedicareCompare.xlsx", sheet = "State")
Clean <- read_excel("MedicareCompare.xlsx", sheet = "Clean")


# Change data types

Clean$Name <- as.factor(Clean$Name)
Clean$State <- as.factor(Clean$State)
Clean$zip_code <- as.factor(Clean$zip_code)
Clean$county_name <- as.factor(Clean$county_name)
Clean$diagnosis_related_group <- as.factor(Clean$diagnosis_related_group)

# explore structure and head of the data

str(Clean)
head(Clean)


# Use dplyr to get the median payment and take into account Frequency

Clean2 <- Clean[rep(1:nrow(Clean), Clean$Frequency),] %>% group_by(State) %>% summarise(Median = median(medicare_average_payment))

head(Clean2)


# give state boundaries a white border
l <- list(color = toRGB("white"), width = 2)

# specify some map projection/options
g <- list(
  scope = 'usa',
  projection = list(type = 'albers usa'),
  showlakes = TRUE,
  lakecolor = toRGB('white')
)

plot_ly(Clean2, z = Median, locations = State, type = 'choropleth',
        locationmode = 'USA-states', color = Median, colors = 'Purples',
        marker = list(line = l), colorbar = list(title = "USD")) %>%
  layout(title = 'Median Medicare payment by State<br>(Hover for breakdown)', geo = g)

Due to the large right skew in different diagnosis groups, I used median instead of the mean to plot the above map. I find it interesting that Alaska has the highest Median Medicare payment, let’s dig into that because that was unexpected. So I graph for the following states (AK, AL, KS, NY) medicare payment by Diagnosis and on a separate axis the frequency, while sorting by payment.

Based on this information above we see that in the other comparison States there is higher frequency towards the lower costing diagnostic groups which brings down the median payment.

Below is some sorted tables to satisfy my own curiosity

Ranked first by Average payment, and then by Frequency, Top and Bottom 5

The most expensive procedures are clustered around Cardiac defibrillators with MCC’s, and the most frequent procedures have to do with Heart failure. The Correlation between average payment and Frequency was (-0.3), makes sense that the most common procedures are less expensive compared to the least common ones.

By Hospital

This one brought back some memories, I worked on the advertising strategy for the CTCA account for a year. Midwestern Region is also a branch of CTCA. In context CTCA is a for profit Cancer Hospital that generally does not take Medicare (at least 3 years ago). The average was brought up from the combination of low frequency and one large “kidney and urinary tract procedures w MCC” Procedure.

Note: MCC Stands for Major Complications or Comorbidities