Python Pandas Dataframe To Nested Json

When working with data in Python,Pandas is a popular library for handling tabular data efficiently. Converting a Pandas DataFrame to a nested JSON structure can be necessary for various reasons, such as preparing data for API responses or interacting with nested JSON-based data structures. In this article, we will explore four approaches to achieving this using Pandas.

What is nested JSON?

Nested JSON is a way of organizing data in a hierarchical manner using key-value pairs. Nested JSON structures are commonly used in various applications, especially in APIs for exchanging data between servers and clients. They allow for the organization of complex data in a way that’s easy to understand and work with programmatically.

Pandas Dataframe To Nested Json in Python

Below are some of the ways in which we can convert Pandas DataFrames into Nested JSON in Python:

Use to_json() method

The most straightforward approach is to use the `to_json` method of pandas, specifying the orientation as ‘records’.

Python3

import pandas as pd

# Create a sample DataFrame
data = {'Name': ['Alice', 'Bob', 'Charlie'],
        'Age': [25, 30, 35],
        'City': ['New York', 'San Francisco', 'Los Angeles']}
df = pd.DataFrame(data)

# Convert DataFrame to nested JSON
json_data = df.to_json(orient='records')
print(json_data)

Output:

[{'Name': 'Alice', 'Age': 25, 'City': 'New York'}, {'Name': 'Bob', 'Age': 30, 'City': 'San Francisco'}, {'Name': 'Charlie', 'Age': 35, 'City': 'Los Angeles'}]

Grouped Data to Json Data

To convert a pandas DataFrame grouped by a column into nested JSON, you can use pandas and Python’s groupby function.

Python3

# Group DataFrame by 'City' column and convert to nested JSON
grouped_data = df.groupby('City').apply(lambda x: x[['Name', 'Age']].to_json(orient='records'))

# Print the grouped data
print(grouped_data)

Output:

City
Los Angeles      {"Name":{"2":"Charlie"},"Age":{"2":35}}
New York           {"Name":{"0":"Alice"},"Age":{"0":25}}
San Francisco        {"Name":{"1":"Bob"},"Age":{"1":30}}
dtype: object

Using a Custom Function

The code below will create a DataFrame with columns ‘Name’ and ‘Age’, define a custom function to create a nested structure for each row, apply this function to each row of the DataFrame, convert the resulting list of dictionaries into JSON format, and finally print the JSON output with indentation for better readability.

Python3

import json

# Define a custom function to create a nested structure


def custom_nested_structure(row):
    return {'Person': {'Name': row['Name'], 'Age': row['Age']}}


# Apply the custom function to each row of the DataFrame
json_data_custom = df.apply(custom_nested_structure, axis=1).tolist()

# Convert list of dictionaries to JSON
json_output = json.dumps(json_data_custom, indent=4)

print(json_output)

Output:

[
    {
        "Person": {
            "Name": "Alice",
            "Age": 25
        }
    },
    {
        "Person": {
            "Name": "Bob",
            "Age": 30
        }
    },
    {
        "Person": {
            "Name": "Charlie",
            "Age": 35
        }
    }
]