Parsing a JSON column of an R data frame

Question

My data comes in from mySQL as a data frame that looks like:

  id users_id value                                                                                                             

2    29937  {"event":"VirtualPageview","eventName":"VirtualPageview","virtualUrl":"/login/mood","fromUrl":"/login"}
3    29937  {"event":"VirtualPageview","eventName":"VirtualPageview","virtualUrl":"/login/done","fromUrl":"/login/mood"}
8    77347  {"event":"VirtualPageview","eventName":"VirtualPageview","virtualUrl":"/signup/survey","fromUrl":"/signup/account"}

I am trying to get it to look like:

  id users_id   event             eventName         virtualUrl    fromUrl                                                                                                    
  2    29937   "VirtualPageview"  "VirtualPageview" "/login/mood" "/login"

The answer here is close, but the original data format is different. I would rather not import 4 libraries if I get away with less.

score 0 · Answer 1 · answered Nov 02 '16 at 15:59

I used

library(jsonlite)

Then turning the whole column into a string and pasting on []

fromJSON(paste0('[',toString(head(df$value)),']'))

        event       eventName     virtualUrl         fromUrl
1 VirtualPageview VirtualPageview    /login/mood          /login
2 VirtualPageview VirtualPageview    /login/done     /login/mood
3 VirtualPageview VirtualPageview /signup/survey /signup/account

df <-cbind(df[,c("id","users_id")],fromJSON(paste0('[',toString(head(df$value)),']')))

id users_id           event       eventName     virtualUrl         fromUrl
    2    29937 VirtualPageview VirtualPageview    /login/mood          /login
    3    29937 VirtualPageview VirtualPageview    /login/done     /login/mood
    8    77347 VirtualPageview VirtualPageview /signup/survey /signup/account

Parsing a JSON column of an R data frame

1 Answers1