Convert JSON response to pandas dataframe

Question

I am getting json response from a site

response = requests.get(API_URL, params=data)
response_json = response.json()
a = response_json['Time Series (5min)']
print(a)

Output:

{
   '2021-02-19 18:45:00': {'1. open': '119.1800', '2. high': '119.1800', '3. low': '119.1800', '4. close': '119.1800', '5. volume': '1250'},
   '2021-02-19 18:25:00': {'1. open': '119.1000', '2. high': '119.1000', '3. low': '119.1000', '4. close': '119.1000', '5. volume': '701'} ... }

Than i am trying to convert it to dataframe:

data = pd.json_normalize(data=a)
print(data)

But in the result i have 1 row with lots of columns.

So how should i convert it to get table with datetime as index and keys in JSON as columns?

You can use pd.DataFrame.from_dict(a, orient='index')

Shubham Sharma
– Shubham Sharma

2021-02-21 15:31:35 +00:00
Commented Feb 21, 2021 at 15:31 — Shubham Sharma
– Shubham Sharma, Commented Feb 21, 2021 at 15:31

Rob Raymond · Accepted Answer · 2021-02-21 17:42:56Z

1

use DataFrame()
transpose for shape
reindex() to convert index to date/time

a = {'2021-02-19 18:45:00': {'1. open': '119.1800',
  '2. high': '119.1800',
  '3. low': '119.1800',
  '4. close': '119.1800',
  '5. volume': '1250'},
 '2021-02-19 18:25:00': {'1. open': '119.1000',
  '2. high': '119.1000',
  '3. low': '119.1000',
  '4. close': '119.1000',
  '5. volume': '701'}}


df = pd.DataFrame(a).T
df.reindex(pd.to_datetime(df.index))

	1. open	2. high	3. low	4. close	5. volume
2021-02-19 18: 45: 00	119.18	119.18	119.18	119.18	1250
2021-02-19 18: 25: 00	119.1	119.1	119.1	119.1	701

edited Feb 21, 2021 at 17:42

answered Feb 21, 2021 at 17:37

Rob Raymond

31.5k3 gold badges19 silver badges34 bronze badges

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

Collectives™ on Stack Overflow

Convert JSON response to pandas dataframe

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