How to Read JSON Files with Pandas?

JSON (JavaScript Object Notation) store data using key-value pairs. Reading JSON files using Pandas is simple and helpful when you're working with data in .json format. There are mainly three methods to read Json file using Pandas Some of them are:

• Using pd.read_json() Method
• Using JSON Module and pd.json_normalize() Method
• Using pd.Dataframe() Methods

1. Using pd.read_json() to Read JSON Files in Pandas

The pd.read_json() function helps to read JSON data directly into a DataFrame. This method is used when we working with standard JSON structures. If the file is located on a remote server we can also pass the URL instead of a local file path. Let’s say you have a file named data.json with the following content:

[

{"id": 1, "name": "Alice", "age": 25},

{"id": 2, "name": "Bob", "age": 30},

{"id": 3, "name": "Charlie", "age": 22}

]

You can read this JSON file using the code below:

import pandas as pd

df = pd.read_json('data.json')
print(df.head())

Output:

2. Using json Module and pd.json_normalize() method

The json_normalize() is used when we are working with nested JSON structues. JSON from APIs often comes in nested form and this method helps to flatten it into a tabular format that’s easier to work with in Pandas. This method is helpful when working with real-world JSON responses from APIs.

import pandas as pd
import json

data = {"One": {"0": 60, "1": 60, "2": 60, "3": 45, "4": 45, "5": 60},
        "Two": {"0": 110, "1": 117, "2": 103, "3": 109, "4": 117, "5": 102}}

json_data = json.dumps(data)

df_normalize = pd.json_normalize(json.loads(json_data))
print("\nDataFrame using JSON module and `pd.json_normalize()` method:")
df_normalize

Output:

3. Using pd.DataFrame with a Dictionary

If JSON data is stored as a dictionary we can directly use pd.DataFrame() convert it into a structured DataFrame. This is helpful when you are working with pre-loaded or manually created JSON data in memory.

import pandas as pd

df = pd.DataFrame(data)

print(df)

Output:

These methods help you to use JSON data into Pandas for analysis and visualization. With just a few lines of code you can turn raw JSON into a clean and usable DataFrame.