Question posted in Json
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Json – How to unlist to a dataframe while maintaining nested lists as columns

Eizy
May 4, 2023
326 views
0 votes
2 Answers

I have a list called "merged". Within each object in the list there are nested lists for "input", "output", and "session". Ultimately I would like to unlist all this into a dataframe that is easy to work. This data comes from shiny app logs and I would like to be able to select and filter variables to see how users are interacting with the app. I am new to lists and need help getting this data into a format I can work.

Head dput

data <- list(list(inputs = structure(list(name = c(".shinymanager_logout", 
"cap_table_search", "cap_table_rows_all", "cap_table_state", 
"site_map_center", "cap_table_state", "cap_table_cells_selected", 
"cap_table_cell_clicked", "cap_table_rows_selected", "region_map", 
"site_map_center", "site_map_zoom", "site_map_bounds", "site_map_bounds", 
"cap_table_columns_selected", "site_map_zoom", "site_map", "cap_table_rows_current"
), timestamp = c("2023-05-04 08:29:48.355-0700", "2023-05-04 08:29:39.094-0700", 
"2023-05-04 08:29:39.093-0700", "2023-05-04 08:29:39.334-0700", 
"2023-05-04 08:29:44.125-0700", "2023-05-04 08:29:39.093-0700", 
"2023-05-04 08:29:39.090-0700", "2023-05-04 08:29:39.094-0700", 
"2023-05-04 08:29:39.090-0700", "2023-05-04 08:29:39.152-0700", 
"2023-05-04 08:29:44.049-0700", "2023-05-04 08:29:44.125-0700", 
"2023-05-04 08:29:44.125-0700", "2023-05-04 08:29:44.049-0700", 
"2023-05-04 08:29:39.090-0700", "2023-05-04 08:29:44.049-0700", 
"2023-05-04 08:29:40.663-0700", "2023-05-04 08:29:39.092-0700"
), value = list(1L, "", list(), list(time = 1683214179333, start = 0L, 
    length = 10L, order = list(), search = list(search = "", 
        smart = TRUE, regex = FALSE, caseInsensitive = TRUE), 
    columns = structure(list(visible = c(TRUE, TRUE, TRUE), search = structure(list(
        search = c("", "", ""), smart = c(TRUE, TRUE, TRUE), 
        regex = c(FALSE, FALSE, FALSE), caseInsensitive = c(TRUE, 
        TRUE, TRUE)), class = "data.frame", row.names = c(NA, 
    3L))), class = "data.frame", row.names = c(NA, 3L))), list(
    lng = -82.6248, lat = 12.7017), structure(list(), names = character(0)), 
    list(), structure(list(), names = character(0)), list(), 
    c("altos_de_campana", "boraceia", "california", "caribbean", 
    "chiriqui", "el_cope", "el_valle", "fortuna", "gamboa", "louisiana", 
    "new_mexico", "pennsylvania", "santa_fe", "santa_virginia", 
    "tennessee", "vermont"), list(lng = -82.6248, lat = 12.7017), 
    3L, list(north = 49.2678, east = 5.2734, south = -29.6881, 
        west = -170.5078), list(north = 49.2678, east = 5.2734, 
        south = -29.6881, west = -170.5078), list(), 3L, c("10008", 
    "10032", "10037", "10055", "10069", "10081", "10090", "10091", 
    "10095", "10100", "10101", "10102", "10103", "10109", "10110", 
    "10111", "10114", "10196", "10197", "10198", "10199", "10206", 
    "10218", "10220", "10223", "10225", "10227", "10228", "10263", 
    "10276", "10277", "10284", "10285", "10314", "10315", "10316", 
    "10319", "10418", "10420", "10421", "10422", "10423", "10461", 
    "10473", "10474", "10475", "10476", "10477", "10486", "10487", 
    "10488", "10489", "10490", "10511", "10533", "10570", "10571", 
    "10572", "10583", "10584", "10591", "10593", "10594", "10697", 
    "11007", "11008", "11009", "11010", "11011", "11027", "11028", 
    "11029", "11030", "11040", "11068", "11215", "11359", "11469", 
    "11494", "11496", "11506", "11518", "11570", "11571", "11572", 
    "11687", "11858", "11870", "11896", "11_water", "12012", 
    "12102", "12110", "12111", "12125", "12222", "12254", "12330", 
    "12331", "12333", "12361", "12464", "12534", "12562", "12590", 
    "12602", "12604", "12605", "12618", "12621", "12648", "13025", 
    "13026", "14001", "1_land", "1_water", "20086", "20135", 
    "20169", "20170", "20190", "20196", "20198", "20199", "20231", 
    "20276", "20278", "20288", "21007", "21081", "21091", "21092", 
    "21097", "21098", "21108", "21109", "21110", "21111", "21520", 
    "21530", "22008", "22019", "2330scacou", "2_land", "2_water", 
    "3_land", "3_water", "40800", "40801", "4_land", "4_water", 
    "50138", "50162", "50183", "50217", "50218", "50229", "50304", 
    "50305", "50306", "50307", "50308", "50709", "50731", "50735", 
    "50783", "50785", "50787", "50837", "50839", "50858", "50887", 
    "50895", "50897", "50899", "50904", "50929", "50967", "51356", 
    "51357", "51361", "51365", "51801", "52003", "52127", "52171", 
    "52175", "52193", "52212", "52234", "52238", "52240", "52241", 
    "52244", "52245", "52246", "52247", "52248", "52249", "52250", 
    "52251", "52252", "52253", "52254", "52255", "52256", "52257", 
    "52258", "52259", "52260", "52261", "52262", "52263", "52264", 
    "52266", "52267", "52268", "52271", "53003", "54035", "54188", 
    "54200", "54202", "54203", "54204", "54205", "5_land", "5_water", 
    "6_land", "6_water", "70028", "70034", "70074", "70114", 
    "70122", "70156", "70158", "70175", "70215", "70251", "70278", 
    "70279", "70284", "70295", "70327", "70331", "70348", "70355", 
    "70370", "70381", "70397", "70399", "70403", "70413", "70442", 
    "70448", "70449", "70459", "70470", "70472", "70481", "70505", 
    "70519", "70525", "70529", "70550", "70552", "70556", "70563", 
    "70567", "70571", "70596", "70597", "70611", "70619", "70627", 
    "70628", "70629", "70634", "70641", "70650", "70657", "70685", 
    "70735", "71121", "71362", "71461", "71597", "71679", "71734", 
    "71811", "71887", "71917", "72008", "72021", "72092", "72093", 
    "72097", "72147", "72179", "72210", "72212", "72215", "72336", 
    "72404", "72442", "72580", "72601", "72620", "72695", "72773", 
    "72808", "72847", "72849", "72885", "72973", "72986", "72989", 
    "72996", "72998", "73037", "73042", "74060", "74061", "74269", 
    "74281", "74335", "74976", "7_land", "7_water", "82643", 
    "82644", "82645", "82646", "82682", "84218", "84221", "84226", 
    "84235", "84237", "84255", "84266", "84277", "84292", "84294", 
    "84303", "84305", "84306", "84307", "84309", "84310", "84312", 
    "84313", "84317", "84325", "84326", "84327", "84329", "84962", 
    "84963", "87495", "8_land", "8_water", "9_land", "9_water", 
    "a2w", "a3w", "a4w", "adjacent_to_sand_bar_state_park", "admin_pond", 
    "alleman", "alojamento", "altos_de_piedra", "artesia", "avilas", 
    "b10t", "b10w", "b1t", "b1w", "b2t", "b2w", "b3t", "b3w", 
    "b4t", "b4w", "b5", "b5w", "b6t", "b6w", "b7t", "b7w", "b8t", 
    "b8w", "b9t", "b9w", "beaver", "black_jack", "bp2", "bp4", 
    "bupu0039", "campana_loop", "camp_ethan_allen_pond", "camp_johnson_pond_1", 
    "camp_johnson_pond_2", "cerro_negro", "cerro_punta", "church", 
    "circle_7", "cow_pit", "cuatro_callitas", "cuchillo", "culvert_0", 
    "david's_pond", "davie's_playa", "davie's_playa__doug_burkett", 
    "deer", "doug's_office", "el_nispero", "el_valle_hotel", 
    "estrada", "farallon", "finca_el_hervidero", "fortuna_loop", 
    "gamboa", "garcia_well", "guabal", "hatchery", "horse_head_ephemeral", 
    "horse_head_permanent", "hotel_campestre", "johnson_tank", 
    "jordinal", "juan_julio", "juan_lana", "kisatchie_bayou", 
    "kurthwood", "lago_angelim_water", "lago_anta_water", "lago_minutal_water", 
    "lago_sede_water", "las_yayas", "leasburg_dam_state_park,_bridge", 
    "mata_ahogado", "medina", "miguel_de_la_borda", "natural_area_close_to_vt01_and_vt02", 
    "newt", "north_beach_pond", "olho_de_agua", "omar_torrijos", 
    "paseo_del_rio", "pena_blanca", "phelps_pond", "pine", "pirapitinga", 
    "poça_temporária_water", "rabbit_stream", "rana_dorada", 
    "range_road12", "red_tank", "red_tank_2", "rhodes_spring", 
    "rio_blanco", "rio_maria", "rio_marta", "rio_tigrero", "roadside_ditch_bude2026", 
    "rouse_tank", "rv_pond", "sargentita", "shelbourne_bay", 
    "shelbourne_pond", "sink", "solomon_lane", "sophia_stream", 
    "sora", "speamulti2237", "trilha_land", "tryon_weber", "tuttle_pond", 
    "vorisek_pond", "west", "wood_lab_pond"), list()), type = c("shiny.action", 
"", "", "", "", "", "shiny.matrix", "", "", "", "", "", "", "", 
"", "", "", ""), binding = c("shiny.actionButtonInput", "", "", 
"", "", "", "", "", "", "shinyWidgets.pickerInput", "", "", "", 
"", "", "", "shinyWidgets.pickerInput", "")), class = "data.frame", row.names = c(NA, 
18L)), outputs = structure(list(name = c("site_map", "cap_table"
), timestamp = c("2023-05-04 08:29:42.950-0700", "2023-05-04 08:29:36.410-0700"
), binding = c("leaflet", "datatables")), class = "data.frame", row.names = 1:2), 
    session = structure(list(app = "ribbitr_datarepository", 
        user = "shiny", server_connected = "2023-05-04 15:29:28.742+0000", 
        sessionid = "5f73950379b2b7a15e3999df602fe6dc", server_disconnected = "2023-05-04 15:30:06.477+0000", 
        user_agent = "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/112.0.0.0 Safari/537.36", 
        screen_res = "1920x1080", browser_res = "1708x894", pixel_ratio = 1L, 
        browser_connected = "2023-05-04 08:29:35.074-0700"), class = "data.frame", row.names = 1L)))

Tags: json list r shiny

Answers

- JoeRobert
- May 4, 2023 at 10:56 pm
- 0 votes
0
I think @MrFlick’s questions are appropriate to answer first. But if you want to, for example, see which input/outputs the user interacts with over time, then you could do something like the following.
```
library(dplyr)
library(ggplot2)
library(ggrepel)
bind_rows(data[[1]]$inputs %>%
            mutate(type = "input"),
          data[[1]]$outputs %>%
            mutate(type = "output")) %>%
  mutate(timestamp = lubridate::ydm_hms(timestamp)) %>%
  arrange(timestamp) %>%
  ggplot(aes(x = timestamp,y = 0,colour = type)) +
  geom_point(aes()) +
  ggrepel::geom_text_repel(aes(label = name),max.overlaps = 20,min.segment.length = 0,force = 10000,position = position_dodge(10))
```
This plot could use a lot of polishing like rearranging the repelled text. It also doesn’t provide information about the value of the inputs, which are nested within the inputs data frame. Hopefully, the basic idea is clear.
Login or Signup to reply.

A way to unfold the list is to access each element and put these in a matrix of data frames.

dd <- sapply(data, data.frame)

dd
                            [,1]        
inputs.name                 character,18
inputs.timestamp            character,18
inputs.value                list,18     
inputs.type                 character,18
inputs.binding              character,18
outputs.name                character,18
outputs.timestamp           character,18
outputs.binding             character,18
session.app                 character,18
session.user                character,18
session.server_connected    character,18
session.sessionid           character,18
session.server_disconnected character,18
session.user_agent          character,18
session.screen_res          character,18
session.browser_res         character,18
session.pixel_ratio         integer,18  
session.browser_connected   character,18

Access elements either with the index number or the row names

dd["session.pixel_ratio",]
$session.pixel_ratio
 [1] 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1

Please signup or login to give your own answer.

Click here to cancel reply.