How to parse (in R) this API call in .txt table format? (related to the "open government" of Israel :))

Question

Israel has published a budget for everyone to see, and there is an API to retrieve data. However, I do not know how to parse it in txt / csv format.

Below is a link to call data .

Here is the result:

[
    {
        "parent": [
            {
                "budget_id": "00", 
                "title": "המדינה"
            }
        ], 
        "net_amount_revised": 6075053, 
        "year": 2003, 
        "title": "השכלה גבוהה", 
        "gross_amount_used": 5942975, 
        "gross_amount_revised": 5942975, 
        "budget_id": "0021", 
        "net_amount_used": 5936491, 
        "inflation_factor": 1.15866084989269, 
        "net_amount_allocated": 5861591, 
        "gross_amount_allocated": 5861591
    }, 
    {
        "parent": [
            {
                "budget_id": "0021", 
                "title": "השכלה גבוהה"
            }, 
            {
                "budget_id": "00", 
                "title": "המדינה"
            }
        ], 
        "net_amount_revised": 5364976, 
        "year": 2003, 
        "title": "השתתפות בתקציב המוסדות להשכלה גבוהה", 
        "gross_amount_used": 5337585, 
        "gross_amount_revised": 5337584, 
        "budget_id": "002102", 
        "net_amount_used": 5331101, 
        "inflation_factor": 1.15866084989269, 
        "net_amount_allocated": 4985915, 
        "gross_amount_allocated": 4985915
    }, 
    {
        "parent": [
            {
                "budget_id": "0021", 
                "title": "השכלה גבוהה"
            }, 
            {
                "budget_id": "00", 
                "title": "המדינה"
            }
        ], 
        "net_amount_revised": 565495, 
        "year": 2003, 
        "title": "השתתפות בפעולות", 
        "gross_amount_used": 462490, 
        "gross_amount_revised": 462490, 
        "budget_id": "002103", 
        "net_amount_used": 462490, 
        "inflation_factor": 1.15866084989269, 
        "net_amount_allocated": 559293, 
        "gross_amount_allocated": 559293
    }, 
    {
        "parent": [
            {
                "budget_id": "0021", 
                "title": "השכלה גבוהה"
            }, 
            {
                "budget_id": "00", 
                "title": "המדינה"
            }
        ], 
        "net_amount_revised": 0, 
        "year": 2003, 
        "title": "רזרבה להתייקרויות", 
        "gross_amount_used": 0, 
        "gross_amount_revised": null, 
        "budget_id": "002105", 
        "net_amount_used": null, 
        "inflation_factor": 1.15866084989269, 
        "net_amount_allocated": 171801, 
        "gross_amount_allocated": 171801
    }, 
    {
        "parent": [
            {
                "budget_id": "0021", 
                "title": "השכלה גבוהה"
            }, 
            {
                "budget_id": "00", 
                "title": "המדינה"
            }
        ], 
        "net_amount_revised": 108000, 
        "year": 2003, 
        "title": "פיתוח מוסדות להשכלה    גבוהה", 
        "gross_amount_used": 108000, 
        "gross_amount_revised": 108000, 
        "budget_id": "002106", 
        "net_amount_used": 108000, 
        "inflation_factor": 1.15866084989269, 
        "net_amount_allocated": 108000, 
        "gross_amount_allocated": 108000
    }, 
    {
        "parent": [
            {
                "budget_id": "0021", 
                "title": "השכלה גבוהה"
            }, 
            {
                "budget_id": "00", 
                "title": "המדינה"
            }
        ], 
        "net_amount_revised": 23634, 
        "year": 2003, 
        "title": "תחום פעולה כללי", 
        "gross_amount_used": 23634, 
        "gross_amount_revised": 23634, 
        "budget_id": "002101", 
        "net_amount_used": 23634, 
        "inflation_factor": 1.15866084989269, 
        "net_amount_allocated": 23634, 
        "gross_amount_allocated": 23634
    }, 
    {
        "parent": [
            {
                "budget_id": "0021", 
                "title": "השכלה גבוהה"
            }, 
            {
                "budget_id": "00", 
                "title": "המדינה"
            }
        ], 
        "net_amount_revised": 12948, 
        "year": 2003, 
        "title": "פעולות עם משרדים       ומוסדות אחרים", 
        "gross_amount_used": 11266, 
        "gross_amount_revised": 11266, 
        "budget_id": "002104", 
        "net_amount_used": 11266, 
        "inflation_factor": 1.15866084989269, 
        "net_amount_allocated": 12948, 
        "gross_amount_allocated": 12948
    }
]

How can this be analyzed in table format?

Thank!

Tal

Answer 1

Yes, this is JSON. fromJSONturn it into a list for you

resp <- getURL("http://budget.yeda.us/0021?year=2003&depth=1")
library(rjson)
resp <- fromJSON(resp)

This will help you make a list. For a data frame, try:

library(plyr)
resp <- llply(resp, function(x) llply(x, function(y) ifelse(is.null(y), "NULL", y)))
budget <- data.frame()
for(i in 1:length(resp)) {
  budget <- rbind.fill(budget, data.frame(resp[[i]]))
}

Nested ones llplytake care of some nuisance when creating data frames containing null values.

Answer 2

rjson, :

do.call( 'rbind', fromJSON( file="http://budget.yeda.us/0021?year=2003&depth=1" ) )

[]

.. parent ,

Answer 3

Looks like JSON. Try the rjson package, but it may take some cyclic or complex puzzle game.

Lunchtime now, otherwise I would have an inserted solution. Give the lunch portion of the hive of reason a few minutes ....

Answer 4

This is similar to the previous answer, but it also creates columns for the second budget_idand titlein fields parent, and not just for the first and in a structured way, works on parentand restseparately, and then hide them together.

library(rjson)
library(plyr)
js <- fromJSON(file = "http://budget.yeda.us/0021?year=2003&depth=1")

toDF <- function(x) do.call("rbind.fill", lapply(x, as.data.frame))
Null2NA <- function(x) if (is.null(x)) NA else x

parent1 <- lapply(js, "[[", "parent")
rest <- lapply(js, function(x) lapply(x[-1], Null2NA))
cbind(toDF(parent1), toDF(rest))