23
Apr
When I first discovered xml2, I was thrilled. A product from RStudio that works with XML! So great! I had some large XML documents I had to parse at work, and my best solution (before we got RStudio) was to drag them one at a time into Microsoft Excel and let its parser convert each into one big, ugly table. Then I was left to either create some elaborate formulas to extract the relevant info, or pass the table on to SAS and try to wrangle it there. Further, the biggest (and arguably most important) XMLs, which contained the most granular data, were over a million rows when parsed by Excel, so they just failed.
Enter xml2. After reading through the documentation and searching SO and the RStudio Community, I felt…disappointed. It seemed that to write something using xml2, you had to know the structure of the XML ahead of time and extract the nodes you needed. I’m prety unfamiliar with XML, but I am comfortable working with rectangular data, so I wanted something that could just convert an entire XML document into something rectangular. Then I could use my usual tidy tools that I love so much. Finally I found this post on the RStudio Community, which is the method I will demonstrate in this post.
I use dplyr’s new across() function, so I need the development version. I had some issue getting it set up; ultimately I had to install the development version of tidyr explicitly and then install dplyr.
# remotes::install_github("r-lib/rlang")
# remotes::install_github("tidyverse/dplyr")
library(xml2) # I though you said we weren't using xml2...
library(tidyr)
library(dplyr)
library(tibble)
library(gt)First we need to find our data. The XML I’m working with comes from something called the External Data Gathering Environment (EDGE) Server, which is used by the federal government in the United States to collect data from health insurance issuers. The data is used for the risk adjustment program under the Affordable Care Act (ACA). You can read a more in-depth summary about all this in my edgedata package README. The Centers for Medicare and Medicaid Services (CMS) provides sample XML files on REGTAP if you have an account, but I’ve downloaded them and put them on my Github in order to write this post (And possibly to make another package with an API for processing them).
ratee <- tempfile(fileext = ".xml")
download.file(
"https://raw.github.com/ArctiCondor/edgexml/master/XML XSD/DDC_RATEE_XSD_XML_083019/RATransferElementsExtract.xml",
destfile = ratee
)
cat(stringr::str_replace_all(readr::read_file(ratee), ">", ">\n"))## <?xml version="1.0" ?>
## <raTransferReport xmlns="http://vo.edge.fm.cms.hhs.gov">
## <includedFileHeader>
## <outboundFileIdentifier>
## 2460f9ad-23bb-4b21-bfb2-378bd060ea81</outboundFileIdentifier>
## <cmsBatchIdentifier>
## CMSBATCHID01</cmsBatchIdentifier>
## <cmsJobIdentifier>
## CMSJOB01</cmsJobIdentifier>
## <snapShotFileName>
## </snapShotFileName>
## <snapShotFileHash>
## </snapShotFileHash>
## <outboundFileGenerationDateTime>
## 2019-07-26T10:42:04</outboundFileGenerationDateTime>
## <interfaceControlReleaseNumber>
## 05.00.24</interfaceControlReleaseNumber>
## <edgeServerVersion>
## EdgeServer_1.00.00_b000157</edgeServerVersion>
## <edgeServerProcessIdentifier>
## 12</edgeServerProcessIdentifier>
## <outboundFileTypeCode>
## RATEE</outboundFileTypeCode>
## <edgeServerIdentifier>
## 104231</edgeServerIdentifier>
## <issuerIdentifier>
## 31492</issuerIdentifier>
## </includedFileHeader>
## <state>
## VT</state>
## <calendarYear>
## 2019</calendarYear>
## <executionType>
## P</executionType>
## <includedPlanIdentifierCategory>
## <planIdentifier>
## 22033VA0019999</planIdentifier>
## <planMarketType>
## 1</planMarketType>
## <exchange>
## On the Exchange</exchange>
## <plansMetalLevel>
## Gold</plansMetalLevel>
## <includedRatingAreaCategory>
## <ratingArea>
## 001</ratingArea>
## <enrolleeMemberMonths>
## 702.799999999999967</enrolleeMemberMonths>
## <enrolleeBillableMonths>
## 702.799999999999967</enrolleeBillableMonths>
## <enrolleeSubscriberMonths>
## 678.466666666666635</enrolleeSubscriberMonths>
## <planLiabilityRiskScore>
## 2.298248339973440</planLiabilityRiskScore>
## <planAllowableRatingFactor>
## 1.296077262379055</planAllowableRatingFactor>
## <planAveragePi>
## 942.988996395370895</planAveragePi>
## <planAgeAvePremium>
## 727.571591422287636</planAgeAvePremium>
## </includedRatingAreaCategory>
## <includedRatingAreaCategory>
## <ratingArea>
## 002</ratingArea>
## <enrolleeMemberMonths>
## 12.166666666666667</enrolleeMemberMonths>
## <enrolleeBillableMonths>
## 12.166666666666667</enrolleeBillableMonths>
## <enrolleeSubscriberMonths>
## 12.166666666666667</enrolleeSubscriberMonths>
## <planLiabilityRiskScore>
## 0.334000000000000</planLiabilityRiskScore>
## <planAllowableRatingFactor>
## 1.302000000000000</planAllowableRatingFactor>
## <planAveragePi>
## 59.671232876712325</planAveragePi>
## <planAgeAvePremium>
## 45.830439997474904</planAgeAvePremium>
## </includedRatingAreaCategory>
## <includedRatingAreaCategory>
## <ratingArea>
## 006</ratingArea>
## <enrolleeMemberMonths>
## 209.866666666666657</enrolleeMemberMonths>
## <enrolleeBillableMonths>
## 209.866666666666657</enrolleeBillableMonths>
## <enrolleeSubscriberMonths>
## 209.866666666666657</enrolleeSubscriberMonths>
## <planLiabilityRiskScore>
## 0.381610864040661</planLiabilityRiskScore>
## <planAllowableRatingFactor>
## 1.302000000000000</planAllowableRatingFactor>
## <planAveragePi>
## 1000.000000000000000</planAveragePi>
## <planAgeAvePremium>
## 768.049155145929339</planAgeAvePremium>
## </includedRatingAreaCategory>
## </includedPlanIdentifierCategory>
## <includedPlanIdentifierCategory>
## <planIdentifier>
## 22033VA0041234</planIdentifier>
## <planMarketType>
## 2</planMarketType>
## <exchange>
## On the Exchange</exchange>
## <plansMetalLevel>
## Gold</plansMetalLevel>
## <includedRatingAreaCategory>
## <ratingArea>
## 001</ratingArea>
## <enrolleeMemberMonths>
## 36.499999999999998</enrolleeMemberMonths>
## <enrolleeBillableMonths>
## 36.499999999999998</enrolleeBillableMonths>
## <enrolleeSubscriberMonths>
## 36.499999999999998</enrolleeSubscriberMonths>
## <planLiabilityRiskScore>
## 4.698370000000000</planLiabilityRiskScore>
## <planAllowableRatingFactor>
## 1.302000000000000</planAllowableRatingFactor>
## <planAveragePi>
## 1000.000000000000000</planAveragePi>
## <planAgeAvePremium>
## 768.049155145929339</planAgeAvePremium>
## </includedRatingAreaCategory>
## </includedPlanIdentifierCategory>
## <includedPlanIdentifierCategory>
## <planIdentifier>
## 22033VA0049999</planIdentifier>
## <planMarketType>
## 2</planMarketType>
## <exchange>
## On the Exchange</exchange>
## <plansMetalLevel>
## Gold</plansMetalLevel>
## <includedRatingAreaCategory>
## <ratingArea>
## 001</ratingArea>
## <enrolleeMemberMonths>
## 97.333333333333328</enrolleeMemberMonths>
## <enrolleeBillableMonths>
## 97.333333333333328</enrolleeBillableMonths>
## <enrolleeSubscriberMonths>
## 97.333333333333328</enrolleeSubscriberMonths>
## <planLiabilityRiskScore>
## 4.698370000000000</planLiabilityRiskScore>
## <planAllowableRatingFactor>
## 1.302000000000000</planAllowableRatingFactor>
## <planAveragePi>
## 937.500000000000000</planAveragePi>
## <planAgeAvePremium>
## 720.046082949308756</planAgeAvePremium>
## </includedRatingAreaCategory>
## </includedPlanIdentifierCategory>
## <includedPlanIdentifierCategory>
## <planIdentifier>
## 22033VA0130001</planIdentifier>
## <planMarketType>
## 1</planMarketType>
## <exchange>
## On the Exchange</exchange>
## <plansMetalLevel>
## Platinum</plansMetalLevel>
## <includedRatingAreaCategory>
## <ratingArea>
## 001</ratingArea>
## <enrolleeMemberMonths>
## 6.033333333333333</enrolleeMemberMonths>
## <enrolleeBillableMonths>
## 6.033333333333333</enrolleeBillableMonths>
## <enrolleeSubscriberMonths>
## 6.033333333333333</enrolleeSubscriberMonths>
## <planLiabilityRiskScore>
## 0.334000000000000</planLiabilityRiskScore>
## <planAllowableRatingFactor>
## 1.302000000000000</planAllowableRatingFactor>
## <planAveragePi>
## 1000.000000000000000</planAveragePi>
## <planAgeAvePremium>
## 768.049155145929339</planAgeAvePremium>
## </includedRatingAreaCategory>
## <includedRatingAreaCategory>
## <ratingArea>
## 006</ratingArea>
## <enrolleeMemberMonths>
## 12.166666666666666</enrolleeMemberMonths>
## <enrolleeBillableMonths>
## 12.166666666666666</enrolleeBillableMonths>
## <enrolleeSubscriberMonths>
## 12.166666666666666</enrolleeSubscriberMonths>
## <planLiabilityRiskScore>
## 0.334000000000000</planLiabilityRiskScore>
## <planAllowableRatingFactor>
## 1.302000000000000</planAllowableRatingFactor>
## <planAveragePi>
## 1000.000000000000000</planAveragePi>
## <planAgeAvePremium>
## 768.049155145929339</planAgeAvePremium>
## </includedRatingAreaCategory>
## </includedPlanIdentifierCategory>
## <includedPlanIdentifierCategory>
## <planIdentifier>
## 22033VA0130014</planIdentifier>
## <planMarketType>
## 1</planMarketType>
## <exchange>
## On the Exchange</exchange>
## <plansMetalLevel>
## Silver</plansMetalLevel>
## <includedRatingAreaCategory>
## <ratingArea>
## 001</ratingArea>
## <enrolleeMemberMonths>
## 79.166666666666663</enrolleeMemberMonths>
## <enrolleeBillableMonths>
## 79.166666666666663</enrolleeBillableMonths>
## <enrolleeSubscriberMonths>
## 79.166666666666663</enrolleeSubscriberMonths>
## <planLiabilityRiskScore>
## 4.329155789473684</planLiabilityRiskScore>
## <planAllowableRatingFactor>
## 1.298311578947368</planAllowableRatingFactor>
## <planAveragePi>
## 945.768421052631571</planAveragePi>
## <planAgeAvePremium>
## 728.460283639642324</planAgeAvePremium>
## </includedRatingAreaCategory>
## <includedRatingAreaCategory>
## <ratingArea>
## 002</ratingArea>
## <enrolleeMemberMonths>
## 11.133333333333333</enrolleeMemberMonths>
## <enrolleeBillableMonths>
## 11.133333333333333</enrolleeBillableMonths>
## <enrolleeSubscriberMonths>
## 11.133333333333333</enrolleeSubscriberMonths>
## <planLiabilityRiskScore>
## 0.273000000000000</planLiabilityRiskScore>
## <planAllowableRatingFactor>
## 1.302000000000000</planAllowableRatingFactor>
## <planAveragePi>
## 1000.000000000000000</planAveragePi>
## <planAgeAvePremium>
## 768.049155145929339</planAgeAvePremium>
## </includedRatingAreaCategory>
## <includedRatingAreaCategory>
## <ratingArea>
## 006</ratingArea>
## <enrolleeMemberMonths>
## 18.166666666666666</enrolleeMemberMonths>
## <enrolleeBillableMonths>
## 18.166666666666666</enrolleeBillableMonths>
## <enrolleeSubscriberMonths>
## 18.166666666666666</enrolleeSubscriberMonths>
## <planLiabilityRiskScore>
## 1.242470458715596</planLiabilityRiskScore>
## <planAllowableRatingFactor>
## 1.302000000000000</planAllowableRatingFactor>
## <planAveragePi>
## 1000.000000000000000</planAveragePi>
## <planAgeAvePremium>
## 768.049155145929339</planAgeAvePremium>
## </includedRatingAreaCategory>
## </includedPlanIdentifierCategory>
## <includedPlanIdentifierCategory>
## <planIdentifier>
## 22033VA0180001</planIdentifier>
## <planMarketType>
## 1</planMarketType>
## <exchange>
## On the Exchange </exchange>
## <plansMetalLevel>
## Platinum</plansMetalLevel>
## <includedRatingAreaCategory>
## <ratingArea>
## 001</ratingArea>
## <enrolleeMemberMonths>
## 36.499999999999998</enrolleeMemberMonths>
## <enrolleeBillableMonths>
## 36.499999999999998</enrolleeBillableMonths>
## <enrolleeSubscriberMonths>
## 36.499999999999998</enrolleeSubscriberMonths>
## <planLiabilityRiskScore>
## 4.603000000000000</planLiabilityRiskScore>
## <planAllowableRatingFactor>
## 1.302000000000000</planAllowableRatingFactor>
## <planAveragePi>
## 1000.000000000000000</planAveragePi>
## <planAgeAvePremium>
## 768.049155145929339</planAgeAvePremium>
## </includedRatingAreaCategory>
## </includedPlanIdentifierCategory>
## <includedPlanIdentifierCategory>
## <planIdentifier>
## 22033VA3332222</planIdentifier>
## <planMarketType>
## 1</planMarketType>
## <exchange>
## On the Exchange</exchange>
## <plansMetalLevel>
## Platinum</plansMetalLevel>
## <includedRatingAreaCategory>
## <ratingArea>
## 001</ratingArea>
## <enrolleeMemberMonths>
## 97.333333333333328</enrolleeMemberMonths>
## <enrolleeBillableMonths>
## 97.333333333333328</enrolleeBillableMonths>
## <enrolleeSubscriberMonths>
## 97.333333333333328</enrolleeSubscriberMonths>
## <planLiabilityRiskScore>
## 3.197750000000000</planLiabilityRiskScore>
## <planAllowableRatingFactor>
## 1.299000000000000</planAllowableRatingFactor>
## <planAveragePi>
## 1000.000000000000000</planAveragePi>
## <planAgeAvePremium>
## 769.822940723633564</planAgeAvePremium>
## </includedRatingAreaCategory>
## <includedRatingAreaCategory>
## <ratingArea>
## 002</ratingArea>
## <enrolleeMemberMonths>
## 60.833333333333332</enrolleeMemberMonths>
## <enrolleeBillableMonths>
## 60.833333333333332</enrolleeBillableMonths>
## <enrolleeSubscriberMonths>
## 60.833333333333332</enrolleeSubscriberMonths>
## <planLiabilityRiskScore>
## 3.834400000000000</planLiabilityRiskScore>
## <planAllowableRatingFactor>
## 1.302000000000000</planAllowableRatingFactor>
## <planAveragePi>
## 178.726027397260269</planAveragePi>
## <planAgeAvePremium>
## 137.270374345053970</planAgeAvePremium>
## </includedRatingAreaCategory>
## </includedPlanIdentifierCategory>
## <includedPlanIdentifierCategory>
## <planIdentifier>
## 22033VA4442222</planIdentifier>
## <planMarketType>
## 2</planMarketType>
## <exchange>
## On the Exchange</exchange>
## <plansMetalLevel>
## Platinum</plansMetalLevel>
## <includedRatingAreaCategory>
## <ratingArea>
## 001</ratingArea>
## <enrolleeMemberMonths>
## 36.499999999999998</enrolleeMemberMonths>
## <enrolleeBillableMonths>
## 36.499999999999998</enrolleeBillableMonths>
## <enrolleeSubscriberMonths>
## 36.499999999999998</enrolleeSubscriberMonths>
## <planLiabilityRiskScore>
## 4.603000000000000</planLiabilityRiskScore>
## <planAllowableRatingFactor>
## 1.294000000000000</planAllowableRatingFactor>
## <planAveragePi>
## 1000.000000000000000</planAveragePi>
## <planAgeAvePremium>
## 772.797527047913447</planAgeAvePremium>
## </includedRatingAreaCategory>
## <includedRatingAreaCategory>
## <ratingArea>
## 002</ratingArea>
## <enrolleeMemberMonths>
## 60.833333333333332</enrolleeMemberMonths>
## <enrolleeBillableMonths>
## 60.833333333333332</enrolleeBillableMonths>
## <enrolleeSubscriberMonths>
## 60.833333333333332</enrolleeSubscriberMonths>
## <planLiabilityRiskScore>
## 3.834400000000000</planLiabilityRiskScore>
## <planAllowableRatingFactor>
## 1.302000000000000</planAllowableRatingFactor>
## <planAveragePi>
## 50.802739726027397</planAveragePi>
## <planAgeAvePremium>
## 39.019001325673884</planAgeAvePremium>
## </includedRatingAreaCategory>
## </includedPlanIdentifierCategory>
## <includedPlanIdentifierCategory>
## <planIdentifier>
## 22033VT0000101</planIdentifier>
## <planMarketType>
## 1</planMarketType>
## <exchange>
## On the Exchange</exchange>
## <plansMetalLevel>
## Silver</plansMetalLevel>
## <includedRatingAreaCategory>
## <ratingArea>
## 001</ratingArea>
## <enrolleeMemberMonths>
## 12.166666666666666</enrolleeMemberMonths>
## <enrolleeBillableMonths>
## 12.166666666666666</enrolleeBillableMonths>
## <enrolleeSubscriberMonths>
## 12.166666666666666</enrolleeSubscriberMonths>
## <planLiabilityRiskScore>
## 4.227000000000000</planLiabilityRiskScore>
## <planAllowableRatingFactor>
## 1.000000000000000</planAllowableRatingFactor>
## <planAveragePi>
## 1000.000000000000000</planAveragePi>
## <planAgeAvePremium>
## 1000.000000000000000</planAgeAvePremium>
## </includedRatingAreaCategory>
## </includedPlanIdentifierCategory>
## <includedPlanIdentifierCategory>
## <planIdentifier>
## 22033VT0000201</planIdentifier>
## <planMarketType>
## 2</planMarketType>
## <exchange>
## On the Exchange</exchange>
## <plansMetalLevel>
## Silver</plansMetalLevel>
## <includedRatingAreaCategory>
## <ratingArea>
## 001</ratingArea>
## <enrolleeMemberMonths>
## 12.166666666666666</enrolleeMemberMonths>
## <enrolleeBillableMonths>
## 12.166666666666666</enrolleeBillableMonths>
## <enrolleeSubscriberMonths>
## 12.166666666666666</enrolleeSubscriberMonths>
## <planLiabilityRiskScore>
## 4.227000000000000</planLiabilityRiskScore>
## <planAllowableRatingFactor>
## 1.000000000000000</planAllowableRatingFactor>
## <planAveragePi>
## 750.000000000000000</planAveragePi>
## <planAgeAvePremium>
## 750.000000000000000</planAgeAvePremium>
## </includedRatingAreaCategory>
## </includedPlanIdentifierCategory>
## </raTransferReport>This is the only section we actually use xml2. The two functions used are read_xml(), which does exactly what it sounds like, reads an XML document, and xml2::as_list(), which converts an XML document to a nested tibble (Just what I was looking for). I’ve specified the package for as_list() so that I remember that this is not rlang::as_list(). The tibble that comes out of as_tibble() is the entire XML nested into the root (raTransferReport), so we use tidyr::unnest_wider() to break it out into a full tibble. Throughout this entire post there are tables that are large and unwieldy, so I’m using the awesome gt package to display them nicely.
tbl_ratee <- read_xml(ratee) %>%
xml2::as_list() %>%
as_tibble() %>%
unnest_wider(raTransferReport)
ratee_str <- capture.output(str(tbl_ratee))
cat(ratee_str, sep = "\n")## tibble [14 x 19] (S3: tbl_df/tbl/data.frame)
## $ outboundFileIdentifier :List of 14
## ..$ :List of 1
## .. ..$ : chr "2460f9ad-23bb-4b21-bfb2-378bd060ea81"
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## $ cmsBatchIdentifier :List of 14
## ..$ :List of 1
## .. ..$ : chr "CMSBATCHID01"
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## $ cmsJobIdentifier :List of 14
## ..$ :List of 1
## .. ..$ : chr "CMSJOB01"
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## $ outboundFileGenerationDateTime:List of 14
## ..$ :List of 1
## .. ..$ : chr "2019-07-26T10:42:04"
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## $ interfaceControlReleaseNumber :List of 14
## ..$ :List of 1
## .. ..$ : chr "05.00.24"
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## $ edgeServerVersion :List of 14
## ..$ :List of 1
## .. ..$ : chr "EdgeServer_1.00.00_b000157"
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## $ edgeServerProcessIdentifier :List of 14
## ..$ :List of 1
## .. ..$ : chr "12"
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## $ outboundFileTypeCode :List of 14
## ..$ :List of 1
## .. ..$ : chr "RATEE"
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## $ edgeServerIdentifier :List of 14
## ..$ :List of 1
## .. ..$ : chr "104231"
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## $ issuerIdentifier :List of 14
## ..$ :List of 1
## .. ..$ : chr "31492"
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## $ ...1 : chr [1:14] NA "VT" "2019" "P" ...
## $ planIdentifier :List of 14
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ :List of 1
## .. ..$ : chr "22033VA0019999"
## ..$ :List of 1
## .. ..$ : chr "22033VA0041234"
## ..$ :List of 1
## .. ..$ : chr "22033VA0049999"
## ..$ :List of 1
## .. ..$ : chr "22033VA0130001"
## ..$ :List of 1
## .. ..$ : chr "22033VA0130014"
## ..$ :List of 1
## .. ..$ : chr "22033VA0180001"
## ..$ :List of 1
## .. ..$ : chr "22033VA3332222"
## ..$ :List of 1
## .. ..$ : chr "22033VA4442222"
## ..$ :List of 1
## .. ..$ : chr "22033VT0000101"
## ..$ :List of 1
## .. ..$ : chr "22033VT0000201"
## $ planMarketType :List of 14
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ :List of 1
## .. ..$ : chr "1"
## ..$ :List of 1
## .. ..$ : chr "2"
## ..$ :List of 1
## .. ..$ : chr "2"
## ..$ :List of 1
## .. ..$ : chr "1"
## ..$ :List of 1
## .. ..$ : chr "1"
## ..$ :List of 1
## .. ..$ : chr "1"
## ..$ :List of 1
## .. ..$ : chr "1"
## ..$ :List of 1
## .. ..$ : chr "2"
## ..$ :List of 1
## .. ..$ : chr "1"
## ..$ :List of 1
## .. ..$ : chr "2"
## $ exchange :List of 14
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ :List of 1
## .. ..$ : chr "On the Exchange"
## ..$ :List of 1
## .. ..$ : chr "On the Exchange"
## ..$ :List of 1
## .. ..$ : chr "On the Exchange"
## ..$ :List of 1
## .. ..$ : chr "On the Exchange"
## ..$ :List of 1
## .. ..$ : chr "On the Exchange"
## ..$ :List of 1
## .. ..$ : chr " On the Exchange "
## ..$ :List of 1
## .. ..$ : chr "On the Exchange"
## ..$ :List of 1
## .. ..$ : chr "On the Exchange"
## ..$ :List of 1
## .. ..$ : chr "On the Exchange"
## ..$ :List of 1
## .. ..$ : chr "On the Exchange"
## $ plansMetalLevel :List of 14
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ :List of 1
## .. ..$ : chr "Gold"
## ..$ :List of 1
## .. ..$ : chr "Gold"
## ..$ :List of 1
## .. ..$ : chr "Gold"
## ..$ :List of 1
## .. ..$ : chr "Platinum"
## ..$ :List of 1
## .. ..$ : chr "Silver"
## ..$ :List of 1
## .. ..$ : chr "Platinum"
## ..$ :List of 1
## .. ..$ : chr "Platinum"
## ..$ :List of 1
## .. ..$ : chr "Platinum"
## ..$ :List of 1
## .. ..$ : chr "Silver"
## ..$ :List of 1
## .. ..$ : chr "Silver"
## $ includedRatingAreaCategory...5:List of 14
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ :List of 8
## .. ..$ ratingArea :List of 1
## .. .. ..$ : chr "001"
## .. ..$ enrolleeMemberMonths :List of 1
## .. .. ..$ : chr "702.799999999999967"
## .. ..$ enrolleeBillableMonths :List of 1
## .. .. ..$ : chr "702.799999999999967"
## .. ..$ enrolleeSubscriberMonths :List of 1
## .. .. ..$ : chr "678.466666666666635"
## .. ..$ planLiabilityRiskScore :List of 1
## .. .. ..$ : chr "2.298248339973440"
## .. ..$ planAllowableRatingFactor:List of 1
## .. .. ..$ : chr "1.296077262379055"
## .. ..$ planAveragePi :List of 1
## .. .. ..$ : chr "942.988996395370895"
## .. ..$ planAgeAvePremium :List of 1
## .. .. ..$ : chr "727.571591422287636"
## ..$ : NULL
## ..$ : NULL
## ..$ :List of 8
## .. ..$ ratingArea :List of 1
## .. .. ..$ : chr "001"
## .. ..$ enrolleeMemberMonths :List of 1
## .. .. ..$ : chr "6.033333333333333"
## .. ..$ enrolleeBillableMonths :List of 1
## .. .. ..$ : chr "6.033333333333333"
## .. ..$ enrolleeSubscriberMonths :List of 1
## .. .. ..$ : chr "6.033333333333333"
## .. ..$ planLiabilityRiskScore :List of 1
## .. .. ..$ : chr "0.334000000000000"
## .. ..$ planAllowableRatingFactor:List of 1
## .. .. ..$ : chr "1.302000000000000"
## .. ..$ planAveragePi :List of 1
## .. .. ..$ : chr "1000.000000000000000"
## .. ..$ planAgeAvePremium :List of 1
## .. .. ..$ : chr "768.049155145929339"
## ..$ :List of 8
## .. ..$ ratingArea :List of 1
## .. .. ..$ : chr "001"
## .. ..$ enrolleeMemberMonths :List of 1
## .. .. ..$ : chr "79.166666666666663"
## .. ..$ enrolleeBillableMonths :List of 1
## .. .. ..$ : chr "79.166666666666663"
## .. ..$ enrolleeSubscriberMonths :List of 1
## .. .. ..$ : chr "79.166666666666663"
## .. ..$ planLiabilityRiskScore :List of 1
## .. .. ..$ : chr "4.329155789473684"
## .. ..$ planAllowableRatingFactor:List of 1
## .. .. ..$ : chr "1.298311578947368"
## .. ..$ planAveragePi :List of 1
## .. .. ..$ : chr "945.768421052631571"
## .. ..$ planAgeAvePremium :List of 1
## .. .. ..$ : chr "728.460283639642324"
## ..$ : NULL
## ..$ :List of 8
## .. ..$ ratingArea :List of 1
## .. .. ..$ : chr "001"
## .. ..$ enrolleeMemberMonths :List of 1
## .. .. ..$ : chr "97.333333333333328"
## .. ..$ enrolleeBillableMonths :List of 1
## .. .. ..$ : chr "97.333333333333328"
## .. ..$ enrolleeSubscriberMonths :List of 1
## .. .. ..$ : chr "97.333333333333328"
## .. ..$ planLiabilityRiskScore :List of 1
## .. .. ..$ : chr "3.197750000000000"
## .. ..$ planAllowableRatingFactor:List of 1
## .. .. ..$ : chr "1.299000000000000"
## .. ..$ planAveragePi :List of 1
## .. .. ..$ : chr "1000.000000000000000"
## .. ..$ planAgeAvePremium :List of 1
## .. .. ..$ : chr "769.822940723633564"
## ..$ :List of 8
## .. ..$ ratingArea :List of 1
## .. .. ..$ : chr "001"
## .. ..$ enrolleeMemberMonths :List of 1
## .. .. ..$ : chr "36.499999999999998"
## .. ..$ enrolleeBillableMonths :List of 1
## .. .. ..$ : chr "36.499999999999998"
## .. ..$ enrolleeSubscriberMonths :List of 1
## .. .. ..$ : chr "36.499999999999998"
## .. ..$ planLiabilityRiskScore :List of 1
## .. .. ..$ : chr "4.603000000000000"
## .. ..$ planAllowableRatingFactor:List of 1
## .. .. ..$ : chr "1.294000000000000"
## .. ..$ planAveragePi :List of 1
## .. .. ..$ : chr "1000.000000000000000"
## .. ..$ planAgeAvePremium :List of 1
## .. .. ..$ : chr "772.797527047913447"
## ..$ : NULL
## ..$ : NULL
## $ includedRatingAreaCategory...6:List of 14
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ :List of 8
## .. ..$ ratingArea :List of 1
## .. .. ..$ : chr "002"
## .. ..$ enrolleeMemberMonths :List of 1
## .. .. ..$ : chr "12.166666666666667"
## .. ..$ enrolleeBillableMonths :List of 1
## .. .. ..$ : chr "12.166666666666667"
## .. ..$ enrolleeSubscriberMonths :List of 1
## .. .. ..$ : chr "12.166666666666667"
## .. ..$ planLiabilityRiskScore :List of 1
## .. .. ..$ : chr "0.334000000000000"
## .. ..$ planAllowableRatingFactor:List of 1
## .. .. ..$ : chr "1.302000000000000"
## .. ..$ planAveragePi :List of 1
## .. .. ..$ : chr "59.671232876712325"
## .. ..$ planAgeAvePremium :List of 1
## .. .. ..$ : chr "45.830439997474904"
## ..$ : NULL
## ..$ : NULL
## ..$ :List of 8
## .. ..$ ratingArea :List of 1
## .. .. ..$ : chr "006"
## .. ..$ enrolleeMemberMonths :List of 1
## .. .. ..$ : chr "12.166666666666666"
## .. ..$ enrolleeBillableMonths :List of 1
## .. .. ..$ : chr "12.166666666666666"
## .. ..$ enrolleeSubscriberMonths :List of 1
## .. .. ..$ : chr "12.166666666666666"
## .. ..$ planLiabilityRiskScore :List of 1
## .. .. ..$ : chr "0.334000000000000"
## .. ..$ planAllowableRatingFactor:List of 1
## .. .. ..$ : chr "1.302000000000000"
## .. ..$ planAveragePi :List of 1
## .. .. ..$ : chr "1000.000000000000000"
## .. ..$ planAgeAvePremium :List of 1
## .. .. ..$ : chr "768.049155145929339"
## ..$ :List of 8
## .. ..$ ratingArea :List of 1
## .. .. ..$ : chr "002"
## .. ..$ enrolleeMemberMonths :List of 1
## .. .. ..$ : chr "11.133333333333333"
## .. ..$ enrolleeBillableMonths :List of 1
## .. .. ..$ : chr "11.133333333333333"
## .. ..$ enrolleeSubscriberMonths :List of 1
## .. .. ..$ : chr "11.133333333333333"
## .. ..$ planLiabilityRiskScore :List of 1
## .. .. ..$ : chr "0.273000000000000"
## .. ..$ planAllowableRatingFactor:List of 1
## .. .. ..$ : chr "1.302000000000000"
## .. ..$ planAveragePi :List of 1
## .. .. ..$ : chr "1000.000000000000000"
## .. ..$ planAgeAvePremium :List of 1
## .. .. ..$ : chr "768.049155145929339"
## ..$ : NULL
## ..$ :List of 8
## .. ..$ ratingArea :List of 1
## .. .. ..$ : chr "002"
## .. ..$ enrolleeMemberMonths :List of 1
## .. .. ..$ : chr "60.833333333333332"
## .. ..$ enrolleeBillableMonths :List of 1
## .. .. ..$ : chr "60.833333333333332"
## .. ..$ enrolleeSubscriberMonths :List of 1
## .. .. ..$ : chr "60.833333333333332"
## .. ..$ planLiabilityRiskScore :List of 1
## .. .. ..$ : chr "3.834400000000000"
## .. ..$ planAllowableRatingFactor:List of 1
## .. .. ..$ : chr "1.302000000000000"
## .. ..$ planAveragePi :List of 1
## .. .. ..$ : chr "178.726027397260269"
## .. ..$ planAgeAvePremium :List of 1
## .. .. ..$ : chr "137.270374345053970"
## ..$ :List of 8
## .. ..$ ratingArea :List of 1
## .. .. ..$ : chr "002"
## .. ..$ enrolleeMemberMonths :List of 1
## .. .. ..$ : chr "60.833333333333332"
## .. ..$ enrolleeBillableMonths :List of 1
## .. .. ..$ : chr "60.833333333333332"
## .. ..$ enrolleeSubscriberMonths :List of 1
## .. .. ..$ : chr "60.833333333333332"
## .. ..$ planLiabilityRiskScore :List of 1
## .. .. ..$ : chr "3.834400000000000"
## .. ..$ planAllowableRatingFactor:List of 1
## .. .. ..$ : chr "1.302000000000000"
## .. ..$ planAveragePi :List of 1
## .. .. ..$ : chr "50.802739726027397"
## .. ..$ planAgeAvePremium :List of 1
## .. .. ..$ : chr "39.019001325673884"
## ..$ : NULL
## ..$ : NULL
## $ includedRatingAreaCategory...7:List of 14
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ :List of 8
## .. ..$ ratingArea :List of 1
## .. .. ..$ : chr "006"
## .. ..$ enrolleeMemberMonths :List of 1
## .. .. ..$ : chr "209.866666666666657"
## .. ..$ enrolleeBillableMonths :List of 1
## .. .. ..$ : chr "209.866666666666657"
## .. ..$ enrolleeSubscriberMonths :List of 1
## .. .. ..$ : chr "209.866666666666657"
## .. ..$ planLiabilityRiskScore :List of 1
## .. .. ..$ : chr "0.381610864040661"
## .. ..$ planAllowableRatingFactor:List of 1
## .. .. ..$ : chr "1.302000000000000"
## .. ..$ planAveragePi :List of 1
## .. .. ..$ : chr "1000.000000000000000"
## .. ..$ planAgeAvePremium :List of 1
## .. .. ..$ : chr "768.049155145929339"
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ :List of 8
## .. ..$ ratingArea :List of 1
## .. .. ..$ : chr "006"
## .. ..$ enrolleeMemberMonths :List of 1
## .. .. ..$ : chr "18.166666666666666"
## .. ..$ enrolleeBillableMonths :List of 1
## .. .. ..$ : chr "18.166666666666666"
## .. ..$ enrolleeSubscriberMonths :List of 1
## .. .. ..$ : chr "18.166666666666666"
## .. ..$ planLiabilityRiskScore :List of 1
## .. .. ..$ : chr "1.242470458715596"
## .. ..$ planAllowableRatingFactor:List of 1
## .. .. ..$ : chr "1.302000000000000"
## .. ..$ planAveragePi :List of 1
## .. .. ..$ : chr "1000.000000000000000"
## .. ..$ planAgeAvePremium :List of 1
## .. .. ..$ : chr "768.049155145929339"
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## $ includedRatingAreaCategory :List of 14
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ : NULL
## ..$ :List of 8
## .. ..$ ratingArea :List of 1
## .. .. ..$ : chr "001"
## .. ..$ enrolleeMemberMonths :List of 1
## .. .. ..$ : chr "36.499999999999998"
## .. ..$ enrolleeBillableMonths :List of 1
## .. .. ..$ : chr "36.499999999999998"
## .. ..$ enrolleeSubscriberMonths :List of 1
## .. .. ..$ : chr "36.499999999999998"
## .. ..$ planLiabilityRiskScore :List of 1
## .. .. ..$ : chr "4.698370000000000"
## .. ..$ planAllowableRatingFactor:List of 1
## .. .. ..$ : chr "1.302000000000000"
## .. ..$ planAveragePi :List of 1
## .. .. ..$ : chr "1000.000000000000000"
## .. ..$ planAgeAvePremium :List of 1
## .. .. ..$ : chr "768.049155145929339"
## ..$ :List of 8
## .. ..$ ratingArea :List of 1
## .. .. ..$ : chr "001"
## .. ..$ enrolleeMemberMonths :List of 1
## .. .. ..$ : chr "97.333333333333328"
## .. ..$ enrolleeBillableMonths :List of 1
## .. .. ..$ : chr "97.333333333333328"
## .. ..$ enrolleeSubscriberMonths :List of 1
## .. .. ..$ : chr "97.333333333333328"
## .. ..$ planLiabilityRiskScore :List of 1
## .. .. ..$ : chr "4.698370000000000"
## .. ..$ planAllowableRatingFactor:List of 1
## .. .. ..$ : chr "1.302000000000000"
## .. ..$ planAveragePi :List of 1
## .. .. ..$ : chr "937.500000000000000"
## .. ..$ planAgeAvePremium :List of 1
## .. .. ..$ : chr "720.046082949308756"
## ..$ : NULL
## ..$ : NULL
## ..$ :List of 8
## .. ..$ ratingArea :List of 1
## .. .. ..$ : chr "001"
## .. ..$ enrolleeMemberMonths :List of 1
## .. .. ..$ : chr "36.499999999999998"
## .. ..$ enrolleeBillableMonths :List of 1
## .. .. ..$ : chr "36.499999999999998"
## .. ..$ enrolleeSubscriberMonths :List of 1
## .. .. ..$ : chr "36.499999999999998"
## .. ..$ planLiabilityRiskScore :List of 1
## .. .. ..$ : chr "4.603000000000000"
## .. ..$ planAllowableRatingFactor:List of 1
## .. .. ..$ : chr "1.302000000000000"
## .. ..$ planAveragePi :List of 1
## .. .. ..$ : chr "1000.000000000000000"
## .. ..$ planAgeAvePremium :List of 1
## .. .. ..$ : chr "768.049155145929339"
## ..$ : NULL
## ..$ : NULL
## ..$ :List of 8
## .. ..$ ratingArea :List of 1
## .. .. ..$ : chr "001"
## .. ..$ enrolleeMemberMonths :List of 1
## .. .. ..$ : chr "12.166666666666666"
## .. ..$ enrolleeBillableMonths :List of 1
## .. .. ..$ : chr "12.166666666666666"
## .. ..$ enrolleeSubscriberMonths :List of 1
## .. .. ..$ : chr "12.166666666666666"
## .. ..$ planLiabilityRiskScore :List of 1
## .. .. ..$ : chr "4.227000000000000"
## .. ..$ planAllowableRatingFactor:List of 1
## .. .. ..$ : chr "1.000000000000000"
## .. ..$ planAveragePi :List of 1
## .. .. ..$ : chr "1000.000000000000000"
## .. ..$ planAgeAvePremium :List of 1
## .. .. ..$ : chr "1000.000000000000000"
## ..$ :List of 8
## .. ..$ ratingArea :List of 1
## .. .. ..$ : chr "001"
## .. ..$ enrolleeMemberMonths :List of 1
## .. .. ..$ : chr "12.166666666666666"
## .. ..$ enrolleeBillableMonths :List of 1
## .. .. ..$ : chr "12.166666666666666"
## .. ..$ enrolleeSubscriberMonths :List of 1
## .. .. ..$ : chr "12.166666666666666"
## .. ..$ planLiabilityRiskScore :List of 1
## .. .. ..$ : chr "4.227000000000000"
## .. ..$ planAllowableRatingFactor:List of 1
## .. .. ..$ : chr "1.000000000000000"
## .. ..$ planAveragePi :List of 1
## .. .. ..$ : chr "750.000000000000000"
## .. ..$ planAgeAvePremium :List of 1
## .. .. ..$ : chr "750.000000000000000"So now we have a tibble, but it doesn’t look very tidy. There are all these NAs (which are actually "NA"s?) There are some nested lists and also a column, includedRatingAreaCategory, that apparently repeated 8 times. Let’s clean this up a bit. I know that the first few columns contain header metadata about how the submission to the EDGE server was made, so we’ll split those off into their own table. Then remove all the empty rows, and finally unnest the list columns. gt() actually unlists what it can really nicely, but if you print(tbl_ratee) instead of piping it into a gt() call, you see that all the columns are actually still list columns.
tbl_head <- tbl_ratee %>%
select(outboundFileIdentifier:issuerIdentifier) %>%
filter(outboundFileIdentifier != "NULL") %>%
unnest(cols = names(.))
tbl_head %>%
gt()| outboundFileIdentifier | cmsBatchIdentifier | cmsJobIdentifier | outboundFileGenerationDateTime | interfaceControlReleaseNumber | edgeServerVersion | edgeServerProcessIdentifier | outboundFileTypeCode | edgeServerIdentifier | issuerIdentifier |
|---|---|---|---|---|---|---|---|---|---|
| 2460f9ad-23bb-4b21-bfb2-378bd060ea81 | CMSBATCHID01 | CMSJOB01 | 2019-07-26T10:42:04 | 05.00.24 | EdgeServer_1.00.00_b000157 | 12 | RATEE | 104231 | 31492 |
Great! That looks much nicer. This metadata can be used to align with other reports created by the EDGE server in the same production run.
Now let’s tackle the rest! There’s a small unnamed column (...1) right after the header info that also seems like metadata to me. It looks like it has a state code, a year, and a P, which typically means production (as opposed to test or validation) when working with EDGE. I’m not concerned with those, so I’m just going to drop that column. Then comes the meat of the report. We’re going to filter out "NA"s again and unnest, which leaves us with a table that has a plan identifier, a market type, exchange status and metal level, followed by some more list columns. Using the very handy pivot_longer() function, we transpose any column that has to do with rating area. The new name column is not important anymore. These are just auto-generated names. We have some more "NA"s to remove, and we’re looking pretty good!
tbl_body <- tbl_ratee %>%
select(planIdentifier:includedRatingAreaCategory) %>%
filter(planIdentifier != "NA") %>%
unnest(1:4) %>%
pivot_longer(cols = starts_with("includedRatingAreaCategory")) %>%
select(-name) %>%
filter(value != "NULL")
tbl_body %>%
gt()| planIdentifier | planMarketType | exchange | plansMetalLevel | value |
|---|---|---|---|---|
| 22033VA0019999 | 1 | On the Exchange | Gold | list("001"), list("702.799999999999967"), list("702.799999999999967"), list("678.466666666666635"), list("2.298248339973440"), list("1.296077262379055"), list("942.988996395370895"), list("727.571591422287636") |
| 22033VA0019999 | 1 | On the Exchange | Gold | list("002"), list("12.166666666666667"), list("12.166666666666667"), list("12.166666666666667"), list("0.334000000000000"), list("1.302000000000000"), list("59.671232876712325"), list("45.830439997474904") |
| 22033VA0019999 | 1 | On the Exchange | Gold | list("006"), list("209.866666666666657"), list("209.866666666666657"), list("209.866666666666657"), list("0.381610864040661"), list("1.302000000000000"), list("1000.000000000000000"), list("768.049155145929339") |
| 22033VA0041234 | 2 | On the Exchange | Gold | list("001"), list("36.499999999999998"), list("36.499999999999998"), list("36.499999999999998"), list("4.698370000000000"), list("1.302000000000000"), list("1000.000000000000000"), list("768.049155145929339") |
| 22033VA0049999 | 2 | On the Exchange | Gold | list("001"), list("97.333333333333328"), list("97.333333333333328"), list("97.333333333333328"), list("4.698370000000000"), list("1.302000000000000"), list("937.500000000000000"), list("720.046082949308756") |
| 22033VA0130001 | 1 | On the Exchange | Platinum | list("001"), list("6.033333333333333"), list("6.033333333333333"), list("6.033333333333333"), list("0.334000000000000"), list("1.302000000000000"), list("1000.000000000000000"), list("768.049155145929339") |
| 22033VA0130001 | 1 | On the Exchange | Platinum | list("006"), list("12.166666666666666"), list("12.166666666666666"), list("12.166666666666666"), list("0.334000000000000"), list("1.302000000000000"), list("1000.000000000000000"), list("768.049155145929339") |
| 22033VA0130014 | 1 | On the Exchange | Silver | list("001"), list("79.166666666666663"), list("79.166666666666663"), list("79.166666666666663"), list("4.329155789473684"), list("1.298311578947368"), list("945.768421052631571"), list("728.460283639642324") |
| 22033VA0130014 | 1 | On the Exchange | Silver | list("002"), list("11.133333333333333"), list("11.133333333333333"), list("11.133333333333333"), list("0.273000000000000"), list("1.302000000000000"), list("1000.000000000000000"), list("768.049155145929339") |
| 22033VA0130014 | 1 | On the Exchange | Silver | list("006"), list("18.166666666666666"), list("18.166666666666666"), list("18.166666666666666"), list("1.242470458715596"), list("1.302000000000000"), list("1000.000000000000000"), list("768.049155145929339") |
| 22033VA0180001 | 1 | On the Exchange | Platinum | list("001"), list("36.499999999999998"), list("36.499999999999998"), list("36.499999999999998"), list("4.603000000000000"), list("1.302000000000000"), list("1000.000000000000000"), list("768.049155145929339") |
| 22033VA3332222 | 1 | On the Exchange | Platinum | list("001"), list("97.333333333333328"), list("97.333333333333328"), list("97.333333333333328"), list("3.197750000000000"), list("1.299000000000000"), list("1000.000000000000000"), list("769.822940723633564") |
| 22033VA3332222 | 1 | On the Exchange | Platinum | list("002"), list("60.833333333333332"), list("60.833333333333332"), list("60.833333333333332"), list("3.834400000000000"), list("1.302000000000000"), list("178.726027397260269"), list("137.270374345053970") |
| 22033VA4442222 | 2 | On the Exchange | Platinum | list("001"), list("36.499999999999998"), list("36.499999999999998"), list("36.499999999999998"), list("4.603000000000000"), list("1.294000000000000"), list("1000.000000000000000"), list("772.797527047913447") |
| 22033VA4442222 | 2 | On the Exchange | Platinum | list("002"), list("60.833333333333332"), list("60.833333333333332"), list("60.833333333333332"), list("3.834400000000000"), list("1.302000000000000"), list("50.802739726027397"), list("39.019001325673884") |
| 22033VT0000101 | 1 | On the Exchange | Silver | list("001"), list("12.166666666666666"), list("12.166666666666666"), list("12.166666666666666"), list("4.227000000000000"), list("1.000000000000000"), list("1000.000000000000000"), list("1000.000000000000000") |
| 22033VT0000201 | 2 | On the Exchange | Silver | list("001"), list("12.166666666666666"), list("12.166666666666666"), list("12.166666666666666"), list("4.227000000000000"), list("1.000000000000000"), list("750.000000000000000"), list("750.000000000000000") |
It looks like there are a few different plan IDs, and each one has a list associated with it that has to do with rating area. Let’s tweak it a little more to see what those lists are about.
tbl_main <- tbl_body %>%
unnest_wider(value) %>%
unnest(cols = names(.)) %>%
unnest(cols = names(.))
tbl_main %>%
mutate(across(enrolleeMemberMonths:planAgeAvePremium, as.numeric)) %>%
gt() %>%
fmt_number(columns = 6:last_col())| planIdentifier | planMarketType | exchange | plansMetalLevel | ratingArea | enrolleeMemberMonths | enrolleeBillableMonths | enrolleeSubscriberMonths | planLiabilityRiskScore | planAllowableRatingFactor | planAveragePi | planAgeAvePremium |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 22033VA0019999 | 1 | On the Exchange | Gold | 001 | 702.80 | 702.80 | 678.47 | 2.30 | 1.30 | 942.99 | 727.57 |
| 22033VA0019999 | 1 | On the Exchange | Gold | 002 | 12.17 | 12.17 | 12.17 | 0.33 | 1.30 | 59.67 | 45.83 |
| 22033VA0019999 | 1 | On the Exchange | Gold | 006 | 209.87 | 209.87 | 209.87 | 0.38 | 1.30 | 1,000.00 | 768.05 |
| 22033VA0041234 | 2 | On the Exchange | Gold | 001 | 36.50 | 36.50 | 36.50 | 4.70 | 1.30 | 1,000.00 | 768.05 |
| 22033VA0049999 | 2 | On the Exchange | Gold | 001 | 97.33 | 97.33 | 97.33 | 4.70 | 1.30 | 937.50 | 720.05 |
| 22033VA0130001 | 1 | On the Exchange | Platinum | 001 | 6.03 | 6.03 | 6.03 | 0.33 | 1.30 | 1,000.00 | 768.05 |
| 22033VA0130001 | 1 | On the Exchange | Platinum | 006 | 12.17 | 12.17 | 12.17 | 0.33 | 1.30 | 1,000.00 | 768.05 |
| 22033VA0130014 | 1 | On the Exchange | Silver | 001 | 79.17 | 79.17 | 79.17 | 4.33 | 1.30 | 945.77 | 728.46 |
| 22033VA0130014 | 1 | On the Exchange | Silver | 002 | 11.13 | 11.13 | 11.13 | 0.27 | 1.30 | 1,000.00 | 768.05 |
| 22033VA0130014 | 1 | On the Exchange | Silver | 006 | 18.17 | 18.17 | 18.17 | 1.24 | 1.30 | 1,000.00 | 768.05 |
| 22033VA0180001 | 1 | On the Exchange | Platinum | 001 | 36.50 | 36.50 | 36.50 | 4.60 | 1.30 | 1,000.00 | 768.05 |
| 22033VA3332222 | 1 | On the Exchange | Platinum | 001 | 97.33 | 97.33 | 97.33 | 3.20 | 1.30 | 1,000.00 | 769.82 |
| 22033VA3332222 | 1 | On the Exchange | Platinum | 002 | 60.83 | 60.83 | 60.83 | 3.83 | 1.30 | 178.73 | 137.27 |
| 22033VA4442222 | 2 | On the Exchange | Platinum | 001 | 36.50 | 36.50 | 36.50 | 4.60 | 1.29 | 1,000.00 | 772.80 |
| 22033VA4442222 | 2 | On the Exchange | Platinum | 002 | 60.83 | 60.83 | 60.83 | 3.83 | 1.30 | 50.80 | 39.02 |
| 22033VT0000101 | 1 | On the Exchange | Silver | 001 | 12.17 | 12.17 | 12.17 | 4.23 | 1.00 | 1,000.00 | 1,000.00 |
| 22033VT0000201 | 2 | On the Exchange | Silver | 001 | 12.17 | 12.17 | 12.17 | 4.23 | 1.00 | 750.00 | 750.00 |
Okay, this is what I wanted! We have member months and the main variables used in the risk adjustment formula. If this was a RATEE file for a real issuer, this could be used to check against internal data to make sure that the data on the EDGE server was accurate. I do want to investigate one strange thing: It looks like there are only 3 rating areas, but they’re numbered 001, 002, and 006. But when I scroll through the full XML text above, it looks like that is accurate! I don’t know why CMS made a sample document with these strange area labels, but at least we’re not dropping information somewhere.