1. Physician Profile Supplement File

The Physician Profile Supplement file contains information about physicians who were indicated as recipients of payments, other transfers of value, or ownership and investment interest in payment records as well as physicians identified as principal investigators associated with research payment records published by Open Payments.

However, this file contains only physicians who are included in at least one published payment record. The criteria used by the Centers for Medicare and Medicaid Services (CMS) to determine which payment records are eligible for publication is available in the Open Payments Data Dictionary and Methodology document. This document can be found on the Resources page of the Open Payments website (https://www.cms.gov/OpenPayments/About/Resources.html). The Data Dictionary and Methodology document also includes information on the data collection and reporting methodology, data fields included in the files, and any notes or special considerations that users should be aware of.

2. Considerations for using the CSV File

Microsoft Excel removes leading zeroes from data fields in comma-separated values (CSV) files. Certain fields in this data set may have leading zeroes. These zeroes will be missing when viewing the information within Microsoft Excel.

Additionally, the latest versions of Microsoft Excel cannot display data sets with more than 1,048,576 rows. This CSV file may exceed that limit. Displaying the data in its entirety may require the use of a spreadsheet program capable of handling very large numbers of records.

3. Details about the OP_PH_PRFL_SPLMTL_P01172017.zip File

This compressed (.zip) file contains one (1) comma-separated values (.csv) format file that uses commas as delimiters and one (1) README.txt file. A description of the CSV file is provided below.

• OP_PH_PRFL_SPLMTL_P01172017.csv:

This supplementary file displays information on all of the physicians indicated as covered recipients of payments and/or physician principal investigators associated with payments in records published by Open Payments. Each record includes the physician’s demographic information, specialties, and license state, as well as the unique identification number (Physician Profile ID) assigned by Open Payments for each physician. The Physician Profile ID can be used to search Open Payments data to find payments made to or associated with that specific physician.

The physician profile information included in the data sets is submitted by the reporting entity. In contrast, the physician information included in the supplementary file can be derived from different sources including the National Plan and Provider Enumeration System (NPPES) and the Provider Enrollment, Chain and Ownership System (PECOS). As a result, the data in these sources may differ slightly. When searching for physicians using the Open Payments search tool on https://openpaymentsdata.cms.gov, use the physician profile information as listed in the supplementary file.

For simplicity reasons, I will read the raw data directly from the source.

Read .csv from url by employing read.csv()

For this project I will experiment a few things as follows:

I will do a combined data management procedure by employing MySQL; that is:

1. I will create a procedure that will create a connection to MySQL.

2. Once the connection is “ON”, I will check to see if a database named cms_OpenPaymentData exist.

• If the database exist, do nothing.

• If the database does NOT exist, create one.

3. Once the previous step is performed, I will check to see if a table named tbl_OpenPaymentData exist.

• If the table exist, read the information from it.

• If the table does NOT exist, then do as follows:

  • Download the .zip file from url in a temp file.

  • Extract the OP_PH_PRFL_SPLMTL_P01172017.csv file containing the required information.

  • Write all the information contained in the .csv file into the tbl_OpenPaymentData table from the cms_OpenPaymentData MySQL scheme.

  • Delete the temp .zip file.

After that I will import all the data into a data frame.

This might take some time since the file contains a large number of records, but in a second or multiple iterations will save a ton of time. Also another advantage is that the information will stay local and it will be straight forward to update if needed.

Function to download .zip file, unzip and extract information from .csv

downloadZip <- function(myurl, myzipfile, mycsvfile){
  temp <- tempfile()
  url <- paste(myurl, myzipfile, sep="")
  download.file(url, temp)
  my.file <- unzip(myzipfile, files = mycsvfile)
  my.data <- read.csv(my.file, header=TRUE, sep=",", stringsAsFactors=FALSE)
# Deleting downloaded file
unlink(temp)
# Returning data
  return(my.data)
}

MySQL Procedure to Read or Write tables

# Establish MySQLconnection
mydbconnection <- dbConnect(MySQL(), 
                  user = myLocalUser,
                  password = myLocalPassword,
                  host = myLocalHost)
# creating a database if it doesn't exist by employing RMySQL() in R
MySQLcode <- paste0("CREATE SCHEMA IF NOT EXISTS ",myLocalMySQLSchema,";", sep="")
dbSendQuery(mydbconnection, MySQLcode)

# Table exists?
mydbconnection <- dbConnect(MySQL(), 
                  user = myLocalUser,
                  password = myLocalPassword,
                  host = myLocalHost,
                  dbname = myLocalMySQLSchema)
# Check to see if table data exist.
myLocalTableName <- tolower(myLocalTableName)
if (dbExistsTable(mydbconnection, name = myLocalTableName)  == FALSE){
# If the table does not exist, download .zip and write .csv file into MySQL
my.data <- downloadZip(myurl= url, myzipfile= zipfile,  mycsvfile= csvfile)
# Then Write the table in MySQL
dbWriteTable(mydbconnection, name= myLocalTableName , value= my.data)
} else {
# Read the data from the local table
 my.data <- dbReadTable(mydbconnection, name = myLocalTableName)
}

# Closing connection with local Schema
dbDisconnect(mydbconnection)

Imported file structure display

## 'data.frame':    814447 obs. of  27 variables:
##  $ Physician_Profile_ID                   : num  29708 29709 29719 29720 29721 ...
##  $ Physician_Profile_First_Name           : chr  "GREGORY" "JOHN" "GEORGE" "MARC" ...
##  $ Physician_Profile_Middle_Name          : chr  "" "D" "A" "S" ...
##  $ Physician_Profile_Last_Name            : chr  "SENSENICH" "BALUCH" "BLESSIOS" "FISK" ...
##  $ Physician_Profile_Suffix               : chr  "" "" "" "" ...
##  $ Physician_Profile_Alternate_First_Name : chr  "GREGORY" "" "" "MARC" ...
##  $ Physician_Profile_Alternate_Middle_Name: chr  "W" "" "" "SASLOW" ...
##  $ Physician_Profile_Alternate_Last_Name  : chr  "SENSENICH" "" "" "FISK" ...
##  $ Physician_Profile_Alternate_Suffix     : chr  "" "" "" "" ...
##  $ Physician_Profile_Address_Line_1       : chr  "861 FAIRWAY DR" "500 E MAIN ST" "550 ORCHARD PARK RD" "375 MOUNT PLEASANT AVE" ...
##  $ Physician_Profile_Address_Line_2       : chr  "" "STE 220" "A103" "" ...
##  $ Physician_Profile_City                 : chr  "CHILLICOTHE" "COLUMBUS" "WEST SENECA" "WEST ORANGE" ...
##  $ Physician_Profile_State                : chr  "MO" "OH" "NY" "NJ" ...
##  $ Physician_Profile_Zipcode              : chr  "64601-3673" "43215" "14224-2646" "07052-2724" ...
##  $ Physician_Profile_Country_Name         : chr  "UNITED STATES" "UNITED STATES" "UNITED STATES" "UNITED STATES" ...
##  $ Physician_Profile_Province_Name        : chr  "" "" "" "" ...
##  $ Physician_Profile_Primary_Specialty    : chr  "Allopathic & Osteopathic Physicians|Family Medicine" "Allopathic & Osteopathic Physicians|Urology" "Allopathic & Osteopathic Physicians|Surgery" "Allopathic & Osteopathic Physicians|Internal Medicine|Cardiovascular Disease" ...
##  $ Physician_Profile_OPS_Taxonomy_1       : chr  "207Q00000X" "208800000X" "208600000X" "207RC0000X" ...
##  $ Physician_Profile_OPS_Taxonomy_2       : chr  "" "" "204F00000X" "" ...
##  $ Physician_Profile_OPS_Taxonomy_3       : chr  "" "" "" "" ...
##  $ Physician_Profile_OPS_Taxonomy_4       : chr  "" "" "" "" ...
##  $ Physician_Profile_OPS_Taxonomy_5       : chr  "" "" "" "" ...
##  $ Physician_Profile_License_State_Code_1 : chr  "MO" "OH" "NY" "NJ" ...
##  $ Physician_Profile_License_State_Code_2 : chr  "" "" "PA" "" ...
##  $ Physician_Profile_License_State_Code_3 : chr  "" "" "" "" ...
##  $ Physician_Profile_License_State_Code_4 : chr  "" "" "" "" ...
##  $ Physician_Profile_License_State_Code_5 : chr  "" "" "" "" ...

In summary, this data frame contains 814447 independent observations with 27 recognizable variables.

Data transformation

Now that I have the data frame I will transform it in order to create some possible outcomes from the given information; for this, I will subset() by excluding small portion of it.

my.new.data <-  my.data %>% subset(select=-(Physician_Profile_Alternate_First_Name:Physician_Profile_Alternate_Suffix ))

my.new.data <-  my.new.data %>% subset(select=-(Physician_Profile_Address_Line_1:Physician_Profile_Address_Line_2))

my.new.data <-  my.new.data %>%
                unite(Physician, c(Physician_Profile_First_Name, Physician_Profile_Middle_Name, Physician_Profile_Last_Name, Physician_Profile_Suffix), remove=TRUE, sep=" ")

my.new.data$Physician_Profile_Zipcode <-clean.zipcodes(my.new.data$Physician_Profile_Zipcode)

# my.new.data$Physician_Profile_Primary_Specialty <- my.new.data %>%
#                                                    separate_rows(Physician_Profile_Primary_Specialty,sep="|", convert = FALSE)
#View(my.new.data)

my.new.data <-  my.new.data %>%
                unite(Taxonomy, c(Physician_Profile_OPS_Taxonomy_1, Physician_Profile_OPS_Taxonomy_2, Physician_Profile_OPS_Taxonomy_3, Physician_Profile_OPS_Taxonomy_4, Physician_Profile_OPS_Taxonomy_5), remove=TRUE, sep=" ")

my.new.data <-  my.new.data %>%
                unite(License, c(Physician_Profile_License_State_Code_1, Physician_Profile_License_State_Code_2, Physician_Profile_License_State_Code_3, Physician_Profile_License_State_Code_4, Physician_Profile_License_State_Code_5), remove=TRUE, sep=" ")

## 'data.frame':    814447 obs. of  10 variables:
##  $ Physician_Profile_ID               : num  29708 29709 29719 29720 29721 ...
##  $ Physician                          : chr  "GREGORY  SENSENICH " "JOHN D BALUCH " "GEORGE A BLESSIOS " "MARC S FISK " ...
##  $ Physician_Profile_City             : chr  "CHILLICOTHE" "COLUMBUS" "WEST SENECA" "WEST ORANGE" ...
##  $ Physician_Profile_State            : chr  "MO" "OH" "NY" "NJ" ...
##  $ Physician_Profile_Zipcode          : chr  "64601" "43215" "14224" "07052" ...
##  $ Physician_Profile_Country_Name     : chr  "UNITED STATES" "UNITED STATES" "UNITED STATES" "UNITED STATES" ...
##  $ Physician_Profile_Province_Name    : chr  "" "" "" "" ...
##  $ Physician_Profile_Primary_Specialty: chr  "Allopathic & Osteopathic Physicians|Family Medicine" "Allopathic & Osteopathic Physicians|Urology" "Allopathic & Osteopathic Physicians|Surgery" "Allopathic & Osteopathic Physicians|Internal Medicine|Cardiovascular Disease" ...
##  $ Taxonomy                           : chr  "207Q00000X    " "208800000X    " "208600000X 204F00000X   " "207RC0000X    " ...
##  $ License                            : chr  "MO    " "OH    " "NY PA   " "NJ    " ...

names(my.new.data) <- c("ID","Physician", "City", "State", "Zipcode", "Country", "Province", "Specialty", "Taxonomy", "License")

## 'data.frame':    814447 obs. of  10 variables:
##  $ ID       : num  29708 29709 29719 29720 29721 ...
##  $ Physician: chr  "GREGORY  SENSENICH " "JOHN D BALUCH " "GEORGE A BLESSIOS " "MARC S FISK " ...
##  $ City     : chr  "CHILLICOTHE" "COLUMBUS" "WEST SENECA" "WEST ORANGE" ...
##  $ State    : chr  "MO" "OH" "NY" "NJ" ...
##  $ Zipcode  : chr  "64601" "43215" "14224" "07052" ...
##  $ Country  : chr  "UNITED STATES" "UNITED STATES" "UNITED STATES" "UNITED STATES" ...
##  $ Province : chr  "" "" "" "" ...
##  $ Specialty: chr  "Allopathic & Osteopathic Physicians|Family Medicine" "Allopathic & Osteopathic Physicians|Urology" "Allopathic & Osteopathic Physicians|Surgery" "Allopathic & Osteopathic Physicians|Internal Medicine|Cardiovascular Disease" ...
##  $ Taxonomy : chr  "207Q00000X    " "208800000X    " "208600000X 204F00000X   " "207RC0000X    " ...
##  $ License  : chr  "MO    " "OH    " "NY PA   " "NJ    " ...

Final Tidy Table

Country:

Based on simple observation, is easy to spot how CMS has made payments to physicians from out of this country, this is something I was not expecting to find and it definitely caught my attention since I was under the impression that those kind of payments for services were performed in the US territories.

State:

If we look at the chart and results, we noticed that over all California and New York, presented the highest physician populations. This, I believe is correlated due to major concentration of humans in those states, once again in this comparison comes to show how less populated areas tend to have less physician populations registered with the open payment data from CMS.

Zipcode:

This is an interesting analysis and I believe it can play a great role in local discoveries related to quality of health care and competition, since it covers immediate surrounding areas.

For example, from the Region distribution we can visualize how the distribution is over the country having a lot of empty spaces on the mid west. Also, something interesting is to observe that the top 3 Zip codes present on the Zip code table, belong to Houston, Chicago and Philadelphia; this is remarkable since these states did not figured as the top states, yet these zip codes have a concentrated number of physicians.

Final conclusion:

Since there’s more data available, it will be interesting to perform more comparisons in regards of the specialties, number of licenses or number of taxonomies that physicians have; this, to find out if some physicians are “better” prepared to deal with proper treatment for all the patients in the surrounding areas, the above conclusion is by assuming that patients tend to visit local health care practitioners in any given emergency situation.