Create one to many nested JSON object in R

I have some data that looks like this:

  flyFrom flyTo price
1     AAE   ALG    48
2     AAE   ORN    84
3     AAE   MRS   158
4     AAE   LYS   186
5     AAE   ORY   198
6     AAE   IST   374
...

and I want to transform it into something like this:

{"AAE": [
    "ALG", 48,
    "ORN", 84,
    "MRS", 158,
    "LYS", 186,
    "ORY", 198,
    "IST", 374
...
],
"YYE": [
    "YXY", 48,
    "YDQ", 78,
    "YXJ", 200
]}

I spent about an hour trying different stuff but I'm not sure how to get it into the structure that I want. I'm using jsonlite right now but I can use whatever you recommend

edit: I want the price column also included but I'm also not sure what the cleanest implementation would be. the relationship between flyTo and price will always be 1:1

edit2: I was able to pull this out but it's not quite there

> test<-head(price,10)[c(1,2,3)]
> test2<-list(test$flyFrom,list(test$flyTo,test$price))
> jsonlite::toJSON(test2, pretty = TRUE,auto_unbox = TRUE)
[
["BUF", "CAK", "JAN", "PHF", "CAE", "FCA", "PHL", "CHS", "AVL", "AVL"],
[
    ["RIC", "PHF", "MEM", "LGA", "LEX", "JAC", "CHO", "DHN", "RDU", "CHS"],
    [7064, 6686, 6134, 6112, 5667, 5616, 5528, 5462, 5115, 5069]
]
] 

1 answer

  • answered 2018-11-17 11:19 Julius Vainora

    As your attempt demonstrates, the result can be achieved by converting your data frame into a certain list. In particular, using

    split(price[, -1], price$flyFrom)
    # $AAE
    #   flyTo price
    # 1   ALG    48
    # 2   ORN    84
    # 3   MRS   158
    # 4   LYS   186
    # 5   ORY   198
    # 6   IST   374
    
    # $YYE
    #   flyTo price
    # 7   YXY    48
    # 8   YDQ    78
    # 9   YXJ   200
    

    gives

    toJSON(split(price[, -1], price$flyFrom), pretty = TRUE)
    {
      "AAE": [
        {
          "flyTo": "ALG",
          "price": 48
        },
        {
          "flyTo": "ORN",
          "price": 84
        },
        {
          "flyTo": "MRS",
          "price": 158
        },
        {
          "flyTo": "LYS",
          "price": 186
        },
        {
          "flyTo": "ORY",
          "price": 198
        },
        {
          "flyTo": "IST",
          "price": 374
        }
      ],
      "YYE": [
        {
          "flyTo": "YXY",
          "price": 48
        },
        {
          "flyTo": "YDQ",
          "price": 78
        },
        {
          "flyTo": "YXJ",
          "price": 200
        }
      ]
    }