CODEWITHSUNDEEP
pythonjsonpandasrequest
RomaFUN
RomaFUN

Reputation: 59

Convert JSON response to pandas dataframe

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. Output

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

Upvotes: 0

Views: 699

Answers (1)

Rob Raymond
Rob Raymond

Reputation: 31146

  • 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

Upvotes: 1

Related Questions