JSON example

JSON (short for JavaScript Object Notation) has become one of the standard formats for transmitting data via HTTP request between web browsers and other applications.

JSON is similar to Python code, except for the null value and the prohibition of commas at the end of lists. The basic types are objects (dicts), arrays (lists), strings, numbers, Boolean values and null. All keys of an object must be strings. There are several Python libraries for reading and writing JSON data. I will use json from the Python standard library here. To convert a JSON string into Python form, I use json.loads:

import json


f = open("books.json")
data = json.load(f)

for i in data:
    print(i)

{'Title': 'Python basics', 'Language': 'en', 'Authors': 'Veit Schiele', 'License': 'BSD-3-Clause', 'Publication date': '2021-10-28'}
{'Title': 'Jupyter Tutorial', 'Language': 'en', 'Authors': 'Veit Schiele', 'License': 'BSD-3-Clause', 'Publication date': '2019-06-27'}
{'Title': 'Jupyter Tutorial', 'Language': 'de', 'Authors': 'Veit Schiele', 'License': 'BSD-3-Clause', 'Publication date': '2020-10-26'}
{'Title': 'PyViz Tutorial', 'Language': 'en', 'Authors': 'Veit Schiele', 'License': 'BSD-3-Clause', 'Publication date': '2020-04-13'}

json.dumps, on the other hand, converts a Python object back to JSON:

json.dumps(data)

'[{"Title": "Python basics", "Language": "en", "Authors": "Veit Schiele", "License": "BSD-3-Clause", "Publication date": "2021-10-28"}, {"Title": "Jupyter Tutorial", "Language": "en", "Authors": "Veit Schiele", "License": "BSD-3-Clause", "Publication date": "2019-06-27"}, {"Title": "Jupyter Tutorial", "Language": "de", "Authors": "Veit Schiele", "License": "BSD-3-Clause", "Publication date": "2020-10-26"}, {"Title": "PyViz Tutorial", "Language": "en", "Authors": "Veit Schiele", "License": "BSD-3-Clause", "Publication date": "2020-04-13"}]'

How you convert a JSON object or list of objects into a DataFrame or other data structure for analysis is up to you. Conveniently, you can pass a list of dicts (which were previously JSON objects) to the DataFrame constructor:

import pandas as pd


df = pd.DataFrame(data)

df

	Title	Language	Authors	License	Publication date
0	Python basics	en	Veit Schiele	BSD-3-Clause	2021-10-28
1	Jupyter Tutorial	en	Veit Schiele	BSD-3-Clause	2019-06-27
2	Jupyter Tutorial	de	Veit Schiele	BSD-3-Clause	2020-10-26
3	PyViz Tutorial	en	Veit Schiele	BSD-3-Clause	2020-04-13

pandas.read_json can automatically convert JSON records in certain arrangements into a Series or DataFrame, for example:

df2 = pd.read_json("books.json")

df2

	Title	Language	Authors	License	Publication date
0	Python basics	en	Veit Schiele	BSD-3-Clause	2021-10-28
1	Jupyter Tutorial	en	Veit Schiele	BSD-3-Clause	2019-06-27
2	Jupyter Tutorial	de	Veit Schiele	BSD-3-Clause	2020-10-26
3	PyViz Tutorial	en	Veit Schiele	BSD-3-Clause	2020-04-13

The default options for pandas.read_json assume that each object in the JSON array is a row in the table.

If you want to export data from pandas to JSON, you can use pandas.DataFrame.to_json:

print(df.to_json())

{"Title":{"0":"Python basics","1":"Jupyter Tutorial","2":"Jupyter Tutorial","3":"PyViz Tutorial"},"Language":{"0":"en","1":"en","2":"de","3":"en"},"Authors":{"0":"Veit Schiele","1":"Veit Schiele","2":"Veit Schiele","3":"Veit Schiele"},"License":{"0":"BSD-3-Clause","1":"BSD-3-Clause","2":"BSD-3-Clause","3":"BSD-3-Clause"},"Publication date":{"0":"2021-10-28","1":"2019-06-27","2":"2020-10-26","3":"2020-04-13"}}

print(df.to_json(orient="records"))

[{"Title":"Python basics","Language":"en","Authors":"Veit Schiele","License":"BSD-3-Clause","Publication date":"2021-10-28"},{"Title":"Jupyter Tutorial","Language":"en","Authors":"Veit Schiele","License":"BSD-3-Clause","Publication date":"2019-06-27"},{"Title":"Jupyter Tutorial","Language":"de","Authors":"Veit Schiele","License":"BSD-3-Clause","Publication date":"2020-10-26"},{"Title":"PyViz Tutorial","Language":"en","Authors":"Veit Schiele","License":"BSD-3-Clause","Publication date":"2020-04-13"}]