Generate Nested JSON Using R

Question

I am trying to generate a nested JSON file using R, and am having difficulties in setting it up. I am admittedly very new to JSON, so conceptually I'm behind. I would appreciate any help- including advice on places (other than R) that would make this task easier.

I have 3 datasets I'm working with- all connected via keys. Essentially, my objective is to create a JSON file that contains patient objects, with nested encounters & within those encounters, nested claim lines.

Here's the set up in R:

patients <- data.frame (
                      PatientID = c('PID01','PID02'),
                      PatientName = c('John Doe','Jane Doe'),
                      PatientGroup = c('Group A','Group B')
                    )

encounters <- data.frame (
                          EncounterID = c('Enc01','Enc02','Enc03','Enc04','Enc05'),
                          PatientID = c('PID01','PID01','PID02','PID02','PID02'),
                          EncounterType = c('Outpatient','Outpatient','Inpatient','Outpatient','SNF')
                         )

encounterLines <- data.frame (
                              EncounterID = c(rep('Enc01',5),rep('Enc04',2)),
                              RevCodes = c('001','100','200','300','400','001','100'),
                              ClaimLine = c(seq(1:5),seq(1:2))
                              )

 ----------------------------------------
 PatientID   PatientName   PatientGroup 
----------- ------------- --------------
   PID01      John Doe       Group A    

   PID02      Jane Doe       Group B    
----------------------------------------

-----------------------------------------
 EncounterID   PatientID   EncounterType 
------------- ----------- ---------------
    Enc01        PID01      Outpatient   

    Enc02        PID01      Outpatient   

    Enc03        PID02       Inpatient   

    Enc04        PID02      Outpatient   

    Enc05        PID02          SNF      
-----------------------------------------

------------------------------------
 EncounterID   RevCodes   ClaimLine 
------------- ---------- -----------
    Enc01        001          1     

    Enc01        100          2     

    Enc01        200          3     

    Enc01        300          4     

    Enc01        400          5     

    Enc04        001          1     

    Enc04        100          2     
------------------------------------

The JSON output I'm looking for is as follows. I've tried using jsonlite but I can't seem to get any real traction:

[
    {
        "PatientID": "PID01",
        "PatientName": "John Doe",
        "PatientGroup": "Group A",
        "Encounters": [
            {
                "EncounterID": "Enc01",
                "EncounterType": "Outpatient",
                "ClaimLines": [
                    {
                        "ClaimLine": 1,
                        "RevenueCode": "001"
                    },
                    {
                        "ClaimLine": 2,
                        "RevenueCode": "100"
                    },
                    {
                        "ClaimLine": 3,
                        "RevenueCode": "200"
                    },
                    {
                        "ClaimLine": 4,
                        "RevenueCode": "300"
                    },
                    {
                        "ClaimLine": 5,
                        "RevenueCode": "400"
                    }
                ]
            },
            {
                "EncounterID": "Enc02",
                "EncounterType": "Outpatient"
            }
        ]
    },
    {
        "PatientID": "PID02",
        "PatientName": "Jane Doe",
        "PatientGroup": "Group B",
        "Encounters": [
            {
                "EncounterID": "Enc03",
                "EncounterType": "Inpatient"
            },
            {
                "EncounterID": "Enc04",
                "EncounterType": "Outpatient",
                "ClaimLines": [
                    {
                        "ClaimLine": 1,
                        "RevenueCode": "001"
                    },
                    {
                        "ClaimLine": 2,
                        "RevenueCode": "100"
                    }
                ]
            },
            {
                "EncounterID": "Enc05",
                "EncounterType": "SNF"
            }
        ]
    }
]

Any/all help would be GREATLY appreciated. As I mentioned before, I'm not opposed to using tools outside R.

Thanks!

Answer 1

A couple of data.table joins and you can get there too

library(data.table)
setDT( patients )
setDT( encounters )
setDT( encounterLines )

## do a 'left-join' by putting 
## encounterLines onto encounters
dt_encounters <- encounterLines[
  encounters
  , on = "EncounterID"
  , nomatch = NA       ## indicates left-join
]

## make a list-column (required for JSON)
dt_encounters <- dt_encounters[
  , .(ClaimLines = list(.SD))
  , by = .(EncounterID, EncounterType, PatientID)
]

## do a 'left-join' by putting
## encounters onto patients
dt_patients <- dt_encounters[
  patients
  , on = "PatientID"
  , nomatch = NA
]

## make a list-column (required for JSON)
dt_patients <- dt_patients[
  , .(Encounters = list(.SD))
  , by = .(PatientID, PatientName, PatientGroup)
] 

toJSON( dt_patients, pretty = TRUE )

[
  {
    "PatientID": "PID01",
    "PatientName": "John Doe",
    "PatientGroup": "Group A",
    "Encounters": [
      {
        "EncounterID": "Enc01",
        "EncounterType": "Outpatient",
        "ClaimLines": [
          {
            "RevCodes": "001",
            "ClaimLine": 1
          },
          {
            "RevCodes": "100",
            "ClaimLine": 2
          },
          {
            "RevCodes": "200",
            "ClaimLine": 3
          },
          {
            "RevCodes": "300",
            "ClaimLine": 4
          },
          {
            "RevCodes": "400",
            "ClaimLine": 5
          }
        ]
      },
      {
        "EncounterID": "Enc02",
        "EncounterType": "Outpatient",
        "ClaimLines": [
          {}
        ]
      }
    ]
  },
  {
    "PatientID": "PID02",
    "PatientName": "Jane Doe",
    "PatientGroup": "Group B",
    "Encounters": [
      {
        "EncounterID": "Enc03",
        "EncounterType": "Inpatient",
        "ClaimLines": [
          {}
        ]
      },
      {
        "EncounterID": "Enc04",
        "EncounterType": "Outpatient",
        "ClaimLines": [
          {
            "RevCodes": "001",
            "ClaimLine": 1
          },
          {
            "RevCodes": "100",
            "ClaimLine": 2
          }
        ]
      },
      {
        "EncounterID": "Enc05",
        "EncounterType": "SNF",
        "ClaimLines": [
          {}
        ]
      }
    ]
  }
]

Answer 2

This seems to get very close

library(tidyverse)
library(jsonlite)

json <- reduce(list(
    patients %>% mutate_if(is.factor, as.character),
    encounters %>% mutate_if(is.factor, as.character),
    encounterLines %>%
        mutate_if(is.factor, as.character) %>%
        group_by(EncounterID) %>%
        nest() %>%
        rename(ClaimLines = data) %>%
        mutate(ClaimLines = map(ClaimLines, transpose))),
    left_join) %>%
    nest(Encounters = c(EncounterID, EncounterType, ClaimLines)) %>%
    transpose() %>%
    toJSON(pretty = TRUE)
#[
#  {
#    "PatientID": ["PID01"],
#    "PatientName": ["John Doe"],
#    "PatientGroup": ["Group A"],
#    "Encounters": [
#      {
#        "EncounterID": "Enc01",
#        "EncounterType": "Outpatient",
#        "ClaimLines": [
#          {
#            "RevCodes": ["001"],
#            "ClaimLine": [1]
#          },
#          {
#            "RevCodes": ["100"],
#            "ClaimLine": [2]
#          },
#          {
#            "RevCodes": ["200"],
#            "ClaimLine": [3]
#          },
#          {
#            "RevCodes": ["300"],
#            "ClaimLine": [4]
#          },
#          {
#            "RevCodes": ["400"],
#            "ClaimLine": [5]
#          }
#        ]
#      },
#      {
#        "EncounterID": "Enc02",
#        "EncounterType": "Outpatient",
#        "ClaimLines": {}
#      }
#    ]
#  },
#  {
#    "PatientID": ["PID02"],
#    "PatientName": ["Jane Doe"],
#    "PatientGroup": ["Group B"],
#    "Encounters": [
#      {
#        "EncounterID": "Enc03",
#        "EncounterType": "Inpatient",
#        "ClaimLines": {}
#      },
#      {
#        "EncounterID": "Enc04",
#        "EncounterType": "Outpatient",
#        "ClaimLines": [
#          {
#            "RevCodes": ["001"],
#            "ClaimLine": [1]
#          },
#          {
#            "RevCodes": ["100"],
#            "ClaimLine": [2]
#          }
#        ]
#      },
#      {
#        "EncounterID": "Enc05",
#        "EncounterType": "SNF",
#        "ClaimLines": {}
#      }
#    ]
#  }
#]
#

The key is to create a suitable nested list first (using multiple purrr::transposes) which is then properly translated into a nested JSON using jsonlite::toJSON.