Json to Data Frame then Manipulating the data

[ml36766]

I am pulling multiple Items from an API, when i pass the Json Data to a dataframe the information from the second, third etc. is added to the right of the data frame instead of below it.

Additionally, the column headers are equal to the body in the request. how do i change this?

How information is currently:

Body.Date | Body.Stock.Apple | Body.Type | Body.Date | Body.ABC.Stock.FB | Body.ABC.Type |
20190101 |99 |Series |20190101 | 100 |Series
20190201 |98 |series |20190201 | 98 |Series
20190301 |97 |Series |20190301 | 101 |Series

How i want it to look:

Date |Stock |Price
20190101| Apple |99
20190201| Apple |98
20190301| Apple|97
20190101| FB |100
20190201| FB |98
20190301| FB |101

Basically dropping the columns i do not want / repeated, making the Stock prices a single column and renaming the column headers. I have 800+ stocks i need to do this for, so looking for a very automated solution

[technocrat]

I haven't used it myself, but tidyjson seems to do what you're looking for -- https://github.com/sailthru/tidyjson

[ml36766]

Thanks, but i am not able to find exactly what i am looking for.

I tried melting the data, but i would have to do this one by one and for 800+ stocks this is not possible.

do you have any suggestions as to how to streamline this?

[technocrat]

Can you post a reproducible example, called a reprex with some sample data in the form you're working with?

[ml36766]

Hello,

Unfortunately i cannot share much more as it is a private connection to the API.

Essentially, in the screenshot i attached, you can see in the top table how the data frame is being created and the below is how i need it.

The challenge here is doing this for 800+ entities and deriving the Bond ID from a header.

library(curl)
library(httr)
library(jsonlite)
library(tidyr)

url_Credit<- 'https://api.citivelocity.com/markets/analytics/chartingbe/rest/external/authed/data?client_id=ABC'
req_Credit<- '{"startDate": 20190301, "endDate": 20190331 ,"tags":["CREDIT.BOND.US71647NAZ24.PRICE","CREDIT.BOND.USP57908AH15.PRICE"]}'
resp_Credit<- POST(url_Credit , body=req_Credit, add_headers("accept" = "application/json","authorization" = Modifed_Token , "content-type" = "application/json"))
data_Credit<- (toJSON(content(resp_Credit, as = "parsed")))
Credit_data_Prices <-as.data.frame(fromJSON(data_Credit))
prettify(data_Credit)