From | to | Use |
---|---|---|
JSON | DataFrame | pandas.read_json() |
DataFrame | JSON | df.to_json() or |
df.to_json(orient='records') | ||
JSON | Python object | json.loads() |
Python object | JSON | json.dumps() |
Ref:-
obj = """
{"name": "Wes",
"places_lived": ["United States", "Spain", "Germany"],
"pet": null,
"siblings": [{"name": "Scott", "age": 30, "pets": ["Zeus", "Zuko"]},
{"name": "Katie", "age": 38,
"pets": ["Sixes", "Stache", "Cisco"]}]
}
"""
# Use json.loads to read JSON data
import json
result = json.loads(obj)
print(type(result))
result
<class 'dict'>
{'name': 'Wes', 'pet': None, 'places_lived': ['United States', 'Spain', 'Germany'], 'siblings': [{'age': 30, 'name': 'Scott', 'pets': ['Zeus', 'Zuko']}, {'age': 38, 'name': 'Katie', 'pets': ['Sixes', 'Stache', 'Cisco']}]}
# Use json.dumps to convert a Python object back to JSON
asjson = json.dumps(result)
asjson
'{"name": "Wes", "places_lived": ["United States", "Spain", "Germany"], "pet": null, "siblings": [{"name": "Scott", "age": 30, "pets": ["Zeus", "Zuko"]}, {"name": "Katie", "age": 38, "pets": ["Sixes", "Stache", "Cisco"]}]}'
# You can pass a list of dicts (which were previously JSON objects)
# to the DataFrame constructor and select a subset of the data fields
import pandas as pd
siblings = pd.DataFrame(result['siblings'], columns=['name', 'age'])
siblings
name | age | |
---|---|---|
0 | Scott | 30 |
1 | Katie | 38 |
# Use pandas.read_json to automatically convert JSON datasets into a Series or DataFrame
!cat examples/example.json
[{"a": 1, "b": 2, "c": 3}, {"a": 4, "b": 5, "c": 6}, {"a": 7, "b": 8, "c": 9}]
# The default options for pandas.read_json assume that each object in the JSON array
# is a row in the table:
data = pd.read_json('examples/example.json')
data
a | b | c | |
---|---|---|---|
0 | 1 | 2 | 3 |
1 | 4 | 5 | 6 |
2 | 7 | 8 | 9 |
# To export the data from pandas to JSON, use the to_json method
print(data.to_json())
{"a":{"0":1,"1":4,"2":7},"b":{"0":2,"1":5,"2":8},"c":{"0":3,"1":6,"2":9}}
print(data.to_json(orient='records'))
[{"a":1,"b":2,"c":3},{"a":4,"b":5,"c":6},{"a":7,"b":8,"c":9}]
Features of the input
import pandas as pd
df = pd.DataFrame([{'day': 'sun', 'month': ['jan']},
{'day': 'mon', 'month': ['feb', 'mar']},
{'day': 'tue', 'month': ['apr', None]}])
print('df: \n', df, '\n')
json = df.to_json(orient='records')
print('json: \n', json, '\n')
# print(type(json))
# Todo:- Is there a need to use orient='records' when calling read_json? Find out later.
# df2 = pd.read_json(json, orient='records')
df2 = pd.read_json(json)
print('df2: \n', df2, '\n')
df: day month 0 sun [jan] 1 mon [feb, mar] 2 tue [apr, None] json: [{"day":"sun","month":["jan"]},{"day":"mon","month":["feb","mar"]},{"day":"tue","month":["apr",null]}] df2: day month 0 sun [jan] 1 mon [feb, mar] 2 tue [apr, None]