In this article, we implement a python library that works with the labeled data ie pandas. It focuses on operations on relational data, here we would convert JSON string pandas DataFrame with the help of the read_json() function. Multiple manipulations of the function will be carried out to receive the desired output.
Also read: How to Read a JSON File in Python
What is read_json()?
JSON is the most used format to exchange data between two systems or web applications hence often processing of JSON files is needed. read_json() function allows us to read a JSON file and display it in grid format.
Syntax of read_json()
pandas.read_json('string/path/file_name', orient)
Parameters
| Parameter | Description | Required/Optional |
| string/path/file_name | Accepts JSON input as the first parameter. Could be in three formats, 1. A JSON string: we can input a JSON string. 2. A path to the JSON file: specifying JSON file name along with the path. 3. The JSON file’s name: If the JSON file is in the current directory, we can specify its name only. | Required |
| orient | The orientation of the JSON string. Possible formats: index, record, values, or columns | Required |
Return value
It returns JSON data as DataFrame or series also it depends on the orient value.
Also read: How to convert Dictionary into JSON in Python?
Examples of read_json()
We begin by importing the pandas library.
import pandas as pd
Example 1: record-oriented JSON string into Pandas Dataframe
json_data ='''[{ "id": "101", "Name": "Emily","Age":22 ,"Department" : "HR" },
{ "id": "102", "Name": "Charles","Age":22,"Department" : "IT"},
{ "id": "103", "Name": "George","Age":21,"Department" : "Finance"},
{ "id": "104", "Name": "Emma","Age":23,"Department" : "HR"}]'''
df= pd.read_json(json_data,orient='records')
print(df)
We input a JSON string that stores the data as a dictionary of values with another list. Such input can be reserved by ‘records’ orientation. We would convert the JSON string containing four key-value pairs in 4 rows to Pandas Dataframe.
Example 2: index-oriented JSON string into Pandas Dataframe
json_data=
'''
{"employee-1": {"id": "101", "Name": "Emily","Age":22 ,"Department" : "HR" },
"employee-2": { "id": "102", "Name": "Charles","Age":22,"Department" : "IT"},
"employee-3": { "id": "103", "Name": "George","Age":21,"Department" : "Finance"},
"employee-4": { "id": "104", "Name": "Emma","Age":23,"Department" :
"HR"}}'''
df= pd.read_json(json_data,orient='index')
print(df)
For the input JSON string containing data in a dictionary of values surrounded by a dictionary with a key as an index, we would be using the ‘index’ orientation.
In this example, we created the JSON string with four key-value pairs in 4 rows with each row specified by index ie the employee number. In the output, the indices will be the rows in the data frame.
Example 3: column-oriented JSON string into Pandas Dataframe
json_data=
'''
{"employee-1": {"id": "101", "Name": "Emily","Age":22 ,"Department" : "HR" },
"employee-2": { "id": "102", "Name": "Charles","Age":22,"Department" : "IT"},
"employee-3": { "id": "103", "Name": "George","Age":21,"Department" : "Finance"},
"employee-4": { "id": "104", "Name": "Emma","Age":23,"Department" :
"HR"}}'
df= pd.read_json(json_data,orient='columns')
print(df)
When orient = columns the column indices will be the column in the data frame. Here the column name id, name,age, and department are the indices.
Example 4: values-oriented JSON string into Pandas Dataframe
json_data='''[
[ "101","Emily",22 ,"HR" ],
[ "102","Charles",22,"IT" ],
[ "103","George",21, "Finance" ],
[ "104", "Emma",23,"HR" ]
]
'''
df = pd.read_json(json_data,orient='values')
print(df)
In this example, the four key-values paired data have their column and row by default starting with 0.
Summary
In this article, we have learned how to convert a JSON from the string into Panda DataFrame and also use different optional parameters with examples. Browse more articles at AskPython.
Reference