Error parsing JSON file with the jsonlite package

by

ANOTHER UPDATE

You can use the ndjson package to process this ndjson/streaming JSON data. It’s faster than jsonlite::stream_in() and always produces a completely “flat” data frame:

system.time(bitly01 <- ndjson::stream_in("usagov_bitly_data2013-05-17-1368832207.gz"))
##    user  system elapsed 
##   0.146   0.004   0.154

system.time(bitly02 <- jsonlite::stream_in(file("usagov_bitly_data2013-05-17-1368832207.gz"), verbose=FALSE, pagesize=10000))
##    user  system elapsed 
##   0.419   0.008   0.427

If we examine the resultant data frame2, you’ll see ndjson expands ll into ll.0 and ll.1 where you get a list column in jsonlite that you have to deal with later.

ndjson:

dplyr::glimpse(bitly01)
## Observations: 3,959
## Variables: 19
## $ a           <chr> "Mozilla/5.0 (Linux; U; Android 4.1.2; en-us; HTC_PN071 Build/JZO54K) AppleWebKit/534.30 ...
## $ al          <chr> "en-US", "en-us", "en-US,en;q=0.5", "en-US", "en", "en-US", "en-US,en;q=0.5", "en-us", "e...
## $ c           <chr> "US", NA, "US", "US", NA, "US", "US", NA, "AU", NA, "US", "US", "US", "US", "US", "US", "...
## $ cy          <chr> "Anaheim", NA, "Fort Huachuca", "Houston", NA, "Mishawaka", "Hammond", NA, "Sydney", NA, ...
## $ g           <chr> "15r91", "ifIpBW", "10DaxOu", "TysVFU", "10IGW7m", "13GrCeP", "YmtpnZ", "13oM0hV", "15r91...
## $ gr          <chr> "CA", NA, "AZ", "TX", NA, "IN", "WI", NA, "02", NA, "OH", "MD", "KY", "OR", "IL", "TX", "...
## $ h           <chr> "10OBm3W", "ifIpBW", "10DaxOt", "TChsoQ", "10IGW7l", "13GrCeP", "YmtpnZ", "15PUeH0", "10O...
## $ hc          <dbl> 1365701422, 1302189369, 1368814585, 1354719206, 1368738258, 1368130510, 1363711958, 13687...
## $ hh          <chr> "j.mp", "1.usa.gov", "1.usa.gov", "1.usa.gov", "1.usa.gov", "1.usa.gov", "1.usa.gov", "go...
## $ l           <chr> "pontifier", "bitly", "jaxstrong", "o_5004fs3lvd", "peacecorps", "bitly", "bitly", "nasat...
## $ ll.0        <dbl> 33.8161, NA, 31.5273, 29.7633, NA, 41.6123, 45.0070, NA, -33.8615, NA, 39.5151, 39.1317, ...
## $ ll.1        <dbl> -117.9794, NA, -110.3607, -95.3633, NA, -86.1381, -92.4591, NA, 151.2055, NA, -84.3983, -...
## $ nk          <dbl> 0, 0, 1, 1, 0, 0, 1, 0, 0, 0, 1, 0, 1, 1, 0, 0, 0, 1, 1, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0,...
## $ r           <chr> "direct", "http://www.usa.gov/", "http://www.facebook.com/l.php?u=http%3A%2F%2F1.usa.gov%...
## $ t           <dbl> 1368832205, 1368832207, 1368832209, 1368832209, 1368832208, 1368832209, 1368832210, 13688...
## $ tz          <chr> "America/Los_Angeles", "", "America/Phoenix", "America/Chicago", "", "America/Indianapoli...
## $ u           <chr> "http://www.nsa.gov/", "http://answers.usa.gov/system/selfservice.controller?CONFIGURATIO...
## $ _heartbeat_ <dbl> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, N...
## $ kw          <chr> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, N...

jsonlite:

dplyr::glimpse(bitly02)
## Observations: 3,959
## Variables: 18
## $ a           <chr> "Mozilla/5.0 (Linux; U; Android 4.1.2; en-us; HTC_PN071 Build/JZO54K) AppleWebKit/534.30 ...
## $ c           <chr> "US", NA, "US", "US", NA, "US", "US", NA, "AU", NA, "US", "US", "US", "US", "US", "US", "...
## $ nk          <int> 0, 0, 1, 1, 0, 0, 1, 0, 0, 0, 1, 0, 1, 1, 0, 0, 0, 1, 1, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0,...
## $ tz          <chr> "America/Los_Angeles", "", "America/Phoenix", "America/Chicago", "", "America/Indianapoli...
## $ gr          <chr> "CA", NA, "AZ", "TX", NA, "IN", "WI", NA, "02", NA, "OH", "MD", "KY", "OR", "IL", "TX", "...
## $ g           <chr> "15r91", "ifIpBW", "10DaxOu", "TysVFU", "10IGW7m", "13GrCeP", "YmtpnZ", "13oM0hV", "15r91...
## $ h           <chr> "10OBm3W", "ifIpBW", "10DaxOt", "TChsoQ", "10IGW7l", "13GrCeP", "YmtpnZ", "15PUeH0", "10O...
## $ l           <chr> "pontifier", "bitly", "jaxstrong", "o_5004fs3lvd", "peacecorps", "bitly", "bitly", "nasat...
## ## $ al          <chr> "en-US", "en-us", "en-US,en;q=0.5", "en-US", "en", "en-US", "en-US,en;q=0.5", "en-us", "e...
## $ hh          <chr> "j.mp", "1.usa.gov", "1.usa.gov", "1.usa.gov", "1.usa.gov", "1.usa.gov", "1.usa.gov", "go...
## $ r           <chr> "direct", "http://www.usa.gov/", "http://www.facebook.com/l.php?u=http%3A%2F%2F1.usa.gov%...
## $ u           <chr> "http://www.nsa.gov/", "http://answers.usa.gov/system/selfservice.controller?CONFIGURATIO...
## $ t           <int> 1368832205, 1368832207, 1368832209, 1368832209, 1368832208, 1368832209, 1368832210, 13688...
## $ hc          <int> 1365701422, 1302189369, 1368814585, 1354719206, 1368738258, 1368130510, 1363711958, 13687...
## $ cy          <chr> "Anaheim", NA, "Fort Huachuca", "Houston", NA, "Mishawaka", "Hammond", NA, "Sydney", NA, ...
## $ ll          <list> [<33.8161, -117.9794>, NULL, <31.5273, -110.3607>, <29.7633, -95.3633>, NULL, <41.6123, ...
## $ _heartbeat_ <int> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, N...
## $ kw          <chr> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, N...

UPDATE

The latest version of the jsonlite package supports streaming JSON (which is what this actually is). You can now read it with one line like so:

json_file <- stream_in(file("usagov_bitly_data2013-05-17-1368832207"))

See also Jeroen’s answer below for stream-parsing it directly over http.

OLD ANSWER

It turns out this is a “pseudo-JSON” file. I come across these in many naive API systems I work in. Each line is valid JSON, but the individual objects aren’t in a JSON array. You need to use readLines and then build your own, valid JSON array from it and pass that into fromJSON:

library(jsonlite)

# read in individual JSON lines
json_file <- "usagov_bitly_data2013-05-17-1368832207"

# turn it into a proper array by separating each object with a "," and
# wrapping that up in an array with "[]"'s.

dat <- fromJSON(sprintf("[%s]", paste(readLines(json_file), collapse=",")))

dim(dat)

## [1] 3959   18

str(dat)

## 'data.frame': 3959 obs. of  18 variables:
##  $ a          : chr  "Mozilla/5.0 (Linux; U; Android 4.1.2; en-us; HTC_PN071 Build/JZO54K) AppleWebKit/534.30 (KHTML, like Gecko) Version/4.0 Mobile "| __truncated__ "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 1.1.4322; .NET CLR 2.0.50727; .NET CLR 3.0.04506.30; .NET CLR 3.0.4"| __truncated__ "Mozilla/5.0 (Windows NT 6.1; rv:21.0) Gecko/20100101 Firefox/21.0" "Mozilla/5.0 (Linux; U; Android 4.1.2; en-us; SGH-T889 Build/JZO54K) AppleWebKit/534.30 (KHTML, like Gecko) Version/4.0 Mobile S"| __truncated__ ...
##  $ c          : chr  "US" NA "US" "US" ...
##  $ nk         : int  0 0 1 1 0 0 1 0 0 0 ...
##  $ tz         : chr  "America/Los_Angeles" "" "America/Phoenix" "America/Chicago" ...
##  $ gr         : chr  "CA" NA "AZ" "TX" ...
##  $ g          : chr  "15r91" "ifIpBW" "10DaxOu" "TysVFU" ...
##  $ h          : chr  "10OBm3W" "ifIpBW" "10DaxOt" "TChsoQ" ...
##  $ l          : chr  "pontifier" "bitly" "jaxstrong" "o_5004fs3lvd" ...
##  $ al         : chr  "en-US" "en-us" "en-US,en;q=0.5" "en-US" ...
##  $ hh         : chr  "j.mp" "1.usa.gov" "1.usa.gov" "1.usa.gov" ...
## ... (goes on for a while, many columns)

I combined the readLines in with the paste/sprintf call since the object.size of the resultant (temporary) object is 2,025,656 bytes (~2MB) and didn’t feel like doing an rm on a separate temporary variable.

More Related Contents:

  1. Read Json file into a data.frame without nested lists
  2. Parse JSON with R
  3. Importing data from a JSON file into R [duplicate]
  4. Getting imported json data into a data frame
  5. Binary Data in JSON String. Something better than Base64
  6. json.Marshal(struct) returns “{}”
  7. What is “406-Not Acceptable Response” in HTTP?
  8. XSLT equivalent for JSON
  9. Why is JSON invalid if an integer begins with a leading zero?
  10. How to merge 2 JSON objects from 2 files using jq?
  11. Load images based on dynamic path in ReactJs
  12. Select objects based on value of variable in object using jq
  13. Json.NET require all properties on deserialization
  14. JSON integers: limit on size
  15. express.json vs bodyParser.json
  16. Modify a key-value in a json using jq in-place
  17. Convert Mongoose docs to json
  18. How can I deserialize JSON with a top-level array using Serde?
  19. Deserializing JSON in Visual basic
  20. How to process a github webhook payload in Jenkins?
  21. How can I use Serde with a JSON array with different objects for successes and errors?
  22. REST with Spring and Jackson full data binding
  23. Get outputs from jq on a single line
  24. Swift JSONDecoder typeMismatch error
  25. Check if a Postgres JSON array contains a string
  26. How to pass payload via JSON file for curl?
  27. Size limit of JSON data type in PostgreSQL
  28. Constructing a JSON object from a bash associative array
  29. “Fatal error: ‘EXTERN.h’ file not found” while installing Perl modules
  30. How to read local json import in flutter?

Leave a Comment