Pandas: Read JSON

The pandas.read_json function is a versatile tool in the Pandas library for loading JSON (JavaScript Object Notation) data into DataFrames, facilitating data manipulation and analysis. Built on NumPy Array Operations, it efficiently handles structured JSON data, offering options for parsing, data type specification, and managing nested structures. This tutorial explores Pandas read_json, covering its usage, parameters, optimization, and applications in machine learning workflows.

---

01. Why Use pandas.read_json?

JSON is a popular format for structured data, commonly used in APIs, web applications, and data storage. pandas.read_json simplifies loading JSON data into DataFrames, enabling seamless preprocessing, feature engineering, and analysis for machine learning. Its flexibility in handling various JSON structures, including nested objects and arrays, combined with NumPy’s performance, makes it essential for preparing datasets from diverse sources.

Example: Basic JSON Reading

import pandas as pd
import json

# Create a sample JSON file
data = [
    {"Name": "Alice", "Age": 25, "Salary": 50000},
    {"Name": "Bob", "Age": 30, "Salary": 60000}
]
with open('sample.json', 'w') as f:
    json.dump(data, f)

# Read JSON into DataFrame
df = pd.read_json('sample.json')
print("DataFrame:\n", df)

Output:

DataFrame:
      Name  Age  Salary
0  Alice   25   50000
1    Bob   30   60000

Explanation:

• pd.read_json - Loads JSON data into a DataFrame, with keys as column names.
• Each row corresponds to a JSON object in the input array.

---

02. Key Features of pandas.read_json

pandas.read_json provides a range of parameters to customize data loading, optimizing memory and performance for various JSON structures. The table below summarizes key features and their relevance to machine learning:

Feature	Description	ML Use Case
Orientation Control	Supports different JSON formats (e.g., records, columns)	Parse API responses or logs
Data Type Specification	Controls column data types	Optimize memory for large datasets
Nested Data Handling	Flattens or processes nested JSON	Extract features from complex data
Missing Value Support	Handles null values	Data cleaning, imputation

2.1 Basic JSON Loading

Example: Loading JSON with Default Settings

import pandas as pd

# Create a sample JSON file
data = [
    {"ID": 1, "Name": "Alice", "Score": 85},
    {"ID": 2, "Name": "Bob", "Score": 90}
]
with open('data.json', 'w') as f:
    json.dump(data, f)

# Read JSON
df = pd.read_json('data.json')
print("DataFrame:\n", df)
print("Data types:\n", df.dtypes)

Output:

DataFrame:
    ID   Name  Score
0   1  Alice     85
1   2    Bob     90
Data types:
 ID        int64
Name     object
Score     int64
dtype: object

Explanation:

• Pandas infers column names and data types from the JSON structure.
• Default orientation assumes a list of records (objects).

2.2 Handling Different JSON Orientations

Example: Reading Column-Oriented JSON

import pandas as pd

# Create a column-oriented JSON file
data = {
    "Name": ["Alice", "Bob"],
    "Age": [25, 30],
    "Salary": [50000, 60000]
}
with open('data_columns.json', 'w') as f:
    json.dump(data, f)

# Read JSON with columns orientation
df = pd.read_json('data_columns.json', orient='columns')
print("DataFrame:\n", df)

Output:

DataFrame:
      Name  Age  Salary
0  Alice   25   50000
1    Bob   30   60000

Explanation:

• orient='columns' - Specifies that the JSON is structured with columns as keys.
• Other orientations include records, split, and index.

2.3 Specifying Data Types

Example: Optimizing Data Types

import pandas as pd

# Create a sample JSON file
data = [
    {"ID": 1, "Category": "A", "Score": 85.0},
    {"ID": 2, "Category": "B", "Score": 90.0}
]
with open('data_types.json', 'w') as f:
    json.dump(data, f)

# Read JSON with specified data types
df = pd.read_json('data_types.json', dtype={'ID': 'int32', 'Category': 'category', 'Score': 'float32'})
print("DataFrame:\n", df)
print("Data types:\n", df.dtypes)
print("Memory usage (bytes):\n", df.memory_usage(deep=True).sum())

Output:

DataFrame:
    ID Category  Score
0   1        A   85.0
1   2        B   90.0
Data types:
 ID            int32
Category   category
Score       float32
dtype: object
Memory usage (bytes): 258

Explanation:

• dtype - Specifies compact types to reduce memory usage.
• category type optimizes memory for discrete values.

2.4 Handling Nested JSON

Example: Flattening Nested JSON

import pandas as pd

# Create a sample nested JSON file
data = [
    {"Name": "Alice", "Details": {"Age": 25, "City": "New York"}},
    {"Name": "Bob", "Details": {"Age": 30, "City": "London"}}
]
with open('data_nested.json', 'w') as f:
    json.dump(data, f)

# Read and flatten JSON
df = pd.json_normalize(pd.read_json('data_nested.json').to_dict('records'))
print("Flattened DataFrame:\n", df)

Output:

Flattened DataFrame:
      Name  Details.Age Details.City
0  Alice          25     New York
1    Bob          30       London

Explanation:

• pd.json_normalize - Flattens nested JSON into columns, useful for extracting features from complex data.
• Nested fields are prefixed with parent keys (e.g., Details.Age).

2.5 Handling Missing Values

Example: Processing Missing Data

import pandas as pd

# Create a sample JSON file with missing values
data = [
    {"Name": "Alice", "Age": 25, "Salary": 50000},
    {"Name": "Bob", "Age": None, "Salary": 60000},
    {"Name": "Charlie", "Age": 35}
]
with open('data_missing.json', 'w') as f:
    json.dump(data, f)

# Read JSON
df = pd.read_json('data_missing.json')
print("DataFrame:\n", df)
print("Missing values:\n", df.isna().sum())

Output:

DataFrame:
       Name   Age   Salary
0   Alice  25.0  50000.0
1     Bob   NaN  60000.0
2  Charlie  35.0      NaN
Missing values:
 Name      0
Age       1
Salary    1
dtype: int64

Explanation:

• JSON null or missing fields are loaded as NaN.
• Ready for imputation or cleaning in machine learning preprocessing.

2.6 Incorrect Usage

Example: Incorrect Orientation

import pandas as pd

# Create a column-oriented JSON file
data = {
    "Name": ["Alice", "Bob"],
    "Age": [25, 30]
}
with open('data_error.json', 'w') as f:
    json.dump(data, f)

# Incorrect: Default records orientation
try:
    df = pd.read_json('data_error.json')
    print(df)
except ValueError as e:
    print("Error:", e)

Output:

Error: Expected object or value

Explanation:

• Default orient='records' expects a list of objects, not column-oriented JSON.
• Solution: Use orient='columns' for this structure.

---

03. Effective Usage

3.1 Recommended Practices

• Specify dtype to optimize memory for large JSON datasets.

Example: Memory-Efficient JSON Loading

import pandas as pd
import json

# Create a large JSON file
data = [{"ID": i, "Category": "A", "Value": 1.0} for i in range(10000)]
with open('large_data.json', 'w') as f:
    json.dump(data, f)

# Read JSON with optimized types
df = pd.read_json('large_data.json', dtype={'ID': 'int32', 'Category': 'category', 'Value': 'float32'})
print("Memory usage (bytes):\n", df.memory_usage(deep=True).sum())
print("DataFrame head:\n", df.head())

Output:

Memory usage (bytes): 40256
DataFrame head:
    ID Category  Value
0   0        A    1.0
1   1        A    1.0
2   2        A    1.0
3   3        A    1.0
4   4        A    1.0

• Use pd.json_normalize for nested JSON structures.
• Inspect JSON structure (e.g., orientation, nesting) before loading.

3.2 Practices to Avoid

• Avoid loading large JSON files without specifying data types, as defaults like float64 are memory-intensive.

Example: Inefficient Data Types

import pandas as pd

# Create a JSON file
data = [{"ID": i, "Value": 1} for i in range(10000)]
with open('inefficient.json', 'w') as f:
    json.dump(data, f)

# Read with default types
df = pd.read_json('inefficient.json')
print("Memory usage (bytes):\n", df.memory_usage(deep=True).sum())

Output:

Memory usage (bytes): 160128

• Default int64 and float64 types waste memory.
• Solution: Use dtype={'ID': 'int32', 'Value': 'float32'}.

---

04. Common Use Cases in Machine Learning

4.1 Loading API Data

Process JSON data from APIs for machine learning.

Example: Loading and Cleaning API JSON

import pandas as pd

# Create a sample API-like JSON
data = [
    {"user_id": 1, "name": "Alice", "score": 85.0},
    {"user_id": 2, "name": "Bob", "score": None}
]
with open('api_data.json', 'w') as f:
    json.dump(data, f)

# Read JSON and handle missing values
df = pd.read_json('api_data.json', dtype={'user_id': 'int32', 'score': 'float32'})
df['score'].fillna(df['score'].mean(), inplace=True)
print("Cleaned DataFrame:\n", df)

Output:

Cleaned DataFrame:
    user_id   name  score
0        1  Alice   85.0
1        2    Bob   85.0

Explanation:

• Handles missing values (None) with imputation, preparing data for modeling.

4.2 Feature Extraction from Nested JSON

Extract features from complex JSON structures.

Example: Flattening Nested JSON

import pandas as pd

# Create a nested JSON
data = [
    {"id": 1, "info": {"height": 170, "weight": 70}},
    {"id": 2, "info": {"height": 165, "weight": 60}}
]
with open('features.json', 'w') as f:
    json.dump(data, f)

# Read and flatten JSON
df = pd.json_normalize(pd.read_json('features.json').to_dict('records'))
print("Flattened DataFrame:\n", df)

Output:

Flattened DataFrame:
    id  info.height  info.weight
0   1          170           70
1   2          165           60

Explanation:

• pd.json_normalize - Converts nested fields into usable features for machine learning.

---

Conclusion

pandas.read_json is a robust tool for loading JSON data into DataFrames, offering flexibility for handling various orientations, nested structures, and missing values. Powered by NumPy Array Operations, it optimizes memory and performance for machine learning workflows. Key takeaways:

• Use orient to match JSON structure.
• Specify dtype for memory efficiency.
• Handle nested JSON with pd.json_normalize.
• Avoid incorrect orientations and default data types for optimal performance.

With pandas.read_json, you’re equipped to efficiently load and prepare JSON data for machine learning and data analysis!