Convert multilevel json/list into data.frame or data.table

Question

I was gathering data by android app, this data I saved into firebase realtime database. This is picture of data > dataScreen

This app is game, where I collect basic info about player, age, gender, etc. Then the game activity starts. Random object is showing on screen of mobile device and players have to react on this object, if they react, I save their reaction like x, y, time(in milliseconds) into object with their name in firebase into level reactions. As you see in picture, if i download it, its list, I tried tidyjson package but its not working with lists...

I need some solution which can make my data into dataframe or datatable in this style

nick, age, gender, .. basic info .., x, y, time ( this is one row)

i need to have row for every reaction recorded for every player ( 36 rows with 36 reactions of one player)

any solution/advice ?

library(tidyjson)
library(RCurl)
raw_data <- getURL("https://myfirebase.firebaseio.com/.json")
data <- fromJSON(raw_data)

    dput(head(data,2))
    Qwerty = list(
age = "50", 
education = "nič", 
gender = "Muz", 
glasses = "Mam okuliare", 
nick = "Qwerty", 
psc = "08005", 
reactions = list(
list(time = 584, x = 814,y = 1615), 
list(time = 743, x = 935, y = 1366), 
list(time = 463, x = 1009, y = 1230), 
list(time = 485, x = 733, y = 1488), 
list(time = 489, x = 482, y = 1433), 
list(time = 483, x = 952, y = 601), 
list(time = 506, x = 20, y = 430), 
list(time = 430, x = 942, y = 871), 
list(time = 495, x = 785, y = 84), 
list(time = 417, x = 769, y = 1682), 
list(time = 433, x = 334, y = 860), 
list(time = 439, x = 979, y = 1183), 
list(time = 458, x = 211, y = 1146), 
list(time = 537, x = 134, y = 1682), 
list(time = 472, x = 812, y = 1655), 
list(time = 448, x = 870, y = 1539), 
list(time = 447, x = 722, y = 910), 
list(time = 477, x = 876, y = 937), 
list(time = 448, x = 723, y = 942), 
list(time = 1479, x = 989, y = 864), 
list(time = 833, x = 1003, y = 1682), 
list(time = 466, x = 738, y = 507), 
list(time = 508, x = 627, y = 1664), 
list(time = 490, x = 340, y = 1070), 
list(time = 874, x = 490, y = 434), 
list(time = 469, x = 423, y = 369), 
list(time = 482, x = 107, y = 1075), 
list(time = 453, x = 102, y = 1341), 
list(time = 491, x = 473, y = 1201), 
list(time = 479,x = 210, y = 517), 
list(time = 533, x = 902, y = 979), 
list(time = 487, x = 1009, y = 1161), 
list(time = 446, x = 823, y = 608), 
list(time = 478, x = 279, y = 35), 
list(time = 482, x = 163, y = 1238), 
list(time = 453, x = 667, y = 697)
), 
score = 532), 
#this is next player object
Qwertz123 = list(
    age = "22", education = "SS", gender = "Muz", glasses = "Nemam okuliare", 
    nick = "Qwertz123", psc = "04013", reactions = list(list(
        time = -1, x = 557, y = 89), list(time = -1, x = 50, 
        y = 648), list(time = 220, x = 47, y = 422), list(time = -1, 
        x = 464, y = 1476), list(time = 694, x = 846, y = 1375), 
        list(time = 618, x = 1009, y = 1011), list(time = 503, 
            x = 45, y = 543), list(time = 574, x = 368, y = 1600), 
        list(time = -1, x = 24, y = 1605), list(time = 549, x = 451, 
            y = 1063), list(time = 857, x = 602, y = 1047), list(
            time = 451, x = 659, y = 1228), list(time = 556, 
            x = 655, y = 1682), list(time = 444, x = 723, y = 617), 
        list(time = -1, x = 1009, y = 1271), list(time = 18, 
            x = 811, y = 350), list(time = 1383, x = 14, y = 255), 
        list(time = -1, x = 575, y = 1337), list(time = 542, 
            x = 648, y = 76), list(time = 647, x = 964, y = 1600), 
        list(time = 458, x = 564, y = 177), list(time = 485, 
            x = 820, y = 1413), list(time = 1070, x = 359, y = 1288), 
        list(time = 677, x = 668, y = 1176), list(time = 637, 
            x = 718, y = 130), list(time = 476, x = 719, y = 1123), 
        list(time = 713, x = 312, y = 263), list(time = 473, 
            x = 122, y = 490), list(time = 395, x = 584, y = 1625), 
        list(time = 372, x = 871, y = 355), list(time = 411, 
            x = 495, y = 617), list(time = 449, x = 109, y = 1086), 
        list(time = 367, x = 400, y = 38), list(time = 449, x = 119, 
            y = 850)), score = 553)

then I want to use tidyjson package, this is just example

data %>% as.tbl_json %>% enter_object("reactions")
Error in UseMethod("as.tbl_json") : 
no applicable method for 'as.tbl_json' applied to an object of class "list"

This package seems good for my problem, but it doesnt work with list.

I need to create dataframe like this example

playersDataFrame <- data.frame(
age = "50", 
education = "nič", 
gender = "Muz", 
glasses = "Mam okuliare", 
nick = "Qwerty", 
psc = "08005",
x = 23,
y = 32,
time = 443
)

this dataframe represents one row of reaction created by player Qwerty, as you see, every player has 36 reactions and players count is for now 61

I need to extract each reaction and have it in a row with player basic data

I have saved players like objects, but I need to have data in form like this example

Please provide a [reproducible example in r](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example). The link I provided, will tell you how. Moreover, please take the [tour](https://stackoverflow.com/tour) and visit [how to ask](https://stackoverflow.com/help/how-to-ask). Cheers. — M--, Apr 09 '19 at 17:11

score 0 · Accepted Answer · answered Apr 09 '19 at 22:00

I realised I dont need to work with json, if it is saved like csv then i can read it easier than have problems with that. It was absolutely random idea but it works :D

for(x in c(1:length(data))){
  for(y in c(1:length(data[[x]]$reactions))){
    write.table(data.frame(data[[x]]$age,data[[x]]$education,data[[x]]$gender,data[[x]]$glasses,data[[x]]$nick,data[[x]]$psc,
                           data[[x]]$reactions[[y]]$x,data[[x]]$reactions[[y]]$y,data[[x]]$reactions[[y]]$time), 
                file = "players.csv", sep = ",", append = TRUE, quote = FALSE,
                col.names = FALSE, row.names = FALSE)
  }
}

then I just use read.csv()

Convert multilevel json/list into data.frame or data.table

1 Answers1