CODEWITHSUNDEEP
jsonrapi
Josh909
Josh909

Reputation: 13

r API call for json data and converting to dataframe

I have tried to return a JSON response using an API call in RStudio. Its returning the desired JSON file in Postman but the httr API calls don't seem to be returning anything which I can parse into a dataframe.

This is the call in r using the httr package: 

req <- 
httr::POST("https://api2.elasticgrid.com/api/v1/analytics/vendor/partnerengagement/advanced/all",
  httr::add_headers(
    "Authorization" = "Bearer <long string>"
  ),
  body = "VendorId=80&TimeFrame=AddMonths(-3)&Language=en-US",encode="json"
);

This returns a List of 10, but nothing useful and I am fairly sure the JSON content is not hiding in the list

I've tried: 

js <- fromJSON(content(req,as="text"))

But, this is returning "An error has occurred"

or: 

json <- httr::content(req, as = "parsed")

But this returns a non-discript key "7b 22 4d..."

This is the working API call in Postman (RAW HTTP version shown): 

POST /api/v1/analytics/vendor/collateral/advanced/all HTTP/1.1
Host: api2.elasticgrid.com
Authorization: Bearer <long string>
Content-Type: application/json
Cache-Control: no-cache
Postman-Token: <token>
{
"VendorId": 80,
"TimeFrame": "AddMonths(-3)",
"Language": "en-US"
}

Can anyone point me in the right direction to parse a JSON into a dataframe based on an r API call. I've tried a few packages and resources already but am really not sure what to try next assuming this is possible.

Upvotes: 1

Views: 3396

Answers (1)

Colin FAY
Colin FAY

Reputation: 5109

The pblm might be coming from the API : 

req <- 
  httr::POST("https://api2.elasticgrid.com/api/v1/analytics/vendor/partnerengagement/advanced/all",
             httr::add_headers(
               "Authorization" = "Bearer <long string>"
             ),
             body = "VendorId=80&TimeFrame=AddMonths(-3)&Language=en-US",encode="json"
  )

req$status_code
[1]500

The status code here indicates that the server is not responding well. You need to have a 200 here, which is the code for success. Check the Wikipedia page for more info: https://en.wikipedia.org/wiki/List_of_HTTP_status_codes

Is the call you provided the exact call you're making? If this is the case, you might want to check your call again. The API might need an access token to grant the access.

For what concerns your situation, the "7b 22 4d..." is a raw string. you can convert it with rawToChar.

Here's a simple workflow with a call on the haveIbeenpwned API. 

#GET the url 
req <- httr::GET("https://haveibeenpwned.com/api/v2/breachedaccount/test@example.com")
req$status_code
[1] 200

# extract req$content 
cont <- req$content
cont
[1] 5b 7b 22 54 69 74 6c 65 22 3a 22 30 30 30 77 65 62 68 6f 73 74 22 2c 22 4e 61 6d 65
[29] 22 3a 22 30 30 30 77 65 62 68 6f 73 74 22 2c 22 44 6f 6d 61 69 6e 22 3a 22 30 30 30
[57] 77 65 62 68 6f 73 74 2e 63 6f 6d 22 2c 22 42 72 65 61 63 68 44 61 74 65 22 3a 22 32
[85] 30 31 35 2d 30 33 2d 30 31 22 2c 22 41 64 64 65 64 44 61 74 65 22 3a 22 32 30 31 35
[113] 2d 31 30 2d 32 36 54 32 33 3a 33 35 3a 34 35 5a 22 2c 22 4d 6f 64 69 66 69 65 64 44
[141] 61 74 65 22 3a 22 32 30 31 35 2d 31 30 2d 32 36 54 32 33 3a 33 35 3a 34 35 5a 22 2c

#Convert to char
char <- rawToChar(req$content)
char
[1] "[{\"Title\":\"000webhost\",\"Name\":
\"000webhost\",\"Domain\":\"000webhost.com\",
\"BreachDate\":\"2015-03-01\",\"AddedDate\":\""
...

#Convert to df 
df <- jsonlite::fromJSON(char)
df
                        Title            Name                   Domain BreachDate
1                  000webhost      000webhost           000webhost.com 2015-03-01
2                       Adobe           Adobe                adobe.com 2013-10-04
3                Bitcoin Talk     BitcoinTalk          bitcointalk.org 2015-05-22
4                       BTC-E            BTCE                btc-e.com 2014-10-01
5                 Dailymotion     Dailymotion          dailymotion.com 2016-10-20
6                     Dropbox         Dropbox              dropbox.com 2012-07-01
...

Upvotes: 2

Related Questions