Help with reading json file

[hhchenfx]

Hi,
Can anyone help me to review this short code below that read json data from url?
I am not sure why it give error.
Thank you,
HHC

data = pd.read_json('http://hl7.org/fhir/2018Sep/invoice-example.json')

[Larz60+]

what was the error, please show unmodified and complete error traceback (bound by bbcode tags)

[hhchenfx]

The whole traceback is:

Error:
Traceback (most recent call last):
  File "<input>", line 1, in <module>
  File "D:\Python\Homework\venv\lib\site-packages\pandas\util\_decorators.py", line 199, in wrapper
    return func(*args, **kwargs)
  File "D:\Python\Homework\venv\lib\site-packages\pandas\util\_decorators.py", line 299, in wrapper
    return func(*args, **kwargs)
  File "D:\Python\Homework\venv\lib\site-packages\pandas\io\json\_json.py", line 563, in read_json
    return json_reader.read()
  File "D:\Python\Homework\venv\lib\site-packages\pandas\io\json\_json.py", line 694, in read
    obj = self._get_object_parser(self.data)
  File "D:\Python\Homework\venv\lib\site-packages\pandas\io\json\_json.py", line 716, in _get_object_parser
    obj = FrameParser(json, **kwargs).parse()
  File "D:\Python\Homework\venv\lib\site-packages\pandas\io\json\_json.py", line 831, in parse
    self._parse_no_numpy()
  File "D:\Python\Homework\venv\lib\site-packages\pandas\io\json\_json.py", line 1078, in _parse_no_numpy
    self.obj = DataFrame(
  File "D:\Python\Homework\venv\lib\site-packages\pandas\core\frame.py", line 529, in __init__
    mgr = init_dict(data, index, columns, dtype=dtype)
  File "D:\Python\Homework\venv\lib\site-packages\pandas\core\internals\construction.py", line 287, in init_dict
    return arrays_to_mgr(arrays, data_names, index, columns, dtype=dtype)
  File "D:\Python\Homework\venv\lib\site-packages\pandas\core\internals\construction.py", line 80, in arrays_to_mgr
    index = extract_index(arrays)
  File "D:\Python\Homework\venv\lib\site-packages\pandas\core\internals\construction.py", line 404, in extract_index
    raise ValueError(
ValueError: Mixing dicts with non-Series may lead to ambiguous ordering.

buran write Jul-07-2021, 07:06 AM:
Please, use proper tags when post code, traceback, output, etc. This time I have added tags for you.
See BBcode help for more info.

[buran]

Exactly what datafarme would you expect to produce from this JSON? It doesn't look suitable for processing as dataframe

Why not simply read the JSON and then process it?

import requests

response = requests.get('http://hl7.org/fhir/2018Sep/invoice-example.json')
data = response.json()

print(data)
print(f"Total Gross: {data['totalGross']['currency']} {data['totalGross']['value']}")

Output:
{'resourceType': 'Invoice', 'id': 'example', 'text': {'status': 'generated', 'div': '<div xmlns="http://www.w3.org/1999/xhtml">Example of Invoice</div>'}, 'identifier': [{'system': 'http://myHospital.org/Invoices', 'value': '654321'}], 'status': 'issued', 'subject': {'reference': 'Patient/example'}, 'date': '2017-01-25T08:00:00+01:00', 'participant': [{'role': {'coding': [{'system': 'http://snomed.info/sct', 'code': '17561000', 'display': 'Cardiologist'}]}, 'actor': {'reference': 'Practitioner/example'}}], 'issuer': {'identifier': {'system': 'http://myhospital/NamingSystem/departments', 'value': 'CARD_INTERMEDIATE_CARE'}}, 'account': {'reference': 'Account/example'}, 'totalNet': {'value': 40, 'currency': 'EUR'}, 'totalGross': {'value': 48, 'currency': 'EUR'}}
Total Gross: EUR 48

[snippsat]

To read it do.

data = pd.read_json('http://hl7.org/fhir/2018Sep/invoice-example.json', orient='index')

Quick test NoteBook
I just mess around a transpose,the way to go is look at Pandas json_normalize(),as in post under.
How to parse JSON data with Python Pandas?

[hhchenfx]

Thank you all for helping.
I understand it now.
HHC