Analyzing Health Expenditure Data By Country From The OECD With R
In this example, we will use the OECD package to access health expenditure data by country.
Packages USED
The key package used in this vignette is the oecd package which accesses stats database from the OECD.[1] Key packages include
- dplyr – for processing the tibble prior to conversion to xts
- ggplot2 – part of tidyverse for plotting the results
- OECD – package to access data using OECD stats api.[2]
Examing the Data Structure
The initial stage is to search for available health related databases.
#OECD RUNLibrary(OECD)library(dplyr)library(ggplot2)#define the countries we are going to charttarget_locations<-c("CAN","USA","FRA","DEU","GBR","DNK","NLD","NZL","AUS","NOR","ESP")#get data set listdataset_list <- get_datasets()p1<-search_dataset("health", data = dataset_list)#we will use SHA which is the 6th in the result p1data_set<-as.character(p1$id[6])data_set_title<-as.character(p1$title[6])#now we will get the structure of the datasetdstruct<-get_data_structure(data_set)str(dstruct,max.level=1)print(dstruct$VAR_DESC)#now examine the structure - the first sections are key # id description# 1 HF Financing scheme# 2 HC Function# 3 HP Provider# 4 MEASURE Measure# 5 LOCATION Countryprint(dstruct$HF)print(dstruct$HC)print(dstruct$HP)print(dstruct$MEASURE)The code above shows how to inspect the structure of a given data set. Each dataset in OECD.stat (https://stats.oecd.org) has a different structure. Target databases can be evaluated using the stats.oecd.org web site. The order of the variables in the VAR_DESC table reflects their hierarchy in the dataset. HF specifically addresses total funding, funding by government or insurance scheme etc.
id label1 HFTOT All financing schemes2 HF1 Government/compulsory schemes3 HF11 Government schemes4 HF12HF13 Compulsory contributory health insurance schemes5 HF121 Social health insurance schemes6 HF122 Compulsory private insurance schemes7 HF2HF3 Voluntary schemes/household out-of-pocket payments8 HF2 Voluntary health care payment schemes9 HF21 Voluntary health insurance schemes10 HF22 NPISH financing schemes11 HF23 Enterprise financing schemes12 HF3 Household out-of-pocket payments13 HF31 Out-of-pocket excluding cost-sharing14 HF32 Cost-sharing with third-party payers15 HF4 Rest of the world financing schemes (non-resident)16 HF42 Voluntary schemes (non-resident)17 HF0 Financing schemes unknownRetrieving the data
The top level of financing will be used with the exception of financing from non-residents for this analysis. For this analysis, we will focus on aggregate health expenditure (HCTOT) and services provided by all providers (HPTOT). The code has been developed to cycle through a list of measures. In this case, we will use PARPIB, the GDP share, and PPPPER, expenditure per capita on a purchasing power parity basis.
function_target="HCTOT"unction_title=filter(dstruct$HC,id==function_target)%>%select(label)target_financing<-c("HFTOT","HF1","HF2","HF3")filter_list<-list(target_financing,function_target,"HPTOT")data1<-get_dataset(dataset=data_set,filter=filter_list)#save base dataset as an excel file for documentationlibrary(openxlsx)write.xlsx(data1,file=paste0("SHA_financing_",Sys.Date(),".xlsx"))#Filters have to be set in the order of variables to reduce the data retrieval to manageable proportions. The retrieved dataset is saved as an XLSX file in the default directory to provide a snapshot of data. This is often a requirement when delivering research because data gets revised. The advantage of the XLSX data structure is it is now an open data standard. The openxlsx package creates the file without using any Microsoft or Java libraries. If one wanted only to postprocess the data in R, it would be better to save the data1 tibble using saveRDS, a native R command which saves in a compact binary form. The command readRDS could be used to read the file in,
Process and Plot the Data by Measure
The next set of code sets up filters to be used by dplyr to further process the retrieved data. A location list is prepared using standard 3 character country codes which are common across many international data providers. The selected locations are defined in the character vector target_locations at the top of the script.
#now we will parse by locations and the measure - share of gdplocation_table<-dstruct$LOCATIONlocation_factor<-factor(target_locations,levels=target_locations)location_titles<-filter(dstruct$LOCATION,id %in% target_locations) %>%select(label)#define the measure to be used# target_measure<-"PARPIB"target_measure<-"PPPPER"measures_list<-c("PARPIB","PPPPER")for(target_measure in measures_list){ target_title<-filter(dstruct$MEASURE,id==target_measure) %>% select(label)%>% as.character() #define financing labels financing_labels<-filter(dstruct$HF,id %in% target_financing) %>% select(label) #organize data #the left_joins add labels for location and financing (label.x label.y) data2<-data1 %>% filter(LOCATION %in% target_locations,MEASURE==target_measure) %>% select(HF,LOCATION,obsValue,obsTime) %>% group_by(HF,LOCATION)%>% arrange(obsTime) %>% slice(n()) %>%ungroup()%>% left_join(location_table,by=c("LOCATION"="id")) %>% left_join(dstruct$HF,by=c("HF"="id"))The loops over the list of measures in this version of the code. The data2 table is created from the initial retrieval. Only the last observation (ragged) is retained by using the slice function. The name of the country and the financing title are joined with left_joins. At this juncture, the country name is not actually used.
Because there are varying terminal years, the year, in the variable obsTime, is appended to the value to label the datapoint. In the plot, the label.y variable, the label from the financing table, is used as a grouping table so that the plots are separated by financing. As much as possible, the plot uses title information obtained from the database.
data_year<-unique(data2$obsTime) print(data_year) #start plot #text adjustment factor to move the point labels high enough text_adjust_factor<-.125*max(data2$obsValue) plot1<-ggplot(data2,aes(x=LOCATION,y=obsValue,group=label.y, label=paste(round(obsValue,1),"-",obsTime)))+ labs(y=target_title,x=NULL, title=function_title,caption=paste("OECD:",data_set_title,"JCI"))+ geom_bar(stat="identity",fill="light green")+ geom_text(size=2.5,angle=90,aes(y=(sign(obsValue)* (text_adjust_factor+abs(obsValue)))))+ facet_wrap(~label.y,ncol=2)ggsave(plot1,file=paste0(function_title,"_",function_target,"_",target_measure,".png"),width=8,height=10.5)}The plot is saved as 4 facets (small sub plots) using facet_wrap function which references the lable.y financing title. A text adjustment factor is calculated to move the value and year labels high enough above the bars. A file name to save the plot is developed.
The example plots are shown below.
