Python Pandas

Pandas From JSON

JSON or JavaScript Object Notation is very similar to Python dictionaries in appearance, as both stores data in key-value pair format. The JSON, a text data format, is used to store, extract or exchange data via the JSON files or JSON string. Sometimes while dealing/working with JSON data, we need to transform/convert it into Pandas DataFrame.

This article will provide details about converting JSON data to Pandas DataFrame with the help of multiple examples.

How to Read and Convert JSON to Pandas DataFrame in Python?

In Python, different methods are used to read and convert JSON data to Pandas DataFrame, such as:

The following JSON file named “example.json” is used in the below example:

Method 1: Convert JSON to Pandas DataFrame Using “read_json()” Method

The “read_json()” method is used to read the JSON Data from the file and convert it into Pandas DataFrame. Here is an example to transformed JSON to DataFrame:

import pandas
df = pandas.read_json('example.json')
print(df)
print(type(df))

 

Here in this example, the “pandas.read_json()” method of the “JSON” module accepts the JSON file path as an argument and retrieves/returns the DataFrame.

Note: Check this guide for a detailed understating of this method.

Output

The DataFrame from the JSON file data has been created successfully.

Method 2: Convert JSON to Pandas DataFrame Using “pd.DataFrame()” Method

The “pandas.DataFrame()” method can also be used to create a Pandas DataFrame by accepting the JSON string data as an argument. Let’s understand it by the following example:

import pandas
json_data ={
    "Name": [
        "Joseph",
        "Lily",
        "Anna",
        "Henry"
    ],
    "Age": [
        15,
        21,
        25,
        22
    ],
    "Height": [
        4.5,
        5.3,
        5.6,
        6.7
    ]
}
df = pandas.DataFrame(json_data)
print(df)
print(type(df))

 

In the above-stated code:

  • First, import the “pandas” module.
  • Then, initialized the JSON string.
  • After that, used the “pandas.DataFrame()” method that accepts the particular JSON string as an argument and returns/retrieves the DataFrame.

As you can see, the JSON string data has been converted into Pandas DataFrame successfully:

Method 3: Convert JSON to Pandas DataFrame Using “json_normalize()” Method

The “json_normalize()” method is used to retrieve the DataFrame by normalizing the semi-structured JSON data. The below example demonstrates how the JSON data has been converted into DataFrame:

import pandas
import json
json_data = '''
{
"students":
         [
         { "Name": "Joseph", "Age": 22,"Height": 5.4},
         { "Name": "Anna","Age": 25,"Height": 5.8},
         { "Name": "Lily", "Age": 23,"Height": 6.1}
         ],
"invalid": ["True"]
}
'
''
data = json.loads(json_data)
df = pandas.json_normalize(data['students'])
print(df)
print(type(df))

 

Here:

  • First of all, add the necessary modules.
  • Then, invoked the “json.loads()” method that accepts the JSON string data as an argument and retrieves/returns the Python dictionary.
  • Next, pass the created dictionary to the “json_normalize()” method to retrieve the DataFrame.
  • Lastly, use the “print()” method to display the retrieved DataFrame and its datatype.

Output

The particular JSON string has been transformed/converted into DataFrame.

Method 4: Convert JSON to Pandas DataFrame Using “df.from_dict()” Method

The “df.from_dict()” method is used to convert the dictionary into Pandas DataFrame. Let us utilize this method to convert the JSON to Pandas DataFrame:

import pandas
import json
json_data = '{ "Name": "Joseph", "Age": 22,"Height": 5.7}'
data = json.loads(json_data)
df = pandas.DataFrame.from_dict(data, orient="index")
print(df)
print(type(df))

 

Here, in this code, the “json.loads()” is used along with the “df.from_dict()” method to convert the JSON string into Pandas DataFrame.

The DataFrame has been successfully created from the JSON string:

Conclusion

The “read_json()”, “pd.DataFrame()”, “json_normalize()”, and “df.from_dict()” methods are used to read and convert JSON data to Pandas DataFrame. The “read_json()” method is employed to read JSON file data and convert it into DataFrame. All the other methods can convert the JSON string data into DataFrame. This article presented a presented tutorial on creating DataFrame from JSON data using several examples.

About the author

Haroon Javed

Hi, I'm Haroon. I am an electronics engineer and a technical content writer. I am a tech geek who loves to help people to the best of my knowledge.