1

I have the following code:

import requests
import json

url = "https://apidojo-yahoo-finance-v1.p.rapidapi.com/stock/v2/get-chart"

querystring = {"interval":"5m","symbol":"AMRN","range":"1d","region":"US"}

headers = {
    'x-rapidapi-key': "xx",
    'x-rapidapi-host': "apidojo-yahoo-finance-v1.p.rapidapi.com"
    }

response = requests.request("GET", url, headers=headers, params=querystring)

Tihs returns me a complex JSON: shared here.

https://codepen.io/luis-valencia/pen/pobZqVJ

Now I need to convert this to a pandas dataframe, but I don't know how, specially because in the pandas dataframe I require only"

{ timestamp, open, close, high, low, volume }

But in the json of the API call, the timestamp and the values are returned in 2 different elements

Improve this question
0

3 Answers 3

Reset to default
3

I took a deep dive to your json object. This is the best i could get:

With j being your json as dictionary:

import pandas as pd
import json

j=json.loads(response.json())

data=[j['chart']['result'][0]['timestamp']]+ list(j['chart']['result'][0]['indicators']['quote'][0].values())

df=pd.DataFrame({'timestamp':data[0], 'close':data[1], 'open':data[2], 'high':data[3], 'low':data[4], 'volume':data[5]})

>>> print(df)

     timestamp   close   open   high    low   volume
0   1604578500  5.1200  5.120  5.120  5.120      0.0
1   1604580900  5.0600  5.060  5.060  5.060      0.0
2   1604581200  5.0500  5.060  5.060  5.050      0.0
3   1604581800  5.1000  5.100  5.100  5.100      0.0
4   1604582100  5.0200  5.120  5.120  5.000      0.0
..         ...     ...    ...    ...    ...      ...
71  1604602200  4.7100  4.655  4.710  4.655  38036.0
72  1604602500  4.6999  4.705  4.710  4.685  31368.0
73  1604602800  4.6950  4.700  4.700  4.690  24811.0
74  1604603100     NaN    NaN    NaN    NaN      NaN
75  1604603119  4.6950  4.695  4.695  4.695      0.0

[76 rows x 6 columns]
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

first convert your json to dict and then the dict to DataFrame:

json = response.json()
df = pd.DataFrame.from_dict(json)

If yout json do not contain lists for each dictionary key you may want to try orienting dataframe by the index instead:

df = pd.DataFrame.from_dict(json, orient='index')

for your specific json use:

df = pd.DataFrame.from_dict(json['chart']['result'][0]['indicators']['quote'][0])

and add the tymestamp later

Improve this answer

4 Comments

that doesnt return the columnas as I explained above, it puts ALL into one df cell.
it is due to the way your json is. The list for 'timestamp' is json['chart']['result'][0]['timestamp'], the one for 'open' is json['chart']['result'][0]['indicators']['quote'][0]['open'], etc. get each of your lists then create a dataframe
I edited my answer with specifications for your json. Check it please
.json() to convert to a dict was exactly what I was looking for to convert JSON (from python requests) to a pandas dataframe (which happens to be the title of the question and the reason I landed on this page). Thanks!
0

Here is one approach. I used pprint() to understand the format of the response.

import pandas as pd

# from Code Pen (manually converted null -> None)
result = {"chart":{"result":[{"meta":{"currency":"USD","symbol":"AMRN","exchangeName":"NMS","instrumentType":"EQUITY","firstTradeDate":733674600,"regularMarketTime":1604603119,"gmtoffset":-18000,"timezone":"EST","exchangeTimezoneName":"America/New_York","regularMarketPrice":4.695,"chartPreviousClose":4.99,"previousClose":4.99,"scale":3,"priceHint":4,"currentTradingPeriod":{"pre":{"timezone":"EST","start":1604566800,"end":1604586600,"gmtoffset":-18000},"regular":{"timezone":"EST","start":1604586600,"end":1604610000,"gmtoffset":-18000},"post":{"timezone":"EST","start":1604610000,"end":1604624400,"gmtoffset":-18000}},"tradingPeriods":{"pre":[[{"timezone":"EST","start":1604566800,"end":1604586600,"gmtoffset":-18000}]],"post":[[{"timezone":"EST","start":1604610000,"end":1604624400,"gmtoffset":-18000}]],"regular":[[{"timezone":"EST","start":1604586600,"end":1604610000,"gmtoffset":-18000}]]},"dataGranularity":"5m","range":"1d","validRanges":["1d","5d","1mo","3mo","6mo","1y","2y","5y","10y","ytd","max"]},"timestamp":[1604578500,1604580900,1604581200,1604581800,1604582100,1604582400,1604582700,1604583000,1604583300,1604583600,1604583900,1604584200,1604584500,1604584800,1604585100,1604585400,1604585700,1604586000,1604586300,1604586600,1604586900,1604587200,1604587500,1604587800,1604588100,1604588400,1604588700,1604589000,1604589300,1604589600,1604589900,1604590200,1604590500,1604590800,1604591100,1604591400,1604591700,1604592000,1604592300,1604592600,1604592900,1604593200,1604593500,1604593800,1604594100,1604594400,1604594700,1604595000,1604595300,1604595600,1604595900,1604596200,1604596500,1604596800,1604597100,1604597400,1604597700,1604598000,1604598300,1604598600,1604598900,1604599200,1604599500,1604599800,1604600100,1604600400,1604600700,1604601000,1604601300,1604601600,1604601900,1604602200,1604602500,1604602800,1604603100,1604603119],"indicators":{"quote":[{"close":[5.12,5.06,5.05,5.1,5.02,4.99,4.8,4.7801,4.7999,4.8,4.8,4.8,4.79,4.82,4.81,4.82,4.86,4.86,4.95,4.6595001220703125,4.688799858093262,4.659999847412109,4.809999942779541,4.75,4.71999979019165,4.6819000244140625,4.651899814605713,4.658400058746338,4.652299880981445,4.650000095367432,4.679999828338623,4.740099906921387,4.760000228881836,4.724999904632568,4.71999979019165,4.755000114440918,4.769999980926514,4.755000114440918,4.735000133514404,4.74399995803833,4.755000114440918,4.735000133514404,4.735000133514404,4.710000038146973,4.71999979019165,4.724999904632568,4.718100070953369,4.730000019073486,4.704999923706055,4.699399948120117,4.690000057220459,4.679999828338623,4.679999828338623,4.679999828338623,4.670000076293945,4.652500152587891,4.659999847412109,4.664999961853027,4.675000190734863,4.675000190734863,4.679999828338623,4.659999847412109,4.644999980926514,4.659999847412109,4.650000095367432,4.65500020980835,4.65500020980835,4.644999980926514,4.639900207519531,4.650000095367432,4.65500020980835,4.710000038146973,4.699900150299072,4.695000171661377,None,4.695000171661377],"open":[5.12,5.06,5.06,5.1,5.12,5.0,4.9,4.7901,4.8,4.8,4.7999,4.8,4.79,4.82,4.81,4.82,4.82,4.86,4.87,4.840000152587891,4.659999847412109,4.6875,4.670000076293945,4.798099994659424,4.75,4.723800182342529,4.683899879455566,4.65500020980835,4.65500020980835,4.659999847412109,4.65500020980835,4.680099964141846,4.739999771118164,4.755000114440918,4.71999979019165,4.730000019073486,4.755000114440918,4.760000228881836,4.755000114440918,4.735000133514404,4.741600036621094,4.760000228881836,4.735000133514404,4.735000133514404,4.708099842071533,4.718100070953369,4.71999979019165,4.715000152587891,4.730000019073486,4.699999809265137,4.699999809265137,4.690000057220459,4.679999828338623,4.683800220489502,4.679999828338623,4.670100212097168,4.659900188446045,4.659999847412109,4.66480016708374,4.675000190734863,4.670000076293945,4.684999942779541,4.659999847412109,4.644999980926514,4.668700218200684,4.658899784088135,4.659999847412109,4.65500020980835,4.64109992980957,4.635000228881836,4.65500020980835,4.65500020980835,4.704999923706055,4.699999809265137,None,4.695000171661377],"high":[5.12,5.06,5.06,5.1,5.12,5.04,4.9,4.8,4.8,4.8,4.8,4.8,4.8001,4.82,4.81,4.83,4.86,4.86,4.95,4.889999866485596,4.724800109863281,4.690000057220459,4.815000057220459,4.809999942779541,4.789999961853027,4.730000019073486,4.6880998611450195,4.664999961853027,4.659999847412109,4.659999847412109,4.684999942779541,4.764999866485596,4.789999961853027,4.7581000328063965,4.739999771118164,4.760000228881836,4.769999980926514,4.764999866485596,4.760000228881836,4.75,4.760000228881836,4.768899917602539,4.75,4.735000133514404,4.71999979019165,4.739999771118164,4.724999904632568,4.730000019073486,4.735000133514404,4.704999923706055,4.699999809265137,4.695000171661377,4.690000057220459,4.690000057220459,4.687300205230713,4.679999828338623,4.670000076293945,4.675000190734863,4.679500102996826,4.678100109100342,4.690000057220459,4.690000057220459,4.670000076293945,4.670000076293945,4.669899940490723,4.659999847412109,4.659999847412109,4.659999847412109,4.650000095367432,4.659999847412109,4.664999961853027,4.710000038146973,4.710000038146973,4.699999809265137,None,4.695000171661377],"low":[5.12,5.06,5.05,5.1,5.0,4.99,4.77,4.7801,4.7999,4.7901,4.7999,4.79,4.79,4.82,4.81,4.82,4.82,4.85,4.86,4.619999885559082,4.630000114440918,4.593999862670898,4.670000076293945,4.744999885559082,4.71999979019165,4.65500020980835,4.639999866485596,4.639999866485596,4.639999866485596,4.650000095367432,4.614999771118164,4.680099964141846,4.730000019073486,4.71999979019165,4.71999979019165,4.704999923706055,4.75,4.730000019073486,4.730000019073486,4.7245001792907715,4.724999904632568,4.731100082397461,4.71999979019165,4.699999809265137,4.679999828338623,4.715000152587891,4.701099872589111,4.699999809265137,4.699999809265137,4.679999828338623,4.679999828338623,4.670000076293945,4.670000076293945,4.650000095367432,4.664999961853027,4.650000095367432,4.650000095367432,4.65500020980835,4.659999847412109,4.664999961853027,4.670000076293945,4.65500020980835,4.644999980926514,4.644999980926514,4.650000095367432,4.650000095367432,4.650000095367432,4.639999866485596,4.630000114440918,4.635000228881836,4.650000095367432,4.65500020980835,4.684999942779541,4.690000057220459,None,4.695000171661377],"volume":[0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,727514,383730,518259,375858,146610,136773,201506,187065,135156,109423,68829,269577,143004,176817,92720,73991,110323,80055,156048,59170,44408,56697,97600,74426,61372,120491,51939,58761,55472,60604,49009,104735,82872,61292,148882,95217,85067,64669,78016,63939,43058,37312,148380,123094,75770,32656,70807,43425,95237,72800,79580,61712,38036,31368,24811,None,0]}]}}],"error":None}}

d = result['chart']['result'][0]['indicators']['quote'][0]
closes  = d['close']
opens   = d['open']
lows    = d['low']
highs   = d['high']
volumes = d['volume']
timestamps = result['chart']['result'][0]['timestamp']

df = pd.DataFrame({'timestamp': timestamps, 
                   'open': opens, 
                   'close': closes, 
                   'high': highs, 
                   'low': lows, 
                   'volume': volumes,})
print(df.shape)
print(df.head(10))

(76, 6)
    timestamp    open   close  high     low  volume
0  1604578500  5.1200  5.1200  5.12  5.1200     0.0
1  1604580900  5.0600  5.0600  5.06  5.0600     0.0
2  1604581200  5.0600  5.0500  5.06  5.0500     0.0
3  1604581800  5.1000  5.1000  5.10  5.1000     0.0
4  1604582100  5.1200  5.0200  5.12  5.0000     0.0
5  1604582400  5.0000  4.9900  5.04  4.9900     0.0
6  1604582700  4.9000  4.8000  4.90  4.7700     0.0
7  1604583000  4.7901  4.7801  4.80  4.7801     0.0
8  1604583300  4.8000  4.7999  4.80  4.7999     0.0
9  1604583600  4.8000  4.8000  4.80  4.7901     0.0
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.