Skip to content

reading json tables

4 messages · Michael Friendly, Duncan Temple Lang

Michael Friendly
# 
I'm trying to read two data sets in json format from a single .js file. 
I've tried fromJSON()
in both RJSONIOIO and RJSON packages, but they require that the lines be
pre-parsed somehow in ways I don't understand.  Can someone help?

 > wheat <- readLines("http://mbostock.github.com/protovis/ex/wheat.js")
 > str(wheat)
  chr [1:70] "var wheat = [" "  { year: 1565, wheat: 41, wages: 5 }," ...
 >

The wheat.js file looks like this and defines two tables:  wheat and 
monarch:

var wheat = [
   { year: 1565, wheat: 41, wages: 5 },
   { year: 1570, wheat: 45, wages: 5.05 },
   { year: 1575, wheat: 42, wages: 5.08 },
   { year: 1580, wheat: 49, wages: 5.12 },
   { year: 1585, wheat: 41.5, wages: 5.15 },
   { year: 1590, wheat: 47, wages: 5.25 },
   { year: 1595, wheat: 64, wages: 5.54 },
   { year: 1600, wheat: 27, wages: 5.61 },
   { year: 1605, wheat: 33, wages: 5.69 },
   { year: 1610, wheat: 32, wages: 5.78 },
   { year: 1615, wheat: 33, wages: 5.94 },
   { year: 1620, wheat: 35, wages: 6.01 },
...
   { year: 1800, wheat: 79, wages: 28.5 },
   { year: 1805, wheat: 81, wages: 29.5 },
   { year: 1810, wheat: 99, wages: 30 },
   { year: 1815, wheat: 78 }, // TODO
   { year: 1820, wheat: 54 },
   { year: 1821, wheat: 54 }
];

var monarch = [
   { name: "Elizabeth", start: 1565, end: 1603 },
   { name: "James I", start: 1603, end: 1625 },
   { name: "Charles I", start: 1625, end: 1649 },
   { name: "Cromwell", start: 1649, end: 1660, commonwealth: true },
   { name: "Charles II", start: 1660, end: 1685 },
   { name: "James II", start: 1685, end: 1689 },
   { name: "W&M", start: 1689, end: 1702 },
   { name: "Anne", start: 1702, end: 1714 },
   { name: "George I", start: 1714, end: 1727 },
   { name: "George II", start: 1727, end: 1760 },
   { name: "George III", start: 1760, end: 1820 },
   { name: "George IV", start: 1820, end: 1821 }
];
Duncan Temple Lang
# 
Hi Michael

  The problem is that the content of the .js file is not JSON,
but actual JavaScript code.

You could use something like the following

tt = readLines("http://mbostock.github.com/protovis/ex/wheat.js")

txt = c("[", gsub(";", ",", gsub("var [a-zA-Z]+ = ", "", tt)), "]")
tmp = paste(txt, collapse = "\n")
tmp = gsub("([a-zA-Z]+):", '"\\1":', tmp)
o = fromJSON(tmp)
data = structure(o[1:2], names = c("wheat", "monarch"))

Basically, this
    removes the 'var <variable name> =' part
    replaces the ; with a , to separate elements
    quotes the names of the fields, e.g. year, wheat, wages
    puts the two global data objects into a top-level array ([]) container

This isn't ideal (as the regular expressions are not sufficiently specific
and could modify the actual values incorrectly). However, it does the job
for this particular file.
On 12/1/12 12:47 PM, Michael Friendly wrote:
Michael Friendly
#
On 12/1/2012 4:08 PM, Duncan Temple Lang wrote:
Thanks for this, Duncan

I hadn't understood that the data had to be pure JSON.

The actual result I want is two data frames, wheat and monarch, whereas 
fromJSON returns a list of lists.  I'll try to figure that part out.

-Michael
Duncan Temple Lang
# 
Hi Michael
do.call(rbind, data[[1]])

will do the job, but there are elements in each of data[[1]] and data[[2]]
that are incomplete and which need to be filled in with NAs before rbinding.

Best,
  D.
On 12/2/12 6:26 AM, Michael Friendly wrote: