Successfully coercing a splitted JSON object into an R file frame
I am trying to convert JSON retrieved from an API to a dataframe in R so that I can use and parse the data.
#Install needed packages
require(RJSONIO)
require(httr)
#request a list of companies currently fundraising using httr
r <- GET("https://api.angel.co/1/startups?filter=raising")
#convert to text object using httr
raise <- content(r, as="text")
#convert to list using RJSONIO
fromJSON(raise) -> new
Once I receive this object, new
I am having a very difficult time parsing the list into a dataframe. Json has this structure :
{
"startups": [
{
"id": 6702,
"name": "AngelList",
"quality": 10,
"...": "...",
"fundraising": {
"round_opened_at": "2013-07-30",
"raising_amount": 1000000,
"pre_money_valuation": 2000000,
"discount": null,
"equity_basis": "equity",
"updated_at": "2013-07-30T08:14:40Z",
"raised_amount": 0.0
}
}
],
"total": 4268 ,
"per_page": 50,
"page": 1,
"last_page": 86
}
I've tried looking at the individual elements inside new
using the following code:
new$startups[[1]]$fundraising$raised_amount
To pull raised_amount
for the first item listed. However, I don't know how to apply this to the entire list of 4,268 startups. In particular, I can't figure out how to deal with pagination. I only ever get one page of startups (i.e. 50 of them) max.
I tried using a for loop to get a list of startups and just put each value in the dataframe string one by one. The example below only shows this for one column, but of course I could do it for all by simply extending the for loop. However, I cannot get any content on any other page.
df1 <- as.data.frame(1:length(new$startups))
df1$raiseamnt <- 0
for (i in 1:length(new$startups)) {
df1$raiseamnt[i] <- new$startups[[i]]$fundraising$raised_amount
}
e: Thanks for the mention of pagination. I'll take a closer look at the docs and see if I can properly structure the API calls to get different pages. I'll update this answer if / when I figure it out!
source to share
You may find the jsonlite package useful. Below is a short example.
library(jsonlite)
library(httr)
#request a list of companies currently fundraising using httr
r <- GET("https://api.angel.co/1/startups?filter=raising")
#convert to text object using httr
raise <- content(r, as="text")
#parse JSON
new <- fromJSON(raise)
head(new$startups$id)
[1] 229734 296470 237516 305916 184460 147385
Please note that this package, or the one asked in the question, can help parse the JSON string, a separate structure must be created accordingly so that each string element can be added without issue, and it is up to the developer.
For the pagnation API, the REST API is presented, so the filter condition is usually added to the URL (for example https://api.angel.co/1/startups?filter=raising&variable=value
). I assume it will be found somewhere in the API doc.
source to share
The httr library already imports jsonlite ( httr documentation ). A more elegant way with better formatting:
library(httr)
resp <- httr::GET("https://api.angel.co/1/startups?filter=raising", accept_json())
cont <- content(resp, as = "parsed", type = "application/json")
#explicit convertion to data frame
dataFrame <- data.frame(cont)
source to share