How to parse Json in R corresponding to ID

Question

I have below mentioned dataframe:

ID         Json_Data
PA-124     Json String
PA-145     Json String
PA-185     Json String
PA-149     Json String

I want to extract all the json field corresponding to each ID as mentioned below.

And I would like to know how to extract some specific field against each particular ID (i.e Country).

ID        Name       Country      Code
PA-124    Ajre       Dubai        154
PA-145    Aret       USA          485
PA-185    Cret       Russia       445
PA-149    Oerp       Canada       789

For that i have used below mentioned code, but it returns value against only one ID out of ~1500 IDs.

My Code:

    library(RJSONIO)
    library(rjson)
    library(jsonlite)
    library(data.table)


    f <- function(json, id){
      tmp    <- jsonlite::fromJSON(json)
      tmp    <- as.data.frame(tmp)
      tmp$id <- id
      return(tmp)
    }

json_dfs <- 
  mapply(f, Df$Json_Data, Df$ID, SIMPLIFY = FALSE)

Dput:

    structure(list(ID = c("PA-124", "PA-124", "PA-124"), Json_Data = c("{\n  \"hed\": {\n    \"srti\": \"RT1455\"\n  },\n  \"nnaesr\": {\n    \"name\": \"\",\n    \"firt\": \"alex\",\n    \"last\": \"john\",\n    \"birth\": \"26061981\",\n    \"gender\": \"Male\"\n  },\n  \"iteret\": {\n    \"adre\": \"asded14eer\",\n    \"adfe\": \"\",\n    \"sfdet\": \"\",\n    \"sfeet\": \"\",\n    \"serted\": \"\",\n    \"sertgf\": \"\"\n  },\n  \"sefetgd\": {\n    \"mobe\": \"2111111\",\n    \"dfete\": \"\",\n    \"tfet\": \"sdfet\"\n  },\n  \"aarfgeette\": {\n    \"aarfgertte\": \"H NO. , dffr rtrg\",\n    \"aarfgot2\": \"ids School, feeeete,\",\n    \"aarfgot3\": \"\",\n    \"aarfgot4\": \"\",\n    \"aarfgot5\": \"\",\n    \"sdfffy\": \"errtyy\",\n    \"errrte\": \"efeteet\",\n    \"sdetVb\": \"\",\n    \"sdfes\": \"5500\",\n    \"aarfgppr\": \"eretft er\",\n    \"seferf\": \"ered\"\n  }\n}", 
"{\n  \"hed\": {\n    \"srti\": \"RT1455\"\n  },\n  \"nnaesr\": {\n    \"name\": \"\",\n    \"firt\": \"alex\",\n    \"last\": \"john\",\n    \"birth\": \"26061981\",\n    \"gender\": \"Male\"\n  },\n  \"iteret\": {\n    \"adre\": \"asded14eer\",\n    \"adfe\": \"\",\n    \"sfdet\": \"\",\n    \"sfeet\": \"\",\n    \"serted\": \"\",\n    \"sertgf\": \"\"\n  },\n  \"sefetgd\": {\n    \"mobe\": \"2111111\",\n    \"dfete\": \"\",\n    \"tfet\": \"sdfet\"\n  },\n  \"aarfgeette\": {\n    \"aarfgertte\": \"efeet efet\",\n    \"aarfgot2\": \"affe fe,\",\n    \"aarfgot3\": \"\",\n    \"aarfgot4\": \"\",\n    \"aarfgot5\": \"\",\n    \"sdfffy\": \"Telangana\",\n    \"errrte\": \"Hyderabad\",\n    \"sdetVb\": \"\",\n    \"sdfes\": \"5500\",\n    \"aarfgppr\": \"acefec ee\",\n    \"seferf\": \"dfefe\"\n  }\n}", 
"{\n  \"hed\": {\n    \"srti\": \"RT1455\"\n  },\n  \"nnaesr\": {\n    \"name\": \"\",\n    \"firt\": \"alex\",\n    \"last\": \"john\",\n    \"birth\": \"26061981\",\n    \"gender\": \"Male\"\n  },\n  \"iteret\": {\n    \"adre\": \"asded14eer\",\n    \"adfe\": \"\",\n    \"sfdet\": \"\",\n    \"sfeet\": \"\",\n    \"serted\": \"\",\n    \"sertgf\": \"\"\n  },\n  \"sefetgd\": {\n    \"mobe\": \"2111111\",\n    \"dfete\": \"\",\n    \"tfet\": \"sdfet\"\n  },\n  \"aarfgeette\": {\n    \"aarfgertte\": \"ad.de 25dfege\",\n    \"aarfgot2\": \"adfed ,\",\n    \"aarfgot3\": \"\",\n    \"aarfgot4\": \"\",\n    \"aarfgot5\": \"\",\n    \"sdfffy\": \"adffe\",\n    \"errrte\": \"Hyderabad\",\n    \"sdetVb\": \"\",\n    \"sdfes\": \"5500\",\n    \"aarfgppr\": \"sadfe ad\",\n    \"seferf\": \"dsfde\"\n  }\n}"
)), row.names = c(NA, 3L), class = "data.frame")

Please make your example reproducible. Can you post the `dput` of the first couple of rows? The `"Json string"` would be useful to have. — Axeman, May 08 '18 at 11:25
Actually, we prefer the `dput` of the `Df`, not just the json string column. Can you also add `dput(head(Df,3)` to the question? — Shique, May 08 '18 at 11:48
if `dput(head(Df,3))` only returns that, then it means that the `Df` in question only contains 1 row, resulting in what you get (just one ID). You need to put all ID's and their json data in `Df`. — Shique, May 08 '18 at 12:02
Running that through the `f` function exactly returns a list of 3, not 1. So it works correctly. What seems to be the problem? More importantly; ***what is your desired output***? — Shique, May 08 '18 at 12:26
@Shique That was a small sample, i am getting this error now: `Error: lexical error: invalid char in json text. NA (right here) ------^ Called from: parse_string(txt, bigint_as_char)` — Roy1245, May 08 '18 at 12:28

score 0 · Answer 1 · answered May 09 '18 at 01:06

0

You can use unlist to convert it into a character vector and then row bind the list of character vector into a data.frame. Then column bind the results with your ID

cbind(temp$ID,
    do.call(rbind, lapply(temp$Json_Data, function(x) unlist(jsonlite::fromJSON(x))))
)

result:

              hed.srti nnaesr.name nnaesr.firt nnaesr.last nnaesr.birth nnaesr.gender iteret.adre  iteret.adfe iteret.sfdet iteret.sfeet iteret.serted iteret.sertgf
[1,] "PA-124" "RT1455" ""          "alex"      "john"      "26061981"   "Male"        "asded14eer" ""          ""           ""           ""            ""           
[2,] "PA-124" "RT1455" ""          "alex"      "john"      "26061981"   "Male"        "asded14eer" ""          ""           ""           ""            ""           
[3,] "PA-124" "RT1455" ""          "alex"      "john"      "26061981"   "Male"        "asded14eer" ""          ""           ""           ""            ""           
     sefetgd.mobe sefetgd.dfete sefetgd.tfet aarfgeette.aarfgertte aarfgeette.aarfgot2    aarfgeette.aarfgot3 aarfgeette.aarfgot4 aarfgeette.aarfgot5 aarfgeette.sdfffy
[1,] "2111111"    ""            "sdfet"      "H NO. , dffr rtrg"   "ids School, feeeete," ""                  ""                  ""                  "errtyy"         
[2,] "2111111"    ""            "sdfet"      "efeet efet"          "affe fe,"             ""                  ""                  ""                  "Telangana"      
[3,] "2111111"    ""            "sdfet"      "ad.de 25dfege"       "adfed ,"              ""                  ""                  ""                  "adffe"          
     aarfgeette.errrte aarfgeette.sdetVb aarfgeette.sdfes aarfgeette.aarfgppr aarfgeette.seferf
[1,] "efeteet"         ""                "5500"           "eretft er"         "ered"           
[2,] "Hyderabad"       ""                "5500"           "acefec ee"         "dfefe"          
[3,] "Hyderabad"       ""                "5500"           "sadfe ad"          "dsfde"

answered May 09 '18 at 01:06

chinsoon12

25,005
4
25
35

Getting this error: `Error: lexical error: invalid char in json text. NA (right here) ------^ Called from: parse_string(txt, bigint_as_char)` – Roy1245 May 09 '18 at 03:48
There are malformed json in your data, try it one by one to find the offending json. For example, for(x in temp$Json_data) {print(x); fromJSON(x)} – chinsoon12 May 09 '18 at 04:06
Yes I have a big Dataframe and checking one by one would be time consuming...Don't we have any other option to find such malformed Json from Dataframe and remove it?? – Roy1245 May 09 '18 at 04:10
Yeah I added it in the comment – chinsoon12 May 09 '18 at 04:12
Still not able to parse...:( – Roy1245 May 09 '18 at 06:51
Please help me here. – Roy1245 May 09 '18 at 08:15
you can share what is the string that is causing the problem. hard to help if there is nothing to work with – chinsoon12 May 09 '18 at 08:16
where should i paste this code: `for(x in temp$Json_data) {print(x); fromJSON(x)} ` – Roy1245 May 09 '18 at 08:16
I am not able to identify those strings. – Roy1245 May 09 '18 at 08:17
just change temp to your data.frame name and run it in the console – chinsoon12 May 09 '18 at 08:18
you have to share the json_data that is causing that error. – chinsoon12 May 09 '18 at 08:29
Is there not any way to just extract json fields in row irrespective of malformed json. – Roy1245 May 09 '18 at 08:29
Or is it not possible to just extract some selected fields only. – Roy1245 May 09 '18 at 08:31
some columns were blank that caused this error, now i am able to parse the json but not able to convert it in dataframe by rbind. – Roy1245 May 09 '18 at 11:27

How to parse Json in R corresponding to ID

1 Answers1