Convert Json Dict object into DataFrame in Python

Question

I am getting some nested output in format of Json dict object from a web service in python. The output is coming as nested Json dict Object. Now when I am trying to convert it into DataFrame in python the parentkey is not considered as column. I have five elements under one key. I want total 6 columns will appear in dataframe.

import pandas as pd
data = {'2019-04-04 05:59:00': 
                              {'1. open': '1353.5500', 
                              '2. high': '1354.8000', 
                              '3. low': '1353.0500', 
                              '4. close': '1353.0500', 
                              '5. volume': '25924'}, 
       '2019-04-04 05:58:00': {'1. open': '1354.2500', 
                               '2. high': '1354.2500', 
                               '3. low': '1353.4000', 
                               '4. close': '1353.4500', 
                               '5. volume': '38418'}
        }
df1=pd.DataFrame(data)
print(df1)

"""
  Output --
                2019-04-04 05:59:00 2019-04-04 05:58:00
  1. open             1353.5500           1354.2500
  2. high             1354.8000           1354.2500
  3. low              1353.0500           1353.4000
  4. close            1353.0500           1353.4500
  5. volume               25924               38418
"""

df2=df1.transpose()
print(df2)

""" 
  Output --
                         1. open    2. high     3. low   4. close 5. volume
2019-04-04 05:59:00  1353.5500  1354.8000  1353.0500  1353.0500     25924
2019-04-04 05:58:00  1354.2500  1354.2500  1353.4000  1353.4500     38418
"""

Here this first date field is considered as index so my first column starts from (1.open) but my need is the first column should be date.

Help on this would be appreciated.

The result should come like :

"""
Index    Date                 1. open    2. high    3. low     4. close      5. volume
0        2019-04-04 05:59:00  1353.5500  1354.8000  1353.0500  1353.0500     25924
1        2019-04-04 05:58:00  1354.2500  1354.2500  1353.4000  1353.4500     38418
"""

EliadL · Accepted Answer · 2019-04-04 15:10:52Z

1

df2.rename_axis(index='Date').reset_index()

Gives you:

                  Date    1. open    2. high     3. low   4. close 5. volume
0  2019-04-04 05:58:00  1354.2500  1354.2500  1353.4000  1353.4500     38418
1  2019-04-04 05:59:00  1353.5500  1354.8000  1353.0500  1353.0500     25924

Also please note there's a simpler way to build df2 using data:

df2 = pd.DataFrame.from_dict(data, orient='index')

Putting the two parts together:

pd.DataFrame.from_dict(data, orient='index').rename_axis(index='Date').reset_index()

To name your index, you can add .rename_axis(index='Index') to the end:

                      Date    1. open    2. high     3. low   4. close 5. volume
Index
0      2019-04-04 05:58:00  1354.2500  1354.2500  1353.4000  1353.4500     38418
1      2019-04-04 05:59:00  1353.5500  1354.8000  1353.0500  1353.0500     25924

edited Apr 4, 2019 at 15:10

answered Apr 4, 2019 at 14:37

EliadL

7,2283 gold badges30 silver badges45 bronze badges

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

Collectives™ on Stack Overflow

Convert Json Dict object into DataFrame in 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