JIYIK CN >

This article will show you how to convert JSON to Pandas DataFrame.

JSON is the abbreviation of JavaScript Object Notation. It is a format based on objects in JavaScript and is an encoding technology for representing structured data. It is a format based on objects in JavaScript and is an encoding technology for representing structured data. Currently, it is widely used, especially for sharing data between servers and web applications.

JSON is becoming very popular due to its simplicity and influence on programming language data structures. It is relatively easy to understand, below is an example of a JSON response from a simple API.

{
"Results":
         [
         { "id": "01", "Name": "Jay" },
         { "id": "02", "Name": "Mark" },
         { "id": "03", "Name": "Jack" }
         ],
"status": ["ok"]
}

As you can see in our example, JSON seems to be a combination of nested lists and dictionaries to some extent, so it is relatively easy to extract data from the JSON file and even store it as a Pandas DataFrame.

Pandas and JSON libraries in Python can help with this. We have two functions read_json()and json_normalize()that can help convert a JSON string into a DataFrame.

json_normalize()Convert JSON to Pandas DataFrame using

json_normalize()The function is very widely used to read a nested JSON string and return a DataFrame. To use this function, we first need to json.loads()read the JSON string using the function from the JSON library in Python, and then we pass this JSON object to json_normalize(), which will return a Pandas DataFrame containing the required data.

import pandas as pd
import json
from pandas import json_normalize

data = """
{
"Results":
         [
         { "id": "1", "Name": "Jay" },
         { "id": "2", "Name": "Mark" },
         { "id": "3", "Name": "Jack" }
         ],
"status": ["ok"]
}
    """

info = json.loads(data)

df = json_normalize(info["Results"])  # Results contain the required data
print(df)

Output:

   id  Name
0  1   Jay
1  2  Mark
2  3  Jack

read_json()Convert JSON to Pandas DataFrame using

Another Pandas function to convert JSON to DataFrame is read_json()for simple JSON string. We can directly pass the path of JSON file or JSON string to the function to store the data in Pandas DataFrame. read_json()There are many parameters among which orientspecifies the format of JSON string.

The downside is that it is difficult to use with nested JSON strings. So for the sake of usage read_json(), we will use a much simpler example as shown below.

import pandas as pd

data = """
          {
              "0":{
                  "Name": "Jay",
                  "Age": "17"
              },
              "1":{
                  "Name": "Mark",
                  "Age": "15"
              },
              "2":{
                  "Name": "Jack",
                  "Age":"16"
              }
          }
    """

df = pd.read_json(data, orient="index")
print(df)

Output:

   Name  Age
0   Jay   17
1  Mark   15
2  Jack   16

We orientset to 'index'because the JSON string fromat matches the pattern {index : {column: value}}.