DataScience+ An online community for showcasing R & Python tutorials. It operates as a networking platform for data scientists to promote their talent and get hired. Our mission is to empower data scientists by bridging the gap between talent and opportunity.

A use of gsub, reshape2 and sqldf with healthcare data

Building off other industry-specific posts, I want to use healthcare data to demonstrate the use of R packages. The data can be downloaded here. To read the .CSV file in R you might read the post how to import data in R. Packages in R are stored in libraries and often are pre-installed, but reaching the next level of skill requires being able to know when to use new packages and what they contain. With that let’s get to our example.


When working with vectors and strings, especially in cleaning up data, gsub makes cleaning data much simpler. In my healthcare data, I wanted to convert dollar values to integers (ie. $21,000 to 21000), and I used gsub as seen below.

Reading the data in R from CSV file. I am naming the dataset “hosp”.

hosp <- read.csv("Payment_and_value_of_care_-_Hospital.csv")

In the code below I will remove hospitals without estimates

hospay<-hosp[hosp$Payment.category !="Not Available" & hosp$Payment.category !="Number of Cases Too Small",]

Now its time to remove the dollar signs and commas in estimate values

hospay$Payment <- as.numeric(gsub("[$,]","",hospay$Payment))
hospay$Lower.estimate <- as.numeric(gsub("[$,]", "", hospay$Lower.estimate))
hospay$Higher.estimate <- as.numeric(gsub("[$,]", "", hospay$Lower.estimate))

[1] 13469 12863 12308 12222 21376 14740


In looking at the data, I wanted to focus on the Payment estimate. So I used the melt() function that is part of reshape2. Melt allows pivot-table style capabilities to restructure data without losing values.

hosp_mel<-melt(data=hospay,id=c(2,5,9,11), measure=as.numeric(c(13)),'Estimate') 

[1] ""        "State"                "" "Payment.category"     "variable"             "Estimate" 


With my data melted, I wanted to get the average estimate for heart attack patients by state. This is a classic SQL query, so bringing in sqldf allows for that.

names(hosp_melt) [3] <- "paymentmeasurename"
hosp_est <- sqldf("select State, avg(Estimate) as Estimate 
from hosp_melt 
where paymentmeasurename = 'Payment for heart attack patients' 
group by State")

   State  Estimate
1     AK  20987.60
2     AL  21850.32
3     AR  21758.00
4     AZ  22690.62
5     CA  22707.45
6     CO  21795.30 

If you have any question feel free to leave a comment below.