Converting a dataframe to nested json format

Question

I am dealing with a r dataframe which looks like this below.

       id    date           items        price
       10    2014/09/13     shoes        187
       10    2014/09/13      belt         35
       10    2014/09/14     shirt         69
       12    2014/10/01     dress         58
       12    2014/10/01       bag        118
       12    2015/01/03     shoes        115

purchase = structure(list(id = c(10, 10, 10, 12, 12, 12), date = structure(c(1L, 
1L, 2L, 3L, 3L, 4L), .Label = c("2014/09/13", "2014/09/14", "2014/10/01", 
"2015/01/03"), class = "factor"), name = structure(c(5L, 2L, 
4L, 3L, 1L, 5L), .Label = c("bag", "belt", "dress", "shirt", 
"shoes"), class = "factor"), price = c(187, 35, 69, 58, 118, 
115)), .Names = c("id", "date", "name", "price"), row.names = c(NA, 
-6L), class = "data.frame")

I am trying to convert this to a nested json format below

[
  {
    "id": "10", 
    "purchase": [
                      {
                        "date": "2014/09/13",
                        "items": [
                          {"id": "shoes", "price": 187},
                          {"id": "belt", "price": 35}
                        ]
                      },
                      {
                        "date": "2014/09/14",
                        "items": [
                          {"id": "shirt", "price": 69}
                        ]
                      }
                ]
  },
  {
    "id": "12", 
    "purchase": [
                      {
                        "date": "2014/10/01",
                        "items": [
                          {"id": "dress", "price": 58},
                          {"id": "bag", "price": 118}
                        ]
                      },
                      {
                        "date": "2015/01/03",
                        "items": [
                          {"id": "shoes", "price": 115}
                        ]
                      }
                ]
  }
]'

I am not sure how to accomplish this so any help on transforming the dataset to this format is much appreciated. Thanks.

jdharrison · Accepted Answer · 2016-09-21 19:39:07Z

A blunt force approach not very elegant but may do for your use case:

purchase = structure(list(id = c(10, 10, 10, 12, 12, 12), date = structure(c(1L, 
                                                                             1L, 2L, 3L, 3L, 4L), .Label = c("2014/09/13", "2014/09/14", "2014/10/01", 
                                                                                                             "2015/01/03"), class = "factor"), name = structure(c(5L, 2L, 
                                                                                                                                                                  4L, 3L, 1L, 5L), .Label = c("bag", "belt", "dress", "shirt", 
                                                                                                                                                                                              "shoes"), class = "factor"), price = c(187, 35, 69, 58, 118, 
                                                                                                                                                                                                                                     115)), .Names = c("id", "date", "name", "price"), row.names = c(NA, 
                                                                                                                                                                                                                                                                                                     -6L), class = "data.frame")

library(jsonlite)
purList <- lapply(split(purchase, purchase$id), function(x) split(x, x$date))

newList <- lapply(names(purList), function(x){
  subList <- purList[[x]]
  purchase <- lapply(names(subList), function(y){
    items <- subList[[y]][c("name", "price")]
    if(nrow(items) > 0){
      names(items) <- c("id", "price")
      list(date = y, items = items)
    }
  })
  list(id = x, purchase = purchase[!sapply(purchase, is.null)])
})
out <- toJSON(newList, auto_unbox = TRUE)
prettify(out) 

> prettify(out)
[
    {
        "id": "10",
        "purchase": [
            {
                "date": "2014/09/13",
                "items": [
                    {
                        "id": "shoes",
                        "price": 187
                    },
                    {
                        "id": "belt",
                        "price": 35
                    }
                ]
            },
            {
                "date": "2014/09/14",
                "items": [
                    {
                        "id": "shirt",
                        "price": 69
                    }
                ]
            }
        ]
    },
    {
        "id": "12",
        "purchase": [
            {
                "date": "2014/10/01",
                "items": [
                    {
                        "id": "dress",
                        "price": 58
                    },
                    {
                        "id": "bag",
                        "price": 118
                    }
                ]
            },
            {
                "date": "2015/01/03",
                "items": [
                    {
                        "id": "shoes",
                        "price": 115
                    }
                ]
            }
        ]
    }
]

Collectives™ on Stack Overflow

Converting a dataframe to nested json format

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related