How to Add JSON Data to a Pandas DataFrame in Python

Hello friends! Today we’ll see how to easily add JSON data to a Pandas DataFrame in Python. This is very useful when working with web APIs or JSON files.

First, import the needed libraries:

import pandas as pd
import json

Now, let’s say we have some JSON data:

json_data = '{"name": "Rahul", "age": 30, "city": "Mumbai"}'

To add this to a DataFrame, we can use the pd.read_json() function:

df = pd.read_json(json_data, orient='index')

If you have a JSON file instead, just give the file path:

If you have a JSON file instead, just give the file path:

For nested JSON, you might need to normalize it first:

df = pd.json_normalize(json.loads(json_data))

That’s it! Now you have your JSON data in a nice Pandas DataFrame. You can do all sorts of analysis and manipulations on it.

Example

import pandas as pd
import json

# Sample JSON data
json_data = '''
{
    "students": [
        {"name": "Rahul", "age": 22, "city": "Mumbai", "grades": {"maths": 85, "science": 92}},
        {"name": "Priya", "age": 21, "city": "Delhi", "grades": {"maths": 78, "science": 88}},
        {"name": "Amit", "age": 23, "city": "Bangalore", "grades": {"maths": 90, "science": 95}}
    ]
}
'''

# Parse JSON data
parsed_data = json.loads(json_data)

# Create DataFrame
df = pd.json_normalize(parsed_data['students'])

# Display the DataFrame
print(df)

# Access specific columns
print("\nNames and ages:")
print(df[['name', 'age']])

# Access nested data
print("\nMaths grades:")
print(df['grades.maths'])

# Add a new column
df['total_grade'] = df['grades.maths'] + df['grades.science']

# Display updated DataFrame
print("\nUpdated DataFrame with total grade:")
print(df)