r API call for json data and converting to dataframe

Question

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.

Colin FAY · Accepted Answer · 2017-09-07 06:34:42Z

2

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/[email protected]")
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
...

edited Sep 7, 2017 at 6:34

answered Sep 6, 2017 at 8:30

Colin FAY

5,1291 gold badge17 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Josh909 Over a year ago

Thanks for clarifying that. Unfortunately though the raw string translates to Message "An error has occurred"

Josh909 Over a year ago

I agree that it does appear that the API is returning an error. My question is about the parameter translation, given that the same query in Postman works. Also, yes the Bearer token has been deleted so its not the exact query.

Colin FAY Over a year ago

Can you share your API token so I can actually try on my machine?

Josh909 Over a year ago

I'm not able to share the token at this stage but we are doing some more investigation on our backend. I've never seen any other successful httr API requests which require a NoAuth token and return a JSON. If there are any similar API's which work, we may find the clue necessary to solve the issue.

Collectives™ on Stack Overflow

r API call for json data and converting to dataframe

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related