CODEWITHSUNDEEP
jsonr
Abraão Caldas
Abraão Caldas

Reputation: 715

Parse nested JSON to Data Frame in R

I'm having trouble with a very nasty nested JSON.

The format is like this

{
  "matches": [
    {
      "matchId": 1,
      "region": "BR",
      "participants": [
        {
          "participantId": 0,
          "teamId": 200,
         "stats": {
            "winner": true,
            "champLevel": 16,
            "item0": 3128,
             }
         {
      "matchId": 2,
      "region": "BR",
      "participants": [
        {
          "participantId": 0,
          "teamId": 201,
         "stats": {
            "winner": false,
            "champLevel": 18,
            "item0": 3128,
            "item1": 3157,
            "item1": 3158,
             }

As you can see in the second match the number of items increased, but in the data frame the first row will have the same columns:

MatchId  region ... stats.winner stats.champLevel stats.item0 stats.item1 stats.item2  
1         BR          TRUE         16                 3128          1       BR
1         BR          TRUE         16                 3128          3157     3158

See the first row is smaller than the second, so R recycle the values ....

If you want the full data you can grab it at: http://pastebin.com/HQDf2ase

How I parsed the json to data.frame:

json.matchData <- fromJSON(file="file.json"))

#Unlist the elements of the Json and convert it to a data frame

matchData.i <- lapply(json.matchData$matches, function(x){ unlist(x)})

#Transform into Data Frame

matchData <- do.call("rbind", matchData.i)
matchData <- as.data.frame(matchData)

But the dataframe is messed up, because some fields should be NA but they are filled with wrong values.

Upvotes: 10

Views: 6481

Answers (1)

MrFlick
MrFlick

Reputation: 206167

I think using the plyr rbind.fill() function would be helpful here. How about this

library(plyr)
matchData <- rbind.fill(lapply(matchData.i, 
    function(x) do.call("data.frame", as.list(x))
))

the lapply() bit is to turn the intermediate lists into data.frames which rbind.fill requires.

Upvotes: 5

Related Questions