Read Washington Medicaid Fee Schedules

The Washington state Health Care Authority website for fee schedules is here.

Fee schedules come in Excel format
Fee schedules are usually biannual (January and July)
Publicly available fee schedules go back to January 2011

However, Washington's Medicaid fee schedules are a pain in the ass. They are publicly available as Microsoft Excel files but...

File names are not systematic
They do not read directly into R nicely (using either the readxl or xlsx packages)
Data lines start at different rows

All these issues makes codifying difficult. As a workaround, the following steps were taken.

Excel files are saved locally
Excel files are converted to CSV
CSV files are version controlled in this repository (since they are not large)
CSV files are read into R

The first 3 steps were done manually. The SHA for the commit of the CSV files is 5bde7f3e33e0c83bdace0ed0cf04553a41a8efb1 (5/5/2016). Step 4 is below.



In [1]:

    
files <- paste("Data", list.files(file.path(getwd(), "Data")), sep="/")
files









    Out[1]:





	"Data/HCA_PREOH_January_1_2013.csv"
	"Data/physician_010114.csv"
	"Data/physician_010115.csv"
	"Data/physician_010116.csv"
	"Data/physician_040115.csv"
	"Data/physician_040116.csv"
	"Data/physician_070114.csv"
	"Data/physician_070115.csv"
	"Data/physician_100115.csv"
	"Data/preoh_010112.csv"
	"Data/preoh_01012011.csv"
	"Data/preoh_070112.csv"
	"Data/preoh_070113.csv"
	"Data/preoh_07012011.csv"



In [2]:

    
library(data.table)
readFS <- function (f, skip) {
    require(data.table, quietly=TRUE)
    for (i in 11:16) {if (grepl(sprintf("%d\\.csv", i), f)) {year <- as.numeric(sprintf("20%d", i))}}
    for (i in 1:12) {
        monname <- format(as.Date(sprintf("%d-%d-01", year, i)), format="%B")
        if (grepl(sprintf("_%02d", i), f) | grepl(tolower(monname), f, ignore.case=TRUE)) {
            mm <- i
        }
    }
    colClasses <- rep("character", 9)
    D <- data.table(read.csv(f, header=FALSE, colClasses=colClasses, skip=skip, na.strings=c(""), strip.white=TRUE))
    old <- names(D)
    keep <- c("code_status_indicator",
              "code",
              "mod",
              "nfs_maximum_allowable",
              "fs_maximum_allowable",
              "pa_required",
              "global_days",
              "comments")
    if (length(old) > length(keep)) {new <- c(keep, old[(length(keep) + 1):length(old)])}
    else {new <- keep}
    setnames(D, old, new)
    D <- D[, effective_date := as.Date(sprintf("%d-%d-01", year, mm))]
    D[, c(keep, "effective_date"), with=FALSE]
}



In [3]:

    
fs <- rbindlist(list(readFS(file.path(getwd(), "Data/HCA_PREOH_January_1_2013.csv"), 9),
                     readFS(file.path(getwd(), "Data/physician_010114.csv"), 9),
                     readFS(file.path(getwd(), "Data/physician_010115.csv"), 9),
                     readFS(file.path(getwd(), "Data/physician_010116.csv"), 10),
                     readFS(file.path(getwd(), "Data/physician_040115.csv"), 9),
                     readFS(file.path(getwd(), "Data/physician_040116.csv"), 10),
                     readFS(file.path(getwd(), "Data/physician_070114.csv"), 9),
                     readFS(file.path(getwd(), "Data/physician_070115.csv"), 10),
                     readFS(file.path(getwd(), "Data/physician_100115.csv"), 10),
                     readFS(file.path(getwd(), "Data/preoh_010112.csv"), 6),
                     readFS(file.path(getwd(), "Data/preoh_01012011.csv"), 6),
                     readFS(file.path(getwd(), "Data/preoh_070112.csv"), 9),
                     readFS(file.path(getwd(), "Data/preoh_070113.csv"), 9),
                     readFS(file.path(getwd(), "Data/preoh_07012011.csv"), 6)))
str(fs)









    



Classes 'data.table' and 'data.frame':	164912 obs. of  9 variables:
 $ code_status_indicator: chr  NA NA NA NA ...
 $ code                 : chr  "00100" "00102" "00103" "00104" ...
 $ mod                  : chr  NA NA NA NA ...
 $ nfs_maximum_allowable: chr  "5 Base" "6 Base" "5 Base" "4 Base" ...
 $ fs_maximum_allowable : chr  "5 Base" "6 Base" "5 Base" "4 Base" ...
 $ pa_required          : chr  NA NA NA NA ...
 $ global_days          : chr  "000" "000" "000" "000" ...
 $ comments             : chr  "N" "N" "N" "N" ...
 $ effective_date       : Date, format: "2013-01-01" "2013-01-01" ...
 - attr(*, ".internal.selfref")=<externalptr>



In [4]:

    
fs[, .N, effective_date][order(effective_date)]









    Out[4]:





effective_date N

	1 2011-01-01 11697
	2 2011-07-01 11518
	3 2012-01-01 11768
	4 2012-07-01 11606
	5 2013-01-01 11832
	6 2013-07-01 11656
	7 2014-01-01 11803
	8 2014-07-01 11743
	9 2015-01-01 12027
	10 2015-04-01 11835
	11 2015-07-01 11837
	12 2015-10-01 11841
	13 2016-01-01 11958
	14 2016-04-01 11791



In [5]:

    
head(fs)
tail(fs)









    Out[5]:





code_status_indicator code mod nfs_maximum_allowable fs_maximum_allowable pa_required global_days comments effective_date

	1 NA 00100 NA 5 Base 5 Base NA 000 N 2013-01-01
	2 NA 00102 NA 6 Base 6 Base NA 000 N 2013-01-01
	3 NA 00103 NA 5 Base 5 Base NA 000 N 2013-01-01
	4 NA 00104 NA 4 Base 4 Base NA 000 N 2013-01-01
	5 NA 00120 NA 5 Base 5 Base NA 000 N 2013-01-01
	6 NA 00124 NA 4 Base 4 Base NA 000 N 2013-01-01









    Out[5]:





code_status_indicator code mod nfs_maximum_allowable fs_maximum_allowable pa_required global_days comments effective_date

	1 NA V2631 NA 342.42 342.42 NA 000 N 2011-07-01
	2 NA V2632 NA 342.42 342.42 NA 000 N 2011-07-01
	3 NA V2785 NA # # NA 000 N 2011-07-01
	4 NA V2787 NA # # NA 000 N 2011-07-01
	5 NA V2788 NA # # NA 000 N 2011-07-01
	6 NA V2799 NA # # NA 000 N 2011-07-01

	effective_date	N
1	2011-01-01	11697
2	2011-07-01	11518
3	2012-01-01	11768
4	2012-07-01	11606
5	2013-01-01	11832
6	2013-07-01	11656
7	2014-01-01	11803
8	2014-07-01	11743
9	2015-01-01	12027
10	2015-04-01	11835
11	2015-07-01	11837
12	2015-10-01	11841
13	2016-01-01	11958
14	2016-04-01	11791

	code_status_indicator	code	mod	nfs_maximum_allowable	fs_maximum_allowable	pa_required	comments	effective_date
1	NA	00100	NA	5 Base	5 Base	NA	N	2013-01-01
2	NA	00102	NA	6 Base	6 Base	NA	N	2013-01-01
3	NA	00103	NA	5 Base	5 Base	NA	N	2013-01-01
4	NA	00104	NA	4 Base	4 Base	NA	N	2013-01-01
5	NA	00120	NA	5 Base	5 Base	NA	N	2013-01-01
6	NA	00124	NA	4 Base	4 Base	NA	N	2013-01-01

	code_status_indicator	code	mod	nfs_maximum_allowable	fs_maximum_allowable	pa_required	comments	effective_date
1	NA	V2631	NA	342.42	342.42	NA	N	2011-07-01
2	NA	V2632	NA	342.42	342.42	NA	N	2011-07-01
3	NA	V2785	NA	#	#	NA	N	2011-07-01
4	NA	V2787	NA	#	#	NA	N	2011-07-01
5	NA	V2788	NA	#	#	NA	N	2011-07-01
6	NA	V2799	NA	#	#	NA	N	2011-07-01