Pandas: Convert JSON to Pandas Dataframe

Question

Experts,

I am having issues parsing Json to Pandas and then save it in CSV format.

data2 = {"date":"2018-01-02","data":{"AAPL":{"open":"170.16","close":"172.26","high":"172.30","low":"169.26","volume":"25555934"},"MSFT":{"open":"86.13","close":"85.95","high":"86.31","low":"85.50","volume":"22483797"}}}

If I try :

df = pd.DataFrame.from_dict(json_normalize(data2), orient='columns')
print(df)

Everything gets printed in a single line :

If I do :

jdata = json.loads(data2)
df = pd.DataFrame(jdata)
print(df.T)

I get an error : TypeError: the JSON object must be str, bytes or bytearray, not 'dict'

I am looking to print it in the following table format so I can then save it as csv :

Date        Data    Open    Close   High    Low     Volume
2018-01-02  AAPL    170.16  172.26  172.30  169.26  25555934
2018-01-02  MSFT    86.13   85.95   86.31   85.50   22483797

What is the correct way to achieve my goal ?

Thanks !!

Zito Relova · Accepted Answer · 2018-12-13 03:04:17Z

5

You can use apply to turn the dict keys into pandas Series

df = pd.DataFrame.from_dict(data2)
df = pd.concat([df['date'],df['data'].apply(pd.Series)], axis=1)
print(df)

            date    open   close    high     low    volume
AAPL  2018-01-02  170.16  172.26  172.30  169.26  25555934
MSFT  2018-01-02   86.13   85.95   86.31   85.50  22483797

answered Dec 13, 2018 at 3:04

Zito Relova

1,0412 gold badges13 silver badges33 bronze badges

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

4 Comments

BENY Over a year ago

This is very unefficient

Gabe H. Coud Over a year ago

@W-B, let me pick your brain, what is the difference vs the method you proposed ? What are the downsides ?

BENY Over a year ago

@EricArambula speed, apply is timing cost function , since you have stock data , when the size is increase , you will find apply + pd.Serise slow

Akhan Over a year ago

from where does this data came from reference: df['data'] ?

BENY · Accepted Answer · 2018-12-13 03:01:17Z

1

I will using your original output and modify it

s=pd.DataFrame(data2)
pd.concat([s.drop('data',1),pd.DataFrame(s.data.tolist(),index=s.index)],1)
            date   close    high     low    open    volume
AAPL  2018-01-02  172.26  172.30  169.26  170.16  25555934
MSFT  2018-01-02   85.95   86.31   85.50   86.13  22483797

answered Dec 13, 2018 at 3:01

BENY

324k22 gold badges176 silver badges250 bronze badges

Collectives™ on Stack Overflow

Pandas: Convert JSON to Pandas Dataframe

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related