Civic Data Wrangling: in R and on data.world

Setup and Initial Analysis

Once we got the CMS spending data pulled out of its original Excel format, tidied, and uploaded, I used the data.world package to query the schema metadata and see exactly how tables were named, choose the right files, and pull them directly into R.

(To run this, you’ll need your data.world API token, which I saved as DW_API in an .Renviron file to make my code shareable.)

library(tidyverse)
library(data.world)

# API token is saved in .Renviron

data.world::set_config(cfg_env("DW_API"))

# Datasets are identified by their URL
drugs_ds <- "https://data.world/data4democracy/drug-spending"

# List tables
data_list <- data.world::query(
  qry_sql("SELECT * FROM Tables"),
  dataset = drugs_ds)

# data_list is a tbl_df with two columns: tableID and tableName.
data_list$tableName

##  [1] "FDA_NDC_Product"             "Data"                       
##  [3] "Methods"                     "Variables"                  
##  [5] "Pharma_Lobby"                "atc-codes"                  
##  [7] "companies_drugs_keyed"       "drug_list"                  
##  [9] "drug_uses"                   "drugdata_clean"             
## [11] "drugnames_withclasses"       "lobbying_keyed"             
## [13] "manufacturers_drugs_cleaned" "meps_full_2014"             
## [15] "spending-2011"               "spending-2012"              
## [17] "spending-2013"               "spending-2014"              
## [19] "spending-2015"               "spending_all_top100"        
## [21] "usp_drug_classification"

Yearly Data Consolidation

The CMS data is stored in the five spending-201x tables, one per year. I read those directly from data.world by writing a simple SQL query identifying those table names, then used dplyr and purrr to quickly add a year variable to each and combine them:

# Function to read in a single year's CSV from data.world and add year
get_year <- function(yr) {
  data.world::query(qry_sql(paste0("SELECT * FROM `spending-", yr, "`")),
    dataset = drugs_ds)[,-1] %>%
    ## First column is a row number; don"t need that
    mutate(year = yr)
}

# Read in and combine all years' data
spend <- map_df(2011:2015, get_year)

Most Popular Generic Drugs

To keep runtime and drop-down menus reasonable, we currently only include the 100 drugs with the most users over the five-year period.

The app allows users to select any one of those to look at people-related values (e.g., the number of users or the number of claims) and spending-related values (e.g., total costs, average out-of-pocket costs for users receiving and not receiving low-income subsidies).

The datasets include one record per drug brand name.

Each generic drug may appear with several brand names. Metformin HCL, for example, has over 35 brand names - Glucophage XR, Glumetza, etc. To include them in my analysis, I calculated the total number of users and units per year for each generic, added those values to the original dataset, then selected the 100 most common generics during that five-year period.

# Add a row for each generic with overall summaries of each variable ----------
spend_overall <- spend %>%
  group_by(drugname_generic, year) %>%
  summarise(
    claim_count = sum(claim_count, na.rm = TRUE),
    total_spending = sum(total_spending, na.rm = TRUE),
    user_count = sum(user_count, na.rm = TRUE),
    unit_count = sum(unit_count, na.rm = TRUE),
    user_count_non_lowincome = sum(user_count_non_lowincome, na.rm = TRUE),
    user_count_lowincome = sum(user_count_lowincome, na.rm = TRUE)
  ) %>%
  mutate(
    total_spending_per_user = total_spending / user_count,
    drugname_brand = "ALL BRAND NAMES",
    ## Add NA values for variables that are brand-specific
    unit_cost_wavg = NA,
    out_of_pocket_avg_lowincome = NA,
    out_of_pocket_avg_non_lowincome = NA
  ) %>%
  ungroup()

# Select top 100 generics by number of users across all five years ------------
by_user_top100 <- group_by(spend_overall, drugname_generic) %>%
  summarise(total_users = sum(user_count, na.rm = TRUE)) %>%
  arrange(desc(total_users)) %>%
  slice(1:100)

# For top 100 generics, add ALL BRAND NAMES rows to by-brand-name rows --------
spend_all_top100 <- bind_rows(spend, spend_overall) %>%
  filter(drugname_generic %in% by_user_top100$drugname_generic) %>%
  arrange(drugname_generic)

Saving The Work

Once I had the final dataset with just our top 100, I uploaded it directly to data.world as a CSV via the dwapi package, which is bundled, and loaded automatically, with data.world:

# Write final file to data.world using package ------------------------------------

# data.world's REST API methods are available via the dwapi package
dwapi::upload_data_frame(
  dataset = drugs_ds,
  file_name = "spending_all_top100.csv",
  data_frame = spend_all_top100
)

About the author

Jennifer Thompson is a well-rounded conversationalist and a standup woman who spends her days as a biostatistician and avid R user. Jennifer is @jthompson on data.world and @jent103 on Twitter.