Extract nested JSON file into a dataframe - python

Question

I am attempting to transform the JSON into a dataframe of just the price information. I understand the API process and how to get the JSON, but then the data I need to put into a dataframe are a series of dictionaries that are nested in a list, which is nested in a dictionary which is nested in another dictionary which is further nested in another dictionary. I would like to get the dataframe to have the columns be the keys of the prices list. I am fairly new to Python, so this is quite challenging. Not sure if it is even possible - deep nestlings.

I have provided my yahoofinancials API code and snippet of the JSON output variables. Thanks for any help.

The printed values look like this, and I am trying to get to the prices portion with all the data (there are many more instances but this is to show where the prices is located in the JSON).

from yahoofinancials import YahooFinancials
raw_data = YahooFinancials('CL=F')
raw_data = raw_data.get_historical_price_data("2017-07-23", "2019-02-13", "weekly")
print(raw_data.values())

dict_values([{'eventsData': {}, 'firstTradeDate': {'formatted_date': '2018-07-23', 'date': 1532318400}, 'currency': 'USD', 'instrumentType': 'FUTURE', 'timeZone': {'gmtOffset': -18000}, 'prices': [{'date': 1532318400, 'high': 69.91999816894531, 'low': 0.0, 'open': 69.19000244140625, 'close': 68.94000244140625, 'volume': 1060185822, 'adjclose': 68.94000244140625, 'formatted_date': '2018-07-23'}, {'date': 1532923200, 'high': 70.43000030517578, 'low': 66.91999816894531, 'open': 68.87000274658203, 'close': 68.5999984741211, 'volume': 1108305118, 'adjclose': 68.5999984741211, 'formatted_date': '2018-07-30'}

Would like to have all the 'prices' data (date, high, low, open, close, volume, adjclose, formatted_date) put into a data frame that I can then manipulate with Pandas and NumPy.

Try using pandas.DataFrame.from_dict

Chris
– Chris

2019-02-15 12:01:18 +00:00
Commented Feb 15, 2019 at 12:01 — Chris
– Chris, Commented Feb 15, 2019 at 12:01

Akhilesh_IN · Accepted Answer · 2019-02-15 12:11:09Z

2

from yahoofinancials import YahooFinancials
import pandas as pd

raw_data = YahooFinancials('CL=F')
raw_data = raw_data.get_historical_price_data("2017-07-23", "2019-02-13", "weekly")
df = pd.DataFrame(raw_data['CL=F']['prices'])

answered Feb 15, 2019 at 12:11

Akhilesh_IN

1,3271 gold badge13 silver badges21 bronze badges

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

Collectives™ on Stack Overflow

Extract nested JSON file into a dataframe - python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related