Convert json list to data frame

Question

I am having an issue converting a json file to a data frame. I use jsonlite and fromJSON() function with also unlist() function but I cannot manage to get the data in the data model I want.

Json file is structured this way:

{"JOHN":["AZ","YZ","ZE","ZR","FZ"],"MARK":["FZ","JF","FS"],"LINDA":["FZ","RZ","QF"]}

And I would like to have a data frame similar to this:

NAME GROUP
JOHN  AZ
JOHN  YZ
JOHN  ZE
JOHN  ZR
JOHN  FZ
MARK  FZ
MARK  JF
MARK  FS
...

Thanks !

akrun · Accepted Answer · 2018-09-03 16:19:08Z

2

We can use fromJSON from jsonlite to get a list of key/value vectors, convert that to a two column data.frame with stack, rearrange the columns and change the column names (if needed).

library(jsonlite)
setNames(stack(fromJSON(str1))[2:1], c("NAME", "GROUP"))
#    NAME GROUP
#1   JOHN    AZ
#2   JOHN    YZ
#3   JOHN    ZE
#4   JOHN    ZR
#5   JOHN    FZ
#6   MARK    FZ
#7   MARK    JF
#8   MARK    FS
#9  LINDA    FZ
#10 LINDA    RZ
#11 LINDA    QF

data

str1 <- '{"JOHN":["AZ","YZ","ZE","ZR","FZ"],"MARK":["FZ","JF","FS"],"LINDA":["FZ","RZ","QF"]}'

edited Sep 3, 2018 at 16:19

answered Sep 3, 2018 at 15:42

akrun

891k38 gold badges590 silver badges700 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ML_Enthousiast Over a year ago

Wow... works like a charm ! Could you just explain me the bracket [2:1] ?

akrun Over a year ago

@ML_Enthousiast It is just to rearrange the columns. By default, stack returns the key column second, I changed the column order so that 1st column is 2nd and 2nd first

Collectives™ on Stack Overflow

Convert json list to data frame

1 Answer 1

data

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

data

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related