Large JSON list to a tidy table

user_9439560 0
Viewed 50

I have this data:

list(list(TrainId = "434", TrainNumber = "602", CarCount = 6L, 
DirectionNum = 1L, CircuitId = 1117L, DestinationStationCode = "G05", 
LineCode = "SV", SecondsAtLocation = 43L, ServiceType = "Normal"), 
list(TrainId = "417", TrainNumber = "609", CarCount = 8L, 
    DirectionNum = 2L, CircuitId = 3021L, DestinationStationCode = "N06", 
    LineCode = "SV", SecondsAtLocation = 1L, ServiceType = "Normal"), 
list(TrainId = "023", TrainNumber = "309", CarCount = 8L, 
    DirectionNum = 2L, CircuitId = 2364L, DestinationStationCode = "C15", 
    LineCode = "YL", SecondsAtLocation = 28L, ServiceType = "Normal"), 
list(TrainId = "450", TrainNumber = "411", CarCount = 8L, 
    DirectionNum = 2L, CircuitId = 1260L, DestinationStationCode = "J03", 
    LineCode = "BL", SecondsAtLocation = 2L, ServiceType = "Normal"), 
list(TrainId = "417", TrainNumber = "609", CarCount = 8L, 
    DirectionNum = 2L, CircuitId = 3021L, DestinationStationCode = "N06", 
    LineCode = "SV", SecondsAtLocation = 1L, ServiceType = "Normal"))

The data came from JSON, and I got it there from a simple list.files and then lst <- lapply(files, fromJSON)

What's the best tidyverse way of getting it into a data frame? One that has the TrainID, TrainNumber, LineCode, etc. as the column headers (not as rows)? I've had no luck with melt combined with lapply. Also tried this:

table <- for(i in lst) {
     melt(i)
   }

That returns one row of what I want, but would prefer to keep it in the apply family. I am NOT looking for the below, which is obtained from a simple melt(lst[1]): 

 structure(list(value = c("417", "609", "8", "2", "3021", "N06", 
 "SV", "1", "Normal"), L2 = c("TrainId", "TrainNumber", "CarCount", 
 "DirectionNum", "CircuitId", "DestinationStationCode", "LineCode", 
 "SecondsAtLocation", "ServiceType"), L1 = c(1L, 1L, 1L, 1L, 1L, 
  1L, 1L, 1L, 1L)), row.names = c(NA, -9L), class = "data.frame")
dplyr r
2 Answers

We can use unnest_wider

library(tidyr)
library(dplyr)
tibble(col1 = lst1) %>% 
          unnest_wider(col1)
# A tibble: 5 x 9
#  TrainId TrainNumber CarCount DirectionNum CircuitId DestinationStationCode LineCode SecondsAtLocation ServiceType
#  <chr>   <chr>          <int>        <int>     <int> <chr>                  <chr>                <int> <chr>      
#1 434     602                6            1      1117 G05                    SV                      43 Normal     
#2 417     609                8            2      3021 N06                    SV                       1 Normal     
#3 023     309                8            2      2364 C15                    YL                      28 Normal     
#4 450     411                8            2      1260 J03                    BL                       2 Normal     
#5 417     609                8            2      3021 N06                    SV                       1 Normal

Or using base R

do.call(rbind, lapply(lst1, as.data.frame))
#TrainId TrainNumber CarCount DirectionNum CircuitId DestinationStationCode LineCode SecondsAtLocation ServiceType
#1     434         602        6            1      1117                    G05       SV                43      Normal
#2     417         609        8            2      3021                    N06       SV                 1      Normal
#3     023         309        8            2      2364                    C15       YL                28      Normal
#4     450         411        8            2      1260                    J03       BL                 2      Normal
#5     417         609        8            2      3021                    N06       SV                 1      Normal
user_37322710

Here is another solution using purrr and base:

as.data.frame(map(split.default(unlist(a), names(unlist(a))), function(x) reduce(x,rbind)))

Pay attention to the use of split.default and reduce. The first function splits the unlisted object while the reduce is used to row-bind soon-to-be column elements.

user_101078640
Related