1

I have a JSON file named stocks.json that looks as follows (note the lack of square brackets in the source file):

{"MSFT": {"exchange": "Nasdaq", "price": 275.79}, "FB": {"exchange": "Nasdaq", "price": 320.22}, "TSLA": {"exchange": "Nasdaq", "price": 990.83}, "GE": {"exchange": "Nasdaq", "price": 83.20}}

I would like to transform this data into a Pandas dataframe that looks as follows:

symbol exchange price MSFT Nasdaq 275.79 FB Nasdaq 320.22 TSLA Nasdaq 990.83 GE NYSE 83.20

My attempt is:

import pandas as pd stock_data = pd.read_json('stocks.json', lines=True) stock_data_normalized = pd.json_normalize(stock_data)

Unfortunately, I get the following when calling stock_data_normalized:

0 1 2 3

Any assistance would be most appreciated. Thanks!

Improve this question

1 Answer 1

Reset to default
3

You can just using the pd.DataFrame() constructor, and then transpose and reset the index:

df = pd.DataFrame(d).T.reset_index().rename({'index': 'symbol'}, axis=1)

Output:

>>> df symbol exchange price 0 MSFT Nasdaq 275.79 1 FB Nasdaq 320.22 2 TSLA Nasdaq 990.83 3 GE Nasdaq 83.2
Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

This solution results in a column named "0" with elements that look like: {"exchange": "Nasdaq", "price": 275.79}. How would I "unpack" that dictionary into separate columns?
Really? What data are you exactly using? It must be different from what you're showing. Also, what's your pandas version?
Yes, the structure of the data in the source file is EXACTLY the same as what I provided. The Pandas version is 1.3.4. Did you apply your solution on the structure of the data I provided?
Yes, I copied all your json, put d = before it, and ran it, like this: d = {"MSFT": {"exchange": "Nasdaq", "price": ...
Maybe your data is nested in another object or array or something?
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.