Creating JSON format data through data frames in R

Question

I want to create a JSON data of the form

{"Recipe Name": "ABC",
"Main Ingredient": "xyz",
"Ingredients": {type:"a"
                 id:"1"},
               {type:"b"
                 id:"2"},
                {type:"b"
                 id:"3"}
"Servings": 3,}

I have a data frame of type:

Recipe, Recipe Id ,Ingredients,Ingredients ID,Servings ,Main Ingredient,Main Ingredient ID  
 "abc"  , 2     ,    {"a","b","c"}  , {1,2,3,}   , 5   , "f"   ,7   
  "bcf"  , 3   ,       {"d","e","f"} , {4,5,7}  ,  4    ,"g"   ,8
   ....

I have tried usign rjson package but got character(0) as output .Can anyone help me with this please ?

I did find one method though

>library(RJSONIO)
>toJSON(dataframe)

Here is the output:

[1] "{\n \"factor1\": [ \"115g\", \"1\", null, null ],\n\"unit1\": [ \"tub\", \"cups\", \"Greek\", \"baby\" ],\n\"item1\": [ \"tomatoes NA\", \"kalamata olives\", \"feta cheese\", \"rocket NA\" ] \n}"

Which is not in the required format

Do you literally have the string `{"d","e","f"}` stored in your data.frame? It would help if you provided a [reproducible example](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) (perhaps a `dput()` if your input data frame to make it clear what you're working with). What code did you try to run that gave you `character(0)`? — MrFlick, Jul 01 '15 at 20:03
@MrFlick Yes I have these stings in my data frame.Could you help me please? — user459, Jul 01 '15 at 20:24
See if these help- http://stackoverflow.com/q/25550711/2650427 http://stackoverflow.com/q/8288925/2650427 — TrigonaMinima, Jul 03 '15 at 18:54

r2evans · Accepted Answer · 2015-07-03T21:04:55.573

Your desired output isn't correctly structured: you are missing some commas (minor), and your Ingredients needs to be an explicit list (using [ and ], nothing implicit allowed). Also, R doesn't really enjoy spaces in column names, so I removed them. The restructured output:

{"RecipeName": "ABC",
 "MainIngredient": "xyz",
 "Ingredients": [{"type":"a", "id":"1"},
                 {"type":"b", "id":"2"},
                 {"type":"b", "id":"3"}],
 "Servings": 3}

Since your data.frame example is a bit difficult to read/parse, here is one that is similar enough, I think:

dat <- data.frame(
    RecipeName=c('ABC', 'DEF'),
    MainIngredient=c('xyz', 'lmn'),
    Ingredients=c('{"a","b","c"}', '{"d","e","f"}'),
    IngredientsId=c('{1,2,3}', '{4,5,6}'),
    Servings=c(7,8),
    stringsAsFactors=FALSE
)
dat
##   RecipeName MainIngredient   Ingredients IngredientsId Servings
## 1        ABC            xyz {"a","b","c"}       {1,2,3}        7
## 2        DEF            lmn {"d","e","f"}       {4,5,6}        8

From here, it takes a little effort to convert the nested lists within Ingredients to something more than just a string:

datlist <- as.list(dat)
datlist$Ingredients <- mapply(function(a,b) {
    a1 <- strsplit(substring(a, 2, nchar(a)-1), ",")[[1]]
    ## might want to remove leading/trailing quotes as well
    a1 <- gsub('"', '', a1)
    b1 <- strsplit(substring(b, 2, nchar(b)-1), ",")[[1]]
    mapply(function(e,f) list(type=e, id=f), a1, b1, SIMPLIFY=FALSE, USE.NAMES=FALSE)
}, datlist$Ingredients, datlist$IngredientsId,
    SIMPLIFY=FALSE, USE.NAMES=FALSE)
datlist$IngredientsId <- NULL # to remove the now-unnecessary field

library(RJSONIO) # jsonlite::toJSON looks similar, not exactly the same
cat(toJSON(datlist, pretty=T))
## {
##  "RecipeName" : [
##      "ABC",
##      "DEF"
##  ],
##  "MainIngredient" : [
##      "xyz",
##      "lmn"
##  ],
##  "Ingredients" : [
##      [
##          {
##              "type" : "a",
##              "id" : "1"
##          },
##          {
##              "type" : "b",
##              "id" : "2"
##          },
##          {
##              "type" : "c",
##              "id" : "3"
##          }
##      ],
##      [
##          {
##              "type" : "d",
##              "id" : "4"
##          },
##          {
##              "type" : "e",
##              "id" : "5"
##          },
##          {
##              "type" : "f",
##              "id" : "6"
##          }
##      ]
##  ],
##  "Servings" : [
##      7,
##      8
##  ]
## }>

Unfortunately, taking a single row from the data.frame results in nested components (specifically Ingredients), so it does not perfectly adhere to your desired output:

datlist2 <- as.list(dat[1,])
## rest of mapply(mapply(...)) ...
cat(toJSON(datlist2, pretty=TRUE))
## {
##  "RecipeName" : "ABC",
##  "MainIngredient" : "xyz",
##  "Ingredients" : [
##      [
##          {
##              "type" : "a",
##              "id" : "1"
##          },
##          {
##              "type" : "b",
##              "id" : "2"
##          },
##          {
##              "type" : "c",
##              "id" : "3"
##          }
##      ]
##  ],
##  "Servings" : 7
## }

Creating JSON format data through data frames in R

1 Answers1