How to parse specific entities in R?

Question

I have JSON file with events and logs inside those events, the example looks like this:

{
  "sessionEvents": [
    {
      "u": "BC0F6A3A2840B6F48386BABC5F34B480BA4F9929",
      "v": "0.1.0",
      "dv": "Unidentified",
      "t": 1462924115818,
      "uid": "",
      "len": 148012,
      "by": 0,
      "g": "U",
      "cy": "PH",
      "cr": "Unknown",
      "dm": "O+ Xfinit",
      "lat": 0.0,
      "lon": 0.0,
      "l": [
        {
          "e": "100_SESSION_START",
          "o": 24,
          "d": 147988,
          "p": {
            "User_Timezone": "-08:00",
            "Session_nb": "0",
            "Energy_Balance": "89",
            "Global_Playtime": "0",
            "Device_id": "75e64b654c01949",
            "Game_Language": "en",
            "Connection_Type": "WIFI",
            "User_Country": "US",
            "Push_Impact": "None"
          }
        },
        {
          "e": "008_TUTORIAL_STEP_OTHER",
          "o": 7561,
          "d": 0,
          "p": {
            "Screen_id": "scene_screen",
            "Misclicks": "0",
            "Tutorial_Step": "19",
            "Average_Time_Per_Frame": "0",
            "Total_Time": "0"
          }
        }
      ]
    },
    {
      "u": "C950FC733D883E11E36E15A705E05A3CC7748C3A",
      "v": "0.1.0",
      "dv": "OPPO Mirror 5",
      "t": 1462908916463,
      "uid": "",
      "len": 5368,
      "by": 0,
      "g": "U",
      "cy": "PH",
      "cr": "Unknown",
      "dm": "A51w",
      "lat": 0.0,
      "lon": 0.0,
      "l": [
        {
          "e": "100_SESSION_START",
          "o": 169,
          "d": 5199,
          "p": {
            "User_Timezone": "-08:00",
            "Session_nb": "0",
            "Energy_Balance": "0",
            "Global_Playtime": "0",
            "Device_id": "d0de71513e48fba",
            "Game_Language": "en",
            "Connection_Type": "WIFI",
            "User_Country": "US",
            "Push_Impact": "None"
          }
        }
      ]
    }
  ]
}

As you can see, there is a second level object "l" with logs of the event and third level "p" with parameters and it gives me a pain. I'm trying to convert it to data frame, but I only need "100_SESSION_START" logs' values in table (all parameter names of "l" and "p" names are the same for it), plus, I need to add all the parameters from higher level object - event ('u','v','dv','t'...). Does anyone have any idea how to do it user R?

upd: in a result it would e nice to have table like this click

Answer 1

You may do something like this using lapply.

topLevel <- c("u", "v", "dv", "t")
midLevel <- c("e", "o", "d")
botLevel <- c("User_Timezone", "Session_nb", "Energy_Balance", "Global_Playtime")

do.call(rbind, lapply(li[[1]], function(x) {
    do.call(rbind, lapply(x$l, function(y) {
        if(y$e == "100_SESSION_START") {
            c(y[midLevel], y$p[botLevel], x[topLevel])
        }
    }))
}))

     e                   o   d      User_Timezone Session_nb Energy_Balance Global_Playtime
[1,] "100_SESSION_START" 24  147988 "-08:00"      "0"        "89"           "0"            
[2,] "100_SESSION_START" 169 5199   "-08:00"      "0"        "0"            "0"            
     u                                          v       dv              t           
[1,] "BC0F6A3A2840B6F48386BABC5F34B480BA4F9929" "0.1.0" "Unidentified"  1.462924e+12
[2,] "C950FC733D883E11E36E15A705E05A3CC7748C3A" "0.1.0" "OPPO Mirror 5" 1.462909e+12

Answer 2

Assuming you have loaded the json file in a data variable

data <- fromJSON("/home/joel/tmp/input.json")

you can then iterate on each event and in each log of the events as you need:

n<-length(data$sessionEvents$u)
for (i in 1:n) { # Iterate over events
  print(data$sessionEvents$u[i])
  print(data$sessionEvents$v[i])
  print(data$sessionEvents$dv[i])
  print(data$sessionEvents$t[i])
  m<-length(data$sessionEvents$l[i][[1]]$e)
  for(j in 1:m){ # Iterate over logs
    print(data$sessionEvents$l[i][[1]]$e[j])
  }
}

Hope it helps.