Python Forum
API -> pandas -> database - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: Data Science (https://python-forum.io/forum-44.html)
+--- Thread: API -> pandas -> database (/thread-15605.html)



API -> pandas -> database - chrismc - Jan-24-2019

Firstly, apologies if I'm not using the right terminology, I'm trying to run before I walk and you will see the problems I'm having. I know how to code in other languages but I've got a tight deadline to meet on this one ....

As the title says I'm trying to get data from an API, do some slight massaging in pandas and write to a relational database.

When I was testing last week I was able to save the API output as a JSON file and read this into python and process it in pandas.

Now I want to read directly from the API and process it the same way. The API is confidential, so I can't post details about it here (but that's not the problem).

This is what I have so far:

Within a function I get the data from the API like this:
req = requests.get(...)
Return the data from the function like this:
return req.content

It seems to get returned as a bytestream, so I make it a string by doing this:
data = data.decode()

This still doesn't look like JSON format (but it's close), so I run a series of replace functions to get the data looking like the file and make it look like this:

[Image: 2019-01-24-09-15-42-Command-Prompt-python.png]

But when I try to convert it to a dataframe:
df0 = pd.DataFrame(data)

I get an error.

Can anyone see what I'm doing wrong ?
Is there a better way to do this ?


RE: API -> pandas -> database - stullis - Jan-24-2019

Can you post the error traceback?

When a JSON is read in Python, it becomes a dict. So, that output is your JSON.


RE: API -> pandas -> database - Larz60+ - Jan-24-2019

Please use code tags, see BBCode
for output use [output][/output]

Please post the url you are getting so that we can try here.
Thanks


RE: API -> pandas -> database - chrismc - Jan-28-2019

Just to close this off, I figured it out, the answer is :

Output:
data = data.decode() data = data.replace("{\"data\":","") data = data.replace("}","") df = pd.DataFrame(ast.literal_eval(data),columns=['index','data'])