How can I recognize null values as "NA" in my rbind function?

Question

I'm using rbind as part of a function to write json data to a simple data frame in R. I've nearly successfully created my df, but rather than writing in NULL values as "NA" my r script prints the next available variable in the frame that should be null. In other words, I'm seeing data laid into rows improperly (ex. The row 3 values of "access_method.created_at" and "access_method.method" are null, and therefore the row is offset by two variables (the "access_method.created_at" and "access_method.method" are showing "address" and "capacity", and so on). Is there a way to account for these null values?

library(httr)
library(jsonlite)


perpage <- "per_page="
pagenumber <- "page="
pp <- 5000
pn <- 0


vpg <- GET("https://api.seatgeek.com/2/venues?country=US&per_page=5000&page=1&client_id=NTM2MzE3fDE1NzM4NTExMTAuNzU&client_secret=77264dfa5a0bc99095279fa7b01c223ff994437433c214c8b9a08e6de10fddd6")

vpgc <- content(vpg)
vpgcv <- (vpgc$venues)




 json_file <-
  as.data.frame(
    Reduce(function(x, y) {
      rbind(unlist(x), unlist(y))
    }, vpgcv)
  )


venues.dataframe <- as.data.frame(t(json_file))

I've attempted to leverage rbindlist which has a null value function fill = TRUE to no luck. I'd appreciate any suggestions to recognize these NULL values and properly generate a flat dataframe. Thanks!

score 1 · Accepted Answer · answered Nov 23 '19 at 11:17

This might rather be a merge() problem than an rbind() one. First, use unlist() and create data frames in each list object. Second, merge() all lists in the Reduce(). (Note that this runs for a while with your 5k lists!)

l <- lapply(vpgcv, function(x) as.data.frame(t(cbind(unlist(x)))))
result <- Reduce(function(...) merge(..., all=TRUE), l)

head(result, 3)
# metro_code postal_code        timezone has_upcoming_events    id      city
# 1        623       76011 America/Chicago                TRUE  4965 Arlington
# 2        623       76011 America/Chicago                TRUE    16 Arlington
# 3        623       76011 America/Chicago                TRUE 11491 Arlington
# stats.event_count    extended_address display_location state      score location.lat
# 1                23 Arlington, TX 76011    Arlington, TX    TX  0.9751414      32.7459
# 2                 5 Arlington, TX 76011    Arlington, TX    TX 0.84144884      32.7506
# 3                 5 Arlington, TX 76011    Arlington, TX    TX  0.4188409      32.7385
# location.lon num_upcoming_events capacity                 slug                 name
# 1     -97.0957                  23    80000         at-t-stadium         AT&T Stadium
# 2     -97.0824                   5    49115      globe-life-park      Globe Life Park
# 3     -97.1072                   5     1056 arlington-music-hall Arlington Music Hall
# url country popularity
# 1         https://seatgeek.com/venues/at-t-stadium/tickets      US          0
# 2      https://seatgeek.com/venues/globe-life-park/tickets      US          0
# 3 https://seatgeek.com/venues/arlington-music-hall/tickets      US          0
# name_v2                 address access_method.employee_only
# 1         AT&T Stadium              1 AT&T Way                       FALSE
# 2      Globe Life Park       1000 Ballpark Way                       FALSE
# 3 Arlington Music Hall 224 North Center Street                        <NA>
#   access_method.created_at access_method.method
# 1     2019-05-06T17:03:30Z               QRCODE
# 2     2015-07-06T00:00:00Z               PDF417
# 3                     <NA>                 <NA>

Thanks @jay.sf! Your method fits my situation well. Much appreciated! — JMK, Nov 23 '19 at 17:45

How can I recognize null values as "NA" in my rbind function?

1 Answers1