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 [3]:

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









    Out[3]:





	"ambulance_transportation_022016.csv"
	"Excel"
	"HCA_PREOH_January_1_2013.csv"
	"physician_010114.csv"
	"physician_010115.csv"
	"physician_010116.csv"
	"physician_040115.csv"
	"physician_040116.csv"
	"physician_070114.csv"
	"physician_070115.csv"
	"physician_100115.csv"
	"preoh_010112.csv"
	"preoh_01012011.csv"
	"preoh_070112.csv"
	"preoh_070113.csv"
	"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

Rename object



In [ ]:

    
fsPhysician <- fs

Ambulance Transportation



In [18]:

    
library(data.table)
f <- file.path(getwd(), "Data/ambulance_transportation_022016.csv")
D <- data.table(read.csv(f, header=TRUE, na.strings=c(""), strip.white=TRUE, stringsAsFactors=FALSE))
old <- names(D)
new <- c("code_status_indicator",
         "code",
         "description",
         "fs_maximum_allowable",
         "limits")
setnames(D, old, new)
D <- D[, fs_maximum_allowable := as.numeric(gsub("[^0-9\\.]", "", fs_maximum_allowable))]
D <- D[, effective_date := as.Date("2006-07-01")]
str(D)
D









    



Classes 'data.table' and 'data.frame':	14 obs. of  6 variables:
 $ code_status_indicator: logi  NA NA NA NA NA NA ...
 $ code                 : chr  "A0430" "A0431" "A0435" "A0436" ...
 $ description          : chr  "Ambulance service, conventional air services, transport, one way (fixed wing)" "Ambulance service, conventional air services, transport, one way (rotary wing)" "Fixed wing air mileage, per statute mile" "Rotary wing air mileage, per statute mile" ...
 $ fs_maximum_allowable : num  910.8 804.5 5.5 13.3 115.3 ...
 $ limits               : chr  "Per client transported." "Per client transported." "One way, per flight, equally divided by the number of clients transported." "One way, per flight, equally divided by the number of clients transported." ...
 $ effective_date       : Date, format: "2006-07-01" "2006-07-01" ...
 - attr(*, ".internal.selfref")=<externalptr> 






    Out[18]:





code_status_indicator code description fs_maximum_allowable limits effective_date

	1 NA A0430 Ambulance service, conventional air services, transport, one way (fixed wing) 910.81 Per client transported. 2006-07-01
	2 NA A0431 Ambulance service, conventional air services, transport, one way (rotary wing) 804.45 Per client transported. 2006-07-01
	3 NA A0435 Fixed wing air mileage, per statute mile 5.5 One way, per flight, equally divided by the number of clients transported. 2006-07-01
	4 NA A0436 Rotary wing air mileage, per statute mile 13.31 One way, per flight, equally divided by the number of clients transported. 2006-07-01
	5 NA A0428 Ambulance service, basic life support, non-emergency transport (BLS) 115.34 Origin and destination modifiers required.  For each additional client, use modifier GM in addition to the origin and destination modifiers. 2006-07-01
	6 NA A0429 Ambulance service, basic life support, emergency transport (BLS-emergency) 115.34 Origin and destination modifiers required.  For each additional client, use modifier GM in addition to the origin and destination modifiers. 2006-07-01
	7 NA A0426 Ambulance service, advanced life support non-emergency transport, level 1 (ALS 1) 168.43 Origin and destination modifiers required.  For each additional client, use modifier GM in addition to the origin and destination modifiers. 2006-07-01
	8 NA A0427 Ambulance service, advanced life support, emergency transport, level 1 (ALS 1 emergency) 168.43 Origin and destination modifiers required.  For each additional client, use modifier GM in addition to the origin and destination modifiers. 2006-07-01
	9 NA A0433 Advanced life support, level 2 (ALS 2) 168.43 Origin and destination modifiers required.  For each additional client, use modifier GM in addition to the origin and destination modifiers. 2006-07-01
	10 NA A0434 Specialty care transport (SCT) 168.43 Origin and destination modifiers required.  For each additional client, use modifier GM in addition to the origin and destination modifiers. 2006-07-01
	11 NA A0425 Ground mileage, per statute mile 5.08 Origin and destination modifiers required. 2006-07-01
	12 NA A0170 Transportation ancillary: parking fees, tolls, other Invoice required. NA Origin and destination modifiers required. 2006-07-01
	13 NA A0424 Extra ambulance attendant, ground (ALS or BLS) or air (fixed or rotary winged); (requires medical review) 23.18 Pertinent documentation to evaluate medical appropriateness should be included when this code is reported. Origin and destination modifiers required. Justification required: * The client weighs 300 pounds or more; or * Client is violent or difficult to move safely; or * More than one client is being transported, and each requires medical attention and/or close monitoring. Note: DSHS pays for an extra attendant in ground ambulance transports only.  No payment is made for an extra attendant in air ambulance transports. 2006-07-01
	14 NA A0998 Ambulance response and treatment, no transport NA Code not payable.  Used for data collection purposes only. 2006-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

	code_status_indicator	code	description	fs_maximum_allowable	limits	effective_date
1	NA	A0430	Ambulance service, conventional air services, transport, one way (fixed wing)	910.81	Per client transported.	2006-07-01
2	NA	A0431	Ambulance service, conventional air services, transport, one way (rotary wing)	804.45	Per client transported.	2006-07-01
3	NA	A0435	Fixed wing air mileage, per statute mile	5.5	One way, per flight, equally divided by the number of clients transported.	2006-07-01
4	NA	A0436	Rotary wing air mileage, per statute mile	13.31	One way, per flight, equally divided by the number of clients transported.	2006-07-01
5	NA	A0428	Ambulance service, basic life support, non-emergency transport (BLS)	115.34	Origin and destination modifiers required. For each additional client, use modifier GM in addition to the origin and destination modifiers.	2006-07-01
6	NA	A0429	Ambulance service, basic life support, emergency transport (BLS-emergency)	115.34	Origin and destination modifiers required. For each additional client, use modifier GM in addition to the origin and destination modifiers.	2006-07-01
7	NA	A0426	Ambulance service, advanced life support non-emergency transport, level 1 (ALS 1)	168.43	Origin and destination modifiers required. For each additional client, use modifier GM in addition to the origin and destination modifiers.	2006-07-01
8	NA	A0427	Ambulance service, advanced life support, emergency transport, level 1 (ALS 1 emergency)	168.43	Origin and destination modifiers required. For each additional client, use modifier GM in addition to the origin and destination modifiers.	2006-07-01
9	NA	A0433	Advanced life support, level 2 (ALS 2)	168.43	Origin and destination modifiers required. For each additional client, use modifier GM in addition to the origin and destination modifiers.	2006-07-01
10	NA	A0434	Specialty care transport (SCT)	168.43	Origin and destination modifiers required. For each additional client, use modifier GM in addition to the origin and destination modifiers.	2006-07-01
11	NA	A0425	Ground mileage, per statute mile	5.08	Origin and destination modifiers required.	2006-07-01
12	NA	A0170	Transportation ancillary: parking fees, tolls, other Invoice required.	NA	Origin and destination modifiers required.	2006-07-01
13	NA	A0424	Extra ambulance attendant, ground (ALS or BLS) or air (fixed or rotary winged); (requires medical review)	23.18	Pertinent documentation to evaluate medical appropriateness should be included when this code is reported. Origin and destination modifiers required. Justification required: * The client weighs 300 pounds or more; or * Client is violent or difficult to move safely; or * More than one client is being transported, and each requires medical attention and/or close monitoring. Note: DSHS pays for an extra attendant in ground ambulance transports only. No payment is made for an extra attendant in air ambulance transports.	2006-07-01
14	NA	A0998	Ambulance response and treatment, no transport	NA	Code not payable. Used for data collection purposes only.	2006-07-01

Read Washington Medicaid Fee Schedules

Physician-Related/Professional Services

Ambulance Transportation