JSONDecodeError when trying to extract a json-CodePudding

I am getting this error every time i try to extract the json in this api Request_URL='https://freeserv.dukascopy.com/2.0/api/group=quotes&method=realtimeSentimentIndex&enabled=true&key=bsq3l3p5lc8w4s0c&type=swfx&jsonp=_callbacks____1kvynkpid'

JSONDecodeError: Expecting value: line 1 column 1 (char 0)

import json
import requests
import pandas as pd

r = requests.get(Request_URL)
df = pd.DataFrame(r.json())

CodePudding user response：

The problem is that the response coming back is in JSONP format. That is, it is JavaScript consisting of a call to a function with an argument that is a JavaScript structure (which should be a valid JSON string if it had single quotes around it, but there is no guarantee that it is). In part it looks like:

_callbacks____1kvynkpid([{"id":"10012" ...])

So we need to first remove the JavaScript call, which are the leading characters up to and including the first ( character and the final ):

import requests
import json

request_url = 'https://freeserv.dukascopy.com/2.0/api?group=quotes&method=realtimeSentimentIndex&enabled=true&key=bsq3l3p5lc8w4s0c&type=swfx&jsonp=_callbacks____1kvynkpid'

r = requests.get(request_url)
text = r.text
idx = text.index('(')
# skip everything up to and including opening '(' and then skip closing ')'
text = text[idx 1:-1]
print(json.loads(text))

Prints:

[{'id': '10012', 'title': 'ESP.IDX/EUR', 'date': '1636925400000', 'long': '71.43', 'short': '28.57'}, {'id': '10104', 'title': 'AUS.IDX/AUD', 'date': '1636925400000', 'long': '70.59', 'short': '29.41'}, {'id': '10266', 'title': 'NLD.IDX/EUR', 'date': '1636925400000', 'long': '73.48', 'short': '26.52'},

... data too big too fully reproduce

 {'id': '82862', 'title': 'MAT/USD', 'date': '1636925400000', 'long': '70.27', 'short': '29.73'}, {'id': '82866', 'title': 'ENJ/USD', 'date': '1636925400000', 'long': '72.16', 'short': '27.84'}]

In this case the structure when interpreted as a string adhered to the JSON format and so we were able to parse it with json.loads(). But what if the JavaScript structure had been (in part):

[{'id':'10012'}]

This is both legal JavaScript and legal Python, but not legal JSON because strings must be enclosed within double-quotes for it to be valid JSON. But since it is legal Python, we could use ast.literal_eval:

import requests
import ast

request_url = 'https://freeserv.dukascopy.com/2.0/api?group=quotes&method=realtimeSentimentIndex&enabled=true&key=bsq3l3p5lc8w4s0c&type=swfx&jsonp=_callbacks____1kvynkpid'

r = requests.get(request_url)
text = r.text
idx = text.index('(')
# skip everything up to and including opening '(' and then skip closing ')'
text = text[idx 1:-1]
print(ast.literal_eval(text))

Of course, for the current situation both json.loads and ast.literal_eval happened to work. However, if the JavaScript structure had been:

[{id:'10012'}]

This is valid JavaScript but, alas, not valid Python and cannot be parsed with either json.loads or ast.literal_eval.