CODEWITHSUNDEEP
pythonjson
lotteryman
lotteryman

Reputation: 399

python: read json with pandas

I want to get the timestamp,high,low,open, close for the the json link below but I get only error charterror Noneresult [{u'indicators': {u'quote': [{u'high': [45.25,...

My code is below:

df = pd.read_json('https://query1.finance.yahoo.com/v7/finance/chart/CPN.BK?range=2y&interval=1d&indicators=quote&includeTimestamps=true')
output = df[['timestamp','open','high,'low','close']]

Please guide how to get the data into dataframe

Upvotes: 0

Views: 2928

Answers (1)

Tiny.D
Tiny.D

Reputation: 6556

If you use pandas to read_json from your url, it will save all json data into one cell (column chart, row result).

Based on your code, you have to extract the dictionary data for 'timestamp','open','high,'low' and 'close', after that you can pass them to pandas DataFrame:

import pandas as pd
df = pd.read_json('https://query1.finance.yahoo.com/v7/finance/chart/CPN.BK?range=2y&interval=1d&indicators=quote&includeTimestamps=true')
data = df['chart']['result'][0]
result = {'timestamp':data['timestamp'],
          'open':data['indicators']['quote'][0]['open'],
          'high':data['indicators']['quote'][0]['high'],
          'low':data['indicators']['quote'][0]['low'],
          'close':data['indicators']['quote'][0]['close']
         }
df1 = pd.DataFrame(result, columns==['timestamp','open','high','low','close'])
df1

df1 will be:

    timestamp    open   high    low     close   
0   1442977200   44.50  45.25   44.25   45.00       
1   1443063600   44.75  45.75   44.50   45.00   
2   1443150000   44.75  45.00   44.25   44.50   
3   1443409200   44.25  44.25   43.00   43.00   
4   1443495600   42.50  44.50   42.25   44.00   
5   1443582000   44.25  44.75   43.50   44.75   
6   1443668400   44.50  45.00   44.25   45.00   
7   1443754800   45.00  45.00   44.00   44.25   
8   1444014000   44.25  44.75   43.75   44.50   
...

Alternatively, you can load the json from the url (refer to this answer), then extract the dictionary data ('timestamp','open','high,'low' and 'close'), pass it to pandas to generate the result dataframe.

Upvotes: 1

Related Questions