json to dataframe conversion / Python

Question

I just imported an API to get the exchange rate of Taiwan dollar (TWD) with other currencies. So I import it with this code :

import requests
r=requests.get('http://api.cambio.today/v1/full/TWD/json?key=X')
dico = r.json()

And it gives me:

{'result': {'from': 'TWD',
  'conversion': [{'to': 'AED',
    'date': '2020-06-23T07:23:49',
    'rate': 0.124169},
   {'to': 'AFN', 'date': '2020-06-23T07:19:53', 'rate': 2.606579},
   {'to': 'ALL', 'date': '2020-06-19T20:48:10', 'rate': 3.74252},
   {'to': 'AMD', 'date': '2020-06-22T12:00:19', 'rate': 16.176679},
   {'to': 'AOA', 'date': '2020-06-22T12:32:59', 'rate': 20.160418},
   {'to': 'ARS', 'date': '2020-06-23T08:00:01', 'rate': 2.363501}
  ]}
}

To turn it into a dataframe I tried two things:

df = pd.DataFrame(dico.get('result', {}))

and

from pandas.io.json import json_normalize
dictr = r.json()
df = json_normalize(dictr)

In both cases, I end up with a "conversion" column with one line per currency. For example the first line is: "{'to': 'AFN', 'date': '2020-06-23T07:19:53', 'rate': 2.606579}". While I would like to have one column for the currency and one for the exchange rate.

Could someone please help me?

could you please paste valid json. I am pretty sure there are typos here — puchal
– puchal, Commented Jun 23, 2020 at 10:42

puchal · Accepted Answer · 2020-06-23 10:47:52Z

1

The json you pasted is not valid json. But I guess the format of the json should be this one

{'result': {'from': 'TWD',
  'conversion': [{'to': 'AED',
    'date': '2020-06-23T07:23:49',
    'rate': 0.124169},
   {'to': 'AFN', 'date': '2020-06-23T07:19:53', 'rate': 2.606579},
   {'to': 'ALL', 'date': '2020-06-19T20:48:10', 'rate': 3.74252},
   {'to': 'AMD', 'date': '2020-06-22T12:00:19', 'rate': 16.176679},
   {'to': 'AOA', 'date': '2020-06-22T12:32:59', 'rate': 20.160418},
   {'to': 'ARS', 'date': '2020-06-23T08:00:01', 'rate': 2.363501}]}}

In that case to create dataframe you want you can use

df = pd.DataFrame(dico.get('result', {}).get('conversion', {}))

answered Jun 23, 2020 at 10:47

puchal

2,39117 silver badges29 bronze badges

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

Comments

Code Ninja 2C4U · Accepted Answer · 2020-06-24 11:24:26Z

0

You need to do get the "conversion" property value with the list of conversion rates, use this:

df = pd.DataFrame(dico["result"]["conversion"])

It will format your conversion data like this:

    to  date                rate
0   AED 2020-06-23T07:23:49 0.124169
1   AFN 2020-06-23T07:19:53 2.606579
2   ALL 2020-06-19T20:48:10 3.742520
3   AMD 2020-06-22T12:00:19 16.176679
4   AOA 2020-06-22T12:32:59 20.160418
5   ARS 2020-06-23T08:00:01 2.363501

answered Jun 24, 2020 at 11:24

Code Ninja 2C4U

1341 silver badge11 bronze badges

Collectives™ on Stack Overflow

json to dataframe conversion / Python

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related